SQL SERVER: Calling Code from another Database

To call object from another database on the same server we should use 3 part syntax


Database.Schema.Object

e.g.


Use tempdb
GO

SELECT Name,ModifiedDate 
FROM [AdventureWorks2008].[Production].[ProductCategory]

Reference: DP (http://dptechnicalblog.blogspot.com)

SQL Server: Renaming Database Column, Table Name

Often we get requirement to rename database column or table.
Here the syntax to do it using T-SQL scripts.

Script to rename any column

sp_RENAME 'tableName.OldColumnName' , 'NewColumnName', 'Column'
GO

Script to rename any db object (table, sp etc)

sp_RENAME 'objectOldName', 'objectNewName'
GO

Reference: DP (http://dptechnicalblog.blogspot.com/)

SQL Server: Difference between LEN() and DATALENGTH()

Sometimes we look for simple information like difference between  SQL Server built in functions LEN() and DATALENGTH().

DATALENGTH() returns length of string in bytes, including trailing spaces. LEN() returns length of  string in characters excluding trailing spaces.

e.g.

Executing the below script returns the result as shown in below screenshot.

SELECTLEN('sqlserver'),LEN('sqlserver '),DATALENGTH('sqlserver'),DATALENGTH('sqlserver '),LEN(N'sqlserver'),LEN(N'sqlserver '),DATALENGTH(N'sqlserver'),DATALENGTH(N'sqlserver ')
-------------------------------------------------------------------------











Reference: DP (http://dptechnicalblog.blogspot.com/)

SQL Server: Finding Duplicate Records

Recently I came across a situation where we need to findout the duplicate records based on certain criteria.

Problem: To findout duplicate users based on firstname and lastname
Solution:
Asssume tableA has below definition and already having data.
tableA(UserID,FirstName,LastName,LoginID,Password,Status)

SELECT a.UserID,a.FirstName,a.LastName,a.LoginID,a.Password
FROM tableA a
WHERE (
SELECT COUNT(b.FirstName+''+b.LastName) FROM tableA b
 WHERE a.FirstName=b.FirstName AND a.LastName=b.LastName AND b.Status <> 'delete'
) > 1
AND a.Status <> 'delete'
Order By FirstName,LastName

Please add your valueble comments and if any other alternative ways to get the same result.

Reference: DP (http://dptechnicalblog.blogspot.com/ )

SQLSERVER Foreign Key Constraint Resolution

Recently I came across a scenario in db cleanup where we need to drop and restore the foreign keys in data migration. Some times we face an issue, foreign key constraint error during both dropping and adding foreign key relationship.
Eg:
tableA(pkEmpID, Name)
tableB(pkDeptID,fkEmpID,Name) where fkEmpID is foreign key for tableA and the foreign key constraint is fkEmpID_tableA_tableB.

To cleanup the data in tableA without deleting the data in tableB, use below syntax and this step is simple.

ALTER TABLE tableA DROP Constraint fkEmpID_tableA_tableB

After this, do db cleanup and complete the data migration.
Again while restoring back the foreign key relationship, use below syntax

ALTER TABLE tableA ADD Constraint fkEmpID_tableA_tableB FOREIGN KEY (pkEmpID) REFERENCES tableB (fkEmpID)

Sometimes the above query fails due to the difference in data between tableA and tableB w.r.t forienkey i.e. fkEmpID. To avoid this issue use below approach

DELETE FROM tableB WHERE fkEmpID NOT IN (SELECT pkEmpID from tableA)
The above query deletes the unmatching EmpID data from tableB, so restoring foreign key will be possible.

Hope this approach helps SQL guys in dealing with foreign key constraint errors.
Please post if you found any other solution or having any suggestions.

Reference
DP (http://dptechnicalblog.blogspot.com/)

Difference between SQLManagementStudio_x64_ENU.exe and SQLManagementStudio_x86_ENU.exe

Recently I came across installation procedure for SQL Server 2008 Management Studio.
I found that there are 2 download exe files available and confused to choose between which one will be suitable for my PC's hardware configuration.

After a little anlaysis found the difference between the two exe files and here is the difference.
First one i.e. SQLManagementStudio_x64_ENU.exe  is for 64 bit and the second  one i.e. SQLManagementStudio_x86_ENU.exe is for 32 bit of Windows version. If you confuse between the 2, use 2nd one as it will work for both 32 bit and 64 bit of Windows which is not applicable for the 1st one.

Regards,
DP.