I've seen several posts today after an ex-colleague mentioned on their blog it not being easy to get the Management Studio for SQL 2008 Express once you've installed the engine only option of SQL Express. There is a method of upgrade that Dan details to change the SQL 2008 Express version, but you can also download the Management Studio from Microsoft directly without upgrading your instance.  You will need Windows Installer Version 4.5 though :) http://www.microsoft.com/downloads/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en

A friend working at Graphico of my recently sent me some SQL that purposed to look for missing indexes in you databases.  It pretty much sat in my inbox for a while until I had time to look at it (which I have just done). It was a great little script and proved very useful in identifying performance improving indexes that we could include in the database that we're developing.  I asked my friend where the SQL was from and he pointed me to the article in question on the MSDN site, which you can find here: http://msdn.microsoft.com/en-us/magazine/cc135978.aspx It really is worth a read ... [More]

I found these two great articles on MSDN about performance and scaling of ASP.Net applications.  They're well worth a read in my opinion. 10 Tips for Writing High-Performance Web Applications Scaling Strategies for ASP.NET Applications

Overview Here's another product suggestion I've logged with Microsoft along the same lines as the previous one.  This one is to allow you to dynamically specify the list of columns you want to unpivot.  This might be from a stored procedure parameter or a subquery, either way you can't do it easily at the moment.  Register your support here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332512 Detail Current behaviour UNPIVOT (value FOR column_name IN (Col1, Col2, Col3, Col4)) All columns must be known in advance and specified in full. Suggested beh... [More]

Overview I've logged a suggestion with Microsoft for support for the inclusion of NULLs when you use the UNPIVOT operator.  Add your support if you think this would be useful: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332325 Example Source data ID Col1 Col2 Col3 Col4 1 Value NULL Value NULL Current behaviour UNPIVOT (value FOR column_name IN (Col1, Col2, Col3, Col4)) ... [More]

Overview Auditing the actual changes made to a table can be quite complex, but is often required.  In SQL Server 2008, you can use the new feature Change Data Capture, but in 2005 you have fewer options. Here's an approach using common table expressions, table variables and a nice, big and hopefully fast query! Context click here to view this online and see the code formatted for viewing Suppose you have a data table and an audit table as follows: 1: CREATE TABLE tblToAudit ( 2: pkid int identity(1,1) primary key, 3: col0 nvarchar(20), 4: col1 nvarchar(20),... [More]

Overview I've been looking at the performance of SQL Server 2008, following the recent SQL Bits conference (see http://www.sqlbits.com), and have been looking at creating some test data for my analysis.  This is probably old stuff to most of you, but I hit an interesting issue... Context Specifically, I've been looking at the performance of the new filtered indexes support in 2008.  If you haven't come across these, I recommend that you have a look but I'll probably post about it in the next few days (no promises, though!).  To do the analysis I wanted a table with heavily weighted data so ... [More]

Overview Have you ever wondered how to view object permissions using a query? Simple example Try this on a database: select o.name, u.name, p.permission_name, p.state, p.state_desc   from sys.database_permissions p        inner join sys.all_objects o          on p.major_id = o.object_id        inner join sys.database_principals u          on p.grantee_principal_id = u.principal_id References sys.database_permissions (Microsoft TechNet) sys.database_principals (Microsoft TechNet) sys.all_objects (Microsoft TechNet) Versions Microsoft SQL Server 2005... [More]

Overview Do you have lots of tables that reference each other using ID fields?  Are they of different types, such as nvarchar(10) and int?  Do you sometimes find that it can be hard to remember how many characters should be in the type?  And what about when you decide that you need a bigger type, such as nvarchar(30)?  How can you ensure that your database design remains consistent? Alias Data Types and the CREATE TYPE statement An Alias Data Type is a type defined by you in SQL Server that refers to an internal data type.  You can use it anywhere you would use a normal SQL type, except in ... [More]

Overview I'm in the process of reviewing a large number of SQL stored procedures, and occasionally I find that a table join isn't properly expressed.  This can result in hard to find bugs, but how can you make your SQL code simpler?  Isn't using a stored procedure best-practice anyway? Scenario It is quite common to have a series of related tables.  Consider a stock control system.  Perhaps you have tables for customers, orders, order lines and stock items.  In this simple scenario, you have four tables and clearly there are joins between several of the tables. Common solution A common so... [More]

Overview I've just found this by chance while in the process of reviewing around 100 stored procedures to check for performance efficiency.  It doesn't help with badly written queries, but if the query is reasonable then it can help you find those elusive missing indices.  Thought I'd have to find them myself, but thankfully help is at hand... Details Normally, you would use the SQL Server Profiler to help track performance issues, but what if you are not in a live environment?  SQL Server 2005 helpfully collects some data that you can use. Warning:  the data is only kept for while the ser... [More]

