SQLWebpedia

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 PIVOY, here is a way to do it using only REPLACE.

When a database starts showing signs of an illness, it's up to the DBA to get to the root of the problem, fast. Kat Hicks takes a look at the most common causes of database troubles, free tools that can help, and the misconceptions that get in the way.

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".

You've just become responsible for a database, only to find that the log file is growing out of control. Why is it happening and what do you do to correct it?

When you need to shred just part of the data within a large XML file into a SQL Server table, the most efficient way is to just select what you need via XQuery or by using XPath, before shredding it into a table. But precisely how would you do that?

Normally, the query Optimiser won't consider 'bushy' plans, where both operands to a join operator could be intermediate results from other joins. This means that it won't produce good query plans for some types of query. Hints, by themselves won't do it. More powerful magic is required.

For some time, Microsoft had a few window functions, but not the full set specified in the SQL 2003 standard. Now, in SQL Server 2012 we have the whole range, and extremely useful they are too. There's no longer an excuse to avoid them, particularly now you have Rob's gentle introduction.

Alex talks through a simple practical example of a database deployment, First creating a empty database and then upgrading it through three steps by writing T-SQL scripts, adjusting configuration files and the change log, before generating a full build script containing all schema objects.

The performance of 'extract, transform, load' (ETL) processes for large quantities of data can always be improved by objective testing and experiment with alternative techniques. The cause of poor performance can sometimes be surprising.

SQL Server's FileTable technology is an intriguing way of accomodating file-based text data in a database, and allowing for complex searches. As with most technologies, the best way of learning them is to try it out and experiment. Feodor shows how to set it up, add some sample data and set up full-text search.

Often, an existing database application must evolve quickly by incremental steps. Alex describes a tried and tested system to provide an automated approach to deploying both new and existing database systems, whilst dealing with common security and configuration issues.

If you need to optimise SQL Server performance, it pays to understand SQL Server Statistics. Grant Fritchey answers some frequently-asked questions about SQL Server Statistics: the ones we somehow feel silly asking in public, and think twice about doing so.

SQL Server isn't usually the best place to format dates or currency as strings. It can be a complex task to conform correctly with national and cultural conventions. Just occasionally, though, you need to do it. This is easy in SQL Server 2012, but if you aren't using that, what do you do?

How does a Database Consultant use a tool such as SQL Monitor? The seasoned expert doesn't just dive into DMVs before considering baseline information, application stress and the interrelation of different factors over time, to understand what's changed. It is the art of putting performance data in context.

Although databases have no inherent way of recording their version numbers, SQL Server provides the means of doing so, and much more besides. This is a great advantage to anyone faced with the task of deploying databases without errors.

Extended Events provide a way of unintrusively monitoring what's going on in a SQL Server instance. Unlike SQL Server Profiler and SQL Trace, it has little performance impact. Now, in SQL Server 2012 SSMS, it is relatively easy to use, as Robert Sheldon shows.

Discover the best backup and recovery articles on Simple-Talk, all in one place.

(Note: I have re-written parts of this post in the light of the comments that SP1 of 2012 include Complete tools.) I have decided to revisit the topic of whats included in Express Edition, with focus on the tools. I have a couple of reasons for this:...(read more)

Traditionally, Database Deployment is a process that isn't associated with smiles, bonhomie and tranquility. There are a number of challenges that make the task more difficult. Alex reviews the common techniques for deploying new databases and upgrading existing ones, and their flaws, and argues the advantages of an automated, incremental, script-based approach to deployments

SQL Server's Query optimiser judges the best query plan from the data in the relevant tables and the server's hardware. How, then, can you investigate the query plans being generated for slow-running queries on a customer's production server when you can neither access the server, nor recreate the database from a backup?