|
|
|
|
www.SQLServerBible.com
-
-
we’d be judged by how well we nailed the disconnect.
Seriously, my Olympic pet peeve is that the gymnasts do these amazing twists and defy gravity, and then the major point of their score is whether they move a foot when they land. It makes no sense to me.
|
-
Usually, we think of index seeks (a possible Query Execution Plan operation) as using the b-tree index to pick out a row and then quickly pass that row to the next operation. But the index seek has special powers: when the conditions are right, it can examine non-key columns and filter based on those values inside the index seek operation.
When this happens the index seek properties will have two predicates – a seek predicate (which details the b-tree portion of the seek), and a predicate (with details about the additional filter using non-key columns).
You can see this behavior with the following code:
USE AdventureWorks2008
DROP INDEX Production.WorkOrder.IX_WorkOrder_ProductID
CREATE INDEX IX_WorkOrder_ProductID
ON Production.WorkOrder (ProductID)
INCLUDE (StartDate)
SELECT WorkOrderID, StartDate
FROM Production.WorkOrder
WHERE ProductID = 75
AND StartDate = '2002-01-04'
|
-
Hidden in the Edit > IntelliSense menu is QuickInfo, keyboard shortcut Ctrl-K, Ctrl-I. It pops up information (e.g. column name, data type, nullability) about the current object (table, view, DMV, column) under the cursor. Very Cool.
Thank You to Buck and his team of SSMS superstars.
|
-
In an effort to be more "open", I've moved AutoAudit (code-gen utility that creates audit tirggers) from my swebsite to CodePlex. Soemthing I like about CodePlex is the open submission and voting on issues and feature requests. I've moved every feature request from emails and this blog to CodePlex. I invite you to download, submit requests, and vote at: http://www.codeplex.com/AutoAudit/WorkItem/List.aspx
|
-
I love SQL and I wanted to put a oval Euro-style "SQL" sticker on the cover of my notebook. It's about the same cost to order a lot than to order one, so I have a few left over. They're nice vinyl top quality stickers suitable for a nice top quality notebook (see attachment).
If you'd like one, email me your name and address, and I'll snail mail you a sticker as a thanks for reading my blog (till they run out). No strings attached, I don't do anything with your address except mail you the sticker.
pauln@sqlserverbible.com please use the subject: Euro SQL Sticker
|
-
just posted - SSMS T-SQL Debugger ScreenCast 5:58 - focuses on the call stack. If you haven't played with the new Debugger, this is an easy way to get a look-see.
|
-
Tomorrow is our Thousandths Day - Edie and I will have been married for exactly one thousand days. How do I know? SQL Server told me:
Select DateAdd(d, 1000, '10/22/2005')
When I first told Edie about Thousandths Day a few months ago, she thought I was making it up, but now she's wondering what I have planned. Since she never reads my blog… I booked the romance package at the Antler's Hotel where we had our wedding reception and a dinner at La Creperie, our favorite French restaurant downtown Colorado Springs.
|
-
Yesterday, Microsoft bought Zoomix, a very cool Israeli company. (<tangent> Israel is one of my favorite words - rich with layers of meaning: Isra meaning "one who struggles" and El, a Hebrew name for God </tangent>.)
Sunday I meet up with a friend who has a PhD in Computer Science and does research in a government lab. He said the most important database trend was semantics - identifying meta data about data. He also said the Oracle was all over semantics and Microsoft was behind the curve on this one.
Zoomix is a database semantics company researching data cleansing, linguistics and how these technologies become practical with databases. The Jerusalem based company will be integrated with Microsoft's Israeli research center and their work is going to integrated into future SQL Server versions.
Sounds cool to me.
|
-
I like the Vista interface, but I know that under the covers Vista is a bit bloated. I've blogged before that the one of the problems with the Vista vs. XP debate is that not every computer needs the power user UI of Vista. Many production machines are better off with the lighter weight XP.
Now we hear that Windows 7 won't be the redesigned microkernel MinWin that was talked about last year. This well written, thoughtful NY Times tech editorial calls for a new kernel for a new Windows. As a huge fan of Windows and one who wishes the very best for Microsoft and the Windows user base, I too would like to see the next Windows be a hit out of the park, not just Vista with multi-touch.
It's time to make a clean break from the past and architect a new kernel that's designed for multi-processors, SSD disks, and virtualization. Modern virtualization means that compatability can be accomplished with VPC running XP. It's time to shrug off the shackes of compatability. I have no doubt that Microsoft can do it. Come on Steve, build a Windows that will make everyone want to jump from XP!
|
-
In the SSWUG Virtual conference, Earl asks,
Paul, I am looking @ several million records. In each record is a nvarchar that IDs the record and a datetime for when the transaction took place for that nvarchar ID. I need to capture the nvarchar ID when a number of the records for that nvarchar ID was within a time period of the other records with the same nvarcharID. It seems that a cursor is the best way to go through each record to verify a count for the nvarchar ID each time the record was within (5) minutes of the original record. Would you have a set oriented method for extracting these sets of records?
Here's your script Earl,
USE TempDB
CREATE TABLE T1 (
T1ID INT NOT NULL IDENTITY PRIMARY KEY,
RecordID NVARCHAR(50) NOT NULL,
TransTime DateTime NOT NULL
)
GO
INSERT T1 (RecordID, TransTime)
VALUES ('abc', '1/1/1980')
INSERT T1 (RecordID, TransTime)
VALUES ('qwerty', '1/1/1980')
GO 3
INSERT T1 (RecordID, TransTime)
VALUES ('abc', GETDATE())
INSERT T1 (RecordID, TransTime)
VALUES ('qwerty', GETDATE())
INSERT T1 (RecordID, TransTime)
VALUES ('qwerty', GETDATE())
INSERT T1 (RecordID, TransTime)
VALUES ('xyz', GETDATE())
WAITFOR delay '000:000:000.050'
GO 25
WITH FirstRecord AS
(
SELECT RecordID, MIN(TransTime) AS FirstTime
FROM T1
GROUP BY RecordID)
SELECT T1.RecordID, COUNT(*) AS COUNT
FROM T1
JOIN FirstRecord
ON T1.RecordID = FirstRecord.RecordID
AND T1.TransTime <= DATEADD(n,1, FirstTime)
GROUP BY T1.RecordID
|
-
In the SSWUG Virtual Conference this morning, in the chat room following my Kill the Cursor session, Boris asked, "How can I find the last five events for every device, without a cursor?"
Here's the script, Boris. Essentially it uses the Over clause with a Partition by in a subquery to segment by device and number each row. The outer query then has a where clause to restrict the results to the last five.
Boris, I'm interested in the performance gains you see with the set-based query over the cursor. Once you test this will you please post your findings? thanks,
USE TempDB
CREATE TABLE Events (
EventID INT IDENTITY NOT NULL PRIMARY KEY,
Device CHAR(2),
EventTime DATETIME,
Data CHAR(10)
)
GO
INSERT Events( Device, EventTime, Data)
VALUES ('a', GETDATE(), 'asd')
INSERT Events( Device, EventTime, Data)
VALUES ('b', GETDATE(), 'qwe')
INSERT Events( Device, EventTime, Data)
VALUES ('c', GETDATE(), 'sdf')
-- waitfor delay '00:00:00.100'
GO 1000
INSERT Events( Device, EventTime, Data)
VALUES ('a', GETDATE(), 'last five')
INSERT Events( Device, EventTime, Data)
VALUES ('b', GETDATE(), 'last five')
INSERT Events( Device, EventTime, Data)
VALUES ('c', GETDATE(), 'last five')
WAITFOR delay '00:00:00.100'
GO 5
SELECT *
FROM (
SELECT EventID, Device, EventTime,
Row_Number() OVER(partition BY device
ORDER BY eventtime DESC) AS RowNum
FROM Events) sq
WHERE RowNum <= 5
|
-
These queries mix a few basic DMVs with a little relational division to indentify every set of duplicate indexes.
This is the result of a collaborative effort. I started with a group by version of this query about a week ago and posted it on the private MVP Newsgroups. Itzik Ben-Gan replied with a pretty cool variation, and then I made some minor edits to handle partial dups and include column logic.
The first query finds exact matches. The indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination. The only caution would be to check for index hints.
-- exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;
The second variation of this query finds partial, or duplicate, indexes that share leading key columns, e.g. Ix1(col1, col2, col3) and Ix2(col1, col2) would be considered duplicate indexes. This query only examines key columns and does not consider included columns.
These types of indexes are probable dead indexes walking.
-- Overlapping indxes
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'partialduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and (c1.cols like c2.cols + '%'
or c2.cols like c1.cols + '%') ;
Be careful when dropping a partial duplicate index if the two indexes differ greatly in width. For example, if Ix1 is a very wide index with 12 columns, and Ix2 is a narrow two-column index that shares the first two columns, you may want to leave Ix2 as a faster, tighter, narrower index.
|
-
Prompted by Adam, I just spent a couple hours updating my links page, removing broken links and adding several from my IE favorites - some SQL links, many geek/cool links. So I invite you to surf a while. If you don't see your favorite link email me: pauln@sqlserverbible.com
And my first e-Newsletter is going out tomorrow, subscribe here.
|
-
Hey! I just found out that I have three *free* passes to the SSWUG Virtual Conference. Check out this video. And, btw, I had a blast recording my sessions in the SSWUG studio in Tucson, AZ. They have a very professional set-up, and I've been in some pretty cool studios.
So here's the deal - I'm going to randomly pick three winners from my new free e-newsletter subscribers list Sunday evening at midnight. You can subscibe at http://tinyurl.com/5zzlmo. Good Luck!
-Paul
|
|
|
|
|
|