Retrieve Records from Row X to Row Y From a SQL Server Table that does not have an ID column

In one of my previous posts, I had discussed How to retrieve record from a specific row for tables that do not have an ID. In this post, I will show you how to retrieve records between two Row Numbers from a SQL Server 2005/2008 table

Taking the example of the Northwind database, the table Customer has a primary key that is non-numeric. So in order to find the rows between position 10 and 20 (ordered by CustomerID), we will use a CTE (Common Table Expression) to do so. Here’s the query:

USE NORTHWIND
GO
DECLARE @Start as smallint = 10;
DECLARE @End as smallint = 20;

WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS cid,
    CustomerID, CompanyName           
    FROM Customers
)
SELECT cid as ID, CustomerID,  CompanyName   
FROM CTE
WHERE cid BETWEEN @Start AND @End

OUTPUT

Microsoft SQL Server Migration Assistant 2008 for MySQL 1.0 CTP 1

For those who missed out on the announcement, SQL Server Migration Assistant for MySQL is available for download here SSMA 2008 for MySQL v1.0 CTP1.

Microsoft SQL Server Migration Assistant (SSMA) 2008 is a toolkit that dramatically cuts the effort, cost, and risk of migrating from MySQL to SQL Server 2008 and SQL Azure. SSMA 2008 for MySQL v1.0 CTP1 provides an assessment of migration efforts as well as automates schema and data migration. Read more about this release over here

Here are the download links:

SSMA SQL Server 2005 for MySQL v1.0 CTP1

SSMA SQL Server 2008 to MySQL v1.0 CTP1

You can provide feedback about this tool to the team at ssmateam@microsoft.com

SQL Server 2008 R2 Release Date Announced

A couple of days ago, the official release date of SQL Server 2008 R2 was announced. SQL Server 2008 R2 will be made available in May 2010.

As mentioned in the announcement, key customer benefits include:

· A trusted and scalable platform with high availability, Master Data Services supporting data consistency across heterogeneous systems, StreamInsight enabling high-scale complex event processing, and support for high scale applications with up to 256 logical processors.

· IT and developer efficiency through central management of multiple database applications, instances or servers, accelerating the development and deployment of applications and providing improved support for virtualization and Live Migration through Hyper-V™ in Windows Server 2008 R2.

· Managed self-service business intelligence empowering a new class of business users to build and share powerful BI solutions with little to no IT support, while still enabling IT to monitor and manage end-user generated BI solutions with the new PowerPivot for Excel and PowerPivot for SharePoint

You can still download the November CTP of SQL Server 2008 R2 from http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx

Using SQLCMD to export SQL Server Data as Comma Seperated Values

I have doing a couple of posts on SQLCMD recently (here and here) and have received some interesting questions too. One of the questions that caught my attention was to export SQL Server Data as CSV’s using SQLCMD.

Here’s the query to so do:

sqlcmd -S Suprotim-PC -d Northwind -E -Q "SELECT CustomerID, CompanyName, ContactName from Customers" -o "D:\MyData.csv" -s","

This query extracts data from 3 columns from the Customers table of the Northwind database and saves it in D:\MyData.csv

After running the command, here’s the output received

Restore SQL Server 2008 backup in SQL Server 2005

I have been asked this question many times – Is there a way to restore a SQL Server 2008 backup in SQL Server 2005. The plain answer is No. But there are workarounds.

The best option I know how to do this is to use the ‘Generate SQL Server Scripts’ Wizard. To do so, open SQL Server 2008 Management Studio > Databases > Right click Your Database > Tasks > Generate Scripts

Here’s a screenshot from my test machine:

A wizard appears. Follow the wizard and in the ‘Choose Script Options’, set the ‘Script for Server Version’ to ‘SQL Server 2005’ and set ‘Script Data’ to ‘True’

Now complete the wizard by saving the script and you should now have a script that can be restored in SQL Server 2005.

There’s also a tool called SQL Data Compare from RedGate that can be useful in this scenario.

List all the SQL Server databases in the current instance using SQLCMD

I had recently written a post on Executing a Stored Procedure using SQLCMD. A user commented back asking if it was possible to print a list of databases in the current instance using SQLCMD. The answer is that it is very much possible and easy using the sp_databases system stored procedure. Here’s the query to do so:

sqlcmd -S <ComputerName>\<InstanceName> –Q “sp_databases”

On my computer, I used the following command

sqlcmd -S SUPROTIM-PC -Q "sp_databases"

and got the following output

Moving the tempdb database to another location

Here are the steps to move the tempdb to another location

1. Determine the logical and physical filename of tempdb using sp_helpfile

use tempdb
go
sp_helpfile
go

