-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Design Patterns

I am Dedicated Agile champion, Database Manager and Solutions Architect with two decades of experience in improving database functionality, diagnosing problems, building successful project teams, and improving SDLC and business processes. I love data and databases and the challenge that comes with designing solutions. This blog is an attempt to share my knowledge with those who are interested.

Contextual views Part II or how to hide sensitive data

In this article I continue to explore the ways to secure the data via contextual views and describe the technique of hiding the data in the logical layer utilizing business rules. Click here to read full article.

Read more

0 comments, 30 reads

Posted in SQL Design Patterns on 18 October 2018

Pagination pattern or how to fetch a large dataset for a web report.

In this post we will discuss on how to add ability to fetch data from a large dataset for web applications using pagination technique. Click here to read full article

Read more

2 comments, 101 reads

Posted in SQL Design Patterns on 17 October 2018

Lookup. Foreign Key Constraint vs Check Constraint

This post helps in deciding between choosing foreign key and check constraints in case we need to constraint the data in a field. Click here to read full article

Read more

1 comments, 141 reads

Posted in SQL Design Patterns on 16 October 2018

Decision tree on the pattern of choosing how to pass data to a procedure

We are often required to pass the data between different database procedures and functions. In this post I will review options available for us to do so. Click here to read full article

Read more

0 comments, 191 reads

Posted in SQL Design Patterns on 6 September 2018

How to pass data between procedures using temp tables.

In this post I will show how in some cases we can split a long procedure to a small and manageable parts using a simple technique of passing data in temp tables

Read more

1 comments, 1,515 reads

Posted in SQL Design Patterns on 23 May 2018

Helper function to convert binary to decimal and back

In this post you will find two small functions that convert binary value to a decimal one and vice versa.

Read more

3 comments, 282 reads

Posted in SQL Design Patterns on 19 May 2018

How to deploy data for the lookup tables, small dimensions and other meta-data with your database code

Deployment process equally responsible for deploying both database objects as well as an initial state of the database/applications. In this post, I will talk about different options on how to bring your own meta-data with the deployment

Read more

3 comments, 478 reads

Posted in SQL Design Patterns on 10 May 2018

Helper procedure to dynamically execute list of statements

I try to avoid dynamic SQL as much as possible and consider it as a necessary evil. However, in some cases, dynamic SQL is the only way. Often, especially while wringing maintenance script, we prepare dynamic scripts that we need to execute statement by statement. In this post, I will…

Read more

2 comments, 2,350 reads

Posted in SQL Design Patterns on 9 May 2018

Best-practices, guiding principles of choosing procedure parameters

Designing implementation of interface is as critical as designing interface itself. In this post we will discuss some advice on how to choose the right set of parameters for your procedures

Read more

1 comments, 1,059 reads

Posted in SQL Design Patterns on 14 April 2018

Contextual View

In this post I describe technique of presenting data that is context specific - for example, data that will be changing depending who looks at it.

Read more

2 comments, 1,022 reads

Posted in SQL Design Patterns on 11 April 2018

Hide and Abstract the physical layer

How to build an abstraction layer between physical and logical layers in the database code

Read more

5 comments, 83 reads

Posted in SQL Design Patterns on 7 April 2018

Extending built-in data types

add generic user defined table data types as extension to built-in ones

Read more

2 comments, 74 reads

Posted in SQL Design Patterns on 5 April 2018

Soft Delete

How to delete without deleting

Read more

1 comments, 139 reads

Posted in SQL Design Patterns on 3 April 2018

Configuration service database design pattern

In many cases, we are required to store configuration values for an application, application functionalities or user-specific parameters. Normally application-level parameters are stored in the configuration files. However, if the parameters values are driving domain or business logic inside of the database call, then making an external call to the…

Read more

4 comments, 166 reads

Posted in SQL Design Patterns on 30 March 2018

The Journey Begins

Thanks for joining me! Target Audience. Database developers, designers and architects. Background. I am lazy efficient, at least am trying to be. First I coded, and coded, and coded. Over time I realized that I like to code, but more, I like to code smart. So I start to read,…

Read more

2 comments, 164 reads

Posted in SQL Design Patterns on 30 March 2018