http://www.bidn.com//Assets/Uploaded-CMS-Files/2180394a-380c-419f-a0a6-2570eb8c76beAlteringForeignKeys_1_PrepTables.zip http://www.bidn.com//Assets/Uploaded-CMS-Files/631a6a0e-8cf1-494e-b609-094c7c08e9deAlteringForeignKeys_2_CreateAlter.zip This is going to be a very rough post, because it's friday night, and I'm almost done for the day :-) A little bit of background: I have a staging environment used for a project built on MS Sync Framework, and the tables currently have Foreign Keys in exactly...
Read More
Hmmmm. Just a quick anecdote about SQL's backup compression. I backed up a DB, tiny one, no compression, resulted in a size of 5636 KB. I was mailing it out, so I quickly 7Zipped it, and got a size of 434KB. Pretty impressive ratio actually, but I've always liked 7Zips native format. Later on, I made a foreign key reference change, and one stored proc change. MINOR changes. Backed up the DB again, but with backup compression. Results in a size of 909KB. Nice improvement on 5636, but nowhere near...
Well, this isn't going to be a long blog post. One of the tasks facing many of us is how to clear tables with foreign keys : we want to truncate those without any foreign keys referencing them, and delete the rest in the correct. Here's a script I've used as a basis to do this just that. Not 100% sure it's perfect yet, if I find issues I'll post them here. You pass the SP a schema, and it gives you a SQL statement to run. Some of the @temp table stuff could have been done with CTEs, but I saved ...
A preface: this wont' always replace partitioning. But in those in between cases, where performance needs a small boost, filtered indexes may well be enough of an advantage to NOT need to partition So here's an example CREATE NONCLUSTERED INDEX PartitionIndex On dbo.TransactionTable (PartitionDate, TrxDate) Where(PartitionDate is null ) update dbo.TransactionTable SET PartitionDate = GETDATE() WHERE TrxDate < DateAdd(M, -1, Getdate()) This will automatically update the indexes, you do a rebui...
For a quick divergence today, I've decided to, as part of the non-technical aspects of this blog, delve a little bit into who th different vendors and partners are in South Africa First off, a disclaimer: I work for a company called Intervate at the moment, and they are a player in the BI space. As I'm from the BI side, I'm going to comment only on their sharepoint capabilities (i.e. things I don't do personally) There are a few vendors in the space, Microsoft, Microstrategy, IBM/Cognos, SAP/Bus...
So here's the scenario: Your cube is up, it's in production, it's been running succesfully. And today it fails. You investigate, manually reprocess the cube, and get the enormously helpful screen below: Well, from here, we need to identify why we now have a duplicate attribute key : do yourself a favor, and just look at the warning. The errors above can be vaguely misleading……. Next step then is to see what Mine Name is related to : So looking at this, we can immediately see that Mine Code is re...
Today I needed to script out portions of a database - to be more precise, only the objects in a single schema. For tables and stored procedures, this is easy : simply press F7, Object Explorer Details, and select the objects you need. For indexes, this is not quite as simple. I started off by searching the net, and found a basic script to do this : http://www.sqlservercentral.com/scripts/Miscellaneous/31893/ This script does create all the indexes, but it does not filter by schema, and it also d...
MY first blog post on BIDN :-) Well, let me start off with a brief introduction to the problem : My client, a large coal mining concern, has a warehouse concerned engineering availabilities - i.e. what the machines are doing at any given point. The fact table, FactAvailabilityHours is related to various dimensions (time et al), and has a measure called Duration - in hours oddly enough. One of those dimensions is a structure representing what a machine was doing - this Dimension is called DimAvai...