SQL TRANSACTION MANAGEMENT WITH JSON INPUT PARAMETER AND NESTED STORED PROCEDURES ON SQL SERVER
This article is aimed to introduce SQL developers to the management of sql transaction with the context of json parameters and nested stored procedures. In order to reuse programmatical object in SQL server (procedures, functions), a SQL developer might need to use nested stored procedures…
Sql server: use of views and filtered indexes
Hello, I have been working lately with several companies, within Ecommerce, telecommunications and payments industries and they all had a team that needed to work on looking at last week or last month invoicing and ordering from clients. The issue was that for 2 companies,…
SQL server: Purging data using switch out
On very large database tables, most of my clients have decided to archive the data by extracting them first and then purging old data based on a Date column using something like : DELETE FROM <SchemaName>.<TableName> WHERE <DateField> < GETDATE() – X days. Not only…
Clustered Columstore and Nonclustered filtered indexes
Hello DBA friends, I had the most bizarre experience on a SQL server 2016 (sp2). On the cumulative hotfixes after, the closest one from the error is this one : https://support.microsoft.com/en-us/help/4316858 It is still not quite the error that I got though. With a client,…
Should you shard or centralize your database ?
Preamble: Nowadays, business tends to get very big database very quickly and they usually need to find ways to manage functionally and physically data in a more efficient matter, both for security and performance reasons. In many ways, centralizing the operating database can make your…
Phase 2: AE and search patterns
Hello again, This time, I implemented the same code but with encryption and hashing, encryption with a symetric key for the email addresses and hashing for the dictionary. This would be similar as implementing random encryption for the email address field and deterministic encryption for…
Phase 1: AE and search patterns
As I was saying in an earlier article, Always Encrypted is a key element of the GDPR compliance implementation: how to encrypt and be able to search the personal data. It is rather better not to be able to search as you can increase encryption…
SQL server: Indexed views and filtered indexes: making your very large database smaller.
Hello, For many clients, I have been able to manage very complex tuning with indexed views and filtered indexes. Indexed views: virtual framework physically maintained! Of course, indexed views are schema binding. Schema binding tables via indexed views offer additional advantage in terms of securing…
SQL server: how to improve multi-threaded inserts on frequently-accessed tables
Hello again, So today we will talk about an issue that I have seen in two of my clients. One of them is a very suffessful Ecommerce that persists the shopping carts (not in cookie) and has millions of users storing in the cart products,…
The dynamic filtered indexes
I have struggled with one client on queries that were dealing with date ranges or type ranges or many filters, on very large tables with low density in many cases, and you knew one index would really improves it but it was based on some filter…
Categories
- Inspiration (1)
- Security (2)
- SQL Architecture (21)
- SQL Monitoring (1)
- SQL Performance (16)
- Tips & tricks (1)
- Uncategorized (5)
- Utilities (1)