Find First And Last Record using SQL Server

Let us see how to find the first and last record of a table based on a criteria. For demonstration purpose we will be using the 'Orders' table of the Northwind database. Our criteria in this example will be to find the First and Last Record of the Customer 'ALFKI' in this table.

Here's the query to do so:

WITH CTE
AS
(SELECT CustomerID,OrderDate,Freight,ShipName,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate) as StartRec,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) as EndRec
FROM ORDERS WHERE CustomerID='ALFKI')
SELECT CustomerID,OrderDate,Freight,ShipName
FROM CTE WHERE StartRec=1 or EndRec=1
ORDER BY OrderDate

Note: If you wish to find out the first and last record for all the customers in this table, just remove the condition WHERE CustomerID = 'ALFKI'.

How to Change the 'sa' password in SQL Server 2005

If you happen to forget your SQL Server password for 'sa' account, then here's a simple query to help you reset it:

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'MyNewPassword' MUST_CHANGE
GO


In Case you remember your Old Password and want to change the 'sa' password, use this query:


ALTER LOGIN [sa] WITH PASSWORD = N'MyNewPassword' OLD_PASSWORD = 'MyOldPassword';
GO

Increase Memory for Queries in SQL Server

The default memory for query execution 'min memory per query' allocated by SQL Server is equal to 1024 KB.

When should I increase the default memory allocated to queries?
1024 KB is sufficient to run queries, however you may need to increase the memory if you have an extremely busy server that runs many concurrent queries simultaneously or your query is quiet resource intensive. Also before increasing the memory, set a few performance benchmarks like the 'Expected Query Time' to determine if you really require an increase.

Note: Do not increase the memory unnecessarily as SQL Server may require it for other operations.

How can I increase memory for query execution?

Just use this query to increase the memory to 1536KB

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'min memory per query', 1536
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Free EBook - Introducing Microsoft SQL Server 2008

If you haven't yet downloaded this Free Ebook about SQL Server 2008, I would highly recommend you to do so.

Introducing Microsoft SQL Server 2008
by Peter DeBetta, Greg Low, and Mark Whitehorn

Table Of Contents:

Chapter 1: Security and Administration
Chapter 2: Performance
Chapter 3: Type System
Chapter 4: Programmability
Chapter 5: Storage
Chapter 6: Enhancements for High Availability
Chapter 7: Business Intelligence Enhancements

Download the Free E-Book from here

How to import XML data in SQL Server

In order to import XML data in SQL Server, you can use the BULK keyword with OPENROWSET

Let us explore how to import XML data with an example.

Step 1: Create an independent sample table in the Northwind database or any db of your choice :

USE NORTHWIND
CREATE TABLE CustomerDiscount (ID int NOT NULL, Discount xml)
GO

Step 2: Now create a XML file and save it on your disk. We will call the file 'sample.xml' and save it in C:\

<?xml version="1.0" encoding="UTF-8"?>
<Customers>
<Customer ID="1">
<Discount>20</Discount>
</Customer>
</Customers>

Step 3: Let us now import the xml data from C:\sample.xml into our newly created table 'CustomerDiscount'

INSERT INTO CustomerDiscount(ID, Discount)
SELECT 1, Disc
FROM
(
SELECT * FROM OPENROWSET(BULK 'c:\sample.xml',SINGLE_BLOB) as Disc
) AS ImportXML (Disc)

Note: Do not forget to specify the UTF-8 encoding scheme in the XML file.

Quickest way to change the location of a log file in SQL Server

The quickest way to move the log file to a different location is to Detach the database, Move the .ldf and then Attach the database

Step 1: Detach the Database:

Use this command to detach the database

sp_detach_db 'YourDBName'

Step 2: Move the log file (.ldf):

Move the log file to a different location. For eg: Move it from 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName_Log.ldf' to 'D:\Logback\YourDBName_Log.ldf'

Step 3: Attach the Database:

Once the file has been moved, attach the database again

sp_attach_db 'mydb','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName.mdf','D:\Logback\YourDBName_Log.ldf'

Note: To find out the current location of your database files, use the command 'sp_helpfile'

Obtain Exclusive Access to Database While Performing Maintenance Tasks in SQL Server 2005

When you need to perform maintenance tasks, you as a DB Admin may want to obtain exclusive access to the database. To do so, you can set the database to Single User Mode, which permits only one database connection at a time. By doing so, if other users try and access the database while you are working on that active connection, they will receive an error.

To bring a database to the single user mode, use the following query

ALTER DATABASE YOURDBNAME SET SINGLE_USER

Now if users are already connected to the db when you run this command, they will not be disconnected. Instead the 'SET SINGLE_USER' command will wait till the others have disconnected. If you want to override this scenario and forcefully disconnect other users, then use the following query

ALTER DATABASE YOURDBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Once you are through with the maintenance tasks, to return the database back to the multi-user mode, use this query

ALTER DATABASE YOURDBNAME SET MULTI_USER

How to Copy a Table from One Database To Another By Passing Table Names As Parameters

Let us take a scenario where you want to copy a table kept in one database to a table in another database. You would also like to keep the query dynamic and would pass the source and destination table names as parameters. Let us see how we can the stored procedure to do so

Create Stored Procedure to copy table

CREATE PROCEDURE usersp_COPYTABLE
@DestTable nvarchar(150), @SrcTable nvarchar(150)
AS
DECLARE @SQL nvarchar(100)
SET @SQL = 'SELECT * INTO ' + @DestTable + ' FROM ' + @SrcTable
-- Pass DestTable = 'AdventureWorks.dbo.CustomersTemp'
-- Pass SrcTable = 'Northwind.dbo.Customers'
EXEC (@SQL)

Execute Stored Procedure by passing table names as parameters

DECLARE @return_value int

EXEC @return_value = [dbo].[usersp_COPYTABLE]
@DestTable = N'AdventureWorks.dbo.CustomersTemp',
@SrcTable = N'Northwind.dbo.Customers'

SELECT 'Return Value' = @return_value

GO

SELECT TOP N Rows Per Group/Category

If you would like to select top 'n' rows for each group or category, then here is a query to do so :

-- Create Sample Table
DECLARE @TT table
( ID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 1, 1, 'AA')
INSERT INTO @TT VALUES ( 2, 1, 'AA');
INSERT INTO @TT VALUES ( 3, 1, 'AA');
INSERT INTO @TT VALUES ( 4, 2, 'BB');
INSERT INTO @TT VALUES ( 5, 2, 'BB');
INSERT INTO @TT VALUES ( 6, 2, 'BB');
INSERT INTO @TT VALUES ( 7, 2, 'BB');
INSERT INTO @TT VALUES ( 8, 3, 'CC');
INSERT INTO @TT VALUES ( 9, 3, 'CC');
INSERT INTO @TT VALUES ( 10, 3, 'CC');
INSERT INTO @TT VALUES ( 11, 3, 'CC');
INSERT INTO @TT VALUES ( 12, 3, 'CC');
INSERT INTO @TT VALUES ( 13, 4, 'DD');

-- Query to retrieve top 3 items per category
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CategoryGroupID ORDER BY ID) AS 'RowNo',
ID, CategoryGroupID, CategoryGroupName
FROM @TT
)
SELECT ID,CategoryGroupID,CategoryGroupName
FROM CTE
WHERE RowNo <= 3

-- Expected Output
1 1 AA
2 1 AA
3 1 AA
4 2 BB
5 2 BB
6 2 BB
8 3 CC
9 3 CC
10 3 CC
13 4 DD

To apply this on a real table, we will select the top 3 products by quantity for each category in the Northwind database

-- TOP 3 Products By Quantity for each Category
USE NORTHWIND;

WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CategoryName ORDER BY UnitsInStock DESC) AS 'RowNo',
CategoryName, ProductName, UnitsInStock FROM Categories c
INNER JOIN Products p ON c.CategoryID = p.CategoryID
)
SELECT CategoryName, ProductName, UnitsInStock FROM CTE
WHERE RowNo <= 3

SQL Server 2000 Exams to be discontinued by March 31st 2009

Microsoft has announced two SQL Server Exams that will retire on March 31,2009. Those are :

Exam 70-228: Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition

Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

This also means that the MCDBA credentials that is dependent on these exams can only be accomplished if you pass these two exams before March 31st 09.

Q. If you already hold a MCDBA, will your credentials expire once the exams retire?

Find out the answer to similar questions over here:

http://www.microsoft.com/learning/mcpexams/status/examstoretire.mspx