[T-SQL] Self Join – Inner Join and Outer Join

USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO



We will now use inner join to find the employees and their managers’ details.

-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO



From the result set, we can see that all the employees who have a manager are visible. However we are unable to find out the top manager of the company as he is not visible in our resultset. The reason for the same is that due to inner join, his name is filtered out. Inner join does not bring any result which does not have manager id. Let us convert Inner Join to Outer Join and then see the resultset.

-- Outer Join
SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO



Once we convert Inner Join to Outer Join, we can see the Top Manager as well. Here we have seen how Self Join can behave as an inner join as well as an outer join.

[T-SQL] Datetime Function SWITCHOFFSET Example

I was recently asked if I know how SWITCHOFFSET works. This feature only works in SQL Server 2008.

Here is quick definition of the same from BOL: Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

What essentially it does is that changes the current offset of the time to any other offset which we defined. Let us see the example of the same.

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+04:00') 'GetCurrentOffSet+4';

The example above clearly shows that Switch offset does not only change the offset; it also alters the current time. If you look at GetcurrentOffset, it is +5.30; but, you will notice that GetCurrentOffset-2 does not only change the offset to -2. It also changes the time with the appropriate time at Timezone -2.

I suggest that you run the code in SSMS Query Window and observe the code behavior.

[T-SQL] Dual - Oracle

What is the Equivalent of DUAL in SQL Server to get current datetime?
Oracle:

select sysdate from dual

SQL Server:

SELECT GETDATE()

What is the equivalent of DUAL in SQL Server?
None. There is no need of Dual table in SQL Server at all.

Oracle:

select ‘something’ from dual

SQL Server:

SELECT ‘something’

I have to have DUAL table in SQL Server, what is the workaround?
If you have transferred your code from Oracle and you do not want to remove DUAL yet, you can create a DUAL table yourself in the SQL Server and use it.

Here is a quick script to do the said procedure.

CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO

After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server.

[T-SQL] ERROR: 8170 Insufficient result space to convert uniqueidentifier value to char

I just came across very simple error and the solution was even simpler. While concatenating NEWID to another varchar string, I had to CONVERT/CAST it to VARCHAR and I accidentally put length of VARCHAR to 10 instead of 36. It displayed following error.

Msg 8170, Level 16, State 2, Line 1
Insufficient result space to convert uniqueidentifier value to char.

The code which I had ran earlier was as following.

SELECT 'GeneratedID:'+CAST(NEWID() AS VARCHAR(10)) AS NEW_ID


Solution:

The solution of above error is extremely simple. I just increased the length from VARCHAR(10) to VARCHAR(36) and it worked fine.

SELECT 'GeneratedID:'+CAST(NEWID() AS VARCHAR(36)) AS NEW_ID

[T-SQL] Computed Column – PERSISTED and Storage

Many people think that as soon as computed columns are created the column is materialized and the data is now stored in the column just like usual. In fact this is not true. If computed column is not marked as persisted, it is not created when the column is created, in fact it is still computed at run time. Once you mark column as persisted, it is computed right away and stored in the data table.

Let us see quickly following example of the creating computed column.

USE tempdb
GO
-- Create Table
CREATE TABLE UDFEffect (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100))
GO
-- Insert One Hundred Thousand Records
INSERT INTO UDFEffect (ID,FirstName,LastName)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Add Computed Column
ALTER TABLE dbo.UDFEffect ADD
FullName AS (FirstName+' '+LastName)
GO
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Add Computed Column PERSISTED
ALTER TABLE dbo.UDFEffect ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Clean up Database
DROP TABLE UDFEffect
GO

I have used the system stored procedure sp_spaceused to find out the space used in the query.

From the resultset it is very clear that when I created the computed column, it did not take any additional space in the database. However, when I created computed column marked as PERSISTED it indeed took more space in the data table and the size of the table is grown larger.



I hope this clear it up. In future article I will write performance and efficiency of the computed columns.

[T-SQL] Advanced Server Configuration

“How I check all the advanced configuration of the SQL Server?”

EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;

[T-SQL] Identity Columns

Allowing inserts to identity columns:

If you are inserting data from some other source to a table with an identity column and you need to ensure you retain the indentity values, you can temporarily allow inserts to the indentity column. Without doing so explicitly you will receive an error if you attempt to insert a value into the indentity column. For example, if I have a table named MYTABLE and I want to allow inserts into it's identity column, I can execute the following:


set identity_insert mytable on

Once you execute the command you will be able to insert values into the table's identity column. This will stay in effect in until you turn it off by executing the following:


set identity_insert mytable off

Be aware that at any time, only a single table in a session can have the identity_insert set to on. If you attempt to enable this for a table and another table already has this enabled, you will receive an error and will not be able to do so until you first turn this off for the other table. Also, if the value used for the indentity is larger than the current identity value then the new value will be used for the identity seed for the column.



Reseeding the identity value:

You can reseed the indentity value, that is, to have the identity values reset or start at a new predefined value by using DBCC CHECKIDENT. For example, if I have a table named MYTABLE and I want to reseed the indentity column to 30 I would execute the following:


dbcc checkident (mytable, reseed, 30)

If you wanted to reseed the table to start with an identity of 1 with the next insert then you would reseed the table's identity to 0. The identity seed is what the value is currently at, meaning that the next value will increment the seed and use that. However, one thing to keep in mind is that if you set the identity seed below values that you currently have in the table, that you will violate the indentity column's uniqueness constraint as soon as the values start to overlap. The identity value will not just “skip” values that already exist in the table.