View vs. Table Valued Function vs. Multi-Statement Table Valued Function

T-SQL
About five years ago, I was checking an app before it went to production. I hadn't seen the app before then and a junior dba had worked with the developers designing and building the app. It didn't use a single stored procedure or view. Instead, it was built entirely of multi-statement UDF's. These UDF's called other UDF's which joined to UDF's... It was actually a very beautiful design in terms of using the functions more or less like objects within the database. Amazing. It also would not, and could not, perform enough to function, let alone scale. It was a horror because they thought they were done and ready to go to production, but no one had ever tested more than a couple of rows of data in any of…
Read More

Did I mention that I love Red Gate’s Data Generator?

T-SQL, Tools
Because I do. I'm working on a set of tests for an article comparing TOP, MAX & ROW_NUMBER. I have a simple data structure and I need a bunch of data in order to create my tests. I wanted that data to be distributed a certain way, to mimic some production system behavior I've seen in the past. Last night I got it all set by mucking about with the seed values of the various columns to get it just right and load up millions of rows in only a few minutes and doing this all on my lap top. Great tool!
Read More

SQL Data Generator

Tools
I just received word from Rachel Hawley over at RedGate that SQL Data Generator has been released. I've been using the beta over the last few months and I've found it incredibly useful for setting up tests and playing around with my database designs, seeing how different data loads will be distributed through the indexes, etc. It's quick to use but fairly powerful and flexible and, frankly, pretty inexpensive. I strongly recommend it.
Read More

Top vs. Max

T-SQL
The company I work for has a very well defined need for versioned data. In a lot of instances, we don't do updates, we do inserts. That means that you have to have mechanisms for storing the data that enables you to pull out the latest version of all the data or a particular version of all the data, or the data at a particular moment in time, regardless of version.  That means maintaining a version table and a series of inserts into various tables. Some tables will have pretty much a new row for each version, some tables may only have one or two versions out of a chain. With the help of a very smart Microsoft consultant, Bill Sulcius, we have a mechanism that works very well. However,…
Read More