March 20, 2015

SQL Server Stored Procedure Recursion Limit

2 comments


In order to save you from a performance penalty, SQL Server has a hard limit set for recursion levels for Stored Procedures, Functions, Triggers  and Views. The limit is 32 levels and you can’t change it programmatically.

Here is a simple example demonstrating this limit:

CREATE PROCEDURE recurseProc (@cnt int)
AS
BEGIN
PRINT @cnt;
SET @cnt += 1;
EXEC recurseProc @cnt; 
END; 
GO

EXEC recurseProc 1;


stored-procedure-recursive


When you execute this code, you get an error after 32 recursions are over Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Note: You can work around this limitation by using a Common Table Expression. A CTE allows up to 100 levels of recursion.


 
  Feedback:

February 18, 2015

SQL Server - Find Physical Location of Records

0 comments


SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:

USE AdventureWorks2014
GO
SELECT *, %%physloc%% AS physloc
FROM Person.AddressType
ORDER BY physloc;


sql-physloc

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

SELECT *
FROM Person.AddressType
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)


The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.

sql-sys.fun_PhysLocCracker

If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:

EXEC sp_helptext 'sys.fn_PhysLocCracker'
which display the definition of sys.fn_PhysLocCracker
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
--    Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
    [file_id]    int not null,
    [page_id]    int not null,
    [slot_id]    int not null
)
as
begin

    declare @page_id    binary (4)
    declare @file_id    binary (2)
    declare @slot_id    binary (2)

    -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
    --
    select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
    select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
    select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))
   
    insert into @dumploc_table values (@file_id, @page_id, @slot_id)
    return
end

The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.


 
  Feedback:

February 15, 2015

SQL Server 2014: How Old is your Build?

0 comments


The @@Version is a very handy T-SQL function which gives you the Major SQL Server version, the Edition (Standard, Business Intelligence or Enterprise), Build Number (RTM, Service Packs, Cumulative Update Level), Build Release Date and Windows Version.

Let’s run this query along with @@ServerName which gives us the SQL Server Instance Name too.

SELECT @@SERVERNAME as [SQL Server Name],
@@VERSION as [SQL Version]

 
Running this query on my SQL Server brings up the following:

sqlserver-version

When you expand the SQL Version column, you get the following.

Microsoft SQL Server 2014 - 12.0.2254.0 (X64) Jul 25 2014 18:52:51  Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

where :

Major SQL Server Version = Microsoft SQL Server 2014
Build Number = 12.0.2254.0 (X64)
Build Release Date = Jul 25 2014 18:52:51
Edition = Enterprise Edition (64-bit)
Windows Version = Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
  
Some SQL Server 2014 RTM Branch Builds are as follows (ordered by Release Date):

Build Short Description Release Date (mm/dd)
11.00.9120 SQL Server 2014 CTP1 6/25/2013
12.00.1524 SQL Server 2014 CTP2 10/15/2013
12.00.2000 SQL Server 2014 RTM 4/1/2014
12.00.2342 Cumulative Update Package 1 4/21/2014
12.00.2370 Cumulative Update Package 2 6/27/2014
12.00.2381 KB 2977316 Security Update (QFE) 8/12/2014
12.00.2254 KB 2977315 Security Update (GDR) 8/12/2014
12.00.2402 Cumulative Update Package 3 8/18/2014
12.00.2405 KB 2999809 FIX 9/25/2014
12.00.2430 Cumulative Update Package 4 10/21/2014
12.00.2423 KB 3007050 FIX 10/22/2014
12.00.2436 KB 3014867 FIX 11/27/2014
12.00.2456 Cumulative Update Package 5 12/18/2014
12.00.2464 KB 3024815 FIX 1/5/2015
12.00.2472 KB 3032087 FIX 1/28/2015
 
The one in bold is installed on my PC and using this table with my Build number gives me an idea of how old is my build. There have been about 8 different builds released after I last installed SQL Server 2014 and I should update my latest build for better performance and security.


 
  Feedback:

December 16, 2014

SQL Tribal Awards

0 comments


Simple-Talk and SQLServerCentral.com are once again hosting the Tribal Awards. Inspired by the book Tribal SQL, the awards seek to recognize outstanding members of the SQL community in 11 categories.



Voting is open now and will close on January 2nd 2015. Winners will be announced on January 20th. In the spirit of Tribal SQL, the awards will also donate £250 to the charity Computers4Africa.org at the close of the event.

Link to Nomination form: https://www.surveymonkey.com/s/HWB5BWG
Link to more information: https://www.simple-talk.com/blogs/2014/11/21/the-tribal-awards-are-here-again/
Categories
Article that Saved My Bacon in 2014
Blog of the Year
Person You’d Most Like to Have as a Mentor
Best Presentation at any Tech Conference
Best Tech User Group
Best New Community Voice
Beyond the call of duty / Outstanding Contribution
Best Free PowerShell Script
Best Twitter Account To Follow
Best New Book About SQL
Best Outfit or Costume at a Tech Conference

These awards are inspired by the book Tribal SQL, published last year. Tribal SQL was created by 15 authors each contributing one chapter, and donating their royalties to a UK-based charity Computers4Africa.org


 
  Feedback:

December 08, 2014

Error Handling in SQL Server with THROW

1 comments


Continuing our series on SQL Server 2012, today we will talk about THROW. In versions prior to SQL Sever 2012, we used @@RAISE_ERROR to generate error messages dynamically or using the sys.messages catalog.

Consider the following example

SELECT ROUND(800.0, -3)

On executing this statement, you get the following error:

image

because the value does not fit into the decimal data type.

You can use @@RAISERROR to raise a message

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
DECLARE @ErrorMsg nvarchar(1000), @Severity int
SELECT @ErrorMsg = ERROR_MESSAGE(),
@Severity = ERROR_SEVERITY()
RAISERROR (@ErrorMsg, @Severity, 1)
END CATCH


Note: The old syntax of RAISERROR syntax specifying the error number and message number got deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead the new syntax RAISEERROR(50005, 10, 1) allowed you to specify the messageid, severity and state). For new applications use THROW.

However in SQL Server 2012, there’s a better way to this without much efforts – THROW. Consider the following code

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
THROW
END CATCH


image

As you can see, with just one word THROW, we were able to handle the error with grace and get a result too.


 
  Feedback:

November 20, 2014

User Defined Functions in SQL Server

0 comments


User Defined Functions play an important role in SQL Server. User Defined functions can be used to perform a complex logic, can accept parameters and return data. Many a times we have to write complex logic which cannot be written using a single query. In such scenarios, UDFs play an important role. For example, we can call user defined function in a where clause or use a user defined function in a JOIN [Where UDF returns a result set].

SQL Server supports two types of User Defined Functions as mentioned below –

- Scalar Functions – The function which returns a Scalar/Single value.
- Table Valued Functions – The function which returns a row set of SQL server Table datatype. Table Valued Functions can be written as –
  • Inline Table
  • Multi-statement Table
We will explore these functions today. I am using SQL Server 2012 for this demonstration, although you can use SQL Server 2005, 2008, 2008 R2 as well.

I have preconfigured Northwind database on my SQL Server instance. We will be using the following tables for creating different User Defined Functions –
  • Customers
  • Employees
  • Orders
  • Order Details
  • Products
Let’s start querying the above table. Open a new Query window and write the following commands –

tablequeries

Scalar Function

We will now create a scalar function, which returns the number of orders placed by a given customer. Write the following code in your query pad –

scalar1

The above function returns an integer value. To test this function, we will write some code as shown below –

scalartest1

Let us see another example which will fetch the number of orders processed by an employee for a given year. Write the following function in our query pad –

scalar2

We will test this function with different years for an employee as shown below –

scalartest2

Table Valued Functions

Now let’s try an Inline Table valued function. Inline Table valued functions can return a row set using SQL Server Table datatype. You cannot perform addition logic in inline table valued functions. We will fetch the product details purchased by a customer as shown below –

tvf1

To test this example we will use a select statement as shown below –

tvftest1

Another example of the Inline Table Valued Function is as shown below –

tvf2

To test this function, we will use different years as shown below –

tvftest2
clip_image001

We will now see a Multi-Statement Table Valued Function. This function can be used to perform additional logic within the function. The code is as shown below –

clip_image003

To use the Multi-Statement Table Valued function, use this code –

tvfmultistatementtest

There are couple of limitations you must consider before creating User Defined Functions. Some of them are as shown below –
  • You cannot modify the state of the database using UDFs
  • Unlike Stored Procedures, UDF can return only one single result set
  • UDF does not support Try-Catch, @ERROR or RAISERROR function
Summary – User-defined functions are routines which perform calculations, receive one or more parameters and return either a scalar value or a result set. In this article, we saw how to create User Defined Functions. We also saw how to use Scalar functions and Table Valued Functions [Inline Table Valued Functions and Multi-Statement Table Valued Functions].

Download the source code of this article (Github)


 
  Feedback:

October 30, 2014

Using SET Operators in SQL Server

1 comments


In this article, we will see how to use SET Operators in SQL Server. We will look at UNION, UNION ALL, INTERSECT and EXCEPT operators.

To start with SET Operators, we will first create two sample tables in our Northwind database. The script is shown as below -

USE Northwind
GO

CREATE TABLE Example1
(
    Name CHAR(1)
)

CREATE TABLE Example2
(
    Name CHAR(1)
)


--INSERT FOR Example1 Table
INSERT INTO Example1 VALUES('A')
INSERT INTO Example1 VALUES('B')
INSERT INTO Example1 VALUES('A')
INSERT INTO Example1 VALUES('C')
INSERT INTO Example1 VALUES('D')
INSERT INTO Example1 VALUES('E')


--INSERT FOR Example2 Table
INSERT INTO Example2 VALUES('X')
INSERT INTO Example2 VALUES('A')
INSERT INTO Example2 VALUES('B')
INSERT INTO Example2 VALUES('B')
INSERT INTO Example2 VALUES('C')
INSERT INTO Example2 VALUES('Z')
INSERT INTO Example2 VALUES('Y')


--SELECT ROWS FROM BOTH THE TABLES [Example1, Example2]
SELECT * FROM Example1
SELECT * FROM Example2


In the above script, we have created two tables, Example1 and Example2 and inserted some dummy data. We will now take a look at how to select the data from both the tables using the SET Operators.

UNION Operator

Let’s take an overview of our very first operator - UNION. The UNION operator combines the result of two or more queries and returns a single result set excluding the duplicate values. The following figure shows the same output -

sqlserver-set-operators
Write the following query in SQL Server Management Studio and observe the result -

union

The output of the above query is as shown here -

unionoutput

Now let’s use the two tables in the Northwind database with the name Dept and Emp.

empdept

We will write a query where we want to find all the unique cities and countries from Customers and Employees table -

union1

The output is shown below -

unionoutput1

UNIONALL Operator

However if you want to include the duplicate values of City and Country from Customers, as well as Employees, include ALL with UNION. The query is shown below -

unionall

Some rules to remember while working with UNION operator -

1. The number of columns must be same.
2. The data type of the columns must be same or implicitly convertible by database.

INTERSECT Operator

We will now take a look at INTERSECT operator. The INTERSECT operator takes the result of two queries and returns common rows which appears in both the result sets excluding the duplicate values. The following figure explains the same -

sqlserver-intersect
Let's try the query on our Example1 and Example2 table. The query is shown below -

intersect1

The output of the above INTERSECT query is shown below -

intersectoutput1

We will try the INTERSECT on Northwind database and find out all the common Cities and countries from Customers and Employees tables excluding duplicates. The query is as shown below -

intersect2

The output of the above query is as shown below -

intersectoutput2

EXCEPT Operator

We will now explore the last operator, the EXCEPT operator. The EXCEPT operator returns distinct rows from the first query which do not appear into the second result set. The following figure explains the same -

except1

The output of the above query is shown below -

exceptoutput1

We will now try the same query with our Northwind database. We will find out all the distinct cities from Employees tables which does not appear in Customers table. The query is shown below -

except2

The output of the above query is shown below -

exceptoutput2

And that’s it. In this article, we have seen how to use SET Operators in SQL Server. We have seen UNION, UNION ALL, INTERSECT and EXCEPT operator with some simple examples.


 
  Feedback:

September 15, 2014

Memory Optimized Tables and Transactions in SQL Server 2014

0 comments


In one of our previous articles “SQL Server 2014 – InMemory Table, Indexes and Stored Procedures”, we saw how to create in-memory tables and indexes. We also saw how to use stored procedures to interact with these tables.

In-memory tables are capable of storing multi-versions of a rows. When you perform transactions on these tables, internally it uses snapshot – based transaction isolation. In memory optimized tables, we always assume that the transactions will optimistically commit the operations.

As we don’t have locks, we don’t have blocking mechanism in memory optimized tables. Given these points, a question always arises that what if there are conflicts. For example, we have two transactions. The first transaction tries to write a row and the same row is getting written by another transaction as well. Then these conflicts are detected and the second transaction will always fail.

In the scenario of two transactions, you can read the old version of the row in case a transaction is occurring on the row. But if the row is not available, then the transaction simply fails.

There are number of Isolation Levels supported by transactions as described below –

· Snapshot Isolation – In this level of isolation, the starting reads are consistent and writes are always consistent.

· Repeatable Read – In this level of isolation, we have guarantee that the rows which we read during the start of the transaction, are the same at the end of the transaction. Other transaction will not be able to change them.

· Serializable – In this isolation level, if you scan the entire table at the end of the transaction before commit to check, if the new row is inserted, then the transaction will fail.

Consider few guide lines while working with Memory Optimized Table Transactions –
  • Always declare which Isolation Level you want you as described above.
  • In case of transaction failure, try writing logic for handling conflicts and validations in your transactions.
  • Try avoiding long running transactions.
In my previous article I have shown how to create Memory optimized table, indexes and stored procedure. I am using the same table “Employees” for demoing Memory Optimized Tables and Transactions.

Let’s open SQL Server Management Studio and write the below script.

emlployee-script

In the script, the query pad 1 contains a T-SQL command which is creating a table with the name Test. We have inserted two rows in the table. We then start a transaction which will update the row from the Test table which has ID=1. We are updating the row without committing it. In the second query pad, we try to access the row which is under the transaction and hence you will not see the row unless the first transaction is either committed or rollback.

Try committing the transaction and you will see the updated row in your second query pad as shown below –
sql-result1

Now let’s compare the same with Memory Optimized table which we have created in the previous article. Let’s write the following script in Query pad 1 –

recordbeforeupd

Now update the above row and change the city from London to New York but do not commit it as shown below –

BEGIN TRAN
UPDATE Employees WITH (SNAPSHOT)
    SET City='New York' 
    WHERE EmployeeID = 88569
COMMIT TRAN


Now let’s try accessing the updated row from our second query pad and see the output. You will see the older version of row as shown below –

recordbeforeupd

Commit the transaction and you will see the updated row as shown below –

afterupdate

Let’s update the city from our first query pad without committing it. And then try to update the same row from our second query pad and then we will see the difference –

script2

The output of the second query pad transaction execution is as shown below. The error says – The current transaction attempted to update a record that has been updated since this transaction…

error1

The above error clearly says that the row which the current transaction attempted to update cannot commit the transaction as the row has been modified outside since this transaction started.

So, in this situation, the second transaction will always fail. We can also write a retry logic which can validate the row and then modify it.

Summary – In this article we have seen how to implement a transaction with memory optimized tables in SQL Server 2014.


 
  Feedback:

August 20, 2014

SQL In the City Event

0 comments


SQL in the City is a unique event organized by RedGate for database developers and administrators. This event features some of the world’s top SQL Server speakers who share their wisdom about Database Development, Administration, Delivery and Best Practices. You can also have one-to-ones with Red Gate developers, and enjoy networking opportunities with the SQL Server community. As if that wasn’t tempting enough, you can learn more about the Red Gate tools that can resolve the SQL Server issues you face every day, direct from Red Gate developers and also do some drop-in hands-on labs and try out these awesome tools.

SQL in the City 2014 will be held in London in October and Seattle in November.

Who are the Speakers?

SQL Server Most Valued Professionals (MVP), Steve Jones and Grant Fritchey will be talking about best practices for database administration, development, and delivery. 10 additional speakers join the London line-up, including SQL Server MVP Ike Ellis and Visual Studio ALM MVP Brian Randell.
In Seattle, 15 additional speakers will present, including Microsoft Certified Masters Gail Shaw and Bob Pusateri.

For the full list of speakers, you can visit their website.


 
  Feedback:

August 17, 2014

List Empty Tables in SQL Server

0 comments


I was recently doing a clean up of my website database. I remember creating some tables on my database but never adding any new rows to it.

Here’s a simple query to list all empty tables in your SQL Server database that uses a Dynamic Management View called dm_db_partition_stats which returns page and row-count information for every partition in the current database.

;WITH EmptyRows AS
(
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
   FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
   GROUP BY OBJECT_ID
)
SELECT * FROM EmptyRows
WHERE [TotalRows] = 0


OUTPUT
  
image

Note that the results from the view is only approximate. If there are any active transactions that are inserting or deleting rows, the count may not include it.


 
  Feedback:
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions