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 1

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


Finding dependencies of a table in sql server

Many a times we face the problem of finding the foreign key relationships, triggers and stored procedures that are referring to a particular table. The below query will help you in identifying the following things:

  1. Find the entire hierarchy of all the parent tables and the corresponding parent table column names and their respective child(s).
  2. Find the entire hierarchy of all the child tables and the corresponding child table column names and their respective parent(s).
  3. The name of the triggers the table is referring to.
  4. The name of the stored proc which uses the table.

Let me elaborate this with the help of an example to have better understanding.

Let’s say I have created 3 tables as follows:

CREATE TABLE parent
(
parent_col1 int primary key,
parent_col2 int
)

GO
CREATE TABLE Child1
(
Child1_col1 int primary key,
Child1_col2 int Foreign key references parent(parent_col1)
)
GO

CREATE TABLE Child2
(
More

Tracing deadlock with Extended events

In last two articles, we have learned how to capture deadlock using SQL Server Profiler and Tracing deadlock with Trace Flag 1222 and Trace Flag 1204. Now in this post we are going to learn a very useful method of tracing deadlock with Extended Events. These events are less resource intensive and light weight in comparison of SQL Server Profiler. We use these events to capture interested information to be analyzed now or later from heavy load servers (Like production server).

If we don’t have any clue about deadlock being occurred in midnight at our production server and because of heavy load on server, we can not use SQL Server Profiler their. Extended events are very useful because of very few resource utilization. We can create a session and start it to capture only those events in which we are interested. Extended events are very useful in such types … More


Tracing deadlock with Trace Flag 1222 and Trace Flag 1204

In previous article, we have already learned that how we can trace a deadlock situation with SQL Server Profiler. In case you don’t read, click here. And to understand locking, blocking and deadlocking in sql server, click here. In this post we will learn how we can use “trace flag 1204 and trace flag 1222” to trace a deadlock.

In this article we are going to learn how we can trace SQL Server deadlock and extract the deadlock information from captured data. We will use DBCC TRACEON command, Trace Flag 1204 and Trace Flag 1222 to capture the details.

What is DBCC (Database Console Commands)?

DBCC statements act as Database Console Commands for SQL Server. We can run it using TSQL programming language. It provides commands related to various categories like Maintenance, Miscellaneous,  Informational and Validation.

Deadlock

As we know that Deadlock in SQL Server is a situation … More