SQL Server: Allow Only Alpha Numeric Characters to a Column

Here is how to restrict non alpha numeric characters to a column in SQL Server

--Create Demo Table
 CREATE TABLE AlphaNumDemo
 (
 DemoCode NVARCHAR(100)
 )
 GO

--Restrict Special Characters
 ALTER TABLE AlphaNumDemo
 ADD CONSTRAINT CHK_AllowAplhaNumericCharactresOnly
 CHECK (DemoCode NOT LIKE '%[^a-zA-Z0-9 ]%')
 GO

--Insert Test Data
 INSERT INTO AlphaNumDemo VALUES ('demo1')
 INSERT INTO AlphaNumDemo VALUES ('Demo1')
 INSERT INTO AlphaNumDemo VALUES ('Demo 1')
 INSERT INTO AlphaNumDemo VALUES ('Demo-1')
 INSERT INTO AlphaNumDemo VALUES ('#1Demo')
 GO

--Check Data
 SELECT DemoCode FROM AlphaNumDemo
 GO

--Cleanup
 DROP TABLE AlphaNumDemo
 GO

In the above demonstration observe the records with special characters will not be inserted to the table.

SQL Server: Kill process running xp_cmdshell

In our last post we discussed how to run a VB Script from SQL Server. Here I will share something interesting related to that task. Well, while extending the program logic of my VB Script I have used some message boxes to check the flow of some variable values. I also ran that script from command line, that finished successfully.

Now, when I try it to run my TSQL code (which uses xp_cmdshell to run that script) and observed that it is taking an unexpected time to complete !!!  After sometime I think of killing the process and killed, but still the query was running where I got a Success message from KILL !!!

Here is the reason behind that…after testing my script I forgot to remove/comment the message boxes, which cause my TSQL to run for an indefinite time.

Solution:

• The script or application we are calling from TSQL (xp_cmdshell) should be self-sufficient, i.e. application should not have any user interaction like InputBox or MessageBox, Visual Windows…etc.
• To stop the query which call such applications, we need to end the process from windows Task Manager.

Note: xp_cmdshell always run the target application in background and user can’t end it interactively to regain the control from that application.

More information for this topic is available here on Microsoft Support site or refer Book Online. Hope this helps you.

lated to

SQL Server: Running VB Script From TSQL

In this post we will see if we can run a VB Script from SQL Server.  Yes, SQL Server allows to run external Scripts. So, here is the process how to run a VB Script from TSQL Code.

Well to test this lets first create a sample VB Script which will just display a message.

Dim VarSetMessage
VarSetMessage  = "Running VB Script from SQL Server..."

'Display message
WScript.Echo VarSetMessage

Save it as “myMsg.vbs” on “C:\” drive of your machine. Okey, before running this script from SQL Server, lets see how to run it from Command Prompt. To do this:

Start => Run => cmd => ENTER
Type Cscript C:\myMsg.vbs => ENTER

(Refer below image for result)

Now to run the VB Script from SQL Server just run the same command line we tested above using xp_cmdshell from SQL Server.

EXEC XP_CMDSHELL 'CScript C:\myMsg.vbs'

and we are done !!!

Note: You may need to configure xp_cmdshell if not configured before.

SQL Server: SET vs SELECT in Variable Assignment

Most often we are using SET and SELECT interchangeably while assigning values to variable, however there are differences between them. In this post we will discuss some diff between SET and SELECT.

• SET can assign value to only one variable at a time, where as SELECT can assign values to multiple variables at once.

Example

--Declare variable
DECLARE @myVar1 INT, @myVar2 VARCHAR(10);

--Assign value to variable using SET
SET @myVar1 = 1234;
SET @myVar2 = 'SQL Journey';

--Extract varible values
SELECT @myVar1, @myVar2

--Assign value to variable using SELECT
SELECT @myVar1 = 1234, @myVar2 = 'SQL Journey';

--Extract varible values
SELECT @myVar1, @myVar2

• SET operation will raise an error if when result of a query returning multiple values is used to assign value to a variable. However  SELECT will not raise any error and assign the last record of query result to the variable. So, Microsoft recommends SET for variable assignment

