DB Design :: Unable To Create Index After Column Resizing
Oct 5, 2015
We have a vendor created database with 9000+ tables, one of which has about 6 billion rows. The vendor redesigned the database recently and ever since we've had terrible performance.
What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.
Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.
Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.
The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).
There is no additional data, just wider columns. Why we are unable to create the index?
What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?
View 3 Replies
ADVERTISEMENT
Oct 22, 2007
Hi
I was asked to import some data from excel into a table within a sqlserver 2000 db the import was complaining about an index so then I deleted the index imported the data succesfilly but I'm now unable to re create the index, please see the error that I'm getting below. Can someone please help.
Thanks
- Unable to create index 'trainingGo'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 32. Most significant primary key is '439'.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
View 14 Replies
View Related
Jul 9, 2015
I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:
What would be the best practices (best methods) for provisioning a ldf file and mdf file? Exists any generic formula?
With this i want to avoid the shrink operation and the autogrow of sql server databases...
View 6 Replies
View Related
Aug 27, 2015
I have table which having clustered index based on column (A,B,C,D,E,F).Now my query based on B,D,F. e.g: where b='Test1' and D='test2' and F='test3' Now Execution plan ask to create non clustered index with (B,D,F) column.is it make any sense to create non clustered index where clustered already available.
View 4 Replies
View Related
Dec 5, 2007
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP
View 3 Replies
View Related
Oct 20, 2015
can you have constraints as such [CreateBy] [nvarchar](30) NOT NULL DEFAULT (suser_sname()),on a table that has a column store index in SQL Server 2012,2014, or 2016?
View 3 Replies
View Related
Nov 30, 2005
I am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you
View 1 Replies
View Related
Nov 24, 2015
I created composite clustered index key based on Gender and Salary column
The Query executed Successfully and <g class="gr_ gr_135 gr-alert gr_tiny gr_spell undefined ContextualSpelling multiReplace" data-gr-id="135" id="135">i</g>
got composite index key id Gender(-), Salary I <g class="gr_ gr_310 gr-alert gr_gramm undefined Grammar only-ins replaceWithoutSep" data-gr-id="310" id="310">want</g> know why Gender(-) display like this?
And Gender is <g class="gr_ gr_391 gr-alert gr_spell undefined ContextualSpelling ins-del multiReplace" data-gr-id="391" id="391">nvarchar</g> (20)
View 2 Replies
View Related
Oct 5, 2015
How Can I Create Two Column with Math Function In one Column Like Below.
Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key,
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total
)
View 5 Replies
View Related
Aug 28, 2015
I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr](
[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
[ContactKey] [int] NOT NULL,
[EventTypeEnum] [tinyint] NOT NULL,
[code]....
View 20 Replies
View Related
Apr 5, 2004
Hi all,
I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.
Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.
Any help would be appreciated.
Thanks,
Amir
View 8 Replies
View Related
Aug 5, 2015
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
View 4 Replies
View Related
Jul 11, 2007
Hi, I was wondering if any SQL Server gurus out there could help me...I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value - I've narrowed it down to this specifically, populating with non nvarchar text seems to work fine.To re-create the problem quickly...If I populate the column viaCONVERT(varbinary(max), 'test text')then there is no problem, I get results as expected.However if I populate the column viaCONVERT(varbinary(max), CAST('test text' as nvarchar(max)))no results are ever returned.Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.
View 14 Replies
View Related
Jul 11, 2007
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.
However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
UPDATE: Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...
Code Snippet
-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO
-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO
-- The below single entry WILL BE FOUND (the text source is being entered directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html')
GO
-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO
-- NOTE: You might need to give the catalog a chance to build before running the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')
View 10 Replies
View Related
Jul 20, 2005
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies
View Related
Mar 1, 2006
I have replaced the installation of MSDE 1.0 with MSDE 2000.
Prior to installation of MSDE 2000, I can perform the activities of all
database role. Now, I can only add, change and delete data - no
more database design.
What should I do to regain database design permission?
I'm using "sa" user with blank password when I login to MS Access Project.
View 5 Replies
View Related
Oct 22, 2007
Hi all,I have a very large table with many columns: dateTime type, nvarchartype and integer field type.A program exec many type of query with where clause.Data field is always in where clause, but some other field is presenttoo. Sometimes integer field, sometimes nvarchar field.Now I must create index for the query!For choose index field what i can do?I must create only index on a datetime field or every combination inevery type of query?In the second case I must create very much index! But this is verydispendious for update/insert/delete operation on the table!!!Some ideas?thnx
View 2 Replies
View Related
Apr 4, 2007
I want to create an index that will cause the cost of the query to be as low as possible and also
must minimize the space that is used by the index. What type of index/parameters I can associate when I create an Index. I already have a clustered index
View 5 Replies
View Related
Apr 25, 2008
When should a composite index be created in favor of multiple single colum indexes?
thanks
View 5 Replies
View Related
Jul 6, 2015
I want to have table with 1 PK and 1 Clustered Index on Column2 and Id, this is my code:
CREATE TABLE [dbo].[Test] (
[Id] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
[Column2] INT NOT NULL,
[Column3] INT NOT NULL,
[Column4] INT NOT NULL,
);
CREATE CLUSTERED INDEX IX_Test ON dbo.Test (Column2, Id);
Error: "Cannot create more than one clustered index on table..."I know, that PRIMARY KEY automatically create Clustered index on Id.Then I manually delete existing Clustered index, and run this code part again:CREATE CLUSTERED INDEX IX_Test ON dbo.Test (Column2, Id);Everything is fine, except I lost my PK on Id...How can I leave PK on Id and create my custom Clustering Index?
View 7 Replies
View Related
Sep 4, 2015
I'm trying to setup my foreign keys so I can use them in a VisualStudio projects but I'm getting an error and I can't track down what's causing it.
'tbl_departments' table saved successfully
'tbl_people' table saved successfully
'tbl_locations' table saved successfully
'tbl_position' table
- Unable to create relationship 'FK_tbl_position_tbl_departments'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tbl_position_tbl_departments". The conflict occurred in database "MyProjectDB", table "dbo.tbl_departments", column 'ID'. All of my tables have an ID field that is a primary key with Allow Null unchecked.
View 3 Replies
View Related
May 25, 2015
I have a table called as A table :
CREATE TABLE [dbo].[A](
[AutoID] [int] IDENTITY(1,1) NOT NULL,
[ProID] [int] NOT NULL,
[LID] [varchar](12) NOT NULL,
[EventID] [varchar](12) NOT NULL,
[HEventID] [varchar](12) NULL,
) ON [PRIMARY]
How I should creating the appropiate index for this table?A few option that I think ok.
Opt 1 : creating a primary key on the autoID with create index . create non clustered index on ProID and EventID
Opt 2 : create a primary key on the autoID with non clustered index . create clustred index on ProID and EventID .
opt 3: create primary key on the ProID and EventID with clustered index.
I have read thru the article on the primary key, clustered and non clustered indexing. However when I want to applyied the indexing..I feel a bit lost.But among the 3 option.... what is the different..
View 5 Replies
View Related
Mar 8, 1999
Hi,
I have a table which I am unable to drop the index.
I tried to drop it via the TSQL and GUI interface (Indexes dialog box),
but when I clicked on table refresh, the index still there.
Below is the result after DBCC commands:
1. DBCC checktable(TB_LOCLoan)
Checking TB_LOCLoan
The total number of data pages in this table is 146542.
Table has 1596232 data rows.
Msg 605, Level 21, State 1
Attempt to fetch logical page 462136 in database 'DM_LOCLoan' belongs to object '1744009244',
not to object 'TB_LOCLoan'.
2. DBCC newalloc(DM_LOCLoan)
TABLE: TB_LOCLoan OBJID = 832005995
INDID=0 FIRST=60168 ROOT=642778 DPAGES=146542 SORT=0
Data level: 0. 146542 Data Pages in 18393 extents.
INDID=5 FIRST=463752 ROOT=463739 DPAGES=5304 SORT=1
Msg 2525, Level 16, State 1
Table Corrupt: Object id wrong; tables: alloc page 463616 extent id=463752 l page#=463752
objid in ext=-832005995 (name = -832005995) objid in page=832005995 (name = TB_LOCLoan)
objid in sysindexes=832005995 (name = TB_LOCLoan)
TOTAL # of extents = 18393
3. From Error Msg 2525, I have tried the suggested action
use DM_LOCLoan
go
select indid, name, object_name(id)
from sysindexes
where id=832005995
and distribution=463752
go
This query does not return any row.
I am running out of clue of how to proceed! Anybody come across this problem before?
I am very eager to solve this problem as the table is hanging here, I can't even drop it to recreate
and continue data loading.
Thank you.
Regards,
Joo Pheng
View 1 Replies
View Related
Jan 13, 2006
According to sysindexes, I have a table with a primary key and an index. I'd like to drop the index. However, when I give the drop command, the message "Cannot drop the index 'eventlog._INDEX', because it does not exist in the system catalog." is returned.
I'm not sure what to look for. How do I remove the index?
View 3 Replies
View Related
Jul 24, 2015
I am creating a simple application form using visual studio 2015. I can create database.mdf successfully and create dbo.table successfully. but when i tried to view table by expanding the table icon on the server explorer, the table should be able to show list of table but it didn't show any record and why is it like that.
View 4 Replies
View Related
May 20, 2015
when I try to open the SSRS in BIDS ( VS 2008), it is displaying the code portion alone. If i click 'View Designer', it prompts me like "....already opened. Do you want to close?" Only XML content of report is displaying. if I click 'Viee Code' then it displays error like "There is no editor available for 'c: eport Sample. rdl'.Make sure the application for the file type (.rdl) is installed."how to correct this issue by installing or by upgrading?
View 6 Replies
View Related
Oct 7, 2015
Here is the DDL of a table that I am trying to add an identity column and create a clustered index. I am using SQL 2012 SE
CREATE TABLE [dbo].[IPF](
[IPFId] [uniqueidentifier] NOT NULL,
[IPId] [uniqueidentifier] NOT NULL,
[FId] [uniqueidentifier] NULL,
[FName] [nvarchar](50) NULL,
[FItemId] [uniqueidentifier] NULL,
[code]....
The table currently has 220million rows. I am trying to add a new identity new column and create a clustered index on it. I ran the script and its been more than 26hrs and its still running. I ensured there is no blocking.
View 7 Replies
View Related
Jan 27, 2006
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
View 2 Replies
View Related
May 20, 2015
I'm working on re-indexing a table using some commands from ALTER INDEX REBUILD from Microsoft. The indexes will be tested for threshold fragmentation. my plan is once the reindex is executed, a transaction backup will occur while controlling the size of the log file. The query impose time limitations or stop reindexing after the specified amount of time has elapsed.
my question,
1. How can I integrate a query which checks if transaction log is getting full and which runs a Tlog backup if over 70%
2. How do I impose time limitation?
View 3 Replies
View Related
Jul 30, 2007
I am using-- exec sp_fulltext_database 'enable'
to enable fulltext indexing on a database but query does not return any results, it just keeps on executing. I waited for about an hour and then cancelled. I dont know what is wrong. Can anybody please help me....
i am using SQL server 2000 on Windows Server 2003
SELECT SERVERPROPERTY('productversion')retured 8.00.194
SELECT SERVERPROPERTY('productlevel')returned RTM
SELECT SERVERPROPERTY('IsFullTextInstalled')returned 1
SELECT DatabaseProperty(db_name(), 'IsFulltextEnabled')returned 0
Previously it was enabled and everyting was working fine.
View 5 Replies
View Related
Sep 13, 2007
Hi,
Please, What s the differnce between a column that s a primary key and a column that s a "key/index with Isunique=true"?
Thanks a lot.
View 4 Replies
View Related
Nov 27, 2000
The size of log is around 600MB and only 20MB is used...
I tried truncating the log using dbcc shrinkfile..but it doesn't truncate
Any help would be appreciated....
Thanks.
View 3 Replies
View Related
Jun 25, 1998
We are looking into sql 7.0 and would like to know how it handles excessively sized databases. If we restore a 6.5 database into a db on 7.0 will it automatically resize to free up disk space or will it only work for new databases which have install scripts run on it.
View 2 Replies
View Related
