SQL Server: Maintenance Plan

Please see my other Database Development articles. Ensure successful disaster recovery for databases This section describes steps taken for database to backup data in preparation for a disaster. Recovery Model Of the three recovery models provided by SQL Server for its databases (Full, Simple, Bulk), the Full recovery model was chosen for its ability to…

SQL Server: Disaster Recovery

Please see my other Database Development articles. Ensuring Recovery from a Disaster This section describes how to recover data lost due to a disaster. As described in the last section, each database regularly performs the following backup types: Full: A complete copy of the database at a point in time. This occurs once a week.…

T-SQL: Subquery with Sum

Please see my other Database Development articles. Performing a subquery while Sum-ming a column In this article I will demonstrate how to use a subquery with SUM() to return parent records and related child records while aggregating currency amounts. First, I’ll start with a a couple customer records, each with two order records with a…

Database Development

Leveraging an organization’s data stores is crucial to enabling strategic decision-making. My articles below illustrate skills necessary for an firm to achieve its goals.   MANAGEMENT, UTILITIES Normalization SQL Server: Disaster Recovery SQL Server: Maintenance Plan SQL Server: SSMS Object Explorer Filter xSQL Data Compare T-SQL T-SQL: Binary_Checksum T-SQL: Bitwise Arithmetic T-SQL: Checksum T-SQL: Complex…

T-SQL: Ranking Functions

Please see my other Database Development articles. Ranking Functions There are several new impressive advancements in the latest version of t-sql; this article will focus on “Ranking” functions. Prior to the release of SQL Server 2005 with its t-sql enhancements, working with blocks of related data was clunky at best. T-sql authors mostly relied on…

T-SQL: Common Table Expressions

Please see my other Database Development articles. Performing Recursive Queries. Prior to SQL Server 2005, performing recursive actions on data required the use of temporary tables, cursive, or some other memory-intensive method. Now, performing such operations requires much less code and avoids the previous performance increase. For this example, we’ll begin with a generic table…

T-SQL: Rank()

Please see my other Database Development articles. Ranking Functions There are several new impressive advancements in the latest version of t-sql; this article will focus on “Ranking” functions. Prior to the release of SQL Server 2005 with its t-sql enhancements, working with blocks of related data was clunky at best. T-sql authors mostly relied on…

T-SQL: Format Date Values

Please see my other Database Development articles. Leveraging Format() for greater control over date values. When displaying date values from a resultset, it is common to reformat them according to various specified business rules. Using the t-sql Format along with specifying pattern provides an easy-to-use method, especially when abstracted into a reusable user-defined function.

T-SQL: Row_Number

Please see my other Database Development articles. Ranking Functions There are several new impressive advancements in the latest version of t-sql; this article will focus on “Ranking” functions. Prior to the release of SQL Server 2005 with its t-sql enhancements, working with blocks of related data was clunky at best. T-sql authors mostly relied on…

T-SQL: Synonym

Please see my other Database Development articles. Simplifying T-SQL syntax. Length T-SQL is unfortunately the byproduct of many business requirements in supporting an application. As the parts of each statement grows (database.schema.object), so the overall length of the entire query. Synonyms provide a painless method to drastically decrease code bloat in queries by allowing for…