Sql Server Version

by
Facebooktwitterredditpinterestlinkedinmail

Here is a super simple query that will get you your current SQL Server version. Enjoy!

SELECT	'SQL Server ' +
		CASE	CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(100)), 1, CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(100)))-1) AS INT)
				WHEN 8 THEN '2000'
				WHEN 9 THEN '2005'
				WHEN 10 THEN '2008'
				WHEN 11 THEN '2012'
				WHEN 12 THEN '2014'
		END	+ ' ' +
		CASE	CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(100))
				WHEN 'RTM' THEN ''
				ELSE CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(100)) + ' '
		END +
		CAST(SERVERPROPERTY('Edition') AS VARCHAR(100)) AS SQLServerVersion

SQL Server Rename Column

by
Facebooktwitterredditpinterestlinkedinmail

This article will cover how to rename a column in SQL Server. The function of a SQL Server rename column is not performed too often. Usually we do this when we roll out a new product and we want to make a column more generic.

 
SQL Server Rename Column (SQL Server 2005 and beyond)

They make this super simple. Just remember to put to single quotes around the parameters.

EXEC	sp_rename 'Animals.AnimalName', 'AnimalRealName', 'COLUMN'

The above SQL Server rename column code snippet has a few parts to it. Here is the breakdown:

  • sp_rename – This is a system stored procedure will rename different types of objects in SQL Server.
  • ‘Animals.AnimalName’ – This is the tablename and column that I would like to rename.
  • ‘AnimalRealName’ – This is the new name that I would like to call the column.
  • ‘COLUMN’ – This is the type of object that you would like to rename is. To rename a column, always leave this value ‘COLUMN’.

 
If you would like to know how to do a SQL Server rename column for SQL Server versions prior to 2005, please leave a note in the comments.

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms188351(v=sql.90).aspx

SQL Server 2014 Feature – Non-Clustered Indexes For Table Variables

by
Facebooktwitterredditpinterestlinkedinmail

This is truly an amazing feature to add to SQL Server 2014. I use table variables all the time and this was the only thing I didn’t like about them. Up to this point, SQL Server did not support having non-clustered indexes on table variables (the one with the name that starts with the @). If you wanted to do this, you had to create/use a temp table (the one with the name that starts with #).

With SQL Server 2014, they changed this and now allow the table variables to have non-clustered indexes as well. To do this, we simply add a little bit of extra code after the column declaration.

DECLARE	@AnimalTableVar	TABLE
( AnimalID		INT,
  AnimalName	VARCHAR(50)	INDEX IX_AnimalTempTable_AnimalName
)

In the sample above, we create a table variable called @AnimalTableVar. Then when we declare the column AnimalName, we add an index called IX_AnimalTempTable_AnimalName.

Here is how selecting from this table shows up in the execution plan!
Non-Clustered Indexes For Table Variables

Reference: http://msdn.microsoft.com/en-us/library/ms188927(v=sql.120).aspx

FORCE ORDER Query Hint

by
Facebooktwitterredditpinterestlinkedinmail

When querying out data, SQL Server has a ton of different query hints. One less commonly known query hint is FORCE ORDER. So here we will talk about what this query hint does and how we can use it efficiently.

FORCE ORDER
When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query. It will join the tables in the exact order that is specified in the query. Normally the SQL Server optimizer will rearrange your joins to be in the order that it thinks will be optimal for your query to execute.

To use the FORCE ORDER query hint, you will want to add this at the end of your query.

 
Example Without FORCE ORDER

SELECT	*
FROM	Habitat H 
INNER	JOIN AnimalHabitat AH ON H.HabitatID = AH.HabitatID
INNER	JOIN Animal A ON AH.AnimalID = A.AnimalID

This is the actual execution plan without the FORCE ORDER hint. You can see that SQL Server starts at the table Animal and AnimalHabitat tables, then goes to the Habitat table. If you look at our query above, we clearly list the tables in the order of Habitat, AnimalHabitat, then Animal.

Execution Plan Without Force Order

 
Example With FORCE ORDER

SELECT	*
FROM	Habitat H 
INNER	JOIN AnimalHabitat AH ON H.HabitatID = AH.HabitatID
INNER	JOIN Animal A ON AH.AnimalID = A.AnimalID
OPTION	(FORCE ORDER)

In the screenshot below, it shows the actual execution plan with the FORCE ORDER hint. In the new query, you can see that SQL Server starts with the Habitat and AnimalHabitat tables and then moves to the Animal table. It does this because when we turn on the FORCE ORDER option, it uses the order of the tables specified in the query syntax.

Execution Plan With Force Order

 
What Is This Used For
The FORCE ORDER query hint is only used when you want to override the way that SQL Server wants to execute this query. Normally you will just let SQL Server figure out how to get the data from the database. It does such a good job at it, that you do not usually need to override this functionality.

Occasionally SQL Server will not figure out the most optimal way to pull this data out. In this case you may want to try this query hint.

SQL Server Port

by
Facebooktwitterredditpinterestlinkedinmail

The SQL Server port is 1433.

The client application port that connects to SQL Server is usually a random port between 1024 and 5000. This port on the client computer is what port SQL Server will use to send data back to the client with.

 
Firewall Considerations
If you are looking to setup a firewall to work with SQL Server ports, you will need to make the following settings:

  • Allow traffic from any port greater than 1024 to port 1433
  • Allow traffic from port 1433 to any port greater than 1024