CALSQL

Handling Relational Data

sekhar@calsql.com (chandra sekhar pathivada) — Thu, 29 Dec 2011 10:52:43 PST

Handling Relational Data In Relational Databases
Managing relational databases is sometimes tedious, especially when we have to upload the data into the relational schemas or delete one particular row from a table as since it’s relational, the referential integrity wont allows the parent table to delete unless the related rows delete from the foreign keys. The chain branches of parent tables are simple or complex as one node might have multiple branches.
The below procedure will give the tree structure, so the output of this procedure you can use to delete or insert or update the keys (especially in case of data masking). All you have to do dump the output of this stored procedure in to temp table and generate the generic queries or do it manually, if it’s simple.

create  procedure [dbo].[pr_schema] as 
set nocount on 

SELECT SCHEMA_NAME(SOF.SCHEMA_ID) AS FSC_NAME,T1.FKEYID AS FK,SCHEMA_NAME(SOR.SCHEMA_ID) AS RSC_NAME,T1.RKEYID AS PK 
into #sysref FROM sysforeignkeys T1 
left outer JOIN sys.objects SOF ON SOF.OBJECT_ID = T1.FKEYID 
left outer JOIN sys.objects SOR ON SOR.OBJECT_ID = T1.RKEYID 
-- below 3 lines is to avoid the problems with self reference 
LEFT OUTER JOIN SYSREFERENCES T2 
ON OBJECT_NAME(T1.FKEYID)+OBJECT_NAME(T1.RKEYID) = OBJECT_NAME(T2.RKEYID)+OBJECT_NAME(T2.FKEYID) 
WHERE T2.FKEYID IS NULL 
--******************************************************* 
-- Get the Max No.Of Joins foreign keys in a Chain 
declare @N VARCHAR(9); 
WITH CTE(FKEYID,LEVEL) 
    AS 
     (SELECT FKEYID,0 FROM sysforeignkeys 
    UNION ALL 
    select T1.PK ,LEVEL+1 FROM #sysref T1 JOIN CTE T2 ON T1.FK = T2.FKEYID ) 
-- Statement that executes the CTE 
SELECT @N = Max(level)  FROM CTE 
PRINT @N 
--******************************************************* 
Declare @V1 varchar(max) 
set @V1 = '' 
  
Declare @V2 varchar(max) 
set @V2 = '' 
declare @select varchar(max) 
set @select = '' 
Declare @OrderBy varchar(2) 
set  @OrderBy = @N+1 
WHILE NOT @N = 0 
BEGIN 
             set @V1 =  @V1 + ' right outer join #sysref A' +cast(@N as varchar(4)) + ' on a'+ @V2+'.pk =A'+cast(@N as varchar(4))+'.fk' 
             set @select =  @select+    'a'+ @N+'.FSC_NAME+''.''+object_name('+'a'+ @N+'.fk),' 
set @V2 = @N 
SET @N = @N -1 
CONTINUE 
END 
SET @select=  @select +  'A1.RSC_NAME+''.''+object_name(A1.PK)' 
exec('SELECT ' + @select +'  from #sysref a ' +@V1 + ' order by '+ @OrderBy ) 
print ('SELECT ' + @select +'  from #sysref a ' +@V1 + ' order by '+ @OrderBy )

sql server dynamic stored procedures

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 04 Jul 2011 17:08:07 PDT

How to create dynamic parameters in SQL Server stored procedure

Below script is an example for how to create a procedure to pass dynamic parameters. This is not a new trick and many developers are using using the method. But probably not every one knows that it might leads to SQL Injection attacks.But still, one good thing about using .NET is that if you execute any sql statement from the front end then it will automatically calls the API function (sp_executesql)that rewrites the SQL statements to a parameterized query which is safe. so make sure the front-end technology which you are using has this capability to call the APIS while passing sql statements to backend.

-- execution script: Execute dbo.pr_customers 'te','tx',1
CREATE PROCEDURE dbo.Pr_customers(@firstname VARCHAR(90)=NULL,
                                  @lastname VARCHAR(90)=NULL,
                                  @age       INT=NULL,
                                  @phone     INT=NULL)
AS
BEGIN
      -- declare the variables to build the string dynamically
      DECLARE @Where VARCHAR(MAX),
              @SQL   VARCHAR(MAX)

      SET @Where = ' WHERE 1=1 '

      IF ( @firstname IS NOT NULL )
        SET @Where = @Where + ' AND firstname = ''' + @firstname + ''''

      -- make sure that you append four quotes(') for charecter parameters
      IF ( @lastname IS NOT NULL )
        SET @Where = @Where + ' AND lastname = ''' + @lastname + ''''

      IF ( @age IS NOT NULL )
        SET @Where = @Where + ' AND city = ' + CONVERT(VARCHAR, @age)

      -- for integer parameters you no need to append quotes(')
      IF ( @phone IS NOT NULL )
        SET @Where = @Where + ' AND country = ' + CONVERT(VARCHAR, @phone)

      SET @SQL = 'select * from customers ' + @Where

      EXEC (@SQL)
END

sql server identify non unique tables and create non clustered indexes with identity property

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 28 Mar 2011 18:17:23 PDT

create unique keys with non-clustered index on all the tables.

one of the audit tool which we are using requires a unique column must be present on the table to audit. Developed the below script to create a identity column with non-clustered index on all the tables , if the table has an identity column without any unique index then the script-2 will do this.

1)modify the tables to have a identity key columns on all the tables with non-clustered indexes
2)script:2- if the table has a identity key already exist but without any unique index then it will create the unique index on the identity key columns.

select 'alter table ['+SCHEMA_NAME(O.SCHEMA_ID)+'].'+o.name +' add IDENTITY_FOR_MASK int identity(1,1) constraint NCL_X_IDENTITY_FOR_MASK unique nonclustered'

from sys.objects o join sys.indexes i on o.object_id=i.object_id

where o.type = 'u' AND objectproperty(o.object_id,'TableHasIdentity') =0

group by o.name,O.SCHEMA_ID

having max(cast(I.is_unique as int))

select 'CREATE UNIQUE INDEX NCL_X_IDENTITY_FOR_MASK on ['+SCHEMA_NAME(O.SCHEMA_ID)+'].'+O.NAME+'('+IC.NAME+')'

from sys.objects o join sys.indexes i on o.object_id=i.object_id join sys.identity_columns IC on o.object_id = IC.object_id

where o.type = 'u' AND objectproperty(o.object_id,'TableHasIdentity') =1

group by o.name,IC.NAME,O.SCHEMA_ID

having max(cast(I.is_unique as int))

sql server broken ownership chain

sekhar@calsql.com (chandra sekhar pathivada) — Sat, 25 Dec 2010 17:03:59 PST

Broken ownership chain occurs when a user other than dbo trying to access the object from other schema(sc_b) which is authorized by another user from his own schema(sc_a)

In the below diagram, the user has the permission to execute a procedure on his own schema SC_A, but the procedure is calling a table which is in another schema where he doen’t have any permission and in fact the DBA don’t want to give the permission to any user to access the table sc_b.mytable directly except through stored procedures. If the user U1 executes the procedure pr_myproc then he will receive the below error
The SELECT permission was denied on the object 'mytable', database 'my_db', schema 's2'.

To avoid the broken ownership chain , we can use the signatures which are available from sql server 2005 to create a bridge between the calling object and objects in callstack.

· To create a certified_user , you need to have a master key , certificate , certified user

· create a signature on a caller object (sc_a.pr_myproc) procedure by a certificate

· grant the select permission on the objects in callstack (underlying table : sc_b.mytable) to the signed user(user created by certificate)

· and of course , you have to create a database master key to protect the certificates.

Lets simulate the broken ownership chain through the below script

--- Simulate broken ownership

create database my_db
GO

use my_db

create login l1 with password = 'm7yu@ik4#'
create login l2 with password = 'm7yu@ik4#'

create user u1 for login l1
create user u2 for login l2

----------------------------------------------------------------------
create schema s1 authorization u1
create schema s2 authorization u2

----------------------------------------------------------------------

create table s2.mytable(iid int)
insert into s2.mytable select 1

----------------------------------------------------------------------
GO

CREATE proc pr_myproc
as
select * from s2.mytable
GO

----------------------------------------------------------------------

grant exec on pr_myproc to u1
grant exec on pr_myproc to u2

execute as user = 'u2'
--- user-2 can access the table because the table belongs to u2 schema
exec pr_myproc
-- REVERT TO SYS LOGIN
revert

execute as user = 'u1'
--- now the user u1 cant access the underlying table through the procedure due to broken ownership chain

exec pr_myproc

--- user cant access the table directly as well
select * from s2.mytable

go
revert
-- but we want to allow the user to access only through procedure , without giving direct permission
---- Create the objects required to bridge the connections
-------------------------------------------------------------------------------------------------------
-- ** bridge the broken ownership chain using the certificate with signature on the object ** --
-------------------------------------------------------------------------------------------------------
-- create a master key , certificate , certified user

create master key encryption by password = 'test'

open master key decryption by password = 'test'

backup master key to file = 'e:\mykey.key' encryption by password = 'passbkp'

-- create a certificate

CREATE CERTIFICATE mycertificate WITH SUBJECT = 'Test Certificate'

backup CERTIFICATE mycertificate to file = 'e:\mycert.cert'

-- create a certified user from the certificate

CREATE USER CERT_USER FROM certificate mycertificate

---------------------------------------------------------------------------

------ Bridge beween calling object and the objects calling in stored procedure callstack

--• create a signature on a caller object (sc_a.pr_myproc) procedure by a certificate

ADD SIGNATURE TO pr_myproc BY CERTIFICATE mycertificate

--•grant the select permission on the objects in callstack (underlying table : sc_b.mytable)

-- to the signed user(user created by certificate)

grant select on s2.mytable to CERT_USER

-- test whether the user can execute the procedure with out any error

execute as user = 'u1'

exec pr_myproc

-- now when the user is trying to access the table , directly then he cant.

select * from s2.mytable

revert

SQL Server Transaction log

sekhar@calsql.com (chandra sekhar pathivada) — Sun, 05 Dec 2010 08:23:20 PST

Here I am describing the t-log architecture at very high level related to transactions only.
for complete details refer the following link : http://msdn.microsoft.com/en-us/library/ms180892.aspx

maintaining ACID through WAL(write ahead logging)

during any update , sql server retrieves the page contains the record to buffer from disk and it will update the page in buffer only and write the same transaction in t-log in buffer.
when the user commit the transaction then it has to write the t-log immediately to the disk to make the transaction to commit before flusing the data page(wal)
during every checkpoint it will write the data pages back to the disk , so in case of any power failure before the checkpoint , it will still recover the modified records from the transaction log which is physically there in the disk during recovery

Transaction log is in a file only, not organized as pages as in data file
Transaction log writes all the transactions (mostly modifications) sequentially.
every transaction in the transaction log will contain a LSN(log sequence number) which is auto incremented value.
when you create a transaction explicitly( begin tran) , sql server writes the tranID + “before image” with lsn in the transaction log and tranID with “after image” with lsn
it also records the “begin tran” and “commit/rollback tran” for the same tranID with next lsn
irrespective of whether the transactions are committed or not , it writes the log and modified data pages to the disk during every checkpoint
for the committed transactions – it will apply the after image
for the uncommitted transactions – it will put a lock on it so that no modification will occur
for the rollback transactions – it will apply the before image
If there is any power failure before the checkpoint , which might have committed or uncommitted transactions recorded in t-log : while recovering , it will start writing the committed transactions permanently to the disk and the transactions which doesn’t have any corresponding “commit tran” will be rolled back.
SQL Server wont read the transaction log except during a recovery.
transaction log plays an important role during recovery and to maintain the state of the transaction as per ACID.

Keep the T-Log on a separate drive : As T-Log writes the data sequentially and always writes each and every transaction in transaction log , it better to place it in separate drive to minimize the disk contention to improve the performance.

Database Design Approaches

sekhar@calsql.com (chandra sekhar pathivada) — Sat, 30 Oct 2010 23:20:33 PDT

Below steps are generally required at the very high level to complete any database design

gather the requirement and document it in FRS , mostly you will get this from systems analyst or you may have to assist the systems analyst to prepare the document.
create a conceptual model using use cases and dataflow diagram.
create a logical model (entity,attribute,relation ship) from the conceptual model using any modeling software(eg. Erwin)
apply the normalization while trading off between (updates and reports)
create a physical model ( choose the RDBMS software, data types, indexes)
test the model with the requirement and prototype

Do you need to design the database from scratch ?

No, not always. below are the few scenarios and the approaches where you might not need to start from the scratch.

check whether any system exists.

for most of the applications there might be some existing legacy systems in place , if there is any then get the schema of it and reverse engineer it.
if the new system has any new enhancements then modify the design , but before modifying the design , document your changes using an mapping table , so that when you move the data from legacy system you can modify the way the data should flow from the old to new as per the changes mentioned in the mapping table.

is there any prototype exists for your application

if your project following a prototype model then you can understand the flow of the application through the prototype and can start designing the database , but make sure about the business rules which you need to apply at the backend.

conclusion : before start designing the model, check whether any system exists and the prototype which helps you to understand the application.

Its always recommended to create the FRS,conceptual,logical and physical design documents ,though something already exists as these documents helps in making further builds and its up to you whether you create these documents at the beginning, in parallel or at the end of your project based on the timelines and other factors.

test tables for poc

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 01 Nov 2010 09:39:47 PDT

-- below 2 tables will be used in all the examples to understand or create the proof of concepts

create table customer_t ( cid int primary key ,vname varchar(30));

create table orders_t( oid int primary key ,cid int foreign key
references customer_t(cid) ,oname varchar(30));

insert into customer_t select 1,'A';
insert into customer_t select 2,'B';
insert into customer_t select 3,'C';

insert into orders_t select 1,1,'O-1';
insert into orders_t select 2, NULL,'0-2';
insert into orders_t select 3,NULL,'0-3';

sql server mind map

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 18 Oct 2010 07:33:32 PDT

SQL Server Enhancements in Mind Map

Mind maps are very helpful to study, problem solving, decision making and imagine the subject .Helps us to recollect the things promptly as its organized based on the association with other objects.

Below flash depicts the SQL Server 2005 (black font) and 2008(red font) enhancements in mind map format.Click on the square boxes on top of the mind maps to resize, change the back ground color, key word search and browsing.

Click on any node on the mind map to explore the branches till the root level and to see the the description of the features hover the mouse on the nodes to view its description in a tool-tip.

Happy Learning !!.

sql server queries taking long time to execute in production

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 15 Nov 2010 07:19:36 PST

query execution in the production taking long time

one of the query in the production taking almost 40 minutes instead seconds where it used to, but interestingly the same query in the development server just took 5 seconds to execute. both the environments are same WRT user contention.

The quick steps to troubleshoot in this scenario is

compare the pre-execution plans (Ctrl + L) on both the production and development.

Note : pre-execution plans are helpful to look at the execution plan with out executing the query as executing query in production with out knowing about is not recommended in production.

Just look at both the plans on whether they are look same or not with out going in details
if both are not same then its due to the statistics are not same.

so just check when the statistics in production are updated using the below query

SELECT STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats
WHERE object_id = OBJECT_ID(Sales.Customer);

-- to check the statistics on indexes , instead sys.stats use sys.indexes

SELECT STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes
WHERE object_id = OBJECT_ID(Sales.Customer);

if these dates are too old then update the statistics using the below command

UPDATE STATISTICS Person.Address

check the performance of the query in production .

Note: even if the “auto create statistics” and “auto update statistics are on” , SQL Server wont create or update for each and every row modification . it will only do when it reaches certain threshold.

row by row insert in sql server

sekhar@calsql.com (chandra sekhar pathivada) — Sun, 22 May 2011 12:27:11 PDT

Row by row insert and update using cursors and catch the error rows in error table.

This is a sample script template to simulate row by row insert in SQL Server and redirect the error rows into an error table.

create table source(iid int);
create table dest(iid int primary key)
create table err(iid int ) 
insert into source select 1 union select 2 union select 3 union select 4;
insert into dest select 3;
select * from source;
select * from dest;
select * from err;
--------------------------------------------------------
-- using cursors
declare mycur cursor for select iid from source
declare @iid int
open mycur  
fetch next from mycur into @iid 
while @@fetch_status = 0 
begin   
begin try   
insert into dest select @iid    
end try   
begin catch   
insert into err select @iid   
end catch     
fetch next from mycur into @iid  
continue end
close mycur 
deallocate mycur

number of rows in foreign key

sekhar@calsql.com (chandra sekhar pathivada) — Wed, 28 Oct 2009 21:29:02 PDT

script to list the number of foreign key instances existed in Foreign key table referenced to their corresponding Primary Keys. This will helps to test the data during migration.

SET NOCOUNT ON

SELECT 'CREATE TABLE #REF(PK_TBL VARCHAR(900),PK_COLS VARCHAR(900),FK_TBL VARCHAR(900),FK_COLS VARCHAR(900),FK_VAL VARCHAR(900) ,CNT INT)';

with CTE_REFERENCES(FK_COLS,FK_TBL,PK_COLS,PK_TBL)
AS
(
select distinct 
replace(replace(replace(
           (SELECT Y.name 
           from sys.sysforeignkeys X
   left outer join syscolumns  Y on X.fkeyid = Y.id and X.fkey = Y.colid
           WHERE X.constid= A.constid
           FOR xml PATH (''))
     ,'<name>','')
     ,'</name>',',')  + '$' ,',$','')as FK_COLS,

SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) as FK_TBL,

replace(replace(replace(
           (SELECT N.name 
           from sys.sysforeignkeys L
   left outer join syscolumns  N on L.Rkeyid = N.id and L.Rkey = N.colid
           WHERE L.constid= A.constid
           FOR xml PATH (''))
     ,'<name>','')
     ,'</name>',',')  + '$' ,',$','') as PK_COLS
,
+ SCHEMA_NAME(SOF_R.SCHEMA_ID) +'.'+ object_name(rkeyid) as PK_TBL

from sys.sysforeignkeys A
left outer join syscolumns  b on a.fkeyid = b.id and a.fkey = b.colid
left outer join syscolumns  c on a.rkeyid = c.id and a.rkey = c.colid
left outer JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID 
left outer JOIN sys.objects SOF_R ON SOF_R.OBJECT_ID = A.RKEYID 
)
SELECT 
'INSERT INTO #REF SELECT  '
+ ''''+ PK_TBL  + ''','   
+ ''''+  PK_COLS + ''','   
+ ''''+ FK_TBL + ''','  
 + ''''+FK_COLS +''','  +
CASE 
WHEN CHARINDEX(',',FK_COLS ) = 0 THEN FK_COLS
ELSE
REPLACE(
  'CAST(' + left(FK_COLS,CHARINDEX(',',FK_COLS )-1) +' AS VARCHAR(MAX)),CAST(' + 
  SUBSTRING(FK_COLS,CHARINDEX(',',FK_COLS )+1,LEN(FK_COLS)) +' AS VARCHAR(MAX))' 
  ,',','+'',''+'
  )
END
+
',COUNT(*)'+ ' FROM ' + FK_TBL + ' WHERE CHECKSUM(' + FK_COLS +
') IN ( SELECT CHECKSUM(' + PK_COLS +') FROM '+ PK_TBL +
 ') GROUP BY ' +FK_COLS 
FROM CTE_REFERENCES

PRINT 'SELECT PK_TBL AS [-SUMMARY_REPORT- PK_TBL ],PK_COLS ,FK_TBL ,FK_COLS ,COUNT(CNT)FROM #REF
GROUP BY PK_TBL ,PK_COLS ,FK_TBL ,FK_COLS ;'

PRINT 'SELECT PK_TBL AS [-DETAILED_REPORT- PK_TBL ],PK_COLS ,FK_TBL ,FK_COLS ,CNT FROM #REF;'

SET NOCOUNT OFF

What’s up?

sekhar@calsql.com (chandra sekhar pathivada) — Sun, 10 Oct 2010 16:41:45 PDT

Check out my recent article in SQL Server magazine June edition to display the tables in hierarchical manner based on the relations.http://www.sqlmag.com/Article/ArticleID/101931/sql_server_101931.html.This article is specifically to produce the list of all the tables across the schemas hierarchically based on the relationships, so what is so special about this ?

Below are the top 3 advantages:.
Database migration
SSIS Development
Data Masking

Database Migration

Migrating data from legacy database to SQL Server, this script will give you 2 sets where the set A contains the list of the independent tables with out any relation where you can migrate the data at a single step.
Set B contains the list of the tables in hierarchy .
check the below link for migrating the data using this stored procedure.
http://www.calsql.com/2009/09/synchronize-data-between-databases-in.html

SSIS Development
Run this procedure and design your package based on the sequence from the output.
Validate your schema

Through this you can validate your database design as it produce the table list which are in conflicts due to self referencing (one-many & many-one)
Data Masking

Masking data can be done through scrubbing the sensitive fields (SSN, credit card number) , but scrubbing the primary field requires populating the relational foreign key values at the same time.
Check out this article about how to scrub the data in development environment to hide the sensitive information using one of the statements inside this procedure
http://www.calsql.com/2009/09/ms-sql-server-data-masking.html

sql server cluster on virtual pc

sekhar@calsql.com (chandra sekhar pathivada) — Tue, 13 Oct 2009 11:31:23 PDT

SQL Server Cluster on Virtual PC

install and configuring cluster on virtual server is simple , but the same is not easily possible in virtual pc.
today i have installed the cluster on virtual pc and going to publish the step-by-step installation.

watch this space ....

ms sql server data masking

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 12 Oct 2009 18:43:34 PDT

SQL Server Data Masking :

Download code
DataMasking.sql

Masking data can be done through updating the confidential information in the database like SSN , Password,Username etc with some other values.

To mask the entire data quickly , its good to create table with list of the tables and columns which you need mask and run the generic statements to generate the update scripts on the table.

But updating the primary key value is diffcult task as we need to update the foreign key references as well.

Below procedure will update the primary key and all the related foreign keys.

Note: this procedure will not mask the table references where the PK is identity

Mostly we don't need to mask the identity values as all are auto generated values.

Steps involved in the below procedure :

Add an identity column to the primary key table
check the primary key datatype if its charecter datatype then it will append 'MASK' to the value its going to update
drop all the foreign keys
update all the foreign keys with the primary key table newly added identity columns value while referring the primary key value in the primary key table
update the primary key value with the newly added identity column
adds all the foreign keys which are dropped before in step:3
drop the newly added identity column in the primary key table which is added in step :1

--sql server data masking , replace sensitive data with some dummy data
GO
--PR_UPDATE_REFERENCES 'sales.customer','customerid'
GO
--
CREATE procedure PR_UPDATE_REFERENCES(@TBL_NAME VARCHAR(256),@PK_COL_NAME VARCHAR(256),@IDENTITY_COL VARCHAR(256)='IID_TEMP',@MASK_WORD VARCHAR(256)='MASK')
AS
SET NOCOUNT ON
-- Adding the Identity Column

select 'alter table '+ @TBL_NAME +  ' add ' + @IDENTITY_COL +' INT IDENTITY(1,1)'
select 'GO'

-- If the primary key is numeric data type then the mask word will be '' to avoid the casting errors
select @MASK_WORD = '' from sys.columns a join sys.types b on a.system_type_id = b.system_type_id
where object_id = object_id(@TBL_NAME)
and a.name = @PK_COL_NAME
AND b.name in ('tinyint','smallint','int','numeric')

-- updating all the foreighn keys 1st with identity column + 'MASK'
-- will update the Primary key at the end of the script
-- !! update through join with new added identity column

select distinct 
-- Below part will produce the script to drop  All Constraints 
'ALTER TABLE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' DROP CONSTRAINT ' + object_name(constid)
from
sys.sysforeignkeys A 
 join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid 
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid 
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID 
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME  and C.NAME = @PK_COL_NAME
---------------------------------------------------------------------------------------------------------
-- Below statement will update all the foreing keys with the values in the updated primary key
select + CHAR(13) + 'UPDATE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' SET ' + B.name

+ ' = CAST('+object_name(rkeyid) 
+
'.'+ @IDENTITY_COL + ' AS VARCHAR(9))' +' + '''+ @MASK_WORD +'''' +' FROM ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid)+' ' + 
'JOIN '
+ SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) + ' '+ 
+ 'ON ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) +'.'
+C.NAME +' = '
+SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) + '.'+ 
B.NAME
from
sys.sysforeignkeys A 
 join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid 
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid 
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID 
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME  and C.NAME = @PK_COL_NAME

---------------------------------------------------------------------------------------------------------
-- Below statement will update all the foreing keys with the values in the updated primary key

SELECT 'update '+ @TBL_NAME + ' set ' + @PK_COL_NAME + ' = '+@IDENTITY_COL +'+'''+ @MASK_WORD+''''

------------------------------------------------------------------------------------------------
select + CHAR(13) + 
-- Below statement will produce the script to Create All Constraints which are Scripted to Dropped Before
'ALTER TABLE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' ADD CONSTRAINT ' + object_name(constid)
+' FOREIGN KEY (' + b.name
+ ') REFERENCES '  + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid)
+ '(' + c.name + ')'
from
sys.sysforeignkeys A 
 join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid 
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid 
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID 
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME  and C.NAME = @PK_COL_NAME
------------------------------------------------------------------------------------------------
-- Dropping the Identity Column
select 'GO'

select 'alter table '+ @TBL_NAME +  ' drop COLUMN ' + @IDENTITY_COL

sql server merge data

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 12 Oct 2009 11:35:02 PDT

merge data in one table from another

COPY THE CODE FROM BELOW TEXT BOX

sql server database design interview questions

sekhar@calsql.com (chandra sekhar pathivada) — Tue, 05 Oct 2010 21:43:38 PDT

Last updated: 10/5/2010 9:19 PM

category

topic

links

Database design interview question

What is your approach on designing a Database?

#DB design approaches

Gather the requirements à create a conceptual model (use case) àCreate a logical model (ER Modeling) from this conceptual model àimplement the physical model (choose RDBMS)(data types, constraints, indexes etc...) from the logical modelà create a prototype of your application and test the model.

Database design interview question

What are the advantages and disadvantages of Normalization

#is my database normalized

Advantages: reduce the redundancy(duplicate) ,avoid null values, avoid update anomalies(refer update anomalies)

Disadvantages: too many joins required to produce reports.

Database design interview question

When will you go for Normalization or De-normalization

#Tradeoff matrix

It depends. A tradeoff between no. of updates vs. reports will guide till what level the database should be normalized or de-normalized.

Database design interview question

What is your approach on normalizing the database to improve the performance of the database

Refer above questions answer.

Database design interview question

Have you experienced any problems or failures in database design?

· Assigning too large data types, thinking that the length of the data would grow in future. E.g.:. Customer_first_name varchar (90) – in general this will not be 90, but if the designer doesn’t have any idea on the realistic values then this leads to occupying more pages for small data.

· Not doing any normalization or improper normalization: when designing the database focusing on meeting the requirements, sometimes the designer might ignore to normalize the database and especially doing the tradeoff between No. of Insert/Update/Deletes vs. reports.
Tip: validate the designing through having a standard checklist helps to make sure that the design is up to the mark.

· Don’t keep the audit data in the same table, look at the below table where the table has the audit data itself where you can’t maintain any constraints (fk, unique). Requires maintaining the integrity at the coding level where it leads to complex SQL statements instead of simple queries.

Name	Age	Status	Date
Robert	30	Updated	01-jan-1999 5 AM
Robert	31	Inserted	01-jan-1999 5 AM
Robert	31	Deleted	01-jan-1999 5 AM

o Resolution: create a view with a SQL statement to produce only the active data and use these views in your front-end application. Rest of all the tables will be act as audit tables.

· Don’t design the database by assumptions, all the assumptions must be clarified with all the stakeholders before finalizing the database design.

· Freeze the scope of the project before finalizing the database design

· I had seen many projects that the front-end developers are mostly complaining about the database that the design is not well and good and leads to the poor performance. This is in fact true and below is the most possible scenarios in most of the projects which makes the design not up to the mark for performance.

o Not following any trade-off matrix before making any decisions on changing the database design during the every build – This is the most common problem as most of the project requires some changes and the designer/developer has to be very careful before making any change on the schema, he has to consider the impact of the change WRT time, cost, scope and has to think about how to mitigate as well.

Database design interview question

What is the difference between Primary Key and Unique Key

#heap table

A unique key can also be a primary key and can involve in joins.

Differences are: when you create a primary key by default it will create a clustered index whereas for the unique key by default it will create non clustered index.

A table will contains only one primary key in a table, but can contains more than one unique key.

Primary key won’t allow any null value, but unique key allows only one null.

Database design interview question

Can a foreign key allow null value?

Yes.(also refer cardinality)

Database design interview question

Mention few best practices to design a database

· Select the right design tool to design the database, there are many tools available in the market and the most popular one is Erwin, but make sure that Erwin ( not sure about the current version) itself has some problems in it and you must know about these before using the Erwin.

§ One of the most challenging task when working with Erwin is during making any changes , Erwin generates the scripts which contains the drop and recreate statements which has to be analyzed first before executing directly on the development database which might make the database inconsistent if the script fails to execute at the middle.

§ Creates a non-clustered index by default on the primary key, so make sure there won’t be any heap tables created by Erwin.

§ When you modify the table in Erwin which has any comments defined then the script generated by Erwin will not contains any of these comments.

§ Finally make sure its 100% compatible with the version of the SQL Server you are using.

· Define the scope of your project and finalize the database design before moving to the development as any change in the schema leads to the major changes in the application development.

· Conform the logical design with the requirements and get the sign-off

· Tip: It’s possible to confirm the design of the database as per requirements through the prototype of the application.

· follow a proper naming convention

· try to define the data types as short as possible ( refer SQL Server Page 8 KB)

· Don’t use the text data type unless its highly required as querying the data inside a text data type sometime requires to build a full text catalog

Database design interview question

What is the difference between Primary Key and Candidate Key

Candidate key: A single column or set of columns that can uniquely identifies the row in a table. a candidate key can acts as a primary key and you can define only candidate key as a primary key.

Primary Key: A key or set of keys that can uniquely identifies the row in a table.

E.g.: sometimes when you declare a surrogate key (column with identity property) as a primary key then the actual business primary key will acts as candidate key.

Database design interview question

What is cardinality

It’s a property to specify the number of instances of an entity that can be existed in each side of the relationship. E.g.:. One-One or null, One-Many or null

Database design interview question

What is Functional Dependency?

It’s a special relationship among columns in a table .A set of attributes X in table functionally dependent on set of attributes Y on the same table where the values in attributes Y identifies the values X.

E.g.:. Employee Address, Employee phone functionally depends on Employee Name.

Database design interview question

What is Tuple

Row in a table

Database design interview question

What is a Schema?

Table, columns and the relations between the tables.

Note: the concept of schema in SQL Server 2005 & 2008 is different from the concept of schema in relational theory where the SQL Server 2005 & 2008 refers to the logical container of database objects.

Database design interview question

what is the difference between DBMS and RDBMS

# SQL Server and Codd Rules

Rather than differentiating between the DBMS and RDBMS in detail, the fact is all the leading database software available in the market are DBMS.

So what is DBMS: software to manage the database (collection of data) through insert/update/delete / retrieval of data through a systematic and structured way (SQL) and manages the hierarchial, network, relational, object oriented databases.

To define any DBMS as RDBMS it has to satisfy all the 12 rules defined by E.F Codd.

There is an assumption that if the system has any relational databases then it’s an RDBMS which is not really true. For e.g.: MS Access is not an RDBMS though the recent version maintains the relationships through primary and foreign keys.

Is SQL Server is DBMS or RDBMS

Yes, but not 100%. As per E.F Codd rule 0, to define any system as an RDBMS it has to satisfy all the 12 rules.

Most of the leading softwares in the market are violating the Rule-9: Logical Data Independence.

Database design interview question

can you create 2 tables with the same name

yes , in a different schemas

SQL server 2008 Database design interview question

Can you enforce the naming convention in SQL Server to have all the tables should follow as defined?

Yes, through policy based management.

Open SSMS àManagement àPolicy Management àFacets à right-click Table, and then click New Condition

à type Table starts with tbl_

* In the Facet box, confirm that Table is selected.

* In Expression area, in the Field box, select @Name, in the Operator box select LIKE, and in the Value type "tbl_%"

* Optionally, you can type a description of the condition, by clicking on the Description tab.

Database design interview question

what are the new data types in SQL Server 2005

Database design interview question

What are the new data types in SQL Server 2008

Database design interview question

what is sparse column and its pros and cons

As per BOL “columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values”

So, when the column value is NULL for any row in the table, the values require no storage, but when it has any value then it takes few more bytes than its actual size.

It’s better to go for this only when the column has lot many null values.

Refer BOL for the limitations and estimated space.

Database design interview question

How will you implement a tree structure (one employee reports to another who is an employee of the organization itself) in design?

What are the challenges in managing this type of tables?

refer the dev-tree struct-qries

TBL_EMPLOYEES
ID	Int ( primary key , identity 1,1)
emp_id	char(9) , unique key
emp_name	varchar(30)
rep_to	int (foreign key references emp_id
designation	varchar(30)

when you enter the data it looks like the below :

ID	emp_id	emp_name	rep_to	designation
1	A1	bill gates		chairman
2	A2	steve		CEO
3	B1	craig	1	vice president
4	B2	Jeff raikes	2	president

Challenges: populating data from the text files is bit challenging.

If you try to insert the new data and update the existing data from the below text file into this table then you need to write at least one cursor.

emp_id	emp_name	reports	designation
M1	tuchen		marketing manager
M2	Jim alchen	M1	co-president
I2	David Campbel	I1	infra-manager
I1	Paul Flessner	1	sr.vice president

Note : above data captured for example from www.directionsonmicrosoft.com/sample/DOMIS/orgchart/sample/outlineorgchart.html

www.directionsonmicrosoft.com/sample/DOMIS/orgchart/sample/orgchart.html

Database design interview question

what is the disadvantage of having a foreign key

There is no any disadvantage in particular and in fact it supports the referential integrity which is the major advantage.

But internally when there is a foreign key means there is a overhead on the SQL Server system :

Before delete a master record: the engine will look at the existence of the child record in foreign key table internally.

before insert or updating a primary key a child record : the engine will look into the master record in the primary key table

conclusion : so it’s always better to create a clustered index on the primary key and a non-clustered index on a foreign key

why there should be a clustered index on every primary key and non-clustered index on every foreign key

Refer above question.

give an example for the generalization and summarization

what is a surrogate key and how you will implement surrogate key in SQL Server

what are the advantages and disadvantage of having Identity Keys in SQL Server

basis on what you will define a identity key

which NF the below table “customers” is in

ID(unique)	Name	Address	telephone	age
1	Albert	123, cb street,new jersey, usa	001999999	50

which NF the below table “customers” is in

ID(unique)	Name	street	apt	city	country
1	Albert	cb street	123	new jersey	USA

what is normalization define 1-NF, 2-NF,3NF

what is a Heap Table and when will you make heap tables

what is collation and how will you define a collation at column level

give an example for 1-1 relationship

give an example for 1- many relationship

Database design interview question

Is your database is DBMS?

this is a very basic and a funny question J

Your database is not same as the database software (Oracle/SQL Server).

Its incorrect to say that my database is an DBMS

what is the difference between and when to choose what:

char	varchar
varchar	nvarchar
text	varchar(max)

complete create table syntax

performance tuning considerations

security considerations

Index – fill factor – pad index – pagesplit

Pivot with out aggregate

sekhar@calsql.com (chandra sekhar pathivada) — Sat, 15 Oct 2011 15:06:17 PDT

Pivot without Aggregating with consistent and inconsistent formats:

One of the most common tasks which I came across at least once in almost every project is to convert the structure of the table data into a PIVOT form. But, I don’t have anything to aggregate in the table; all I want is convert the structure as below.

Image-1

Before SQL Server 2005, in the earlier versions we used to perform this through using cursors and temp tables which takes a lot of I/O. Starting with SQL Server 2005 which provides a new operator called PIVOT which actually does this, but the main purpose of using PIVOT is to rotate the table data from vertical to horizontal as shown in above picture, and performs AGGREGATIONS on required columns which will be shown in the final data set.

As per the syntax PIVOT only works with an aggregate:

SELECT <non-pivoted column>,

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,

...

[last pivoted column] AS <column name>

FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

PIVOT

(

<aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

But I don’t want to do any aggregate operation as all I want is to transpose the data into a tabular format.

There are 2 approaches to do this based on the requirements:

1. If the column names are consistent across the data then follow the 1^st approach

2. If the column names are NOT consistent then the 2^nd approach will solve the purpose using a schema table.

Let’s create a table with the data as shown in the Image-1

Approach- 1 :- columns (NAME,ADDRESS,PHONE ,EMAIL) is consistent across all the sets

create table customers ( IID INT IDENTITY(1,1),col0 varchar(90),col1 varchar(90))

-- 1st set

insert into customers values ( 'NAME','A_NM')

insert into customers values ( 'ADDRESS','A_ADR')

insert into customers values ( 'PHONE','A_PH')

insert into customers values ( 'EMAIL','A_EMAIL')

-- 2nd set

insert into customers values ( 'NAME','B_BM')

insert into customers values ( 'ADDRESS','B_ADR')

insert into customers values ( 'PHONE','B_XYZ')

insert into customers values ( 'EMAIL','B_PH')

-- 3rd set

insert into customers values ( 'NAME','C_BM')

insert into customers values ( 'ADDRESS','C_ADR')

insert into customers values ( 'PHONE','C_XYZ')

insert into customers values ( 'EMAIL','C_EMAIL')

To transpose the data into a pivot report, I have used the partition function and included the results in a CTE and select the data in the CTE using PIVOT operator
script –A1

WITH CUST_CTE(iid, COL0,COL1,RID)

(

SELECT iid, COL0,COL1 , ROW_NUMBER() OVER (PARTITION BY (COL0)ORDER BY IID) AS RID FROM CUSTOMERS

)

SELECT NAME,ADDRESS,PHONE,EMAIL

FROM

(SELECT COL0,COL1,RID

FROM CUST_CTE)C

PIVOT

(

max(COL1)

FOR COL0 IN (NAME,[ADDRESS],PHONE,EMAIL)

) AS PivotTable;

Output:

Explanation: the key to the whole solution is to partition the sets in the data, the column RID has the partitioned values which actually works with the PIVOT. To make it more clear, look at the output of the CTE. You can see the data sets are partitioned with a column in RID. So when you execute the script A1, the RID makes the rows to unique.

WITH CUST_CTE(iid, COL0,COL1,RID)

(

SELECT iid, COL0,COL1 , ROW_NUMBER() OVER (PARTITION BY (COL0)ORDER BY IID) AS RID FROM CUSTOMERS

)

SELECT * FROM CUST_CTE ORDER BY 1,4

Approach- 2:- considering the column NAME acts as a primary key and present across all the sets , but rest of the columns (ADDRESS, PHONE, and EMAIL) are NOT consistent across all the sets.
Lets create the table with the sample set to simulate the problem.

TRUNCATE TABLE CUSTOMERS

-- 1st set

insert into customers values ( 'NAME','A_NM')

insert into customers values ( 'ADDRESS','A_ADR')

insert into customers values ( 'PHONE','A_PH')

insert into customers values ( 'EMAIL','A_EMAIL')

-- 2nd set

insert into customers values ( 'NAME','B_BM')

insert into customers values ( 'ADDRESS','B_ADR')

--insert into customers values ( 'PHONE','B_XYZ')

insert into customers values ( 'EMAIL','B_PH')

-- 3rd set

insert into customers values ( 'NAME','C_BM')

insert into customers values ( 'ADDRESS','C_ADR')

insert into customers values ( 'PHONE','C_XYZ')

insert into customers values ( 'EMAIL','C_EMAIL')

SELECT * FROM CUSTOMERS

When you execute the Script-A, you will see an incorrect results in the transposed data.
Script –A2

WITH CUST_CTE(iid, COL0,COL1,RID)

(

SELECT iid, COL0,COL1 , ROW_NUMBER() OVER (PARTITION BY (COL0)ORDER BY IID) AS RID FROM CUSTOMERS

)

SELECT NAME,ADDRESS,PHONE,EMAIL

FROM

(SELECT COL0,COL1,RID

FROM CUST_CTE)C

PIVOT

(

max(COL1)

FOR COL0 IN (NAME,[ADDRESS],PHONE,EMAIL)

) AS PivotTable;

Output:

The value “C_XYZ should belong to C_BM , but its moved to B_BM.

As I mentioned, the key is the partition column RID, let’s look at what is there in the RID by executing only the statement in CTE

WITH CUST_CTE(iid, COL0,COL1,RID)

(

SELECT iid, COL0,COL1 , ROW_NUMBER() OVER (PARTITION BY (COL0)ORDER BY IID) AS RID FROM CUSTOMERS

)

SELECT * FROM CUST_CTE ORDER BY 1,4

So, the partitioned value RID for C_XYZ should be 3 as it belongs to 3^rd set (look at the insert statement above) , but here its 2 , because I had partitioned the col0 order by IID.
So, if I can partition the data based on the sets, then it will resolve the problem, but there is no such function exists in SQL Server to partition based on defined sets. So I followed the below technique to partition the data based on the column value (NAME).

with cte_group(RID,IID,COL0,COL1)

(

select row_number() over (ORDER BY IID )AS RID,* from

--customers ~ select statement with adding one row at the end with value Name

(SELECT * FROM customers UNION SELECT MAX(IID)+1,'NAME','' FROM customers)A

where col0 = 'NAME'

)

-- sent data to a temp table

SELECT * INTO #GROUPS FROM CUSTOMERS a

join (SELECT A.RID, A.IID as strt ,B.IID as ends FROM cte_group A JOIN cte_group B ON B.RID - A.RID = 1) b

on a.iid >= b.strt and a.iid <b.ends

select * from #GROUPS

Execute the below script which shows the partitioned column based on sets without using partition function

select from #GROUPS

Output:

Now, you will get the desired output as the RID is able to show the correct data based on the sets by using the below script.

SELECT NAME,ADDRESS,PHONE,EMAIL

FROM

(SELECT COL0,COL1,RID

FROM #GROUPS)C

PIVOT

(

max(COL1)

FOR COL0 IN (NAME,[ADDRESS],PHONE,EMAIL)

) AS PivotTable;

OUTPUT:

sql server table column list with comma as seperator

sekhar@calsql.com (chandra sekhar pathivada) — Wed, 28 Apr 2010 09:05:00 PDT

Below function mostly will be used for automated tasks in SQL Server to produce the insert statements for data population and migration from other systems.

make sure to use the schema name with the table name eg: dbo.customers or sales.customer.
select dbo.fnColList 'dbo.customers'

SSIS archive and rename all the files in a folder in loop

sekhar@calsql.com (chandra sekhar pathivada) — Thu, 19 Nov 2009 11:56:06 PST

Archive and rename all the text files in a folder in loop
Download Package
ArchiveAndRename.dtsx

If one of the task in your SSIS package is to move the source text files to an archive folder after its processed then this can be done through the script task in SSIS.

Steps involved :

create a variable in SSIS package : right click anywhere in your package and click on variables --> create a variable as "vFileName" with string as datatype
Create a text file connection with the path to connect the source text file --> define the columns ( the connection path will disappear once its executed as it will use the expressions for connection string in next step)

ssis create expressions for connection in ssis : right click on text file in the connections area --> click on properties --> click on expressions --> select the user::variable in the left pane --> select "connection string" at the right pane --> click "Ok"

Drag the "foreach loop container" --> right click -->; click on properties --> click on variable --> select "vFileName"
Drag a dataflow or any other transformations to process the text file ( here this package is using a dummy dataflow)
Last task is to archive the source file using a "script task" to a different folder called ARCHIVE and rename the filename with date.

sql server list of all primary keys and foreign keys relations including tables in database

sekhar@calsql.com (chandra sekhar pathivada) — Thu, 08 Oct 2009 22:42:33 PDT

sql server function stuff before and after the string

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 05 Oct 2009 10:10:25 PDT

To append a string at each comma

Example for the string 'A,B,C'.

Result should be 'beforestringAafterstringbeforestringBafterstringbeforestringCafterstring

when you need these operations ?

Mostly during automations , for example if you are retrieving the columns from the sys.columns table to generate a generic select statement with casting as below example

column names output from the syscolumns 'IID,Name,Age'

output : CAST(IID AS VARCHAR(MAX)), CAST(Name AS VARCHAR(MAX)), CAST(Age AS VARCHAR(MAX))

execute the script to create the function as below and run the below statement

select dbo.fn_stuff('xx_,xxxx,',',','before','after')

create function dbo.fn_stuff(@vString varchar(4000),@exp varchar(5),@vStart varchar(2000),@vEnd varchar(2000))

returns varchar(8000)

begin

declare @vRet varchar(8000)

SELECT @vRet= coalesce(nullif(cast(charindex(@vString,@exp) as varchar(max)),0),

STUFF(replace(@exp+@vString,@exp,@vEnd+@vStart) + @vEnd , 1,len(@vEnd),''))

return @vRet

end

sql server function charindex at n position

sekhar@calsql.com (chandra sekhar pathivada) — Mon, 18 Jul 2011 22:22:04 PDT

Charindex at n position

To find the charindex of an expression “_” in n position say 3^rd occurancein a given word 'xx_xxx_xx_xx'

Result : 10

SELECT dbo.fn_charIndex('_',3,' xx_xxx_xx_xx')

CREATE FUNCTION [dbo].[fn_charIndex](@Expression varchar(256),@nPos int,@Word varchar(2000))

returns int

as

begin

Declare @ret int,@strt int, @strLen int

set @ret = 0

set @strt = 0

while not @strt = @npos

begin

select @ret = charindex(@Expression,@Word,@ret+1)

set @strt = @strt +1

IF @ret = 0

Break

ELSE

Continue

end

return @ret

end

sql server table columns as comma seperated string

sekhar@calsql.com (chandra sekhar pathivada) — Sun, 04 Oct 2009 21:46:14 PDT

Below function will produce the list of the columns in a table as a comma separated list.

It will be useful especially while making generic insert statements with column names as values.

usage : select dbo.fnColList('MyTable')

create function fnColList(@in_vcTbl_name varchar(8000))

returns varchar(8000)

begin

declare @colList2BuildAuditTable varchar(max)

SELECT @colList2BuildAuditTable = coalesce(@colList2BuildAuditTable+ ',', '')+ '['+ B.NAME +']'

FROM SYSOBJECTS A JOIN SYSCOLUMNS B ON A.ID = B.ID

WHERE A.ID = OBJECT_ID(@in_vcTbl_name)

ORDER BY B.COLORDER

return @colList2BuildAuditTable

end

sql server function to return the column datatype

sekhar@calsql.com (chandra sekhar pathivada) — Sat, 03 Oct 2009 19:22:06 PDT

select dbo.FN_COL_TYPE ('SALES.CUSTOMER','customerid')

CREATE FUNCTION FN_COL_TYPE (@TBL_NAME VARCHAR(256),@PK_COL_NAME VARCHAR(256))
returns varchar(256)
as
begin
DECLARE @vType VARCHAR(50)

select @vType = b.name from sys.columns a join sys.types b on a.system_type_id = b.system_type_id
where object_id = object_id(@TBL_NAME)
and A.name = @PK_COL_NAME
return @vType
end

system keywords in SQL Server

sekhar@calsql.com (chandra sekhar pathivada) — Tue, 06 Oct 2009 22:36:56 PDT

System Keywords in SQL Server

Download the code:

systemkeywords.sql

Its a good practise to not include the system keywords in the user defined objects such as tables,stored procedures, triggers,views etc..

To validate your database design quickly with whether it contains any system keywords , execute the attached code which will create a table called tbl_keywords with the data contains all the system keywords.

Once you done with execute the attached script then run the below statement to validate your table names in your database , especially during the design phase.

select * from sys.columns where type = 'U' and name in (select keywords FROM tbl_keywords)