Self join SQL Interview Question

This seems like a good interview question. Let's suppose in our database, there is a Section table with a SectionId and a ParentSectionId. ParentSectionId contains the SectionId of the parent.
Now, find sections and which are parents of sections other than themselves.

SELECT S.SECTIONID FROM Section S  INNER JOIN
Section S2 ON S.SectionId= S2.ParentSectionId
WHERE S2.ParentSectionId <> S2.SectionId)

Alternatively,

SELECT * FROM Section S  AND
(SELECT COUNT(*) FROM Section S2
WHERE S2.ParentSectionId = S.SectionId
AND S2.ParentSectionId <> S2.SectionId)

Coding digest (SQL): Order by CreatedDateTime And ID Descending

I am creating a timeline of events right now in descending order. We have the events inside a table called timeline table. If the two events have the same CreatedDateTime, they should be ordered by their IDs (primary key which is a identity).

 So let's write a query for that.
(Assuming X is the current datetime)
SELECT * FROM TIMELINETABLE WHEN CREATEDDATETIME < X
ORDER BY DATETIME DESC, ID DESC

Is this right?
It is right but it won't work for me. Because, I am populating timeline items 10 at a time. And once the user scrolls to the bottom of the page, I will display 10 more events. For that I need to pass the last datetime and ID displayed on screen.

(Assuming X is the datetime and Y is the ID)
SELECT TOP 10  * FROM TIMELINETABLE WHEN CREATEDDATETIME < X OR (DATETIME = X AND ID < Y)
ORDER BY DATETIME DESC

Why does a page take a lot of time to load?

Suppose your users complain that the website takes a long time to load once they login. It's problem solving question. Let's break it down into the areas where the problem might be:

1. Server scripts

2. Database

3. Browser

4. Client scripts

5. Actual server

Lets address each area:

1. Server scripts

Server side code has some faults. What if there is an long loop of inefficient code?

2. Database
There might be a lot of concurrent users on the application and which leads to a lot of database reads and writes. There are several ways to find out how much time that it takes to execute a particular query. 

3. Browser.
Our website wasn't loading on IE either since we had not published the P3P policy.

4. Client scripts.
Errors in JavaScript.

5. Server issues.
Less RAM? Too many programs or users running concurrently may be one of the reason why the server takes a long time to respond. Why don't you check if the website loads properly at time of when there are few users accessing the system?

By the way, I came across this great post at Kissmetrics.

Read connection string from web.config

Add System.Configuration as a reference.

For some bizarre reason it's not included by default.

System.Configuration.ConfigurationManager.
    ConnectionStrings["connectionStringName"].ConnectionString;

SQL Server: Difference between varchar and nvarchar data types

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarcharrather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benefits of full Unicode storage.

Credits: Jeffrey L Whitledge