Vadivel's blog

We might have heard that always we need to create our CLUSTERED index first then NONCLUSTERED indexes. Why is that? What would happen if NONCLUSTERED indexes are created first and then we create the CLUSTERED index? If you create NONCLUSTERED indexes first and then CLUSTERED index internally ALL NONCLUSTERED indexes on that table would get recreated. On a big table this might take for ever to create the CLUSTERED Index itself. Example: In the sample shown in blog post titled " Query tuning using SET STATISTICS IO and SET STATISTICS TIME " we had created couple of NONCLUSTERED indexes alone. Now, let us assume we need to create a CLUSTERED index for that table on ProductID column. First enable SET STATISTICS PROFILE ON so that we can see the profile information of the scripts we are going to execute. Then execute the below script: --Script to create CLUSTERED index on ProductID column CREATE CLUSTERED INDEX [ix_productId] ON [dbo].[tblTest] ( [ProductID] ASC...

Do you find anything wrong with this script? CREATE PROCEDURE uspProcedureName       @param1 VARCHAR AS .... .... If you aren't sure may be you should read this post completely without fail :) All this while I was thinking that it is a well known issue until last week I saw a stored procedure something similar to the one shown above. Who ever created that stored procedure hasn't bothered to specify the length. Before jumping into the explanation of why we should SPECIFY THE LENGTH ALWAYS let us do a small exercise to understand this better. Guess the results: Try to answer what would be the output before checking the result. --Declaring a variable without specifying the length DECLARE @strSentence VARCHAR SET @strSentence = 'Rajinikanth is always the NO 1 hero of India' SELECT @strSentence Expected Output:  Rajinikanth is always the NO 1 hero of India Actual Output: R --While CASTing / CONVERTing --The given string has 36...

Often I find people aren't making use of the benefit of SET STATISTICS IO and SET STATISTICS TIME while trying to tune their queries. Bottom-line is we want our queries to run as fast as possible. One of the challenges we face is not all environments which we would be working on are similar. The configuration, loads et al would be different between our Development box, Staging box, Production box etc., So how can we measure whether the  changes which we do really improves the performance and it would work well in other environmentts as well? Let's try to understand few basics before seeing some code in action. For any query to be executed by SQL Server it uses many server resources. One such is "Amount of CPU resources it needs to run the query". This information would remain almost the same (There might be minimal changes in milliseconds) between executions. Another SQL resource which it needs for executing a query is IO . It would first check the Memory/Dat...

Prior to SQL Server 2012 we can make use of DATEADD function to find the last day of a month for the provided date. DECLARE @dtTempDate DATETIME SELECT @dtTempDate = GETDATE() In SQL Server 2005 or 2008 these are couple of ways by which we can get the desired --Option1 SELECT DATEADD(DAY, -1, DATEADD (MONTH, MONTH (@dtTempDate) , DATEADD (YEAR,  YEAR (@dtTempDate) - 1900, 0 ))) --Option2 SELECT DATEADD( DAY , -1, DATEADD(MONTH , 1, DATEADD(DAY , 1 - DAY (@dtTempDate), @dtTempDate))) --Option3 SELECT DATEADD(DAY , -1, DATEADD(MONTH, DATEDIFF(MONTH , 0, @dtTempDate) +1, 0)) Now in SQL Server 2012 there is a new Date and Time function named EOMONTH . EOMONTH ( start_date [, month_to_add ] ) Solution using EOMONTH which would work only in SQL Server 2012 and above: SELECT EOMONTH ( @dtTempDate ) AS [Current Month Last Date] --here the second parameter tells how many MONTHS to add to the given input SELECT EOMONTH ( @dtTempDate ,1) AS [Next Month Last...

When you install SQL Server 2012 by default it installs only the Help viewer and its related components. It doesn't install any help documentation locally and user has to go online for checking any reference. But that concept won't work for me as I have installed SQL Server 2012 in a laptop which doesn't have internet connection at all.  Download SQL Server 2012 Product Documentation So I was searching for 'downloadable version of SQL Server 2012 product documentation' and I found it here -  http://www.microsoft.com/download/en/details.aspx?id=347 .  I downloaded  SQLServer2012Documentation.exe which was approx 204 MB and ran the exe which created a folder named "SQLServer2012Documentation" and extracted the following: Folder named "Packages" which had 25 CAB files  (cabinet files) Four HTML document (book1, book2, book3, product1) One MSHA file (HelpContentSetup.msha) So over all 30 items are extracted. Installing the...

At last during the weekend I was able to download and install SQL Server 2012 standard edition in my laptop. Next to get started I was looking for sample database for SQL Server 2012. Using the search string "adventureworks 2012 download" I found the below download link. Adventure Works DB for SQL Server 2012 -  http://msftdbprodsamples.codeplex.com/releases/view/55330 Actually it downloads the DATA file named AdventureWorks2012_Data.mdf and the size when I downloaded was 189 MB . Copy the DATA file to the location where you want it to be stored in your hard drive and then make use of that path in "FILENAME" attribute within CREATE DATABASE syntax. CREATE DATABASE AdventureWorks2012 ON ( FILENAME = ' C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\DATA\AdventureWorks2012_Data.mdf' ) FOR ATTACH_REBUILD_LOG ; Reference: CREATE DATABASE syntax for SQL Server 2012