SQL Basic Queries

These queries are not related to any specific topic. Each query used in some specific conditions to perform a specific task. I promise that this article will built some good concepts.

This is my second article on “SQL Basic Query”. If you didn't read my first article then read that article using the following link. Basic SQL Query Part1.

Now we start today’s session.

Find Identity Column of Table

Sometime we need to find out the identity column of a table. In such a case we can use “$identity” option to find out the identity column of a table.

Let us take an example.

First we create two tables.

  1. CREATE TABLE Temp1  
  2. ( Id int IDENTITY(1,1),  
  3. Name [nvarchar](max)  
  4. )  
  5.   
  6. CREATE TABLE Temp2  
  7. ( Id int ,  
  8. Name [nvarchar](max)  
  9. )  
We can see that table Temp1 contains an identity column but table Temp2 doesn’t contain any identity column.

Now insert data into both the tables:
  1. INSERT INTO Temp1 VALUES ('A')  
  2. INSERT INTO Temp1 VALUES ('B')  
  3.   
  4.   
  5. INSERT INTO Temp2 VALUES (1,'A')  
  6. INSERT INTO Temp2 VALUES (2,'B')  
Let us check the identity column for both the tables.

query

We can see that in the above query SQL server returns the identity column for Temp1 table.

This query returns column name with data. We can retrieve only column name without it’s data using the following query.

column name

Above query only return the name of identity column without data.

Now we find identity column for Temp2 table. Let us check.

identity

When we execute above command then you will find an error because “$IDENTITY” command throws an error if table doesn’t contain any identity column.

You can find more about Identity and it’s properties here. Identity in SQL Server.

Generate Row Number Without Effecting Ordering of Column

Sometime we required to generate unique serial number for a given record set. SQL Server provide various type of Rank function to generate the serial number such as RANK,ROW_NUMBER,DASH_RANK and NTILE.

But when we use above rank function then we must supply the “ORDER BY” column (s) name. On behalf of this column(s) name SQL Server sort the record and provide a serial number to each record.

Let us take an example.
  1. --Declare Table--  
  2. DECLARE @TAB TABLE(  
  3. Name_ [nvarchar](max),  
  4. Age [int]  
  5. )  
  6. --Insert Data--  
  7. INSERT INTO @TAB VALUES ('Pankaj',21)  
  8. INSERT INTO @TAB VALUES ('Sandeep',22)  
  9. INSERT INTO @TAB VALUES ('Nitin',23)  
  10. INSERT INTO @TAB VALUES ('Rahul',20)  
  11. INSERT INTO @TAB VALUES ('Amit',22)  
  12. --Select data--  
  13. SELECT * FROM @TAB t  
Output:

data

Now we want to create a unique serial number of this table, for this we can use any Rank function, we take ROW_NUMBER. Let us try to generate a unique serial number.
  1. SELECT t.Name_ ,t.Age , ROW_NUMBER() OVER(ORDER BY t.Name_) AS "Rank"  
  2. FROM @TAB t  
Output:

Rank function

We can see that a rank has been generated for each record but order of data has been changed. What can we do to maintain the original order of data?, can we neglect the “ORDER BY” clause? Let us try.

record

We can see that we can’t neglect “Order By” clause. But we can solve this problem by using ORDER BY clause with any literal value.
  1. SELECT t.Name_ ,t.Age , ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS "Rank"  
  2. FROM @TAB t  
Output:

clause

We can see that we generate a Rank for each record without effecting the original order of data.

Case Sensitive Search SQL Query

