Здесь был Вася!!!1!!!1!!один!!1!

SQL Output Clause

Insert, update, and delete statements can have an output clause.

This can output cells from the impacted rows.  

SQL XML

Sql 2005 supports xml natively. This allows for data structures to be stored in yet another way, and is occasionally very useful for building objects.

Try Catch Throw, and Transactions

Sql 2005 introduced a better try/catch construct and the ability to re-throw the exception. Previously checking the @@error was really the only way to test for errors. This makes commiting/rolling back transactions a synch and still allows the option to bubble an error up to .net or whatever is calling a stored proc

Pivot, Unpivot

Pivot and unpivot allow the selection of rows as columns and vice versa. This is mostly useful for reporting and often data-imports. Pivot rotates rows (values) into columns

Common Table Expression (CTE) now with 100% more recursion!

Common Table Expressions are pretty much a just in time view that can be used in a query or stored proc. The advantage over a view is the ability to use parameters to limit the dataset used.

Group By, Where, Having, Select Distinct

Group By is possibly the least understood commonly used function of SQL. A typical use for group by will come from a query such as this one:

SELECT count([ProductID])

      ,[Name]

      ,[ProductNumber]

      ,[MakeFlag]

      ,[FinishedGoodsFlag]

      ,[Color]

      ,[DaysToManufacture]

 FROM Production.Product

Which returns an error:

Msg 8120, Level 16, State 1, Line 1

Column 'Production.Product.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So a developer will end up doing something like this:

SELECT count([ProductID])

      ,[Name]

      ,[ProductNumber]

      ,[MakeFlag]

      ,[FinishedGoodsFlag]

      ,[Color]

      ,[DaysToManufacture]

 FROM Production.Product

group by [Name]

      ,[ProductNumber]

      ,[MakeFlag]

      ,[FinishedGoodsFlag]

      ,[Color]

      ,[DaysToManufacture]

Which doesn’t really provide any useful information.

A proper use for the group by clause is to specify on what to base the aggregate function (count, sum, avg, max, etc.). By saying “select count(productid)…. Group by daystomanufacture” the developer is requesting a listing of the count of products FOR EACH value in daystomanufacture.

The where and having clauses can change these numbers. Where is used for pre-aggregation filtering, and Having is used for post-aggregation filtering. Consider these two queries

SELECT count([ProductID])

      ,[DaysToManufacture]

 FROM Production.Product

where daystomanufacture<2

group by

      [DaysToManufacture]

And

SELECT count([ProductID])

      ,[DaysToManufacture]

 FROM Production.Product

group by

      [DaysToManufacture]

having count(ProductID)>7

The first will return the rowset where DaysToManufacture, a field in the table, is less than 2. The second will return the rowset where the COUNT of productid, a calculated aggregate is >7

Select Distinct returns records that are not identical. THIS DOES NOT FIX PROBLEMS WITH INCORRECT CARTESIANS!

Select a, b from tblsomething

Select distinct a, b from tblsomething

Joins

Properly joining two tables or views is a bit of an artform. Incorrect but functioning joins cause hours of annoyance to developers, especially when they’re doing reporting and trying to figure out why their totals are 2x the expected results (usually a Cartesian because of multiple matches caused by not enough conditions in the join).

There are 4 (well 5) types of joins: Inner, left outer, right outer, full outer, and Inner with a different syntax (a leftover from older versions). Let’s take 2 tables, a, and b. Joined on column A.ID and B.AID

A inner join B will return everything in A and B where A.ID and B.AID are equal. Only records that match in both will be returned.

A left outer join B will return everything in A and only records in B where B.AID=A.AID and will return null for the B portion of the record where there is no match

A right outer join B will return everything in B and only records in A where B.AID=A.AID and will return null for the A portion of the record where there is no match

A full outer join B will return everything in A and B with nulls for no matches

Cartesians and Many-to-one or many-to-many joins

Joins which have multiple matches between the tables will return all combinations of the match

