viernes, 12 de junio de 2009

Modify tables and columns in design mode

By default, in SQL Server 2008, prevent from saving changes in Design mode.
If you modify some table in design mode, probably appears the message "Saving changes is not permitted"

Desing Mode

Warning Message

For changing this option, go to Tools menu, Options, Designers tree option, and Table and Database Designers option. (You can see in next picture).

I hope that this article is usefull for you.

Returning Ranked Results with Microsoft SQL Server 2005

Numbering Rows with ROW_NUMBER

The ROW_NUMBER function assigns an ordinal value with each record returned, with the ordinal values depending on a particular ORDER BY clause used in tandem with the function. The syntax for ROW_NUMBER is: ROW_NUMBER() OVER([partition] order-by-clause)

For example:

Notice that certain customers are on this list multiple times (Nil, Dani, and Jordi). Perhaps rather than seeing all orders, ordered by sales amount, we are interested in seeing the top orders per customer. We can accomplish this with the PARTITION BY clause in the ROW_NUMBER function like so:

SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount,
ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomer
FROM vwTotalAmountBilledPerOrder AS tab
o.OrderID = tab.OrderID
INNER JOIN Customers AS c ON
c.CustomerID = o.CustomerID

This will return the following results:

Name DateOrdered TotalOrderAmount BestCustomer
Nil 12/1/2005 12649.9900 1
Nil 12/19/2005 265.8500 2
Jordi 12/22/2005 14.9500 1
Jordi 12/18/2005 12.4400 2
Peter 1/2/2006 620.0000 1
Dani 1/5/2006 14.9500 1
Dani 1/4/2006 9.9900 2
MC 1/3/2006 8.5000 1