development speed

Script Multiple Objects [Easy]

by
Facebooktwitterredditpinterestlinkedinmail

Have you ever noticed that you can only highlight 1 object in the Object Explorer in SQL Server Management Studio? This usually is enough for most things you do, but if you want to script multiple objects at once… it becomes a pain. Well there is a way to script multiple objects at once, and it’s super easy.

  1. Open the Object Explorer Details screen by selecting View -> Object Explorer Details.
    Script Multiple Objects 1
  2. Highlight the folder with the objects that you would like script. A list of all objects in that folder will appear on the right. This works for tables, stored procedures, etc…
    Script Multiple Objects 2
  3. Highlight all the objects that you want, right click, and select Create To.
    Script Multiple Objects 3

Adding All Columns In A Table To A Query [Easy]

by
Facebooktwitterredditpinterestlinkedinmail

I wanted to share this quick tip with you on how to add a list of all the columns in a table to your query window in SQL Server Management Studio. This is the easiest way I know!

  1. Expand the Object Explorer to show the table that you want to add the columns for.
        Adding All Columns 1
  2. Select the Columns folder on to your query window.
        Adding All Columns 2
  3. Drag the whole folder on to your query window and just drop it where you want it. That’s it!
    Adding All Columns 3

Parsing A Delimited String In SQL Server

by
Facebooktwitterredditpinterestlinkedinmail

At some point in your life, you’ve probably had to parse a string inside SQL Server. Maybe you had a comma delimited string and you need the data inside a table. So, you go to stackoverflow and find some stored procedure that someone made that you don’t understand. It works, but yuck. Well… those days are over! In SQL 20016 they introduced a new table-valued function called STRING_SPLIT. This makes is very easy to parse a comma delimited string in SQL Server.

 
STRING_SPLIT Basics
The STRING_SPLIT function is really easy to use. It basically just takes 2 parameters. The first parameter is the string that you want to parse. The second parameter is the delimiter for parsing on.

The STRING_SPLIT function will always return a one column result set with a column named value.

Here’s an example of how to use it:

SELECT	value AS AnimalName
FROM	STRING_SPLIT('Dog,Cat,Bird,Walrus', ',')

[table width=”150″ colwidth=”150″ colalign=”left”]
value
Dog
Cat
Bird
Walrus
[/table]

 
If your input string has spaces by the delimiter, you just need to trim the value that you get from the function. Here’s an example of trimming the spaces and also parsing on a pipe character:

SELECT	value AS AnimalName
FROM	STRING_SPLIT('Dog |Cat |Bird | Walrus', '|')

 
 
Source: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

SQL Server Management Studio Keyboard Shortcuts

by
Facebooktwitterredditpinterestlinkedinmail

Although some of these keyboard shortcuts may seem a little odd at first… once you start using them I think that you’ll love them and use them all the time.

Ctrl + R – Show/Hide the results pane

Ctrl + N – New query window

Ctrl + Shift + U – Switch current selection to uppercase

Ctrl + Shift + L – Switch current selection to lowercase

F5 – Execute the currently selected query (or entire query if nothing is selected)

Ctrl + Tab – Switch to the next open tab

Ctrl + Shift + Tab – Switch to the previous open tab

Ctrl + Space – Pops up IntelliSense complete if it is not already open

Ctrl + Shift + R – Refresh local IntelliSense cache

F8 – Open the Object Explorer if not already open

 

 What other SSMS keyboard shortcuts do you use?

SQL Server Pagination

by
Facebooktwitterredditpinterestlinkedinmail

Paging through data is something that developers need to do frequently.  Until SQL Server 2012, the best way to achieve this is with the ROW_NUMBER function… and let’s face it… that wasn’t the easiest/most elegant thing to use.  Our prayers have been answered in SQL Server 2012.  The SQL Server team has come out with a better way of doing pagination using the OFFSET FETCH clause.

 
The OFFSET FETCH Clause

The OFFSET FETCH clause allows the client application to pull only a specified range of records.  To implement pagination using the OFFSET FETCH clause, it takes two parts… the OFFSET and the FETCH. 🙂

NOTE: To use the OFFSET FETCH clause for pagination, you must use an ORDER BY clause as well.

 
OFFSET

This part tells SQL Server to skip N number of rows in the result set.

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	50 ROWS

The above statement tells SQL Server to return all of the AnimalNames in the Animal table, but only return the names after the 50th.  So 51 and beyond.

 
FETCH

This part tells SQL Server how many records to return in the result set.  If you use FETCH, you always need to use OFFSET.

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	50 ROWS
FETCH   NEXT 10 ROWS ONLY

The above statement tells SQL Server to return the AnimalNames in the Animal table with row numbers 51-60.  The OFFSET clause tells it to skip 50 rows and the FETCH clause tells it to return 10 records.

 
Variable Row Counts

You are also able to use a variable for the record counts in the query.  If you wanted to do this, it would look like this:

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	@RowsToSkip ROWS
FETCH	NEXT @RowsToReturn ROWS ONLY

 
 
Reference: http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx