SS SLUG: June 2010 Meetup

It's time again for another edition of the SQL Server Sri Lanka User Group meeting. Please see image below for all the details.

.net articles will be coming soon....

OUTPUT Clause (Transact-SQL)

This Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Here I use the AdventureWorks sample database to show the sample T SQL for OUTPUT clause.

USE AdventureWorks;

BEGIN TRANSACTION;

DECLARE @testoutput TABLE

(

SalesOrderID int

, SalesOrderDetailID int

, QtyBefore int

, QtyAfter int

, ModifiedDate datetime2

, UserName varchar(30)

)

SELECT * FROM @testoutput;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

OUTPUT inserted.SalesOrderID

, inserted.SalesOrderDetailID

, deleted.OrderQty

, inserted.OrderQty

, GETDATE()

, SUSER_NAME()

INTO @testoutput

WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1;

SELECT * FROM @testoutput;

ROLLBACK TRANSACTION;

SELECT OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1;

SSSLUG May 2010 Meetup

We have organized the May 2010 meetup of the SS SLUG for May 19. Please see below details regarding it. If you are in the Colombo area on that day, we would be happy to see you there. For more information checkout the SS SLUG home page.

.NET User Group Meeting May 2010

Basic transaction concepts

This is mostly referred as "ACID" properties.

So, what are they?

• Atomicity - When Two or more transactions are taking place, either all of them are committed or none of them are committed.

• Consistency - That is, after a transaction, valid and new data exists in the database or the data returned to its original state. ROLLBACK function provides the facility of returning data to its original state.

• Isolation – During a transaction, data must be in an isolated level there by not allowing other transactions to access it.

• Durability - Data is still available even if the server fails. This is achieved by CHECKPOINTS and DATABASE RECOVERY PROECESS.

Correlated Subqueries

Any queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

This query retrieves one instance of each employee's first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables.

USE AdventureWorks;

GO

SELECT DISTINCT c.LastName, c.FirstName, e.EmployeeID

FROM Person.Contact AS c JOIN HumanResources.Employee AS e

ON e.ContactID = c.ContactID

WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson sp

WHERE e.EmployeeID = sp.SalesPersonID) ;

GO

And the result is,

LastName FirstName EmployeeID

------------------ -- ------------------ -----------------

Ansman-Wolfe Pamela 280

Saraiva José 282

(2 row(s) affected)

Correlated subqueries can also include table-valued functions in the FROM clause by referencing columns from a table in the outer query as an argument of the table-valued function. In this case, for each row of the outer query, the table-valued function is evaluated according to the subquery.

Data Compression in SQL Server 2008

Estimate the Savings in Storage Space Using Inbuilt Stored Procedure..

sp_estimate_data_compression_savings inbuilt system stored procedure gives the DBA the facility of estimating the saving in storage space . The mentioned TSQL Query shows the space saved when Row Level Compression is enabled for Production.TransactionHistoryArchive Table of AdventureWorks Database.

The attached SQL code snippet shows that there are columns namely size_with_current_compression_settings (KB) and size_with_requested_compression_settings (KB) which provides you an estimate on the amount of disk space which you can save for Production.TransactionHistoryArchive table.

Estimate the Savings in Storage Space Using Data Compression Wizard
SQL Server 2008 inbuilt wizard Data Compression Wizard provides the facility of estimate the savings of storage space when compression is enabled for a particular table or index in a database.

To do that:

• Click on the AdventureWorks and under the tables find "Production.TransactionHistoryArchive".
• Right Click on the Production.TransactionHistoryArchive and then click on "Storage" and then "Manage Compression".
• Click on next

• After that click on "Row" from the Compression type drop down list.

• Select the compression type option as Row and click the calculate button on the wizard, it will calculate the amount of storage space required when row compression type is used.

• Click Next to go to; Select an Output Option Page. There are many options such as create a script, run immediately, or create a Schedule. Click Next to continue with the wizard.

• Finally Click on Finish.

Database Design Mistakes

Hi All

This is my first article. hooray for sql server and my friends specially Gogula (MVP - SQL Server), Dinesh P (MVP - SQL Server) and a lot more.. and more specially Roshan M Karunaratne my Head of IT. Thank you all for yours' valuable information...

Let's back to our topic.

Since this is my first article, I would prefer to present this as simple as possible.

So, as developers (.net, database or another technology developers) we make mistakes and make sure that the same mistake will not repeat in the future.

Hopefully, after reading this article, you will realize that what practices and terms should be considered when designing a database.

So, here are the things...

• Poor designing / Planing.
• Normalization issues.
• Proper naming standards.
• Documentation issues.
• One table to hold all domain values.
• Using identity/guid columns as your only key.
• Not using SQL facilities to protect data integrity.
• Not using stored procedures to access data.
• Lack of testing.

I will be discussing these issues one by one in my next post and it will be available for you by 28th of Feb 2010.

Thanks