[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.

[SQL] Counting Rows with the Same Value of a Given Field

Here's a piss-easy script to count the number of rows in a table where a given field has the same value:

SELECT myField, COUNT(*) AS 'inelegantCount'
FROM myTable
GROUP BY myField

It's the classic GROUP BY statement example - which I can't manage to remember, thus the blog post.

[T-SQL] How to REPLACE on TEXT field

The REPLACE T-SQL function can be used only with VARCHAR - if you try to run REPLACE on a TEXT field you'll notice you're pretty much screwed (and that's probably how you ended up on this page in the first place).

There are ridiculously complicated ways to overcome this but the easieast solution I was able to find around is the following:

UPDATE myTable
SET myField = REPLACE(SUBSTRING(myField, 1, DATALENGTH(myField)), 'string2replace', 'replacementString')

SUBSTRING is returning VARCHAR so this way it works like charme.

I HATE SQL but I am getting to know more than I would like about it.

UPDATE:

as pointed out by people in the comments, the above will truncate to 8000 characters. This wasn't a concern for me at all because I knew my strings where pretty short. If you're worried about truncation you can use a CAST (check comments for more details).

[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

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.

How I feel about SQL

I really tried hard to make friends with SQL, I even got a few books, and O'Reilly ones (they all sucked, but - hey - it's SQL we're talking about). I eventually came to cope with my feelings - if you gotta hate something, SQL ain't too bad a pick. Ain't too bad for poor SQL either, there's a whole lot of awesome indian dudes who love it to death. 

Maybe it's because I've been too lazy to install intellisense plugins for SQL Management Studio so far, maybe because of the cryptic error messages ("there's a fuck up around line 345" - jeez, Javascript is easier to debug) or maybe just because that's the way it is, you gotta hate it. 

P.S. There's been a lot going on lately - mostly going crazy with a solution that builds fine manually but fails to build through CruiseControl.NET - but I've been a lazy ass and didn't post anything about it. You can read about it here and here for now.