Programmatically picking apart foreign-key constraints

Another quickie today. Don't ask why, but this morning I was trying to programmatically work out which tables have foreign-key references to table Sales, because they're preventing fast partition switching on table Sales. After more spelunking around I thought I had it with this code: SELECT * FROM sys.all_objects WHERE [type] = 'F' AND [parent_object_id] […]

How to change a default constraint

While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() – function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll […]

Conference Questions Pot-Pourri #8: How to move constraint indexes?

It's been a long time since the last Conference Questions Pot-Pourri – in fact it was at the last SQL Connections in Orlando in April. Now we're in Las Vegas doing SQL Connections Fall – Kimberly's lecturing for an hour on partitioning so I can get out a quick post. This is a question that […]

Search Engine Q&A #9: How to update constraints?

A quickie today to get back into the swing of things. In Kimberly's whitepaper on partitioning she discusses the 'sliding window' scenario (where you switch in and out partitions of data into an existing table – see this previous post for a few more details). She recommends that the constraints are extended rather than dropped […]