Log in | Register

Transact-SQL Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education Transact-SQL Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education

SQLServerGeeks Annual Summit 2016 IS HAPPENING IN BANGALORE. HAVE YOU REGISTERED?

Database Scoped Configurations and Trace flag 4199

Those who are working on query performance whether you are a DBA or Developer I am sure you guys must have used trace flag 4199.  TF 4199 enables all query optimizer related fixes.Microsoft used to have separate TF for each hotfix applied to query optimizer prior to SQL Server 2005 Service Pack 3 (SP3) Cumulative […]

SQL Sever 2016 – Two things developers should know

Hello Friends,

There are lots of new features and improvement launched by Microsoft in SQL Server 2016. There are two small but really interesting things for the T-SQL Developers, DBA Developers who are involve in writing T-SQL code.

Situation: Let’s think about a situation where we are creating a new object (for example –  Stored Procedure) […]

DBCC CloneDatabase

This blog was due from a long time.  With the release of SQL Server 2014 Service pack 2, a new DBCC command included that is DBCC CloneDatabase. My primary job role is to do query tuning and I was looking for this kind of functionality. Using this command, it is quite easy   to copy […]

By |March 15th, 2017|Categories: Database Administration, Performance, Transact-SQL|Tags: , , |Comments Off on DBCC CloneDatabase

New Function String_Split in SQL Server 2016

SQL Server 2016 has many features which makes developer life bit easy to do various task. Earlier to split a string developer use to write a user defined function using temp tables and complex logic. In almost every database I see a user defined function exists which does string split work. The project for […]

By |March 8th, 2017|Categories: Database Development, Programming, SQL Server, Transact-SQL|Tags: , |Comments Off on New Function String_Split in SQL Server 2016

Step by Step Guide to Install, configure and Run R script in SQL Server 2016

Microsoft acquired Revolution Analytics in 2015 and integrated R in SQL Server 2016.Today I will show you how to install, configure and run R script in SSMS. Like all other Microsoft software installation of R is pretty easy. You just need to run the set up and with few click on Next button will […]

By |February 28th, 2017|Categories: Database Administration, SQL Server, Transact-SQL|Tags: , , |Comments Off on Step by Step Guide to Install, configure and Run R script in SQL Server 2016

New Security Features In SQL SERVER 2016 – PART 1

Data Security in any application is vital. Different business or application  has their own security policy.  SQL Server 2016 provides three new security features which helps to protect data.

Below there are new security features added in SQL Server 2016.

Row-Level Security (RLS)
Dynamic Data Masking (DDM)
Always Encrypted

In this part of blog, we will see use case […]

By |February 1st, 2017|Categories: Database Administration, Security and Encryption, Transact-SQL|Tags: , , , |Comments Off on New Security Features In SQL SERVER 2016 – PART 1

Sub Query behaviour looks like a bug

While debugging one procedure I came across below Scenario where a sub-query was returning all records from outer query.

Initially I thought it is a bug in SQL server. As COL1 column is not part of Table2 , it should fail binding stage and throw error. But it returned all 20 records from Table1. Though […]

SQL Server – Join Operation

In this blog we’ll find how SQL Server works when we do join operation. SQL Server optimizer chooses one of the below physical operator to perform logical join operation.

Hash Match
Merge Join
Nested Loop Join

As far as performance is concern let me tell you, we can’t say which one will best.Each operator has its own advantages […]

Don’t use ISDATE() function to check valid Date

I got the below email from one of my teammate on ISDATE() function.

Thanks to him for asking me the question. I do not have any idea on this behaviour of  sql server. I gone through the MSDN documentation and  got to  know, why SQL Server behaves like this.

select ISDATE(’08/16/013′) returns true because here it […]

By |October 11th, 2016|Categories: SQL Server, Transact-SQL|Tags: , , , |Comments Off on Don’t use ISDATE() function to check valid Date

How to redefine the returning result sets of a stored procedure?

This feature was introduced in SQL Server 2012. Using With Result Sets option with Execute Command we can change the column name and data type of returning result set.
Ex:

Output: 
(2 row(s) affected) 
ID          Name       DOB 
———– ———- ———————– 
1           A          1980-04-10 06:09:00.100 
1           B          1982-03-08 03:42:41.003
(2 row(s) affected)
MyID                 MyName  DateOfBirth 
——————– ——- ———– 
1                    A       1980-04-10 
1                    B       1982-03-08
(2 row(s) affected)
The second result set […]

By |October 4th, 2016|Categories: Transact-SQL|Tags: , |2 Comments
Newsletter_Footer