SQL Server

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new table with same structure and identity column and copy existing table records to this table and drop the... By Ali Adravi   On 02 May 2013  Viewed: 1,096
  • sql server
  • identity

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but hope fully you will get something for you which can may you happier, so let’s start one by... By Ali Adravi   On 31 Mar 2013  Viewed: 936
  • sql server

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get our result, see this Declare @price Decimal(10,2) Set @price=12345678.90 Select... By Myghty   On 26 Mar 2013  Viewed: 3,394
  • sql server
  • parsename

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see when we need wildcards, sometimes we need to search records on the basis of some pattern then we... By Dev D   On 25 Feb 2013  Viewed: 678
  • sql server

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's say we have a customers table with following structure and data ![alt text][1] Now suppose we... By Ali Adravi   On 13 Feb 2013  Viewed: 12,778
  • sql server

Bulk insert into SQL Server from CSV file by skipping first row

Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma? How to load CSV file to SQL Server database? We will discuss here the same but with more detail, so... By Ali Adravi   On 08 Feb 2013  Viewed: 17,334
  • bulk-insert
  • sql server
  • csv

Error cannot be caught by a TRY…CATCH Construct in SQL Server

TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error. -... By Ali Adravi   On 28 Dec 2012  Viewed: 586
  • sql server

SQL transaction status and XACT_STATE()

XACT_STATE() is a scalar function that gives the user transaction state of a current running request. It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not. XACT_STATE returns the following three values - **1**: The current request has an active user transaction. The request can perform any actions, including... By Ali Adravi   On 28 Dec 2012  Viewed: 8,032
  • sql server

Conditional where clause in SQL server and tricks

*Suppose you want to search the records on the basis of start and end date and for that if ModifiedDate is not null then use it otherwise use the Creation date:* Simply we can use, if in where clause like this If ModifiedDate IS NULL Where CreationDate Between @StartDate And @EndDate Else Where ModifiedDate Between @StartDate And @EndDate If there... By Ali Adravi   On 14 Jul 2012  Viewed: 672
  • sql server

Prevent SQL Injection in your application

I found many web sites where easily inject the SQL and logged in there, so what is wrong with these web site code. Are they not authenticating the user information? They are, so how can anyone can logged in without creating the account? Let’s say you have written code to authenticate the user from your database like this: SqlCommand cmd = new SqlCommand(); cmd.CommandText = "Select... By A K Sinha   On 14 Jul 2012  Viewed: 537
  • sql server

With statement and it's usage in sql server 2008

A new feature introduced in sql server 2005 named common table expression, it's really cool feature to use in many cases to solve the complicated problems in sql server. **As defined by the Microsoft:** It specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single *SELECT, INSERT,... By Liam Kelley   On 04 Jul 2012  Viewed: 390
  • sql server

How to retrieve data from database using JavaScript in asp.net

I was checking how to validate a user email from database without post back, using JavaScript. After some search and RND found the way to call the web service method with the help of Ajax and JavaScript. Let’s say we have a registration page on which we accept email from the user and we expect it should be unique in entire database. So we need a method to immediately validate the email from... By Ali Adravi   On 24 Jun 2012  Viewed: 14,670
  • javascript
  • sql server
  • asp.net