TABLESAMPLE Gotcha (ah the stress)

I needed a few random rows from one of our tables with at least a million rows.
I used the following query:

SELECT
  myID,
  ROW_NUMBER() OVER (ORDER BY myID) AS RowNumber
FROM
  AD
TABLESAMPLE (25 ROWS)

This worked awesome when I was testing it on my local machine. However, as soon as I deployed it to a dev machine, it just decided to stop working. I knew TABLESAMPLE was pretty random – it can return 15 or 30 or 100 even if I request for 25 rows. I just didnt realize it was very unreliable.

I found this post from Erland Sommarskog:
http://www.issociate.de/board/post/450002/TABLESAMPLE_question.html
Quote:

I saw the suggestion to use TABLESAMPLE, and I was not very happy with it, but I did not reply to at the time. TABLESAMPLE is fairly
approxamite in its nature. I ran

SELECT * FROM Orders TABLESAMPLE (1 ROWS)
SELECT * FROM Orders TABLESAMPLE (10 ROWS)
SELECT * FROM Orders TABLESAMPLE (100 ROWS)

in an inflated version of Northwind with 344000 orders. The first two
selects returned no rows at all, the last returned 86 rows. The first
time. The second time it returned no rows, and the last time it return 132 rows. Furthermore, the sample was not entirely random, but I got a couple of sequences of order ids. Presumably because TABLESAMPLE works on page level.

Possibly you could combine newid() and TABLESAMPLE:

SELECT TOP 1 * FROM (
select * from Orders TABLESAMPLE (100 ROWS)) AS d
ORDER BY newid()

You would need to make your sample size big enough so that you are
sure that it retrieves a row each time, but the bigger you make it,
the bigger the cost for the sorting.

If your table is only some few thousand of rows, it’s not likely to
be worth the pain.

–
Erland Sommarskog, SQL Server MVP, esquel [at] sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

I will go with his suggestion – NOT to use TABLESAMPLE.

Awesome reference on SQL Server dates

Robyn Page’s SQL Server DATE/TIME Workbench
http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

 

Fixing Orphaned Users

This is a follow up posting on

Issue: Server principal is not able to access the database under the current security context

Here is a script to loop through all of the users and re-attach them to their logins. Note that the assumption here is the logins in the old server/instance exists in the new server/instance

SQL Server 2000/2005 (using sp_change_user_login, which is marked for deprecation):

   1: DECLARE @UserName varchar(35)

   2:  

   3: DECLARE UserListCrs CURSOR FOR

   4: SELECT 

   5:     [name]

   6: FROM 

   7:     sysusers

   8: WHERE 

   9:     issqluser = 1

  10:     AND SUSER_SNAME(sid) IS NULL

  11:     AND [name] <> 'guest'

  12:     -- add additional exceptions here if needed

  13:  

  14: OPEN UserListCrs

  15:  

  16: FETCH NEXT FROM UserListCrs

  17: INTO @UserName

  18:  

  19: -- loop through all users, and remap the user to the login

  20: -- assumption: username is the same as loginname

  21: WHILE @@FETCH_STATUS = 0

  22: BEGIN

  23:     

  24:     EXEC sp_change_users_login 'update_one', @UserName, @UserName  

  25:    

  26:     FETCH NEXT FROM UserListCrs

  27:    INTO @UserName

  28: END     

  29:  

  30: CLOSE UserListCrs

  31: DEALLOCATE UserListCrs

SQL Server 2005 onwards (using ALTER LOGIN, replacement for sp_change_users_login):

   1:  

   2: DECLARE @UserName varchar(35)

   3: DECLARE @SQLString varchar(500)

   4:  

   5: SET @SQLString = ''

   6:  

   7: DECLARE UserListCrs CURSOR FOR

   8: SELECT 

   9:     [name]

  10: FROM 

  11:     sysusers

  12: WHERE 

  13:     issqluser = 1

  14:     AND SUSER_SNAME(sid) IS NULL

  15:     AND [name] <> 'guest'

  16:     -- add additional exceptions here if needed

  17:  

  18: OPEN UserListCrs

  19:  

  20: FETCH NEXT FROM UserListCrs

  21: INTO @UserName

  22:  

  23: -- loop through all users, and remap the user to the login

  24: -- assumption: username is the same as loginname

  25: WHILE @@FETCH_STATUS = 0

  26: BEGIN

  27:     

  28:     SET @SQLString = ' ALTER USER ' + @UserName +

  29:                          ' WITH LOGIN = ' + @UserName

  30:     

  31:     PRINT @SQLString

  32:     -- uncomment below when you are ready to execute

  33:     -- you may also want to rewrite this dynamic SQL using sp_executesql

  34:     -- EXEC(@SQLString)

  35:    

  36:     FETCH NEXT FROM UserListCrs

  37:    INTO @UserName

  38: END     

  39:  

  40: CLOSE UserListCrs

  41: DEALLOCATE UserListCrs

  42:  

  43: -- Sample Output

  44: -- ALTER USER belle WITH LOGIN = belle

  45:  

 

