SQLServerPedia

Q:  How to do the query “select * from tbl1 where tbl1.fname like ‘John’ and lname not in (select lname from tbl1 where ybirth>1975 and city=’London’)” without the subquery?

Kevin Kline says:  This is a very simple SQL problem. The following query can be rewritten:

SELECT *
FROM tbl1
WHERE tbl1.fname LIKE ‘John’
AND lname NOT IN (SELECT lname
FROM tbl1
WHERE ybirth>1975 AND city=’London’)

As:

SELECT *
FROM tbl1
WHERE tble1.fname = ‘JOHN’
AND (ybirth <> 1975
AND city <> ‘LONDON’)

Note that you don’t need the LIKE operator since you didn’t use a wildcard operator in the name ‘JOHN’. Also note that ybirth must not be a datetime datatype or else the query would need to say “…AND ybirth <> ‘JAN-01-1975…’ and otherwise follow a proper datetime format.

Technorati Tags:
SQL Server, SQL development, LIKE operators
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:  How can you view SQL Server through the Internet?

Jason Hall says:  Unfortunately, you didn’t tell me if you needed to expose the database to developers, end-users, or administrators. Their needs differ as do the means of exposing SQL Server over the internet to each different type of user.

However, I’ll assume you’re a developer and you want to program an application that is exposed to end-users over the internet. In a situation like this, most people use ASP code.

Read all about it at Microsoft support here.

Technorati Tags:
SQL Server, ASP, development
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:   How I can handle SQL injection in all over application by using query??

Kevin Kline says:   It’s funny that you mention this since Microsoft UK just had a very public SQL Injection attack on their website. It’s reported on TechWorld here.

In essence, a SQL Injection attack is where a programmer has written a web page that exposes a SQL query to the internet. For example, you might write a query that allows the end user of a web page to enter the name of product that they want to find on the web site. However, the web site is now vulnerable to attack because the programmer placed no string checking onto the values returned by the web page, which some malicious person could, instead of putting the name of product into the open field, substitute something like ‘DELETE FROM products’.

SQL Injections is a broad and complex topic though. So you’d best read what the experts have written. I like what NGS Software puts out, such as this white paper.

I also really like Chip Jones’ website called SQL Security.com, which has extensive information on SQL Injection attacks and defenses. It also has a really good utility available for free called LOCKDOWN.SQL.

Technorati Tags:
SQL injection attack, database security, database vulnerabilities, NGS Software, Chip Jones, www.sqlsecurity.com, LOCKDOWN.SQL
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:  If i have a db of size 500 GB and have only one data file of 350 GB, then: 1) would splitting the data file into multiple files (3) on same File Group would help performance? 2) do all 3 files have to be on the same disk and /or same file group?

Kevin Kline says:  Generally speaking, the main reason you ever segment a database across files and/or filegroups is to put those segments onto separate physical disks. If you don’t place them on separate disk or RAID arrays, then you get no performance improvement because all of the IO still resides in the same place it did before segmentation.

If, on the other hand, you place the filegroups or files onto separate physical disks you will also be moving the IO to separate disks, thus reducing the overall IO load on the disk subsystem(s).

And of course, the first step here is to make sure that the transaction log file is on a separate physical disk or RAID array before doing any of these other steps. If you haven’t done this step first, you’ll still have terrible IO.

Technorati Tags:
SQL Server, datafiles, file groups, segmentation, RAID, I/O
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:  Can we rollback the truncate statment? whats the difference between delete and truncate?

Kevin Kline says:  You cannot rollback TRUNCATE. It is a “non-logged statement”. TRUNCATE removes ALL records from a table.

You -can- rollback DELETE. It is a “logged statement”. DELETE removes one or more records from a table according to the conditions of your WHERE clause.

My recommendation is not to use TRUNCATE in a production application since you cannot recover from it. Instead, use DELETE unless you have a very strong reason to use TRUNCATE.

Technorati Tags:
SQL Server, database operations, truncate, rollback
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:  Is the SQL Server 2005 Standard performance twice as fast as SQL Server 2005 Express? I sell software that uses both and aside from limitations to the data size and number of users other consultants always say go with Standard it is twice as fast.

Kevin Kline says:  Well, I’d say the other consultants are wrong.

On applications that are agnostic and running relatively few users/connections, you should see little or no difference between Express Edition and Standard Edition.

It is true that Standard Edition can scale much higher. So Standard Edition could potentially be faster (and much more than simply twice as fast) when scaling to dozens, scores, or hundreds of users.

But if the application is for relatively small workloads, the speed should be nearly identical.

Technorati Tags:
SQL Server 2005 Standard, SQL Server 2005 Express, application workloads, scalability
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

This post on the MSDN forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2064643&SiteID=1

...reminded me of something interesting I found out a few months ago. It seems that whereas you can't mix regular and calculated measures in a set used in the SCOPE statement, you can rewrite the assignment to avoid using SCOPE and do a direct assignment instead. So, for example, if you were trying to assign to a regular measure and a calculated measure using a SCOPE statement like this:

SCOPE({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]});
    this=1;
END SCOPE;

You would get the following error:

A set has been encountered that cannot contain calculated members.
MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.

You could rewrite the assignment as follows using two SCOPEs:

SCOPE([Measures].[RegularMeasure]);
    this=1;
END SCOPE;

SCOPE([Measures].[CalculatedMeasure]);
    this=1;
END SCOPE;

...but this is clearly a pain, as you're duplicating your assignment logic. What you can do instead is this:

({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]})=1;

Not as easy to read as using SCOPE, I know, especially if you're doing something complex, but at least it works! Now I wonder why SCOPE has this problem? Probably something worth opening an issue on Connect about...

This post on the MSDN forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2064643&SiteID=1

...reminded me of something interesting I found out a few months ago. It seems that whereas you can't mix regular and calculated measures in a set used in the SCOPE statement, you can rewrite the assignment to avoid using SCOPE and do a direct assignment instead. So, for example, if you were trying to assign to a regular measure and a calculated measure using a SCOPE statement like this:

SCOPE({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]});
    this=1;
END SCOPE;

You would get the following error:

A set has been encountered that cannot contain calculated members.
MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.

You could rewrite the assignment as follows using two SCOPEs:

SCOPE([Measures].[RegularMeasure]);
    this=1;
END SCOPE;

SCOPE([Measures].[CalculatedMeasure]);
    this=1;
END SCOPE;

...but this is clearly a pain, as you're duplicating your assignment logic. What you can do instead is this:

({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]})=1;

Not as easy to read as using SCOPE, I know, especially if you're doing something complex, but at least it works! Now I wonder why SCOPE has this problem? Probably something worth opening an issue on Connect about...