What is the differences between TRUNCATE and DELETE ?.

TRUNCATE :

Truncate is a DDL command.
Truncate removes all rows from a table.
Truncate is executed by using a  table and page lock but not each row, So it can not activate a trigger and we cannot rollbacked the transaction.
Truncate resets the seed value if the Identity column exists.
Truncate drops all object statistics.
Truncate is faster because it deletes pages instead of rows.

Syntax : TRUNCATE TABLE TABLE_NAME

DELETE :

Delete is a DML command.
Delete removes specified rows from a table by using where condition and it removes all rows if no where condition.
Delete is executed by using a row lock, So it can activate a trigger and we can rollbacked the transaction.
Delete retain the identity value same.
Delete keeps all object statistics.
Delete is slower than truncate command.

Syntax : DELETE FROM TABLE_NAME  [WHERE COLUMN_ID = 1]

Script to find out the Missing Indexes in Database :

DECLARE @dbid INT

SELECT @dbid = Db_id('database_name')

SELECT d.statement                     AS table_with_missing_index,
       s.avg_user_impact,
       CONVERT(INT, ( unique_compiles + user_seeks + user_scans ) *
                    avg_total_user_cost
                    * (
                    avg_user_impact )) AS index_advantage,
       d.equality_columns,
       d.inequality_columns,
       d.included_columns,
       s.avg_total_user_cost,
       s.unique_compiles,
       s.user_seeks,
       s.user_scans,
       s.last_user_seek
FROM   sys.dm_db_missing_index_group_stats s,
       sys.dm_db_missing_index_groups g,
       sys.dm_db_missing_index_details d
WHERE  s.group_handle = g.index_group_handle
       AND d.index_handle = g.index_handle
       AND database_id = @dbid
ORDER  BY d.statement,
          index_advantage DESC,
          s.avg_user_impact DESC 

Concatenate all the rows values into single row :

Script to create a table and load the data :
CREATE TABLE test_rowconcatenate
  (
     id   INT IDENTITY(1, 1),
     name VARCHAR(10)
  )

SELECT *
FROM   test_rowconcatenate

INSERT INTO test_rowconcatenate
VALUES     ('A'),
            ('B'),
            ('C'),
            ('D'),
            ('E'),
            ('F'),
            ('G'),
            ('H'),
            ('I'),
            ('J'),
            ('K'),
            ('L'),
            ('M'),
            ('N'),
            ('O'),
            ('P')

SELECT *
FROM   test_rowconcatenate

Ways to concatenate all the row values into single record :

1).

DECLARE @string VARCHAR(max) = ''

SELECT @string = @string + ',' + name
FROM   test_rowconcatenate

PRINT Substring(@string, 2, Len(@string))

2).
DECLARE @string VARCHAR(max)='',
        @field  VARCHAR(10)='',
        @i      INT = 1,
        @n      INT

SELECT @n = Count(0)
FROM   test_rowconcatenate

WHILE( @i <= @n )
  BEGIN
      SELECT @field = name
      FROM   (SELECT name,
                     Row_number()
                       OVER(
                         ORDER BY id) row_id
              FROM   test_rowconcatenate) a
      WHERE  row_id = @i

      SET @string = @string + ',' + @field
      SET @i = @i + 1
  END

PRINT Substring(@string, 2, Len(@string))

3).
DECLARE @string VARCHAR(max) ='',
        @field  VARCHAR(10)
DECLARE test_cur CURSOR FOR
  SELECT name
  FROM   test_rowconcatenate

OPEN test_cur

FETCH FROM test_cur INTO @field

WHILE @@Fetch_status = 0
  BEGIN
      SET @string = @string + ',' + @field

      FETCH FROM test_cur INTO @field
  END

CLOSE test_cur

DEALLOCATE test_cur

PRINT Stuff(@string, 1, 1, '')

4).
SELECT Stuff((SELECT ',' + name
              FROM   test_rowconcatenate
              FOR xml path('')), 1, 1, '')

Query to find out the 2nd highest or Nth highest record :

Script to create a table and loading data :

CREATE TABLE test_highest_record
  (
     id     INT IDENTITY(1, 1),
     name   VARCHAR(10),
     salary INT
  )

