Saturday 16 February 2008

SQL Server 2008: Row Value Constructor

Definitely, we were sometimes in a need of a new technique to insert data massively in SQL Server 2005 and previous versions. We used to do it by using traditional techniques like UNION ALL and individual INSERT statements. This story has ended up with the arrival of a new feature in SQL Server 2008 which allows to insert much data with only one INSERT statement, that is, Row Value Constructor.

To begin with, we are going to illustrate this new feature by creating a new table where four data rows will be inserted.

CREATE TABLE [Production].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileName] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileExtension] [nvarchar](8) COLLATE Latin1_General_CS_AS NOT NULL,
    [Revision] [nchar](5) COLLATE Latin1_General_CS_AS NOT NULL,
    [ChangeNumber] [int] NOT NULL CONSTRAINT [DF_Document_ChangeNumber]  DEFAULT ((0)),
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) COLLATE Latin1_General_CS_AS NULL,
    
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Document_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Document_DocumentID] PRIMARY KEY CLUSTERED 
(
    [DocumentID] ASC
)
) ON [PRIMARY]

At first, we will do it in SQL Server 2005. This is the traditional and old-fashioned way (one INSERT statement per row): 

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...')

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL)

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...')

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...')

This is the cumbersome way by using UNION ALL clause which has sometimes poor performance:

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
SELECT 'Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...'
UNION ALL
SELECT 'Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL
UNION ALL 
SELECT 'Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...'
UNION ALL
SELECT 'Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...'

Now in SQL Server 2008 we can use Row Value Constructor feature (only one INSERT statement to insert four data rows):

INSERT INTO [Production].[Document](  
      [Title]
      ,[FileName]
      ,[FileExtension]
      ,[Revision]
      ,[ChangeNumber]
      ,[Status]
      ,[DocumentSummary]
)
VALUES ('Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...'),
('Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL),
('Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...'),
('Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...')

As you have noticed, we do not need to use UNION ALL any longer, it allow to save a lot of time and work. It is truly useful when we need to insert much data and it also offers a remarkable performance. I hope you enjoy this new feature. Thanks for reading again!

Saturday 5 August 2006

SQL Formatter Tool

Being asked sometimes about a good tool for formatting T-SQL code, I would like to share one of them known as 'SQL Formatter tool'. Not only will this tool format T-SQL code, but also MS ACCESS, ORACLE/PLSQL, DB2, and MySQL. The great thing of this tool is that we will be able to generate output results for HMTL, C#, VB, Cobol, PHP, Java, and others. Try using it to see if it works for you and then make the most out of it. Here is the link of the web version:

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Let me know any remarks or experience you may have using the tool. Thanks for reading. Stay tuned.

Sunday 21 May 2006

Installing SQL Server 2005 Service Pack 1 on all SQL instances via CMD

When it comes to installing SQL Server 2005 Service Packs on various instances rapidly, we can do it by executing the Service Pack setup with some parameters via CMD. First of all, we can use it to figure out which parameters we do have available to work with, so the following can be done:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /?

Having executed that via CMD, the complete list of parameters will be shown:

/? – Help
/quiet – Quiet mode
/reportonly -Instance report only
/allinstances – Apply patch all product instances by default
/instancename – Product instance names to patch
/SAPWD – SQL SA password
/use – Remote admin domain and account
/password – Remote admin password


Furthermore, if we are curious to know about how many SQL instances have the right service pack installed, we may use the '/reportonly' parameter:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /reportonly

Only after that will we be able to verify which client and server components are installed for each SQL Server 2005 instance like MSXML Parser version, Database Engine services, SSAS services, SSIS services, and so on. Now we are ready, for example, to install the SQL Server 2005 Service Pack 1 on SVPRDB:  

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /instancename SVPRDB

Finally, we may want to install it on all SQL Server 2005 instances in the same server:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /allinstances

As you have been, using this technique is not complex. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.

Friday 14 April 2006

How to remove 'sa' account from 'sysadmin' fixed server role in SQL Server 2000

When it comes to securing the databases, we need to have a small number of accounts with 'sysadmin' fixed server role, which means that only the DBA and someone else should be assigned to that role inside the database engine. So, I highly recommend disabling or removing 'sa' account from 'sysadmin' fixed server role in SQL Server 2000. Nevertheless, this task is not so easy to do because any change on 'sa' account is protected internally by SQL Server. Furthermore, Microsoft says that this account cannot be changed at all http://msdn.microsoft.com/en-us/library/aa905197(SQL.80).aspx.
System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords).
Because I also know that "In theory there is no difference between theory and practice. But, in practice, there is.” (Jan L.A. van de Snepscheut), I was digging deep into this issue and found out that, fortunately, what Microsoft says is true up to a point, but we can take control of it by making small changes internally. In other words, the famous 'sa' account can be changed in SQL Server 2000. Today's post is going to show how to achieve it with ease. (The demonstration is done on SQL Server 2000 Service Pack 3a.)

First of all, we do need to know that the logic to manage the 'sa' account is stored inside the 'master' system database of SQL Server. Consequently, I decided to look into the code of sp_dropsrvrolemember, sp_addsrvrolemember, and sp_droplogin system stored procedures. What I figured out is that all of them contain the following validation based on the name of the account to prevent from any change.


According to that code, only if we change that name will SQL Server allow us to make other changes on it. Likewise, I also noticed that SQL Server does not allow to delete this account if its SID is equal to '0x01', so, if I we also change that SID, we will be able to delete the account. This is what we are going to do now.

Naturally, we will get the following error when we try to remove the 'sa' account from 'sysadmin' fixed server role:

exec sp_dropsrvrolemember sa,‘sysadmin’ 

Server: Msg 15405, Level 11, State 1, Procedure sp_dropsrvrolemember, Line 40 
Cannot use the reserved user or role name ‘sa’.

Trying to drop the 'sa' account:

exec sp_droplogin sa

Server: Msg 15405, Level 11, State 1, Procedure sp_droplogin, Line 39
Cannot use the reserved user or role name ‘sa’.


In order to be able to make those changes, we firstly need to enable 'allow updates'.
exec sp_configure 'allow updates', 1  
go  
reconfigure with override 

Having done that, we can remove the 'sa2' account from 'sysadmin' fixed server role.

exec sp_dropsrvrolemember sa2, 'sysadmin'

Changing the SID to '0x02':

 update sysxlogins set sid=0x02 where name='sa2' 

Finally, only now are we able to delete 'sa2' account.

exec sp_droplogin 'sa2' 

Having successfully executed each step, we have succeeded in deleting the 'sa' account. As you have seen, it was not overly complicated to get this account deleted. On the other hand, how can we create that account again? if we try to create again the 'sa' account, we will get this error:

exec sp_addlogin 'sa'

Server: Msg 15405, Level 11, State 1, Procedure sp_addlogin, Line 49
Cannot use the reserved user or role name ‘sa’.


Do not worry about that, we can also create it again by executing this code as follow:

exec sp_addlogin 'sa2'  
go  
exec sp_addsrvrolemember sa2,'sysadmin'  
go 
update sysxlogins set name='sa' where name='sa2'
go
update sysxlogins set sid=0x01 where name='sa' 


That is all for now. I hope you find this tip helpful. Let me know any remarks you may have. Thanks for reading. Stay tuned.
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE