How to inner join an inline table valued function in SQL
When faced with the task of creating an inline table valued function in T-SQL, sometimes you'll want to inner join this function with other tables. It seems like it should be straight forward to do, but it really isn't. There's a variety of workarounds, including options such as creating a view that references the new user defined table valued function. If performance isn't a tremendous concern to your application, the "cross apply" method is probably the most straight forward. Keep in mind that this method was introduced as of SQL Server 2005. So if you're using SQL 2000 or an earlier version, you're out of luck. Honestly, I've found that the performance hit with using cross apply is greatly exaggerated. Here's how it would work.
Update a Limited Amount of SQL Datarecords
I'd consider myself a fairly advanced SQL programmer. I can do most anything that I ever need to do through SQL, though I still learn something new almost every day. I hope that I always will learn new ways to write efficient queries and manipulate databases. I'm not embarrassed to admit that I just finally figured out how to update a limited amount of records within an update query. It's actually a really easy process, with a slight nuance that I'll get into.
Read more
T-SQL - Search all tables for field name
A while back, I went over a method that could be used, via query, to find all SQL stored procedures containing a specific string. When working with large databases with thousands of objects (including stored procedures), that has come in handy. Another situation that comes up from time to time is the need to find all tables which contain a specific field name. If you've been consistent with your naming conventions, and have numerous links (possibly foreign keys) between tables, this can be accomplished fairly easy. Just execute the following query (verified to be working in MSSQL 2008, MSSQL 2005, and MSSQL 2000).
SELECT *
FROM information_schema.columns
WHERE column_name = '[FIELD NAME]'
ORDER BY TABLE_NAME
Where [FIELD NAME] is the name of the field that you're actually looking for. This will provide you a list of tables in your database which have the field that you're looking for. Easy enough.
Setting SqlDataSource command timeout for ASP.NET
Here's a quick tip in regards to changing the command timeout for a SqlDataSource object in ASP.NET. Sometimes you'll want to increase the command timeout of a SqlDataSource object from 30 seconds (its default value) to something greater for resource intensive queries.
Read more
Find modified date for all SQL stored procedures in a database
When working with SQL, sometimes you need to know information such as "when did this stored procedure or function change?" Maybe someone that you work with fubared a previously flawless procedure. Maybe you had a brain fart and left an ambiguous field name in your query and just want to know when you made that change. Hey - its happened to all of us. Keep in mind, there ARE source control programs for SQL. I know red-gate has one. There are a few other version control programs from companies like Skilled Software, SQLDBControl, etc. Many of us (probably most of us), however, don't use these programs. So how can we find when a stored procedure has been modified? We'll take it to a query like the following.
Read more
Using a table variable in your SQL stored procedure
There comes a time in any programmer's life, when writing SQL code beyond a beginner level, that a temporary table is needed. Be it to manage complex update processes before committing to any transaction, or whatever the case may be, I'm going to discuss using a table variable as an efficient and versatile alternative to using a temporary table.
Why do we not like using temporary tables? Well, for one, they require some type of garbage collection cleanup. Nobody likes having a bunch of useless orphaned temporary tables hanging around their well structured SQL database, right? Also, a temporary table takes up SQL server space and just isn’t quite as efficient as other SQL friendly solutions.
Using SQL 2008, a table variable is easy to manage. Just declare it as you would any other type of SQL variable.
DECLARE @table1 TABLE (
CustID uniqueidentifier,
CustType varchar(50),
NewName varchar(50)
)
Now that you’ve got a SQL table variable declared, we’ll want to insert datarows into it – right? Let’s write an insert command within our stored procedure.
INSERT INTO @table1
SELECT [CustID], [CustType], ‘’
FROM Customer
Now you’ve got your SQL table variable populated with some data. In this example, we’ll now run a series of SQL update queries against this temporary table. You would treat it as you would any other SQL table – whether it be a variable or not.
UPDATE @table1
SET NewName = ‘East’
WHERE CustType = ‘1’
UPDATE @table1
SET NewName = ‘West’
WHERE CustType = ‘2’
You can even run joins such as INNER JOIN, LEFT JOIN, RIGHT OUTER JOIN, etc. against this table variable with some of your established tables. You’ll just need to provide a table name alias (in this case T1).
SELECT *
FROM Customer
INNER JOIN @table1 AS T1 ON T1.NewName = Customer.Name
Using CASE statements in T-SQL
One of the more useful and flexible expressions in T-SQL is the CASE statement. Have you ever been asked to write a report that returns data in an unnatural form, or with slight alterations to the stored fields? Using a SELECT CASE can usually fit your needs without altering database table structure. Lets go through a couple of examples.
Customer A wants a report of all of their vendors and total dollar sums for each type of item purchased. You have a table in your database listing all orders which have fields for the vendor, the type of order, and the billing amount. Writing a query like the following will return the requested data.
SELECT Vendor, SUM(Type_A$), SUM(Type_B$)
FROM (SELECT Vendor, CASE WHEN Type = 'A' THEN Billing$ ELSE 0.0 END AS TypeA_$, CASE WHEN Type = 'B' THEN Billing$ ELSE 0.0 END AS TypeB_$ FROM Orders) AS VMatrix
GROUP BY Vendor
Customer B wants a report that determines and displays a category for orders made (category being a field type that is not stored in the database).
SELECT Vendor, Order,
CASE
WHEN Billing$ < 10.0 THEN 'Cheap' WHEN Billing$ >= 10.0 AND Billing$ < 100.0 THEN 'Average'
ELSE 'Expensive'
END AS BillingCategory
FROM Orders
Search T-SQL stored procedures containing text string.
I've written thousands of stored procedures that I barely remember. So, as you can imagine, sometimes I'd like to search an entire database for a specific text string. Here's a simple SQL query that will accomplish this:
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%searchtext%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
I've used this with success in management studio for SQL 2008, SQL 2005, and through query analyzer in SQL 2000.
ISNULL vs. COALESE in T-SQL
As many SQL developers know, COALESCE and ISNULL can be used to achieve the same results. They both essentially check if a value is NULL and return something else if it is. Both functions are widely used from earlier versions of SQL to SQL Sever 2008 development. For anyone needing to brush up on their syntax, here are their MSDN definitions.
COALESCE
ISNULL
Some of the main advantages of using COALESCE over ISNULL are
On the flip-side, some of the advantages of using ISNULL are
Fixing the SQL 2000 MMC snap-in error
Earlier today, I was experiencing a SQL 2000 management console error. Unfortunately I have to maintain a SQL Server 2000 database in addition to developing a SQL Server 2008 database. The error stated "MMC could not create the snap-in". This prevented me from viewing merge and transactional replication status for my SQL 2000 servers. This error can be resolved by installing Microsoft's SQL 2005 backwards compatibility msi package. This sounds ridiculous, as any SQL 2005 package should have no effect on a SQL 2000 installation - but it works. This package can be found here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en