Index optimization is always interesting subject to me. Every time I receive requests to help optimize query or query on any specific table. I always ask Jr.DBA to go over following list first before I take a look at it. Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.
SQL SERVER – Good, Better and Best Programming Techniques
A week ago, I was invited to meeting of programmers. Subject of meeting was “Good, Better and Best Programming Techniques”. I had made small note before I went to meeting, so if I have to talk about or discuss SQL Server it can come handy. Well, I did not get…
Read MoreSQL SERVER – Difference Between Unique Index vs Unique Constraint
Unique Index and Unique Constraint are the same. They achieve same goal. SQL Performance is same for both. Add Unique Constraint ALTER TABLE dbo.<tablename> ADD CONSTRAINT <namingconventionconstraint> UNIQUE NONCLUSTERED ( <columnname> ) ON [PRIMARY] Add Unique Index CREATE UNIQUE NONCLUSTERED INDEX <namingconventionconstraint> ON dbo.<tablename> ( <columnname> ) ON [PRIMARY] There…
Read MoreSQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table
Following script will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table. SELECT IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE…
Read More
SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index
Clustered Index Only 1 allowed per table Physically rearranges the data in the table to conform to the index constraints.
SQL Server Interview Questions and Answers – Part 3
Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers.
SQL Server Interview Questions and Answers – Part 2
Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers.