How to get records in random order from a sql query in sql server?

In SQL Server we can get records in random order from a sql query using NEWID() Function like:

SELECT Subject FROM dbo.forumThreads ORDER BY NEWID()

What is the use of indexes and what are the types of indexes available in SQL Server?

Indexes are used to find data quickly when a query is processed in any relational database. Indexes improve performance by 10 to 500 times.
Index can improve the performance in following operations:

• Find the records matching with WHERE clause

UPDATE Books SET Availability = 1 WHERE SubjectId =12
DELETE FROM Books WHERE Price <10
SELECT * FROM Books WHERE Price BETWEEN 50 AND 80

• Sorting the result with ORDER BY

SELECT * FROM Books ORDER BY Price DESC

• Grouping records and aggregate values

SELECT Count(*) as Units, Price FROM Books GROUP BY Price

There are two types of indexes available in SQL Server: clustered and non-clustered
Clustered index
Clustered index physically reorders the records of a table. Therefore a table can have only one clustered index. Usually a clustered index will be created on the primary key of a table.
Non – Clustered Index
Non – Clustered index are stored in the order of the index key values, but the information in the table is stored in a different order. Means logical sorting of data not Physical. In SQl Server 2005 a table can have 249 non-clustered indexes.
Composite Indexes
A composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes. If you have composite index on Price and BookName then take can take advantage of it like this:
SELECT BookName, Price FROM Products ORDER BY UnitPrice BookName, Price DESC

Explain ACID properties of the database?

All Database systems which include transaction support implement ACID properties to ensure the integrity of the database. ACID stands for Atomicity, Consistency, Isolation and Durability

• Atomicity: Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. Modifications on the data in the database either fail or succeed.
• Consistency: This property ensures that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.
• Isolation: It requires that multiple transactions occurring at the same time not impact each other’s execution.
• Durability: It ensures that any transaction committed to the database will not be lost.

What is the purpose of connection pooling in ADO.NET?

Connection pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a connection pool, an application can reuse that connection without performing the complete connection creation process.

By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.

When a user request a connection, it is returned from the pool rather than establishing new connection and, when a user releases a connection, it is returned to the pool rather than being released. But be sure than your connections use the same connection string each time. Here is the Syntax

conn.ConnectionString = "integrated Security=SSPI; SERVER=192.168.0.123; DATABASE=MY_DB; Min Pool Size=4;Max Pool Size=40;Connect Timeout=14;";

Sql Query for Creating Temp Table

-- create temp table
CREATE TABLE #temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT #temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

-- initialize the NTEXT column so there is a pointer
UPDATE #temp SET Definition = ''

-- declare local variables
DECLARE
@txtPval binary(16),
@txtPidx INT,
@curName SYSNAME,
@curtext NVARCHAR(4000)

-- set up a cursor, we need to be sure this is in the correct order
-- from syscomments (which orders the 8KB chunks by colid)

DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT OBJECT_NAME(id), text
FROM syscomments s
INNER JOIN #temp t
ON s.id = t.Proc_id
ORDER BY id, colid
OPEN c
FETCH NEXT FROM c INTO @curName, @curtext

-- start the loop
WHILE (@@FETCH_STATUS = 0)
BEGIN

-- get the pointer for the current procedure name / colid
SELECT @txtPval = TEXTPTR(Definition)
FROM #temp
WHERE Proc_Name = @curName

-- find out where to append the #temp table's value
SELECT @txtPidx = DATALENGTH(Definition)/2
FROM #temp
WHERE Proc_Name = @curName

-- apply the append of the current 8KB chunk
UPDATETEXT #temp.definition @txtPval @txtPidx 0 @curtext

FETCH NEXT FROM c INTO @curName, @curtext
END

-- check what was produced
SELECT Proc_Name, Definition, DATALENGTH(Definition)/2
FROM #temp

-- check our filter
SELECT Proc_Name, Definition
FROM #temp
WHERE definition LIKE '%foobar%'

-- clean up
DROP TABLE #temp
CLOSE c
DEALLOCATE c