Write Queries Faster and Cleaner Using System and User Defined Query Templates

---For Syntax

I had blogged about a tip recently on Scripting your actions in SQL Server Management (SSMS) which helps us to easily generate T-SQL Scripts even for those operations for which we do not remember/know the exact the syntax in T-SQL. That definitely is a nice approach, however, there is one more approach which can be used to quickly write-up scripts for most of the common tasks which we perform on a database in our day-to-day work. This approach, however, provides a template and we need to fill in the details to get the script completed for the intended task.

This feature called Template Explorer in SSMS, offers a list of various pre-defined T-SQL Templates for performing majority of the common tasks using T-SQL which we perform in our day-to-day work.

To bring up the Template Explorer, Go to View | Template Explorer or alternatively, you can press Ctrl+Alt+T.

Below screenshot shows a list of all the available Templates categorized into different categories based on the type of object/operation.

For a step by step instructions on using these templates, take a look at this article: T-SQL Coding Made Easy with Inbuilt SQL Server Templates.

Using Template Explorer we can perform the following tasks:

• Edit the existing templates to include additional details like parameters, fields, comments, formatting etc.
• Create new templates for commonly used scripts in a module / project so that they can be reused by other team members and also across various modules / projects.
• Rename existing folders / templates as per your convenience (Though this is not required 99.9% of the times as all the folders which ship with SQL Server are named appropriately).
• Create new folders / Renaming existing folders to add new or existing templates.
• Re-organizing the templates by moving them across folders.

Following are few of the advantages / benefits of using Template Explorer:

• These templates act as a quick reference for getting the syntax in the absence of locally installed SQL Server Books Online or Access to Internet.
• Saves time required for formatting the T-SQL code since the templates are pre-formatted hence ensures consistency in terms of formatting across various modules / projects.
• We can share commonly used scripts across all the team members working across modules / projects.

Quickest way to Get Row Count of All Tables from a SQL Server Database

In this tip we will see an approach which can be used to get the Table Row Counts without writing any explicit queries.

• Open SQL Server Management Studio (SSMS)
• Connect to the SQL Server Instance from which you want to get the Row Counts
• Go to View | Object Explorer Details (or alternatively, you can press F7)
• In the Object Explorer, navigate to Tables folder under the database (say AdventureWorksDW) from which you wan to get the Row Counts (Databases | AdventureWorksDW | Tables)
• Object Explorer Details window shows a summary of all the Tables (present in the Tables folder under AdventureWorksDW database) as shown in the below screenshot.

Now, in the Object Explorer Details window, Right Click on the header and select Row Count from the context menu as shown in the below screenshot.

Now you will see the Row Count of all the Tables in the Object Explorer Details window as shown below.

Also, in the above displayed view, you can select multiple rows and copy the information into an excel or any other format for further reporting/auditing purposes, and you can add or remove the columns as required like we added the Row Count column above. This is a very hand approach for getting the Row Count from all the tables in a SQL Server database.

Quickest way to SELECT and Query All Columns from a Table in SQL Server

Most of you, who are working on T-SQL must be knowing that using “SELECT * FROM <<Table>>” syntax is treated as a Bad Practice and instead, using “SELECT <<Column Names>> FROM <<Table>>” syntax is recommended as a Best Practice for various reasons .

Say you want to select all the columns from a table, for which you would write a query something like this:

SELECT *

FROM dbo.DimProduct

Now, as specified above, we need to replace the asterisk (*) in the above query with the list of all the columns present in DimProduct table. To do this, follow these steps:

• Go to SSMS, Open a new query window, Type SELECT followed by a space on First Line and FROM dbo.DimProduct on the Second Line.
• Go to Object Explorer | Database Containing DimProduct Table | DimProduct Table
• Expand the DimProduct table and click on Columns folder
• Hold the Left Mouse Button and Drag the Columns folder on to the Query Window after SELECT, which will pull all the columns from DimProduct table and place them after the SELECT statement in the query window separated by commas (,) as shown below.

When you drag the Columns folder, All the Columns from DimProduct are placed after the SELECT Clause and before the FROM Clause in the above query window as shown below.

Now, you can make slight formatting changes to suit your needs and if you want, you can even remove some of the columns based on your need (Remember that, removal is always easier than addition when it comes to Coding and hence this approach!).

Generate Script for Multiple Select Objects in One-Go

Generating scripts of various objects in SQL Server Management Studio (SSMS) is a common requirement and generating the script for all the objects is a pretty straight forward task, which can be done using a simple approach as detailed out this msdn article. However, let us say that you want to generate the create scripts for a selected set of tables in your database (say AdventureWorksDW database). How would you do it? Well, keep reading to know how :-)

• Open SSMS, Connect to the SQL Server Instance
• Go to Object Explorer, and expand Databases folder
• Expand the data Database from which you want to script objects
• Select the Tables folder
• Go to View | Object Explorer Details (alternatively you can press F7). Now the Object Explorer Details looks as shown below.

• Now, to generate Create Table Script for multiple selected objects in AdventureWorksDW database, select multiple tables in the Object Explorer Details (by holding Ctrl key).
• Right click and select Script Table as | CREATE TO | New Query Editor Window as shown below.

This will generate the CRATE TABLE Script for all the objects selected in the Object Explorer Details window.

What approach do you use to perform this task?

Note: Above demonstration is based on SQL Server 2008 R2 Management Studio.

Find The Number of Occurrences of a Character or a Substring in a String

Below is a simple query which can answer both the above questions.

Find the Number of Occurrences of a Character in a String using T-SQL

Query:

DECLARE @SourceString VARCHAR(100)

DECLARE @FindString VARCHAR(10)

SET @SourceString = ‘Find Number of Occurrences of a Character in a String’

SET @FindString = ‘S’

SELECT (LEN(@SourceString) – LEN(REPLACE(@SourceString, @FindString, ”)))  AS NumberOfOccurrences

Output:

NumberOfOccurrences

——————-
2
(1 row(s) affected)

Find the Number of Occurrences of a Substring in a String using T-SQL

Query:
DECLARE @SourceString VARCHAR(100)
DECLARE @FindString VARCHAR(10)
SET @SourceString = ‘Find Number of Occurrences of a Substring in a String’
SET @FindString = ‘of’
SELECT (LEN(@SourceString) – LEN(REPLACE(@SourceString, @FindString, ”))) / LEN(@FindString) AS NumberOfOccurrences
Output:

NumberOfOccurrences

——————-

2

(1 row(s) affected)

Add Catalog to an Existing Linked Server/ Missing Database in Linked Server

 I have an existing Linked Server with a few catalogs... how can I add another, existing database/catalog to this linked server?

For Example, my object explorer looks like this:

Linked Servers

• Providers
• DB4\PRODUCTION
• DB4_LINK
  • Catalogs
    • System Catalogs
    • MyDatabase
    • MyOtherDatabase

How can I add yet another database that already exists to DB4_LINK?

EDIT: I'm trying to add an existing database to this linked server entry.

Ref:

http://stackoverflow.com/questions/11975063/add-catalog-to-an-existing-linked-server/11975274#comment32035221_11975274

When u tried to access that db from linked server or missing db in linked server

see error below

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "linkedservername" does not contain the table ""databasename"."dbo"."tablename"". The table either does not exist or the current user does not have permissions on that table.

Solution:

You can do this by sending dynamic SQL via the linked server:

EXEC Linkedservername.master..sp_executesql N'CREATE DATABASE databasename;';

Of course this requires that you have the permissions to do so, and it's a simplistic command assuming that the default settings are fine - you may want to customize the CREATE DATABASE command.

How to update Identity Column in SQL Server?

update table
set identity_column_name = some value

It is not an easy process and it is not advisable to use it, as there may be some foreign key associated on it.

 You can't update the Identity column as you update the normal ones

But here are steps to do it, Please take a back-up of table

Step 1- Select design view of the table

Step 2- Turn off the identity column

Now you can use the update query.

Now redo the step 1 and step 2 and Turn on the identity column

(OR)

USE DBCC CHECKIDENT

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

If you want to update the value of identity column of existing records then you need to set

set identity_insert YourTable ON

Example

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
--Now set the idenetity_insert OFF to back to prevoius track
SET IDENTITY_INSERT YourTable OFF

New Features in SQL Server 2008/2012/2014

1)New Features in Sql Server 2008

Ref:

http://sqlhints.com/2011/09/11/new-features-in-sql-server-2008/

1) Variable declaration allows initialization:

Prior to Sql Server 2008 to initialize a variable, we needed to first declare the variable and then we can initialize it by using SET/SELECT statement as shown below:

1 2	DECLARE @COUNT INT SET @COUNT =100

Now in Sql Server 2008 Variable declaration allows initialization similar to the one we do in C#. Now instead of writing two statements, we can write a single statement as below:

1	DECLARE @COUNT INT =100

2) Insert multiple rows using single INSERT Statement

To understand this feature first create an Employee Table by using the below script:

