Vadivel's blog

In SQL Server Management Studio: Navigate to your database > Programmability  Press F7 (or) View > Object Explorer Details In the Object Explorer Details window right click anywhere on the header row and select "Date Last Modified" field (in case it isn't selected already) T-SQL Script to achieve the same result: Option 1: SELECT     name,     create_date,     modify_date FROM sys.procedures WHERE modify_date > '2016-08-09' ORDER BY modify_date DESC ; Option 2: SELECT specific_name, created, last_altered FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_type = N'PROCEDURE' and last_altered > '2016-08-09'  ORDER BY last_altered DESC ;

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...

There are still people who are believing that Foreign Key does create an index automatically by SQL Server. I think since Primary key by default creates a Clustered Index people are assuming that Foreign keys would also create an Index automatically. This is a myth and SQL Server does NOT automatically  create an index on a foreign key columns. But one of the best practices for Index tuning is to Index all the columns which are part of a foreign key relationship. Check out the MSDN documentation  for the sub heading Indexing FOREIGN KEY Constraints. The first line says " Creating an index on a foreign key is often useful .... ". Microsoft wouldn't be saying this if FK is automatically indexed.

Avoid using SCOPE_IDENTITY() and @@IDENTITY functions if your system is using Parallel Plans . Extract from the above link: Posted by Microsoft on 3/18/2008 at 1:10 PM Dave, thanks to your very detailed and dilligent report I was able to find the problem.  Yes, it's a bug - whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistenly and can't be relied upon.  The few workarounds I can offer you for now: Use MAX_DOP=1 as you are already using. This may hurt performance of the SELECT part of your query. Read the value from SELECT part into a set of variables (or single tabel variable) and then insert into the target table with MAX_DOP=1. Since the INSERT plan will not be parallel you will get the right semantic, yet your SELECT will be parallel to achieve performance there if you really need it. Use OUTPUT clause of INSERT to get the value you were looking for, as in the example I give further below. In fact I hi...

If we have an IDENTITY column and if our insert statement is trying to exceed the maximum value of INTEGER then it would throw this error. To know the range for TinyInt, SmallInt, INT, BIGINT check out this MSDN link Lets reproduce the error for an TINYINT column CREATE TABLE dbo.tblIdentityTest ( Sno TINYINT IDENTITY(250,1) --Max limit of TinyInt is 255 ,Firstname VARCHAR(20) ) GO --These records would get inserted INSERT INTO dbo.tblIdentityTest VALUES ('250') INSERT INTO dbo.tblIdentityTest VALUES ('251') INSERT INTO dbo.tblIdentityTest VALUES ('252') INSERT INTO dbo.tblIdentityTest VALUES ('253') INSERT INTO dbo.tblIdentityTest VALUES ('254') INSERT INTO dbo.tblIdentityTest VALUES ('255') GO SELECT * FROM dbo.tblIdentityTest GO --As TINYINT has already reached it max limit any new insertion would fail INSERT INTO dbo.tblIdentityTest VALUES ('This would fail') GO Msg 8115, Level 16, State 1, Lin...

SARG able is the short form of  " S earch ARG ument able". A condition in the SQL Query is said to be SARGable if the database engine can take advantage of an available Indexes and do an INDEX SEEK instead of Table Scan / Index scan to speed up the execution of that query. One of the major mistakes developers do which makes a query non-SARGable is they use functions directly on a column in the WHERE Clause. The next common mistake i have seen is the issues created because of "implicit data type conversions". In this post I would explain those with few examples. --Sample table CREATE TABLE tblSARGTest ( ProductID INT IDENTITY PRIMARY KEY, ProductName VARCHAR(50) NOT NULL, Manufacturing_Date DATETIME NOT NULL ) GO --Non clustered index on Manufacturing Date CREATE NONCLUSTERED INDEX [nc_ix_manufacturing_dt] ON [dbo].[tblSARGTest]  ( [Manufacturing_Date] ASC ) GO --Non clustered index on Product Name CREATE NONCLUSTERED INDEX [nc_ix_productNam...

I would strongly suggest to do this in the front end application (or) make use of CLR based function to do this job. But for simple well formed html string may be we can make use of the new XML datatype introduced in SQL Server 2005 as shown in the below example. CREATE FUNCTION dbo.Strip_WellFormed_HTML (  @inputString VARCHAR(MAX)  )  RETURNS VARCHAR(MAX)  AS BEGIN --Variable Declaration     DECLARE @htmlContent XML     DECLARE @parsedValue VARCHAR(MAX)      --Variable Initialization SET @htmlContent = @inputString;        WITH HTML(InnerText) AS     (         SELECT Html.Tag.query('.') FROM @htmlContent.nodes('/') AS Html(Tag)     )     SELECT @parsedValue = InnerText.value('.', 'VARCHAR(MAX)') FROM HTML     RETURN @parsedValue END GO Pasting of html tags in blogger seems to be a diffic...

GO is not a SQL Statement or SQL Command. It is just a Batch Separator used by SQL Client tools like SQL Server Management Studio, SQL CMD etc., Extract from MSDN "GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO." In SSMS: Though the default Batch Separator is GO we can change it as well! Just go to Tools > Options >Query Execution > SQL Server > General > Batch Separator . Let's change our Batch Separator as "Done". Please note that it would take effect only from the next SQL Query window which we open. We can't use it di...

If you haven't heard about $IDENTITY in SQL Server check out the initial part of the scripts explained below to know what it does. CREATE TABLE IdentityTest (   Sno INT IDENTITY(1,2) PRIMARY KEY, CreatedDate DATETIME ) GO INSERT INTO IdentityTest(CreatedDate)  SELECT Getdate() Go 10 1. How to display the LAST inserted IDENTITY Value into a table in the current scope and session? SELECT SCOPE_IDENTITY() Generally we would use SCOPE_IDENTITY() but since SQL Server 2005 there is a serious bug with this . The safe option to use is OUTPUT clause. Check out this KB article here . 2. What if we don't know the IDENTITY column name but wanted to display its column values?  (OR)  Without mentioning the IDENTITY column name how to list all the IDENTITY values in a table? SELECT $IDENTITY FROM IdentityTest GO I have seen $IDENTITY working since SQL Server 2005 and above. Not very sure whether it used to work in earlier versions as I have never tr...