Download SQL Server 2012 Service Pack 1

Last week Microsoft released Service Pack 1 for Microsoft SQL Server 2012. If you have been waiting for the Service Pack 1 to be released, to apply on your existing SQL Server 2012 installations, then go ahead and download the Service Pack 1 from Microsoft Download Center link http://www.microsoft.com/en-us/download/details.aspx?id=35575.

As with all Service Packs for SQL Server, this Service pack can also be applied to all the editions of SQL Server 2012.. If you would like to know more about what are the new features that this SP1 brings to SQL Server 2012, read them here in the following link http://msdn.microsoft.com/en-us/library/bb500435. Look for the section New or Enhanced features in SQL Server 2012 SP1..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Create Linked Server to connect to a SQL Server Part – II

This blog is Part-II of last week’s blog post Create Linked Server to connect to a SQL Server Part – I. In this blog we shall learn two more ways of creating a Linked Server for a SQL Server destination.

In the first part, we saw how to create a Linked Server using graphical interface from SSMS. In that method, we created linked server by selecting Other data source as Server type. In this method of creating Linked Server, we shall choose SQL Server as Server type and know more details about what is the difference when creating this way.

When creating Linked Server in this way, keep in mind that Linked Server name we are going to enter in the text box should be the same as the target SQL Server. If the target SQL instance is a named instance, you should be entering the full instance name as Servername\Instancename.

Security and Server Options in this method is same as as we saw in the first part of this  blog, so I am avoiding explanation of those steps in this blog. Once you are done, choose OK to create the Linked Server.

Using T-SQL

You can also create a Linked Server using system stored procedure sp_addlinkedserver. Below screen shot shows the script that I used to create a linked server using the system stored procedure sp_addlinkedserver. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Create Linked Server to connect to another SQL Server Part – I

This is the first blog in a 2 part series on how to create Linked Server to connect to another SQL Server (Instance) and run queries using the created Linked Server. These 2 blogs will give you step by step instructions to create Linked Server on SQL Server 2012 using Graphical User Interface and T-SQL.  

Using graphical user Interface in SSMS

First we will look at the steps to create a Linked Server the easy way.  In Object Explorer , expand the tree view of the SQL Server, expand Server Objects and Right Click on Linked Servers and choose New Linked Server… from the pop-up menu (as shown below)

Then a dialog box is displayed, by default the General Page is displayed, where we can enter the name of the Linked Server that we are about to create in the Text box corresponding to Linked Server : In this example I am creating a  Linked Server with name as SAMPLEMART_LINSVR. 

Then, under Server type, choose Other data source option, (in the next blog we shall see when to choose SQL Server option) . From the drop down list corresponding to Provider, choose SQL Server Native Client 11.0.

Note: If you care creating a Linked Server to connect to another SQL Server in SQL Server 2008, then choose SQL Server Native Client 10.0, apart from that the rest everything is similar to what you see in this blog..

For Product name, enter SQLServer, for Data Source enter the name of the SQL Server Instance (the remote SQL Instance to which you want to connect), if the remote instance is a named instance, then enter the complete server name as machinename\instancename.

Next, select the Security page in the left side pane. Choose the last option Be made using this security context and enter the SQL Login Username and Password. This username with that password must exist on the remote server, prior to you using it here.

Next, select Server Options page. Choose appropriate value for the server options, in most cases the values are True or False which indicates whether the Linked Server being created should have that appropriate setting enabled or not. As an example, for Data Access, I selected True, indicating that we would like to access the data on the remote server using the Linked Server. If you would like to create a Linked Server now and do not want to access data using the Linked Server, you can choose False and then when the need arises to access data on the remote server, you can choose True.

Note that you can modify all of the options at a later time after the Linked Server is created. Once your selections are done, click OK to create the Linked Server.

If you do not see any error message, then your task of creating Linked Server was successful. You can find if it was created successfully by expanding the Linked Server node in Object Explorer and expanding the newly created Linked Server. You should be able to see the databases and also able to expand those databases and see the tables, etc (as shown below)

Querying data using a linked server

Now that we have created a linked server, we shall see how to query the data using the linked server. There are 2 ways we can query using a linked server; In this blog we are going to look at the easiest way to query a linked server.

Using 4 part Notation