1 2	CREATE TABLE DBO.Employee ( Id INT,  Name VARCHAR(50) )

Prior to Sql Server 2008, to insert multiple records we use to write statements like below:

1 2 3

INSERT INTO dbo.Employee VALUES(1,'Basavaraj') INSERT INTO dbo.Employee VALUES(2,'Shashank') INSERT INTO dbo.Employee VALUES(3,'Monty')

Now in Sql Server 2008 we can accomplish the same by writing script like below:

1 2 3 4

INSERT INTO dbo.Employee VALUES(1,'Basavaraj') ,        (2,'Shashank') ,        (3,'Monty')

3) Arithematic Assignment Operators
Now Sql Server 2008 also supports the Arithematic Assignment Operators like the below ones:

Operator Usage            Description +=       SET @x+=@y       Same as: SET @x = @x + @y -=       SET @x-=@y       Same as: SET @x = @x - @y *=       SET @x*=@y       Same as: SET @x = @x * @y /=       SET @x/=@y       Same as: SET @x = @x / @y %=       SET @x%=@y       Same as: SET @x = @x % @y

Example:

1 2 3

DEClARE @x INT =2 ,@y INT = 2 SET @x+=@y  SELECT @x as x,@y as y

Result: x           y ----------- ----------- 4           2

4) Table-Valued Parameters in Sql Server:
It provides option for the Client Applications to pass multiple rows of Data to Sql Server.

Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml /comma separated values and pass it to the stored procedure and in Stored Procedure convert this xml/comma separated values to a table variable/temporary table.

You can find detailed information on the Table-Valued Parameters and also on calling Stored Procedure with Table-Valued Parameter from Sql Server and C# .Net Code @http://sqlhints.com/2011/09/09/table-valued-parameters-in-sql-server/

5) MERGE Statement

Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

You can find detailed information on MERGE Statement @http://sqlhints.com/2011/09/24/merge-statement-in-sql-server-2008/

6) Sparse Column

Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

You can find detailed information on Sparse Column @http://sqlhints.com/2011/11/12/sparse-columns-in-sql-server-2008/

7) Date and Time Data Types

Date, Time, DateTime2 etc are the new date and time data type introduced in SQL SERVER 2008. You can find detailed information on DateTime2 datatype in the article Difference between DateTime and DateTime2 DataType.

8) As SP_Depends results were not always reliable/accurate/correct. To resolve this in SQL SERVER 2008 following two DMV’s are introduced

sys.dm_sql_referencing_entities

sys.dm_sql_referenced_entities

9) Filtered Indexes

Filtered Index (i.e. Index with where clause) is one of the new feature introduced in Sql Server 2008. It is a non-clustered index, which can be used to index only subset of the records of a table. As it will have only the subset of the records, so the storage size will be less and hence they perform better from performance perspective compared to the classic non-clustered indexes.

For detailed information on filtered index you can go through the article A-Z of Filtered Indexes with examples in Sql Server
Below is an example Filtered Index Creation Script:

SET QUOTED_IDENTIFIER ON GO CREATE NONCLUSTERED INDEX IX_Emplyoee_EmployeeId  ON Employee(EmployeeId) WHERE EmployeeId > 500 GO 10)Policy Based Management (PBM) introduced  11)Central Management Server(CMS) is Introduced

12) Backup with Compression is introduced

2)New Features in Sql Server 2012

Ref:

http://sqlhints.com/tag/new-feature-in-sql-server-2012/

Following are the some of the new features of the Sql Server 2012 which I have blogged. Click on the feature name to know it in detail with extensive examples:

SEQUENCE OBJECT
Introduction to SEQUENCE SEQUENCE Limitations and Restrictions SEQUENCE Cache Management and Internals
THROW
THROW Statement
More Options
EXECUTE Statement with WITH RESULT SETS option in SQL SERVER 2012 Order by Clause have OFFSET/FETCH Options FULL TEXT Search have more options
CONVERSION FUNCTIONS	STRING FUNCTIONS
PARSE TRY_PARSE TRY_CONVERT	CONCAT FORMAT
LOGICAL FUNCTIONS	DATE AND TIME FUNCTIONS
CHOOSE IIF	EOMONTH DATEFROMPARTS DATETIMEFROMPARTS SMALLDATETIMEFROMPARTS

Columnstore index in SQL Server 2012

Microsoft introduced the columnstore index in SQL Server 2012 to provide significantly improved performance for data warehousing types of queries. Microsoft states that for some types of queries, columnstore indexes can provide up to 10x performance improvements

Unlimited Concurrent Connections introduced in SQL 2012

In SQL 2008 Concurrent Connections  was 32767

3)Important New Features in SQL Server 2014

Microsoft's new release of SQL Server 2014 comes pretty close on the heels of the last SQL Server 2012 release. For many organizations, this could be a hurdle to adoption, because upgrading core pieces of an IT infrastructure can be both costly and resource-intensive. However, SQL Server 2014 has several compelling new features that can definitely justify an upgrade. Without a doubt, the most notable new feature is the new In-Memory OLTP engine, which promises some big performance improvements for OLTP applications. The past couple of SQL Server releases have had a strong focus on business intelligence (BI), which makes the new In-Memory OLTP engine an especially welcome addition for relational database professionals. Let's dig in and have a closer look at the new In-Memory OLTP engine and the other new features in SQL Server 2014.

New In-Memory OLTP Engine

By far the most important new feature in SQL Server 2014 is the In-Memory OLTP engine (formerly code-named Hekaton). By moving select tables and stored procedures into memory, you can drastically reduce I/O and improve performance of your OLTP applications. Microsoft states that some applications can expect up to a 20x performance improvement. Edgenet, an early adopter, saw a 7x performance increase in its online and retail supply application.

The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. The In-Memory OLTP tables are copied into memory and made durable by transaction log writes to disk. An all-new lock-free engine processes the transactions for memory-resident tables. Stored procedure performance is improved by compiling the stored procedures into native code DLLs. Standard T-SQL stored procedures are interpreted, which adds overhead to the execution process. Compiling the stored procedures to native Win64 code makes them directly executable, thereby maximizing their performance and minimizing execution time.

To help you evaluate how the In-Memory OLTP engine will improve your database performance, Microsoft includes the new Analysis, Migrate, and Report (AMR) tool. Like its name suggests, the AMR tool analyzes your database and helps you identify the tables and stored procedures that would benefit from moving them into memory. It lists the expected performance improvements as well as any incompatibilities that need to be addressed. In addition, the AMR tool can help you perform the actual migration of tables to the new memory-optimized format. (For more information about the AMR tool, see "SQL Server 2014's Analysis, Migrate, and Report Tool.")

The In-Memory OLTP engine works with commodity server hardware, but it has a number of limitations. For instance, not all of the data types are supported. Some of the data types that aren't supported for memory-optimized tables include geography, hierarchyid, image, text, ntext, varchar(max), and xml. In addition, several database features can't be used with the new In-Memory OLTP capability. Database mirroring, snapshots, computed columns, triggers, clustered indexes, identity columns, FILESTREAM storage, and FOREIGN KEY, CHECK, and UNIQUE constraints aren't supported.

The In-Memory OLTP engine is supported on Windows Server 2012 R2, Windows Server 2012, and Windows Server 2008 R2 SP2. In addition, you need to be using the SQL Server 2014 Enterprise, Developer, or Evaluation edition. Notably, In-Memory OLTP won't be supported on the SQL Server 2014 Standard edition. For more information, check out "Rev Up Application Performance with the In-Memory OLTP Engine."

Enhanced Windows Server 2012 Integration

SQL Server 2014 provides improved integration with Windows Server 2012 R2 and Windows Server 2012. SQL Server 2014 will have the ability to scale up to 640 logical processors and 4TB of memory in a physical environment. It can scale up to 64 virtual processors and 1TB of memory when running on a virtual machine (VM).

SQL Server 2014 also provides a new solid state disk (SSD) integration capability that enables you to use SSD storage to expand SQL Server 2014's buffer pool. The new buffer pool enhancements can help increase performance in systems that have maxed out their memory capability by using high-speed nonvolatile RAM (NVRAM) in the SSD drives as an extension to SQL Server 2014's standard buffer pool. The new buffer pool extensions can provide the best performance gains for read-heavy OLTP workloads.

SQL Server 2014's Resource Governor provides a new capability to manage application storage I/O utilization. First introduced with SQL Server 2008, the Resource Governor originally enabled you to limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resources Governor so that you can now manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool, allowing you to have more predictable application performance.

SQL Server 2014 also integrates with several new and improved features in Windows Server 2012 R2 and Windows Server 2012. For example, SQL Server 2014 supports the OSs' new Storage Spaces feature. With Storage Spaces, you can create pools of tiered storage to improve application availability and performance. In addition, SQL Server 2014 can take advantage of the OSs' Server Message Block (SMB) 3.0 enhancements to achieve high-performance database storage on Windows Server 2012 R2 and Windows Server 2012 file shares. Many enhancements were made to SMB 3.0, with the most notable being SMB Transparent Failover and SMB Direct. The new SMB Transparent Failover feature provides highly reliable SMB storage that's fully supported for applications like SQL Server and Hyper-V. With the new SMB Direct feature, you can leverage the NIC's Remote Direct Memory Access (RDMA) feature to provide access speeds for SMB file shares nearing the access speed for local resources.

