[Code] Finding the lowest ID (identity) for a date when there's no index on the date, like Zeno

Needed this today.  I remember seeing someone at a SQLSaturday (which for me this year, doesn't narrow it down much) with this idea, but couldn't figure out who it was, so I wound up implementing my own.

Say you have, like most of us, a table with an "id INT IDENTITY PRIMARY KEY," so your PK is the ID, but the table also has a date column.  Nobody cares about the date.  Until they do, months later.  So now you have a busy table that you can't easily add an index on, and an ADHOC OMGWTFBBQ project comes in, where you need to query a date range, and no idea how to get there quickly. .

*raises hand* Yup, that'd be me.  So I wrote this. 

What it does: cycle down through a table, using the ID column.  Start by decrementing the max ID from the table by 10 million.  Does that go below the date you need?  Yes?  Okay, try 1 million. Yes? 100k.  No? How about 200k? Yes. 110k? No. 120k? Yes. 111k? No. 112k? Yes. 111100? (and so on). 

It'll pop down even a terabyte table pretty quickly, since it only does a handful of queries, and they're all against the PK.  Granted, I could make it faster by doing a (min+max)/2, then (newmin+max)/2 etc, but this works.  I've nicknamed it "Zeno's Arrow", although technically my code doesn't go halfsies - but it is fast and direct.

Also, (importantly!) it may not cope with missing rows (which could happen due to stuff like, for instance, a failed transaction).  I don't have a good fix for that, yet.  Maybe in V2

Hope this helps.

[Code] Powershell - a basic script for all servers

Just acquainting myself with Powershell - I've known about it, but have preferred using Cygwin's shell. However, I wanted to run some code against all servers, and this seemed a good time to try with Powershell.

I found this original script, which would run the same code against all servers in a list. (http://www.quicksqlserver.com/2009/01/powershell-sqlcmd-and-invoke-expression.html)

foreach ($svr in get-content "C:\MyInstances.txt"){
    $svr
    invoke-expression "SQLCMD -E -S $svr -i createMyuser.sql"
   
}

So then I adapted it - using SQLCMD /Lc to get a list of servers on the network, print the server name, then run SQLCMD against each server, running the c:\simplemodel.sql script (while you can run SQL inline, it kept parsing the @@ and choking on it). Not foolproof, mind you, but a good starting point.

foreach ($svr in (sqlcmd /Lc))
{
        $svr
        invoke-expression "SQLCMD -E -S $svr -i c:\simplemodel.sql"
}

SELECT INTO from SP

Found this on usenet, courtesy of BP Margolin (who pointed at a post by Umachandar Jayachandran). I've always just figured out what the columns are and built a table, but this will save me a ton of work.

-- To enable data access locally, do:
EXEC sp_serveroption localsrvr , 'data access' , 'true'
--(where localsrvr is your server's name)

-- To do SELECT...INTO from SPs results do:
SELECT * INTO SomeTbl FROM OPENQUERY(localsrvr , '{call sp_who}')

-- If the SP uses temporary tables,
-- you have to do something like the following b'cos
-- the above call will fail
SELECT * INTO SomeTbl FROM OPENQUERY( localsrvr ,'SET FMTONLY OFF {call sp_who}')

But you need to understand that this will result in the SP being executed twice once when OPENQUERY tries to determine the metadata for the result set & again
when it actually executes the call.


[Sysadmin] Kick users after midnight

Not mine, but we use it. Pretty basic - looks for SPIDs that are in a particular database and kills the SPIDs. We use it to ensure that people don't have active connections during maintenance time.

The one downside is that it's very literal - if you aren't explicitly in the system as sysadmin, out you go. Set in a job to run right before your maintenance.


set nocount on
declare @spid nvarchar(10)
declare @killem nvarchar(20)
declare spid_csr insensitive cursor for
select spid from master..sysprocesses
where sid not in
(
select sid from master..syslogins
where sysadmin = 1
)
and dbid in (db_id('Main'),db_id('AnotherOne'))
and loginame like 'MyDomain%'
open spid_csr
fetch next from spid_csr into @spid
while @@fetch_status = 0
begin
select @killem = 'kill ' + convert(varchar(3),@spid)
exec (@killem)
fetch next from spid_csr into @spid
end
close spid_csr
deallocate spid_csr

[ETL] Disabling foreign key constraints

When you're doing bulk loads, the Foreign Keys can bring you to a standstill. I can't load that one without this one going first, etc.

Quick way around it: disable all the constraints, load, then enable. Thanks to "sqladmin" at http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=48410&enterthread=y for this one, as well as Erland for the WITH CHECK.

I love sp_MSforeach[...] - makes it very easy to walk through databases/tables. Indispensable.

One note, though: even if you disable the constraints, on SQL Server 2005 SP2 (and possibly others; haven't thoroughly tested yet) you still cannot TRUNCATE the table. You can DELETE from it, just not TRUNCATE.

To Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

To Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

To Enable all Constraints (the WITH CHECK forces it to verify all the constraints are now good. Very important, as it helps ensure good execution plans)
exec sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

Enable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'