A result of an inner join between these tables on A.ID=B.AID would look like this

Inner join has 2 syntaxes:

select ... from a inner join b on a.ID=b.AID

and

select ... from a, b where a.ID=b.AID

these work the same way, but the older style (using the where) is much harder to quickly read to the developer (the conditions of the join are not next to the join, but instead in the where clause). Also, the outer joins don’t have a similar syntax, so you can end up with mixed syntax in the from clause. What an eyesore!

Views

Views help organize the logical structure of the database and reduce code clutter. They can also be indexed for faster search results (useful for improving performance)

A view is basically a query stored for use by other queries. For example to get products and some related info to them we can create this view.

create view dbo.ProductsCategories

as

select

      ProductID,

      prod.[Name] as ProductName,

      ProductNumber,

      subc.[Name] as SubCategoryName,

      prodc.[Name] as CategoryName

from Production.Product prod

      left outer join Production.ProductSubcategory subc

            on subc.ProductSubcategoryID=prod.ProductSubcategoryID

      left outer join Production.ProductCategory prodc

            on subc.ProductCategoryID=prodc.ProductCategoryID

Notice a couple things: with [Name] brackets are used to differentiate between a SQL keyword and a field. Prod, prodc, subc are aliases for tables used to reduce clutter and typing.

This view could be used as

select * from ProductsCategories

or more appropriately since “select * “ is a major no-no for sql (hurts performance, readability, and is just bad form)

SELECT ProductID

      ,ProductName

      ,ProductNumber

      ,SubCategoryName

      ,CategoryName

 FROM ProductsCategories

Also this view can be used in joins

SELECT p.ProductID, pc.ProductID from

Production.Product p

inner join ProductsCategories pc on p.ProductID=pc.ProductID

Stored Procedures

An absolute must for SQL development, stored procs allow you to keep data access code and logic outside of your web projects.

create proc dbo.SelectProductByIDorName

      @productid int,

      @name varchar(50)

as

Select

      ProductID,

      Name,

      ProductNumber,

      Color,

      ReorderPoint

from

      Product

where

      ProductID=@productid

      or [Name]=@name

here we create a stored procedure that returns a rowset of products that are matched on the parameters passed to it.

This can be called using

exec SelectProductByIDorName 1,''

or

SelectProductByIDorName 1,''

Or

DECLARE @RC int

DECLARE @productid int

DECLARE @name varchar(50)

select @productid=1, @name=''

EXECUTE @RC = [SelectProductByIDorName]

   @productid

 ,@name

Similar things can be done for insert, update, delete, and nearly anything else

Also, parameters for stored procs can go both ways, both input and output.

Input parameters are shown above, they are used to feed information to the procedure.

Output parameters can be used to return data from the procedure that is not in rowset format.

CREATE PROCEDURE dbo.spReturnAndOutputTest

    @OutTest int OUTPUT

AS

 SET @OutTest = 9

 RETURN 1

Calling this proc

declare @outtest int

exec spReturnAndOutputTest @outtest OUTPUT

select @outtest

Here we declared a variable to store the results from the stored procedure call. Called the proc passing it the variable, and selected the result

saw a sneak preview of Zohan. 
it's surprisingly good (not the typical adam sandler role). recommend seeing it definately. It's a weird mix of Munich, West Side Story, Shaft, Little Nicky and something else that i can't put my finger on
it's a spoof about an israeli special forces dude that wants to move to america to cut hair. 
movie contains: 
-constant jokes at the expense of Humus ("a tasty middle eastern substance that looks like diarrhea")
-stereotypical palestinian cab drivers
-stereotypical everyone really (jewish electronics store, arab terrorist with 20 hot wives,etc.)
-entirely too much use of the definite article "the"
movie does not contain: 
-present tense conjugation of the verb "to be" namely "is", "am", etc. (i.e. "i go to store")

 Billy`s band  (kupchino stolica mira)
Marksheider Kunst