INSERT INTO test_highest_record
VALUES     ('A',
            10000),
            ('A',
             1000),
            ('B',
             70000),
            ('C',
             40000),
            ('D',
             90000),
            ('E',
             20000),
            ('F',
             300),
            ('G',
             4000),
            ('H',
             11000)

No w check the records in table,

SELECT * 

FROM   test_highest_record 

ORDER  BY 3 

ways to find out the 2nd highest number : 

1). 

SELECT TOP 1 salary 

FROM   (SELECT TOP 2 salary 

        FROM   test_highest_record 

        ORDER  BY 1 DESC) a 

ORDER  BY 1 

2).

SELECT Max(salary) 

FROM   test_highest_record 

WHERE  salary NOT IN (SELECT Max(salary) 

    

                  FROM   test_highest_record) 

3).

SELECT salary 

FROM   (SELECT salary, 

               Row_number() 

                 OVER( 

                   ORDER BY salary DESC) row_id 

        FROM   test_highest_record) a 

WHERE  row_id = 2 

4).

SELECT Max(salary) 

FROM   test_highest_record t1 

WHERE  2 <= (SELECT Count(0) 

             FROM   test_highest_record t2 

             WHERE  t1.salary <= t2.salary) 

To find out the Nth highest Number :

1).

SELECT TOP 1 salary 

FROM   (SELECT TOP @N@ salary 

        FROM   test_highest_record 

        ORDER  BY 1 DESC) a 

ORDER  BY 1 

2).

SELECT salary 

FROM   (SELECT salary, 

               Row_number() 

                 OVER( 

                   ORDER BY salary DESC) row_id 

        FROM   test_highest_record) a 

WHERE  row_id =  @N@

3).

SELECT Max(salary) 

FROM   test_highest_record t1 

WHERE   @N@ <= (SELECT Count(0) 

             FROM   test_highest_record t2 

             WHERE  t1.salary <= t2.salary) 

Note : Replace  @N@ with number .

SSMS Shortcut Keys

Shortcut Keys :

CTRL+R  ===>  Hide the query results window

SHIFT+ALT+ENTER ===> Expand the query window

ALT+F1 ===> Sp_Help

CTRL+1 ===> Sp_who

CTRL+2 ===> Sp_lock

CTRL+U ===> Changing the Database.

CTRL+SHIFT+L ===> Chnaging the code case to Lower

CTRL+SHIFT+U ===> Chnaging the code case to Upper

CTRL+K followed by CTRL+C ===> Commenting the selected Code

CTRL+K followed by CTRL+U ===> Uncommenting the selected Code

CTRL+K followed by CTRL+K ===> To set the bookmark on the line.

CTRL+K followed by CTRL+N ===> To goto the next bookmarked line in the code.

CTRL+K followed by CTRL+P ===> To goto the Previous bookmarked line in the code.

CTRL+K followed by CTRL+L ===> To clear all bookmarks in the code. (Note : It will ask you for confirmation to clear all the bookmarks, if you click yes then only it will clear all the bookmarks)

CTRL+K followed by CTRL+W ===> To manage all the bookmarks in a query window.

CTRL+F ===> To find a spefic keyword.

CTRL+H ===> To replace a spefic keyword with mentioned keyword.

CTRL+G ===> To go to a spefic Line.

CTRL+N ===> To Open a New Query window.

CTRL+O ===> To open a File.

CTRL+TAB ===> To Switch to other query window.

F8 ===> Object Explorer

CTRL+ALT+T ===> Template Explorer

CTRL+ALT+L ===>  Solution Explorer

F4 ===>  Properties window

CTRL+ALT+G ===>  Registered Servers explorer


CTRL+T (before executing the script) ===> To dosplay the results as Text format.

CTRL+D (before executing the script) ===> To display the results in GRID format.

CTRL+SHIFT+F (before executing the script) ===> To get the results in File format.

CTRL+L ===> To display the Query Execution Plan.

CTRL+M ===> To get the query Actual execution plan with query results.

SHIFT+ALT+S ===> To include client statistics.



Reference :  MSDN_SK
               

