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

OFFSET and FETCH – New in SQL Server 2012

In this article we shall learn how to limit the numbers of rows returned by a query using OFFSET and FETCH clause introduced in SQL Server 2012. The following query is executed against AdventureWorks Database.

 SELECT SalesOrderID,SalesOrderDetailID,OrderQty,SpecialOfferID
 FROM [Sales].[SalesOrderDetail]
 ORDER BY SalesOrderDetailID

The following is the query result set when the above T-SQL code is executed.

Observe that the data is displayed sorted by the column SalesOrderDetailID, which is very much clear from the ORDER BY clause at the end of the query. This query will display all records in that table as we have not included any filtering condition.

You might be aware that using TOP clause we can restrict the query result set to as many rows we want it to display. Instead of the top n records, if we wanted to return x number of records from the middle of the result set, sorted on a particular column, we would have to do some additional manipulations and retrieve them. 

In SQL Server 2012, this can be achieved by using the OFFSET and FETCH clause at the end of the Select query, after ORDER BY clause. Let’s take a look at this sample query to understand this better..

 SELECT SalesOrderID,SalesOrderDetailID,OrderQty,SpecialOfferID
 FROM [Sales].[SalesOrderDetail]
 ORDER BY SalesOrderDetailID 
 OFFSET 5 ROW
 FETCH NEXT 10 ROW ONLY

The OFFSET clause sets how many rows needs to be skipped before displaying the result set. In this case we gave OFFSET 5 ROW, so the first 5 rows are skipped. FETCH NEXT x ROW ONLY, displays the next x records, if there are records inside the table. In our example script, we used 10 so 10 rows are displayed. If we mention 10000000 rows, and if there are only 1000 rows in the table, then only 995 rows will be displayed. A small clarification, the keywords ROW or ROWS both will work the same.

Note: Please note that this is correct as of SQL Server 2012 RC0.

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