Chris Miller Blog

RocketScientist's Miscellaneous Ramblings

Actually useful BI

Kidding. Mostly. Here's another BI-type analysis tool that's probably used by more people than any other BI tool in the world. It's cloud based, it provides incredibly detailed and useful information for performance analysis and identifying areas of improvement. Read more →

IsNumeric('.')

So, anybody know why if you do this: print IsNumeric('.') it prints out this: 1 Seems that it won't convert to a float, it won't convert to an int, but it will throw lots of great errors…. Read more →

Tables, and the space they use

So I hacked around in sp_spaceused and got this sorted out. Works in SQL 2005, haven't tested north or south of that.  Share and enjoy. drop table #mainpartitionstats drop table #LOBPartitionStats drop table #totals  SELECT  object_name(object_id) name,  SUM (reserved_page_count) reserved_page_count,  SUM (used_page_count) used_page_count,  SUM (  CASE  WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  ELSE lob_used_page_count + row_overflow_used_page_count  END  ) Pages,  SUM (  CASE  WHEN (index_id < 2) THEN row_count  ELSE 0  END  ) RC  into #MainPartitionStats  FROM sys. Read more →

COM+, DTC, and 80070422

One of our "packaged" software bits that accesses my servers is going through an upgrade right now. Apparently this software requires DTC to be installed on my SQL Server, and able to accept remote connections. Read more →

Star Trek Online Open Beta, Thumbnail Review

I figure there's got to be some crossover between the SQL Server folks and the MMO folks, so here ya go. I downloaded and played the new Star Trek: Online open beta this week, just to see if it was going to be worthwhile. Read more →

Left-duplicate indexes

At Kim Tripp's PASS session on indexing and covered indexes, she more-or-less proved that wider is better for indexes, within reason and searchability. It's better to have the index (Column1, Column2, Column3) than the index (Column1) and then a (Column1, Column2) and then a (Column1, Column2, Column3). Read more →

Finding tables with no unique indexes

Same vendor, different issue. How many tables in the database aren't really tables, but are actually just 2 dimensional data stores of unknown quality? Tables should have a unique index. Of some kind. Read more →

Finding duplicated indexes

So I wanted to start being a bit more aggressive about index management.Specifically, I want to be able to find duplicated indexes inside of one of my vendor databases.So (1) I can make fun of them even more than I do already, and (2) so I can give them a script of all their issues that they might be able to resolve them. Read more →

How to specify a Windows Authentication user in T-SQL

This may only be a SQL Server 2005 problem. Comments either way please. Only one of these works: grant execute on stored_proc to [domain1\ismom] grant execute on stored_proc to 'domain1\ismom' grant execute on stored_proc to '[domain1\ismom]' Only one of these Execute As User works: execute as user = 'domain1\ismom' exec master. Read more →

Someone might not understand how CASE works….

case when StatusFlag = 'LWCA' and TransactionType <> 'W' then 'Layw Cancel' ELSE case when StatusFlag = 'LWCA' and TransactionType = 'W' then 'Warranty Cancel' ELSE case when StatusFlag = 'WAPK' then 'Warranty Pickup' ELSE case when StatusFlag = 'LWSE' and TransactionType <> 'W' then 'Layaway' ELSE case when StatusFlag = 'LWSE' and TransactionType = 'W' then 'Warranty Setup' ELSE case when StatusFlag = 'SALE' and isnull(HV. Read more →

Aggregate Functions

I've run into this before, I ran into it today, and I'll probably run into it again. Why isn't there an aggregate function that concatenates strings? Besides the obvious "string don't get that big" (which I'd manage by manipulating my group by to fit) why not? Read more →

Hyperbac Followup

This is, specifically, a followup to http://weblogs.sqlteam.com/chrism/archive/2008/11/25/Hyperbac-anyone.aspx Been using the software for 2 months, and it works very well. I have one issue on one server (a development box) that their support team is aware of and has been very helpful with tracking down. Read more →

My, isn't THAT intuitive.

I still have a SQL Server 2000 server. Don't blame me, blame my ISV. The upgrade to their software to support SQL Server 2005 is coming out in August. Of 2006. Read more →