Example:

CREATE TABLE #TestVar(myID int, myComments VARCHAR(30))
INSERT INTO #TestVar VALUES(1, 'A'),(2, 'B'),(3, 'C'),(4, 'D'),(4, 'E')

--Using SET
--Declare variable
DECLARE @myVar3 VARCHAR(30);
--Assign query result (returning multiple values) to variable
SET @myVar3 = (SELECT myComments FROM #TestVar WHERE myID = 6)
--Extract varible values
SELECT @myVar3

/****Result
Msg 512, Level 16, State 1, Line 8
Subquery returned more than 1 value.
This is not permitted when the subquery follows =, !=, <, <= , >, >= or
when the subquery is used as an expression.
****/

--Using SELECT
--Declare variable
DECLARE @myVar4 VARCHAR(30);
--Assign query result (returning multiple values) to variable
SELECT @myVar4 = myComments FROM #TestVar WHERE myID = 4;
--Extract varible values
SELECT @myVar4;

• In the above case if the query result will not return any values then the variable value will be NULL either using SELECT or SET.

• While talking in terms of performance variable assignment using SET will take less time as compared to SELECT.

SQL Server: Remove Non-Alphanumeric Characters from a Column

In database we often need to clean the non-alphanumeric characters in some column of a table. Here is the code I use to remove non-alphanumeric characters in Sql Server.

Create the table with sample data:

   1: CREATE TABLE STRSTORE(COMMENT VARCHAR(2000))

   2:  

   3:  

   4: INSERT INTO STRSTORE(COMMENT) VALUES

   5: ('1.We^lcom&<e> to Sql{Journey}.&'),

   6: ('2=http://sqljourney.wordpress.com/'),

   7: ('3#[S$tring] &Cle<@anUp'),

   8: ('4-We* are'' do+ne~')

 

Remove non-alphanumeric characters from the column.

   1: WHILE @@ROWCOUNT > 1

   2:     UPDATE STRSTORE

   3:     SET COMMENT = REPLACE(COMMENT, SUBSTRING(COMMENT, PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT), 1), '')

   4:     WHERE PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT) <> 0

   5:  

   6:  

   7: SELECT * FROM STRSTORE

 

Let me also know if you have some better ideas.

SQL Server: Keyboard Shortcuts – Part 1

Writing clean code is an important part in development activity. People sometimes ignore this may be because of some or other reason, which creates difficult to understand the code in later stage when it need changes. Here are some quick keyboard shortcuts for SQL Server Management Studio, which helps to write clean code and speed up development works.

So the below shortcuts related to code editing and execution in SSMS.

Purpose	Keyboard Shortcut
Open New Query Window	CTRL + N
Comment selected text	CTRL + K then CTRL + C
Make selected text to UPPER case	CTRL + SHIFT + U
Make selected text to LOWER case	CTRL + SHIFT + L
Display estimated execution plan	CRTL + L
Execute query	F5 (Or) ALT + X (Or) CTRL + E
Show / hide result pane	CTRL + R
Toggle between Query & Result pane	F6

 

So have quick and clean coding…

Fixing Error: 8134 (Devided By Zero Error Encountered) in SQL Server

Problem:

While performing mathematical operations it throws Divided by zero error.

Msg 8134, Level 16, State 1, Line 5
Divide by zero error encountered.

This situation often arises in production databases if the script has not been tested with sufficient data before putting the script to production database. This happens when a number is divided by 0 (zero).

Solution:

There can be many ways to handle this error. Here are some of my workarounds in SQL Server.

1. Using NULLIF & ISNULL/ COALESCE
2. Using CASE
3. Using ARITHABORT & ANSI_WARNINGS

Method: 1

SELECT ISNULL(Number1 / NULLIF(Number2, 0), 0) AS [Result]
FROM tbl_err_8134

In this method uses NULLIF. In this case when the divisor is 0 (Zero) it will return NULL to the divisor, so the result will also became NULL. Then by IFNULL it returns 0 as the result is NULL here.

Method: 2