Enhancements to AlwaysOn Availability Groups

SQL Server 2014's AlwaysOn Availability Groups has been enhanced with support for additional secondary replicas and Windows Azure integration. First introduced with SQL Server 2012, AlwaysOn Availability Groups boosted SQL Server availability by providing the ability to protect multiple databases with up to four secondary replicas. In SQL Server 2014, Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now available for read-only workloads, even when the primary replica is unavailable. SQL Server 2014 also provides Windows Azure AlwaysOn integration. This new integration feature enables you to create asynchronous availability group replicas in Windows Azure for disaster recovery. In the event of a local database outage, you can run your SQL Server databases from Windows Azure VMs. The new Windows Azure AlwaysOn availability options are fully integrated into SQL Server Management Studio (SSMS).

Enhancements to Backups

Database backups in SQL Server now support built-in database encryption. Previous releases all required a third-party product to encrypt database backups. The backup encryption process uses either a certificate or an asymmetric key to encrypt the data. The supported backup encryption algorithms are Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES (3DES).

SQL Server 2014 also provides new Windows Azure integration to SQL Server's backup capabilities. You can specify a Windows Azure URL as the target for your SQL Server 2014 database backups. This new Windows Azure backup feature is fully integrated into SSMS.

Updateable Columnstore Indexes

Columnstore indexes are another of Microsoft's high performance in-memory technologies. Microsoft introduced the columnstore index in SQL Server 2012 to provide significantly improved performance for data warehousing types of queries. Microsoft states that for some types of queries, columnstore indexes can provide up to 10x performance improvements. However, in the original implementation of the columnstore indexes, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction. The new updateable columnstore index enables updates to be performed to the underlying table without first needing to drop the columnstore index. A SQL Server 2014 columnstore index must use all of the columns in the table, and it can't be combined with other indexes.

SQL Server Data Tools for Business Intelligence

Previously known as Business Intelligence Development Studio (BIDS) and SQL Server Data Tools (SSDT), the new SQL Server Data Tools for BI (SSDT BI) is used to create SQL Server Analysis Services (SSAS) models, SQL Server Reporting Services (SSRS) reports, and SQL Server Integration Services (SSIS) packages. SSDT BI is based on Microsoft Visual Studio 2012 and supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014. In the pre-release version of SQL Server 2014 Community Technology Preview 2 (CTP2), SQL Server Setup doesn't install SSDT BI. Instead, you must download it separately from the Microsoft Download Center.

Power BI for Office 365 Integration

Power BI for Office 365 is Microsoft's cloud-based BI solution that leverages familiar Office 365 and Excel tools. Power BI for Office 365 provides business insights through data visualization and navigation capabilities. Power BI for Office 365 includes:

• Power Pivot (formerly PowerPivot). This Excel 2010 and Excel 2013 ProPlus add-in enables Excel to perform data analysis on large volumes of data.
• Power View. This Excel 2013 ProPlus add-in provides a Silverlight-based data visualization and navigation tool. Microsoft has extended Power View so that you can now use it with multidimensional models (OLAP cubes). Power View multidimensional models also support queries using Data Analysis Expressions (DAX). Power View's data visualization capabilities have also been enhanced. Power View now supports a number of data visualizations, including tables, matrixes, bubble charts, and geographical maps. To learn more about Power View's new multidimensional support, go to MSDN's Power View for Multidimensional Models web page.
• Power Query (formerly code-named Data Explorer). This Excel 2013 add-in lets you discover and integrate data into Excel. It supports SQL Server data sources as well as external sources such as Windows Azure, text files, XML files, Open Data Protocol (OData) feeds, web pages, Hadoop data sets, and public data sets (e.g., Open Government data from data.gov).
• Power Map (formerly code-named GeoFlow). This Excel 2013 ProPlus add-in provides 3D mapping visualizations.

As you can see, several of the Power BI for Office 365 components require Excel 2013, which must be acquired separately. You can learn more about Microsoft's Power BI for Office 365 tools at Microsoft's Power BI for Office 365 website.

Unchanged Subsystems and Discontinued Features

Like you might expect after such a short release cycle, not every subsystem has been updated. There are no major changes to SQL Server 2014's replication, SSIS, or SSRS.