Partial stored procedures in SQL Server June 8, 2018 by Gerald Britton Introduction SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility. Read more »
Restricting and monitoring SQL Server data access with views and procedures May 17, 2018 by Timothy Smith We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users? Read more »
Debugging stored procedures in SQL Server Management Studio (SSMS) February 20, 2018 by Ben Richardson Debugging is one of the most important but painful parts of any software process. To find some errors you have to run the code step by step to see which section of the code is responsible for the error. This is called runtime debugging. Luckily, SQL Server Management Studio (SSMS) comes with automated debugging capabilities to help developers debug their scripts. In this article, we will explain practically how SSMS can be used to debug stored procedures in SQL Server by working through a very simple example. Read more »
How to email SQL query results to your smartphone using the sp_send_dbmail stored procedure July 24, 2017 by Daniel Calbimonte Introduction Sometimes there are tasks that require too much time to finish during business hours and we need to leave the office with some still pending like the migration of data, backups or long running queries. In such cases, we would still want to receive an email in our smartphones to make sure that the job was completed successfully. Read more »
Functions and stored procedures comparisons in SQL Server February 20, 2017 by Daniel Calbimonte Introduction In a previous article, Functions vs stored procedures in SQL Server, we compared Functions vs stored procedures across various attributes. In this article, we will continue the discussion. We will talk also about Table-valued functions and compare performance with stored procedures with table valued functions and scalar functions. Read more »
Functions vs stored procedures in SQL Server February 14, 2017 by Daniel Calbimonte Introduction Usually DBAs prefer stored procedures instead of functions in SQL Server. Is this a good practice? Read more »
Creating a gap in sequences – TSQL Stored Procedure advisor January 6, 2016 by Eli Leiba Introducing the Problem Gaps existence in automatic sequenced columns occurs all the time. Missing identity values (or other sequencing values) occur for a variety of Reasons. The most common reasons include: roll backed transactions, failed inserts and Deletes, large row deletes after delete commands that occur after many inserts to a sequenced table and so forth. Read more »
Creating and using CRUD stored procedures April 7, 2014 by Milica Medic A typical N-tier application design contains of the several layers from the client (Web or Windows), to the Business Logic Layer, to the Data Access Layer, and finally to the Data Storage Layer. Read more »
