T-SQL: Reminders 1

For the last few month I have spent most of my development time working with T-SQL.  This is just down to the nature of the current project I am working on.  On other projects I will spend far less time on T-SQL tasks and more time on front-end and middle-tier development.  Over time, especially when I am working on projects that don't rely heavily on SQL, I tend to forget some of the useful commands and tricks that are available in T-SQL.

This post is just as a reminder to myself about some of those commands that I have seen in the past and forgotten about until recently, and some new ones I would like to remember (or find here) in the future.

These first two I have seen before:

SET STATISTICS TIME ON

This will turn on time statistics which in turn will produce time info in the messages pane after a query is run.

Example:

SET STATISTICS TIME ON

WAITFOR DELAY '00:00:02.5'

GO

Output:

SET STATISTICS IO ON

This will turn on IO statistics which in turn will produce IO info in the messages pane after a query is run.

Example:

SET STATISTICS IO ON

SELECT * FROM Person p

INNER JOIN Address a ON p.PostalAddressID=a.AddressID

WHERE IDNumber=456

GO

Output:

Once you no longer want time or IO statistics you can turn the statistics off using:

SET STATISTICS TIME OFF

GO

or

SET STATISTICS IO OFF

GO

And these ones I haven't:

REPLICATE

This function takes 2 parameters: REPLICATE(string_expressing, integer_expression). Whatever is contained in the string expression will be replicated the number of times specified in the integer_expression.

Example:

SELECT REPLICATE('abc', 3) AS replicate_example

Output:




BATCH INSERT TEST DATA
This trick reminds me of the strange word function that allows you to create random text in a word document.  Next time you have word open try typing (followed by Enter): =rand(9)

To batch insert into a table:

Example:

CREATE TABLE MyTest (Id int identity(1,1) primary key, Name varchar(100) not null)

GO

INSERT MyTest (Name) VALUES (CAST(NEWID() AS varchar(36)))

GO 5

SELECT * FROM MyTest

Output: