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!

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.

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.

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.

How to uninstall SQL Server 2005 instances successfully

With the passing of days, the number of people asking me how to uninstall SQL Server 2005 instances successfully is on increase. I would like to say that it is a simple question but quite difficult to answer as there are many related components installed which need to be uninstalled by following a correct order, otherwise, we are going to fail. Today's post is going to explain my own technique to do it with ease and success.

Here is the correct procedure to uninstall a SQL Server 2005 instance:

1. Firstly, we do have to find the right directory where 'ARPWrapper' tool is installed (usually it is inside of either 'C:\Program Files' or 'C:\Program Files (x86)' folders). After doing that, we can start the tool by executing one of the two following commands via CMD with '/remove' parameter included.

"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe" /Remove

"C:\Program Files (x86) \Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe" /Remove

Having done that, 'ARPWrapper.exe' will start and we will be able to choose the SQL Services we want to uninstall. The wizard shows two parts: the first one lists the server components and the second one the common components. Because we only want to uninstall all server components (Database Engine, Analysis Services, SSIS, and etc.), we will select the first one and then click on 'Next' and 'Finish'.


Naturally, if we wanted to uninstall the common components at the same time, we will have to check all of them in the same window:



Only after successfully completing the process will we be able to move on the next steps. (If we had Reporting Services installed, we would have to make sure that its virtual directories were deleted as well, otherwise, we do have to delete manually the directories ReportServer[$SQLInstanceName] and Reports[$SQLInstanceName] by using IIS Services Manager)

2. We are ready to move on. Going to 'Add or Remove Programs', we will uninstall some components following strictly this order: 

• MSXML 4.0 Parser
• SQLXML6
• Microsoft SQL Server VSS Writer
• Microsoft SQL Server 2005 Backward Compatibility Components
• All components related to SQL Server 2005, except Microsoft SQL Server Native Client y Microsoft SQL Server Setup Support Files components

(It is time to move on the next step if this was the only SQL Instance that we wanted to remove, otherwise, we need to come back to the first step and do the same for each SQL Instance until removing all.)

3. Remove 'Microsoft SQL Server Native Client' component by using  'Add or Remove Programs'.

4. Remove 'Microsoft SQL Server Setup Support Files' component by using ''Add or Remove Programs'.

5. Finally, we have to restart the server.

To sum up, not only do we have SQL Server 2005 uninstalled successfully, but also very cleanly by following some simple and practical steps. Now this task is not going to be a pain in the neck any longer. 

I hope you find this post very useful and make the most out of it. That is all for now, let me know any remarks you may have uninstalling SQL Server 2005. Thanks for reading. Stay tuned.