Finding the Nth Row from a table

Hi all,

when you are selecting the data from a table and you are looking for the topmost or top N rows, getting that is easy with the TOP clause. But, for getting the Nth row in the table there is no direct method. But there are a couple of workarounds to it, that I am giving here.

The first is the old man's method who likes using the normal SQL query capabilities. (Could be a bit inefficient). Here is a method to get the 18th row from the table Sales.SalesOrderDetail, which you can find in the famous AdventureWorks database. If you dont have it, either download it, or change the query to use your table and column names.

  Select Top 1 LineTotal FROM
(Select Top 1 LineTotal FROM
(Select Top 18 LineTotal From Sales.SalesOrderDetail ORDER BY LineTotal Desc) AS a
Order By LineTotal) as b

The other method is using the Row_Number() function, which assigns the sequential numbering to the rows on the basis of the Order By clause. We have used the CTE here because the Ranking functions can not be used in the where clause. Have a look.

WITH MyCTE
AS
(
    SELECT DepartmentID, Name, ROW_NUMBER() OVER(ORDER BY DepartmentID) AS MyRank
    FROM HumanRescources.Department
)
SELECT * FROM MyCTE
WHERE MyRank = 18

I hope that you find the post useful.

SQL Server View Constraint Removed

Hi all,

You all are familiar with the views in SQL Server. They serve a lot of purposes, but they have there limitations too. One of the limitations of the view is that you can not have an ORDER BY statement a part of the query in SQL Server. I was never able to understand the logical reason for this constraint.

One of my students Paulo Underland V Rocha (phew..... long name)  from Norway. He wanted to avoid this problem and  we were looking at all the different methods to implement ordering the data inside the view (or in any object which can return a table).

But, if you read the error message of SQL Server when you try to do something like this

CREATE VIEW  vContactListByEmail

AS

SELECT  EMailAddress, FirstName, LastName

FROM Person.Contact

ORDER BY FirstName

Go

is

Msg 1033, Level 15, State 1, Procedure abc, Line 5

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So, by principal you can not use it in views functions, derived tables.....

but then we tried using this

CREATE VIEW  vContactListByEmail

AS

SELECT TOP 100 PERCENT EMailAddress, FirstName, LastName

FROM Person.Contact

ORDER BY FirstName

Go

since, if you know, you can use ORDER BY if you use the top clause. This worked, but to our surprise, it didnt showed us the data in the sorted order. But it was showing us the sorted data if we used any other value except 100. So finally, we used this query

CREATE VIEW  vContactListByEmail

AS

SELECT  TOP 99.999999999 EMailAddress, FirstName, LastName

FROM Person.Contact

ORDER BY FirstName

Go

and this one finally worked and gave us all the rows from the table in the sorted order from the table.

Simple isn't it ?

How to get the logins which are mapped to different Users

Hi friends,
Sorry for this long inactivity but I was a bit busy with one of my clients from Mexico Republic working in Europe.

This guy is an IT Auditor and he has a lot of queries regarding security and auditing in SQL Server. I will keep writing about the queries posed by him. So here is the first and a simple one.

He wanted to find out the way to get a list of all logins, well categorized by different criteria. eg. He wanted a list of all windows logins mapped as a SQL Server Login. All windows users mapped as SQL Login and all these logins mapped to which database as which user along with their default database.

So, for this query we need a few system catalog views. First is the sys.server_principals. This is really a good place to look for when you want to see all the server logins along with which server level role they are registered in the server (eg. sysadmin, serveradmin etc.). Another is the sys.sysusers. This is the catalog view which will give you the information about users mapped in different databases. Remember that this catalog view can give different results for different databases.

So here is that simple query, use it or modify it according to your own needs. DECLARE @i int, @ctr int, @dbname nchar(50)
SELECT @i = COUNT(name) FROM sys.databases
SET @ctr = 1
WHILE (@ctr <= @i) BEGIN SELECT @dbname = name from sys.databases WHERE database_id = @ctr
SELECT @dbname [Database Name]
EXECUTE('USE ' + @dbname + '; Select P.name [Server Login Name], S.name [Database User Name] , default_database_name [Default Database]' +
'FROM sys.server_principals P INNER JOIN sys.sysusers S ' +
'ON P.sid = S.sid WHERE S.name != ''dbo''')
SET @ctr = @ctr + 1
END

SQL Backups and Transaction Log

Recently I was involved in testing the performance of SQL Server 2005 in the event of an external attack. I used a machine with Pentium Dual Core 2.0 GHz, 3 GB RAM and sufficient amount of hard disk space.

I used a script to create 2 simple Products and Orders Tables and tried to insert 100,000,000 (A hundred million) rows into it. I used windows system monitor to monitor a few things. The important ones are

1. Free space in tempdb
2. Data File Space of my database
3. Log file space of my database
4. Lock request per second and
5. Page writes per second

The whole insertion part was in a single transaction.The script kept running for around 25 min. and then the Management studio gave me an error of "Insufficient Memory Resource". The transaction was automatically cancelled. By that time around 4,700,000 rows were transferred in the database.

The data file size at that time was around 59 MB, but the Transaction Log size was grown upto a whopping 5.87 GB. Well all this was still very OK for me, but then I started taking the backups of the database.

I started with a full database backup. I thought, according to my knowledge, that it won't have any effect on the Log file size. but the full database backup brought down the "Used Log File Space" down to 1/10th of its size.

After that I tried to take the Log backup and it had no effect on the size of the database. can u guess why? Because the full backup already committed the inactive portion of my transaction log and removed them. And there were no new transactions to be transferred. So the used space in log file remain unchanged. Then I used the DBCC SHRINKDATABASE command using WITH TRUNCATE option. Before executing this command the log file size was still 5.87 GB. What do you think, was the size of this database after execution of this command?

If you said 5.87 GB you are......... wrong. It's not 5.87 GB, not 4 GB, not 1 GB......, not even 200 MB. It was just 2.31 MB. Any guesses about how it turned out to be so small? Because the data insert operation was performed in a transaction and the transaction was rolled back when OS returned me the error of Insufficient resources. That error rolled back the transaction. But even then, the transaction was available in the transaction log. When we truncated the transaction log, the whole Rolled back transaction got removed from the transaction log. Another thing that even I was not able to find a reason for was, as soon as I used DBCC SHRINKDATABASE, for some strange reason, the "Used Transaction Log Space" shoot up for a few seconds and then started coming down. If anybody has the reason for that, he/she can write a comment.

Hope that may give you some insight about the backups and Transaction Logs.