Tips and Tricks

                                       BASIC OPERATION IN TABLE

--------------------------alter/change data type to an existing column in a table------------------

alter table tablename alter column columnname varchar(100)/int/char(10)

--------------------------add primary key to an existing  table------------------

alter table tablename add constraint pk_columnname primary key(columnname)

-------------Used to Check for Allowed Values in a Column:---------

ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT CK_Gender
CHECK ( [Gender] IN ('M', 'F', 'U') )

ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT CK_Contact_Information
CHECK ( [PhoneNumber] IS NOT NULL OR [CellNumber] IS NOT NULL )

.....Contd

Convert Oracle String Functions to SQL Server String Functions

Function	Oracle	SQL Server
Convert Character to ASCII	ASCII(p)	ASCII(p)
Convert ASCII to Character	CHR(p)	CHAR(p)
Concatenate Strings	CONCAT(p1, p2) p1 || p2	p1 + p2
Return Starting Point of Character in Character String	INSTR(p1, p2)	CHARINDEX(p2, p1)
Convert String to All Lower Case	LOWER(p1)	LOWER(p1)
Convert String to All Upper Case	UPPER(p1)	UPPER(p1)
Pad Left Side of Character String	LPAD(p1, p2, p3)	RIGHT(REPLICATE(p3, p2) + p1, p2)
Remove Leading Blank Spaces	LTRIM(p1)	LTRIM(p1)
Remove Trailing Blank Spaces	RTRIM(p1)	RTRIM(p1)
Return Starting Point of Pattern in Character String	INSTR(p1, p2)	PATINDEX(p2, p1)
Pad Right Side of Character String	RPAD(p1, p2, p3)	LEFT(p1 + REPLICATE(p3, p2), p2)
Phonetic Representation of Character String	SOUNDEX(p1)	SOUNDEX(p1)
Convert Numeric Data to String	TO_CHAR(p1)	CAST(p1 as VARCHAR)
Extract a Portion of a String from the Input String	SUBSTR(p1, p2, p3)	SUBSTRING(p1, p2, p3)
Replace Characters	REPLACE(p1, p2, p3)	REPLACE(p1, p2, p3)
Reverse String	REVERSE(p)	REVERSE(p)
Capitalize First Letter of Each Word in String	INITCAP(p1)	Not Available
Translate Character String	TRANSLATE(p1)	Not Available
Return Length of String	LENGTH(p)	LENGTH(p)
Return Length of String in Bytes	LENGTHB(p)	DATALENGTH(p)
Greatest Character String in List	GREATEST(p1, p2, …)	Not Available
Least Character String in List	LEAST(p1, p2, …)	Not Available
Replace String if NULL	NVL(p1, p2)	ISNULL(p1, p2)
Return NULL if both Expressions are the Same.	NULLIF(p1, p2)	NULLIF(p1, p2)

SQL Server Management Studio Shortcuts

Here’s a list of shortcuts available from SQL Server Management Studio (SSMS).  This includes the function keys and what it does in SQL Server Management Studio.  Some of these shortcuts are the same as the ones used in other applications such Microsoft Word like the ones in the Basic Editing Shortcuts.

Basic Editing Shortcuts Ctrl+X Cut Ctrl+C Copy Ctrl+V Paste Ctrl+A Select All Ctrl+Z Undo Ctrl+Y Redo Ctrl+F Quick Find Ctrl+H Quick Replace Ctrl+Shift+H Replace in Files Ctrl+G Go To Line File Shortcuts Ctrl+N New Query with Current Connection Ctrl+Shift+N New Project Ctrl+O Open File Ctrl+Shift+O Open Project/Solution Ctrl+S Save Ctrl+Shift+S Save All Ctrl+P Print View Shortcuts Ctrl+Alt+G Registered Servers Ctrl+Alt+T Template Explorer Ctrl+Alt+L Solution Explorer Ctrl+K, Ctrl+W Bookmark Window Ctrl+Alt+X Toolbox Ctrl+\, Ctrl+E Error List Ctrl+Alt+R Web Browser Ctrl+Alt+K Task List Ctrl+Alt+O Output Shift+Alt+Enter Full Screen Ctrl+- Navigate Backward Query Shortcuts Ctrl+Shift+M Specify Values for Template Parameters Ctrl+L Display Estimated Execution Plan Ctrl+Alt+P Trace Query in SQL Server Profiler Ctrl+Shift+Q Design Query in Editor Ctrl+M Include Actual Execution Plan Shift+Alt_S Include Client Statistics Ctrl+T Results to Text Ctrl+D Results to Grid Ctrl+Shift+F Results to File Window Shortcuts Ctrl+R Hide/Show Results Pane

Advanced Editing Shortcuts Ctrl+Shift+U Make Uppercase Ctrl+Shift+L Make Lowercase Ctrl+K, Ctrl+\ Delete Horizontal White Space Ctrl+I Incremental Search Ctrl+K, Ctrl+C Comment Selection Ctrl+K, Ctrl+U Uncomment Selection Bookmarks Shortcuts Ctrl+K, Ctrl+K Toggle Bookmark Ctrl+K, Ctrl+P Previous Bookmark Ctrl+K, Ctrl+N Next Bookmark Ctrl+K, Ctrl+L Clear Bookmarks Ctrl+Shift+K, Ctrl+Shift+P Previous Bookmark in Folder Ctrl+Shift+K, Ctrl+Shift+N Next Bookmark in Folder Function Keys F1 Help Ctrl+F1 Help – How Do I Ctrl+Alt+F1 Help Contents Ctrl+Alt+F2 Help Index F3 Find Next Ctrl+Alt+F3 Help Search F4 View Properties Window F5 Refresh / Execute Alt-F5 Start Debugging Ctrl+F5 Parse Ctrl+Alt+F5 Help Tutorial F6 Window-Next Pane Shift+F6 Window-Previous Pane F7 View Object Explorer Details F8 View Object Explorer Alt+F8 Open Server in Object Explorer F9 Debug-Toggle Break Point F10 Debug-Step Over F11 Debug-Step Into Intellisense Shortcuts Ctrl+J List Members Ctrl+Shift+Space Parameter Info Ctrl+K, Ctrl+I Quick Info Alt+Right Arrow Complete Word Ctrl+Shift+R Refresh Local Cache

                      How to Get a List of SQL Server Servers on the Network

For SQL Server 2005, SQL Server 2008 and later, to get a list of SQL Server servers on the network, the sqlcmd.exe utility can be used.  The sqlcmd utility lets you enter Transact-SQL statements, system procedures and script files at the command prompt.  One of the arguments that can be passed to the sqlcmd utility is “-Lc” which will list locally configured server computers, and the names of the server computers that are broadcasting on the network.  The “c” part of the “-Lc” argument stands for clean output and it means that the output appears without the Servers: header line and each server line is listed without leading spaces.

C:\> sqlcmd -Lc

For SQL Server 2000, the isql or osql utilities can be used.  Just like the sqlcmd utility, the isql and osql utilites allow you to enter Transact-SQL statements, system procedures and script files form the command prompt.  The osql utility uses ODBC to communicate with the server while the isql utility uses DB-Library to communicate with SQL Server 2000.  Unlike the sqlcmd utility, the clean output “c” argument option is not available and only the “-L” command argument is available.

C:\> osql -L

Generate Random Password User-Defined Function
As discussed on the said tip of the day, I will need to call the 

NEWID() function within the user-defined function.  Since I cannot call 
the NEWID() function directly from the user-defined function, I need to 
create a view first:


