Category Archives: MS SQL Server

TRIM function in SQL 2017

Posted on April 17, 2018 by MKumar

The much awaited “TRIM” function is finally available. Now, no need to convince both the “LTRIM” and “RTRIM” friends to do your job. Your new friends can do it alone and can do much more. TRIM function not only remove … Continue reading →

Posted in BI, MS SQL Server, SQL, TSQL | Leave a comment

ETL Staging tables: Best Practices

Posted on June 19, 2017 by MKumar

When the volume of data in flow is high and the business rules enforce several bulk/ intermediate operations, maintaining a temp or stg table is a best practice keeping in mind that you are emptying that table after loading work … Continue reading →

Posted in Best Practices, ETL, MS SQL Server, SSIS | Leave a comment

Finding last N records

Posted on March 31, 2017 by MKumar

I was going through few blogs and suddenly it came into my mind – “how can i find last n records” provided there is no identity or sequential field and also there is no any supportive date field. There might … Continue reading →

Posted in MS SQL Server, SQL, TSQL | Leave a comment

Constraint and associated index

Posted on March 6, 2017 by MKumar

Constraints like UNIQUE and PRIMARY KEY comes with associated index behind the scene. We can impose UNIQUE Index on a column either by defining UNIQUE Constraint in table definition CREATE TABLE [dbo].[emp]( [id] [int] NOT NULL, [name] [varchar](20) NULL, [email] … Continue reading →

Posted in MS SQL Server, SQL, TSQL | Leave a comment

Know the Primary Key Fields using Tsql code

Posted on February 27, 2017 by MKumar

Often, we need to know what are those columns which are constructing the Primary Key of the table. Recently, i got similar requirement and found these 2 below mentioned queries which helped me. 1. exec sp_pkeys @table_name = ‘<TableName>’ , @table_owner='<SchemaName>‘, @table_qualifier=’<DatabaseName>‘ … Continue reading →

Posted in MS SQL Server, SQL, TSQL | Leave a comment

How to remove(retain one copy) duplicate records from a table without using Ranking functions(Row_Number,Rank,Dense_Rank,NTile),Subquery or Group by

Posted on January 28, 2017 by MKumar

Sometime we come across a situation when we get a job to do in very different way that we didn’t expect yet. Often, in interview we get at least one such question and we first think whether it is possible … Continue reading →

Posted in MS SQL Server, SQL, TSQL | Leave a comment

T-Sql Renaming – Database and Objects

Posted on January 4, 2017 by MKumar

We sometimes encounter a situation, when we need to rename the existing database or database objects. SQL Server provides a Stored Procedure named “sp_rename” which can do all such kind of renaming. The definition of this procedure is below- create … Continue reading →

Posted in MS SQL Server | Leave a comment

Find nth highest Salary using t-sql

Posted on May 10, 2016 by MKumar

There are various ways to get nth highest Salary from Employee table which are very hot question asked in any sql interview. I am mentioning below most of them in generalized manner though they are free from optimization perspective, and … Continue reading →

Posted in MS SQL Server | Leave a comment

SQL Server(2012) Collations

Posted on January 18, 2016 by MKumar

A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. There are 4-levels of sql collation supported in SLQ Server 2012 version. Server-level collation – This … Continue reading →

Posted in MS SQL Server | Leave a comment

Powerful Tools alternative to SSMS:

Posted on December 11, 2015 by MKumar

There are many tools free or charged alternative to typical SSMS. Each one comes with exciting features to enhance SQL Server operations and to ease the pain of SQL Professionals. I am listing few of them which are widely used … Continue reading →

Posted in BI, MS SQL Server | Leave a comment

NULL comparision with ANSI_NULLS option

Posted on December 26, 2014 by MKumar

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against … Continue reading →

Posted in MS SQL Server | Leave a comment

Different ways of creating T-sql table

Posted on December 23, 2014 by MKumar

I figured out following 3 ways in t-sql to create a physical table inside a db. a) Using CREATE TABLE statement create table dbo.EMP (id int); b) Using SELECT..INTO statement select id,name into EMP from EMP1; c) Using Dynamic SQL … Continue reading →

Posted in MS SQL Server | Leave a comment

T_Sql order in Statement and order of Execution:

Posted on December 22, 2014 by MKumar

In our day-to-day SQL life, the most used t-sql code is SELECT statement having more or less other key terms like TOP, JOIN, GROUP BY, etc. There are situations, we might got stuck when referring the column alias in JOIN … Continue reading →

Posted in MS SQL Server | Leave a comment