Skip to main content

Posts

How to find missing (GAPS) sequence numbers

Few years back I wrote a solution for the same problem here . Recently I read an article by Henning on the similar topic which was interesting and informative. After that, I was wondering why not just make use of a NUMBER table and a NOT IN to do this. I am providing the test script as-is and I haven't compared the performance yet between any other solutions out there :) --Lets set up a sample table and pump in few thousand records with gaps in it SET NOCOUNT ON; GO CREATE TABLE tblFindGaps  (     ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,     [Name] VARCHAR(50) NULL ) GO INSERT INTO tblFindGaps (Name) VALUES ('a') GO 10000 -- Delete some rows to create gaps in the identity column DELETE tblFindGaps WHERE ID IN (23, 64, 3392, 44) DELETE tblFindGaps WHERE ID BETWEEN 5000 AND 6000 GO Solution: Lets create a table to hold numbers from 1 till the MAX available number already present in tblFindGaps table. -- Number t...

How to truncate all tables in a SQL Server database?

1. For those who are adamant to do it via scripts: :) Few years back I have written a t-sql script to do this. I have disabled the constraints before truncating the data and once it is done have enabled it back. But I guess in the latest versions of SQL Server we might need to drop & recreate the constraints (haven't tried it yet). 2. Generate Scripts: If we need to return back to complete empty database with just table schema and other SQL Objects like Stored procedure, trigger, constraints etc., then it would be simple to script out the whole database. That way we can just drop and recreate the database anytime using those scripts. 3. Backup/Restore: If we need to achieve what's mentioned in point 2 and along with that if we need few tables to have the master entries preserved so that our application can work properly! then why not take a Full backup of the database once everything is set in it. So that when ever we want to revert back to that state we can re...

TOP 100 PERCENT doesn't work within a VIEW

Way back in 2003, I wrote a blog post titled " Is sorting possible in views? ". I came to know that by using TOP 100 PERCENT in our SELECT query we can make use of ORDER BY clause within a VIEW. I tested that out in a SQL Server 2000 machine. But till date I have never used that in any of the real project I have worked on :) Recently I was trying out in a SQL Server 2005 machine using AdventureWorks database. I thought would make a blog post of that experience so that it might help someone someday! USE AdventureWorks GO --Attempt 1:  CREATE VIEW vw_OrderedAddress AS  SELECT  AddressID, AddressLine1,  AddressLine2, City,  StateProvinceID, PostalCode,  rowguid, ModifiedDate FROM Person.Address ORDER BY  AddressID DESC If we try creating the above view it would throw this error: Msg 1033, Level 15, State 1, Procedure vw_OrderedAddress, Line 12 The ORDER BY clause is invalid in views, inline functions, derived tables...

How to SPLIT a delimited string values in SQL Server 2005

There are many ways by which we can SPLIT delimited string in SQL Server. Few years back I have already done couple of posts regarding this. Let me link those here for quick reference: 1. SPLIT function in SQL Server - Method 1 2. SPLIT function in SQL Server - Method 2 In SQL Server 2005 there are interesting XML enhancements done. So for past few years I have been using this XML trick to parse the comma separated strings or any delimited strings into a table. CREATE FUNCTION [dbo].[SplitUsingXML]  (  @String VARCHAR(MAX), --If your delimiter is multi character --then change it as VARCHAR with appropriate length  @Delimiter CHAR(1)  ) RETURNS @Results TABLE ( parsedValue VARCHAR(MAX) )    AS BEGIN     DECLARE @xml XML         --Below line seems to get screwed up once i post it in Blogger :(     --please use the line as shown in the attached image. Sorry about ...

How to ask questions in Forums?

You have to provide as much information as possible in your question for somebody to provide you a valid answer! If proper information is not provided then even the simplest of question would suddenly start looking very hard for the readers. Assume of a question like this in one of our India based forums. "What is the shortest route to reach Tidel park" So is this an easier question or a tougher question? How do the readers know from where are you starting? How would they know whether you wanted to take a public transport or you have a private vehicle so you are expecting a short cut route? Please remember the readers can just read the question written there and cannot read your mind as well. The same question if it has been asked something like this: "I am planning to travel by two wheeler and need to know what is the shortest route to reach Tidel park, Chennai from Tambaram railway station, Chennai?"  What do you think of the below question asked in a f...

Does Clustered Index physically orders the data within a table?

Quite a lot of time I have come across people saying "Clustered Index Physically sorts the data inside the table based on the Clustered Index Keys". It's not completely true! The short answer to them is there is something called "Row offset Array" in a PAGE which manages the order of rows in a PAGE. For a long answer with a sample script read on... There can lot of rows inserted one after the other and as we know each row can vary in size. That being the case how does SQL Server know where a row begins and where does it end? That's where ROW OFFSET table/array comes into picture. A ROW OFFSET array starts at the end of the PAGE, and it contains one entry for each row on the PAGE. Each entry records how far the first Byte of the row is from the start of the PAGE. The entries in the row offset table are in reverse sequence from the sequence of the rows on the PAGE. CREATE TABLE ci_order_example ( Sno INT NOT NULL PRIMARY KEY, Fname VARCHAR(20...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

1. If you haven't yet read the Ultimate Guide to Datetime Datatypes I would strongly suggest you to do it. 2. Understand that within SQL Server we can store DATETIME values between 01/01/1753 and 12/31/9999 only. If we try to enter a value which isn't this range then it would throw an error. 3. The way you handle dates will depend on the DATEFORMAT set for your login. Internally DATEFORMAT takes its value from SET LANGUAGE. /* This code block would throw an error The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. */ SET DATEFORMAT MDY GO DECLARE @dt VARCHAR(20) --Format is Month Day Year. But we are passing 13 as month so it is expected to throw the error SET @dt = '13/09/10 18:52:12' SELECT CONVERT(DATETIME, @DT) GO --Now it will work! SET DATEFORMAT DMY GO Declare @dt VARCHAR(20) SET @dt = '13/09/10 18:52:12' SELECT CONVERT(DATETIME, @DT)

Difference between two dates in SQL Server

If we want to get the difference between two dates in SQL Server then we need to make use of DATEDIFF function. Showed few variations using DATEDIFF in my forum response here . So thought of logging in my blog as well for future reference :) DECLARE @StartDate DATETIME SET @StartDate = '2011-01-01 10:15:00.000' Declare @EndDate DATETIME SET @EndDate = '2011-01-01 11:35:00.000' --To get only Hours SELECT DATEDIFF(hh, @StartDate,@EndDate ) AS [Hours]; --To get the result in Minutes SELECT CAST(DATEDIFF(ss, @StartDate, @EndDate) AS DECIMAL(10, 0)) / 60 AS [Minutes]; --To get both Hours and Minutes SELECT DATEDIFF(hh, @StartDate, @EndDate) AS [Hours], DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @StartDate, @EndDate),@StartDate),@EndDate) AS [Minutes];

Script ALL SQL JOBs from SQL Server Management Studio

How to script all the SQL Jobs from SQL Server Management studio? Step 1: Go to SQL Server Mgmt Studio. Step 2: Expand SQL Server Agent >> Jobs Step 3: Hit F7 (or) goto menu View >> Summary Step 4: Now ALL jobs would be listed out there. Step 5: Make use of Control key and choose all the JOBs you want to script. (OR) If you want to script everything press Ctrl + A Step 6: Right click >> "Script Job as" >> "Create To" >> File Now you can make use of this file to create these jobs in your destination SQL Server Database.

Calculating PERCENTILE in SQL Server

