SqlServerKudos

Query

1
kudos

Kudos

Finding Tables Without Primary Keys

published 1002 days, 23 hours, 35 minutes ago posted by

sa 1005 days, 23 hours, 53 minutes ago

This is a companion post to my earlier post Finding Tables Without Referential Integrity. People have since asked me how to systematically identify all tables that don’t have a primary key. Here’s a simple query to do that for SQL Server 2005 and above... (more)

category: Query | clicked: 1 | comment | | source: sqlblog.com

tags: Primary Key

1
kudos

Kudos

ALTERing a Partition Function

published 1002 days, 23 hours, 35 minutes ago posted by

sa 1006 days, 1 hour, 25 minutes ago

Finally, a real geeky post. I received two questions in as many weeks about what really happens to existing partitions when you alter the underlying partition function. The big question being… does altering a partition function cause any data movement? The answer, as usual, is “It depends” Most people who work with partitioning are aware that the SWITCH operation does not cause data movement. But SWITCH is an option to ALTER TABLE, not ALTER PARTITION FUNCTION. ALTER TABLE SWITCH … allows you to ‘switch... (more)

category: Query | clicked: 1 | comment | | source: sqlblog.com

tags: Metadata, Partition Functions

1
kudos

Kudos

How are auto-created column statistics names generated?

published 1002 days, 23 hours, 35 minutes ago posted by

sa 1008 days, 20 hours, 40 minutes ago

How are auto-created column statistics names generated?

This is a question that came up at the Cleveland SQL Server User Group last night. An attendee had a wager with his colleagues about how the names of auto-created column-level stats are generated... (more)

category: Query | clicked: 0 | comment | | source: www.sqlskills.com

tags: statistics

1
kudos

Kudos

Calculating Running Streak over many records

published 1004 days, 3 hours, 21 minutes ago posted by

sa 1009 days, 21 hours, 9 minutes ago

Calculating Running Streak over many records

It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do. But this will work, and work fast! (more)

category: Query | clicked: 2 | comment | | source: weblogs.sqlteam.com

tags: Calculation, Performance

1
kudos

Kudos

Using CTE (Common Table Expression) and Views to generate data

published 1005 days, 2 hours, 11 minutes ago posted by

sa 1010 days, 22 hours, 10 minutes ago

Using CTE (Common Table Expression) and Views to generate data