SELECT CASE WHEN Number2 = 0 THEN 0 ELSE Number1 / Number2 END AS [Result]
FROM tbl_err_8134

In this method uses CASE. Here when the divisor is 0 (Zero) it will return 0 as result or else the result will be division of two numbers.

Method: 3

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
GO

SELECT ISNULL(Number1 / Number2, 0) AS [Result]
from tbl_err_8134

Here when ARITHABORT & ANSI_WARNINGS are set to OFF it will continue processing and will return NULL as a result. To know more about ARITHABORT you can follow this link.

Download the complete script file here.

Finding Nth highest number in SQL Server

Many times you face this question while working with real time data and most interviews. In this post we will go through different ways to find the records with Nth highest number. Use this script to populate some sample data to test the results.

CREATE TABLE PLAYER
(
PLAYERID INT IDENTITY(100,2) NOT NULL,
NAME VARCHAR(100) NOT NULL,
DOB DATETIME,
COUNTRY VARCHAR(3),
PRIZEMONEY MONEY NOT NULL
)
GO

INSERT INTO PLAYER(NAME,DOB,COUNTRY,PRIZEMONEY) VALUES
('Andre Agassi','4/29/1970','USA',$31152975),  ('Rafael Nadal','6/3/1986','ESP',$27224163), ('Boris Becker','11/22/1967','GER',$25080956), 
('Yevgeny Kafelnikov','2/18/1974','RUS',$23883797), ('Ivan Lendl','3/7/1960','USA',$21262417),  ('Stefan Edberg','1/19/1966','SWE',$20630941),
('Goran Ivanisevic','9/13/1971','CRO',$19876579),  ('Michael Chang','2/22/1972','USA',$19145632), ('Lleyton Hewitt','2/24/1981','AUS',$18312036),
('Andy Roddick','8/30/1982','USA',$17109084), ('Novak Djokovic','5/22/1987','SRB',$15984098), ('Gustavo Kuerten','9/10/1976','BRA',$14807000),
('Jonas Bjorkman','3/23/1972','SWE',$14600323),  ('Marat Safin','1/27/1980','RUS',$14373291), ('Jim Courier','8/17/1970','USA',$14034132), 
('Carlos Moya','8/27/1976','ESP',$13382822), ('Nikolay Davydenko','6/2/1981','RUS',$1339499),  ('Michael Stich','10/18/1968','GER',$129528),
('Juan Carlos Ferrero','2/12/1980','ESP',$12588898),  ('John McEnroe','2/16/1959','USA',$12552132), ('Thomas Muster','10/2/1967','AUT',$12225910), 
('Tim Henman','9/6/1974','GBR',$11635542), ('Sergio Bruguera','1/16/1971','ESP',$11632199),  ('Patrick Rafter','12/28/1972','AUS',$11127058),
('Thomas Enqvist','3/13/1974','SWE',$10461641),  ('Petr Korda','1/23/1968','CZE',$10448900), ('Alex Corretja','4/11/1974','ESP',$10411354), 
('Todd Woodbridge','4/2/1971','AUS',$10095245), ('Richard Krajicek','12/6/1971','NED',$10077425), ('Roger Federer','8/8/1981','SUI',$53362068), 
('Pete Sampras','8/12/1971','USA',$43280489)
GO

Here is 4 different ways to find the same result.

--Define Level of N
DECLARE @N INT = 3

--Method-1: Without using any functions
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT DISTINCT PRIZEMONEY FROM PLAYER p1 WHERE (@N-1)=
(SELECT COUNT(DISTINCT p2.PRIZEMONEY) FROM PLAYER p2 WHERE p2.PRIZEMONEY > p1.PRIZEMONEY ))

--Method-2: Using TOP
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT TOP 1 PRIZEMONEY FROM PLAYER
WHERE PRIZEMONEY in (SELECT DISTINCT TOP (@N) PRIZEMONEY FROM PLAYER ORDER BY PRIZEMONEY DESC)
ORDER BY PRIZEMONEY)

--Method-3: Using MIN() function
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT MIN([t].[amt]) FROM
(SELECT DISTINCT TOP (@N) PRIZEMONEY AS [amt] FROM PLAYER ORDER BY PRIZEMONEY DESC) AS [t])

