T-SQL: How to split a path name in the path and file name

A common task in T-SQL is how to split a path name in the path and file name.

The following code will do the task:

DECLARE @file VARCHAR(200) = 'c:\temp\data\log.txt'

SELECT REVERSE(LEFT(REVERSE(@file), CHARINDEX('\', REVERSE(@file), 1) - 1)) AS [FileName]

SELECT LEFT(@file, LEN(@file) - CHARINDEX('\', REVERSE(@file), 1) + 1) AS [Path]

T-SQL: Converting rows to columns

A common task of a database developer is to convert rows to columns from a query result set.

T-SQL has the PIVOT that facilitates the process.

A simple example:

DECLARE @myValues TABLE (Quantity INT, Category VARCHAR(100))

INSERT INTO @myValues (Quantity, Category)
VALUES 
 (10, 'Fruits'),
 (200, 'Vegetables'),
 (40, 'Meats')


SELECT *
FROM @myValues

SELECT [Fruits], [Vegetables], [Meats]
FROM @myValues
PIVOT
(
  MAX(Quantity)
  FOR Category IN ([Fruits], [Vegetables], [Meats])
) Piv

SQL Server Table Value Constructor

The latest versions of Sql Server (SQL Server 2008 or above) implement the Table Value Constructor.

Definition

The Table Value Constructor defines a set of row value expressions to be added to a table.
It allows us to create tables of values and/or expressions.
The Table Value Constructor allows us to simplify the T-SQL syntax.

Syntax

The Table Value Constructor is defined as:

VALUES (<row value expression>),(<row value expression>), (<row value expression>), ...

The Table Value Constructor must start with the VALUE keyword and that it can have one or more row value expression.
The <row value expression> can have one of the following values:
1) NULL
2) DEFAULT. If there isn't a default defined for the column NULL is inserted.
3) A constant, variable or an expression. The expression cannot have the EXECUTE statement.
The expression only allows single scalar values. Sub-queries that return multiple columns are not supported.
An example of an invalid sub-query is:

SELECT ID, Name FROM [User]

Note that the values list can have a maximum of 1000 rows. The sql server error 10738 is thrown if the number of rows is greater than the 1000 rows limit.
To insert more row use the classic approaches like a multiple INSERT statements or a BULK INSERT.

Examples

1) Insert multiple rows

CREATE TABLE Product
(
Id INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(1000)
)
INSERT INTO Product
VALUES ('John Doe'), ('Tim Burton'), ('Tom Thomas')

This example can be used to initialize table values.

2) SELECT using a derived table

SELECT x,y
FROM (VALUES (1, 'row 1'), (2, 'row 2'), (3, 'row 3')) AS myTable(x,y)

This example uses the Table Value Constructor as a derived table, to create a table named myTable with the columns x and y.

3) SELECT the maximum value between two variables

DECLARE @val1 MONEY = 20
DECLARE @val2 MONEY = 5

SELECT MAX(Val)
FROM (VALUES (@val1), (@val2)) AS MyTable(Val)

The query returns the value 20. This can be very useful to easily select the maximum value between two or more variables, since sql server doesn't have a built-in function to do this operation.

Table Value Constructor can also be used with the MERGE statement. CodeProject

Poor Mans T-SQL formatter

A database developer must have a set of SQL coding Standards to avoid each statement being written differently.

Steven Bates has witten the SQL Server 2005 coding Standards in a series of blog posts at the MSDN blogs.
The rules are implemented by a SQL Server Management Studio add-in that is named Poor Mans T-SQL formatter.
The Poor Mans T-SQL formatter is a open-source T-SQL formatter that is available in several distibutions: Stand alone application, Notepad++ add-in, online,...
For more information visit the Poor Mans T-SQL formatter site, by pressing here

As for now there is no support to Management Studio 2014.
To make it work with Management Studio 2014:

1) Run the setup for the SQL Server Management Studio 2012 add-in available at the site.

2) Create the folder
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\12.0\Addins\
if it doesn't already exists.

3) Copy the file from:
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\11.0\Addins\PoorMansTSqlFormatterSSMSAddIn.AddIn
To
%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\12.0\Addins\PoorMansTSqlFormatterSSMSAddIn.AddIn

And now the add-in should be available at the Tools menu:
Note: You may need to enable show Hidden Items, in windows explorer View Menu, for the folder ProgramData to be shown.

Query Optimization and the SQL Server Cache

Query optimization is a very important task to assure that the server resources are not heavily consumed by a bad performing query.
One of the important tasks when optimizing a query  is to clean the SQL Server Cache so that the results are not influenced by the caching mechanism.

The T-SQL commands, that clean the Sql Server cache, will cause severe performance problems in a production environment, so they are to be used for testing purposes on a development or staging environment only.
They can be executed in a production server in special and controlled conditions. An example is to remove an individual plan of a query that has a bad performance because of a bad plan cache.

This article explains the main concepts involved and then the possible options to Clean the Sql Server Cache.


Execution Plan

The execution plan is the result of the query optimizer attempt to calculate the most efficient way to process the request represented by the SQL query statement.
Sql Server has to build an execution plan for each Transact-SQL (T-SQL) statement it has to execute.
The  execution plan defines how the T-SQL statement can be executed by Sql Server to produce the desired results.

The execution plan is built based on several considerations:
1) The tables it needes to join
2) The Indexes to use 
3) The sub-queries it has to execute
4) How aggregations of Group By are calculated
5) The estimated cost and load the operations place on the system
6) other even more complex considerations

The execution plan is also known as query plan.

As can be easily understandable, SQL has to put a lot of work to build a Execution Plan, so it caches the execution plan in memory to avoid having to do the same work over and over again.
Sql Server uses the Plan Cache to reuse plans. In this way, SQL Server can avoid the overhead of calculating the execution plan for each T-SQL statement and in this way speed up the execution of the queries.

 
Plan Cache

The plan cache is used by Sql Server to store the Execution Plans of the queries it has run.
The plan cache allows Sql Server to reuse Execution Plans for subsequent requests. It stores plans and it's associated information. There are metrics about the number of times a query was executed and the resources it used for example.

The Plan cache can be flush totally in some situations, the principal are:
1) Sql Server Service Restarts
2) Statistics of an object changing
3) Restores of a database
4) Executing T-SQL commands to clean it
5) Insufficient Memory on the server, causing memory pressure
6) Detaching a database
7) Some T-SQL Commands like for example RECONFIGURE, ALTER DATABASE ... MODIFY FILEGROUP or modifying a collation using ALTER DATABASE … COLLATE command

 The Plan cache of a database can also be flushed totally in some situations, where the principal are:
1) Some operations like for example DROP DATABASE or ALTER DATABASE … MODIFY NAME
2) If the database auto-closes
3) The database is set online or offline


Recompilations

Sql Server checks for correctness and for the optimality of a query plan before it executes it.
If one of the checks fails, the statement is compiled again and new query plan is produced.
These compilations are named as recompilations.

The recompilations are necessary to:
1)  Ensure statement correctness
2)  Obtain potentially better query execution plans as data changes

The recompilations can also have the side effect that they can slow down executions considerably.
In this case it is necessary to reduce the number of recompilations.

Parameter sniffing

Parameter sniffing is a complex topic, but according to Microsoft:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.

The query optimizer uses the parameters passed to the statements for performing estimates  when evaluating possible execution plan options. The final effect is that the plan is optimized for those specific parameter values. 
This feature allows more efficient stored procedure execution plans in most cases. There is however an assumption that the parameter values are "typical".
The main problem with this approach is when a parameter is atypical. The asymmetry usually is in the distribution of the data values or how the value is distributed across where the data is a non-uniform distribution, this is called skewed data.
 One example of skewed data is when there is a table with two million records and a column of type BIT. That column has the value 0 for only 1000 records and all the others have the value 0.
The LIKE clause is also very prune to this issue.

Parameter sniffing affects the performance of a query since the execution plan that is generated by the query optimizer depends on parameter sniffing.

Parameter values are sniffed for:
1) Stored Procedures
2) Queries executed using sp_executesql
3) Prepared queries

The following statements help to control the parameter sniffing performance problems:
1) WITH RECOMPILE - the stored procedure
2) Dummy local variables that are set equal to a parameter
3) OPTION(RECOMPILE) - This query hint is used to extended the behavior to queries (SELECT, INSERT, UPDATE, or DELETE). In this case,  both the parameter values and the current values of local variables are sniffed.
4) OPTION(OPTIMIZE FOR ())

The explanation of these options are out of the scope of this article, but you can search for a detailed explanation if you wish to learn more.


Clean the buffers

Sql Sever buffers the data before it is written to disk, this can cause dirty pages.
To guarantee that all these dirty pages, for the current database, are be written to disk and buffers are clean use the CHECKPOINT statement.
CHECKPOINT forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

The CHECKPOINT is to guarantee that if you performed an operation or if you are in a collaborative environment that you are also testing you don't end up with dirty pages in the buffers.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.  It serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

Example:
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO


Clean the Plan Cache

There are several options to clean the plan cache. The next sections are going to explain the principal options, but don't forget to always Clean the Buffers first.


Clean the entire Plan Cache

The more drastic method is to clean the entire Sql Server plan cache, using the statement FREEPROCCACHE.
This will free the entire plan cache and causes the recompilation of subsequent ad-hoc SQL statements or Stored Procedures.

Usage:
DBCC FREEPROCCACHE


Clean a Database Plan Cache

A less drastic option, is to clear only the entire plan cache for a specific database and not the full Sql Server Plan Cache.

Usage:
DECLARE @dbId INTEGER
SELECT @dbId  = dbid FROM master.dbo.sysdatabases WHERE name = ‘myDatabase’
DBCC FLUSHPROCINDB (@dbId)

Where myDatabase is database to clear the entire plan cache.
The @dbId parameter is the database the number (database ID) to be affected by the FLUSHPROCINDB  command.


Clean a Compiled Plan

In some situations it is useful to clean only a specific compiled execution plan for an Stored Procedure or a Ad-Hoc query.
One example is when some queries are executed in production for testing or to Extract, Transform, Load (ETL) data and should be removed form the Plan Cache so that they don't occupy cache space.

Usage:
SELECT [text], CachedPlans.size_in_bytes, CachedPlans.plan_handle, CachedPlans.objtype, CachedPlans.usecounts
FROM sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE CachedPlans.cacheobjtype = N'Compiled Plan'

This query returns all the compiled plans.
The column Text identifies the T-SQL statement executed (Stored Procedure: proc or Ad-Hoc Query: Adhoc for example) and  the plan_handle can be passed to FREEPROCCACHE to remove it:

DBCC FREEPROCCACHE (plan_handle)


Clean  Stored procedure or Trigger Plan cache

The sp_recompile statement causes the recompilation of stored procedures and triggers the next time that they are run.It drops the existing plan from the procedure cache.

Usage:
EXEC sp_recompile N'myObject'';

Where myObject can be a stored procedure, trigger, table, or view in the current database.
If is the name of a stored procedure or trigger, the stored procedure or trigger will be recompiled the next time that it is executed.
If object is the name of a table or view, all the stored procedures or triggers that reference the table or view will be recompiled the next time that they are executed. CodeProject

Migration from SQL server 2008 to 2012

The company that I work at the present time has received the new SQL Sever hardware, so now is the time to upgrade from the SQL server 2008 to the 2012.
An upgrade, in this context,refers to the process of moving from the SQL server version 2008 to the new version 2012.
There are two approaches when upgrading:

1. In-Place: The SQL Server is upgraded where it is currently installed
2. Migration: A a new environment is installed, the data is copied to it and configured with the existing data. The content from SQL server 2008 must be migrated to the 2012 supported formats.

The approach I am going to take is the second, since I have a new hardware and I am going to do a fresh SQL Server 2012 installation.

The migration from SQL server 2008 to 2012 must be well prepared and tested before going to production.

I have to migrate several databases, the integration services (SSIS) packages, analisys services (SSAS) packages and reporting services (SSRS) reports.

Requirements

This article assumes you already know the SQL Server database engine, integration services, analisys services, reporting services and it's tools.
Database administration knowledge is also important, like knowing what is a backup and a restore.
Some T-SQL knowledge is also assumed.

Databases

The database engine isn't a complete rewrite. This means that we can expect a deep compability level.

There is an article on MSDN that explains the SQL Server Database Engine Backward Compatibility. You must read it here to ensure that you ate not using a feature that breaks the SQL 2012 compatibility.
After reading the article and fixing any issues, the upgrade of the databases can be implemented in the following steps:

1. Create the databases and configure them on the SQL 2012 Server. If you don't have any special requirements skip this step, since when you perform the restore the databases are created automatically.
   Nevertheless, it is a good practice to think of the architecture of your databases e configure them accordingly.
2. Backup the SQL Server 2008 databases and restored them in SQL 2012.
3. Change each database compatibility level from 2008 to 2012. This action is important since it allows the usage of the new SQL Server 2012 features.
   The following script can be useful you you have several databases to migrate:
   
   USE [master]
   GO
   ALTER DATABASE [mydatabase] SET COMPATIBILITY_LEVEL = 110
   
   where [mydatabase] is the database to change the compatibility level
   
   or goto the database properties and on the options select the Compatibility Level 110.
   
4. Check the logical and physical integrity of all the objects in the upgraded databases:
   
   DBCC CHECKDB([myDatabase]) WITH NO_INFOMSGS
   where [mydatabase] is the database to  run the integrity checks
   NO_INFOMSGS option  suppresses all informational messages.
   
   If If DBCC printed any error messages you must fix them so that your database will work correctly.

Don't forget to create the databases maintenance plans.

Integration Services (SSIS)

