Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday 15 November 2012

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

  1. 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

  1. CREATE TABLE dbo.TestTable (
  2. ID int NOT NULL,
  3. Description varchar(100)
  4. )
  5. GO
  6. --Now create triggers on above created table at same event INSERT
  7. CREATE TRIGGER dbo.trg_i_TriggerOrder1
  8. ON dbo.TestTable
  9. AFTER INSERT
  10. As
  11. PRINT 'I will be fired first.'
  12. GO
  13. CREATE TRIGGER dbo.trg_i_TriggerOrder2
  14. ON dbo.TestTable
  15. AFTER INSERT
  16. AS
  17. PRINT 'I will be fired last.'
  18. GO
  19. CREATE TRIGGER dbo.trg_i_TriggerOrder3
  20. ON dbo.TestTable
  21. AFTER INSERT
  22. AS
  23. PRINT 'I won''t be first or last.'
  24. GO

Set Triggers Firing Order

  1. --Now set triggers firing orders
  2. EXEC sp_settriggerorder 'dbo.trg_i_TriggerOrder1', 'First', 'INSERT'
  3. EXEC sp_settriggerorder 'dbo.trg_i_TriggerOrder2', 'Last', 'INSERT'
  4. --The order of firing the third trigger 'dbo.trg_i_TriggerOrder3' will be between above two
  1. --Insert data to see trigger firing order
  2. 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.
  1. CREATE TABLE dbo.Employee
  2. (
  3. EmpID int IDENTITY(1,1) NOT NULL,
  4. Name varchar(55) NULL,
  5. Salary decimal(10, 2) NULL,
  6. Designation varchar(20) NULL
  7. )
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

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

Remove all User-defined Stored Procedures

  1. -- drop all user defined stored procedures
  2. Declare @procName varchar(500)
  3. Declare cur Cursor For Select [name] From sys.objects where type = 'p'
  4. Open cur
  5. Fetch Next From cur Into @procName
  6. While @@fetch_status = 0
  7. Begin
  8. Exec('drop procedure ' + @procName)
  9. Fetch Next From cur Into @procName
  10. End
  11. Close cur
  12. Deallocate cur

Remove all Views

  1. -- drop all user defined views
  2. Declare @viewName varchar(500)
  3. Declare cur Cursor For Select [name] From sys.objects where type = 'v'
  4. Open cur
  5. Fetch Next From cur Into @viewName
  6. While @@fetch_status = 0
  7. Begin
  8. Exec('drop view ' + @viewName)
  9. Fetch Next From cur Into @viewName
  10. End
  11. Close cur
  12. Deallocate cur

Remove all Triggers

  1. -- drop all user defined triggers
  2. Declare @trgName varchar(500)
  3. Declare cur Cursor For Select [name] From sys.objects where type = 'tr'
  4. Open cur
  5. Fetch Next From cur Into @trgName
  6. While @@fetch_status = 0
  7. Begin
  8. Exec('drop trigger ' + @trgName)
  9. Fetch Next From cur Into @trgName
  10. End
  11. Close cur
  12. Deallocate cur 
SQL Commands Category:

  1. 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.
  2. Data Query Language (DQL)

    These SQL commands are used to fetch/retrieve data from database tables. In this category we have only SEELCT command.
  3. 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.
  4. 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.
-- 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' 
        
[ @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.
0 (success) or a nonzero number (failure)
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.
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.

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
 
 
 
 
 
 
 

No comments:

Post a Comment