--Method-4: Using Dense_Rank()
;WITH cte
AS (
SELECT NAME,DOB,COUNTRY,PRIZEMONEY,  DENSE_RANK() OVER (ORDER BY PRIZEMONEY DESC) AS [rnk]
FROM PLAYER t
)
SELECT NAME,DOB,COUNTRY,PRIZEMONEY
FROM cte
WHERE [rnk] = @N

Also let me know your suggestions on this.

CHECK constraint with User Defined Function in SQL Server

This post describes how to use result of an user defined function with CHECK constraint in SQL Server.  For demonstration, considered a situation where it is not  allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.

So for this first we need to create the function before creating the CHECK constraint. Here is a function which will return age as per the date of birth provided.

/*
This function will take Date Of Birth as input parameter,
and returns Age in Years to the caller
*/
CREATE FUNCTION [dbo].[fnGetAge](@DateOfBirth DATETIME)
RETURNS SMALLINT
AS
BEGIN
DECLARE @Age SMALLINT
SET @Age =(DATEDIFF(YY, @DateOfBirth, GETDATE())-
(CASE
WHEN GETDATE() >= DATEADD(YY, DATEDIFF(YY, @DateOfBirth, GETDATE()), @DateOfBirth) THEN 0
ELSE 1
END))
RETURN @Age
END;
GO

Now create a table where CHECK constraint will refer to this function to check if the age of the person meets the required criteria or not (minimum 18 Years in this case).

--Create Customer table
CREATE TABLE Customers
(
CustID INT IDENTITY(1,1) NOT NULL,
CustName VARCHAR(100) NOT NULL,
DateOfBirth DATETIME NOT NULL,
Email VARCHAR(100),
CONSTRAINT pkCustomers PRIMARY KEY(CustID),
--Calculate & check if age of customer is atleast 18 Years
CONSTRAINT chkCheckAge CHECK(dbo.fnGetAge(DateOfBirth) >= 18)
)
GO

--Populate table with some sample data.
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('ABC','19810726','abc@cust.info'),
('XYZ','19840510','xyz@cust.info'),
('MNO','19720417','mno@cust.info')
GO
--Result Message
--(3 row(s) affected)

Now try to insert a record where calculated age is less than 18 years and see what happens.

--Try to insert a record where Age less than 18 Years(as per provided Date of Birth)
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('TEST','20010315','test@cust.info')
GO

--Error Message
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "chkCheckAge".
The conflict occurred in database "SQLJourney", table "dbo.Customers", column 'DateOfBirth'.
The statement has been terminated.

As the age does not meet the required criteria in defined CHECK constraint, it doesn’t allow to insert this record to the table.

Note:

CHECK constraint evaluates the provided expression while UPDATE operation as well.

You cannot DROP the function till the table (which refers to that function) exists in the database.

Generate Report with Alternate Order of an Attribute (say Gender) in SQL Server

Some times we may need to generate a report which display employees/customers in alternate order of any particular attribute.This post describes for employees with alternate order of Gender i.e. first a Male employee then a Female employee and so on or vice versa.

In SQL Server this is just a simpler way. There could be may ways to do this. Here is my set based approach with ROW_NUMBER() function.

DECLARE @EMPLOYEE TABLE
(
NAME VARCHAR(100) NOT NULL,
GENDER VARCHAR(1) NOT NULL
)

--Populate sample data
INSERT INTO @EMPLOYEE(NAME,GENDER) VALUES
('Anup','M'), ('Sheetal','F')
,('Sonam','F'), ('Payal','F')
,('Parul','F'), ('Peter','M')
,('Rahul','M'), ('Roxy','F')
,('Preeti','F'), ('Manav','M')

SELECT * FROM @EMPLOYEE

--Result
;WITH CTE(SEQ,ENAME,GENDER)
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY GENDER ORDER BY GENDER), NAME, GENDER FROM @EMPLOYEE
)
SELECT ENAME,GENDER FROM CTE
ORDER BY SEQ, GENDER DESC
GO
 

Also put your valuable suggestions on this.