When querying a table using linked server, it is easier to query with a 4 part notation which is LinkedServer.Database.Schema.Objectname. In a normal queries we usually use a 2 part notation as schema.objectname such as dbo.Employee. While querying data using a linked server we would have to prefix LinkedServer.databasename to the 2 part notation. Hence in the below query, I am including the Linked Server name SAMPLEMART_LINSVR followed by the database name (SampleMartDW) followed by schema (dbo) and table name DimEmployeeDetails..

Though this way of querying a linked server is easier, the queries will be slower when compared to the other way of querying a Linked Server.

You can read the 2nd part of this blog here @ Create Linked Server to connect to a SQL Server Part – II.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

New DMV in SQL Server 2012: sys.dm_server_services

In this blog we shall learn a new Dynamic Management View introduced in SQL Server 2012 called sys.dm_server_services. This dmv can be used to query the startup type, current status, last_startup_time, service accounts under which the service starts, is the service clustered and other related information.

SELECT * FROM sys.dm_server_services

The dmv returns output with 11 columns, for better readability, the output is broken down. The data displayed is for the 3 services, SQL Server, SQL Server Agent service and Full-text. The below screenshot displays only 2 rows (for SQL Server and SQL Server Agent) as Full-text is not installed.  The following are some of the important startup_type codes: 2 – Automatic, 3 – Manual, 4 – Disabled. Some of the status codes are 1 – Stopped, 4 – Running, 7 – Paused etc.. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Why should I use CONCAT when SQL Server can concatenate using + ?

Recently, I wrote a blog on CONCAT string function in 2012 and there were couple of interesting questions about the CONCAT function. I shall answer those questions in this blog, as it would be better to answer with examples.

The first question we will look to answer is, Why should we I use CONCAT function when SQL Server can concatenate using +?

When CONCAT is used, all values passed into the function are implicitly converted to String values and then concatenated and NULL values are implicitly converted into am empty string. To better understand this, let us look at an example.

Use the following code to create a table, note that this is similar to the code used in the actual blog, except the values passed into Address1 in the first 2 rows is NULL.

CREATE TABLE [dbo].[Customer_Address_New](
 [CustomerID] [bigint] NULL,
 [Address1] [varchar](50) NULL,
 [Address2] [varchar](50) NULL,
 [ZipCode1] [char](5) NULL,
 [ZipCode2] [char](4) NULL
 )

INSERT INTO Customer_Address_New
VALUES
(1,NULL,'Moon Walk Drive', 24578,2881 ),
(2,NULL,'Roof Top Lane', 54856,5421 ),
(3,'#3','Full Thottle Blvd', 90425,5782 ),
(4,'#4','Drive Slow Road', 18854,6502 )

Once you inserted the data, run the following 2 statements to and look at the results

SELECT CustomerID, CONCAT(Address1,' ',Address2) AS Address,
CONCAT(ZipCode1,'-',ZipCode2) AS ZIP
FROM Customer_Address_New
SELECT CustomerID, Address1 + ' ' + Address2 AS Address,
ZipCode1 + '-' + ZipCode2 AS ZIP
FROM Customer_Address_New

The output will look like this..

Note that the Address column is the result set from using +. By default SQL Server returns a NULL when you concatenate NULLs and Strings. You have to turn on the option SET CONCAT_NULL_YIELDS_NULL OFF and run the queries using + to get the desired results  as shown below.

Apart from the benefit of concatenating NULL values, CONCAT provides another benefit by implicitly converting every argument as string value, so it makes easy to get the output as expected without much fuss (I mean using CAST or CONVERT string functions). To look at this, let me give a very simple example. Run the following code.

DECLARE @DATE DATETIME, @STRINGDATE VARCHAR(20),@INTI INT

SET @DATE = GETDATE()
SET @INTI = 1234
SET @STRINGDATE = '10/10/2011'

SELECT  @INTI + ' ' +@DATE + ' ' +  @STRINGDATE 

SELECT CONCAT (@INTI , ' ', @DATE , ' ', @STRINGDATE)

You will see the following output.

If you see the results, the output we got when concatenated using +, is not we were looking for, in order to get the output returned by CONCAT, we have to use CAST and Convert functions to first convert the integer value 1234 to a string and so on, which is out of topic for this blog post.

Finally, to answer one more question on performance benefit of CONCAT, I haven’t yet tested the performance benefits of using CONCAT in SQL Server 2012 over “+”, so I cannot comment on it..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty