For some reason by default you can have "AUTOCLOSE" property of your database set to ON. That's not bad, but if you check your server Event Log you can find a lot of entries like "Starting database 'xxxxxxx' ". And log is not a problem, but for sure this takes a time and slow down your asp.net application response.

You can turn it off in Managment Studio.
or you can run a little sql to update all databases on server:

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))

   EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')

'

All databases won't close automatically and response instantly.

Looking for rows with some duplicate values in some column(s) is very common. I'm not saying that's something hard.  I was asked about this. Here is the solution.

   1:  SELECT [Name], COUNT([Name]) as NumOccurrences 

   2:  FROM [People]

   3:  GROUP BY [Name]

   4:  HAVING ( COUNT( [Name] ) > 1 )

And the query to get all PKs ( primary keys ) for rows with duplicate [Name]:

   1:  SELECT [uid] 

   2:  FROM [People] 

   3:  WHERE [Name] IN (

   4:  SELECT [Name]

   5:  FROM [People]

   6:  GROUP BY [Name]

   7:  HAVING ( COUNT([Name]) > 1 ))

That's all.

During web development often task is to get some stuff in random order. For example, to display random articles, products, testimonials and so on.

First thing you want to do is to write something like this:

SELECT TOP 10 * 
FROM Articles 
ORDER BY RAND() 

And previous query DOES NOT work. It shows records in regular order. Why? Because RAND() calculated once, not in each row.

The following way works fine:

SELECT TOP 10 * 
FROM Articles 
ORDER BY NEWID() 

That's all. Nice and easy. Not recommended for HUGE tables... but this is crazy to try show something randomly from the huge table. Use other ways. For example sub queries.

There are some difference in SQL syntax for MySQL and MS SQL. One of the differences is TOP and LIMIT keywords. And this is bad. Because LIMIT comes in the end of query and TOP comes in the beginning. So, simply to replace is not good. I'm missing the ability of LIMIT to use 2 parameters... but this is possible too, and you need to remember this and add this support if you find this code useful.

[code=c#;Simply approach to make MySQL queries work on MS SQL]/// <summary>
/// Converts MySQL "LIMIT" queries to MS SQL "TOP" queries
/// </summary>
/// <param name="q">Query</param>
/// <returns></returns>
public string MakeQuerySQLServerCompatible(string q)
{
    string res = q;
    Regex limit = new Regex("SELECT(.+)LIMIT (\\d+)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
    Match m = limit.Match(q);
    if (m.Groups.Count == 3)
    {
        string beforeLimit = m.Groups[1].Value;
        string limitCount = m.Groups[2].Value;
        res = string.Format("SELECT TOP ({0}) {1}", limitCount, beforeLimit);
    }
        return res;
    }[/code]

This stuff is tricky, that's why I decided to post it. The idea to remove LIMIT stuff and replace it with SELECT TOP statement. These queries special abilities always on high demand because almost every query should define the max amount of records to receive.

Any suggestions how to support "LIMIT 10,20"-like queries in MS SQL ?

Still overloaded with work, maybe add more later.

The problem:

Post-PHP developers always ask me about this. Because there are good know solution for MySQL in phpMyAdmin, but not so know for MS SQL Express. Usually, we are talking about ASP.NET, and we can easily decide what DB to use. MySQL very good database too and I'm often use it for web projects. 

While developing ASP.NET projects using SQL Express 2005 Database everything is good until you need move your database on server. Usually the best to create install script, but how to dump database schema and data ? For web developers, who just started ASP.NET development... or used MSDE before this can be a big problem.

The solution: 

The first way to publish everybody tries - DTS wizard... But there are no DTS wizard in SQL Express 2005.

The second way we can find over internet - some "strange" Export/Import functions in SQL Management Studio. So, after long search how to add these functions to Management Studio - no results.

And the right way. Appears, that Microsoft created  Microsoft SQL Server Database Publishing Wizard 1.1 that solves all the problems. Neat! It works with  Visual Studio 2005 and/or Visual Web Developer 2005.

Summary: 

So, what we need to develop project for ASP.NET 2.0:

1. Visual Web Developer 2005

2. Microsoft SQL Express 2005

3. Database Publishing Wizard

4. ASP.NET 2.0 Hosting