Archive

Archive for the ‘SQL Tips’ Category

Convert Hex to String – MSDN TSQL forum

December 22, 2010 Leave a comment

–> Question:

How can I convert the hex code ‘0x16004D616E75623232’ to string?

An online utility provides me its equivalent: http://www.string-functions.com/hex-string.aspx

But how can I do this by TSQL, CLR would be a second thought.
 

–> Answer:

Didn’t thought it was so simple, actually my hex string had ‘1600’ prefixed after 0x, on removing it I’m getting the expected o/p, which is:

SELECT CONVERT(varchar(64), 0x4D616E75623232, 0)

 

–> Answer from Hunchback:

If you are using SS 2008 or earlier, then check function CONVERT in BOL.

SELECT
  CONVERT(varbinary(64), '0x16004D616E75623232', 1),
  CONVERT(varchar(64), 0x16004D616E75623232, 1),
  CONVERT(varchar(64), 0x16004D616E75623232, 2);
GO

For lower versions you can use some tricks from Umachandar Jayachandran, link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/20e92e10-a0ab-4a53-a766-76f84bfd4e8c
 

Ref link

Categories: SQL Tips Tags: ,

SQL Error – UPDATE statement conflicted with the FOREIGN KEY/REFERENCE constraint

December 21, 2010 11 comments

Just replied on MSDN forum’s following post: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/289c4ca5-6ab2-4ed6-b446-0300f5c7dc88

When tables are referenced by Foreign Key constraints it gets difficult to UPDATE the key columns.
An example shows this issue and how one can perform UPDATE on FK columns.

USE [tempdb]
GO

-- CREATE test tables
CREATE TABLE dept (d_id INT PRIMARY KEY, dept VARCHAR(50))

CREATE TABLE emp (sn INT PRIMARY KEY, ename VARCHAR(50),
	d_fk INT CONSTRAINT fk_dept_d_fk FOREIGN KEY (d_fk) REFERENCES dept(d_id))

-- INSERT test data
INSERT INTO dept
SELECT 1, 'finance' UNION
SELECT 2, 'hr'

INSERT INTO emp
SELECT 1, 'Manoj', 1 UNION
SELECT 2, 'Saurabh', 2 UNION
SELECT 3, 'Kanchan', 1 UNION
SELECT 4, 'Pooja', 2

-- SELECT the INSERTed data
SELECT * from dept

d_id	dept
1	finance
2	hr	-- DeptID needs to be updated to 3
SELECT * from emp

sn	ename	d_fk
1	Manoj	1
2	Saurabh	2	-- DeptID should also be updated here to 3
3	Kanchan	1
4	Pooja	2	-- DeptID should also be updated here to 3
-- Now lets UPDATE the FK column values
UPDATE dept SET d_id=3 WHERE d_id=2

Error Message:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "fk_dept_d_fk".
The conflict occurred in database "tempdb", table "dbo.emp", column 'd_fk'.
The statement has been terminated.
UPDATE emp SET d_fk=3 WHERE d_fk=2

Error Message:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the FOREIGN KEY constraint "fk_dept_d_fk".
The conflict occurred in database "tempdb", table "dbo.dept", column 'd_id'.
The statement has been terminated.

We can disable the FK constraints by ALTER statement before the UPDATE and enable them after the UPDATE, like:

-- Disable FK Constraint
ALTER TABLE emp NOCHECK CONSTRAINT fk_dept_d_fk

-- Perform UPDATE
UPDATE dept SET d_id=3 WHERE d_id=2
UPDATE emp SET d_fk=3 WHERE d_fk=2

-- Enable FK Constraint
ALTER TABLE emp WITH CHECK CHECK CONSTRAINT fk_dept_d_fk

-- Following SELECTS will show the new values in updated records:
SELECT * from dept

d_id	dept
1	finance
3	hr	-- Updated record
SELECT * from emp

sn	ename	d_fk
1	Manoj	1
2	Saurabh	3	-- Updated record
3	Kanchan	1
4	Pooja	3	-- Updated record
-- Final Cleanup, DROP the tables.
DROP TABLE emp
DROP TABLE dept

MSDN BOL: http://msdn.microsoft.com/en-us/library/10cetyt6(v=VS.80).aspx

Categories: SQL Errors, SQL Tips Tags: ,