CREATE VIEW [dbo].[RandomNewID]
AS
SELECT NEWID() AS [NewID]
GO
Now that I have this view, I can now create my user-defined function 
that will generate a random password.  To make the function more 
flexible, it accepts the number of characters that the user wants for 
the password:

CREATE FUNCTION [dbo].[ufn_GeneratePassword] ( @PasswordLength INT )
RETURNS VARCHAR(20)
AS
BEGIN


DECLARE @Password     VARCHAR(20)
DECLARE @ValidCharacters   VARCHAR(100)
DECLARE @PasswordIndex    INT
DECLARE @CharacterLocation   INT


SET @ValidCharacters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890'


SET @PasswordIndex = 1
SET @Password = ''


WHILE @PasswordIndex <= @PasswordLength
BEGIN
 SELECT @CharacterLocation = ABS(CAST(CAST([NewID] AS VARBINARY) AS INT)) % 
LEN(@ValidCharacters) + 1
 FROM [dbo].[RandomNewID]


 SET @Password = @Password + SUBSTRING(@ValidCharacters, @CharacterLocation, 1)


 SET @PasswordIndex = @PasswordIndex + 1
END


RETURN @Password


END
Here are some sample output using the generate password function:


SELECT [dbo].[ufn_GeneratePassword] ( 8 ) AS [Password8],
       [dbo].[ufn_GeneratePassword] ( 10 ) AS [Password10],
       [dbo].[ufn_GeneratePassword] ( 12 ) AS [Password12]


Password8  Password10   Password12
---------  -----------  -------------
rj58PffA   0x8dwB9peF   iuLysW0zA2jy


How to Get a List of Stored Procedures Within a Database
There are different ways of listing all stored procedures within a database.  The first first method is with the INFORMATION_SCHEMA.ROUTINES system view.  The INFORMATION_SCHEMA.ROUTINES
 system view contains one row for each stored procedure and function 
accessible to the current user in the current database.  Since only 
stored procedures are needed, the ROUTINE_TYPE needs to be filtered out 
for a value of ‘PROCEDURE’:


SELECT [Routine_Name] 
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_TYPE] = 'PROCEDURE'
Another way of getting a list of stored procedures within a database is with the sp_stored_procedures system stored procedure.  The sp_stored_procedures
 system stored procedure returns a list of stored procedures and 
user-defined functions in the current environment.  The list returned by
 the sp_stored_procedures includes system stored 
procedures as well.  To limit the list to user-defined stored 
procedures, the owner of the stored procedure needs to be passed as a 
parameter:


EXECUTE [dbo].[sp_stored_procedures] @sp_owner ='dbo'
The third way of getting a list of stored procedures is by querying the different system views and system tables, namely the sys.procedures, sys.objects, sys.all_objects, and dbo.sysobjects:


SELECT [Name] FROM [sys].[procedures]

SELECT [Name] FROM [sys].[objects] WHERE [type] = 'P'

SELECT [Name] FROM [sys].[all_objects] WHERE [Type] = 'P' AND [Is_MS_Shipped] = 0

SELECT [Name] FROM [dbo].[sysobjects] WHERE [XType] = 'P'



How to Join with an Inline User-Defined Function
An inline user-defined function is a type of function which returns a table dat type instead of just a scalar value and can be used to achieve the functionality of parameterized views.  An inline user-defined function can be used where table or view expressions are allowed in Transact-SQL queries.  The table returned by an inline user-defined function can be referenced in the FROM clause of a Transact-SQL statement.
Performing a JOIN with an inline user-defined function where the value passed as a parameter to the function comes from the table being joined with it, the INNER JOIN (or LEFT OUTER JOIN) cannot be used as it will give an error of 
Msg 4104, Level 16, State 1, Line 2 The multi-part identified could not be bound.

To illustrate, given the following tables and inline user-defined function:
CREATE TABLE [dbo].[MLBTeam] ( [TeamCode]    CHAR(3),
 [TeamName]    VARCHAR(50))

CREATE TABLE [dbo].[MLBPitcher] (
 [PitcherName]    VARCHAR(50),
 [TeamCode]       CHAR(3),
 [ERA]            DECIMAL(6, 3)
)

CREATE FUNCTION [dbo].[ufn_GetTopPitchers] ( @TeamCode CHAR(3) )
RETURNS TABLE AS
RETURN (
    SELECT TOP 3 [TeamCode], [PitcherName]
    FROM [dbo].[MLBPitcher]
    WHERE [TeamCode] = @TeamCode
    ORDER BY [ERA] )

The following INNER JOIN statement will generate an error:


SELECT A.[TeamCode], A.[TeamName], B.[PitcherName]
FROM [dbo].[MLBTeam] A INNER JOIN [dbo].[ufn_GetTopPitchers] ( A.[TeamCode] ) B
          ON A.[TeamCode] = B.[TeamCode]

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "A.TeamCode" could not be bound.
To perform a join between a table and an inline user-defined function
 where the value passed to the function parameter comes from a column in
 the table, the CROSS APPLY operator will be used:


SELECT A.[TeamCode], A.[TeamName], B.[PitcherName]
FROM [dbo].[MLBTeam] A CROSS APPLY [dbo].[ufn_GetTopPitchers] ( A.[TeamCode] ) B





Differences Between DELETE and TRUNCATE command 

The DELETE command and the TRUNCATE

command both are used to delete data from a table.  Both commands will 
only delete the data of the specified table and not the structure of the
 table.


The DELETE command removes rows from a table or a view and has the following basic syntax:


DELETE FROM <Table or View Name>
WHERE <Search Condition>
On the other hand, the TRUNCATE command removes all rows from a table without logging the individual row deletions, and has the following basic syntax:


TRUNCATE TABLE <Table Name>
Here’s a summary of the differences between the DELETE command and the TRUNCATE command.




    


            

DELETE

            

TRUNCATE

        

            
Logged operation on a per row basis which means the deletion of each row is logged and physically deleted.
            
Logging is done by the deallocation of data pages in which the data exists.
        

            
Allows the deletion of rows that does not violate a foreign key constraint.
            
Does not allow the 
truncation of a table that is referenced by a foreign key constraint.  
The foreign key constraint needs to be dropped first, then TRUNCATE the 
table, and then re-create the constraint.
        

            
Does not reset the identity column to the default seed value.
            
Resets any identity column in the truncated table to the default seed value.
        

            
Allows the conditional deletion of rows by specifying the condition in the WHERE clause.
            
Deletes all rows in the table and cannot specify a conditional deletion of rows.
        

            
The DELETE command is a Data Manipulation Language (DML) Command.
            
The TRUNCATE command is a Data Definition Language (DDL) Command.
        

            
Fires any DELETE TRIGGER defined on the table where the rows are deleted.
            
Since the TRUNCATE command is a DDL command, any DELETE TRIGGER is not fired.



Uses of the RIGHT String Function
The RIGHT string function returns the right part of a character string with the specified number of characters. The syntax of the RIGHT string function is as follows:

RIGHT( <character expression>, <integer expression> )
The first parameter of the RIGHT string function is a 
character expression of character or binary data type from which to 
extract characters.  The <character expression> parameter can be 
of any data type, except TEXT or NTEXT, that can be implicitly converted
 to VARCHAR or NVARCHAR.  The second parameter is a positive integer 
expression indicating the number of characters of the <character 
expression> to extract and return.


Here are a few examples on the uses of the RIGHT string function:



Usage #1 : Extract Last Name from a Full Name
DECLARE @FullName VARCHAR(100) = 'Donald Duck'
SELECT RIGHT(@FullName, CHARINDEX(' ', REVERSE(@FullName)) - 1) AS [LastName]
LastName
----------
Duck


Usage #2 : Extract File Extension from a File Name
DECLARE @FileName VARCHAR(100) = 'sqlserver.exe'
SELECT RIGHT(@FileName, CHARINDEX('.', REVERSE(@FileName))) AS [FileExtension]
FileExtension
---------------
.exe


Usage #3 : Extract the Page Name from a URL
DECLARE @PageName VARCHAR(2000) = 'http://www.sql-server-helper.com/default.aspx'
SELECT RIGHT(@PageName, CHARINDEX('/', REVERSE(@PageName)) - 1) AS [PageName]

PageName
--------------
default.aspx


Usage #4 : Right-Justify a String
DECLARE @Header1 VARCHAR(25) = 'SQL Server Helper, LLC'
DECLARE @Header2 VARCHAR(25) = '1234 1st Street'
DECLARE @Header3 VARCHAR(25) = 'Somewhere City, NY 01001'
PRINT RIGHT(SPACE(25) + @Header1, 25)
PRINT RIGHT(SPACE(25) + @Header2, 25)
PRINT RIGHT(SPACE(25) + @Header3, 25)

   SQL Server Helper, LLC
          1234 1st Street
 Somewhere City, NY 01001

Usage #5 : Pad a Numeric Value with Leading Zeros When Converted to a String
DECLARE @StudentNumber INT = 12498
SELECT RIGHT('0000000000' + CAST(@StudentNumber AS VARCHAR(10)), 10) AS [StudentNumber]
StudentNumber
----------------
0000012498



Uses of the LEFT String Function
The
 LEFT string function returns the left part of a character string with 
the specified number of characters. The syntax of the LEFT string 
function is as follows:


LEFT ( <character expression>, <integer expression> )
The first parameter of the LEFT string function is an expression of 
character or binary data. It can be of any data type, except TEXT or 
NTEXT, that can be implicitly converted to VARCHAR or NVARCHAR. The 
second parameter of the LEFT string function is a positive integer 
expression that specifies how many characters of the to extract and return.


Here are a few examples on the uses of the LEFT string function:



Usage #1 : Extract First Name from a Full Name
DECLARE @FullName VARCHAR(100) = 'Mickey Mouse'
SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName]

FirstName
-----------
Mickey


Usage #2 : Extract the Local Name or the User Name of the Recipient in an Email Address
DECLARE @EmailAddress VARCHAR(100) = 'sqlserverhelper@sql-server-helper.com'
SELECT LEFT(@EmailAddress, CHARINDEX('@', @EmailAddress) - 1) AS [UserName]

UserName
-------------------
sqlserverhelper


Usage #3 : Extract the Domain Name from a URL
DECLARE @URL VARCHAR(100) = 'http://www.sql-server-helper.com/default.aspx'
SELECT LEFT(REPLACE(@URL, 'http://', ''), 
       CHARINDEX('/', REPLACE(@URL, 'http://', '')) - 1) AS [DomainName]

DomainName
---------------------------
www.sql-server-helper.com


Usage #4 : Extract the Area Code from a Phone Number
DECLARE @PhoneNumber VARCHAR(20) = '(555)987-6543'
SELECT LEFT(REPLACE(@PhoneNumber, '(', ''), 3) AS [AreaCode]

AreaCode
---------
555


Usage #5 : Left and Right Justify a String
DECLARE @Contents TABLE ( 
    [Title]         VARCHAR(50),
    [PageNumber]    INT
)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Introduction', 1)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Table of Contents', 2)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Index', 100)

SELECT LEFT([Title] + REPLICATE('.', 100), 100) + 
       RIGHT('     ' + CAST([PageNumber] AS VARCHAR(5)), 5)
FROM @Contents

Introduction......................................    1
Table of Contents.................................    2
Index.............................................  100



Uses of the Modulo (%) Operator
The
 modulo (%) operator returns the remainder of one number divided by 
another. The syntax of the modulo (%) operator is as follows:


<dividend> % <divisor>


The <dividend> is the numeric expression to divide and 
it must be a valid expression of any one of the data types in the 
integer and monetary data type categories, or of the numeric data type.  The <divisor>
 is the numeric expression to divide the dividend by and must be a valid
 expression of any one of the data types in the integer and monetary 
data type categories, or of the numeric data type.


Here are a few examples on the uses of the modulo (%) operator:



Usage #1 : Generate a Random Number
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1 AS [RandomNumber]

Usage #2 : Determine if a Number is Odd or Even
DECLARE @Input INT
SET @Input = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1
SELECT @Input AS [Input], CASE WHEN @Input % 2 = 0 THEN 'Even' ELSE 'Odd' END


Usage #3 : Determine if a Date/Year is a Leap Year
DECLARE @Year INT = YEAR(GETDATE())
SELECT CASE WHEN (@Year % 4 = 0 AND @Year % 100 != 0) OR @Year % 400 = 0
            THEN 'Leap Year'
            ELSE 'Not a Leap Year'
       END AS [IsLeapYear]


Usage #4 : Determine if a Number is a Palindrome
DECLARE @Input INT = 123454321
DECLARE @ReversedInput INT = 0
DECLARE @TempInput INT
SET @TempInput = @Input
WHILE @TempInput > 0
BEGIN
    SET @ReversedInput = @ReversedInput * 10 + @TempInput % 10
    SET @TempInput = @TempInput / 10
END
SELECT @Input AS [Input], CASE WHEN @Input = @ReversedInput
                               THEN 'Palindrome'
                               ELSE 'Not a Palindrome' END AS [Palindrome]


Usage #5 : Get the Greatest Common Factor (GCF)
DECLARE @Dividend   INT = 1989
DECLARE @Divisor   INT = 867
DECLARE @Remainder   INT = -1

WHILE @Remainder != 0
BEGIN
    SET @Remainder = @Dividend % @Divisor
    SET @Dividend = @Divisor
    SET @Divisor = @Remainder
END

SELECT @Dividend AS [GCF]

GCF
-----
51


Usage #6 : Determine if a Number is a Prime Number
DECLARE @Input   INT
DECLARE @IsPrime  BIT = 1
DECLARE @Factor   INT = 2

SET @Input = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 3
WHILE @Factor <= SQRT(@Input)
BEGIN
    IF @Input % @Factor = 0
    BEGIN
        SET @IsPrime = 0
        BREAK
    END

    SET @Factor = @Factor + 1
END

SELECT @Input AS [Input], @IsPrime AS [IsPrime]


Usage #7 : Determine if a Number is Divisible by Another Number
DECLARE @Dividend  INT
DECLARE @Divisor  INT

SET @Dividend = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 1000 + 100
SET @Divisor  = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 10 + 1
SELECT @Dividend AS [Dividend], @Divisor AS [Divisor],
       CASE WHEN @Dividend % @Divisor = 0
            THEN 'Divisible'
            ELSE 'Not Divisible'
       END


Uses of the REPLACE String Function
The
 REPLACE string function replaces all occurrences of the second 
specified string expression in the first string expression with a third 
expression. The syntax of the REPLACE string function is as follows:


REPLACE ( <string_expression_1>, <string_expression_2>, <string_expression_3> )
The first parameter of the REPLACE string function, 
<string_expression_1>, is the string expression to be searched, 
the second parameter, <string_expression_2>, is the string 
expression to try to find, and the third parameter, 
<string_expression_3>, is the replacement string expression.  All 
three parameters can be of character or binary data.  If any of the 
parameters is NULL, the REPLACE string function returns NULL.


