[Code] Xtype definitions

'C' =  CHECK constraint 
'D' =  Default or DEFAULT constraint  
'F' =  FOREIGN KEY constraint  
'L' =  Log  
'FN' =  Scalar function  
'IF' =  In-lined table-function  
'P' =  Stored procedure  
'PK' =  PRIMARY KEY constraint (type is K)  
'RF' =  Replication filter stored procedure 
'S' =  System table  
'TF' =  Table function  
'TR' =  Trigger  
'U' =  User table  
'UQ' =  UNIQUE constraint (type is K)  
'V' =  View  
'X' =  Extended stored procedure 

[Ugly] Drop a view using Dynamic SQL

There's a time and a place for Dynamic SQL. This is, as far as I know, one of them.

I need to ensure a view (standard_view) is dropped in a particular database (My2008DB). That database is named for the year, so it's different each year. I can't use sp_msforeachdb for a particular (REDACTED) reason. You probably don't have that problem, I do.

So, best I can tell, the way to do it is to call sp_executesql from within the context of that other database. And since I have to execute the code to verify the drop before doing it, we wind up with this particularly ugly piece of code. Yes, we're 3 layers deep. sp_executesql calls My2008DB.dbo.sp_executesql, which calls the actual drop code. And yes, it works, at least on SQL Server 2005.

DECLARE @date SMALLDATETIME, @drop_view VARCHAR(8000) ,@SQLString nvarchar(500), @ParmDefinition nvarchar(500)
select @date = '5/15/2008'

--DROP VIEW.  Need to look in the DB, determined dynamically via the @date
SELECT @drop_view = 'if object_id(''My' + CONVERT(CHAR(4),@date, 112) + 'DB..standard_view'') is not null
    drop view standard_view'

--now we create a "wrapper" to run in the correct database context.
SELECT @sqlstring = N'execute My' + CONVERT(CHAR(4),@date,112) + 'DB.dbo.sp_executesql @mysql', @ParmDefinition = N'@mysql nvarchar(max)'

--execute in our dynamic db
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@mysql = @drop_view

[Tips] HOW TO find page splits by reading the transaction log

http://strictlysql.blogspot.com/2009/10/identifying-page-splits.html

There are two tricks in here. The first is using ::fn_dblog (undocumented function) to read the transaction log for a database.
The second is pulling out the delete messages involved in a page split and using that to determine where the splits are occurring.

select AllocUnitName, count([AllocUnitName]) [Splits]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
    and parsename(AllocUnitName,3) <> 'sys'
group by AllocUnitName

[Tuning] Getting only the DATE from a datetime

Many ways to handle dates, and most time I don't think about it - then came across a gigantic thread over two years that discussed it.


Comparing dates. Traditionally, for simplicity, to mark particular dates, I've used convert(char(8),a_date_field,12), but it’s non-SARGable, and not terribly efficient.


Looking online I found a couple alternatives, though they’re mostly useful when having to deal with large tables.

http://blog.sqlauthority.com/2008/10/18/sql-server-retrieve-select-only-date-part-from-datetime-best-practice-part-2/

For SQL Server 2008 it's pretty simple:

SELECT  cast(GETDATE() as date)
or
SELECT CONVERT(date,GETDATE())

(date is a new datatype that only stores the date, and is 3 bytes in size)

For SQL Server 2005, there seem to be two options. Naturally, neither is SARGable from what I’ve seen.

SELECT CONVERT (datetime, FLOOR(CONVERT (float, a_field_name)))
and
SELECT DATEADD ( DAY, DATEDIFF(DAY, 0, a_field_name), 0)

The first one performs math on the internally-stored FLOAT value of the datetime - right now, for instance, is 40315.37003125, where the whole number is the day and the fraction is the time. (Note that you can’t cheat and use INT; it can round up during the conversion).

The second one does date manipulation - there's a whole article online about using datediff and dateadd and the like to get the first day of the month, the first day of last month, etc, etc.

From what I’ve seen online, the FLOOR option can be slightly faster than the DATEADD, but either is considerably faster than CONVERT(char(8)).

[Tuning] Disk usage, redux

Here's a slightly different version of an earlier query, that tells you how much IO each database is getting.

This one is by database, not by file - did this since it makes dealing with TEMPDB easier. So you can't use it for physical file IO - that's the other version. And it's in GB, since that's our issue.

SELECT

sysdb.name AS name,

master_files.type_desc,

vfs.sample_ms,

SUM(vfs.num_of_bytes_written)/1024/1024/1024 AS GB_written,

SUM(num_of_bytes_read)/1024/1024/1024 AS GB_read,

SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 AS GB_total_IO,

SUM(size_on_disk_bytes)/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files

ON vfs.[file_id] = master_files.[file_id]

AND vfs.database_id = master_files.database_id

INNER JOIN master..sysdatabases sysdb ON vfs.database_id = sysdb.dbid

GROUP BY

sysdb.name, master_files.type_desc,

-- master_files.physical_name,

vfs.sample_ms

ORDER BY SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 desc

[Tuning] Filtered indexes...and filtered statistics.

So, SQL Server (starting with 2008) now has Filtered Indexes. Which are great - since an index is basically just a copy of the data, why copy particular fields for the entire table when you only query particular subsets of data? Add a WHERE clause to your index, and now you're querying subsets of data.


However, while reading up on it I came across this:
http://msdn.microsoft.com/en-us/library/ms190397.aspx
which introduces filtered statistics(!).

Instead of

CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)

which would give you stats for the entire range, do this:

CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3)

which will only compute statistics for that subset of data.

[Files] Tricking Excel with SP_SEND_DBMAIL

