[SQL] How to escape single quotes in the words of Pinal Dave

I will answer this SQL riddle in the words of Pinal Dave:

Jr. Developer asked me other day how to escape single quote?

User can escape single quote using two single quotes (NOT double quote).

In case you need it (I doubt it) he also provides a great example.

The man is a SQL master, and if you're a Jr.Developer (I am not but when it comes to SQL I am probably less than that) you gotta follow him on twitter.  

Another way of injecting single quotes using CHAR(39):

DECLARE @IanQuigley as varchar(MAX)
SET @IanQuigley = 'Thanks, I don' + CHAR(39) + 't know much about SQL'

I did my worst - as usual.

How to figure out which SQL2000 edition is installled

I recently had the problem to understand which edition (standard/developer) was installed on an old machine. This sounds like a pretty straightforward thing to do - and it is indeed IF you know you need to run the following query:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

You'd imagine you could figure this out looking at the 'about' information in enterprise manager (as in SQL2005 in SQL Management Studio) but that doesn't seem to be the case. I couldn't find any other solution.

Mysteries of MS faith.

[SQLServer] Indexing and Scalability Tips

Recently I spent a lot of time messing about with indexes. Not because I like it - but because there was a little bit of a fuckup at work with scalability issues on a DB. After messing around with SQLProfiler (real life-saver in this case) I gained a bit more of understanding about indexes as I never got the chance to do before.

What I learned about indexes and scalability can be summarized in 2 points:

1) You don't wanna have a clustered index on the primary key on a table once it gets huge (just make it nonclustered) if you plan on running loads of inserts

This is not a problem when the table is small - and since it's good for fast selects everyone's cool with it. Once the table gets populated with LOADS of records or it gets HUGE in size this WILL KILL YOU on any insert or update operation.

Just to give a few figures:

- A clustered index on a table with 45 million records was killing my SQL server - every insert from code was resulting in nasty timeouts (it was taking more than the default 30 secs for standard inserts - Increasing your timeout tolerance won't solve your scalability problems!)

- A clustered index on a HUGE table (15 gigs) with a few million records was slowing down inserts to the point it was taking 15 times as much as it was taking on an empty db (we're talking mSecs but on big batches it's gonna kill you)

2) You wanna have a non-clustered index for frequently selected fields on any table you plan on running extensive selects on (add the fields you select often to a single index)

- On a medium size table (40k records) select time on a given set of fields was doubling in time compared to the same table with a lot less data before adding a triple-index on the set of given fields.

Following the points above I was able to solve my scalability issues (the process I was running is now taking the same time on a huge db compared to an empty one). Obviosuly these rules are not applicable in every case - but understanding the points above might help people when 'scalability' hits the fan.

[T-SQL] How to boost 'joined' Update Performance

Time for some real boring stuff.

The first thing people are tempted to write when they have to run an update on a table picking stuff from another table is probably something like this:

--method 1: nested select
--around 15 mins to execute on 100.000 records
UPDATE myTable
SET myTable_field = (SELECT myOtherTable_field
                   FROM myOtherTable
                   WHERE myOtherTable_someID = myTable_someID)

This way the nested select will run once for each record you're updating (100k times in my example) and you'll get shit performance.

Alternative to this are:

--method 2: update FROM more than one table
--around 45 secs to execute on 100.000 records
UPDATE myTable
SET myTable_field = b.cache_VmiID
FROM myTable a, myOtherTable b
WHERE b.myOtherTable_someID = a.myTable_someID

or, more intuitive:

--method 3: JOINED UPDATE
--42 secs to execute on 100.000 records
UPDATE myTable
SET a.myTable_field = b.myOtherTable_field
FROM myTable a join myOtherTable b
on b.myOtherTable_someID = a.myTable_someID

Execution times assume you're clearing SQLServer cache with fucking DROPCLEANBUFFERS (without clearing cache method 2 and 3 take about 4-5 secs).

[T-SQL] How to get length of TEXT field

To calculate the lenght of a TEXT field the LEN function used to calculate the length of VARCHAR fields won't work. 

You need to use the DATALENGTH T-SQL function:

SELECT DATALENGTH(myTextField) AS lengthOfMyTextField

[SQLServer] Error 3205: too many devices specified for backup or restore only 64 are allowed

Problem: restoring a backup on SQLServer I get the following error:
[Error 3205: too many devices specified for backup or restore only 64 are allowed]