Here are a few examples on the uses of the REPLACE string function:



Usage #1 : Count the Occurrence of a Character
DECLARE @Input VARCHAR(50) = 'SHE SELLS SEASHELLS AT THE SEASHORE'
DECLARE @Char CHAR(1) = 'E'
SELECT LEN(@Input) - LEN(REPLACE(@Input, @Char, '')) AS [CharCount]

CharCount
-----------
7


Usage #2 : Count the Occurrence of a String
DECLARE @Input VARCHAR(50) = 'I SCREAM, YOU SCREAM, WE ALL SCREAM FOR ICE CREAM'
DECLARE @String VARCHAR(10) = 'SCREAM'
SELECT (LEN(@Input) - LEN(REPLACE(@Input, @String, ''))) / LEN(@String) AS [StringCount]

StringCount
-------------
3


Usage #3 : Count the Number of Words Within a String
DECLARE @Input VARCHAR(50) = 'SHE SELLS SEASHELLS AT THE SEASHORE'
SELECT LEN(@Input) - LEN(REPLACE(@Input, ' ', '')) + 1 AS [WordCount]

WordCount
-----------
6


Usage #4 : Trim Leading Zeros
DECLARE @Input VARCHAR(20)
SET @Input = '0000120-3100'
SELECT REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0')


Usage #5 : String Character Translation
DECLARE @Input VARCHAR(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @Output VARCHAR(26) = '!@#$%^&*()[]{}\|;:<>,./?`~'
DECLARE @String VARCHAR(50) = 'THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG'

SELECT @String = REPLACE(@String, SUBSTRING(@Input, [Number], 1), SUBSTRING(@Output, [Number], 1))
FROM [master].[dbo].[spt_values]
WHERE [Type] = 'P' AND [Number] != 0 AND [Number] <= LEN(@Input)
SELECT @String AS [String]

String
----------------------------------------------
>*% ;,(#[ @:\/} ^\? ),{|< \.%: >*% ]!~` $\&





I want to display all the employees who report to a person.

My data is like a tree structure.



eid   ename   mgrid
 1    Emp1     0
 2    Emp2     1
 3    Emp3     1
 4    emp4     2
 5    emp5     3
 6    emp6     4

For example, if I give eid as 2, I need report as:



eid   ename   mgrid
 4    emp4     2
 6    emp6     4

I need this data in the report because

- eid 4's mgrid is 2

- eid 6's mgrid is 4.


How to do that using SQL?



Suppose the given eid is 2. So we are looking for all nodes that 
are in the subtree of 2. Here is one solution which uses nested subqueries:



select eid
     , ename   
     , mgrid
  from employees as t1
 where mgrid = 2
    or exists 
       ( select * 
           from employees as t2
          where eid = t1.mgrid 
            and (
                mgrid = 2 
             or exists 
                ( select * 
                    from employees as t3
                   where eid = t2.mgrid 
                     and mgrid = 2 
                )                 
                ) 
       ) 

In effect, the query returns all employees:



whose manager is 2, or 
whose manager's manager is 2, or
whose manager's manager's manager is 2





empid and managerid in same table then how to do this?

Consider the emp table as below




 
 


  
eid
  
ename
  
mgrid
 

  
1
  
Emp1      
  
0
 

  
2
  
Emp2      
  
1
 

  
3
  
Emp3      
  
1
 

  
4
  
Emp4      
  
2
 

  
5
  
Emp5      
  
3
 

  
6
  
Emp6      
  
4
 

  
7
  
Emp7      
  
5
 

  
8
  
Emp8      
  
2
 

  
9
  
Emp9      
  
3
 

  
10
  
Emp10     
  
4

 


select e.eid,e.ename employee,m.ename manager from empmanager e join empmanager m 
on m.eid=e.mgrid


(OR)

select eid
     , ename,( select m.ename
           from empmanager as m
          where m.eid = e.mgrid )[Manager]   
       from empmanager e


O/P:

 
 


  
eid
  
ename
  
mgr
 

  
2
  
Emp2      
  
Emp1      
 

  
3
  
Emp3      
  
Emp1      
 

  
4
  
Emp4      
  
Emp2      
 

  
5
  
Emp5      
  
Emp3      
 

  
6
  
Emp6      
  
Emp4      
 

  
7
  
Emp7      
  
Emp5      
 

  
8
  
Emp8      
  
Emp2      
 

  
9
  
Emp9      
  
Emp3      
 

  
10
  
Emp10     
  
Emp4      
 

Different ways to count rows from a table













a)Select count(*) from table_name












b)select sum(1) from table_name












c)select count(1) from table_name












d)DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS.





then 





select rows from sysindexes where object_name(id)='table_name' and indid<2












d)exec sp_spaceused 'table_name'




e)DBCC CHECKTABLE('table_name') 










To know the Object name in SQL


use database_name

select * from sysobjects where type = ‘U’


Similarly you can find out other objects created by user, simple change type = 


C = CHECK constraint 


D = Default or DEFAULT constraint 


F = FOREIGN KEY constraint 


L = Log 


FN = Scalar function 


IF = In-lined table-function 


P = Stored procedure 


PK = PRIMARY KEY constraint (type is K) 


RF = Replication filter stored procedure


S = System table 


TF = Table function 


TR = Trigger 


U = User table ( this is the one I discussed above in the example)


UQ = UNIQUE constraint (type is K) 


V = View 


X = Extended stored procedure


   


Basic of Cursor Implementation:

declare @eid int
declare empid cursor for
select id from employees
open empid 
fetch next 
from empid into @eid
while @@fetch_status=0
begin 
print @eid
fetch next 
from empid into @eid
end
close empid
deallocate empid

Steps for CURSOR:
DECLARE: Defines a CURSOR with standard SELECT statement. This must be done before you open the CURSOR.

OPEN: physically open the CURSOR and received the record set exist in table at the time of opening the CURSOR.

FETCH: CURSOR always points to one row at a time and FETCH is retrieve the value from that row to manipulate it further.

CLOSE: CLOSE will release the lock on table made by CURSOR. If you wish than you can re-open CURSOR after closes it.

DEALLOCATE: Once you are done with CURSOR, do DEALLOCATE it and removes the memory from the server. You can open the CURSOR once you close it but can’t re-open CURSOR once you DEALLOCATE it.

Type of CURSOR:

Static: This is lowest type of CURSOR and used to use for finding data and generating reports. Once getting the data into the CURSOR you will not identify any modification done in data after retrieving it because it make a copy of your data into the temp table of tempDB.

Forward_Only: This is the default type of CURSOR and really very similar to Static CURSOR the only difference you will find it, it will move forward only. In short, this CURSOR will scroll from first to last no other movement supported.

Fast_Forward: this is a mixture of Forward_Only and Read_Only.

Dynamic: this CURSOR will be bit slow as it will accept any data modification done by any user even after you fetch the data because you scroll around the CURSOR. Data membership, value and its order will be changed in each FETCH if any data modification has been done in record set.

Keyset-Driven: when you open this CURSOR, membership key and order of row are fixed in CURSOR.

Output Vertical to horizantal:

IF table have data  1
                              2
                              3
                               4
                               5
But u want to display like here means   1,2,3,4,5

use below:
select id,name,salary  from employees----table


DECLARE @name VARCHAR(1000)

SELECT @name = COALESCE(@name,'') + Name + ';'  
from employees

select @name

           

SQL Server Setting Triggers Firing Order:

