Partition Elimination in SQL Server 2005

Partition function and Partition Scheme Step by Step Example

http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx


Tried the same for Table test1. Deleting data from a Partition
--STEP 1

select $partition.PF1(a) [Partition Number], a, b from test1

--STEP2
--To Delete this data from a Partition in the Table, Create another table and switch the partition
create table test1_truncate (a int, b int)

DECLARE @PartitionNumber INT
SET @PartitionNumber = 1
ALTER TABLE dbo.test1 SWITCH PARTITION @PartitionNumber TO test1_truncate;

TRUNCATE TABLE test1_truncate

--STEP 3
select $partition.PF1(a) [Partition Number], a, b from test1

Partitioning Tips
Handling Large SQL Server Tables with Data Partitioning
Dynamically controlling the number of rows affected by a SQL Server query


Thanks,
Sivaram

Software Testing Interesting Reads - Sara Ford

Very Good Reads from Sara Ford Blog
What are Test Hooks?. With VSTS 2010 You can do a Record and Playback of UI controls
5 Tips for surviving as a Tester - Excellent Article. Key Learnings I would suggest are

• Develop Deep Domain Expertise, Work with Business and Try to master the domain/Application
• Learnt from Mistakes - Production Bugs
• Participate in Design Reviews and question Error Handling/Exception Scenarios
• Encourge situations to work together and Debug the code together with DEV

Automation Testing versus Manual Testing Guidelines
Target Automation for BVT (Build Verification Test), Regression Test. Access ROI (Return of Investment) on Functional Test Automation. If the feature set changes Release over Release then it may not be a right candidate for Automation.

More Good Reads
manual v. automated testing again
the manual v. automated testing debate
10 tips for WebApp Testing
Programming Paradigms in Test Automation
GUI test automation is not child's play
Data-Driven Testing
Automated Testing Basics
SpecExplorer: Model-Based Testing made practicable
Hans Buwalda's Test Automation Ideas
Goals of Test Automation

SQL Server Side Trace explained

As Against using profiler, Server side trace is light weight, runs in the server not at the client end. Details and walk thru provided here http://www.mssqltips.com/tip.asp?tip=1035

Thanks,
Sivaram

Understanding Query Processing and Query Plans in SQL Server

Compiled list of posts for Learning Execution Plan

• Superb presentation avalilable in this link - http://blogs.msdn.com/craigfr/attachment/8508493.ashx
• Execution Plan cost formulas explained here - http://perftuning.com/pdf/ExecutionPlanCostFormulas.pdf
• SQL Server Execution Plan Analysis Video
• Introduction to How to Interpret SQL Server Execution Plans
• Spool operators in query plan...
• Estimated vs Actual Plans
• Sorted Seeks
• Interpreting SQL Server execution plans
• SQL execution plans, part 2: SQL compilation
• Adventures in TSQL: SQL Server Query Performance Analysis using DMVs
• SQL Server Query Execution

Happy Learning!!!

SQL Server Coding Guidelines

Compiled List of posts for TSQL Code Review and Best Practices

• The Curse and Blessings of Dynamic SQL
• Error Handling in SQL 2005 and Later
• Server Side Error Handling - Part 1 (Migrating from @@error to tsql try-catch)
• Server Side Error Handling - Part 2 (Errors and Error Messages)
• Server Side Error Handling - Part 3 (Why do I still see error messages from inside tsql try-block?)
• How to Share Data Between Stored Procedures
• Coding Efficient Transactions
• My stored procedure "best practices" checklist
• SQL Coding Guidelines
• Standardized SQL Server Error Handling and Centralized Logging
• SQL Server 2005 Try and Catch Exception Handling
• Avoid mixing old and new styles of error handling
• Common T-SQL mistakes
• Looking for security vulnerabilities in database code
• Server Side Error Handling - Part 1
• parameter sniffing
• Conor vs. “Does Join Order Matter?”
• Wide vs. Narrow Plans
• division by zero
• use SCOPE_IDENTITY() - @@Identity would not work in case you have a trigger enabled on the source table. Recommended to use SCOPE_IDENTITY()
• Top 10 T-SQL Code Smells
• MSDN presentation on Introducing XML in SQL Server 2005
• Proper T-SQL Stored Procedure Formatting
• The vote is in! (CHECK CONSTRAINTS vs. TRIGGERS vs. STORED PROCEDURE business logic)
• Stored Procedures are NOT evil... but they can be frustrating!
• Stored procedures are NOT bad
• Dr. Unknown, or how I learned to stop worrying and love the NULL
• Ten Common Database Design Mistakes
• Finding stored procedures containing %string%
• What should I be looking for during Code Reviews?

Happy Reading!!!

SQL Server 2008 Migration Guidelines

For SQL Database Migration - Major items to be covered are mentioned in below blog
http://madhuottapalam1.blogspot.com/2008/06/upgrading-sql-server-from-lower-version.html

1. SQL Upgrade advisor - Try running this tool and analyze the recommendations of this tool
http://blogs.technet.com/andrew/archive/2008/03/26/sql-server-upgrade-advisor.aspx
2. Inplace Upgrade /Side by Side Upgrade
3. Test the Rollback strategy, In case upgrade fails how to mitigate it
4. SQL Server 2008 Upgrade Technical Reference Guide

More Reads
http://blogs.msdn.com/psssql/archive/2009/02/19/how-to-copy-dts-2000-packages-between-servers-and-from-sql-2000-to-sql-2005-and-sql-2008.aspx
Upgrading from 32bit to 64bit SQL Servers
SQL 2000 to 2005 migration: execution plan change issue.
SQL 2000 to SQL 2005 migration: temporary tables reuse issue.
Useful links for upgrading to SQL Server 2008
Transparent Benefits of SQL Server 2005
Checklist for upgrading to a new version of SQL Server - Very Good
Resources - Migration from SQL 2000/SQL2005 to SQL 2008
SQL Server 2008 Upgrade Technical Reference Guide


Very Good Tech-ed Talk

Microsoft SQL Server Upgrade, 2000 - 2005 - 2008: Notes and Best Practices from the Field


Happy Reading!!