Tag: T-SQL

Avoid nested INSERT EXEC (where possible)

Some days ago, I received a tedious error while executing a pipeline of nested stored procedures, which contains INSERT-EXEC statements. The issue is based on a SQL Server limitation, by design since SQL Server 2005. Actually, the INSERT-EXEC was supported in SQL Server 2000, but I’ve never tried that behavior on the older version. Let’s see the behavior. Continue reading “Avoid nested INSERT EXEC (where possible)” →

How to mark SQL Server objects as deprecated with extended properties

I want to share a “trick” on how to mark SQL Server user objects as deprecated. But first, why we need to mark an object as deprecated?

  • code refactors
  • object schema refactors
  • mandatory backward compatibility in a multi customer scenario
We can use extended properties. They are a set of name/value properties in which you can specify custom value, with your own naming convention. Every SQL Server object can be, let’s say, extended using those properties. There are also three stored procedures which you can use in order to manage extended properties:

SSMS Boost another cool addin for SQL Server Management Studio

During my SQL Devlife I tried lots of SQL Server management studio add-ins. Some days ago I tried SSMSBoost. Since I’ve already spoken about SSMSToolsPack and SQLPrompt, I’d like to focus this post on the capabilities that SSMSBoost adds to Sql Server Management Studio. I’ll speak about some of the available features, in the following order:

  • UI/User experience capabilities
  • Connections management
  • Searches
  • Extended menu options
  • Grid commands

Continue reading “SSMS Boost another cool addin for SQL Server Management Studio” →

Repeating rows based on a value in a different column

I’ve just replied to a question in dotnethell italian forum about the topic in the title. Actually it’s not the first time I got this kind of question:

“How can I repeat a row using a value that is stored in another column of the same table?”

 
Ok, let’s describe a solution step by step.

Share t-sql snippets to the team with SQL Prompt

Red-Gate SQL Prompt is a plugin for SQL Server Management Studio that makes the developers life easier when writing t-sql. It provides improved intellisense, indent and coding styles, and also a snippet manager feature that allows the developer to create a set of useful shortcuts. Those autocomplete items can be really useful for a team, let’s see how we can share them in a team.

Continue reading “Share t-sql snippets to the team with SQL Prompt” →

ALTER SCHEMA does not reflect changes into the sys.sql_modules catalog view (definition)

I have recently found a strange behavior related to a comfortable DDL command, the ALTER SCHEMA statement. That happened while I was trying to move a stored procedure from a schema to another one. Both stored procedure and function definitions are stored on a catalog object which is accessible by the sys.sql_modules catalog view (column “definition”). After executing the ALTER SCHEMA statement the definition field is not updated.

We’re speaking about both SQL Server 2012 on premise and SQL Azure databases. My current SQL Server 2012 version/edition is:

Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64) 
Dec 28 2012 20:23:12 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Continue reading “ALTER SCHEMA does not reflect changes into the sys.sql_modules catalog view (definition)” →

SQL Server Logical Query Processing summary

Usually I found some questions on the forums about that topic. For instance:

  1. “WHERE or HAVING?”
  2. “Is the ON clause more efficient than the WHERE clause?”
  3. “Why the field aliases cannot be used with the GROUP BY clause?”
These three questions are syntax oriented. Actually there are a lot of requests about subqueries, temporary objects, sort operations and so on. This kind of questions can be replied reading this logical process document (pdf). I love it and I share it everywhere, also when training in classes/on the job.
It is a LOGICAL workflow used by the Query Processor in order to generate the related PHYSICAL process for retrieving data.