Sql Server Interview Questions and Answers (1758) - Page 85

DECLARE @t1 DATETIME;

DECLARE @t2 DATETIME;

SET @t1 = GETDATE();



--***********   THE QUERY BEGINS        ************************ 



DECLARE @maxLimit INT = 1000000



;WITH

        a AS (SELECT 1 AS i UNION ALL SELECT 1),

        b AS (SELECT 1 AS i FROM a AS x, a AS y),

        c AS (SELECT 1 AS i FROM b AS x, b AS y),

        d AS (SELECT 1 AS i FROM c AS x, c AS y),

        e AS (SELECT 1 AS i FROM d AS x, d AS y),

        f AS (SELECT 1 AS i FROM e AS x, e AS y),

NumCTE AS (SELECT TOP(@maxLimit) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number

FROM f)

SELECT *

FROM NumCTE



--***********   THE QUERY ENDS          ************************ 



SET @t2 = GETDATE();

SELECT 

                DATEDIFF(millisecond,@t1,@t2) AS ElapsedTimeInMilliSeconds,

                (DATEDIFF(millisecond,@t1,@t2) - (((DATEDIFF(millisecond,@t1,@t2))/60000)*60000)) /1000 AS ElapsedTimeInSeconds;



/* Result */

ElapsedTimeInMilliSeconds       ElapsedTimeInSeconds

5246                            5.246

DECLARE @t1 DATETIME



SELECT * INTO #temp 

FROM master..spt_values



--***********   THE CURSOR BEGINS       ************************ 

BEGIN TRAN 

DECLARE @name VARCHAR

SELECT @t1 = GETDATE()



--Declare a cursor and loads the rows/data into the cursor memory

DECLARE tempCursor CURSOR

FOR SELECT name FROM #temp



-- Open the cursor

OPEN tempCursor



--Loop thru each row for processing 

FETCH NEXT 

FROM tempCursor 

INTO @name



--Do something interesting

WHILE @@FETCH_STATUS = 0

BEGIN

    UPDATE #temp 

        SET number = 0 

        WHERE NAME = @name



        --Repeat the process for accessing the next row available in cursor memory

    FETCH NEXT FROM tempCursor 

    INTO @name

END 



-- All operations completed.Now close the cursor

CLOSE tempCursor



-- Clean up

DEALLOCATE tempCursor



--***********   THE CURSOR ENDS         ************************ 



--Measure the cursor processing time

SELECT 

                DATEDIFF(millisecond, @t1, GETDATE()) AS ElapsedTimeFromCursorInMillis;



--Clean up the table

DROP TABLE #temp

SELECT * FROM TableName

Select *

                                From table1

                                Join table2  ON table1.ID = table2.ID

       SELECT ColumnName(s)

        FROM TABLENAME t1

        WHERE

                -- the statemet turn out true as soon as a match is found

                EXISTS ( SELECT COLUMNNAME(S) 

                        FROM TABLENAME t2

                        WHERE t1.ID = t2.ID

                        )

        SELECT ColumnName(s)

                FROM TABLENAME t1

                WHERE t1.ID IN(SELECT ID FROM FROM TABLENAME t2)

Int

Smallint

Tinyint

Bigint

Decimal

Numeric