And years ago, I guess that was fine.
But Server Management Objects (SMO) are phenomenal. They just work. With a few lines in C#, you can create or kill a database, install schema scripts, enumerate the database, and much more. You also get exceptions thrown if there is an error (much easier than parsing the osql output from the command line).
Good stuff.
One of Steve McConnell's tips from his great book on estimating (Software Estimation: Demystifying the Black Art) is that you should not estimate that which you can easily count. Estimating database table sizes is a great example of this. Sure, on one hand disk space is relatively cheap; on the other hand you want to know at least a ballpark estimate of how much space your app will need - will database size explode and no longer fit on your existing SAN?
Here's a general strategy to estimate table size:
1. Determine the general schema for the table
Note the column datatypes that could be huge (like varchar(2000) for notes, or xml, or blob)
2. Find out how many rows you expect the table to contain
Is the table extending an existing table, and therefore proportional to it? For example, do you have an existing "Employee" table with 100,000 records, and you're creating a new "Employee_Reviews" table where each employee has a 2-3 reviews (and hence you're expecting 200,000 - 300,000 records)? If the table is completely new, then perhaps you can guess the rowcount based on expectations from the business sponsors.
If the table has only a few rows (perhaps less than 10,000 - but this depends), the size is probably negligible, and you don't need to worry about it.
3. Write a SQL script that creates and populates the table.
You can easily write a SQL script to create a new table (and add its appropriate indexes), and then use a WHILE loop to insert 100,000 rows. This can be done on a local instance of SQL Server. Note that you're not inserting the total number of rows you estimated - i.e. if you estimated that table will contain 10M rows, you don't need to insert 10M rows - rather you'll want a "unit size", which you can then multiple by however many rows you expect. (Indeed, you don't want to wait for 10M rows to be inserted, and your test machine may not even have enough space for that much test data).
For variable data (like strings), use average sized data. For null columns, populate them based on how likely you think they're be used, but err on the side of more space.
Obviously, save your script for later.
4. Run SP_SPACEUSED
SP_SpaceUsed displays how much data a table is using. It shows results for both the data, as well as the indexes (never forget the index space).
You can run it as simply as:
exec SP_SPACEUSED 'TableTest1'
Now you can get a unit-size per row. For example, if the table has 3000KB for data, and 1500KB for indexes, and you inserted a 100K rows, then the average size per row is: (3000KB + 1500KB) / 100,000. Then, multiple that by however many rows you expect.
This may seem like a lot of work, and there are certainly ways to theoretically predict it by plugging into a formula. My concern is that it's too easy for devs to miscalculate the formula (like forgetting the indexes, not accounting the initial table schema itself, or just all the extra steps)
5. Estimate the expected growth
Knowing the initial size is great, but you also must be prepared for growth. We can make educated guesses based on the driving factors of the table size (maybe new customers, a vendor data feed, or user activity), and we can then estimate the size based on historical data or the business's expectations. For example, if the table is based on new customers, and the sales team expects 10% growth, then prepare for 10% growth. Of if the table is based on a vendor data feed, and historically the feed has 13% new records every year, then prepare for 13% growth.
Depending on your company's SAN and DBA strategy, be prepared to have your initial estimate at least include enough space for the first year of growth.
6. Add a safety factor
There will be new columns, new lookup and helper tables, a burst of additional rows, maybe an extra index - something that increases the size. So, always add a safety factor.
7. Prepare for an archival strategy
Some data sources (such as verbose log records) are prone to become huge. Therefore, always have a plan for archival - even if it's that you can't archive (such as it's a transactional table and the business requires regular transactions on historical data). However, sometimes you get lucky; perhaps the business requirements say that based on the type of data, you only legally need to carry 4 years worth of data. Or, perhaps after the first 2 years, the data can be archived in a data warehouse, and then you don't worry about it anymore (this just passes the problem to someone else).
Summary
Here's a sample T-SQL script to create the table and index, insert data, and then call SP_SpaceUsed:
USE [MyTest]
GO
if exists (select 1 from sys.indexes where [name] = 'IX_TableTest1')
drop index TableTest1.IX_TableTest1
if exists (select 1 from sys.tables where [name] = 'TableTest1')
drop table TableTest1
--=========================================
--Custom SQL table
CREATE TABLE [dbo].[TableTest1](
[SomeId] [int] IDENTITY(100000,1) NOT NULL,
[phone] [bigint] NOT NULL,
[SomeDate] [datetime] NOT NULL,
[LastModDate] [datetime] NOT NULL
) ON [PRIMARY]
--Index
CREATE UNIQUE NONCLUSTERED INDEX [IX_TableTest1] ON [TableTest1]
(
[SomeId] ASC,
[phone] ASC
) ON [PRIMARY]
--=========================================
--do inserts
declare @max_rows int
select @max_rows = 1000
declare @i as int
select @i = 1
WHILE (@i <= @max_rows)
BEGIN
--=============
--Custom SQL Insert (note: use identity value for uniqueness)
insert into TableTest1 (phone, SomeDate, LastModDate)
select 6301112222, getDate(), getDate()
--=============
select @i = @i + 1
END
--Get sizes
exec SP_SPACEUSED 'TableTest1'
I have a strong bias against injecting business logic into the stored procedures. It is not scalable, is hard to reuse, is a pain to test, has limited access to your class libraries, etc... However, I think there are some legit cases to put advanced T-SQL in a SP, but you're playing with fire.
Here are some reasons - basically either performance, functionality, or legacy constraints force you to.
Note that for any of these reasons - consider at least still testing your database logic, and refactoring your SQL scripts.
These "reasons" are probably a bad idea:
In my personal experience, I see more data in the SP for the bad reasons ("it's real quick!")- the majority of the time it can be refactored out, to the benefit of the application and its development schedule.
Everyone who maintains code knows that duplicated code is bad. While OOP languages like C# (and even xml "languages" like MSBuild) provide ways to refactor, SQL seems lacking in such features. If you run a similarity-analyzer (like Simian), you can probably see large amounts of duplicated code. Two common refactoring techniques to help with this would be:
Note that mere stored procs or user-defined-functions are insufficient, as they can’t handle all snippets (like declaring variables which are used in the calling function), or they have awful performance in the where clause.
We can use a technology “SqlCmds” to accomplish this. (http://msdn.microsoft.com/en-us/library/aa833281.aspx).
How to enable Sql Commands in SqlStudio:
http://msdn.microsoft.com/en-us/library/ms174187.aspx
How to enable Sql Commands in Visual Studio
This requires the database edition of Visual Studio. Click the allow "SqlCmd" button on the tool bar.
Basic variable test
--set variable, and then use it - use the ":setvar" command
:setvar SomeTable TestCodeGen
select * from $(SomeTable)
-- environmental variables too!
select '$(COMPUTERNAME)' --returns my comp name (like 'TimStall2')
This means we could have an external script set the environmental variables (like the PrimaryDataBase), and then easily re-run those in the SQL Editor. Note that you can use the free tool SetX.exe to set environmental variables.
File Include – Basic (use the “:r” command)
--File 1 (var_def.inc):
:setvar PrimaryDB MyDatabase70
--File 2:
:r C:\Development\var_def.inc
select * from $(PrimaryDB).MySchema.TestCodeGen
For example, we could have a “header” file that includes a bunch of variable definitions (like all the PrimaryDB, ReportDB, etc…), and then include it wherever needed. Or, we could include any other SQL snippet. For example, we could use this to effectively make private functions (instead of only have global functions) that are encapsulated to a single stored proc.
File Include – avoid function in a where clause
--File 1 (myProc_func1.sql):
--some reusable snippet (note how is uses the variable '@myNum")
co = '1234' or NumberInteger > @myNum
--File 2:
declare @myNum integer
select @myNum = 10
select * from TestCodeGen
where
:r C:\Development\myProc_func1.sql
and LastChangedBy < GetDate()
Summary
One catch to all of this is that if you have your own database installation code via ADO.Net, you need to manually parse it yourself. However, that should be easy enough to do given the strict syntax of the SqlCmds.
Note that this is substituted “at compile time”. If you run the SQL Profiler, you won’t see the “:setvar” or “:r”, but rather the content already parsed. These techniques could be used to help refactor SQL code, just like similar techniques help refactor the code in other languages.
SQL temp tables can be a functional and performance life-saver because they let you store intermediate results. You can then do resource-heavy calculations on this smaller result set, or just break a complex procedure into multiple, easier-to-handle parts. Sometimes you'll have two temp tables with the same schema. SQL provides as easy way to clone a temp table (thanks to our Paylocity SQL architect for showing me this). Here's a sample:
CREATE TABLE #Main1 --prePaging
(
colInt int,
colString varchar(20)
)
Insert Into #Main1 Values(10, 'aaa')
Insert Into #Main1 Values(20, 'bbb')
Insert Into #Main1 Values(30, 'ccc')
--Dynamically create a close of #Main1, call it #Main2
--This doesn't touch data
Select * Into #Main2 From #Main1 Where 1=2
--show that Main2 exists, and has the right schema
Select * From #Main1
Select * From #Main2
--Now insert some data into Main2
Insert Into #Main2 (colInt, colString) Values(40, 'ddd')
Select * From #Main2
drop table #Main1
drop table #Main2
The key is the line "Select * Into #Main2 From #Main1 Where 1=2", which dynamically creates table Main2 and sets its columns equal to that of Main1.
Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.
Say you want a table to have 10,000 rows so you can do some SQL performance tuning. Ideally you could specify some algorithm to dictate the number of rows and how each row of data is unique. For example, you may want to say "Insert 10,000 rows of company data, using different company names of the form 'co' + i.ToString(), such as co1, co2, co3, etc...".
You can easily do this. First you could use the MDH to insert the parent data. Then for specific high-volume tables, you could use the SQL while loop to specify the insert strategy, like so:
Declare @i int select @i = 1 WHILE (@i <= 10000) BEGIN --Define the dynamic data to insert Declare @co varchar(10) select @co = 'co' + cast(@i as varchar(4)) --Do the SQL insert Insert into MyTable ( [co], [SomeColumn] ) Values( 1, @co, 'someData' ); --increment the counter select @i = (@i + 1) END
The would quickly insert 10,000 rows of test data into MyTable. You could customize the technique for other tables, adding multiple inserts in the loop, or adjusting for a multi-column unique index.
Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.
Performance is critical, and slow SQL procs are often a huge performance bottleneck. The problem is how to measure it. Microsoft provides SQL Profiler to help with that.
While I'm certainly no DBA, here's a basic tutorial on how to tune a SQL script (check here for more on SQL profiler).
Because performance can very exponentially (i.e. there may be twice as much data, but it goes twenty times slower), you absolutely need to test with production-sized data, else all your measurements could be off.
We don't want to chase ghosts, so make sure you have a deterministic environment: (A) no one else is changing the script-under-test, (B) you can call the script with a single SQL command (like exec for a SP, or select for a function), (B) you can call the script repeatedly and get the same functional result every time. Once the script works, we can make it work fast.
SQL profiler lets you measure how fast each SQL command took. This is invaluable if you have a complicated script with many sub-commands. It's almost like stepping through the debugger where you can evaluate line-by-line.
Simply run your SQL statements in SQL studio, and check the results in SQL profiler.
The tuning template in profiler will record every command and sub-command being run. It will show the total duration (in milliseconds) for each line, and the full SQL text of what was run. This allows you to identify the bottlenecks, and tune those by changing the SQL code to something more optimal.
If you don't have an exhaustive suite of database tests, you can still help ensure that your proc is functionally equivalent by comparing the original SQL output (before tuning) to the new SQL output (after tuning). For example, you could save the output resultset as a file and then use a file-diff tool like Beyond Compare to ensure they're identical.
Again, books could be written on how to SQL tune. This is just a brief high-level tutorial to get you started.
Living in Chicago and interested in a great company? Check out the careers at Paylocity.
if @@error != 0
begin
RAISERROR 50001 'Some error message'
RETURN
end
This has obvious problems (lots of extra code, what if you miss an error message, etc...). However, SQL Server 2005 has try-catch statements!
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
{ sql_statement | statement_block }
END CATCH
[ ; ]
This is definitely a convenient thing.
