SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Archive for July, 2010

SQL Server: SET vs SELECT in Variable Assignment

Posted by Prashant on July 14, 2010


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


--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.

Posted in Interview Questions, SQL Server | Tagged: , , , , , , | Leave a Comment »

SQL Server: Remove Non-Alphanumeric Characters from a Column

Posted by Prashant on July 13, 2010


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:

CREATE TABLE STRSTORE(COMMENT VARCHAR(2000))

INSERT INTO STRSTORE(COMMENT) VALUES
('1.We^lcom&<e> to Sql{Journey}.&'),
('2=https://sqljourney.wordpress.com/'),
('3#[S$tring] &Cle<@anUp'),
('4-We* are'' do+ne~')

Remove non-alphanumeric characters from the column.

WHILE @@ROWCOUNT > 1
UPDATE STRSTORE
SET COMMENT = REPLACE(COMMENT, SUBSTRING(COMMENT, PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT), 1), '')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT) <> 0

SELECT	*
FROM STRSTORE

Let me also know if you have some better ideas.

Posted in Interview Questions, SQL Server | Tagged: , , , , , , , , , | 1 Comment »

SQL Server: Keyboard Shortcuts – Part 1

Posted by Prashant on July 8, 2010


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…

Posted in Interview Questions, SQL Server | Tagged: , , , | 1 Comment »

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

Posted by Prashant on July 2, 2010


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.

Posted in Interview Questions, SQL Server | Tagged: , , , , , , | 6 Comments »

 
%d bloggers like this: