SQL Server –Semicolon in sql

Not using a statement terminator – such as semicolon (;)  for T-SQL statements is a deprecated feature in SQL Server.  It’s a feature that will not be supported in a future SQL Server version – not as yet specified.  For the DBA this raises the question of when and how to enforce this change.

Depending on the SQL Server environment  there  may be multiple SQL Server Instances , different groups of developers, supporting in-house and third  party databases. All will probably have different levels of controls around coding the  semicolon in sql

The semicolon is included in the ANSI SQL-92 standard. Before some recent features (more later) coding in T-SQL required no semicolon for parsing or execution. Most developers who implement code on servers I manage don’t feel it’s necessary to use a semicolon – they argue against the extra typing and no rule enforcement

Some arguments to convince SQL developers it’s time to start implementing the semicolon

1) When using a Common table Expression(CTE) and the CTE is not the first batch statement , it’s mandatory to use a semicolon

2)  If a Service Broker statement is issued and the Service Broker statement is not the first statement in the batch a semicolon must be used.

3)  If a MERGE statement is used ,  a semicolon must be used

4) Easier manageability of splitting code

5) Makes the code more transportable

6)  Easier for an outside coder to understand the code breakdown

Let me know your opinion

Related Posts

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server Performance Killers

Stored Procedures versus ad-hoc paramaterized queries.Which is faster?

ISO-11179 Naming Conventions and SQL DDL

SQL Server – Create a comma delimited sql result set

Use the SQL COALESCE  function to create a comma delimited sql result set.

Normally , I disagree with  the SELECT statement managing client formatting issues .The application layer should deal with formatting the SELECT statement output to the client.

But ,sometimes, if there’s no application layer the SELECT is forced to manage formatting  issues. One example is producing a comma delimited result set from a sql statement. Usually a sql statement will produce a result set   , the result set is placed into a recordset , and depending on how the application sets the recordset properties , cursor behaviour is possible.

On this occasion , a developer requested I create a result set which returned one row , and that row containing a comma separated result set.

With a  COALESCE function combined with a CTE  , it is possible to create a comma delimited resultset

In this example, the CTE creates a temporary resultset of all databases on one drive with all databases that had a differential backup of more than 30 GB . The SELECT statement below , places the COALESCE results into the @CSVlist variable.

 

DECLARE @Quoted_Ident char(1); 
SET @Quoted_Ident = '';  
DECLARE @Delimeter char(1); 
SET @Delimeter = ',';
DECLARE @CSVlist nvarchar(max);
DECLARE @drive1 CHAR(1);
SET @drive1 = 'P';

WITH Results_CTE AS (

SELECT 
bus.database_name as 'dbname'
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id
WHERE 
bus.backup_start_date > GETDATE() - 7
and bus.[type] = 'I'
and    CAST(bus.backup_size /1024/1024/1024 AS DECIMAL(10,2))> 30.00
UNION 
select 
name
from sys.sysdatabases where LEFT(RTRIM(LTRIM(filename)),1) IN (@drive1)
)
SELECT @CSVlist = COALESCE(@CSVlist + @Delimeter, '') + @Quoted_Ident + dbname
FROM Results_CTE order by dbname 


select @CSVlist

Related Posts 

SQL Server CTE and PARTITION BY

Export-CSV Powershell

SQL Server - Bulk Insert CSV into a SQL Server table

SQL Server - User Could Not Execute Stored Procedure sp_enable_sql_debug

Question:  When running SQL Debugging I get this SQL error. How can I fix?

 

 

The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

 

 

Answer: SQL Debugging requires sysadmin permission. This is unfortunate for DBAs , as they are forced to give developers sysadmin privileges, which usually  is against most SQL Server Security Policies.

As a rule I don’t give developers Sysadmin rights . There normally has to be a strong case for sysadmin rights

As a note from Microsoft

“We recommend that you debug Transact-SQL code on a test server, not a production server, for the following reasons: Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server. Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session has ended or the transaction is committed or rolled back.”

Related Posts

SQL Server – How to troubleshoot query timeouts

SQL Server - ALTER TRACE and developers

SQL Server Security Policy

SQL Server – Aggregate expression containing an Outer Reference

Question: How do I fix a query with an aggregate expression containing an outer reference returning this error message:

 

Msg 8124, Level 16, State 1, Line 4
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

 

Answer:  In summary,  SQL Server experiences problems when  mixing outer and inner references for aggregate expressions. SQL Server has to solve the problem of which query it will use  for aggregate computation

The first query tries to create a SUM with a reference from the inner query to the outer query. A simple solution is to create another table instance as part of the inner query – and correlate it  with the outer table by ensuring the JOIN is based on the outer table key. This way you are recreating the original attempt of joining an inner query to an outer query.

On the second the query, you’ll notice the addition of a JOIN to the table based on the same key as the outer table.

IF OBJECT_ID('TempDB..#tmpSaleAmt','U') IS NOT NULL 
DROP TABLE #tmpSaleAmt



CREATE TABLE #tmpSaleAmt (
[id] [int] IDENTITY(1,1) NOT NULL,

AmtSld [decimal](13, 2) NULL,
AmtLeft [decimal](13, 2) NULL


)

SET IDENTITY_INSERT #tmpSaleAmt ON
iNSERT INTO #tmpSaleAmt (ID, AmtSld,AmtLeft ) 
SELECT '1','122.00',0 UNION ALL
SELECT '2','103.00',0 UNION ALL
SELECT '3','57.00',23.000 UNION ALL
SELECT '4','700.00' ,700.00 UNION ALL 
SELECT '5','300.00' , 300.00


--===== Set the identity insert back to normal 
SET IDENTITY_INSERT #tmpSaleAmt OFF


---works
select   AmtSld 
	,(SELECT  
            SUM(CASE WHEN a.ID < o2.ID THEN a.AmtSld ELSE 0 END) AS PrevAmt
            FROM #tmpSaleAmt a
            JOIN #tmpSaleAmt AS O2 ON a.ID = O.ID
          WHERE a.ID <= O.ID)t
	 
from #tmpSaleAmt O


--fails
select   AmtSld 
	,(SELECT  
            SUM(CASE WHEN ID < o.ID THEN AmtSld ELSE 0 END) AS PrevAmt
            FROM #tmpSaleAmt a
          WHERE ID <= O.ID)t
	 
from #tmpSaleAmt O

 Related Posts

SQL Server - UPDATE JOIN on a table

JOIN IN SQL FAQ

Why does Query Optimizer choose a Clustered Index Scan?

Last DML operation

Question : How can I  find the last DML operation on a SQL Server database?

Answer:  Firstly , ask yourself why you need to find the last DML operation. The answer may influence the technique used. For example, is  to troubleshoot a suspected row DELETE or are you troubleshooting a SSIS workflow task?

A combination of techniques may be used – a then construct a picture of the change.

Methods to get the Last DML operation

 1)       Is Change Data Capture (CDC) enabled? The CDC captures both the DML change and the actual data changed. Before SQL Server 2008 , a DBA required custom development using triggers, schema changes and stored procedure to capture changes. This provided some challenges to the DBA , such as having to track source table schema changes.  

2)       Do you have Change Tracking enabled?  Change Tracking is available. If enabled, applications can query changes to data in a database. A typical question may be “what rows have changed?” .

The difference between CDC and Change Tracking is that CDC captures both the DML occurrence and the actual data changed, whereas Change Tracking captures only the DML occurrence change.

3)        Find the tables you think are most commonly used. Do they have a timestamp column? Are there any other clues , such as Logon ID – which may indicate who and when the change was last made.

4)       Analyse the Security section in Event Viewer. If Active Directory Authenticated security is used – it may be easier to track the Logon account to a specific individual. It can be  difficult to estimate when a change occurred and then map to a specific logon through the Security Logs. Event Viewer security logs grow quickly on a busy Production SQL Server.

5)       Read  the Transaction Log with the  undocumented function fn_dblog().

Read More

How to request SQL Server troubleshooting

SQL Server – default trace FAQ

SQL Server – Audit Backup\Restore Event with SQL Default trace