aiDeeva.com

Category: SQL Server 2008

  • How to rename Column Name and Table Name?

    We can use sys.SP_Rename procedure to rename the column and table in SQL Server.

    CREATE TABLE empl
    (
    name VARCHAR(20),
    remarks VARCHAR(100)
    )

    –syntax to rename column
    EXEC sys.Sp_rename
    @objname = ‘table_name.old_column_name’,
    @newname = ‘new_column_name’,
    @objtype = ‘COLUMN’

    –rename column example
    EXEC sys.Sp_rename
    @objname = ’empl.name’,
    @newname = ’empname’,
    @objtype = ‘COLUMN’


    –rename table name Old Table Name and New Table Name
    EXEC sys.Sp_rename
    ’empl’,
    ’empInfo’
    EXEC sys.Sp_rename

  • Logical Joins and Physical Joins

    Logical Joins: INNER JOIN, RIGHT/LEFT OUTER JOIN, CROSS JOIN, OUTER APPLY. This joins are written by the user.

    Physical Joins: Nested loop, merge join and hash join is common operators which we will see in execution plan as long as query contains any logical joins (inner join, outer join, cross join, semi join). These three joins are called Physical Joins.

    In below example I have executed query against AdventureWorks database with Inner Join. You can see the Nested Join in Execution Plan.

  • Date Time Functions

    CURRENT_TIMESTAMP()
    Returns the current database system timestamp as a datetime value without the database time zone offset.

    GETDATE ()
    Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    GETUTCDATE()
    Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time).

    SYSDATETIME()

    Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running

    SYSDATETIMEOFFSET()
    Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running with timezone offset.

    SYSUTCDATETIME
    Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

    SELECT SYSDATETIME()
    ———————-
    2011-01-10 06:38:25.05

    SELECT SYSDATETIMEOFFSET()
    ———————————-
    2011-01-10 06:38:25.0527369 -05:00

    SELECT SYSUTCDATETIME()
    ———————-
    2011-01-10 11:38:25.05

    SELECT CURRENT_TIMESTAMP
    ———————–
    2011-01-10 06:38:25.050

    SELECT GETDATE()
    ———————–

    2011-01-10 06:38:25.050

    SELECT GETUTCDATE()
    ———————–
    2011-01-10 11:38:25.050

    Note: Refer books online for more information.

  • Download and Install AdventureWorks Sample Database

    Microsoft SQL Server Sample Database is moved to Microsoft’s Open Source site of CodePlex.

    You can download it from the below link.
    http://msftdbprodsamples.codeplex.com/

  • How to Create Alias in SQL Server?

    What is Alias?
    A SQL Server alias is the user friendly name. For example if there are many application databases hosted on same SQL Server. You can give the different alias name for each application. Simply Alias is an entry in a hosts file, a sort of hard coded DNS lookup a SQL Server instance.

    You can create Alias from Configuration Manager.

    Go to SQL Server Configuration manager – Go to SQL Native Client -> Right Click and SELECT New Alias from the popup window.

    Alias Name — Alernative name of SQL Server
    Port No — Specify the Port No
    Server – Mentioned the Server Name or IP address

    Make sure On a 64-bit system, if you have both 32-bit and 64-bit clients, you will need to create an alias for BOTH 32-bit and 64-bit clients.