Sometimes we have multiple triggers on the same event(s). In that case we can't predict the firing order of triggers. Sometimes the firing order of trigger(s) is important for us to implement business logic. To solve this issue, In SQL Server we have option to set the firing order of triggers on same event(s).

Syntax for setting trigger firing order

 sp_settriggerorder @triggername='trg_name', @order='FIRST|LAST|NONE', @stmttype='INSERT|UPDATE|DELETE|CREATE_INDEX,ALTER_INDEX', @namespace='DATABASE|SERVER|NULL' 



Simple example for setting trigger firing order

 CREATE TABLE dbo.TestTable (
ID int NOT NULL,
Description varchar(100)
)
GO
--Now create triggers on above created table at same event INSERT
CREATE TRIGGER dbo.trg_i_TriggerOrder1
ON dbo.TestTable
AFTER INSERT
As
PRINT 'I will be fired first.'
 GO
CREATE TRIGGER dbo.trg_i_TriggerOrder2
ON dbo.TestTable
AFTER INSERT
AS
PRINT 'I will be fired last.'
GO
CREATE TRIGGER dbo.trg_i_TriggerOrder3
ON dbo.TestTable
AFTER INSERT
AS
PRINT 'I won''t be first or last.'
GO 



Set Triggers Firing Order

 --Now set triggers firing orders 
EXEC sp_settriggerorder 'dbo.trg_i_TriggerOrder1', 'First', 'INSERT'
EXEC sp_settriggerorder 'dbo.trg_i_TriggerOrder2', 'Last', 'INSERT'
--The order of firing the third trigger 'dbo.trg_i_TriggerOrder3' will be between above two 



 --Insert data to see trigger firing order
INSERT dbo.TestTable(ID,Description)VALUES (1,'Trigger firing order') 

Remove Duplicates in a Table:

Sometimes we required to remove duplicate records 
from a table although table has a UniqueID Column with identity. In this
 article, I would like to share a best way to delete duplicate records 
from a table in SQL Server.
Suppose we have below Employee table in SQL Server. 

 

 CREATE TABLE dbo.Employee
( 
EmpID int IDENTITY(1,1) NOT NULL, 
Name varchar(55) NULL, 
Salary decimal(10, 2) NULL, 
Designation varchar(20) NULL
 ) 


The data in this table is as shown below:

 

with a
as
(
select row_number() over(partition by name,salary order by name) [dup]
, * from emp
)
select * from a
where dup>1

Enable Intellisense in SQL Server 2005, 2008:


By default, SQL Server 2000, 2005, 2008 and 2008R2 
doesn't provide intellisense like Visual Studio. While writing T-SQL 
statements, we need to recall the database table structure (including 
columns, their data types, size) and other database objects structure 
like stored procedure, view and functions again and again. Sometimes it 
becomes tedious task.
Don't worry, we have some tools for enabling intellisense in SQL Server like as SQL Complete, SQL Developer Bundle that are free to use and also have their standard versions for advanced features.


SQL Complete
This is a very use ful tools and provide good intellisense help in 
SQL Server. First download its free or standard version and installed 
it. After installing you will see a new menu option "SQL Complete" in 
SQL Server menu as shown in fig.


Now, whenever you will wrie your T-SQL query, it will provides the intellisense help like Visual Studio like as :

 



SQL Developer Bundle
This is also very use ful tools and provide good intellisense help in
 SQL Server. First download its free or standard version and installed 
it. After installing you will see a new menu option "SQL Source Control 
and SQL Search" in SQL Server menu as shown in fig.


Now, whenever you will wrie your T-SQL query, it will provides the intellisense help like Visual Studio like as :

 


Drop all tables, stored procedure, views and triggers:


Sometimes, there is a case, when we need to remove 
all tables, stored procedure, views and triggers completely from the 
database. If you have around 100 tables, stored procedure and views in 
your database, to remove these, completely from database became a 
tedious task. In this article, I would like to share the script by which
 you can remove tables, stored procedure, views and triggers completely 
from database.

Remove all Tables

 -- drop all user defined tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" 



Remove all User-defined Stored Procedures

 -- drop all user defined stored procedures
Declare @procName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'p' 
Open cur 
Fetch Next From cur Into @procName 
While @@fetch_status = 0 
Begin 
 Exec('drop procedure ' + @procName) 
 Fetch Next From cur Into @procName 
End
Close cur 
Deallocate cur 



Remove all Views

 -- drop all user defined views
Declare @viewName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'v' 
Open cur 
Fetch Next From cur Into @viewName 
While @@fetch_status = 0 
Begin 
 Exec('drop view ' + @viewName) 
 Fetch Next From cur Into @viewName 
End
Close cur 
Deallocate cur 



Remove all Triggers

 -- drop all user defined triggers
Declare @trgName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'tr' 
Open cur 
Fetch Next From cur Into @trgName 
While @@fetch_status = 0 
Begin 
 Exec('drop trigger ' + @trgName) 
 Fetch Next From cur Into @trgName 
End
Close cur 
Deallocate cur 






SQL Commands Category:

Data Manipulation Language (DML)

These SQL commands are used to store, modify, and delete data from database tables. In this category we have INSERT, UPDATE, and DELETE commands.


Data Query Language (DQL)

These SQL commands are used to fetch/retrieve data from database tables. In this category we have only SEELCT command.


Transaction Control Language (TCL)

These SQL commands are used to handle changes which affect the data in database. Basically we use these commands with in the transaction or to make a stable point during changes in database at which we can rollback the database state if required. In this category we have SAVEPOINT, ROLLBACK and COMMIT commands.


Data Control Language (DCL)

These SQL commands are used to implement security on database objects like table,view,stored procedure etc. In this category we have GRANT and REVOKE commands.





5.Data Definition Language (DDL)

These SQL commands are used to create, modify, and drop the structure of database objects like table, view, procedure, indexes etc. In this category we have CREATE, ALTER, DROP and TRUNCATE commands.







BACKUP DATABASE METHOD:

A. Backing up to a disk device

The following example backs up the complete 
    AdventureWorks2012
   database to disk, by using FORMAT to create a new media set.




Transact-SQL




USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2012';
GO







B. Backing up to a tape device

The following example backs up the complete 
    AdventureWorks2012
  database to tape, appending the backup to the previous backups.




Transact-SQL




USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of AdventureWorks2012';
GO







C. Backing up to a logical tape device

The following example creates a logical backup device for a tape drive. The example then backs up the complete 
    AdventureWorks2012
   database to that device.




Transact-SQL




-- Create a logical backup device, 
-- AdventureWorks2012_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'AdventureWorks2012_Bak_Tape', '\\.\tape0'; 
USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
   TO AdventureWorks2012_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'AdventureWorks2012_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0', 
      NAME = 'Full Backup of AdventureWorks2012';
GO

D. Backing up the database and log

The following example backups up the 
    AdventureWorks2012
   sample database, which uses the simple recovery model by default. To support log backups, the 
    AdventureWorks2012
   database is modified to use the full recovery model.  

Next, the example uses sp_addumpdevice to create a logical backup device for backing up data, AdvWorksData, and creates another logical backup device for backing up the log, AdvWorksLog. 

The example then creates a full database backup to AdvWorksData, and after a period of update activity, backs up the log to AdvWorksLog. 





-- To permit log backups, before the full database backup, modify the database 
-- to use the full recovery model.
USE master;
GO
ALTER DATABASE AdventureWorks2012
   SET RECOVERY FULL;
GO
-- Create AdvWorksData and AdvWorksLog logical backup devices. 
USE master
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksData', 
'Z:\SQLServerBackups\AdvWorksData.bak';
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksLog', 
'X:\SQLServerBackups\AdvWorksLog.bak';
GO

