Vadivel's blog

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

Though "NOT EXISTS" and "NOT IN" sounds similar there is quite a lot of difference between them.  To start with check out the blog post by Mladen . In continuation to what Mladen has already written I thought I would show the differences it makes on the Execution Plan and the IO / Time when we use NOT EXISTS or NOT IN in our queries. Let's see few of the differences between them. Case 1: Lets use them in columns which are declared as NOT NULL SET NOCOUNT ON GO CREATE TABLE PackageInformation ( Sno INT IDENTITY(1,1) PRIMARY KEY, PackageID INT NOT NULL, PackageName VARCHAR(20) ) GO -- I am using the random records generator which I wrote few days back to populate data into this table. -- generating 10000 records INSERT INTO PackageInformation  (PackageID, PackageName) SELECT CAST(RAND(CHECKSUM(NEWID())) * 10000 AS INT), dbo.udf_StringGenerator('A', 20) GO 10000 CREATE TABLE ChildTable ( RID INT IDENTITY(1,1) PR...

Converting UTC format date to Standard time of CST or EST is straight forward. DECLARE @UTC_Date DATETIME SET @UTC_Date = GETUTCDATE() SELECT @UTC_Date AS [UTC], DATEADD(hh, -6, @UTC_Date) AS [CST - Standard Time], DATEADD(hh, -5, @UTC_Date) AS [EST - Standard Time] But if the given date falls under daylight saving then the above calculation won't work. So how is Daylight saving calculated? 1. If the year <= 2006 then daylight saving is between: 2 am on First Sunday in April till 2 am on Last Sunday in October 2. If the year >= 2007 then daylight saving is between: 2 am on Second Sunday in March till 2 am on First Sunday in November 3. UTC to CST (Standard Time) = -6 4. UTC to CDT (Daylight Time) = -5 5. UTC to EST (Standard Time) = -5 6. UTC to EDT (DayLight Time) = -4 Solution - 1: DECLARE @UTC_Date DATETIME SET @UTC_Date = GETUTCDATE() SELECT @UTC_Date AS [UTC], DATEADD(hh, -6, @UTC_Date) AS [CST - Standard Time], DATEADD(hh, CA...

Yesterday I saw a question in a forum asking for help in writing a script to list out all Triggers within a database. They also wanted to see the source code of each trigger. Method 1: Which works in SQL Server 2000, SQL Server 2005 and SQL Server 2008. But the problem with SYSCOMMENTS is it would truncate any text beyond 4000 characters. As the "Text" column which preserves the source code is declared as NVARCHAR(8000). /* Written By: Vadivel Mohanakrishnan Date: Oct 10, 2011 URL: http://vadivel.blogspot.com Purpose:  To list all triggers in the current database along with the actual text/source code. It also lists what of type of trigger it is.  But for all future development we should avoid using SYSOBJECTS, SYSCOMMENTS as it might be removed in the future versions. */ SELECT   SO2.Name AS [Table Name],     SO1.name AS [Trigger Name],   OBJECTPROPERTY( [so1].[id], 'ExecIsUpdateTrigger') AS [isUpdate],     ...

The best product I have seen so far for generating random test records within a SQL Server database is Red Gate's Data Generator software . Its feature list is really impressive. Please be advised it is not a free software but it has a 14 days trial version to try it out. If you are looking for a simple script to generate records for a standalone table may be try out the below scripts. To Generate Random Integer Value: --This will generate an Integer value lesser than 100000  SELECT CAST(RAND(CHECKSUM(NEWID())) * 100000 AS INT) To Generate Random date: --This will create DOB whose age would be between 21 and 60 (21 + 39) SELECT GETDATE() - ((21 * 365) + RAND()* (39 * 365)) To Generate Random Money value: --This will generate Money value (rounded to 2 decimals) between 25000 to 30000  SELECT Round(CAST(25000 + RAND(CHECKSUM(NEWID())) * 5000 AS Money),2) Assume in a company the salary range is between 25000 to 100000. So we can generate random ...