*** Various
Usefull Examples ***
------RE-Seed Auto-number in Relational Table
(if TRUNCATE not allowed on table)---
--PRIMARY TABLE
CREATE TABLE masterTbl (
id INT IDENTITY(1,1) CONSTRAINT pk_id PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(max))
--FORIGN TABLE
CREATE TABLE detailTbl (
id INT IDENTITY(1,1) PRIMARY KEY,
m_id INT NOT NULL CONSTRAINT fk_mid FOREIGN KEY REFERENCES masterTbl(id) ON DELETE CASCADE,
details VARCHAR(MAX))
GO
----THIS CODE WILL RE-SEED AND AUTONUBER BEGIN
TO ONE AS LIKE TRUNCATE---
DELETE FROM [dbo].[masterTbl]
DBCC CHECKIDENT ('[dbo].[masterTbl]', RESEED, 1)
GO
-------------------------END------------------------------
IF NOT EXISTS (SELECT 1 FROM [tb1] WHERE [id]=101)
PRINT '101 Not Exist'
ELSE
PRINT '101 Exist'
GO
----------- CTE (Common Table
Expressions)-----------------------
WITH CTE (config_element, rowNum)
AS
(
SELECT config_element,
ROW_NUMBER() OVER(PARTITION BY config_element ORDER BY config_element) AS rowNum
FROM [myDb].[dbo].[temp_configuration]
)
--update cte set
config_element='factoryShortKeys' WHERE rowNum=2
SELECT * from CTE
GO
SELECT MY_NAME = [name] FROM [myDb]..[myTable] --alias
SELECT CAST([id] as FLOAT) FROM [myDb]..[myTable]
SELECT CONVERT (FLOAT, [id]) FROM [myDb]..[myTable]
SELECT CONVERT(VARCHAR, [date], 101) as MY_Date FROM [myDb]..[myTable] ---four
digit yr US format
SELECT CONVERT(VARCHAR, [date], 1) as MY_Date FROM [myDb]..[myTable] ---two
digit yr US format
SELECT CONVERT(VARCHAR, [date], 103) as MY_Date FROM [myDb]..[myTable] ---four
digit yr UK format
SELECT CONVERT(VARCHAR, [date], 3) as MY_Date FROM [myDb]..[myTable] ---two
digit yr UK format
SELECT [date], DATEDIFF(YY, [date], GETDATE()) AS Age FROM [myDb]..[myTable] -------DATE DIFF() GETDATE()
SELECT CASE WHEN [name] = 'Ajay' THEN 'AJAY GARG' ELSE [name] END as [my name] FROM [myDb]..[myTable]
SELECT CASE WHEN [name] = 'Ajay' THEN 'AJAY GARG' WHEN [name]='Vijay' THEN 'VEDA GARG' ELSE [name] END as [my name] FROM [myDb]..[myTable]
SELECT [name] FROM myTable1 WHERE name <> 'Ajay'
SELECT [name] FROM myTable1 WHERE (name<>'Vijay' and name <>'Ajay') or [salary] is null
SELECT [name] FROM myTable1 WHERE Left([name],2)='Aj'
SELECT [name] FROM myTable1 WHERE [name] like '[A-K]%'
SELECT [name] FROM myTable1 WHERE [name] like '[^A-K]%'
SELECT CHARINDEX('i','ANIAC',1)
SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'rk')
SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'"rk*"')
SELECT TOP 1000 [id],[name],[dob] FROM [RKDB].[dbo].[tb1] ORDER BY 1 DESC
SELECT TOP 1 WITH TIES [name],[salary] FROM [RKDB].[dbo].[tb1] ORDER BY salary DESC --salary is highest
SELECT TOP 50 PERCENT [id],[name],[dob],[salary] FROM [RKDB].[dbo].[tb1]
SELECT COUNT(*) FROM [myDb].[dbo].[customer]
SELECT COUNT_BIG(*) FROM [myDb].[dbo].[customer]
SELECT COUNT(distinct [customerId]) FROM [myDb].[dbo].[customer]
SELECT MIN([customerId]) FROM [myDb].[dbo].[customer]
SELECT MAX([customerId]) FROM [myDb].[dbo].[customer]
SELECT SUM([customerId]) FROM [myDb].[dbo].[customer]
SELECT '$' + CAST ( CAST(SUM([customerId]) AS DECIMAL(17,2)) AS VARCHAR) FROM [myDb].[dbo].[customer]
SELECT AVG([customerId]) FROM [myDb].[dbo].[customer]
SELECT STDEV([customerId]) FROM [myDb].[dbo].[customer]
SELECT STDEV(ALL [customerId]) FROM [myDb].[dbo].[customer]
SELECT STDEV(DISTINCT [customerId]) FROM [myDb].[dbo].[customer]
SELECT COUNT([customerId]) AS COUNT,[customerId] into #NewDimProduct FROM [myDb].[dbo].[customer] GROUP BY [customerId]
//------BOTH
are similar query---
SELECT DISTINCT userId FROM TB1 WHERE exists (SELECT projectId FROM TB2 T2 WHERE T2.userId='121')
SELECT DISTINCT userId FROM TB1 WHERE projectId IN (SELECT projectId FROM TB2 T2 WHERE T2.userId='121')
//-------------------
SELECT * FROM TB1 //--UNION TABLE WITH NO
REPEAT ROW
UNION
SELECT * FROM TB2
SELECT * FROM TB1 //--UNION TABLE WITH
REPEAT ROW
UNION ALL
SELECT * FROM TB2
SELECT * FROM TB1 //--SELECT FROM TB1 WHICH
IS NOT IN TB2
EXCEPT
SELECT * FROM TB2
SELECT * FROM TB1 //--SELECT ONLY COMMON ROW
INTERSECT
SELECT * FROM TB2
//------------------------------GUID
as ID by default-------------------
CREATE TABLE [tb1] (guidCol UNIQUEIDENTIFIER DEFAULT newsequentialid() ROWGUIDCOL, name VARCHAR(60))
INSERT INTO [tb1] (name) SELECT 'abc'
INSERT INTO [tb1] (name) SELECT 'xyz'
SELECT $rowguid FROM [tb1]
SELECT guidCol FROM [tb1]
SELECT CURRENT_TIMESTAMP
SELECT DAY(CURRENT_TIMESTAMP)
SELECT MONTH(GETDATE())
SELECT YEAR('2012-04-24')
---DATE DIFFERENCES---
SELECT DATEDIFF(SECOND, GETUTCDATE(), GETDATE()) AS SEC_DIFF --SECOND DIFFERENCE **RESULT 19800
SELECT DATEDIFF(MI, GETUTCDATE(), GETDATE()) AS MIN_DIFF --MIN DIFFERENCE **RESULT 330
SELECT DATEDIFF(YEAR, GETUTCDATE(), GETDATE()) AS YEAR_DIFF --YEAR DIFFERENCE **RESULT 0
SELECT DATEDIFF(YEAR,'2011-04-15', '2012-05-09') AS YEAR_DIFF --YEAR DIFFERENCE **RESULT 1
SELECT DATEADD (MINUTE , 50 , GETDATE()) ADD MINUTE IN DATE
SELECT SIGN(3-7) --RESULT -1
SELECT SIGN(3-1) --RESULT +1
SELECT SIGN(3-3) --RESULT 0
SELECT STUFF ('ABCDEF',2,0,'xyz') --syntax: STUFF( char_expression, start, length,
replaceWith_expression)
SELECT COALESCE([salary],12000) FROM [tb1] -- If
salary then salary else 12000
--CONCATENATE VALUES OF A COLUMN
DECLARE @name nvarchar(max)
SELECT @name = coalesce(@NAME+', ', '')+[name] FROM [tb1]
SELECT @name
SELECT STUFF ((SELECT N', ' + name FROM tb1 FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'')
SELECT STUFF ((SELECT','+ [name] FROM [tb2] FOR XML PATH('')),1,1,'') AS [name_csv_col]
BULK INSERT [tb3] FROM 'c:\a\data.txt' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n') --IMPORT DATA FROM CSV FILE
--GET EXT NAME OF GIVEN FILE NAME
DECLARE @TEXT VARCHAR(50) ='MY_FILE.JPG'
SELECT SUBSTRING(@TEXT, CHARINDEX('.',@TEXT)+1 ,LEN(@TEXT))
--GET LENTH OF DATA IN BYTE
SELECT DATALENGTH(dob) FROM [tb2]
--** CREATE FUNCTION UDF TO EXTRACT INTEGER FROM
STRING **---
CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END
RETURN @IntNumbers
END
GO
SELECT dbo.ExtractInteger('ASDFASDFASDF-AS-DF-ASD-F43QW25-432-534-345-ASDF')
GO
-------**END FUNCTION**----------
--------TABLE VARIABLE DOES NOT KNOW ROLLBACK
UNLIKE TEMP TABLE ----
CREATE TABLE #MY_TEMP (col1 VARCHAR(100)) --CREATE TEMP TABLE
INSERT INTO #MY_TEMP (col1) --INSERT ROW IN TEMP TABLE
VALUES ('TEMP TABLE ----- OUT SIDE TRANSACTION')
DECLARE @TBL_VAR TABLE (col1 VARCHAR(100)) --DECLARE TABLE VARIABLE
INSERT INTO @TBL_VAR (col1) --INSERT INTO TABLE VARIABLE
VALUES('TABLE VARIABLE-------OUT SIDE TRANSACTION')
SELECT [col1] AS tempTable_beforeTrans --CHECK VALUE IN TEMP TABLE
FROM #MY_TEMP
SELECT [col1] as tempVariable_beforeTrans --CHECK VALUE IN TABLE VARIABLE
FROM @TBL_VAR
BEGIN TRAN --BEGIN TRANSCTION
INSERT INTO #MY_TEMP (col1)
VALUES ('TEMP TABLE --------AFTER TRANS') --INSERT INTO TEMP IN
TRANS
INSERT INTO @TBL_VAR (col1)
VALUES ('TABLE VARIABLE -------- AFTER TRANS')-- INSERT INTO TABLE
VARIABLE IN TRANS
ROLLBACK
SELECT [col1] AS tempTable_afterTrans --NOW CHECK VALUE
FROM #MY_TEMP
SELECT [col1] as tempVariable_afterTrans
FROM @TBL_VAR
GO
-----------END DIFF TABLE VARIABLE AND TEMP
TABLE--------
SELECT [create_date] FROM sys.server_principals WHERE sid = 0x010100000000000512000000 --TO GET SQL-SERVER INSTALLATION DATE
---------------------------BEGIN-------------------
--CASE SENSITIVE COLLECTION ON RESULT SET
--Latin1_General_CI_AS (CASE INSENSITIVE), Latin1_General_CS_AS (CASE
SENSITIVE)
CREATE TABLE [MyTable] (
Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS,
Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS) ;
INSERT [MyTable](Col1, Col2)
VALUES ('Hyna','Hyna'),
('hyna','hyna'),
('starfish','starfish'),
('Starfish','Starfish');
GO
-- Retrieve SORTED Data will retrieve case
in-sensitve order
SELECT * FROM [MyTable] ORDER BY Col1
GO
-- Retrieve SORTED Data will retrieve case
sensitve order
SELECT * FROM [MyTable] ORDER BY Col2
GO
//----------------END--------------------------
----Short Key to jump Parenthesis---
SELECT *
FROM (SELECT *
FROM (SELECT *
FROM (SELECT *
FROM myTable)x)y)z
--CTRL + SHIFT + ] --Shortcut to
Select Code Between Two Parenthesis
--CTRL + ] --Shortcut
to Switch Between Two Parenthesis
