SQL

I am going to share some basic and advance SQL Query that is generally used by you in day to day development retune so please see following important sql query-
1.
--To know about total tables count
SELECT COUNT(*) AS TablesCount FROM sys.tables
2.
--To know about total procedures count
SELECT COUNT(*) AS TotalCount FROM sys.procedures
3.
--To know about total views in same db
SELECT COUNT(*) AS TotalCount FROM sys.views
4.
--To Know result of following query
SELECT CASE WHEN NULL = NULL THEN 'Yup' ELSE 'Nope' END AS Result;
5.
--To check null column if null then pass defual value
SELECT ISNULL(columnName,'NA') FROM tableName
6.
--RANK() v/s DENSE_RANK()
SELECT RANK() OVER (ORDER BY columnName) AS ROW_RANK FROM  tableName
-- return rank with gap if same record e.g. 22,22,34,45 then return row value i.e.- 1,1,3,4
7.
SELECT DENSE_RANK() OVER (ORDER BY columnName) AS ROW_DENSE_RANK FROM tableName
-- return rank with any gap if same record e.g. 22,22,34,45 then return row value i.e.- 1,1,2,4
8.
--To get top ten records form starting
SELECT DISTINCT TOP 10 columnName FROM tableName
ORDER BY columnName DESC
9.
--To get only % from a column in the table
SELECT * FROM tableName WHERE columnName like '%[%]%'
10.
--To know current datetime of sql server system
Select CURRENT_TIMESTAMP
SELECT GETDATE()
SELECT SYSDATETIME()
11.
--Return dirty data, including updated in isolation transaction data
SELECT * FROM tableName WITH(NOLOCK)
12.
--Return data that are not locked by any trasaction
SELECT * FROM tableName WITH(READPAST)
13.
--If set following query in the starting of procedure then we get dirty data from all the tables
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
14.
--If set following query in the starting of procedure then we get commited data from all the tables
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
15.
--Insert records into table using direct proc
GO
CREATE PROC procInsertRecords
AS
SET NOCOUNT ON
SELECT 'A' AS Col1,123 AS Col2

CREATE TABLE #TEMP (
DATABASE_NAME VARCHAR(128),
DATABASE_SIZE INT);

--Insert the output of stored procedures into temp table
INSERT INTO #TEMP EXEC procInsertRecords;

--Return Data From temp table
SELECT * FROM #TEMP;

-- CLEAN UP
DROP TABLE #TEMP;
--End insert records into table using direct proc
16.
--To stop t-sql running query for five seconds
 WAITFOR DELAY '00:00:05'
17.
--This option specifies the setting for ANSI NULL comparisons. When this is on then if any query that compares a value with a null returns a 0 record. When off, any query that compares a value with a null returns a null value.
SET ANSI_NULLS OFF
SELECT * FROM tableName WHERE columnName = NULL
18.
--This options specifies the setting for usage of double quotation. When this is on then double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.
 SET QUOTED_IDENTIFIER ON
 CREATE TABLE "tableName"(Id INT,Name VARCHAR(10))
 --above table successfully created if QUOTED_IDENTIFIER is on and in case of off then getting error.
19.
--If it ON then No value return in message of sql outbox
SET NOCOUNT OFF
INSERT INTO tableName VALUES ('BizTalkLive',NULL)
20.
--When used it in starting of procedure as SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
SET XACT_ABORT ON
21.
--GET all data of last 5th all rows from table
SELECT TOP 1 * FROM tableName
WHERE columnId IN (SELECT TOP 5 columnId FROM tableName ORDER BY columnId DESC)
22.
--Difference between charindex and patindex:  Patindex - working with only Wildcard "%" to get integer value of first match case while in charindex we can no include wildcard "%" only "'" and resutl is same in both case.
SELECT PATINDEX('%ter%', 'interesting data'); 
SELECT CHARINDEX('ter', 'interesting data');
23.
--STUFF function
SELECT STUFF('BizTalkJitendra', 8, 8, 'Live')
--Result: BizTalkLive :: explanation: insert second string value Live at 8th position and delete 8 values from first string i.e. J

!! Keep Visiting BizTalkLive !!