Yearly Archives: 2014


Count of total not null values from all columns of a table 1

In this post, we will count not null values from all the columns of a given table. One of my friend asked me to get the count of all not null values from all the columns of a given table. I used a CASE statement in my query and achieved this task but again i thought can i do this with the help of COUNT function too? And then i found an another way to achieve this task too. Here i am sharing both the ways. I am also sharing a trick to find only NULL values too.
To demonstrate this first create a table and put some demo data as below;

Create a Table:

CREATE TABLE dbo.TEST
(
    ColA VARCHAR(10),
    ColB VARCHAR(10),
    ColC VARCHAR(10),
    ColD VARCHAR(10),
    ColE VARCHAR(10),
    ColF VARCHAR(10)
)
GO

Add rows:

INSERT INTO dbo.TEST(ColA, ColB, ColC, ColD, ColE, ColF)
VALUES('Mango', 'Apple', 'Banana', NULL, NULL, NULL),
('Mango', 
More

sp_executesql vs execute in SQL Server 4

In this post “SP_EXECUTESQL vs Execute”, we are going to compare sp_executesql and execute in SQL Server. Apart from differences, we will also discuss the similarities between sp_executesql and execute commands. Lets start with few questions like, What is the difference between Execute and sp_ExecuteSQL? How can we execute a T-SQL String? Similarities between Execute and sp_ExecuteSQL. Execute and sp_ExecuteSQL common features. Benefits of sp_ExecuteSQL. Which is better to use sp_ExecuteSQL or EXEC? Execute vs sp_ExecuteSQL.

Let’s discuss these points one by one.

Difference between sp_ExecuteSQL vs Execute

sp_ExecuteSQL

sp_ExecuteSQL is also used to execute a T-SQL string in SQL Server and points are below:

  • It allows parametrization and hence more secure than EXEC command to execute a SQL string dynamically. It’s tough to inject.
  • We don’t need to cast the parameter values as like we need in EXEC command. We simply put the parameter name in T-SQL string as
More

Convert date range into date list in SQL Server 3

In this post “Convert date range in date list”, we will convert a given date of range into list of dates as per the business requirement. Just have a look on mentioned demo table which has three columns CustomerID, StartDate and EndDate. I want to generate the date list for the given date range for each customer. For example if we have a entry as CustomerID – “1”, StartDate – “10-Dec-2012” and EndDate – “19-Mar-2013”, then this should return a date list for CustomerID 1 with 4 rows. First row for 10-Dec-12 to 31-Dec-12, Second row for 01-Jan-13 to 31-Jan-13, Third row for 01-Feb-13 to 28-Feb-13 and fourth row for 01-Mar-13 to 19-Mar-13.

Mind that in first row i have started with the actual start date of the given date range and in the last row i have put the end date as actual end date. But if there is … More


Truncate vs Delete in SQL Server

Difference between DELETE and TRUNCATE in SQL Server

Let’s discuss this point “Truncate vs Delete” below in simple way.

  1. Delete is a DML statement whereas Truncate is a DDL statement.
  2. Delete commmand deletes each row from the table which are satisfying the WHERE clause and logs it in the log file where as Truncate command deallocates the data pages and logs it in the log file.
  3. Delete performs slower than truncate because of logging mechanism it has whereas Truncate is faster than delete.
  4. Delete can remove full or partial data as per it’s filter defined in WHERE clause whereas Truncate does not support WHERE clause and all data will be removed from table.
  5. Delete executes the Triggers on the table if it has any because it is a DML command whereas Truncate does not execute the Triggers.
  6. Delete requires more locks than truncate because each row is locked for deletion
More

Full Text Search on files in SQL Server

What is Full Text Search in SQL Server?

Full Text Search in SQL Server enables us to perform complex queries against character based data. Full Text Search supports char, varchar, nchar, nvarchar, text, ntext, image, varbinary and xml data types. We Can store document files in varbinary(max) format with their extensions and enable Full-Text search on it. SQL Server supports many types of document files for Full-Text indexing some of them are .asp, .aspx, .ascx, .c, .doc, .html, .ppt, .txt, .xls and many more. Some other extension like .pdf provides it’s own “Filter Pack” to enable Full-Text index on pdf files in SQL Server.

To enable Full Text Search on one or more columns of the given table or indexed view, we have to create a Full Text Index on it. After creating a full text index, we can perform word searching or phrase searching on a column based on … More


Fill Factor in SQL Server

Do you know what is FillFactor for an index? In this post, we will discuss about Fill Factor in SQL Server. Lets start discussion about FILLFACTOR in SQL Server.

What is FILLFACTOR?

Fill factor in SQL server is used to control the filled space of leaf pages with data. Remaining space is left to accommodate future growth of data inside the page. The default value for fill factor is 0 i.e. packed to 100%. Fill factor has a percentage value which could be anything between 1 and 100 including both. A page in SQL Server is the smallest unit of 8K, which holds the number of rows depending on the size of the row.

Setting Fill Factor

Fill factor can be set either at Server Level or at index level. Just see each in action below;

Setting Fill Factor at Server Level

By default it’s 0 at server level, but … More


Creating primary key without clustered index 1

One of my colleague asked me a question that “Can we create a primary key without a clustered index?”. I answered him that “Yes, of-course!” and also did not forget to share this information to all of you in this post named “Create nonclustered primary key”. I know many of us are well aware of this and have did this many times in our day to day SQL activity. Still for those who don’t know how they can accommodate a primary key with a nonclustered index, i am sharing this post, in which i will show you that how we can create a non-clustered primary key on a table.

Before creating this nonclustered primary key, here i am showing you the default behavior of SQL Server during Primary key creation. In case table does not have any Clustered index created on it, SQL Server creates a PRIMARY KEY on a … More


Dynamic management views and functions in SQL Server 1

In this post named “Dynamic management objects in SQL Server”, we will explore the DMVs and DMFs in SQL Server. Dynamic management views (DMVs) and dynamic management functions (DMFs) are used to extract current internal information of the Server State using T-SQL commands. Formerly such types of data were only available in Performance Monitor (a Microsoft tool to analyze System performance), now SQL Server has exposed such information with some other important information related to current server state through dynamic management views and dynamic management functions. Both dynamic management views and dynamic management functions are collectively known as Dynamic Management Objects (DMOs). These objects were introduced primarily in SQL Server 2005 which allow us to monitor the current internal status of SQL Server using simple T-SQL.

DMVs and DMFs are very useful in collecting current performance related data of the system. We can categorize these objects in two groups as … More


Open SQL Server Management Studio as different windows user

Have you ever required to open SQL Server Management Studio Application (SSMS instance) as different windows user from your local machine? Sometimes we require to “Run SSMS instance as different Windows user” another machine which is not available physically to us. In such type of scenarios we can open our local SSMS instance as different windows user at our local machine. In this article we are going to learn how we can open SSMS instance as different windows user. If you are still not required this till now, may be sometime in future you will be required to do the same.

Now to accomplish this task i.e. to open SSMS as a different windows user, we have below approaches;

  1. Using Run as different user option in windows explorer
  2. Using runas command in command prompt
  3. Using runas command with “.bat” file

We will cover all the above techniques below.

1. Using

More

Deterministic and non-deterministic functions in SQL Server 6

In this topic “Deterministic and non-deterministic functions”, we are going to discuss the deterministic and non-deterministic functions in sql server. Lets start with “what is a deterministic function in sql server?”. It’s a common interview question also. I will elaborate the definition of deterministic as well as non-deterministic functions and also provide some good examples. So after reading this article you will be able to distinguish deterministic and non-deterministic functions.

Deterministic Functions

The functions which always return same output when they called with a particular set of input values in the same state of the database are known as deterministic functions.

SELECT ISNULL('ABCDEF', '1')

Above t-sql code will always return the output as ‘ABCDEF’ and thus ISNULL is a deterministic function. Below is the list of some other deterministic functions.

ISNULL, ISNUMERIC, DATEDIFF, POWER, CEILING, FLOOR, DATEADD, DAY, MONTH, YEAR, SQUARE, SQRT etc.

Non-deterministic Functions

The functions which may return … More