SQL Server

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: 770
  • 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: 715
  • sql server
  • identity

Get only date or time from a datetime column in sql server

In Sql Server we use DateTime column but in many cases we need to get either only date or only time. So we will see different ways to get these values according to our requirements. If you are using SQL Server 2008 or newer version then luckily we have two types Date and Time Let’s see this in action with Sql Server 2008 (it will not work in older version of SQL) SELECT Getdate()... By Hamden   On 07 Sep 2013  Viewed: 639
  • sql server

Handle error in your database base by using using a single procedure

How to handle all the error in sql server database, it is very easy, you will say the same after reading this article. If I ask the same question right now many of us start thinking how we can use it, do we need to create a table, procedure and what would be useful values which should be in this table and how to call the procedure from every procedure, how to page the parameter etc. stop thinking... By Ali Adravi   On 23 Feb 2014  Viewed: 611
  • sql server
  • procedure
  • error-log

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: 591
  • sql server

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: 537
  • sql server

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: 479
  • sql server

How to get number of days in a month in SQL Server?

Sometimes we need to get the total number of days in month for given date, there is no build in function which can help us to directly use and get our result, so we need to write a small SQL statement to get the total number of days for a particular date. I found on some blog sites to use this but it will not work for every date DECLARE @date DATETIME SET @date = '05/17/2020' ... By Hamden   On 14 May 2013  Viewed: 474
  • sql server

UNION VS UNION ALL in Sql Server with Example

UNION and UNION ALL is the way to select records from different tables having the same number and type of columns. Union internally use DISTINCT to remove the duplicate records while Union All don't use distinct and shows all the records even duplicate records. -- Table A -- Table B -- Table C ID VALUE ID VALUE ID VALUE 1 ... By Ali Adravi   On 14 Jun 2013  Viewed: 463
  • union
  • union-all
  • 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: 456
  • sql server

New conversion functions in sql server 2012

SQL Server introduced three new conversion functions to convert an expression to another data type, in this post we will see those functions and their usage **Conversion Functions** - PARSE - TRY_PARSE - TRY_CONVERT **PARSE:** This function parse the value and return then result, relies on CLR. It will take some performance overhead. Syntax PARSE (string_value AS data_type [... By Hamden   On 15 May 2013  Viewed: 348
  • 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: 321
  • sql server
12