SQLServerCentral.com Articles tagged T-SQL

Solving Complex T-SQL Problems, Step-By-Step

Thu, 25 Apr 2013 06:00:00 UT

What should you do if your first, most intuitive solution to a problem ends up scanning the data more than is necessary, resulting in poor performance? Have you missed a new SQL Server feature that can remove inefficiency from your technique? Alternatively, do you need a little help, and some lateral thinking, to open the path to a different approach? Sometimes, the answer is "both".

Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here

Using FULL JOINs to Compare Datasets

Tue, 23 Apr 2013 06:00:00 UT

This article describes a technique of using FULL JOINs to compare two datasets within a numerical tolerance.

12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

Using Regular Expressions to Convert PL/SQL code to T-SQL

Fri, 19 Apr 2013 06:00:00 UT

When converting Oracle PL/SQL to T-SQL, there are a number of ways to do this, but this article shows us how this can be done with regular expressions in Management Studio.

Comparison of Queries Written in T-SQL and SQL Server MDX

Fri, 19 Apr 2013 06:00:00 UT

Beginning to learn and comprehend SQL Server Analysis Services (SSAS) MDX queries can be difficult after one has spent years writing queries in T-SQL. When trying to write SQL Server MDX queries, it's worth considering "How would I write this query in T-SQL?"

The Elusive Conditional WHERE Clause

Thu, 18 Apr 2013 06:00:00 UT

What's this, a conditional WHERE clause that doesn't use dynamic SQL?

The Cascading (CROSS) APPLY

Tue, 16 Apr 2013 06:00:00 UT

An old trick nowadays but one which is still underused here on SSC is the cascading CROSS APPLY – where output from one CROSS APPLY is used as input for another.

T-SQL Coding Style

Tue, 16 Apr 2013 06:00:00 UT

When you write T-SQL code what kind of coding style do you use? Do you have a consistent way to comment and format your code? Does your organization provide standards for how you must code your T-SQL? Join Greg Larsen as he discusses some of the coding styles you should consider when writing your T-SQL scripts and stored procedures.

How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial

New and Basic Result-set Paging functionality in SQL 2012 Version.

Wed, 10 Apr 2013 06:00:00 UT

Overview of 2 very important clauses added to SELECT statement in SQL2012 to allow paging on result sets.

A Simple Formula to Calculate the ISO Week Number

Mon, 08 Apr 2013 06:00:00 UT

He admits it wasn't his idea but his head sure wishes it was. SQL Server MVP Jeff Moden explains a wonderful, super simple, very high performance formula that will calculate ISO Week Numbers. If you're "stuck" with SQL Server 2005 or less, you're going to like this a whole lot!

A Hazard of Using the SQL Merge Statement

Wed, 03 Apr 2013 06:00:00 UT

The SQL MERGE statement can make your DML querying more efficient but you need to take care or you may get burned

TSQL Pivot Rotations using only REPLACE

Mon, 01 Apr 2013 06:00:00 UT

Pivoting SQL Server tables is always awkward, even with the PIVOT and UNPIVOT operators. If you want to get the job done without GROUP BY or PIVOT, here is a way to do it using only REPLACE.

Re-throw Errors with Logging

Thu, 28 Mar 2013 06:00:00 UT

This article brings you a technique and framework to use in your stored procedures that can allow you to re-throw and log errors.

HASHBYTES: Is CHECKSUM really required?

Wed, 27 Mar 2013 06:00:00 UT

When compared with HASHBYTES, CHECKSUM, has a number of drawbacks. Hence, a question that comes up is: Is Checksum really required? In this article, I attempt to answer that question.

Search for a Value Throughout Your Database

Thu, 14 Mar 2013 06:00:00 UT

Learn how to search for a value throughout all database tables.

Deleting large number of rows from a table in a VLDB

Fri, 08 Mar 2013 07:00:00 UT

For enterprise systems, purging data is a reality. Today, we will see some strategies that I recently implemented to make this process work efficiently.

Hidden Tricks To SQL Server Table Cleanup

Wed, 06 Mar 2013 07:00:00 UT

How to delete millions of rows with virtually no contention.

Using a Variable for an IN Predicate

Fri, 01 Mar 2013 07:00:00 UT

How to use a passed string variable for an IN type predicate in your queries as a way to more efficiently handle an array of strings.

Data Profiling with T-SQL

Mon, 25 Feb 2013 07:00:00 UT

Need to understand new data? This article explains why - and how you can profile it efficiently

An in-depth look at change detection in SQL Server - Part 02

Tue, 19 Feb 2013 07:00:00 UT

Auditing, conflict resolution, tamper & concurrency protection are some of the most common requirements for any enterprise system – this 2-part series presents an in-depth look at the various change detection mechanisms available within SQL Server.

SQL Server 2012: Time Marches On

Wed, 13 Feb 2013 07:00:00 UT

Greg Larsen explores the new SQL Server 2012 date and time functions and shows you how to exploit these functions in new application code.

Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here

An in-depth look at change detection in SQL Server - Part 01

Tue, 12 Feb 2013 07:00:00 UT

Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.

T-SQL insert using SSIS Data Pump

Tue, 05 Feb 2013 07:00:00 UT

This article describes a T-SQL procedure to insert data using the SSIS Data Pump

Silent Truncation of SQL Server Data Inserts

Fri, 01 Feb 2013 07:00:00 UT

There are certain circumstances where SQL Server will silently truncate data, without providing an error or warning, before it is inserted into a table. In this tip we cover some examples of when this occurs.

VARCHAR (MAX), NVARCHAR (MAX) and NTEXT Data Types

Mon, 21 Jan 2013 07:00:00 UT

Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, I will briefly demonstrate the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types, and the impact on performance from using NTEXT/TEXT.

Stairway to T-SQL DML Level 12: Using the MERGE Statement

Wed, 16 Jan 2013 07:00:00 UT

The final level of this stairway looks at the MERGE statement in detail, focusing on how to perform insert, update and delete logic using the MERGE statement.

SQL Server T-SQL Tuning – NOT IN and NOT Exists

Wed, 16 Jan 2013 07:00:00 UT

Following on from the previous TSQL performance tuning article in this series, we now focus on the important topic of T-SQL “Not IN” And “Not Exists “

Using a TSQL semaphore in SSIS

Mon, 14 Jan 2013 07:00:00 UT

This article shows how you can use a TSQL semaphore in SSIS.

SQL Server T-SQL Tuning -TVF and Scalar Functions

Thu, 10 Jan 2013 07:00:00 UT

A UDF is very convenient for centralising business logic as we can specify a set of business logic in one UDF which references multiple stored procedures and ad-hoc queries. However, they can lead to significant performance degradation due to their demands on the CPU

SQL Server Rounding Functions - Round, Ceiling and Floor

Wed, 09 Jan 2013 07:00:00 UT

I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.

Single Quotation Marks in SQL

Thu, 03 Jan 2013 07:00:00 UT

One of the common problems is dealing with apostrophes in T-SQL. This article examines the challenges of single quotation marks and ends with a short quiz.