-- Back up the full AdventureWorks2012 database.
BACKUP DATABASE AdventureWorks2012 TO AdvWorksData;
GO
-- Back up the AdventureWorks2012 log.
BACKUP LOG AdventureWorks2012
   TO AdvWorksLog;
GO

A simple T-SQL script for full backups

USE master
GO
BACKUP DATABASE [DatabaseForFullBackups] TO
DISK = N'C:\SQLBackups\Chapter3\DatabaseForFullBackups_Full_Native_2.bak'
WITH FORMAT, INIT, NAME = N'DatabaseForFullBackups-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

problem:

In a table I have one column named ‘value’ and another column named ‘xmlpath’ which will store xpath like this 



root\
root\first
root\second\one
root\second\two

Table:


INSERT INTO [pf].[dbo].[tbl_test]
       ([value]
       ,[tag])
 VALUES
       ('100','root\first'),
       ('200','root\second\one'),
       ('300','root\second\two')

GO


output will be like below:


<root>
<first>100</first>
<second>
      <one>200</one>
      <two>300</two>
</second>
</root>

Solution:


DECLARE @tbl_test TABLE
(
[value] VARCHAR(10)
       ,[tag] VARCHAR(500)
)

INSERT INTO @tbl_test
select '100','root\first'
union
select '200','root\second\one'
union
select '300','root\second\two'
       

