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…

T-SQL: String Search

Please see my other Database Development articles. Returning selection portions of values in a resultset. When searching for specific values within a field, returning portions surrounding the search criteria may be very helpful for providing the user a context in which the value in being used. In this example, I will illustrate searching within a…

T-SQL: Stored Procedure Default Values

Please see my other Database Development articles. Building flexibility into data INSERT operations. Often when designing an application that receives user input, it’s difficult to determine beforehand exactly which fields will be supplied where a user completes a form. SQL Server provides the ability to supply default values for parameters of stored procedures to allow…