2. Now use the ALTER database and specify the FILENAME parameter to the location where you want to move each file.

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (name = tempdev, filename = 'D:\Data\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (name = templog, filename = 'D:\Data\templog.ldf')
GO

You will receive a message similar to the following “File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.”

3. Stop and then restart SQL Server.

SQLIOSim - Identify the optimal disk configuration and troubleshoot I/O faults before deploying SQL Server

I came across a very cool tool while helping one of my colleagues troubleshoot a database corruption issue. This tool is called SQLIOSim which simulates SQL Server I/O workload, even when SQL Server is not installed. In earlier versions, we had the SQLIOStress utility which tested I/O workload. SQLIOSim is an enhanced version of this tool.

Note: As a best practice mentioned over here, to help maintain appropriate data integrity and security, Microsoft recommends that you perform stress tests of your I/O subsystem before you deploy SQL Server on new hardware. The SQLIOSim utility simulates the read patterns, the write patterns, and the problem identification techniques of SQL Server.

Here’s a document that will teach you the basics of SQLIO.

How to exclude databases while using the sp_MSforeachdb stored procedure

Some time back, I had done an article on 6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

So now if you want to use this undocumented stored procedure to list the size of all databases in a SQL Server Instance, you would write the following query:

EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

However if you wanted to exclude some databases from the query, then here’s how to do so:

EXEC    sp_msforeachdb 
"IF      '?'     NOT IN ('master','tempdb', 'model')   BEGIN
        USE ?; EXEC sp_spaceused
END"

This query will now list the size of all databases except the master, tempdb and model databases.

How to quickly determine the DataType and Nullability of a Column in a query in SQL Server Management Studio 2008

When there are too many columns referenced in a query, it becomes difficult to track the datatype of each column referenced in that query. In such cases, SSMS 2008 offers a very handy feature using which you can determine the datatype and nullability of a column without going to the Object Explorer.

This feature is called the ‘Quick Info’ feature and if not already enabled on your machine, this feature can be enabled using Edit > Intellisense > Quick Info

Once enabled, you can then hover your mouse over a column in a query to see it’s datatype and nullability as shown below. Here we are viewing the datatype and nullability of the EmployeeID column.

Allow Users to Access a Table even when New Indexes are being created

When you are creating a new index on a table, Table locks are applied for the duration of the index operation. For large tables, index creations can take a longer time and this prevents user access to the table till the index gets created.

However if you want to allow users to access the table even when new indexes are being created, you can do so using the ONLINE option. Here’s a query that shows how:

CREATE NONCLUSTERED INDEX IX_VendorAddress_AddressTypeID
ON Purchasing.VendorAddress(AddressTypeID)
WITH (ONLINE = ON)

Note: By default, the ONLINE option is set to OFF.

How to Reduce the Size of a Non-Clustered Index in SQL Server 2008

The Alter Index command can be used to reduce the size of a Non-Clustered Index. To reduce the size of a Non-Clustered Index, we will be using the DATA_COMPRESSION = ROW option to compress the Index by using row compression. The other options are None and Table

Here’s the query. Observe that the REBUILD keyword is used with the WITH clause since we are modifying an existing index.

ALTER INDEX IX_WorkOrder_ProductID
ON Production.WorkOrder
REBUILD
WITH (DATA_COMPRESSION = ROW)

Microsoft will end Service Pack Support for SQL Server 2005 SP2 and SQL Server 2008 RTM

Microsoft is ending support for SQL Server 2005 Service Pack 2 (SP2) on January 12, 2010 and support for SQL Server 2008 RTM on April 13, 2010.

Both SQL Server 2005 SP2 and SQL Server 2008 RTM will no longer receive assisted support or security updates from Microsoft after their respective end of support dates. Self-Help online support will continue to be available for a minimum of 12 months after the product reaches the end of support. Self-Help options include public knowledge base articles, FAQs, troubleshooting tools which are typically available from http://support.microsoft.com/ and the Microsoft Download Center.

Read more over here

How to capture SQL Server PerfMon Counters without running Performance Monitor

The performance monitor is a great tool to monitor the performance of your network. You can use PerfMon to view SQL Server objects and performance counters as well as the behavior of other objects. However if you want to know an alternative method to capture SQL Server PerfMon Counters without running the Performance Monitor Tool, then use a SQL Server Operating System Related Dynamic Management View called sys.dm_os_performance_counters

SELECT * FROM sys.dm_os_performance_counters

I would still prefer the GUI, but knowing an alternate way of capturing PerfMon Counters does not hurt!

Quickly Add and Drop a Column from all Tables in SQL Server

If you have been looking out for a quick way to add and drop a column from all the tables in SQL Server, then here’s the query for it. I will use an undocumented stored procedure sp_MSforeachtable for this purpose

USE Northwind
GO
-- Add a Column to all tables
EXEC sp_MSforeachtable 'ALTER TABLE ? ADD ColumnTemp varchar(10) NULL'

-- Remove a Column from all tables
EXEC sp_MSforeachtable 'ALTER TABLE ? DROP COLUMN ColumnTemp'

The query shown above adds the column ‘ColumnTemp’ to all tables in the Northwind database. Similar the ‘ColumnTemp’ is then removed from all the tables.

Note: While removing a column, I am assuming there are no constraints defined.