I thought I mention this, since these types of tools are rarely needed, but when they are, they are also seriously saving your ass. :-) SQL Log Rescue is basically undo tool which analyzes transaction logs of your SQL Server database and allows you to
Read More...
From my friends down south: The March Richmond SQL Server Users Group meeting is Wednesday 12 Mar 2008 . This is going to be a good one folks - Kevin Viers is a Business Intelligence guru for Microsoft. Register today! (... if you plan to attend, please.)
Read More...
I have several SQL utility scripts (as well as classic ASP scripts, controls, utils, etc...) that I use regularly for everything from retrieving table listings to creating select, insert, update & delete stored procedures. Below are some of my favorite,
Read More...
I see surprisingly often people using dynamic SQL when their need is to filter data with WHERE clause, with varying fields fields used. Say I have a table
CREATE TABLE [dbo].[SAMPLETABLE](
[id] [int] IDENTITY(1,1) NOT NULL,
[somenumber] [int] NOT NULL,
[sometext] [varchar](255) NULL,
CONSTRAINT [PK_SAMPLETABLE] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SAMPLETABLE] ON [dbo].[SAMPLETABLE]
(
[somenumber] ASC,
[sometext] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
With data something like:
1 10 Test text 1
2 11 Test text 2
3 15 Test text 2
4 12 Test text 3
5 19 Something totally different
Ok. Let's say that my query would need to filter either with minimum somenumber or LIKE sometext or with both of them (let's forget the direct id restriction for the sake of the example). The needed SQL takes the minimum allowed somenumber in, and uses LIKE for sometext.
People very often would write the SQL in .NET code. The pseudo could be
Dim sql as String="select * from dbo.sampletable"
Dim sqlwhere As String=""
if somenumber <> 0 then
sqlwhere &= "WHERE somenumber >= " & somenumber
end if
if sometext <> "" then
If sqlwhere <> "" THEN
sqlwhere &= " AND "
else
sqlwhere &= " WHERE "
end if
sqlwhere &= " sometext LIKE '%" & sometext & "%'"
end if
Then concatenate sql and sqlwhere if sqlwhere <> "" or they would build in in the stored proc and use sp_executesql or EXEC . Ofr course I didn't use parameters either in the previous pseudo, which is bad practise alone.
In fact, it's useful to know that you can build this "if something is passed then do something" directly into SQL. You can use basic boolean trick for that, by controlling the evaluation of OR operator. When either of the OR's two expressions are true, that's enough to evaluate the entire it as true. So, using certain defaults in stored proc for parameters and OR operator you can create a procedure (or just query) like this
CREATE PROCEDURE sampleTableSelect
@somenumbermin int=NULL,
@sometext varchar(255)=NULL
AS
BEGIN
SELECT * FROM dbo.SAMPLETABLE
WHERE (@somenumbermin IS NULL OR somenumber >= @somenumbermin)
AND (@sometext IS NULL OR sometext LIKE '%' + @sometext + '%')
END
As you can see now: if @somenumbermin is not passed, it has NULL as default value and the other side of the first OR isn't evaluated, meaning that part of the WHERE has no effect since it evaluates to true. Same applies for the @sometext. Basic idea is to check if passed parameter has default value , and when *not* then evaluate the other expression. Otherwise evaluate to true by default.
So this means you can execute the proc with following variations
> exec sampleTableSelect
1 10 Test text 1
2 11 Test text 2
4 12 Test text 3
3 15 Test text 2
5 19 Something totally different
> exec sampleTableSelect @sometext = 'text 2'
2 11 Test text 2
3 15 Test text 2
> exec sampleTableSelect @somenumbermin = 11
2 11 Test text 2
4 12 Test text 3
3 15 Test text 2
5 19 Something totally different
> exec sampleTableSelect @somenumbermin = 15,@sometext = 't'
3 15 Test text 2
5 19 Something totally different
And as you understand, from usage perspective be it directly running the proc or using it with SqlCommand, idea is to control it by passing the parameter. If you don't want the condition to apply, do not pass the related parameter. With this knowledge you could build queries so that you prepare for certain fields becoming queryable, and kind of "map" the likely variations in beforehand.
I'm not saying this is the correct way. It's just one way to do these things, but I like because it's explicit and keeps my procedure in control of the querying. So I prefer it.
Here's from email I got,
Dear Friends,
I am very pleased to announce the release of SQL Prompt 3.5.
This is a major new release and a recommended upgrade for all current SQL Prompt 3.0/3.1 users. New features include:
- Visual Studio 2005 database project support
- Cross database queries
- Distributed queries across linked SQL Server instances
- Correlated subqueries
- 3 - 5 times faster metadata retrieval
- Large script support
- 40 - 95% decrease in memory footprint
We have also included dozens of bug fixes and other enhancements, and importantly have eradicated the editor hangs that have been plaguing some users.
See: http://www.red-gate.com/products/SQL_Prompt/index.htm
When you use one database platform long enough you start to think everything you do is ANSI SQL-92 compliant. Until you switch database platforms that is! Then you find the simple things no longer work. Take for example the TOP clause used so often in MS SQL Server. It does not work on Oracle. <sigh/>
I've been digging into Oracle lately and am noting down the differences in the hopes it will help someone else down the road. So, without further waiting, here are the examples to get the top 10 records for the different platforms:
- MS SQL Server
SELECT TOP 10 *
FROM [table]
- Oracle
SELECT *
FROM [table]
WHERE rownum <= 10;
- MySql
SELECT *
FROM [table]
LIMIT 10
- DB2
SELECT *
FROM [table]
FETCH FIRST 10 ROWS ONLY;
Hope that helps someone out.
