May 28, 2014 at 12:31 pm
Hi,
I do have a table with 15000 records. I am not sure it is transaction table or static table.
Table1(ID int not null, Code1 nvarchar(10) not null,Code2 nvarchar(10) not null,Code3 nvarchar(10) not null,Code4 nvarchar(10) not null,Code5 nvarchar(10) not null,Code6 nvarchar(10) not null,Code7 nvarchar(10) not null, Code8 nvarchar(10) not null, time datetime null, valid smallint null, problem nvarchar(50) null)
I didn't see any PK on the table.
I need to write following update statement and wrap in SP.
UPDATE Table1
SET [valid] = @given
[Time] = @giventime
[Problem] = @givenProblem
WHERE ID = @givenID.
To get the optimization of this statment is it good to create non-clustered index on ID column or just creating PK is fine or shall we create PK and non-clustered Index on the same ID column
May 28, 2014 at 12:56 pm
Personally I believe it is your chance to do things right, so I would create a PK (as a clustered index).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 28, 2014 at 12:58 pm
Is ID going to be your most commonly queried field in that table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 28, 2014 at 3:04 pm
I am new to the environment. I think might be ID they use for the most of the queries. Is it better to leave that table with out creating any index. 15000 rows table scan is better or index scan is better?
May 28, 2014 at 4:00 pm
I'm of the opinion that tables should have clustered indexes.
With an index scan versus a table scan, the index being smaller (if just on the ID) could be less impact. But your mileage may vary and how it is actually used in your environment could vary.
Have you tried to test this in a dev environment to find which works best for your environment?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 28, 2014 at 7:23 pm
ramana3327 (5/28/2014)
Hi,I do have a table with 15000 records. I am not sure it is transaction table or static table.
Table1(ID int not null, Code1 nvarchar(10) not null,Code2 nvarchar(10) not null,Code3 nvarchar(10) not null,Code4 nvarchar(10) not null,Code5 nvarchar(10) not null,Code6 nvarchar(10) not null,Code7 nvarchar(10) not null, Code8 nvarchar(10) not null, time datetime null, valid smallint null, problem nvarchar(50) null)
I didn't see any PK on the table.
I need to write following update statement and wrap in SP.
UPDATE Table1
SET [valid] = @given
[Time] = @giventime
[Problem] = @givenProblem
WHERE ID = @givenID.
To get the optimization of this statment is it good to create non-clustered index on ID column or just creating PK is fine or shall we create PK and non-clustered Index on the same ID column
I agree with what has already been said. Even though this is a tiny table, it should have a clustered index on the ID column. Since that's the likely candidate for a primary key, it should be a clustered PK.
As to the table itself, there are some problems that I'd like to suggest corrections to.
First, the ID column. Unlike certain folks, I have no problem with such surrogate keys especially for the type of table you have but you should probably prefix the word "ID" with the table name to make it a wee bit more description if it gets caught out in the code all alone.
Second, you have 8 "Code" columns and they're all being built as NOT NULL. The NOT NULL is a great idea but will you truly have all 8 values for every row or are you going to fill some of them with blanks or some other form of "no code" representation? If the latter, then consider a redesign of the table to normalize it. That will also save you incredible headaches when the inevitable "Code9" rears its ugly head.
Third, I assume that "Valid" is likely going to be a "1" or a "0". That type of thing frequently changes because people decide just two conditions for a given row aren't enough. I strongly recommend you change the name of this column to "StatusCode" and make it a CHAR(1) or CHAR(2) for alphabetic status codes that someone can easily memorize. Of course, there should be a lookup table with an FK on this column to that lookup table. I've had an "IsValid" column change meaning enough times in my life to almost guarantee you that such a change now will save you huge amounts of aggravation further down the line.
What's going into the "Time" column??? First, that's a rserved word in 2012 and most folks will tell you to avoid reserved words as object names in SQL Server. Yeah, you can sometimes get away with it but it will eventually cause an addiction to Rolaids. 😉 Also, "Time" could mean absolutely anything having to do with temporal features. Be more descriptive. Since you used the DATETIME datatype, it appears that it should also contain a date. Would that be the date and time the row was created, the date and time the row was validated, the date and time the problem was solved, the date and time the row was last modified, or what?
Last but not least, what on this good Green Earth is the "problem" column going to be used for? Will it contain a problem number, problem code, problem description, or what??? Name that column with a decent suffix so people don't have to guess what it's supposed to contain.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply