Introduction:

Microsoft SQL Server 2012 introduces many features that help database administrators, database developers, and BI developers.

In this article, I will cover some of the new features for database developers in these main points:

• Database Engine Improvements
• Improvements to SQL Server Management Studio Debugging
• Changes to the Scope of Objects
• Conclusion
• References

Database Engine Improvements:

1. File Tables:

   When we open up SQL Server Management Studio, one of the first changes we notice for SQL Server 2012 is the addition of a new type of table called a File Table.

   File table allows us to make a connection between windows share and a database table such that any file that appears in the share will become a row item in the table.

   It allows us to run queries that tell us how many files we have in that shared location, what type of files, what size the files are, etc….

   Setting this up is a multiple step process:

   • Enable file stream: is The first step we have to do at the instance level.

     We will do that with the configuration manager tool, open properties of the instance we are interested in, and there is a tab for file stream, in there we should click on all of the checkboxes(enabling all features) as below

     
     

     Figure 1 – Enabling file stream at instance level

   • Enable file stream access for Transact-SQL and Win32 streaming access: is The second step we will also do at the instance level that can be done by running this brief line of code:

     1 2 3 4

     EXEC sp_configure filestream_access_level, 2 RECONFIGURE

   • There are a few steps we need to accomplish at the database-level:
     – we need to provide a new file stream file group.
     – we to enable Non-Transactional access.
     – we need to specify a directory for the file tables.
     – Then, we need to create a file table.
     
     Run the following script to perform database-level steps:
     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

     --Provide an FILESTREAM Filegroup at the Database Level ALTER DATABASE myDatabase ADD FILEGROUP FileTable_FG CONTAINS FILESTREAM; ALTER DATABASE myDatabase ADD FILE ( NAME= 'myFileTable_File', FILENAME = 'C: \SQLData\myFileTable_File' ) TO FILEGROUP FileTable_FG; GO --Enable Non-Transactional Access at the Database Level ALTER DATABASE myDatabase     SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'myFileTable_File' ) ----Specify a Directory for FileTables at the Database Level ALTER DATABASE myDatabase     SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'myFileTable_File’) CREATE TABLE DemoFileTable AS FILETABLE WITH ( FILETABLE_DIRECTORY = 'myFileTable_File', FILETABLE_COLLATE_FILENAME = database_default ); GO

     By that, we created a file table that contains all data about any files stored on the specified shared location.

2. Statistical Semantic Search:

   This new feature has been built in the existing feature “FULL – TEXT – SEARCH” but gives us more capabilities like extracting keyphrases and identifying similar documents.

   To get these capabilities, we need to perform some effort as the following:

   • First, we need to download semantic language statistics for Microsoft SQL server 2012 from the link below:
     Microsoft® SQL Server® 2012 SP1 Semantic Language Statistics
     and install it.
   • Then we need to attach the database file created by the previous installation to our SQL server instance.
   • Last step we need to register this database as the semantics database by running the following two lines of code:
   1 2 3 4

   EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'myDatabase’;   GO

   By that, we can create a full-text index as we used to do in the previous versions of SQL but with the new feature STATISTICAL_SEMANTICS like this example here:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   USE  [myDatabase] GO CREATE FULLTEXT CATALOG myCatalog AS DEFAULT; GO CREATE FULLTEXT INDEX ON  [dbo].[DemoFileTable] (     file_stream         TYPE COLUMN file_type         LANGUAGE 2057         STATISTICAL_SEMANTICS)     KEY INDEX  [PK__DemoFile__5A5B77D55C63AE2A] GO

   and we can use the three function supported with that new feature:

   • SEMANTICKEYPHRASETABLE: gets the Key Phrases of a Document
   • SEMANTICSIMILARITYTABLE: gets Similar documents
   • SEMANTICSIMILARITYDETAILSTABLE: gets the Key Phrases which Make Documents Similar

   And you can get more information from here Semantic Search (SQL Server)

3. New paging ad-hoc for queries:

   In this section, I’d like to talk about creating a query that returns data in small chunks.

   For example, if we want to display the first ten records then the next ten records then the next ten records and so on.

   This operation commonly is done in many applications and could sometimes require running some complex codes of the application.

   Now in SQL Server 2012, we have an additional tool where we can simply modify a database query that it will handle just retrieving part of the results, commonly known as paging.

   So if we have a query like the following one, for example, is retrieving 25000 rows of data:

   1 2 3 4 5

   Select id,lastname,firstname From students Order by id

   We didn’t want to view that much of data at the same time so we can view them ten records by ten records adding our new paging tool to the same query:

   1 2 3 4 5 6 7

   Select id,lastname,firstname From students Order by id Offset 0 rows Fetch next 10 rows only

   The previous query will retrieve the first ten records as shown in figure 2, then by changing the number following “offset” part to 10, we will get the next ten records and so on.

   
   

   Figure 2 – Using (offset-fetch) feature for query paging

   So by using this (offset-fetch) new feature, we can implement paging on the database side rather than having to do all of our paging in the application.

4. Error Handling’s new feature:

   Error handling means the way that we can detect and resolve any error could be in our code. First of all, let us look at the way it was previously handle:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   BEGIN TRY DECLARE @x AS INT; SET @x = 7/0 END TRY BEGIN CATCH DECLARE @message NVARCHAR (4000); DECLARE @severity INT; DECLARE @state INT; SELECT @message = ERROR_MESSAGE () SELECT @severity = ERROR_SEVERITY () SELECT @state = ERROR_STATE () RAISERROR (@message,@severity,@state) END CATCH;

   This code will retrieve an error message saying “Divide by zero error encountered.” And here is the new way it can be handled:

   1 2 3 4 5 6 7 8 9

   BEGIN TRY DECLARE @x AS INT; SET @x = 7/0 END TRY BEGIN CATCH THROW END CATCH;

   simply by that “THROW,” we will get the same error message.

   
   

   Figure 3 – Divide by Zero Error

   and here are some reasons to stop using “RAISERROR” and start using “THROW”:

   • The spelling of “RAISERROR” is odd.
   • “RAISERROR” has been deprecated.
   • “THROW” is more similar to other programming languages.
   • Simpler syntax.
   • “THROW” always triggers the catch block and stop the control flow rather than the “RAISERROR” which depends on the error severity.

   It is not a big change, all your error handling mindset and philosophy doesn’t have to change dramatically it is not more of a small syntactic change.

   But I encourage you to start using “THROW” and stop using “RAISERROR.”

IMPROVEMENT to SQL Server Management Studio Debugging:

1. New improvements to breakpoints:

   Breakpoints are used in SQL Server like other programming languages to help in debugging our code.

   
   

   Figure 4 – SQL Server Breakpoints

   SQL Server 2012 introduces more options to control breakpoints like:

   • Location: you can control what line and which character your breakpoint will start.
   • Condition: you can define a condition so when the breakpoint is reached, your condition is evaluated and your breakpoint will be hit if your condition is true or has changed.
   • Hit Count: you can set a breakpoint to only function when it has already been reached a certain number of times or set it to stop functioning after it has been reached too many times, this is very useful if we are concerned about loops that run too many times.
   • Filter: by this option, you can set a condition that makes a breakpoint run on a development machine and doesn’t run on a production machine, for example.
   • When Hit: so the default action of a breakpoint is that when it is hit, it stops the execution and allows you to look at the values but now we can do something else like print a message or continue execution.
   • Edit labels: we can give labels to our breakpoints and then we can later search for those labels.So we can search for all the breakpoints that have a specific label than either enables those, disable those, or delete those as a group.
   • Exports: we can export all of our breakpoints to an XML file and then later import them.

2. New improvements to debug-mode windows:

   We can run our code in debug mode to monitor the execution of it.

   There are new changes to some windows of this mode that increase productivity and administration of the code debugging:

   • Locals window: this window was previously read-only window, but in SQL Server 2012 you can edit these values that can affect your code.
   • Quick info window: this is a new type of Windows to SQL Server 2012 debug-mode.we can see it when we just hover the mouse over any variable, and we can pin it to be always displayed.Not only that but also it is editable.
   • Comments: while we are in debug mode, now we can add comments, and these comments will stay not only in debug mode but also permanently out of debugging mode.
3. Snippets:

   Is a new feature has been added by Microsoft to SQL Server 2012’s intelligence which has the basic templates to create a function, index, login, role, schema, stored procedure, synonym, table, trigger, user, user-defined data type, user-defined table type, user-defined type, or SQL Server view.

   
   

   Figure 5 – SQL Server Snippets

   The point of the snippets is to be small sections of reusable code.So now we can right click on anywhere on query window and choose “insert snippet” option then add whatever you want from the provided templates, and we can add our snippet if we wish” from tools menu(code snippets manager).”

   There is a sort of special type of snippets under this option you had chosen called surround with. Also, it is a new added to SQL Server 2012.you can highlight any part of your code and choose to surround it with begin, if, or while statement.

   
   

   Figure 6 – SQL Server Snippets with option (surround with)

   As you become familiar with the new snippet functionality, hopefully, it will decrease the amount of time it takes you to code and increases the consistency with which you can write good code.

Changes to the Scope of Objects:

1. The scope of database user:

   In previous versions of SQL server, if we want to give a user access to one of our databases, we first have to create a login on the server level and then create a user at the database level.

   One of the problems with this setup is that if we ever move this database to a different server, that login isn’t going to be on the new server and therefore we have a problem that test user would become disconnected from his login and would stop functioning.So we have to figure out some ways to migrate our login to the new server.

   Microsoft has apparently thought of this and come up with a new feature in SQL Server 2012 called contained a database, or partially contained database enables us to store all of the information in the database.

   We can test this by running this script as a proof-of-concept:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

   --enable contained database sp_configure 'contained database authentication', 1; GO RECONFIGURE GO --create a new contained database CREATE DATABASE [PartContDB] CONTAINMENT=PARTIAL GO USE [PartContDB] GO CREATE TABLE baseTable(id int, [name] varchar (255)) GO ---------------------------------------- USE [PartContDB] GO CREATE USER user1 WITH PASSWORD = N'password', DEFAULT_SCHEMA= [dbo] GO EXEC sp_addrolemember'db_owner', 'user1' GO

   By that, you can connect to SQL server using this user’s credentials, but you must specify the database at the connection properties tab as shown in figure 2:

   
   

   Figure 7 – Connect to SQL Server options

   Now you can copy/move this database to any other server, and that user should still have access, but do be aware that you now have some confidential information in your database.you need to be careful to protect that.

2. Custom server roles:

   Server roles have always been used to assign a fixed group of permissions to multiple members “logins.”

   In previous versions of SQL Server, there are some fixed server roles which we couldn’t change any option except adding/removing members” logins.”

   Now in SQL Server 2012, for the first time we can create our custom server roles.they do not yet have a graphical interface for this, so we have to do this in code like the following example:

   1 2 3 4 5 6 7 8 9 10 11 12

   USE [master] GO CREATE SERVER ROLE [ReadOnly_Admin] GO GRANT VIEW ANY DATABASE TO [ReadOnly_Admin] GO GRANT VIEW SERVER STATE TO [ReadOnly_Admin] GO GRANT VIEW ANY DEFINITION TO [ReadOnly_Admin] GO

   Now you have listed your server role, and also you can edit it’s permission even with the graphical interface as shown in figure 3:

   
   

   Figure 8 – Editing permission of custom server role

   So this is a fairly simple tool Microsoft has added that has the potential to reduce the administrative effort significantly.

3. Changes to auditing SQL Server:

   We can, as usual, create a server as shown in that script:

   1 2 3 4 5

   CREATE SERVER AUDIT myAudit TO FILE (FILEPATH = 'C: \’) WITH (ON_FAILURE = CONTINUE);

   The default value is still CONTINUE.

   In previous versions of SQL server, once it fails to audit once it will stop trying and continue this has changed in SQL server2012 beside other changes at the behavior of some values of the ON_FAILURE option:

   • CONTINUE – changed – the machine will continue processing and will try to log again and again till success.
   • FAIL_OPERATION – new – the operation that caused logging will fail, but other action will continue.

   Other than that there is a new stored procedure can write anything to the audit log as shown in this example:

   1 2 3 4 5

   EXEC sp_audit_write @user_defined_event_id = 23, @succeeded = 1, @user_defined_information = N'Object was dropped’;

   There are many scenarios where we might use this great level of control, one of them as an example:

   If you have an error handling module that writes all of your errors to an error log.You can add this stored procedure to that module, and that would write all of your errors to the audit log that could be in addition to writing them to the error log or instead of writing them to the error log.

Conclusion:

As a conclusion of this article, no doubt that keeping track of newly released features of the technologies we use is very important so we can gain more productivity introduced, get more controls provided, and get more easy ways to handle our work. I hope this article has been informative for you.

References:

• Programmability Enhancements (Database Engine)
• New SQL Server 2012 Features
• Top 20 exciting features of SQL Server 2012

• Author
• Recent Posts

Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East.

Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server.

Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases.

View all posts by Mustafa EL-Masry

Latest posts by Mustafa EL-Masry (see all)

• Concept and basics of DBCC Commands in SQL Server - March 31, 2017
• Hybrid Cloud and Hekaton Features in SQL Server 2014 - February 28, 2017
• How to analyze Storage Subsystem Performance in SQL Server - February 23, 2017