Returning random result from a dataset

Problem: Many of the developers write complex and huge code to return random images or news on every page refresh on its website or project.

Solution: Sql server provides easy way to return random result on each call.

Query:

Select * from [table1] order by newid()

Result:
if you have 2 records in your table you will get different result each time.

How to avoid dynamic query using procedures

We all know string query or dynamic query utalize so much resources while execution. Major flow back of dynamic query is string conversion of all types of parameters and writing bad code through SQL injection.

Here is a solution to avoid that type of query.

Case:
There is a column "Param" and it is not required every time while query execution.
the table joins with master table as left outer join.

Problem:
When we provide parameter we can use case to handle it without writing dynamic query. But when we do not provide parameter value then only child table records return what can i do to return whole dataset.

Solution:
here is the solution

DECLARE @PARAM INT
SET @PARAM = 0

SELECT * FROM Master LEFT OUTER JOIN Child ON Master.id = Child.id
WHERE (CASE WHEN @PARAM = 0 THEN master.id ELSE child.id END )
IN (CASE WHEN @PARAM = 0 THEN master.id ELSE
CASE WHEN @PARAM > 0 THEN @PARAM ELSE child.id END
END)

when we provide paramter then only required records returns else all records returns from master table and we do not need any dynamic query.

New Features in SQL Server 2008 Katmai

Microsoft SQL Server 2008 introduces several important new Transact‑SQL programmability features and enhances some existing ones. You can find details in SQL Server Books Online.

Following are highlighted features

1. Declaring and initializing variables : http://sqlservercoollinks.blogspot.com/2009/07/1-declaring-and-initializing-variables.html
2. Compound assignment operators : http://sqlservercoollinks.blogspot.com/2009/07/1-declaring-and-initializing-variables.html
3. Table value constructor support through the VALUES clause
4. Enhancements to the CONVERT function
5. New date and time data types and functions
6. Large UDTs
7. The HIERARCHYID data type
8. Table types and table-valued parameters
9. The MERGE statement, grouping sets enhancements
10.DDL trigger enhancements
11.Sparse columns
12.Filtered indexes
13.Large CLR user-defined aggregates
14.Multi-input CLR user-defined aggregates
15.The ORDER option for CLR table-valued functions
16.Object dependencies
17.Change data capture
18.Collation alignment with Microsoft® Windows®
19.Deprecation

I have picked this list from official website of Microsoft TECHNET.I will define it in detail one by one.You can also get detail from this attached link.