SQL Server CURSOR and it’s Life Cycle

October 24, 2010 1 comment

A CURSOR in SQL is a database object that contains a set of records that you can traverse one-by-one, rather than the SET as a whole.

SQL is a set-based language and produces a complete result set, and the SQL queries works on this complete set only, and not on individual rows. But there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

CURSOR in SQL language gives you the flexibility to traverse records like the way you do in other programming languages with iterators and for-loop.
 

–> A simple Cursor life cycle with minimum definition:

USE [AdventureWorks]
GO

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Before Declare'

DECLARE myCursor CURSOR
FOR SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID BETWEEN 100 and 102

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'After Declare'

DECLARE @ContactID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50)

OPEN  myCursor

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'Open Cusrsor'

FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
WHILE @@fetch_status=0
BEGIN

	-- SQL Statements with logic inside
	SELECT @ContactID, @FirstName, @LastName

	FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
END

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'While loop exited, all rows iterated'

CLOSE myCursor

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor is Closed'

DEALLOCATE myCursor

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor Deallocated'

 

Do check & Like my FB Page.

Categories: Datatypes, SQL Tips Tags: , , , ,

Multiple ways to INSERT records in a table

October 15, 2010 4 comments

The following exercise shows multiple ways to INSERT records in a table, as the post title says.

USE [tempdb]
GO

CREATE TABLE sometable(a INT, b VARCHAR(20), c INT)
GO

-- Method #1 - Simple INSERT statement
INSERT sometable (a, b, c)
VALUES(1, 'New York', 123)
GO

DROP TABLE sometable

-- Method #2 - CREATE the table and INSERT records. This is minimally logged operation and faster than explicitly creating table and inserting records.
SELECT 1 a, 'New York' b, 334 c
INTO sometable
UNION
SELECT 2, 'London', 823
UNION
SELECT 3, 'Paris', 1124
UNION
SELECT 4, 'Munich', 2080
GO

-- Method #3
INSERT sometable (a, b, c)
 EXEC('SELECT 5, ''New York'', 234
  SELECT 6, ''London'', 923
  SELECT 7, ''Paris'', 1024
  SELECT 8, ''Munich'', 1980')
GO

-- Method #4
INSERT sometable (a, b, c)
 SELECT 9, 'New York', 334 UNION
 SELECT 10, 'London', 823 UNION
 SELECT 11, 'Paris', 1124 UNION
 SELECT 12, 'Munich', 2080
GO

-- Method #5 - More options in SQL Server 2008, by using the VALUES() constructor
INSERT sometable (a, b, c)
VALUES	(13, 'New York', 334),
		(14, 'London', 823),
		(15, 'Paris', 1124),
		(16, 'Munich', 2080))
GO

-- Method #6 - Yes you can also use SQL statements at column level inside the VALUES constructor
INSERT sometable (a, b, c)
VALUES	(18, 'New York', 334),
		(19, 'London', 823),
		((SELECT MAX(a)+1 FROM sometable), (SELECT b FROM sometable WHERE a=15), SELECT SUM(c) FROM sometable),
		(20, 'Munich', 2080))
GO

-- Now check the resultset
SELECT * FROM sometable

-- Final Cleanup
DROP TABLE sometable

More on VALUES constructor on MS BOL: http://technet.microsoft.com/en-us/library/dd776382.aspx

Categories: SQL Tips Tags:

Include other columns while doing aggregates and GROUP BY – MSDN TSQL forum

September 22, 2010 Leave a comment

 
–> Question:

I need maximum mark scored each students and min mark scored each students.

I have done this way…

select ID, max(mark), min(mark) 
from table1 
group by ID

But i need more field as Class…. I don’t want include class field in group by clause
 

–> My Answer:

Try joining the query with the table, like:

select T1.*, T2.ID, T2.max_mark, T2.min_mark
from table1 T1
join (select ID,max(mark) as max_mark,min(mark) as min_mark
	from table1 
	group by ID
) AS T2
on T1.ID=T2.ID

 

–> shaahs Answer:

In this situation you can use the window functions:

select ID, Class, 
	max(mark) over(partition by ID), 
	min(mark) over(Partition by ID) 
from table1

… in this cause you don’t want to include the fields in group by clause.

Categories: SQL Server Questions, SQL Tips Tags: ,