Let us take an example:
  1. --Declare Table--  
  2. DECLARE @TAB TABLE(  
  3. Name_ [nvarchar](max)  
  4. --Insert Data--  
  5. INSERT INTO @TAB  
  6. VALUES  
  7. ('Search'INSERT INTO @TAB  
  8. VALUES  
  9. ('SEARCH'INSERT INTO @TAB  
  10. VALUES  
  11. ('SeArCh'INSERT INTO @TAB  
  12. VALUES  
  13. ('sEaRcH'--Select Data--  
  14. SELECT  
  15. t.Name_  
  16. FROM  
  17. @TAB t  
Output:

VALUES

We can see that the Table contains single word many times, but case of each word is different. Now we want to search record from table where Name_ is “SEARCH”. Let us try.
  1. SELECT t.Name_ FROM @TAB t  
  2. WHERE t.Name_='SEARCH'  
Output:

Table contain

Above query returns all the records from the table where Name_ is “SEARCH” without checking the case. To make a query case sensitive we need to change the collation of query. Default collation of SQL Server is “SQL_Latin1_General_CP1_CI_AS” that is select at the time of Installation. We add the “Latin1_General_CS_AS” collation to make query case sensitive. Query is the following:
  1. SELECT t.Name_ FROM @TAB t   
  2. WHERE t.Name_ COLLATE Latin1_General_CS_AS ='SEARCH'  
Output:

SEARCH

Remove Numeric Value From String

Suppose we have a table Tab, this table contains a column Name_ , the data type of this column is nvarchar. Name_ column contain string that is combination of Numeric and Characters. Now we want to retrieve only characters from each column value. Tab table contains the following data:

Numeric Value

We can see that the value of Name_ column is a combination of characters and numeric data type. Now we create a function that will remove the numeric value from each string and we use this function in select command.

Here's the function:
  1. CREATE FUNCTION Return_String(@Str [nvarchar](max))  
  2. RETURNS [nvarchar](max)  
  3. AS   
  4. BEGIN  
  5. DECLARE @Count int;  
  6. SET @Count=0  
  7. WHILE @Count<10  
  8. BEGIN  
  9. SET @Str=REPLACE(@Str,CHAR(49+@Count),'')  
  10. SET @Count=@Count+1  
  11. END  
  12. RETURN @Str  
  13. END  
Now we use above function for Tab table and remove the Numeric value from Name_ column.

Tab table

Remove All Characters From String

This is similar to previous method but in this query we remove the characters from string. We use same Tab table for this example.

Here's the function to remove all characters from string:
  1. CREATE FUNCTION Return_NUMERIC(  
  2. @Str [nvarchar](max)  
  3. RETURNS [nvarchar](maxAS BEGIN DECLARE @Count int;  
  4. SET  
  5. @Count = 0 WHILE @Count < 26 BEGIN   
  6. SET   
  7.   @Str = REPLACE(  
  8.     @Str,   
  9.     CHAR(65 + @Count),   
  10.     ''  
  11.   )   
  12. SET   
  13.   @Count = @Count + 1 END RETURN @Str END  
Now we use above function for Tab table and remove all the characters from Name_ column.

characters
Insert Data from Stored procedure to Table

Sometime we required to insert the result of stored procedure in a table. We have two method to perform this task.

First of all we create a table.
  1. --Declare Table--  
  2. CREATE TABLE TAB(  
  3. IID int,  
  4. Name_ [nvarchar](max)  
  5.   
  6. )  
  7. --Insert Data--  
  8. INSERT INTO TAB VALUES (1,'Pankaj')  
  9. INSERT INTO TAB VALUES (2,'Sandeep')  
  10. INSERT INTO TAB VALUES (3,'Rahul')  
  11. INSERT INTO TAB VALUES (4,'Sanjeev')  
  12. --Select Data--  
  13.   
  14.   
  15. SELECT * FROM dbo.TAB t  
Tab table looks like the following:

table
Now we create a stored procedure that will return table.
  1. CREATE PROCEDURE Return_Data  
  2. AS   
  3. BEGIN  
  4. SELECT t.IID,t.Name_ FROM dbo.TAB t  
  5. END  
Now we retrieve data from Return_Data stored procedure and insert in a Table.

Method 1: When Table Already Exist.

This method is useful when a table already exist and we know the schema of resultant retrieved from stored procedure.

Example:

First we create another table similar as TAB table.
  1. CREATE TABLE TAB1(  
  2. IID int,  
  3. Name_ [nvarchar](max)  
  4. )  
Now we insert data into this table.
  1. --Execuet Stored Procedure  
  2. INSERT INTO TAB1  
  3. EXEC Return_Data  
  4. --Select Data  
  5. SELECT * FROM dbo.TAB1 t  
Output:

dbo

In above method we execute the stored procedure and insert the result in TAB1 table.

This method is useful if we already know the schema of stored procedure return. But if we don’t know the schema of stored procedure result or schema is dynamic then we can’t use this method. For such a condition we can use the following method.

Method 2:
Create Table RunTime.

In this method we create a table run time and insert the result of stored procedure in table, for this we use the “OPENROWSET” method.

Let us take an example.

First of all we need to enable the ad hoc query distribution in SQL Server. For this execute the following query first.
  1. --Enable Ad Hoc Query  
  2. sp_configure 'Show Advanced Options', 1  
  3. GO  
  4. RECONFIGURE  
  5. GO  
  6. sp_configure 'Ad Hoc Distributed Queries', 1  
  7. GO  
  8. RECONFIGURE  
  9. GO  
Now we execute Main Query as in the following code snippet:
  1. SELECT  
  2. INTO TAB1  
  3. FROM  
  4. OPENROWSET(  
  5. 'SQLNCLI''Server=Your_Server_Name;Trusted_Connection=yes;',  
  6. 'EXEC Demo.dbo.Return_Data'  
  7. -- Select Table  
  8. SELECT  
  9. *  
  10. FROM  
  11. TAB1;  
Output:

Create Table RunTime
Reverse String Word By Word

In this query I will explain how to reverse a string word by word like if the string is “Pankaj Kumar Choudhary” then it should be reversed as “Choudhary Kumar Pankaj”.

First we create a table and insert some data into this table:
  1. Declare @StringTab TABLE (  
  2. Name [nvarchar](max)  
  3. INSERT INTO @StringTab  
  4. SELECT  
  5. 'I AM PANKAJ KUMAR CHOUDHARY'  
  6. UNION ALL  
  7. SELECT  
  8. 'I AM IN 4TH YEAR OF GRADUATION'  
  9. UNION ALL  
  10. SELECT  
  11. 'I LIVE IN ALWAR'  
  12. SELECT  
  13. *  
  14. FROM  
  15. @StringTab st  
Table looks like the following:

name

Now we create a function that will revert the string.
  1. CREATE FUNCTION Reverse_String(  
  2. @String VARCHAR(MAX)  
  3. RETURNS VARCHAR(MAXAS BEGIN DECLARE @Str [nvarchar](max);  
  4. DECLARE @Index int;  
  5. DECLARE @Return_Strint [nvarchar](max);  
  6. SET  
  7. @Return_Strint = NULL;  
  8. SET  
  9. @String += ' ' WHILE CHARINDEX(' ', @String) > 0 BEGIN  
  10. SET  
  11. @Index = CHARINDEX(' ', @String)  
  12. SET  
  13. @Str = SUBSTRING(@String, 1, @Index -1)+ ' ' + ISNULL(@Str, '')  
  14. SET  
  15. @Return_Strint = @Str  
  16. SET  
  17. @String = SUBSTRING(  
  18. @String,  
  19. @Index + 1,  
  20. LEN(@String)  
  21. END RETURN @Str END  
Now we use above function to obtain reverse of a string. 
  1. SELECT st.Name ,dbo.Reverse_String(st.NameAS "Reversed String" FROM @StringTab st  
Output:

revert the string

Top Command With Ties

Suppose we want to find top two result from a table. If we use top command for a table then SQL server return top two record from table according to sorting order. But if we use top command with Ties, then SQL Server returns the top two result and also returns all the record that contain value equal to last row. For better practice we take two example one for Top command and second for Top command with Ties and find out the difference between the result of both the commands.

Let us take an example.

Firstly, we create a table and insert some data in that table.
  1. DECLARE @Table TABLE  
  2. (  
  3. IID int  
  4. )  
  5.   
  6. INSERT INTO @Table  
  7. SELECT 1 UNION ALL  
  8. SELECT 5 UNION ALL  
  9. SELECT 2 UNION ALL  
  10. SELECT 1 UNION ALL  
  11. SELECT 3 UNION ALL  
  12. SELECT 3 UNION ALL  
  13. SELECT 5 UNION ALL  
  14. SELECT 6 UNION ALL  
  15. SELECT 7 UNION ALL  
  16. SELECT 6 UNION ALL  
  17. SELECT 1  
Top Command without Ties

If we use Top command to select the Top two record from table then result will be the following:
  1. SELECT TOP 2 t.IID FROM @Table t  
  2. ORDER BY t.IID DESC  
Execution Plan for query:

Execution

Output:

id

Top Command with Ties
  1. SELECT TOP 2 WITH TIES t.IID FROM @Table t  
  2. ORDER BY t.IID DESC  
Execution Plan for query:

Output
Output:

result

We can see that Top command with Ties contain more record compare to Top command without Ties. Because top command without ties for a table  only return record equal to number that is provided with Top command but record obtained by Top command with ties  also contain that record which have value equal to last row.

Today we read some basic queries, I hope you enjoyed today’s session. I request that if you have a better solution for any of the above query then please share that, it will very helpful.

Thank you for reading the article.