Overview You may know that it is best practice to create FOREIGN KEY constraints on related tables in a database.  Why is this? Details A foreign key constraint can be used for: Ensuring referential integrity: a foreign key constraint can ensure database consistency Self-documenting databases: the foreign key clause can be viewed in the database and describes a relationship Efficiency: even without an index, the SQL Server engine recognises the constraint and uses it to optimise table joins automatically Generally, you will also want to index the foreign key columns i... [More]

Overview Following on from my previous post, here's a quick way to find out which data tables are using the most disk space... References Table and Index Organization (Microsoft SQL Server 2005 Books Online (September 2007)) Solution SELECT o.name AS table_name, SUM(au.data_pages) as pages,        ((SUM(au.data_pages) * 8192) / (1024 * 1024)) AS MB FROM sys.allocation_units AS au      JOIN sys.partitions AS p ON au.container_id = p.partition_id      JOIN sys.tables AS o ON p.object_id = o.object_id      JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = ... [More]

Overview I'm in the process of reviewing a database for performance analysis.  One of the most important tasks to perform is reviewing of statistics.  Because this database is currently live, I cannot get the locks to use the sys.dm_db_index_physical_stats built-in function.  I have an Oracle background and didn't know how to get the row count quickly.  Here's my solution... References http://www.mssqlcity.com/Articles/KnowHow/RowCount.htm Solution I use the following code to get the total number of rows for all tables in the current database: SELECT sysobjects.[name], max(sysin... [More]

Overview How can you make your Commerce Server reports easy to maintain, and how can you make it work with long SQL queries?  Did you even know that long SQL queries won't always work?! The problem The query is stored in the Report table in SQL Server as an nvarchar(2000) field.  This means you've only got 2000 characters to work with.  In fact, the limit is smaller than this because of processing that Commerce Server is doing itself.  I noticed problems around 900 characters, which seems a bit small! The solution The best solution I found was to reference a SQL view that was created for ... [More]

Overview What is a malicious trigger?  What can it do?  How can you protect yourself and your database server? Background I've been reading the Microsoft Press MCITP training book for the Microsoft 70-441 exam: Designing Database Solutions by Using Microsoft SQL Server 2005 and I came across an interesting comment about trigger security.  I'm describing it here, because it is quite easy for a malicious database developer to use against an unsuspecting DBA. You can find the book here: http://www.amazon.com/MCITP-Self-Paced-Training-Exam-70-441/dp/0735623422/ref=sr_1_1?ie=UTF8&s=books&... [More]

Overview Sometimes you need to use a temporary table within a SQL Server stored procedure.  How can you make sure that this will work? Process The first thing you have to decide is the scope of the temporary table.  Local temporary tables (preceded by a single # character) are scoped to the session.  Global temporary tables are available to all sessions.  This can be important as it means that removal of a global temporary table is not a safe option.  Because a local temporary table is specific to the creating session, it can be removed at will. The next thing you should test is whether th... [More]

Overview It is the responsibility of a stored procedure to manage the transaction context of the session.  An error can result when the transaction depth on entry and exit from a stored procedure do not match.  This may occur when a transaction is rolled back in a stored procedure due to an error. There are two ways to resolve this.  The first is to rely on the calling session to manage transactions appropriately and the stored procedure does not issue any commit or rollback statements.  This also means that the stored procedure should not be used outside of a transaction if the whole stor... [More]

When you have a fairly active DB, your going to get a pretty large log file unless you have a suitable maintenance plan.   We've had a few problems before where large log files have taken up all of the available disc space, causing application problems when acessing the DB.   If you don't need the logs, then this will tell you how to shrink them.  But you must set up maintenance plans to look after you databases and conserve resources (it will also make your DBs a little more efficient).   If you don't know how to set up a maintenance plan, read this article.  If you still don't know aft... [More]

Heh, a slight addition to my previous post here.  Kinda feeling sheepish now. My code worked frin for about 8 hours or su, until randomly, my client in question decided to switch date formats.  I can't really figure out why, but intermittantly, the thing calling my code will send 4/20/2007 or 20/4/2005.  This kinda sucks so I have to have another scurry around on google to see what I could find.  Basically, this article  by Amol Kulkarni here helped me out alot. You have to be careful with XACT_ABORT I think, again - I'm not SQL guru and I'll need to read up on this.  It seems fairly sim... [More]