1- Date Function
SELECT @@DATEFIRST;
SELECT CURRENT_TIMESTAMP;
SELECT DATEADD(day, 1, '2006-08-30');
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635');
SELECT DATEDIFF_BIG(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result; 2010-12-31
SELECT DATENAME(year, GetDate());
SELECT DATEPART(year, GetDate());
SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Result;
SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 ) AS Result;
SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS Result;
SELECT DAY('2015-04-30 01:01:01.1234567')
SELECT EOMONTH (GetDate()) AS Result;
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();
SELECT ISDATE('04/15/2008');
SELECT MONTH('2007-04-30T01:01:01.1234567 -07:00');
SELECT SMALLDATETIMEFROMPARTS ( 2010, 12, 31, 23, 59 ) AS Result
SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result;
SELECT YEAR('2007-04-30T01:01:01.1234567-07:00');
2-String Function
SELECT CHARINDEX('is', 'This is a string', 4);
SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;
SELECT CONCAT_WS(',','1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;
SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green','Greene');
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'
SELECT LEFT('abcdefg',2);
SELECT
@v1 = 'Test of 22 characters ',
@v2 = 'Test of 22 characters ';
SELECT LEN(@v1) AS [varchar LEN] , DATALENGTH(@v1) AS [varchar DATALENGTH];
SELECT LOWER(Name)
SELECT NCHAR(143743);
SELECT PATINDEX('%ter%', 'interesting data');
SELECT QUOTENAME('abc def');
SELECT REPLACE('abcdefghicde','cde','xxx');
SELECT [Name] , REPLICATE('0', 4)
SELECT REVERSE(1234) AS Reversed ;
SELECT TOP(1) RIGHT('abcdefg',2) FROM dbo.DimProduct;
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');
SELECT RTRIM(LastName) + ',' + SPACE(2) + LTRIM(FirstName) FROM Person.Person
SELECT STR(123.45, 6, 1);
SELECT STRING_AGG (FirstName, CHAR(13)) AS csv FROM Person.Person;
SELECT STRING_ESCAPE('\ / \\ " ', 'json') AS escapedText;
SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
SELECT x = SUBSTRING('abcdef', 2, 3);
SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');
SELECT TRIM( ' test ') AS Result;
SELECT TRIM( '.,! ' FROM '# test .') AS Result;
SELECT UPPER(RTRIM(LastName)) From Customer
3-System Function
SELECT $PARTITION.TransactionRangePF1(TransactionDate) AS Partition,
COUNT(*) AS [COUNT] FROM Production.TransactionHistory
GROUP BY $PARTITION.TransactionRangePF1(TransactionDate)
ORDER BY Partition ;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 4
WHERE BusinessEntityID = 1;
IF @@ERROR = 547
PRINT N'A check constraint violation occurred.';
DECLARE @myint int;
SET @myint = 'ABC';
GO
SELECT 'Error number was: ', @@ERROR;
INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
VALUES ('Damaged Goods', 5, 2.5, GETDATE());
GO
SELECT @@IDENTITY AS 'Identity';
UPDATE HumanResources.Employee
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
PRINT @@TRANCOUNT
-- The BEGIN TRAN statement will increment the
-- transaction count by 1.
BEGIN TRAN
PRINT @@TRANCOUNT
BEGIN TRAN
PRINT @@TRANCOUNT
-- The COMMIT statement will decrement the transaction count by 1.
COMMIT
PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT
INSERT INTO player (name, surname, info )
VALUES (N'Ovidiu', N'Cracium',
COMPRESS(N'{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}'));
INSERT INTO player (name, surname, info )
VALUES (N'Michael', N'Raheem', compress(@info));
DELETE player
WHERE datemodified < @startOfYear
OUTPUT id, name, surname datemodifier, COMPRESS(info)
INTO dbo.inactivePlayers
SELECT
ConnectionProperty('net_transport') AS 'Net transport',
ConnectionProperty('protocol_type') AS 'Protocol type';
SELECT CONTEXT_INFO();
SELECT _id, name, surname, datemodified,
CAST(DECOMPRESS(info) AS NVARCHAR(MAX)) AS info
FROM player;
CREATE TABLE (
_id int primary key identity,
name nvarchar(max),
surname nvarchar(max),
info varbinary(max),
info_json as CAST(decompress(info) as nvarchar(max))
);
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
END CATCH;
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
CREATE PROCEDURE usp_ExampleProc
AS
SELECT 1/0;
GO
BEGIN TRY
-- Execute the stored procedure inside the TRY block.
EXECUTE usp_ExampleProc;
END TRY
BEGIN CATCH
SELECT ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH;
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
SELECT text FROM sys.messages WHERE message_id = 20009 AND language_id = 1033;
DECLARE @var1 VARCHAR(200);
SELECT @var1 = FORMATMESSAGE(20009, 'First Variable', 'Second Variable');
SELECT @var1;
SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS Result;
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
SELECT GETANSINULL('AdventureWorks2012')
Select HOST_ID ()
Select HOST_NAME ()
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)
DECLARE @myid uniqueidentifier ;
SET @myid = 'A972C577-DFB0-064E-1189-0154C99310DAAC12';
SELECT @myid;
SELECT SESSION_ID();
4-Configuration
SELECT @@DBTS;
SELECT @@LANGID AS 'Language ID'
SELECT @@LANGUAGE AS 'Language Name';
SELECT @@LOCK_TIMEOUT AS [Lock Timeout];
SELECT @@MAX_CONNECTIONS AS 'Max Connections';
SELECT @@MAX_PRECISION AS 'Max Precision'
CREATE PROCEDURE usp_InnerProc AS
SELECT @@NESTLEVEL AS 'Inner Level';
GO
CREATE PROCEDURE usp_OuterProc AS
SELECT @@NESTLEVEL AS 'Outer Level';
EXEC usp_InnerProc;
GO
EXECUTE usp_OuterProc;
SELECT @@OPTIONS AS OriginalOptionsValue;
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'abc' + NULL AS ResultWhen_OFF, @@OPTIONS AS OptionsValueWhen_OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'abc' + NULL AS ResultWhen_ON, @@OPTIONS AS OptionsValueWhen_ON;
CREATE PROCEDURE usp_CheckServer
AS
SELECT @@REMSERVER;
SELECT @@SERVERNAME AS 'Server Name'
SELECT @@SERVICENAME AS 'Service Name';
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';
SET TEXTSIZE 0
SELECT @@TEXTSIZE AS 'Text Size'
SET TEXTSIZE 2048
SELECT @@TEXTSIZE AS 'Text Size'
SELECT @@VERSION AS 'SQL Server Version';
SELECT length = DATALENGTH(Name), Name
FROM Production.Product
ORDER BY Name;
SELECT length = DATALENGTH(EnglishProductName), EnglishProductName
FROM dbo.DimProduct
ORDER BY EnglishProductName;
SELECT IDENT_CURRENT ('Person.Address') AS Current_Identity;
SELECT SCOPE_IDENTITY();
SELECT IDENT_INCR('Person.Address') AS Identity_Increment;
SELECT IDENT_SEED('Person.Address') AS Identity_Seed;
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable;
--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable;
DECLARE @v1 sql_variant;
SET @v1 = 'ABC';
SELECT @v1;
SELECT SQL_VARIANT_PROPERTY(@v1, 'BaseType');
SELECT SQL_VARIANT_PROPERTY(@v1, 'MaxLength');