;WITH CTE AS
(
SELECT SUBSTRING([tag],CHARINDEX('\', [tag])+1, ISNULL(NULLIF(CHARINDEX('\', [tag], CHARINDEX('\', [tag])+1),0)-1,LEN([tag])) - CHARINDEX('\', [tag]))  GroupName, *
FROM @tbl_test AS TT
),
CTE1 AS
(
 SELECT  GroupName  GroupName, 
       ((SELECT 
       CASE WHEN CHARINDEX('\', [tag], 6) > 6 THEN 
       '<' + SUBSTRING([tag]+'>', ISNULL(NULLIF(CHARINDEX('\',[tag],CHARINDEX('\', [tag], 6)),0),LEN([tag])+1)+1, LEN([tag]) ) ELSE 
       '' END + value +
       CASE WHEN CHARINDEX('\', [tag], 6) > 6 THEN
        '</'
       +  SUBSTRING([tag], ISNULL(NULLIF(CHARINDEX('\',[tag],CHARINDEX('\', [tag], 6)),0),LEN([tag])+1)+1, LEN([tag]) )+ '>' ELSE '' END
        FROM CTE T1
       WHERE T1.GroupName = T.GroupName
 FOR XML PATH(''),TYPE).value('.','varchar(500)'))  XMLCOL
       FROM CTE AS T
       GROUP BY GroupName
)

 SELECT '<root>'+((
 SELECT '<'+ GroupName + '>' + XMLCOL + '</' + GroupName+ '>' 
 FROM cte1
 FOR XML PATH(''),TYPE).value('.','varchar(max)')) + '</root>'

check if all characters in string are same:

 
Method 1:
declare @string varchar(50)
set @string = 'aaaaaaaa'

select
 @string,
 case
 when patindex('%[^'+left(@string,1)+']%',@string) = 0
 then 'All same'
 else 'Not all same'
 end

Method 2:
declare @string varchar(50)
set @string = 'aabaaaaaa'

SELECT CASE WHEN LEN(REPLACE(@STRING, LEFT(@String,1),'')) = 0
THEN 'All Same' ELSE 'NOT ALL SAME' END

sp_refreshview (Transact-SQL)

SQL Server 2012

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Syntax

---

          

sp_refreshview [ @viewname = ] 'viewname' 
        

Arguments

---

[ @viewname= ] 'viewname'

Is the name of the view. viewname is nvarchar, with no default. viewname can be a multipart identifier, but can only refer to views in the current database.

Return Code Values

---

0 (success) or a nonzero number (failure)

Remarks

---

If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

Permissions

---

Requires ALTER permission on the view and REFERENCES permission on common language runtime (CLR) user-defined types and XML schema collections that are referenced by the view columns.

Examples

---

A. Updating the metadata of a view

The following example refreshes the metadata for the view Sales.vIndividualCustomer.

USE AdventureWorks2012;
GO
EXECUTE sp_refreshview N'Sales.vIndividualCustomer';

B. Creating a script that updates all views that have dependencies on a changed object

Assume that the table Person.Person was changed in a way that would affect the definition of any views that are created on it. The following example creates a script that refreshes the metadata for all views that have a dependency on table Person.Person.

USE AdventureWorks2012;
GO
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''' 
FROM sys.objects AS so 
INNER JOIN sys.sql_expression_dependencies AS sed 
    ON so.object_id = sed.referencing_id 
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('Person.Person');

Count
Number of Rows for Tables of a Database 

Easiest way to get an exact value of
Number of Rows for all the
tables in a SQL Server database



1. Use DBCC UPDATEUSAGE - this
updates the values of rows for each partition in a table.

2. Use undocumented stored procedure sp_msForEachTable
and store the result set in a table.



Below is the query to get required output:



USE [DatabaseName]

GO



DECLARE @DynSQL NVARCHAR(255)


SET @DynSQL = 'DBCC
UPDATEUSAGE (' + DB_NAME() + ')' 

EXEC(@DynSQL) 



IF OBJECT_ID('tempdb..#T','U') IS NOT NULL

DROP TABLE #T

GO



CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)

GO



INSERT INTO #T 

EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1)
as TableName,COUNT(*) as NumberOfRows FROM ?'

GO 

SELECT * FROM #T ORDER BY NumberOfRows DESC



T-SQL
script for BULK INSERT 

Here is the T-SQL code
to BULK insert data from text file to SQL Table:



BULK INSERT [Database]..[TableName]



FROM 'D:\test.txt' --
Path of text file

WITH

(

   FIRSTROW = 1 

   ,BATCHSIZE = 10000 

   ,FIELDTERMINATOR = ','

   ,ROWTERMINATOR = '\n'

   ,LASTROW = 20

)

GO



Here is the description of Keywords used:



FIRSTROW 

Specifies the number of the first row to load. The default is the first row in
the specified data file.



BATCHSIZE

Specifies the number of rows in a batch. Each batch is copied to the server as
one transaction. If this fails, SQL Server commits or rolls back the
transaction for every batch. By default, all data in the specified data file is
one batch.



FIELDTERMINATOR

Specifies the field terminator to be used for char and widechar data files. The
default field terminator is \t. 



ROWTERMINATOR

Specifies the row terminator to be used for char and widechar data files. The
default row terminator is \r\n (newline character).



LASTROW

Specifies the number of the last row to load. The default is 0, which indicates
the last row in the specified data file. 





Time
Dimension 

After posting script for Date
Dimension, I thought of sharing script for Time Dimension as well.
Some times we need to drill down the Cube data by time hierarchy e.g.
AM/PM-->Hrs-->Min. Below script will create Time dimension and solve the
purpose.



USE [DatabaseName] 

GO



IF OBJECT_ID('DimTime') IS NOT NULL

DROP TABLE DimTime

GO





CREATE TABLE [dbo].[DimTime]

(

   [DimTimeSK] [int] NOT NULL,

   [Time] [varchar](11) NOT NULL,

   [Time24] [varchar](8) NOT NULL,

   [HourName] [varchar](5),

   [MinuteName] [varchar](8),

   [Hour] [tinyint],

   [Hour24] [tinyint],

   [Minute] [tinyint],

   [Second] [int],

   [AM] [char](2)

) ON [PRIMARY]



GO





DECLARE

   @DimTimeSK int,@Date datetime, @AM char(2),


   @hour24 tinyint, @hour tinyint, 

   @minute tinyint, @second int



SET @DimTimeSK = 0



WHILE @DimTimeSK < (60*60*24)

BEGIN

   SET @DimTimeSK = @DimTimeSK + 1

   SET @Date = DATEADD(second,@DimTimeSK,convert(datetime, '1/1/2007'))

   SET @AM = right(convert(varchar,@Date,109),2)

   SET @hour24 = DATEPART(hour,
@Date)

   SET @hour = CASE WHEN @AM = 'PM' THEN
@hour24 - 12 ELSE @hour24 END

   SET @minute = DATEPART(minute,
@Date)

   SET @second = DATEPART(second,
@Date)



   INSERT INTO dbo.DimTime

   (

      [DimTimeSK]

      ,[Time]

      ,[Time24]

      ,[HourName]

      ,[MinuteName]

      ,[Hour]

      ,[Hour24]

      ,[Minute]

      ,[Second]

      ,[AM]

   )



   SELECT

   @DimTimeSK AS [DimTimeSK]

   ,right('0'+
convert(varchar,@hour),2)
+ ':' +

   right('0'+
convert(varchar,@minute),2)
+ ':' + 

   right('0'+
convert(varchar,@second),2)
+ ' ' + @AM AS
[Time]

   ,convert(varchar,@Date,108) [Time24]

   ,right('0'
+ convert(varchar,@hour),2)
+ ' ' + @AM AS
[HourName]

   ,right('0'
+ convert(varchar,@hour),2)
+ ':' + 

   right('0'
+ convert(varchar,@minute),2)+
' ' + @AM AS
[MinuteName]

   ,@hour AS [Hour]

   ,@hour24 AS [Hour24]

   ,@minute AS [Minute]

   ,@second AS [Second]

   ,@AM AS [AM]

END

GO



Date
Dimension 

Below is the script to create Date
Dimension.



USE [DatabaseName] 

GO



IF OBJECT_ID('Date','U')
IS NOT NULL

DROP TABLE Date

GO





CREATE TABLE [dbo].[Date](

   [DateSK] [int] NOT NULL,

   [FullDate] [datetime] NOT NULL,

   [DateName] [char](11) NOT NULL,

   [DayOfWeek] [tinyint] NOT NULL,

   [DayNameOfWeek] [char](10) NOT NULL,

   [DayOfMonth] [tinyint] NOT NULL,

   [DayOfYear] [smallint] NOT NULL,

   [WeekdayWeekend] [char](7) NOT NULL,

   [WeekOfYear] [tinyint] NOT NULL,

   [MonthName] [char](10) NOT NULL,

   [MonthOfYear] [tinyint] NOT NULL,

   [CalendarQuarter] [tinyint] NOT NULL,

   [CalendarYear] [smallint] NOT NULL,

   [CalendarYearMonth] [char](7) NOT NULL,

   [CalendarYearQtr] [char](15) NOT NULL,

CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)

) ON [PRIMARY]

GO

RAISERROR('Table Date
created successfully!',0,1)



DECLARE @StartDate datetime,
@EndDate datetime

-- Set StartDate and EndDate as per your
requirement

SELECT @StartDate = '2009-01-01',
@EndDate = '2010-12-31'



WHILE (@StartDate <= @EndDate )

BEGIN

INSERT INTO Date 

SELECT 

   CAST(CONVERT(varchar(8),@StartDate,112) AS
int) DateSK

   ,@StartDate AS [Date]

   ,CONVERT(varchar(20),@StartDate,106)
AS DateName

   ,DATEPART(DW,@StartDate)  
[DayOfWeek]

   ,DATENAME(DW,@StartDate)
[DayNameOfWeek]

   ,DATENAME(DD,@StartDate) 
[DayOfMonth] 

   ,DATENAME(DY,@StartDate) 
[DayOfYear]

   ,CASE WHEN DATEPART(DW,@StartDate) IN
(1,7) THEN 'WeekEnd' 

             ELSE 'WeekDay' END     [WeekdayWeekend]

   ,DATEPART(WW,@StartDate)
[WeekOfYear]

   ,DATENAME(MM ,@StartDate)
[MonthName]

   ,DATEPART(MM
,@StartDate)   [MonthOfYear]

   ,DATEPART(QQ,@StartDate)    
[CalendarQuarter]

   ,DATEPART(YY
,@StartDate)    [CalendarYear]

   ,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as
varchar),2)  [CalendarYearMonth]

   ,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate)
[CalendarYearQtr]



   SET @StartDate = @StartDate +1

END

GO





Date Dimension is ready to use as soon as you execute this script in required
database.

You can create following Calendar Hierarchy in your dimension to provide
drill down featrues for Growth, Trends, and ToDate calculations (e.g. YTD, QTD,
MTD).

Year-->Quarter-->Month-->Week-->Date

Find
Largest Size Tables in a Database 

Below is the stored procedure to return largest tables of a
database.



IF OBJECT_ID('sp_LargestTables'
,'P') IS NOT NULL

DROP PROC
sp_LargestTables

GO

/***************************************************************

CREATE BY : Hari Sharma

PURPOSE : To get a list of tables according to
their size.

***************************************************************/

CREATE PROC
sp_LargestTables

(

   @n int = NULL,

   @IsSystemAllowed bit
= 0

)

AS

BEGIN

   SET NOCOUNT ON

   DECLARE @LOW int

   SELECT @LOW =
LOW 

   FROM
[master].[dbo].[spt_values] (NOLOCK) 

   WHERE [number]
= 1 AND [type] = 'E'



   IF
@n > 0 SET ROWCOUNT @n

   SELECT
TableName,[Row Count],[Size (KB)] FROM 

   (

      SELECT
QUOTENAME(USER_NAME(o.uid))
+ '.' + 

                    
QUOTENAME(OBJECT_NAME(i.id))


         AS TableName

         ,SUM(i.rowcnt) [Row Count]

         ,CONVERT(numeric(15,2),

          
 (((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)]

      FROM
sysindexes i (NOLOCK)

      INNER
JOIN sysobjects o (NOLOCK) 

         ON i.id = o.id AND


         ((@IsSystemAllowed
= 1 AND o.type IN
('U', 'S')) OR o.type = 'U') 

      WHERE
indid IN (0, 1, 255)

      GROUP
BY 

         QUOTENAME(USER_NAME(o.uid))
+ '.' + 

         QUOTENAME(OBJECT_NAME(i.id))

   ) AS Z

   ORDER BY [Size
(KB)] DESC



   SET ROWCOUNT
0

END

GO





How to use:



1. If you want all the user tables in the database with
largest db size then:

EXEC sp_LargestTables [No Need to pass parameters]



2. If you want only 3 tables in the database with largest db
size then:

EXEC sp_LargestTables 3



3. If you want only 20 tables in the database with largest
db size including system tables then:

EXEC sp_LargestTables 20,1



Track
SQL Database Growth 

In this article I am sharing a
simple T-SQL code to track database growth for specific database. This could be
a very simple query for SMEs but it can really help newbies:



/*************************************************

Purpose : Track Database Growth for a specific
DB

Create By : Hari Sharma

**************************************************/

SELECT 

   BackupDate = 

   CONVERT(VARCHAR(10),backup_start_date, 111) 

   ,SizeInMBs=FLOOR(backup_size/1024000)


FROM
msdb..backupset 

WHERE 

   database_name = DB_NAME()
--Specify DB Name

   AND type = 'd'

ORDER BY 

   backup_start_date desc



Moving
Database Files From One Drive to Another 

In practice, database files grows
everyday. Sometimes it occupy the complete disk and we may end up in
unsufficeint mamroy - leading to unexpected results.



In this blog, I'll explain how to transfer database files from one drive to
another. To explain, I'll create a test database TestDB. Here
is the command:



USE [master]

GO



IF DB_ID(N'TestDB') IS NULL

BEGIN

CREATE DATABASE [TestDB] ON PRIMARY 

(

   NAME = N'TestDB'

   ,FILENAME = N'C:\MSSQL\Data\TestDB.mdf'

   ,SIZE = 3MB

   ,MAXSIZE = 2048GB

   ,FILEGROWTH = 1024KB

)

LOG ON 

(

   NAME = N'TestDB_log'

   ,FILENAME = N'C:\MSSQL\Data\TestDB.ldf'

   ,SIZE = 3MB

   ,MAXSIZE = 2048GB

   ,FILEGROWTH = 10%

)

END

GO



Now check existing files location:



USE TestDB

GO

SELECT name, physical_name, state_desc 

FROM TestDB.sys.master_files


WHERE database_id = DB_ID(N'TestDB')

  



  

  

  

  

    

  

Now the database is created, you can create some tables and enter some
data into the database, if you wish, Otherwise proceed like this: 

  

Step1: Make the database OFFLINE. 



USE [master]

GO

ALTER DATABASE TestDB SET
OFFLINE



Step2: Move file Physically 

Now you need to move that file physically from existing folder to the new
the location. Open the parent folder (Here 'C:\MSSQL\DATA') , You can see both
mdf and ldf files', make sure that you cut the appropriate file, in this
example it is the Log file. Cut that "TestDB_log.LDF" file and paste
it on "D:\MSSQL\Data" 

  

Step3: Update the system reference 

Once you move the file physically , you need to update the system reference
using the ALTER DATABASE .. MODIFY FILE Command as shown below: 

  

ALTER DATABASE TestDB 

MODIFY FILE ( 

   NAME ='TestDB_log'

   ,FILENAME = 'D:\MSSQL\Data\TestDB.LDF'

)



Step5 : Make database ONLINE 

Last step is to make database online as shown below:



ALTER DATABASE TestDB
SET ONLINE

GO





CONCAT() IN
SQL SERVER 2012

SQL Server 2012 introduces a new string function CONCAT()
which allows you to concatenate up to 255 string or variable values in to one
single string. It requires a minimum of two input values. Good thing about the
function is that it takes care of implicitly converting the data values to a
string by following the data type conversion rules of SQL Server 2012. This
function would help eliminate the need of explicit data conversions when concatenating
two values.



 Note:
NULL values are implicitly converted to an empty string. If all the variables
passed as parameters in CONCAT function are NULL, an empty string of type
VARCHAR(1) is returned. 



SYNTAX: 



SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN])

-- Between 2 and 254 values can be passed. 



EXAMPLE:



DECLARE @FirstName NVARCHAR(10) = N'हरि' 

       ,@LastNameVARCHAR(10) = 'Sharma'

       ,@Cell VARCHAR(20) = '+1 (425) XXX-YYYY'

       ,@Age INT = 30

       ,@Delimiter VARCHAR(2) = SPACE(2)

SELECT CONCAT(@FirstName, @Delimiter, @LastName, @Delimiter, @Cell, @Delimiter, @Age)









OUTPUT:

　







You might have observed that there’s no data conversion being
performed in the above example.



The data returned by CONCAT function depends on the type of
valued passed. Below are some of the mapping input and output types:

  



 


  



Input Value / Type

  

Output Value / Type

 

  

SQL-CLR (TYPES &
  UDT)

  

NVARCHAR(MAX)

 

  

NVARCHAR(MAX)

  

NVARCHAR(MAX)

 

  

NVARCHAR(<=4000)

  

NVARCHAR(<=4000)

 

  

VARBINARY(MAX)

  

NVARCHAR(MAX)

 

  

ALL OTHER INPUT
  TYPES

  

VARCHAR(<=8000)
  *Unless one of the parameters is an NVARCHAR of any length and in that case,
  the output value would be in NVARCHAR(MAX)

 




NOTE: CONCAT function only works with SQL Server
2012 and later versions.


How
to find missing Identity/Sequence numbers using T-SQL? 



SQL
Developers, did you ever come across a situation where you need to find missing
identity or sequence number for a given table? For instance, someone deleted
few records from a table which has an IDENTITY column. Have you wondered how to
find those missing rows? In this blogpost, I am going to explain different ways
to find missing identity or sequence numbers.



First I
will create some sample data for demonstration purpose.

-- Prepare Test data --

SET NOCOUNT ON

IF OBJECT_ID('dbo.TestData') IS NOT NULL    DROP TABLE dbo.TestData

CREATE
TABLE dbo.TestData (

    ID INT IDENTITY(1, 1)
NOT NULL

)



DECLARE @Counter INT = 1

WHILE @Counter <= 15

BEGIN

    INSERT dbo.TestData DEFAULT VALUES

    SET @Counter += 1

END

 

SELECT
ID FROM dbo.TestData

 









  

Now
lets delete some rows manualy:

-- Now delete some records (IDs)

DELETE dbo.TestData WHERE ID IN (3,7,8,10,12,13)

 

--Verifiy the data

SELECT ID FROM dbo.TestData





Below are three different ways to identity missing values.



Find missing sequence numbers using Ranking Function:

-------------------------------------------

-- Option 1:
Using Ranking function

-------------------------------------------

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)



SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER()
OVER (ORDER BY column_id) SeqID from sys.columns) LkUp

LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE
t.ID is null
and SeqID < @MaxID



Here is the output:





 -- If there are less records in sys.columns and 

-- you need need larger result then use CROSS
JOIN

SELECT
SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER()
OVER (ORDER BY c1.column_id) SeqID 

FROM sys.columns c1

CROSS JOIN sys.columns c2) LkUp

LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID





Find missing sequence numbers using CTE:

-------------------------------------------

-- Option 2: Using Common Table Expression

-------------------------------------------

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

;WITH
CTE (MissingSeqID, MaxID) 

AS (

    SELECT 1 AS MissingSeqID, @MaxID

    UNION ALL

    SELECT MissingSeqID +
1, MaxID FROM CTE WHERE MissingSeqID < MaxID

    )

SELECT
MissingSeqID FROM
CTE

LEFT JOIN dbo.TestData t on t.ID = CTE.MissingSeqID

WHERE t.ID is NULL

GO




Find
missing sequence numbers using Tally Table:

This
is the most prefered way out of all the above options.

-------------------------------------------

-- Option 3: Using Tally Table

-------------------------------------------

DECLARE
@MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT
t.ID  MissingSeqID FROM dbo.Tally t

LEFT JOIN dbo.TestData td

ON td.ID = t.ID

WHERE td.ID IS NULL

AND t.ID < @MaxID








XML Data Inserted in a Table:







Script:

DECLARE @Xml XML




DECLARE @Emp TABLE(

        [ID] [int] NOT
NULL, 

        [EmpName] varchar(max) not null,

        [Sal] [int] NULL

)




SELECT @Xml = '<Record
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Emp><ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp></Record>'







insert into @emp




SELECT  1[ID],'/Record/Emp/EmpName' [EmpName],

        5000 [Sal]







select * from @Emp-------Display the XML Data inserted in a table