July Discount: Comprehensive Database Performance Health Check | Testimonials

SQL SERVER – Disable Index – Enable Index – ALTER Index

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
, ,
There are few requirements in real world when Index on table needs to be disabled and re-enabled afterwards. e.g. DTS, BCP, BULK INSERT etc. Index can be dropped and recreated. I prefer to disable the Index if I am going to re-enable it again. USE AdventureWorks GO ----Diable Index ALTER INDEX [IX_StoreContact_ContactTypeID]…
Read More

SQL SERVER – Index Optimization CheckList

Pinal Dave
SQL Performance
, , ,

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.

Read More

SQL SERVER – Good, Better and Best Programming Techniques

Pinal Dave
SQL, SQL Interview Questions and Answers, SQL Performance, SQL Server, SQL Tips and Tricks
, , , , , , , , , , ,
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 More

SQL SERVER – Difference Between Unique Index vs Unique Constraint

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
, ,
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 More

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
, , , , ,
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
Menu