select * from dbo.
d
OUTPUT:
PersonType Title FirstName LastName Suffix EmailPromotion HireDate
EM Ken Sánchez 0
02/08/2003
EM Terri Duffy 1
02/24/2002
EM Gary Altman III 0
12/27/2003
EM Rob Walters 0
12/29/2001
EM Ms. Gail Erickson 0
01/30/2002
EM Mr. Jossef Goldberg 0
02/17/2002
--Determine if object exists:
/*OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
object_name' [ ,'object_type' ] )
Object_types:
Object type:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
U = Table (user-defined)
V = View
EC = Edge constraint
Applies to: SQL Server 2012 (11.x) and later.
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
UQ = UNIQUE constraint
X = Extended stored procedure
Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, Azure Synapse Analytics,
Parallel Data Warehouse.
ET = External Table*/
IF OBJECT_ID('dbo.d', 'U') IS NOT NULL
print 'dbo.d table exists'
IF OBJECT_ID('dbo.EMP2', 'U') IS NOT NULL
print 'dbo.EMP2 table exists'
else
print 'dbo.EMP2 table does not exists'
--add Check Constraint
/*2.
https://www.w3schools.com/sql/sql_constraints.asp
SQL Server contains the following 6 types of constraints:
Not Null Constraint
Check Constraint
Default Constraint
Unique Constraint
Primary Constraint
Foreign Constraint*/
ALTER TABLE dbo.d
ADD CONSTRAINT CHK_dbo_d_EmailPromotion
CHECK (EmailPromotion <= 2)
/*T-SQL Processing Order
From
Where
Group by
Having
Select
Order By*/
Select FirstName,PersonType,Count(*) from dbo.d
where EmailPromotion ='0'
Group by FirstName,PersonType
Having FirstName Like '%e%'
--Window Function
SELECT
FirstName,
YEAR(HireDate) yearno,
ROW_NUMBER() OVER ( PARTITION BY YEAR(HireDate) ORDER BY FirstName) AS rownum
--SUM(subtotal) OVER (PARTITION BY PurchoseOrderID) AS purchaseOrderTotal
FROM dbo.d
Order by yearno
SELECT
FirstName,
YEAR(HireDate) yearno,
ROW_NUMBER() OVER ( ORDER BY YEAR(HireDate)) AS rownum,
Rank() OVER ( ORDER BY YEAR(HireDate)) AS Rankno,
dense_Rank() OVER (ORDER BY YEAR(HireDate)) AS denseRankno,
NTILE(5) OVER ( ORDER BY YEAR(HireDate)) AS rownum
FROM dbo.d
Order by yearno
OUTPUT:
FirstName yearno rownum Rankno denseRankno rownum
Rob 2001 1 1 1 1
Gail 2002 2 2 2 1
Jossef 2002 3 2 2 2
Terri 2002 4 2 2 3
Gary 2003 5 5 3 4
Ken 2003 6 5 3 5
--Casting
SELECT CAST(10.6496 AS INT) as trunc1,
CAST(-10.6496 AS INT) as trunc2,
CAST(10.6446 AS NUMERIC) as round1,
CAST(10.6456 AS NUMERIC(4,2)) as round2,
CAST(10.6466 AS NUMERIC(4,2)) as round3,
CAST(10.6446 AS NUMERIC(4,2)) as round4,
CAST(-10.6496 AS NUMERIC) as round5;
OUTPUT:
trunc1 trunc2 round1 round2 round3 round4 round5
10 -10 11 10.65 10.65 10.64 -11
--Case Expressions
Declare @printvalue Varchar(255)
set @printvalue =
CASE
WHEN OBJECT_ID('dbo.EMP1', 'U') IS NOT NULL THEN 'dbo.EMP1 table exists'
WHEN OBJECT_ID('dbo.d', 'U') IS NOT NULL THEN 'dbo.d table exists'
ELSE 'dbo.EMP2 table does not exists'
END
Print @printvalue
Output:
dbo.EMP1 table exists
------------------------------------------------------
select Title =
CASE
WHEN Title = '' THEN 'No Title'
ELSE Title
END
from dbo.d
Output:
Title
No Title
No Title
No Title
No Title
Ms.
Mr.
LEN('sql server') -- 10
select CHARINDEX('e','sql server') -- 6
select PATINDEX('%serv%', 'sql server') -- 5
select REPLACE('sql server', 's', 'cookie') -- cookie server
select REPLICATE('sql', 4) -- sqlsqlsqlsql
select STUFF('sql server', 1, 0, 'Microsoft ')-- Microsoft sql server
select STUFF('sql server', 2, 0, 'Microsoft ')--sMicrosoft ql server
select STUFF('sql server', 2, 1, 'Microsoft ')--sMicrosoft l server
select STUFF('sql server', 1, 1, 'Microsoft ')--Microsoft ql server
select STUFF('sql server', 1, 3, 'Microsoft ')---Microsoft server
select STUFF('sql server', 1, 5, 'Microsoft ')---Microsoft erver
sp_helpsort
sp_who2
sp_updatestats
select ServerProperty ('Collation')
--set DATEFIRST 3
select @@DATEFIRST -- Returns the day number (eg: 7 for sunday, 1 for monday,...)
SELECT @@DBTS
SELECT @@LANGID
SELECT @@LANGUAGE
sp_helplanguage
/***SET LOCK_TIMEOUT allows an application to set the maximum time that a statement waits
on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting,
the blocked statement is automatically canceled, and an error message is returned to the
application.
@@LOCK_TIMEOUT returns a value of -1 if SET LOCK_TIMEOUT has not yet been run in the
current session.*/
select @@LOCK_TIMEOUT
sp_configure
Select @@MAX_CONNECTIONS
select @@MAX_PRECISION
select TRY_CAST('xyz' AS BIGINT)
SELECT ISNULL(TRY_CAST('xyz' AS BIGINT), '99');
SELECT PARSE ('25 Mai 2018' AS date USING 'fr-FR');
•@@IDENTITY returns the last identity value generated for any table in the current session, across all
scopes.
•SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the
current scope.
•IDENT_CURRENT returns the last identity value generated for a specific table in any
session and any scope.
IDENTITY (Function)
Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.
Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and
ALTER TABLE.
Eg:
SELECT IDENTITY(int, 1,1) AS ID_Num INTO NewTable FROM OldTable;
SQL_VARIANT_PROPERTY:
Returns the base data type and other information about a sql_variant value.
DECLARE @v1 sql_variant;
SET @v1 = 'ABC';
SELECT @v1;
SELECT SQL_VARIANT_PROPERTY(@v1, 'BaseType');
SELECT SQL_VARIANT_PROPERTY(@v1, 'MaxLength');
[Column 01] == “i”
?”0”
:”1”