In SQL Server 2012 the SSIS Package format changed and the specifications are now Open Source.
The Business Intelligence Development Studio (BIDs) is replaced by the SQL Server Data Tools (SSDT).
SQL Server 2012 SSIS offers a wizard for upgrading most of the solution components, but a few settings may be needed to be changed manually.
The wizard appears when you open a SQL Server 2008 package on the SQL Server data tools.
Microsoft has a white Paper that gives you 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012. You can read it here.

SSIS 2012 supports two deployment models:

1. Package deployment model: In this model the the unit of deployment is the package. This is the model used in previous versions of SSIS and is the default deployment model for upgraded packages.
2. Project deployment model: . The unit of deployment is the project for this model. This model is new in SQL Server 2012 and provides additional package deployment and management features such as parameters and the Integration Services catalog

I have decided to use the Package deployment model for now, since it is the one the gives more compatibility with the SSIS 2008 model. When I have more time for testing and development I am going to convert to the Project deployment model. There is an wizard the performs this task and that is explained in the white paper I mentioned previously.

The migration of the integration services (SSIS) packages

1. Open the solution (sln) file with the packages to migrate
2. The Visual Studio Conversion wizard appears. It is very simple and after a few next's pressed, the Package Management options appear.
3. In the Package Management options select validate upgraded packages, so that the packages are validated and only the ones that pass validation are saved.
4. Disable Ignore configurations, so that the configurations are validated during the upgrade process.
5. The wizard ends the conversion and you can close it.
6. Test each package and verify that it is working as expected.

If there is a conversion error by the wizard, when you open the package in Visual Studio  it is immediately converted. This methodology allows that you can easly control the errors and correct them.

Analisys Services (SSAS)

The SSAS 2012 has a great deal of changes. The main new features are:

• Business Intelligence Semantic Model
• Tabular model
• PowerPivot for Excel and Sharepoint
• SQL Server Data Tools (SSDT)
• Programmability with support for the new features

The options for migrating are:

1. Use only the multidimensional model 
2. Convert to the tabular model
3. Use the multidimensional model for existing cubes and use the tabular model for new developments
4. Use the tabular model or the multidimensional model depending on the project requirements

The conversion from the multidimensional model to the other models isn't supported by Microsoft at  the date this article was written.
The approach to keep the existing cubes in the multidimensional model is the one I selected, the main reasons are:

1. The existing cubes can be migrated to the SSAS 2012 multidimensional model, without any modifications.
2. The existing reports and client tools will work without any problems
3. The model is more mature and supports much higher data volumes 
4. The team has knowledge of this model and can continue the development without any significant changes

In the future I pretend to explore the new models, but for now the mature multidimensional model is the best option.

The analysis services migration, with the selected approach, can be performed in the following simple steps :

1. Open the solution (sln) file with the SSAS databases to migrate
2. The Visual Studio Conversion wizard appears. The wizard doesn't have any options, so press Next and then Finish.
3. Terminated the wizard and let it execute
4. Deploy and process the SSAS database 
5. Test the SSAS database and confirm that everything is working as expected

The only issued I faced was that after processing I got the error:

- Errors in the back-end database access module. The provider 'SQLNCLI10.1' is not registered.
- The following system error occurred:  Class not registered

This error hints that there is a problem with the Data Sources connection string.
When I tried to open a data source the in project and pressed the edit button to edit the connection string, I got the error:

"The specified provider is not supported. Please choose different provider in connection manager."

The SQL Server 2008 Native Client is not installed in the Sql Server 2012 server, so I changed the connection string provider to the native client 11.0 and the issue was fixed.

Another option, if strictly necessary, is to Download and install the SQL Server 2008 SQL Native Client or the SQL Server 2005 SQL Native Client, depending on the connection string provider you want to use.

After his issue was fixed the processing occurred correctly and smoothly.

Reporting services (SSRS)

The SQL Server 2012 Reporting Services (SSRS) has two processing modes:
1) SSRS 2012 report processor. A report that is successfully converted to SSRS 2012 format is executed in this mode and can use the new SSRS features.
2)  Backward-compatibility mode processor. A report that cannot be converted to SSRS 2012 is processed in backward-compatibility mode and the new features are not available, but the report is still rendered.
You can find more information here.

This approach by Microsoft gives a high degree of compatibility and I don't expect to have any issues in the migration.
The reporting services migration steps are:

1. Open the solution (sln) file with the reports to migrate
2. The Visual Studio Conversion wizard appears. The wizard doesn't have any options, so press Next and then Finish. 
3. An information message may appear asking if you want to upgrade the report server project to the latest version. Press Yes
4. Let the wizard execute.
5. Open each data source and test the connection string. If there is an error fix it.
6. Deploy the reports
7. Test the reports and confirm that everything is working as expected

CodeProject