Here is a good reference:

Understanding and dealing with orphaned users in a SQL Server database

http://www.mssqltips.com/tip.asp?tip=1590

Follow up on Issue: Server principal is not able to access the database under the current security context

SQL Server operates on the concepts of Logins and DB Users.

A login (server level) can map to a single database user. The name of the db user can be the same, or can be different. A login is mapped to a user via an SID (security ID)

Often, when databases are moved around, this login-user mapping gets lost because a different login in a different server or instance will have a different SID. And this causes grief not just to users but also to DBAs.

Couple ways to fix this:

 

1. sp_change_users_login

This is marked for deprecation, but you can still use this.

3 options for sp_change_users_login:

Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present

Report, it lists the orphaned users and their security identifiers (SID)

Update_One, it links the specified database user to an existing SQL Server login

EXEC sp_change_users_login @Action=’Report’

EXEC sp_change_users_login  @Action =  ‘Update_One’ 
   UserNamePattern = ‘dbusername’  
    @LoginName = ‘loginname’ 

When there are a lot of users, can use a cursor.

 

2. ALTER LOGIN – better way or recommended way

ALTER USER dbusername WITH LOGIN ‘loginname’

Issue: Server principal is not able to access the database under the current security context

Issue:
We backed up and restored one database from one server to another. Sounds simple, right? Should be. This isn’t one of those days

So we get this issue:

The server principal “myuser” is not able to access the database “mydb” under the current security context.

Resolution:
Thanks to the following forums, we were able to troubleshoot. Steps follow:

1. In order to ‘Map’ the Login, the Login must not already be as User on the database.

Go to ServerName -> Databases -> DatabaseName -> Security -> Users

2. Delete the Login from the list of database Users before mapping the Login to the database.

3. In Object Explorer -> Server -> Security -> Logins, right click and choose Properties

4. The ‘User Mapping’ page should list all databases on the server with a check mark on the databases that the Login has been mapped to. 

Map the Login by checking the box next to the database name.

You may also need to add some fixed database roles, just to get your stuff working first:

ie, db_datareader and public. 

5. Click OK
6. Rejoice

References:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1638146&SiteID=17

http://www.sqlservercentral.com/Forums/Topic362018-359-1.aspx

Follow Up Post:
Follow up on Issue: SQL Server Principal Is Not Able to Access the Database Under the Current Security Context

Executing Batches Multiple Times (SQL Server 2005 only)

In SQL Server 2005, we can execute batches multiple times using the following syntax following the GO delimiter

GO n

– Sample Script

   1: DECLARE @id int

   2:  

   3: SELECT @id = MAX(id)

   4: FROM #tmp 

   5:  

   6: INSERT INTO #tmp

   7: VALUES (@id + 1)

   8:  

   9: SELECT MAX(id)

  10: FROM #tmp

  11:  

  12: GO 5

List all ASCII characters

   1: -- ---------------------------------------------------------------------

   2: -- displays ASCII characters, and identify which ones are printable

   3: -- ---------------------------------------------------------------------

   4: DECLARE @min int, @max int

   5: DECLARE @chars TABLE

   6: ( 

   7:     ASCIIValue       int,

   8:     ASCIIString      nvarchar(3),

   9:     IsPrintable      bit 

  10: )

  11: SET @min = 0

  12: SET @max = 300

  13:  

  14: -- Codes 33 to 126 are printable characters

  15: -- http://en.wikipedia.org/wiki/ASCII

  16: WHILE @min <= @max

  17: BEGIN

  18:     INSERT INTO @chars

  19:     SELECT 

  20:         @min, 

  21:         CHAR(@min),

  22:         CASE 

  23:             WHEN @min BETWEEN 33 AND 126 THEN 1

  24:             ELSE 0

  25:         END

  26:     SET @min = @min+1

  27: END

  28:  

  29: SELECT 

  30:     * 

  31: FROM 

  32:     @chars

  33: WHERE 

  34:     ASCIIString IS NOT NULL

  35:  

  36:  

  37:  

DBAs are humans too …

Kalen Delaney’s post on DBA blunders. Not really for the faint of heart

http://sqlblog.com/blogs/kalen_delaney/archive/2008/05/15/dba-blunders.aspx