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 – 5 things SQL Server developers should know about SQL Server

SQL Server developers and SQL Server DBAs meet somewhere in the middle of the development life cycle.  That’s the theory. In reality , with time and cost pressures , the DBA sometimes can be the last to know code is about to go live. There is a responsibility for the Developer to develop code with some understanding of the SQL Database Engine.

 The way in which Developers approach coding has a big impact on the success of the project. Scalable code should be a central principle in coding. The code may work efficiently with a controlled set of data – but how does it scale with 10x the amount of users, or how does it deal with adding a new column a after the go-live date. This is where a good DBA advises and works with Developers to apply tactics for performant SQL Server.

SQL Tuning Advisor

The SQL Tuning Advisor promises much but can also lead to problems.  No in-depth expertise is required, therefore tempting for non DBAs to run a sample workload. The Advisor typically recommends indexes and statistics. Do not just add them without considering the consequences , for example, a) Adding new indexes has an extra storage space requirement b) Extra maintenance is required . How will this impact the maintenance window? 

SQL Server TempDB – Does not retain permanent data

Tempdb is cleared out everytime there is a SQL Server restart.  Think of Tempdb as a temporary data store, for Application data and Internal operations. There is only one TempDB per SQL Server Instance – it is possible a query hitting one database supporting one application can adversely impact another application using another database on the same Instance.

TempDB performance and strategy checklist

Temporary Tables versus Table Variables

 

SQL Query Optimizer is cost based

The SQL Query Optimizer works on a cost-based model . There doesn’t appear to be any obvious measure of acceptance , apart from a comparison to other potential Execution Plans. From a developers perspective , the key to maximising the process is to write efficient code. This means not only focusing on Set- Based principles , but also working with a DBA to study  the Execution Plan .   Ensure Statistics are kept up to date – as Statistics remain a fundamental part of Optimization.

Query Hints are a powerful tool, but be wary of using them. Generally , if the SQL Server is maintained properly , it will choose  a suitable Execution Plan. Generally , I don’t recommend them as there is always potential to create new problems.

 SQL Server Query Optimizer and Statistics

Transact-SQL and the xQuery language

Transact-SQL (T-SQL) is fundamental in using SQL Server. All types of applications use SQL server , but they all use T-SQL to communicate.

Querying xml data requires using the XQuery language. XQuery is used for Structured and semi structured xml data.

Avoid using XML for storing and retrieving data.  Based on experience the XML data type is very slow.

Love your DBA

A good DBA will work hard to monitor and maintain the Database Server. In the background , they will usually be fixing many small bugs and issues that can topple the server .  Database Servers are part of a large IT ecosystem – when you consider SAN storage, backups, high availability, monitoring, networks etc , many things can go wrong.

As the developer cultivate a good relationship with the DBA , for example , set up a review process of new code going into Production. Try not to rush code into Production because a manager is shouting . It’s in everyones interest to maintained optimized code. Contact me for any questions

Related Posts

SQL Server – DBA: Daily Routine

SQL Index Usage

SQL Server Performance Killers

DBA supertech . Specialise or generalise?

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

SQL Server - Stored Procedure Checklist

DBAs find it easier to manage large Database Server environments when checklists are used. This Stored procedure checklist  will stimulate some ideas you could use

For acceptance into the SQL Server environment , I’ve developed some scripts that scan the T-SQL code for some items in this checklist

1)  Same case for T-SQL Keywords. I prefer UPPER CASE , but I know other DBA\Developers like lower case.

2)  Inspect input parameters for valid values (numbers, strings etc)

3)  Force developers to justify  usage of  dynamic SQL (SP_EXECUTESQL, EXEC('SELECT col1…..')

4)  Commenting. I prefer a comment block at the top with usage and revision history . Snippets throughout the code to enhance the understanding

5)  Limit xp_cmdshell usage

6)  Think in set theory. Avoid looping  such as cursors.

7)  Clean up . Close objects ,drop #temp tables, deallocate\close cursors, close transactions

8)  Use try...catch blocks. Trap errors and report into a log file

9)  Performance is satisfactory

10) Investigate input parameters for SQL injection attacks. Better if it occurs at the application level .

 11)  Manage  security. Follow the SQL Server Security Policy.Access to stored procedure and underlying objects

Read More

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

Last time a stored procedure was executed - SQL Server DBA

Grant execute on all stored procedures - SQL Server DBA

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

I spoke to a developer today about the differences between a stored procedure and ad-hoc parameterized sql statement. The conversation focused on – “Is a stored procedure faster than an ad-hoc parameterised query?” The short answer : “it depends”

Firstly some definitions:

According to BOL : “A stored procedure is a saved collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters.”

A parameterized query is defined when using sp_executesql or the forced parameterization is enabled.

 An example of sp_executesql

 

EXECUTE sp_executesql

          N'SELECT col1 FROM Companies 

          WHERE CompanyID = @companyID',

          N'@companyID INT',

          @companyID = 3;

Forced parameterization in BOL “When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement, submitted in any form, is converted to a parameter during query compilation”

 Ad-hoc query create execution plans. Stored procedures create execution plans. What are the differences and similarities?

1)  Stored procedures don’t have syntax checked at run-time. Already done at compilation.

2) Ad-hoc queries upon query execution  – syntax is checked , compiled , placed into memory and executed.

3)  Plans remain in memory until space is required.Depending on activity it can eventually get flushed

4) Stored procedures , once compiled , don’t need syntax checked. Check SQL Server query plans in cache

Questions to ask Developers

1) What is the purpose of the application?

 2) Is it a distributed point-of-sale application?

 3) Is it a Decision Support System?

  4) Will multiple developers need access to sql code?

  5) Are the query requirements suitable to ad-hoc queries or stored procedures?

Map out the performance stack of the application and create benchmarks. Use the benchmarks to gauge performance.

 

Related Posts

SQL Server - Stored Procedure Checklist

How to report a SQL Server performance problem

 SQL Server - Last time a stored procedure was executed

IT Job Search Engine

I've been working on integrating RSS feeds into a SQL Server db . Using OPENXML , you can get some powerful inputting , without to much data manipulation.  This ITjobfeed job engine grabs the feeds from a list , via DTS , scans for syntax errors  and then enters link and meta information into the db .

It would be useful to see more xml lists under certain sectors to have the same data structures.

They are inviting more companies,organisations,agencies to supply them with feeds to expand the search engine

ITJobfeed  was sold to a large job site company - and have now integrated the engine into their services.

Related Posts

OpenXML - more than 8000 characters