Practice 1 Using the AdventureWorks database, you will design a solution using query notifications. To do this, you will need to perform the following steps:
Create a Service Queue using Transact-SQL code in Query Analyzer.
Create a Visual Studio application (Windows Forms or Web-based) that will create the notification and wait for changes. The application will need to create a notification using the SqlNotificationRequest object. It will also need to create a thread that waits for changes. When changes are registered, an e-mail should be sent directly to you.
Manually alter the data in the target table using Query Analyzer.
Ensure that the notification is triggered and the email is sent to your mailbox.
Practice 1
Locate a production SQL Server in your organization (it can be one with an earlier version of SQL Server). Using Query Analyzer or SQL Server Management Studio, examine the stored procedures and
ASP.NET Caching
http://www.
.NET Data Caching
http://aspnet.4guysfromrolla.com/articles/100902-1.aspx
ASP.NET Caching: Techniques and Best Practices
http://msdn2.microsoft.com/en-us/library/aa478965.aspx
Working with Query Notifications
http://msdn2.microsoft.com/en-us/library/ms130764.aspx
Performance Tuning SQL Server Database
http://www.sql-server-performance.com/cursors.asp
Application Development Trends - The SQL Server 2005 Paradigm Shift
http://www.adtmag.com/article.aspx?id=11148&
SQL Server Query Design: 10 Mistakes to Avoid
http://searchsqlserver.techtarget.com/loginMembersOnly/1,289498,sid87_gci1229788,00.html?NextURL=http%3A//searchsqlserver.techtarget.com/tip/0%2C289483%2Csid87_gci1229788%2C00.html
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
How to Perform SQL Server Row-by-Row Operations Without Cursors
http://www.sql-server-performance.com/dp_no_cursors.asp
Using SQL Server Cursors
http://www.mssqlcity.com/Articles/General/UseCursor.htm
SQL Server Clinic: T-SQL Performance Problems and Solutions
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220_tax301334,00.html?adg=301324&bucket=ETA?track=sy41
Page-level and
Query notifications, new with SQL Server 2005, allow your applications to receive notifications when the data in the cache has changed. Eventually, all caches will expire, and there must be a method in place for refreshing the cache. This can be done on demand by the user or at the point the application attempts to access the
A cursor is a server-side object that can be used to access multiple rows one at a time from a result set. Once the cursor is declared, it can be opened and then
When using
You can evaluate the efficiency of your cursors by looking at the execution time. This is done using the New Query window in Microsoft SQL Server Management Studio. You can also use the dynamic management function named sys.dm_exec_cursors to evaluate currently allocated cursors.
Generally, you want to avoid the use of cursors whenever possible. Transact-SQL was designed as a set-based language and not an object-oriented language. Try to use alternatives to the cursor. If you are able to, measure the execution times for the cursor and the alternative and use the