Scribd Logo
Upload

Welcome to Scribd!

  • 107 views

    SQL Server Notes

    Uploaded by

    Suzzane Khan
    The document contains examples of SQL Server date, string, system, and configuration functions. It demonstrates how to retrieve date values, perform string manipulation, handle errors, get server configuration settings, and work with identity columns and sql_variant data types.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    SQL Server Notes

    Uploaded by

    Suzzane Khan
    107 views7 pages
    The document contains examples of SQL Server date, string, system, and configuration functions. It demonstrates how to retrieve date values, perform string manipulation, handle errors, get server configuration settings, and work with identity columns and sql_variant data types.

    Original Description:

    Sql Server Notes

    Original Title

    Sql Server Notes

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains examples of SQL Server date, string, system, and configuration functions. It demonstrates how to retrieve date values, perform string manipulation, handle errors, get server configuration settings, and work with identity columns and sql_variant data types.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    107 views7 pages

    SQL Server Notes

    Uploaded by

    Suzzane Khan
    The document contains examples of SQL Server date, string, system, and configuration functions. It demonstrates how to retrieve date values, perform string manipulation, handle errors, get server configuration settings, and work with identity columns and sql_variant data types.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 7

    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');

    You might also like