Tuesday, October 6, 2015

SQL : Sql Query Execution Order

Most programming languages executes set of statements from Top to Bottom but SQL Server executes set of statements in a logical order which is uniquely decides by the SQL Server in a predefined order known as Logical Query processing phase.

Introduction

When we are developing an application our concern mostly related to the code optimization which enhances the performance of application. When we are talking about an application it has broadly divided into two part one is the our code written in any programming language and other is the code written in database which executes at database level and return the results to our application.
In real world most application facing the performance issue which can be improved by code optimization. Here we will talk about the SQL query optimization.Most programming languages executes set of statements from Top to Bottom but SQL Server executes set of statements in a logical order which is uniquely decides by the SQL Server in a predefined order known as Logical Query processing phase.

Sunday, November 16, 2014

Difference between Stored Procedures and User Defined Functions in SQL Server


Stored Procedure
Stored Procedures are pre-compile objects which are compiled for first time and saved, which executes (compiled code) whenever it is called. Stored procedure does not necessarily return data, but it can return more than one result set. It can be used to affect data (update/insert/delete), can have transactions, can have error handling, do not have restrictions about non-deterministic functions, and can call other stored procedures.

User Defined Function
User defined function is compiled and executed every time when it is called. Function attempts to return something, always, and has several restrictions - for example, you can't use DML statements, call stored procedures, call NEWID(), etc. You also cannot have error handling, transactions, or non-deterministic functions (e.g. GETDATE() in SQL Server 2000).

Differences :
  • Stored procedures compiled first time and reuse the execution plan when next time called while function compiled every time when called.
  • Stored procedure can have input parameter as well as output parameter while user defined function can have only input parameters.

Tuesday, August 6, 2013

SQL Server- User Defined Function to Parse HTML- Strip HTML -Without Using Regular Expression

This is a simple tip post. In this post I will show you a user defined function in SQL Server which parse the HTML and retrieve the Text only from it.

In previous posts, I explained Case Sensitive Search in SQL Server, Check Primary Key Existence in Table, Reset Identity Column in SQL Server, Insert Values into Identity column, Identity Column in SQL Server , STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL ServerASP.Net, C#.

Following UDF takes the HTML as input and returns Text only from it. Please note you need to replace all the single quotes (if any) with two single quote (not double quote) in HTML before passing it as input to function.

Thursday, May 2, 2013

SQL Server- Case Sensitive Search in SQL Server

Case Sensitive Search in SQL Server
Today I was working on text search functionality in my application. It was not a very big deal until I had not a requirement for Case Sensitive search functionality. This was new thing (at least for me). I Googled it and find number of search results with same solution. So here I am just adding one more search result for Google.

In previous posts, I explained Check Primary Key Existence in Table, Reset Identity Column in SQL Server, Insert Values into Identity column, Identity Column in SQL Server , STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL ServerASP.Net, C#.

In  SQL Server ,  Installation by default are case insensitive .This means that SQL Server ignores the case of the characters and treats the string 'Dot Net World' equal to the string 'dot net world'.

Saturday, April 13, 2013

How To- Check Primary Key Exists or not in Table

This is a simple tip post that may seem obvious and taken for granted for those of us who have been working with SQL Server for a while now but may be a newbie will find this helpful.

Here, I will give you a simple tip to check that primary is exists or not in table.

You can find some other articles and tips related to C#, ASP.Net and SQL Server in this blog.

Wednesday, April 10, 2013

SQl Server- Reset Identity Column in SQL Server

If you deleted all the records from the table and there is an Identity column in your table, You will see that when you insert a new record in your table then Identity column value starts from the last value of that column incremented by your step value.

If you want to start the Identity column value from the seed value after deleting all the record then you must have reset the Identity column value. Here, I am going to show you that how you can reset Identity column value in SQL Server?.

Friday, April 5, 2013

SQL Server- Insert Values into Identity column

In my previous post Identity Column in SQL Server , I explained about Identity column and how you can create Identity column in table.In this post , I am going to show you that how you can insert values into Identity column.

In previous posts, I explained STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL ServerASP.Net, C#.

Monday, April 1, 2013

SQl Server- Identity Column in SQL Server

In this post, I will explain about the IDENTITY column into table in SQL Server.

In my previous posts, I explained STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server. 

 

Identity Column


IDENTITY column is an auto incrementing column provided by SQL Server. SQL Server will take care of incrementing this column automatically.This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle.

Wednesday, March 13, 2013

SQL Server STR Function

In this post , I will explain the STR function of SQL Server of string functions category.
In my previous posts, I explained SPACE, SUBSTRING, UPPER, STUFF, LEN and other string functions of  SQL Server. You can also find some other articles related to SQL Server.

Here, I am going to explain STR function of SQL Server.

Monday, March 11, 2013

SPACE Function in SQL Server

In this post , I will explain the SPACE function of SQL Server of string functions category.
In my previous posts, I explained SUBSTRING, UPPER, STUFF, LEN and other string functions of  SQL Server. You can also find some other articles related to SQL Server.

Here, I am going to explain SPACE function of SQL Server.

Restore SQL Server Database Using C#

In previous post SQL Server Database BackUp using C#, I explained how to take backup of SQL Server  database using C#.  Today I am going to describe how to restore SQL Server database backup programatically using C# and SQL Server Management Objects (SMO).

You can also check more articles related to C#, ASP.Net , SQL Server and others.

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

Friday, March 8, 2013

SQL Server Database BackUp using C#

There are various ways to take the SQL Server database backup. You can take the database backup using SQL Server backup wizard or using SQL Server BackUp Database statement. Here I am going to describe how to take the SQL Server database backup programatically using C# and SQL Server Management Objects (SMO).

In my previous posts, I explained Partial Methods,Contextual Keyword, C# Static Methods and some other articles related to C#, ASP.Net and SQL Server .

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

SQL Server LOWER Function

In this post , I will explain the LOWER function of SQL Server of string functions category.
In my previous posts, I explained STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server.

Here, I am going to explain LOWER function of SQL Server.

Thursday, March 7, 2013

SQL Server SUBSTRING Function

In this post , I will explain the SUBSTRING function of SQL Server of string functions category.
In my previous posts, I explained UPPER Function, STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function and some other articles related to SQL Server.

Here, I am going to explain SUBSTRING function of SQL Server.

Tuesday, March 5, 2013

SQL Server UPPER Function

In this post , I will explain the UPPER function of SQL Server of string functions category.
In my previous posts, I explained STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server.

Here, I am going to explain UPPER function of SQL Server.

Exporting Query Results to CSV using SQLCMD

In this post , I will explain how to export the query result set to CSV file using SQLCMD.
In my previous posts, I explained STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server.

Here, I am going to explain exporting the query result to CSV using SQLCMD in SQL Server.

Monday, March 4, 2013

SQL Server STUFF Function

In this post , I will explain the STUFF function of SQL Server of string function category.
In my previous posts, I explained UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server.

Here, I am going to explain STUFF function of SQL Server.

Friday, March 1, 2013

SQL Server LEN Function

In this post , I will explain the LEN function of SQL Server of string function category.
In my previous posts, I explained UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server.

Here, I am going to explain LEN function of SQL Server.

SQL Server UNICODE Function

In this post , I will explain the UNICODE function of SQL Server of string function category.
In my previous posts, I explained LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server.

Here, I am going to explain UNICODE function of SQL Server.
^ Scroll to Top