My pain = your gain. I needed to automate a process to send a file to an end user. They would just double-click it and have it open in Excel. The problem was due to a field with a leading zero, which Excel will simply lop off. One way around is to create an XML file. I've done that before, but this is simpler and ideal for this situation. The syntax and parameters are important.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'youremailprofile', 
    @recipients = 'dev@null.com', 
    @subject = 'Here is your file pull done',
    @query = 'SELECT a, b, convert(varchar(12),c) as C, char(61) + char(34) + leadingzerossuck + char(34) as leadingzerossuck FROM mytemptable', 
    @attach_query_result_as_file = 1, 
    @query_attachment_filename = 'yourfilename.csv', 
    @query_result_separator = ' ', -- tab
    @exclude_query_output = 1, 
    @append_query_error = 1

Table Partitioning

Working on learning partitioned tables. We've done it, and it works really well for us, but it's time to start getting clever.

Was reading this, which is an excellent primer:
http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

A few rules I learned the hard way. I deliberately did it from scratch in hopes that I'd remember it better - what to do, what not to do. In particular, working on switching tables into partitions (our current stuff switches out, not in)

* clustered key of the table you're switching in has to be on the same filegroup as the partitioned table.
* indexes have to match. ALL of them.
* indexes are NOT on the partition scheme
* only one constraint (below)
* Add a constraint to make sure the partitioned key is within the partition range (order_date >='20100428' and order_date <'20100429' and order_date is not null)
* Then, SPLIT the range (code below), and SWITCH the table in.

    ALTER PARTITION SCHEME ps_daily NEXT USED your_partitioned_fg
    
    ALTER PARTITION FUNCTION pf_daily() SPLIT RANGE ( @Day) --@day is one day larger than current max date

    ALTER TABLE orders_stg SWITCH TO dbo.orders PARTITION 200

Now, what if you need to add an old day in? You have up to 5/4/2010, but need 5/3?

ALTER PARTITION SCHEME [ps_daily] NEXT USED your_partitioned_fg
ALTER PARTITION FUNCTION [pf_daily] () SPLIT RANGE (N'2010-05-03')

[Offtopic] How to print to a networked Laserwriter (XP)

Needed this desperately, so posting it here. Worked like a charm for me. Thanks to this Anthony Duplessis chap, whomever he is. Thanks!

Note that once you get to the test page print the first time, you're NOT DONE, so uncheck it. Follow the directions, step by step.

(found some screenshots at: http://blog.eppesconsulting.com/2009/05/05/AppleLaserWriter16600PSOnWindowsVista.aspx)

Printing to an Apple Laserwriter 16/600 using IP from a Win 2K and Win XP
client.

The following procedure explains how to configure desktop clients,
running Windows 2000 or Windows XP, without installing the appletalk
protocol.

• Select Start/Settings/Printers.
  
• Double-click 'Add Printer' icon. Click the 'Next' button.
  
• Select 'Local printer' bullet.
  
• Uncheck 'Automatically detect and install my Plug and Play printer' box.
  
• Click the 'Next' button.
  
• Select 'Create a new port' bullet.
  
• Select 'Standard TCP/IP Port' from the drop-down arrow list.
  
• Click the 'Next' button. Click the 'Next' button again.
  
• Enter the appropriate IP address of the printer. Click the 'Next' button.
  
• Select 'Apple Network Printer' from the drop-down arrow list in the 'device
  type' field.
  
• Click the 'Next' button. Click the 'Finish' button.
  
• Select the appropriate printer driver. Click the 'Next' button.
  
• Accept or modifiy the suggested printer name. Click the 'Next' button.
  
• Select the 'Yes' bullet if you want to make this printer the default, or
  
• Select the 'No' bullet if you do not want to make this printer the default.
• Click the 'Next' button.
  
• Select the 'Do not share this printer' bullet. Click the 'Next' button.
  
• Select the 'No' bullet so that a test page is not printed.
  
• Click the 'Finish' button.
  
• Open the Printers folder.
  
• Select and Right-click the apple printer. Select 'properties' from the
  context menu.
  
• Select the ‘Ports’ tab and then the ‘Configure Ports’ button.
  
• Verify that the 'LPR' bullet is selected in the Protocol section.
  
• Enter ‘lp’ in the 'Queue Name' field. [Note: that's LP, lowercase, not IP]
  
• Click to select the 'LPR Byte Count Enabled' box.
  
• Click the 'OK' button.
  
• Select the ‘General’ tab and then Click the 'Print Test Page' button.
  

Anthony Duplessis.
LIFE is the toughest teacher of all.
First, you get the test.
Then, you're taught the lesson.

[Powershell] What a piece of $#!+

Wow. So, Powershell is Microsoft's idea of UNIX: a command-line shell. Unfortunately, more than a bit half-assed, even with version 2.0.

I grabbed a module to allow UI scripting. Here's what I have to do in order to run a simple Hello World with it:

1. download Powershell
   
2. download PowerShellPack
   
3. install both
   
4. start Powershell
   
5. Set-ExecutionPolicy Unrestricted (so I can run scripts)
   
6. Import-Module PowerShellPack (needs to be done each time)
   
7. Hit ctrl+c because it's going to ask me once per script, and there are about 50. And, each time I run powershell.
   
8. Set-ExecutionPolicy Bypass (hello, usefulness, goodbye protection. Grrrr)
   
9. Import-Module PowerShellPack
   
10. Import-Module WPK
    
11. New-Label "Hello, World" -Show
    
12. get odd error: Exception calling ".ctor" with "0" argument(s): "The calling thread must be STA, because many UI components require this."
    
13. read blog
    
14. change shortcut to Powershell to add -STA
    
15. restart Powershell
    
16. Import-Module PowerShellPack
    
17. Import-Module WPK
    
18. New-Label "Hello, World" -Show
    

Success!

WTF. Am I a bad person if I wonder why the hell I have to jump through all these hoops?