Get the Weekend Count Between Two dates :

Check This code


declare @todate datetime, @fromdate datetime,@t int,@n int,@i int
select @todate = getdate() , @fromdate = getdate()-12, @t = datediff(dd,@fromdate,@todate), @n = 1 , @i = 0
while(@n <= @t)
Begin
IF datepart(dw,@fromdate) = 1 or datepart(dw,@fromdate) = 7
BEGIN
set @i = @i +1
END
set @fromdate = @fromdate+1
set @n = @n + 1
END
print @i

STUFF FUNCTION

This function inserts a string into another string, it deletes a specified lenght of the characters at the starting position in the expression and inserts the second string at the starting position.

syntax : STUFF ( character_expression , start , length , replaceWith_expression )

start : Integer value it specifies the position to start the insertion.

length : Integer value it specifies the number of characters to delete from start position.

Reference : MSDN_STUFF



Difference between STUFF and REPLACE Functions :

STUFF function deletes a specified length of string from start position and inserts the second string in the expression.

REPLACE function replaces all occurences of the string with the second string in the expression.

Check this code

select stuff('krishna',1,0,'MR. ')

output is
MR. krishna

select replace('krishna', 'k','satya k')

output is
satya krishna

GOTO

Alters the flow of execution to a label. The statements that follow GOTO are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can be nested.
Reference : MSDN_GOTO

Difference between Len() and DataLength() functions

Len() function gives the length of the characters in a string by excluding the trailing spaces, but datalength() function will give the length of characters in a string by including trailing spaces also because it returns the amount of storage required to represent a column.

Datalength() function works on nvarchar and nchar accurately by considering 2 bytes per character where len() function does not.

Datalength() function works on text and ntext where len() function does not works.

Check This code

create table test(val1 char(10), val2 nchar(10))


insert into test values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from test

Output is
3 3   10   20

create table test_v(val1 varchar(10), val2 nvarchar(10))


insert into test_v values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from test_v

Output is
3   3   3   6


create table test_c(val1 text, val2 ntext)


insert into test_c values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from  test_c

Above query gives the below error, because len() function not support text data types
Argument data type text is invalid for argument 1 of len function.

select datalength(val1),datalength(val2) from  test_c

Output is
3 6


Reference : MSDN_Len
                   MSDN_DataLength

ROW Functions

@@Rowcount : Returns the number of rows affected by the last statement.
Reference : MSDN_ROWCOUNT   


ROWCOUNT_BIG( ) : This function is also working same as @@rowcount, if the table having more than 2 billion rows then this one will useful.
Reference : MSDN_ROWCOUNT_BIG


SET ROWCOUNT : MSSQL Server stops the query processing after the specified number of rows are returned.
By using the below code we can eliminates the restriction on number of rows processing
SET ROWCOUNT 0

Reference : MSDN_SET_ROWCOUNT

TOP : Limits the rows returned in a query result set to a specified number of rows or percentage of rows.
Reference : MSDN_TOP


ROW_NUMBER : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Reference : MSDN_ROWNUMBER

RANK : Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row.
Reference : MSDN_RANK

DENSE_RANK : Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
Reference : MSDN_DENSERANK

NTILE : Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Reference : MSDN_NTILE

Difference between CAST and CONVERT functions

Cast and convert functions are used to convert one data type to another datatype, but CAST is ANSI standard function and CONVERT is specific to MSSQL Server and by using CONVERT we can get the specified format particularly for datetime datatype fields.

Reference : MSDN_CAST_CONVERT


Performance wise both the functions will perform equally.

Reference : Good Post

Null Functions

ISNULL : It validates the expression and replaces to a specified value if it is null, other wise it will returns the expression.

ISNULL(expression,specifiedvalue)

NULLIF : It compaares the expressions and returns NULL if both the expressions are same, otherwise it will returns first explression.

NULLIF(expression1,expression2)

COALESCE : It will return the first Non Null expression from the specified expressions, if all expressions are NULL then it will returns NULL.

COALESCE(expression1,expression2,expression3.....,expressionN)


GETANSINULL : It returns the default nullability of the specified database.

GETANSINULL('databasename')