Here is how you can use Views and CTE (Common Table BLOCKED EXPRESSION to accomplish generating A - Z and 0 - 9 and get a random selection of that for 'x' number of alphanumeric combinations... In a real system you'd probably store them to inch that extra performance out, but - this is the fun and educational way of building the data.... (more)

category: Query | clicked: 4 | comment | | source: sqlblogcasts.com

tags: CTE, View

1
kudos

Kudos

Spatial Data Type Structure

published 1009 days, 2 hours, 14 minutes ago posted by

sa 1012 days, 23 hours, 27 minutes ago

Spatial Data Type Structure Hi Folks, As part of a larger effort to document our protocols, SQL Server has just released documentation on the structure of our spatial types. The document is also available in PDF. Note that all of this is preliminary, and while I doubt it will change much, it could. I had very little to do with this effort; you have Ed to thank. It turns out that this structure is pretty simple, and there are folks out there who have fairly easily reverse-engineered it. Now that w... (more)

category: Query | clicked: 2 | comment | | source: blogs.msdn.com

tags: Spatial

1
kudos

Kudos

The Query Optimizer and Computed Columns

published 1009 days, 2 hours, 14 minutes ago posted by

sa 1012 days, 23 hours, 35 minutes ago

The Query Optimizer and Computed Columns

In my previous post I talked about contradiction detection as one of the early steps in query optimization. Another interesting step performed during query optimization is the automatic matching of computed columns. Although computed columns have been available in previous versions of SQL Server, the automatic matching feature was introduced until SQL Server 2005. In this post I will show you how this feature works and explain how computed columns can help you improve the performance of your queries... (more)

category: Query | clicked: 1 | comment | | source: sqlblog.com

tags: Optimization

3
kudos

Kudos

The effects UNION in a SQL query « WebbTech Solutions

published 1010 days, 35 minutes ago posted by

joewebb 1017 days, 2 hours, 56 minutes ago

The effects UNION in a SQL query « WebbTech Solutions

In a recent blog, I shared some of the natural but sometimes unanticipated consequences of adding DISTINCT to a SELECT statement. Adding DISTINCT ensures that you don’t have duplicated rows in your result set, but that assurance comes at a price. Performance is typically impacted negatively when you add DISTINCT to a query. You can read the blog and see the proof here. But there is another operator that can have some not-so-obvious side affects. Let’s a have look how UNION can affect a query’s performan... (more)

category: Query | clicked: 4 | comment | | source: webbtechsolutions.com

1
kudos

Kudos

Teaser: Left Join..the SQL ego crusher

published 1010 days, 35 minutes ago posted by

sa 1018 days, 22 hours, 18 minutes ago

One of the more difficult things to master for people coming to SQL seems to be the Outer Join. The common mistake people make is of course putting the condition in the WHERE clause from the outer joined table which should have been in the JOIN itself. There was a question about this on Stackoverflow and a person claimed he did have an Left Join because...well....he had Left Join in his SQL statement...... (more)

category: Query | clicked: 6 | comment | | source: sqlblog.com

tags: Left Join

1
kudos

Kudos

SQL Server – Multiple CTE in One SELECT Statement Query

published 1010 days, 35 minutes ago posted by

sa 1014 days, 22 hours, 54 minutes ago

SQL Server – Multiple CTE in One SELECT Statement Query

I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject. Option 1 : / Method 1 / ;WITH CTE1 AS (SELECT 1 AS Col1), CTE2 AS (SELECT 2 AS Col2) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO Option 2: / Method 2 / ;WITH CTE1 AS (SELECT 1 AS Col1), CTE2 AS (SELECT COL1+1 AS Col2 FROM ... (more)

category: Query | clicked: 7 | comment | | source: blog.sqlauthority.com

tags: CTE, Pinal Dave, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

1
kudos

Kudos

TSQL Challenge - counting non zero columns

published 1011 days, 4 hours, 46 minutes ago posted by

sa 1015 days, 2 hours, 14 minutes ago

TSQL Challenge - counting non zero columnsI'm working on a project where I need to cycle a flag amongst a set of columns. To achieve this I am storing a position value in each column which allows me to cycle them.i.e with columns a to f I have the values as follows... (more)

category: Query | clicked: 3 | comment | | source: sqlblogcasts.com

tags: Puzzle

1
kudos

Kudos

Aaron Bertrand : More on splitting lists : custom delimiters, preventing duplicates, and maintaining order

published 1011 days, 4 hours, 46 minutes ago posted by

sa 1016 days, 19 hours, 33 minutes ago

Aaron Bertrand : More on splitting lists : custom delimiters, preventing duplicates, and maintaining order

The other day, I showed you my approach (but not necessarily the best approach for everyone) to splitting a comma-separated list of integers. Some of the functions I threw up there catered to integers specifically, whereas I am sure many of you are splitting more generic data (e.g. strings). So in this post I wanted to show how I split up lists and add a little more flexibility to the approach as well. Some of the limitations of several approaches out there are as follows:they are dedicated to breakin... (more)

category: Query | clicked: 4 | comment | | source: sqlblog.com

tags: Celko, CSV, numbers table, splitting strings, sql injection, TVF

1
kudos

Kudos

How to display detailed data below each header data?

published 1013 days, 2 hours, 15 minutes ago posted by

sa 1017 days, 41 minutes ago

How to display detailed data below each header data?

One of my friends asked me if it possible to generate Detailed data for each Header data without using a Cursor or while loop. (The output is something like the one usually seen via reporting tool). He wanted the result to be output to text file Consider the jobs and employee tables from pubs database select jobid, jobdesc from jobs select jobid,empid,fname,lname from employee The task is to show employee det... (more)

category: Query | clicked: 3 | comment | | source: sqlblogcasts.com

tags: detail, header, report, SQL Server, T-sql

1
kudos

Kudos

Find Recently Executed Stored Procedures

published 1016 days, 2 hours, 38 minutes ago posted by

sa 1020 days, 29 minutes ago

Find Recently Executed Stored Procedures

This past weekend, we had an issue where replication fell far behind on one of our databases. The replicated database is used for all sorts of reporting, so the immediate need was to identify processes that may have been affected by the incomplete data. Now, there’s hundreds of stored procedures that reference the affected database; the trick is finding out which ones are relevant. To do this, I used the sy... (more)

category: Query | clicked: 3 | comment | | source: sqlfool.com

tags: SQL Tips, Syndication, T-SQL Scripts

1
kudos

Kudos

Fast and Simple Prime Number Factorization

published 1016 days, 2 hours, 38 minutes ago posted by

sa 1019 days, 21 hours, 49 minutes ago

Fast and Simple Prime Number Factorization

This algorithm requires an existing Prime numbers table. You can easily create one of your own or importing the primes ranging from 2 to 3,037,000,493 from the Internet. If you only is interested in primes with INT range {2..2,147,483,647} you only need the first 4,792 primes {2..46,337} (more)

category: Query | clicked: 3 | comment | | source: weblogs.sqlteam.com

tags: Factorization, Prime Numbers

1
kudos

Kudos

Performance impact: Can SELECT (NOLOCK) block INSERTs?

published 1016 days, 2 hours, 38 minutes ago posted by

sa 1020 days, 1 hour, 42 minutes ago

Performance impact: Can SELECT (NOLOCK) block INSERTs?

The answer is yes. This may be obvious to some, but may not be so obvious to many. It’s good to look at a specific example to see how it may happen. First of all, let’s be precise about what we are talking about here. Assume that we have a table called Junk. We are running a query on the table as follows: SELECT * FROM Junk (NOLOCK); The question is whether this may block the following INSERT: INSERT Junk VALUES(1); You would expect no blocking on INSERT. And in most cases, that would... (more)

category: Query | clicked: 7 | comment | | source: sqlblog.com

tags: Blocking, Locking, NOLOCK, Query Processing

1
kudos

Kudos

Processing a list of integers

published 1017 days, 4 hours, 38 minutes ago posted by

sa 1020 days, 3 hours, 6 minutes ago

It is a common problem... you need to pass multiple values into a stored procedure and, as a result, update multiple rows. Let's pretend we have a table of students: CREATE DATABASE School; GO USE School; GO CREATE TABLE dbo.Students ( StudentID INT PRIMARY KEY, FullName NVARCHAR(64), ModifiedDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ); INSERT dbo.Students(StudentID, FullName) SELECT 1, 'Aaron Bertrand' UNION ALL SELECT 2, 'Kalen Delane... (more)

category: Query | clicked: 2 | comment | | source: sqlblog.com

tags: list

1
kudos

Kudos

Uniquely Identifying XML Nodes with DENSE_RANK

published 1017 days, 4 hours, 38 minutes ago posted by

sa 1020 days, 2 hours, 21 minutes ago

Uniquely Identifying XML Nodes with DENSE_RANK

When working with XML in SQL Server, you might want to uniquely identify one node against another. But due to the flexibility with which XML can be defined, this is not always directly possible. SQL Server's own XML structures are guilty of having this problem, as I discovered while writing the Extended Events Code Generator. Events in the XML produced by XE have a limited number of attributes and depending on what you're collecting you may get numerous instances of events that look exactly the same. I w... (more)

category: Query | clicked: 2 | comment | | source: sqlblog.com

tags: CROSS APPLY, DENSE_RANK, XML

1
kudos

Kudos

Regular expression

published 1017 days, 4 hours, 38 minutes ago posted by

sa 1019 days, 23 hours, 31 minutes ago

One of the posters asked a question in the forums about finding a number in an expression consists of alphabets and number if a particular digit is provided Consider the following set of data this is test place located at no 123456 test foo 35234 bar 126831 72348707 foo foo 987654 bar If the digit provided is 7, then the numbers 7234870 and 987654 should be extracted from the data One of the methods is using a regular expression declare @... (more)

category: Query | clicked: 0 | comment | | source: sqlblogcasts.com

tags: RegEx

1
kudos

Kudos

The Query Optimizer and Contradiction Detection

published 1018 days, 4 hours, 18 minutes ago posted by

sa 1018 days, 23 hours, 44 minutes ago

The Query Optimizer and Contradiction Detection

One of the many interesting topics I found while working as technical editor of the SQL Server 2008 Internals book was the concept of contradiction detection. So, in this post I will to try to expand on this topic and provide some additional examples. Contradiction detection is performed by SQL Server during the early steps of the query optimization process. During this process the Query Optimizer tries to find contradictions that can be removed and can make the query perform better. Since these ... (more)

category: Query | clicked: 2 | comment | | source: sqlblog.com

tags: Contradiction Detection, Query Optimizer

Welcome!

Query

Finding Tables Without Primary Keys

ALTERing a Partition Function

How are auto-created column statistics names generated?

Calculating Running Streak over many records

Using CTE (Common Table Expression) and Views to generate data

Spatial Data Type Structure

The Query Optimizer and Computed Columns

The effects UNION in a SQL query « WebbTech Solutions

Teaser: Left Join..the SQL ego crusher

SQL Server – Multiple CTE in One SELECT Statement Query

TSQL Challenge - counting non zero columns

Aaron Bertrand : More on splitting lists : custom delimiters, preventing duplicates, and maintaining order

How to display detailed data below each header data?

Find Recently Executed Stored Procedures

Fast and Simple Prime Number Factorization

Performance impact: Can SELECT (NOLOCK) block INSERTs?

Processing a list of integers

Uniquely Identifying XML Nodes with DENSE_RANK

Regular expression

The Query Optimizer and Contradiction Detection

Open ID Login

Standard Login

Forgot Password?

Sign up