Solution: you're probably trying to restore a SQL2005 backup on a SQL2000 instance. There's no backward compatibility for backup operations bewteen SQL2005 and SQL2000. Installing SQL2005 will make the error go away (I know - it sucks).

I recently came across the cryptic error message in subject. At first I was puzzled - then google helped me understand the problem was not my lack of sweet SQLServer skills...

Learning SQL with Ganesh - Part 1 of ?

I found (and shared) this desperate help request yesterday on my google reader - I felt a burning desire to help the poor guy and I immediately followed the link to StackOverflow, but folks over there already edited the heck out of the original question.

Anyway this is what I'do to find AND delete duplicate rows on a table (with Identity):

DELETE
FROM    MyTable
WHERE   ColumnID NOT IN
(    SELECT   MIN(ColumnID)
FROM MyTable
GROUP BY    Column1, Column2, ..., ColumnN )

If you don't have an identity on the table you can add it then drop it when you're done.

Anyway - I obviously up-modded the guy and went ahead to have look at other questions from him and - guess what - I found a pair of pearls (the previous one was pretty OK besides the original title):

Probably weirdest SQL questions ever asked, but anyway, even if I HATE SQL, I tried to find answers anyway ('cause that's human nature - I guess).

To get the top 10 rows without sorting I'd probably do some rocket science:

select top 10 *
from MyTable
-- duh!

but then, if random order is required (?), maybe something like:

select top 10 *
from MyTable
order by NEWID()

To get the last 5 rows without sorting I'd rather go with:

select top 5 *
from MyTable
order by ColumnID desc

But what if you don't have an ID? What does without sorting mean? In doubt, I'd probably do something like the following:

select top 5 *
from ( select *
 from MyTable
 order by newId() desc)

Which is some crazy shit and without doubt doesn't make any sense.

[SQLServer] SQLServer2008: Workgroup VS Standard

You might be in doubt when choosing between SQLServer2008 Standard or Workgroup edition (most people rule out the Enterprise edition since it's 7500 € or so) - here's a list of the differences bewteen the two (in red most relevant).

Reporting Services memory limits : unlimited for Standard - 4GB for workgroup

Standard features not supported by workgroup:

Standard algorithms
Data mining tools: wizards, editors, query builders
SQL Server Analysis Services service
SQL Server Analysis Services backup
General performance/scale improvements
SSIS Designer including VSTA scripting
Integration Services service, wizards, and command prompt utilities
Basic tasks and transformations
Log providers and logging
Data profiling tools
Additional sources and destinations: (Raw File source, XML source, DataReader destination, Raw File destination, Recordset destination, SQL Server Compact destination, SQL Server destination)
Business Intelligence Development Studio
MDX edit, debug, and design tools
Standard performance reports
Plan guides
Plan freezing for plan guides
Policy-based best practices
Multi-server policy-based management
Heterogeneous subscribers
Database mirroring (witness only)
Failover clustering (very limited in workgroup)
Dynamic AWE
Failover without client configuration
Automatic corruption recovery from mirror

The Workgroup edition apparently includes 5 CAL licences (a license is around 130 €) - I see that as the only possible reason you might go for it since the price of the piece of software itself is very similar (around 600 € for the workgroup against 750 for the standard).

For more details http://msdn.microsoft.com/en-us/library/cc645993.aspx

[SQL Server 2005] SQL Server 2005 hangs on "Setting [File/Registry ] Security"

Prolem: SQL Server 2005 (any edition including express) installation hangs on "Setting [File/Registry ] Security".

Solution: Either remove the network cable and restart or Go out for lunch and enjoy (it takes ages, 2-3 hours for the whole installation for big networks).

This is a very common problem that occurs often in presence of huge networks (global corporate networks are good candidates); the reason why it all happens seems to be related to some domain resolutions. The installation runs smoothly to this point, then it appears to hang for a long time, so long the temptation cancel the installation is almost irresistible. What stops you from doing it is obviously the fact that from taskmanager everything seems to be just fine. If you let it be for a couple of hours it evetually will finish the installation alright. I am at the moment not aware of other solutions rather than disconnect the machine from the network or - my favourite - go out for lunch, and make sure to have a big one.

Missing Sql Server 2005 Management Studio

Problem: after the installation of Microsoft SQL Server 2005 there is no trace of the Management Studio.

Asked Johnny Idol about the solution, he answered "Google it"...what a great tip!

The main problem seems related to a previous installation of MS Visual Studio (2008), which comes with a SQL Server Express Edition, that interferes for some reason.

Below are two solutions for the problem:

Solution 1: check this link for an elegant solution, found just when I had already used solution 2.

Solution 2: just because this is a butchering blog, I'll show you a very powerful solution.

1. open Control Panel


2. select Add or Remove Programs


3. uninstall ALL voices related to MS SQL Server


4. Reinstall Microsoft SQL Server 2005


5. Now the Management Studio is right into your program menu.

Simple and brutal, but it works both in Windows XP and Vista.
Stay tuned!

[ASP.NET, SQLServer] How to fix Performance Problems - guidelines

Scenario/Problem: You have an ASP.NET (framework x.x.) application and some pages are taking forever to load up.

Guidelines: Follow these steps:

1. If using DBs: Are you indexing your DB? If not you should consider it (up to 30% speed increase, depending on you DB design)
2. Check your controls ViewState: are you sending unuseful stuff back and forth? If you're generating/populating dinamically some control you should disable its viewstate. Use ASP.NET trace to check controls viewstate size (DRAMATIC speed increase, depending on your app)
3. Look at your T-SQL stored procedures: are you abusing temporary tables or dynamic SQL execution? If so it's not a good idea, use SQL profiler to check for stored procedures recompilation-execution time. If you don't have stored procedures you probably should use them
4. Look at your code behind: are you round-tripping more than you need? Are you sharing open connections to DB when you can (use SQL Profiler to spot how many connections you are opening and closing)? Are you mis-using some controls (huge drop down list can cause major delays)? Use ASP.NET trace to locate hot-spots and long latencies.

Real World Case: I don't feel much of a butcher these days: I was working on a web app [ASP.NET 1.1, SQLServer2000] developed by someone else and i realized butchery is a fine art and I still got a long way to go.

My main task was to speed things up. It was taking 18 seconds to load a single page, with an average amount of data (nothing major). I first tried to put some indexes on the DB (1), which didn't have any, but not having at the time much experience on DB indexing I put my life in the hands of the Enterprise Manager Index Tuning Wizard (painful experience), knowing it would have butchered my DB, but hopefully just a bit, speeding up things for me in the end. It happened, but from 16-18 seconds the guilty page went down to 12-13, which was something but not what I was hoping. The wizard claimed a 28% speed increase: true, but not enough.

After this delusion, I went on inspecting the trace (enable it setting Trace="true" in the top page declaration, or @ app level from web.config) and I noticed KBytes of useless stuff being sent back and forth as ViewState (2). The app had a coulple of user controls dynamically generated every Page.Load, so I just disabled the ViewState for those (EnableviewState = "false") and the app speed dramatically increased (50% in my case). Good, but still it was taking 6-7 goddamn seconds to load. I also took off the SmartNavigation="true" page property: it might be the pre-AJAX coolest thing but it's deprecated and it saved me almost 2 seconds: down to 5.

I started looking at the code, spotting un-necessary open/close operations for connections that could've been shared, loads of butchery in there, of the finest quality, and I noticed ALL the stored procedures were being recompiled (3) because of a massive use of temporary tables and dynamic SQL, even when non necessary (if the stored procedure is being recompiled there's no gain speed, ergo no point in having it as stored procedure). I didn't really want to change the code, a work of art is a work of art, so I decided to roam a little bit more through the trace looking for hot-spots.

I noticed the page was laoding pretty fast looking at the trace latencies. I put some Trace.Warn("HereandThere") but there was nothing major, the whole page was taking only around 1 sec to process and render, but I was still seeing the content trhough the browser after 5 secs. Then again a look at the ViewState and controls sizes and it happened: I saw a 900KB dropdownlist (4). Turned out that drop down was being populated with more than 10.000 record (yes > 10^4) and the browser was obviously taking forever (around 3 secs) to load it. Took that out (set up a popup with only the dropdown for that selection) and EUREKA: loading time went down to 1-3 secs, which considering shitty server and crappy network is more than acceptable (compare it with 18 seconds if you don't agree).

As I said, there was (and there is) a lot more to change and optimize in this app, as per stored procedures code and code behind, but the art is art and I wanna keep this mess untidy as long as I can, to inspire me and to remind me what butchery truly means.