How to find 25th percentile or 75th percentile of given set of numbers (records in table) in SQL Server 2005? In this post I will show couple of ways of calculating Percentile. I am not a statistician so i just took the help of YouTube to learn on how Percentile is actually calculated. During that course I found two different formulas for calculating Percentile! So I have provided couple of methods to calculate Percentile based on both the formulas I learnt. IF  EXISTS (SELECT * FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[tblPercentile]') AND type in (N'U')) DROP TABLE [dbo].[tblPercentile] GO CREATE TABLE tblPercentile ( Sno INT identity(1,1), iValue float ) GO --First set of sample records Insert into tblPercentile SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 5 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 8 UNION A...

Find count of each character within a word / string using SQL Server 2005+

There was a question yesterday in a forum asking how to find each character count within a word. i.e., We need to count number of occurrences of each character within the given string. For example : If the given string is 'Amala Paul' the result should be like this: Characters, Total Occurance A, 4 I, 2 m, 1 P, 1 u, 1 I gave a CTE Based Solution which would work on SQL Server 2005+ to achieve this. Find below the solution which I had provided. CTE Based Solution DECLARE @tblCountChar TABLE  ( strString VARCHAR(20)  ) INSERT INTO @tblCountChar VALUES('Amala Paul') ;WITH CharacterOccurrance AS  (    SELECT SUBSTRING(strString, 1, 1) AS Characters,           STUFF(strString, 1, 1, '') AS ProcessedString,           1 AS RunningNumber    FROM @tblCountChar    UNION ALL    SELECT SUBSTRING(ProcessedString, 1, 1) AS Characters,  ...

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul...

How to add or modify IDENTITY property to an existing column in SQL Server?

Method 1 : If there is no data in the table or column then use this method. CREATE TABLE tblIdentityCheck (    SerialNo INT,    FirstName VARCHAR(10) ) GO ALTER TABLE tblIdentityCheck DROP COLUMN SerialNo GO --By default, IDENTITY will start with 1 and increment by 1 Alter Table tblIdentityCheck Add SerialNo INT IDENTITY GO Method 2 : If there is data in the table then create a new table with IDENTITY column in it. Move the data from old table to new table. 1. Create a new table with identity column 2. Enable IDENTITY_INSERT for this new table 3. Move the data from this old table to this new table. 4. Disable IDENTITY_INSERT for this new table 5. Delete the old table. 6. Rename the newtable with the oldtable name. Let me explain the above steps with an example. Assume the table already had these records in it. INSERT INTO tblIdentityCheck VALUES (1, 'Vadivel') GO INSERT INTO tblIdentityCheck VALUES (10, 'Robo') GO Now once we make...

ISNULL would take the datatype and length of the parameter

In continuation to my earlier post about ISNULL and COALESCE  I thought would write one of the basic difference between those two. DECLARE @strTest VARCHAR(10) SET @strTest = NULL --it would return only 'Enter some' SELECT ISNULL(@strTest, 'Enter some value') In this case, ISNULL would return only any output which is lesser or equal to 10 characters because the variable used as its first parameter is defined as VARCHAR(10) --it would return the complete string! SELECT COALESCE(@strTest, 'Enter some value')

UPSERT (Insert and Update) in SQL Server 2005

In SQL Server 2008 using MERGE concept we can achieve INSERT and UPDATE in a single statement. Many times we would have faced a scenario like if the key is present in the table then update that record. If the key is not present in the table then insert that as a new record. To do this we normally have to SELECT the record and see whether it EXISTS or not to decide whether to INSERT or UPDATE. The better way which I learnt recently is to make use of @@ROWCOUNT and do this with ease. Table Structure: CREATE TABLE tblUpsert ( EmpID INT PRIMARY KEY, firstname VARCHAR(25), lastname VARCHAR(25), emailid VARCHAR(50) ) GO Normal method: /*   This SP would work unless it isn't used in multiple threads simultaneously :)   Same SP is used to INSERT as well as UPDATE a table. */ CREATE PROCEDURE uspUPSert ( @empID INT, @fname VARCHAR(25), @lname VARCHAR(25), @emailid VARCHAR(50) ) AS BEGIN SET NOCOUNT ON IF EXISTS (SELECT 1 FRO...

How to convert Roman numerals to Integer in C#

I was looking up the web all day but couldn't find any C# code for converting Roman letters or numerals to Integer value. The best thing i came across was this code by andyb  but the problem was it has been written in PHP which I had no clue :) So I had just taken the logic of AndyB and converted it into C# for anybody who cares for it. Add this Namespace: using System.Collections.Generic; Solution: Dictionary RomanNumbers =   new Dictionary (); RomanNumbers.Add("M", 1000); RomanNumbers.Add("CM", 900); RomanNumbers.Add("D", 500); RomanNumbers.Add("CD", 400); RomanNumbers.Add("C", 100); RomanNumbers.Add("XC", 90); RomanNumbers.Add("L", 50); RomanNumbers.Add("XL", 40); RomanNumbers.Add("X", 10); RomanNumbers.Add("IX", 9); RomanNumbers.Add("V", 5); RomanNumbers.Add("IV", 4); RomanNumbers.Add("I", 1); string strRomanValue = "M...

Convert Datetime into YYYYMMDDHHMMSS format

Converting Datatime value in YYYYMMDDHHMMSS format is not that complex as it sounds :) SELECT CONVERT(VARCHAR(10),GETDATE(),112) --Date alone SELECT CONVERT(VARCHAR(10),GETDATE(),108) - Time alone The only problem is in the Time portion we would get ":" separator within HH:MM:SS. So we need to just replace colon with space and concatenate both this result to get an output in the expected format. Solution: SELECT     CONVERT(VARCHAR(10),GETDATE(),112) +     REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','')            AS [YYYYMMDDHHMMSS]

How many times a character (string) is repeated within a string

I am assuming in interviews this question "How to find the number of times a character or string is repeated within a string" seems to be asked quite often. May be that's why it is one of the popular question in forums :) Normally people respond with a .NET based solution so i thought I would log a SQL Server based solution for this questions. --Variable Declaration DECLARE @ strColumn VARCHAR (20) DECLARE @ searchString    VARCHAR(10) DECLARE @ intLen INT --Variable Initialization SET @ strColumn = '123asd123asd123'  SET @ searchString ='123' SET @ intLen = LEN(@ searchString ) --Solution SELECT  ( LEN(@ strColumn ) -  LEN(REPLACE(@ strColumn , @ searchString , '')) )/@ intLen

ISNULL, COALESCE works with both INT and VARCHAR column

There was a question saying ISNULL won't work for VARCHAR columns. I thought I would provide a very basic sample where ISNULL works with string column as well. --Sample table with a INT and a VARCHAR column Create table tbltest ( intSno int, strName Varchar(10) ) GO --Key-in NULL for both columns Insert into TblTest Values (null, null) GO Sample queries: Select isnull(intSno, 0), isnull(strName, 0) from tblTest GO SELECT COALESCE(intSno, 0), COALESCE(strName, 0) from tblTest GO --ISNULL on a varchar column replacing with a non numeric value SELECT ISNULL(strName, '/0') FROM tblTest GO --Coalesce on a varchar column SELECT COALESCE(strName, '/0') FROM tblTest GO

Easiest way to export query result to Excel or CSV using SQL Server

If we want to export the result of an ad-hoc query or Stored procedure from SQL Server Management Studio without writing any scripts then check this out. Option1:: One of the easiest option if you are using SQL Server 2005 or above is to make use of the output format specifier in SQL Server Management Studio. Step1: Open SQL Server Management Studio. Step2: Click on New Query Step3: Goto Menu Query >> Query Options >> Within Results Choose Text Step4: On the right window you can see an option called "Output Format". Select "Comma Delimited". Step5: Next either Press Control + T (to select Results to Text option). Or goto Menu Query >> Results To >> Results to Text option. Now you are all set. Just run your SELECT statement or SP with the SELECT statement to see the result as Comma Separated Values on the screen. You can copy and save it as a file now. Option2:: You don't have to change any formats for this option. You just execute yo...