SQL
There are 17 entries for the tag
SQL
One thing I’ve found with various open source projects is that many of them contain very useful code nuggets that could be generally useful to developers writing different kinds of apps. Unfortunately, in many cases, these nuggets are hidden. If you’ve ever found yourself thinking, Man, I wonder how that one open source app does XYZ because I could use that in this app, then you know what I mean. One goal I have with Subtext is to try and expose code that I think would be useful to others. It’s part of the reason I started the Subkismet...
I’m not one to post a lot of quizzes on my blog. Let’s face it, while we may create altruistic reasons for posting quizzes such as:
It’s an interesting problem I thought up
It’s an interesting bug I ran into
we all know the real reasons for posting a quiz.
It serves as blog filler.
It’s a way to show off how smart the blogger is.
With that in mind, let me humbly present my latest SQL Quiz, which is something I ran into at work recently, and will not...
A few days back Jon Galloway and I were discussing a task he was working on to document a database for a client. He had planned to use some code generation to initially populate a spreadsheet and would fill in the details by hand. I suggested he store the data with the schema using SQL extended properties.
We looked around and found some stored procs for pulling properties out, but no useful applications for putting them in there in a nice, quick, and easy manner.
A few days later, the freaking guy releases this Database Dictionary Creator, a nice GUI tool to document your database,...
Working as a team against a common database schema can be a real challenge. Some teams prefer to have their local code connect to a centralized database, but this approach can create many headaches. If I make a schema change to a shared database, but am not ready to check in my code, that can break the site for another developer. For a project like Subtext, it is just not feasible to have a central database.
Instead, I prefer to work on a local copy of the database and propagate changes via versioned change scripts. That way, when I...
In two earlier posts I presented a couple of SQL stored procedures that I promised to tie together. The first procedure generates a random time of day and the second can be used to FTP a file from within SQL Server.
Well in this post I put these two together to create a stored procedure for creating a database job that will take a nightly backup of a database and FTP it to another location. I hope this isn’t terribly anti-climactic for you.
This script is perfect for a quick and dirty backup plan for a database. If you are...
This is another useful Sql Server Stored Procedure I found on the net written by Nigel Rivett.
The procedure uses the xp_cmdshell extended stored procedure to shell out an FTP command. You can use this procedure to ftp a file from one place to another. Of course, you will need to make sure that your command runs in the proper security context.
I made some very slight modifications in my own version of this procedure. I changed some of the parameters to be of type nvarchar instead of varchar for my international friends. I also changed the name to suit...
Here is a function that will generate a random time of day. Later I will show why I am posting this particular query and how I am using it. It comes in useful when trying create random scheduled jobs in SQL Server. I made use of a technique for generating random dates via Jon Galloway.
Parameter
DataType
...
Lately I have been spending a little bit of time performing maintenance tasks on my blog’s SQL Server. I noticed that OdeToCode’s Scott Allen was in the same mood.
In looking to free up some database space, he took the somewhat drastic step of deleting all referrals and urls before a certain date. Since he doesn’t care about this data, it isn’t really all that drastic. But it makes a data packrat like me shudder. I wanted to free up some space as well so I created an approach that frees up a lot of space, but keeps the data...
My friend Jeremy (no blog) pointed me to these two useful articles on how to perform dynamic WHERE clauses and ORDER BY clauses without using dynamic SQL. These were written long ago, but I had never thought to use COALESCE in this way. Very cool!
I will post them here so I can find them later.
Implementing a Dynamic WHERE Clause
Dynamic ORDER BY
UPDATE: This technique may not be as performant as hoped for. Marty in the comments noted that he saw table scans in using COALESCE in this way. Jeremy...
I’m not sure if this is common knowledge, but you can place template parameters in your SQL scripts and evaluate them within query analyzer. I think I learned this one a long time ago from a former fantastic SysAdmin, turned DBA, turned Developer, Tyler.
Here’s an example of a short script that makes use of a template variable.
SELECT * FROM <tableName, varchar(32), 'MyTable'>
Paste that into SQL Query Analyzer and hit CTRL+SHIFT+M. A dialog to replace the template parameters will pop up like so
Just fill in the values and hit return and you’re ready to run the...
In T-SQL, you can use the @@IDENTITY keyword to obtain the value of the identity column when you insert a new record. For example, the following query inserts a record into an imaginary table and returns a result set containing the ID of the inserted column.
INSERT INTO SomeTable
SELECT Value1, Value2
SELECT @@IDENTITY -- LAST INSERTED IDENTITY VALUE
There's the potential for a subtle bug here. Suppose later on, a coworker realizes that any time a record is inserted into [SomeTable] a record should also be inserted into the table [SomeTableAudit]. The simplest solution would be to add a trigger...
Found this interesting article via Hassan Voyeau that details the performance penalty when naming your stored procedure with an sp_ prefix in a database other than the master database.
Personally, I hate adding extraneous and unecessary prefixes and suffixes to names. Sometimes they’re useful and necessary, like when programming in Fortran 77. But I hate naming tables with a tbl prefix and stored procs with an sp prefix (I’m forced to at my current position). Sql Enterprise Manager does a nice job of separating tables from stored procedures when they are being displayed. I’m never going to get the fact confused...
What will the last two lines print. Will they be the same?
DECLARE @test VARCHAR(2)
DECLARE @first VARCHAR(4)
DECLARE @second< VARCHAR(4)
SELECT @first = ISNULL(@test, 'test')
SELECT @second = COALESCE(@test, 'test')
PRINT @first
PRINT @second
What do you think?
Technorati Tags:
SQL, Quiz
I’m posting this great tip so I can find it later and for your benefit.
[Via Julia Lerman Blog - Don’t Be Iffy...]
Technorati Tags:
SQL, Tips
I found a nice tip for selecting random rows from within a SQL Server 2000 database. Well actually, pseudorandom. Since my undergraduate thesis was on the topic of pseudorandom number generation, I might as well be precise. For some reason, my non-geek friends find it awfully funny when I mention pseudorandom numbers.
I digress. In order to select 10 records from some table at random, try this:
SELECT TOP 10 * FROM someTable ORDER By NEWID()
Now for my homework, I should find out just how random this is. There's a whole slew of statistical tests I can run to...
Microsoft just released the the Microsoft Sql Server 2000 Best Practices Analyzer 1.0. It's basically FxCop for SQL Server. Very cool.
Technorati Tags:
SQL, Tips
I needed to create a temp table in SQL with a column that contained an incrementing integer, without making that column an identity.
For example, suppose I want to select record from a table of users, but add a column that contains an incrementing counter. The data in the table should look like so:
counter
UserID
...