Rants Tagged with “SQL Server”
1 2 3 > >> (Total Pages: 3/Total Results: 27)
Its been an exciting day here at the Wildermuth compound. I noticed that the old setup for my SQL Server Monitor project was broken (mostly dependencies were wrong), so I figured I'd just open it up and tweak a couple of settings and move along with my day. D'oh!
Seems that I'd lost the source to that project. I use Subversion to save my sources locally but that project pre-dated that source control. What to do? Well, I rebuilt it using Reflector to give me the old code. Then I rebuild the setup using Visual Studio 2008's Setup projects (which suspiciously don't seem like they've changed since their inception in Visual Studio 2002!).
Since I had to rebuild the source, I thought it was time to fix the one nagging 'bug' in the code. Not really a bug since it was built without UAC in mind, it required it be to run as Admin on UAC enabled machines (since it mucks with the services and such). I thought I was going to have to dig into some arcane Win32 stuff to get it to work, but interestingly all it needed was a manifest file:
<?xml version="1.0" encoding="utf-8"?>
<asmv1:assembly manifestVersion="1.0"
xmlns="urn:schemas-microsoft-com:asm.v1"
xmlns:asmv1="urn:schemas-microsoft-com:asm.v1"
xmlns:asmv2="urn:schemas-microsoft-com:asm.v2"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<assemblyIdentity version="1.0.0.0"
name="MyApplication.app"/>
<trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
<security>
<requestedPrivileges xmlns="urn:schemas-microsoft-com:asm.v3">
<requestedExecutionLevel level="requireAdministrator"
uiAccess="false" />
<!--<requestedExecutionLevel level="asInvoker" uiAccess="false" />-->
</requestedPrivileges>
</security>
</trustInfo>
</asmv1:assembly>
The trick was adding the 'requireAdminstrator' as a requestedExecutionLevel. Once the manifest was added as an embedded resource, Vista/2008 recognizes it needs some admin privledges and should ask the user when necessary.
Go get the new setup file if you want to be my beta tester! Please email me at shawn (at) wildermuth.com if you find any bugs.
Hey everybody look...its not a Silverlight post ;)
I just finished listening to this interview with Peter Spiro. Peter Spiro discusses leadership in the SQL Server team, Shackleton and WinFS. Its worth a listen as he's one of the smartest guys in MS (in my opinion) and has enough cred to back it up.
One concern is that it seems that his discussion of WinFS (which he corrects the interviewer and calls a "failure") is cut short. There is a hard break (the only one I noticed) about at 40 minutes into the video, in the middle of the WinFS discussion. I'd love to get a hold of what's missing, but I don't see that happening. Hopefully it was just a rambling discussion of esoteric topics that was cut for clarity, but I can't help but feel like we're missing 18 1/2 minutes of tape.
For those of use who have spent any time in the sample AdventureWorks database, I just found a handy Visio (or HTML) database diagram of the database. Its clean and explains some of their ideas about using schemas in SQL Server. Worth a look if you've ever looked at the AW database.
I got thinking about writing custom paging code with the new SQL Server 2005 ROW_NUMBER, RANK, and DENSE_RANK functions. I started playing around with code and ended up with this simple ASP.NET 2.0 example. It works with SQL Server 2005 and the Adventure Works sample database. The example uses ad-hoc SQL so it would be easy to show how it works, but moving it into stored procs would be simple.
I'd love any feedback!
PagingDemo.zip
I had an interesting discussion with some members of a class I am teaching right now about how paging is done. It looks like there are four Functions that allow for paging and using Common Table Expressions. They all can optionally include a separate ORDER BY clause to specify how they are sorted (so your result set can be sorted differently than it is ranked).
ROW_NUMBER()
This function returns the exact number of the row number in the result. For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, ROW_NUMBER() as RowNum
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE RowNum BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (ROW_NUMBER() OVER (ORDER BY SalesYTD)) as RowNum
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE RowNum BETWEEN 1 AND 5
ORDER BY LastName
RANK()
This function returns the ranking of the row in the result. Rows with identical results in the ordering will return the same rank (e.g. Two sales people with sales of $1,000 will get the same ranking). For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, RANK() as Ranking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE Ranking BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (RANK() OVER (ORDER BY SalesYTD)) as Ranking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE Ranking BETWEEN 1 AND 5
ORDER BY LastName
DENSE_RANK()
Same as RANK above except there will not be any gaps (e.g. If rank 3rd and 4th are tied, they will both be RANK'd 3. With DENSE_RANK the next row will be ranked 4 instead of 5 (as RANK would do). For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, DENSE_RANK() as DenseRanking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE DenseRanking BETWEEN 1 AND 5
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (DENSE_RANK() OVER (ORDER BY SalesYTD)) as DenseRanking
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE DenseRanking BETWEEN 1 AND 5
ORDER BY LastName
NTILE(x)
Separates the result into x number of batches. When you specify NTILE you must specify the number of 'tiles' to create. For example:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, NTILE(5) as PageNumber
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE PageNumber = 1
ORDER BY SalesYTD
Or if you want to order the rows by SalesYTD and sort it by Lastname instead:
WITH SegmentedSalesPeople AS
(
SELECT FirstName, LastName, SalesYTD, (NTILE(5) OVER (ORDER BY SalesYTD)) as PageNumber
FROM Sales.vSalesPerson
WHERE SalesYTD <> 0
)
SELECT *
FROM SegmentedSalesPeople
WHERE PageNumber = 1
ORDER BY LastName
This is fun!
If you upgrade to RC1 be aware that SQL Server 2005 requires SP2 before it will work with Vista RC1. Yeah, I know there is no SP2...but that's the case. It just doesn't work. I am trying to hack around the problem so I'll let you know if I find a solution...
I have been attempting to try out some new software from Microsoft (including Glidepath and Visual Studio Database Edition). Both of these require SQL Server Express installed. Problem is that I install a Developer Edition of SQL Server 2005 (as well as 2000) because it is more feature friendly than SQL Server Express. Why does Microsoft insist I have a third Database server? Why can't it prompt me to tell it what database to use, or at least attempt to find SQL Server 2005 as the default instance on the current machine? Just stoopid in my opinion. It's keeping me from trying out and possibly exhaulting these new interesting projects.
"There I said it..."
Recently I posted about Timestamps and CommandBuilders and I got a well informed reply by Luciano Evaristo Guerche concerning a related approach of using BINARY_CHECKSUM in SQL Server to do the same thing. I think Luciano's response means to say that if you can't use Timestamp's in the database (like you don't have control over the schema) then BINARY_CHECKSUM is an improvement over the brute force concurrency that CommandBuilders do by default.
I thought Luciano was right, but I wanted to prove it out. I ran some tests using a Typed DataSet and the Northwind Customer table:
- I changed all 91 records of the customer table by changing a single field (Address).
- I updated the table with the brute force method, the BINARY_CHECKSUM method and the timestamp method.
- I ran it in batches of iterations to get some scale. I changed the entire table in each batch. I ran it in batches of 5, 25, 100, 250, 500 and 1,000 iterations.
In general the results were what I thought. The brute force performed worst, the BINARY_CHECKSUM was a performance boost, but not to a great extent; and the timestamp showed considerable improvement. Here is a chart of my results. The number of rows updated corresponds ot the batches (e.g. 5 iterations x 91 rows = 455, etc.) and I have calculated the row/second response time from each result:
Let me know if you have any questions...
I was talking with a bright guy inside the ADO.NET team today when he told me that the DbCommandBuilder supports a new option called ConflictOption. This option alerts the DbCommandBuilder to use one of three methods for detecting concurrency conflicts:
- CompareAllSearchableValues: The 1.x behavior of comparing all values to make sure the row hasn't changed.
- CompareRowVersion: This compares the primary key and a timestamp field (or rowversion). This only works if the table has both a primary key and a timestamp (or rowversion) defined.
- OverwriteChanges: This only compares the primary key and does not provide any concurrency, but simply allows for overriting of data. *I DO NOT SUGGEST YOU USE THIS*.
Unfortunately, the TableAdapters in Typed DataSets (v 2.0) don't seem to be using this when they create their concurrency (the TableAdatper code generator uses a DbCommandBuilder to create the Update/Delete statements).
Additionally, the ASP.NET 2.0 DataSources do not use DbCommandBuilders so they won't use timestamps to do currency at all. But my rant about DataSources is for another day.
If you're in Atlanta and want to see me talk about the SQL Server CLR Integration, come by the Atlanta C# Users Group on May 1st (That's today). Meeting starts at 6pm. Here's a link to the User Group's Site:
http://www.atlantacsharp.org/NextMeeting.aspx