When the nullability of the specified database allows for null values and the column or data type nullability is not explicitly defined, GETANSINULL returns 1. This is the ANSI NULL default.

Reference : GETANSINULL


check this code

SET ANSI_NULL_DFLT_ON off
select getansinull('master')
create table m(id int,val char)
insert m values(2,null)

This query returns the below error,

Cannot insert the value NULL into column 'val', table 'master.dbo.m'; column does not allow nulls. INSERT fails.
The statement has been terminated.


SET ANSI_NULL_DFLT_ON on
select getansinull('master')
create table p(id int,val char)
insert p values(2,null)

This query working fine.

Note : Twist in ISNULL Function

NULL

1 NULL can be defined as absence of value, undefined, or the value which is unknown.

2 All datatypes can be defined with NULL constraint

3 Direct usage of arithmetic or logical operations on NULL will not work as expected

4 The system functions ISNULL, COALESE and NULLIF are used only to deal with NULL

5 NOT NULL constraint cannot be defined for a computed column until it is also PERSISTED

6 The only datatypes that will interpret NULL differently are rowversion and timestamp

Run this code and see the output

declare @rv rowversion , @ts timestamp
select @rv=null, @ts=null
select @rv as rowversion,@ts as timestamp

Output is
rowversion         timestamp
------------------ ------------------
0x                 0x

7 NULL=NULL will never be true unless SET ANSI_NULLS is OFF

While the first query returns nothing, the second will return 6
--Query 1
set ansi_nulls on
select 6 where null=null
--Query 2
set ansi_nulls off
select 6 where null=null
The condition WHERE col IS NULL will not be affected by the above setting

8 The default datatype of NULL is INT. Refer Default datatype of NULL for more information

9 Column with UNIQUE constraint will allow only one NULL value in SQL Server

10  NULL will make SQL Server to use short circuit logic in some cases

Consider this example
select 6/0/null
select null/6/0
While the first query throws an error the second query returns NULL

11 The value NULL is not equal to string value 'NULL'

12 By default NULL values come first when a column is ordered in ascending order and come last when ordered in descending order

13 If you dont use GROUP BY clause, the aggregate functions will always return single value (NULL) when the condition is false

select sum(col) as col from ( select 45 as col ) as t where 1=0
The above returns NULL

14 NULL values are by default omitted in all aggregate functions

Consider the following example

select sum(col) as col_cum,count(col) as col_count,avg(col*1.0) as col_avg from ( select 1 as col union all select null as col union all select 2 as col union all select 3 as col ) as t

The output is
col_cum     col_count   col_avg
----------- ----------- -----------
6           3           2.000000

15 Aggregate functions cannot be directly applied over NULL value

This code

select sum(null) as null_sum
returns the following error
Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

16. If sub-query returns NULL value when NOT IN used to filter the records, main query returns no records.

This code

create table test(id int)
insert into test select 1 union all select 2 union all select null union all select 3
select * from test where id in (1,2,3,null)
select * from test where id not in (1,2,3,null)

where first query returns 3 values and second query returns no records

Reference : About NULL

Database Schema :

A database schema is a way to logically group objects such as tables, views, stored procedures etc.You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.


Create Syntax :

CREATE SCHEMA schema_name

Refer :
http://msdn.microsoft.com/en-us/library/ms189462.aspx

http://msdn.microsoft.com/en-us/express/bb403186


http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/

http://www.quackit.com/sql_server/sql_server_2008/tutorial/sql_server_database_schemas.cfm

Database :

Databases are designed to offer an organized mechanism for storing, managing and retrieving information. They do so through the use of tables. If you’re familiar with spreadsheets like Microsoft Excel, you’re probably already accustomed to storing data in tabular form.

Definition : 

A database is a collection of information organized into interrelated tables of data and specifications of data objects.



Syntax :

CREATE DATABASE database_name

Refer : http://msdn.microsoft.com/en-us/library/ms176061.aspx

Creating new Database from Management Studio :

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance and then Right-click on Databases, and then click New Database.In New Database, enter a database name and click OK.

Refer : http://msdn.microsoft.com/en-us/library/ms186312.aspx