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.

CREATE TABLE Temp1
( Id int IDENTITY(1,1),
Name [nvarchar](max)
)

CREATE TABLE Temp2
( Id int ,
Name [nvarchar](max)
)

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:

INSERT INTO Temp1 VALUES ('A')
INSERT INTO Temp1 VALUES ('B')


INSERT INTO Temp2 VALUES (1,'A')
INSERT INTO Temp2 VALUES (2,'B')

Let us check the identity column for both the tables.



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.



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

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



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.

--Declare Table--
DECLARE @TAB TABLE(
Name_ [nvarchar](max),
Age [int]
)
--Insert Data--
INSERT INTO @TAB VALUES ('Pankaj',21)
INSERT INTO @TAB VALUES ('Sandeep',22)
INSERT INTO @TAB VALUES ('Nitin',23)
INSERT INTO @TAB VALUES ('Rahul',20)
INSERT INTO @TAB VALUES ('Amit',22)
--Select data--
SELECT * FROM @TAB t

Output:



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.

SELECT t.Name_ ,t.Age , ROW_NUMBER() OVER(ORDER BY t.Name_) AS "Rank"
FROM @TAB t

Output:



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.



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.

SELECT t.Name_ ,t.Age , ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS "Rank"
FROM @TAB t

Output:



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:

--Declare Table--
DECLARE @TAB TABLE(
Name_ [nvarchar](max)
) --Insert Data--
INSERT INTO @TAB
VALUES
('Search') INSERT INTO @TAB
VALUES
('SEARCH') INSERT INTO @TAB
VALUES
('SeArCh') INSERT INTO @TAB
VALUES
('sEaRcH') --Select Data--
SELECT
t.Name_
FROM
@TAB t

Output:



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.

SELECT t.Name_ FROM @TAB t
WHERE t.Name_='SEARCH'

Output:



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:

SELECT t.Name_ FROM @TAB t
WHERE t.Name_ COLLATE Latin1_General_CS_AS ='SEARCH'

Output:



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:



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:

CREATE FUNCTION Return_String(@Str [nvarchar](max))
RETURNS [nvarchar](max)
AS
BEGIN
DECLARE @Count int;
SET @Count=0
WHILE @Count<10
BEGIN
SET @Str=REPLACE(@Str,CHAR(49+@Count),'')
SET @Count=@Count+1
END
RETURN @Str
END

Now we use above function for Tab table and remove the Numeric value from Name_ column.



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:

CREATE FUNCTION Return_NUMERIC(
@Str [nvarchar](max)
) RETURNS [nvarchar](max) AS BEGIN DECLARE @Count int;
SET
@Count = 0 WHILE @Count < 26 BEGIN
SET
  @Str = REPLACE(
    @Str,
    CHAR(65 + @Count),
    ''
  )
SET
  @Count = @Count + 1 END RETURN @Str END

Now we use above function for Tab table and remove all the characters from Name_ column.


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.

--Declare Table--
CREATE TABLE TAB(
IID int,
Name_ [nvarchar](max)

)
--Insert Data--
INSERT INTO TAB VALUES (1,'Pankaj')
INSERT INTO TAB VALUES (2,'Sandeep')
INSERT INTO TAB VALUES (3,'Rahul')
INSERT INTO TAB VALUES (4,'Sanjeev')
--Select Data--


SELECT * FROM dbo.TAB t

Tab table looks like the following:


Now we create a stored procedure that will return table.

CREATE PROCEDURE Return_Data
AS
BEGIN
SELECT t.IID,t.Name_ FROM dbo.TAB t
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.

CREATE TABLE TAB1(
IID int,
Name_ [nvarchar](max)
)

Now we insert data into this table.

--Execuet Stored Procedure
INSERT INTO TAB1
EXEC Return_Data
--Select Data
SELECT * FROM dbo.TAB1 t

Output:



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.

--Enable Ad Hoc Query
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

Now we execute Main Query as in the following code snippet:

SELECT
* INTO TAB1
FROM
OPENROWSET(
'SQLNCLI', 'Server=Your_Server_Name;Trusted_Connection=yes;',
'EXEC Demo.dbo.Return_Data'
) -- Select Table
SELECT
*
FROM
TAB1;

Output:


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:

Declare @StringTab TABLE (
Name [nvarchar](max)
) INSERT INTO @StringTab
SELECT
'I AM PANKAJ KUMAR CHOUDHARY'
UNION ALL
SELECT
'I AM IN 4TH YEAR OF GRADUATION'
UNION ALL
SELECT
'I LIVE IN ALWAR'
SELECT
*
FROM
@StringTab st

Table looks like the following:



Now we create a function that will revert the string.

CREATE FUNCTION Reverse_String(
@String VARCHAR(MAX)
) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Str [nvarchar](max);
DECLARE @Index int;
DECLARE @Return_Strint [nvarchar](max);
SET
@Return_Strint = NULL;
SET
@String += ' ' WHILE CHARINDEX(' ', @String) > 0 BEGIN
SET
@Index = CHARINDEX(' ', @String)
SET
@Str = SUBSTRING(@String, 1, @Index -1)+ ' ' + ISNULL(@Str, '')
SET
@Return_Strint = @Str
SET
@String = SUBSTRING(
@String,
@Index + 1,
LEN(@String)
) END RETURN @Str END

Now we use above function to obtain reverse of a string. 

SELECT st.Name ,dbo.Reverse_String(st.Name) AS "Reversed String" FROM @StringTab st

Output:



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.

DECLARE @Table TABLE
(
IID int
)

INSERT INTO @Table
SELECT 1 UNION ALL
SELECT 5 UNION ALL
SELECT 2 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 6 UNION ALL
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:

SELECT TOP 2 t.IID FROM @Table t
ORDER BY t.IID DESC

Execution Plan for query:



Output:



Top Command with Ties

SELECT TOP 2 WITH TIES t.IID FROM @Table t
ORDER BY t.IID DESC

Execution Plan for query:


Output:



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.