How many Java programmers does it take to round up to a power of two?
Well take a look:
http://forum.java.sun.com/thread.jspa?threadID=248212&start=0&tstart=0
Thursday, November 01, 2007
Friday, October 26, 2007
Do you want to laugh or cry?
I don't know if I should laugh or cry after looking at this code. This could not have been written by a human right?
Anyway here it is, consider it a teaser. If you want to torture yourself and look at the whole query then go here: http://www.tek-tips.com/viewthread.cfm?qid=1421304&page=1
HAVING (((1 = 1)
AND (PP.Created >= ISNULL(NULL,'1/1/1900')
AND PP.Created < 1 =" 2)">= ISNULL(NULL,'1/1/1900')
AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
WHERE
(
(NULL IS NOT NULL
AND pv.DoctorId IN (NULL))
OR(NULL IS NULL)
)
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END
Someone will red flag the post, here is the whole query.
/* New Patient Analysis */
SET NOCOUNT ON
CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL ,
[Prefix] [varchar] (10) NULL ,
[First] [varchar] (30) NULL ,
[Middle] [varchar] (30) NULL ,
[Last] [varchar] (30) NULL ,
[Suffix] [varchar] (20) NULL ,
[Created] [datetime] NOT NULL
)
Insert #PatientTemp
SELECT PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix,
CASE
WHEN 1 = 1 THEN PP.Created
WHEN 1 = 2 THEN Min(PV.Visit)
ELSE NULL
END As Created
FROM PatientVisit PV
INNER JOIN PatientProfile PP ON PP.PatientProfileId = PV.PatientProfileId
GROUP BY PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix, PP.Created
HAVING (((1 = 1)AND
(PP.Created >= ISNULL(NULL,'1/1/1900') AND PP.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(PV.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
Order By PP.First, PP.Last
SELECT dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix) AS Name,
CASE
WHEN 1 = 1 THEN #PatientTemp.Created
WHEN 1 = 2 THEN Min(pv.Visit)
ELSE NULL
END As Created,
COUNT(*) AS [Number Of Visits],
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULLA
END As Grouping
FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN DoctorFacility df1 ON pv.ReferringDoctorId = df1.DoctorFacilityId
LEFT JOIN DoctorFacility df2 ON pv.FacilityId = df2.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId
WHERE
(
(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.ReferringDoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY #PatientTemp.Created,dbo.FormatName(#PatientTemp.Prefix, #PatientTemp .First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix),
pva.PatientProfileId,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END
HAVING (((1 = 1)AND
(#PatientTemp.Created >= ISNULL(NULL,'1/1/1900') AND #PatientTemp.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(pv.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(pv.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
ORDER BY dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp .Suffix), #PatientTemp.Created
Drop table #PatientTemp
Anyway here it is, consider it a teaser. If you want to torture yourself and look at the whole query then go here: http://www.tek-tips.com/viewthread.cfm?qid=1421304&page=1
HAVING (((1 = 1)
AND (PP.Created >= ISNULL(NULL,'1/1/1900')
AND PP.Created < 1 =" 2)">= ISNULL(NULL,'1/1/1900')
AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
WHERE
(
(NULL IS NOT NULL
AND pv.DoctorId IN (NULL))
OR(NULL IS NULL)
)
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END
Someone will red flag the post, here is the whole query.
/* New Patient Analysis */
SET NOCOUNT ON
CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL ,
[Prefix] [varchar] (10) NULL ,
[First] [varchar] (30) NULL ,
[Middle] [varchar] (30) NULL ,
[Last] [varchar] (30) NULL ,
[Suffix] [varchar] (20) NULL ,
[Created] [datetime] NOT NULL
)
Insert #PatientTemp
SELECT PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix,
CASE
WHEN 1 = 1 THEN PP.Created
WHEN 1 = 2 THEN Min(PV.Visit)
ELSE NULL
END As Created
FROM PatientVisit PV
INNER JOIN PatientProfile PP ON PP.PatientProfileId = PV.PatientProfileId
GROUP BY PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix, PP.Created
HAVING (((1 = 1)AND
(PP.Created >= ISNULL(NULL,'1/1/1900') AND PP.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(PV.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
Order By PP.First, PP.Last
SELECT dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix) AS Name,
CASE
WHEN 1 = 1 THEN #PatientTemp.Created
WHEN 1 = 2 THEN Min(pv.Visit)
ELSE NULL
END As Created,
COUNT(*) AS [Number Of Visits],
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULLA
END As Grouping
FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN DoctorFacility df1 ON pv.ReferringDoctorId = df1.DoctorFacilityId
LEFT JOIN DoctorFacility df2 ON pv.FacilityId = df2.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId
WHERE
(
(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.ReferringDoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY #PatientTemp.Created,dbo.FormatName(#PatientTemp.Prefix, #PatientTemp .First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix),
pva.PatientProfileId,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END
HAVING (((1 = 1)AND
(#PatientTemp.Created >= ISNULL(NULL,'1/1/1900') AND #PatientTemp.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(pv.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(pv.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
ORDER BY dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp .Suffix), #PatientTemp.Created
Drop table #PatientTemp
Friday, October 19, 2007
Sort Values Ascending But NULLS Last
This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don't want the NULLS at the beginning.
Oracle has this syntax: ORDER BY ColumnName NULLS LAST;
SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.
The 2 approaches with a datetime data type
DECLARE @Temp table(Col datetime)
INSERT INTO @Temp VALUES(getdate())
INSERT INTO @Temp VALUES('2007-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2005-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2004-10-19 09:54:03.730')
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)
FROM @Temp
ORDER BY COALESCE(Col,'9999-12-31 23:59:59.997')
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col
The 2 approaches with an integer data type
DECLARE @Temp table(Col int)
INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(555)
INSERT INTO @Temp VALUES(444)
INSERT INTO @Temp VALUES(333)
INSERT INTO @Temp VALUES(5656565)
INSERT INTO @Temp VALUES(3)
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)
FROM @Temp
ORDER BY COALESCE(Col,'2147483647')
SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col
Monday, October 15, 2007
Interview With Kalen Delaney About Inside Microsoft SQL Server 2005 Query Tuning and Optimization
I am a big fan of Inside Microsoft SQL Server 2005 The Storage Engine so when I saw that yet another book got published in this series I just had to check it out. Inside Microsoft SQL Server 2005: Query Tuning and Optimization is very well written gets to the point and give you the answers that you need. I decided to contact Kalen to see if she would be willing to do an interview, by now you know that the answer is yes of course ;-)The question-and-answer session with Kalen that follows was conducted via email.
What is the audience for this book, is it the enterprise user or can a small department benefit from the tips in this book?
Because this book deals with query tuning, anyone who writes SQL queries for SQL Server can benefit. Very little in the book is geared towards system tuning, so the size of the machine doesn’t really matter. Now of course, if you have a very small system with very small tables, you won’t get as much benefit out of tuning your queries. However, if you have any tables of more than a few thousand rows, and you do any joins, you will need to tune your queries. In addition, the issues of blocking and concurrency control can impact any system, no matter how small.
What new technologies in SQL Server 2005 do you think are the most beneficial for performance?
For very large databases, the best new technology is partitioning. For any size system, if you have had serious performance problems due to blocking, you might find a big performance benefit by using one of the snapshot-based isolation levels, but you really need to understand the resource costs that come along with the improved performance. For your individual queries, I think the new optimizer hints and query level recompiles can make a big difference. For indexes, the ability to add included columns to nonclustered indexes can give some of your hard-to-tune queries a major performance boost.
What will a person who reads this book gain in terms of understanding how to performance tune a server?
The focus of this book is not so much on tuning the server, but on tuning queries. There is more in Inside SQL Server 2005: The Storage Engine on server issues such as memory and processor management. The biggest server wide issues are covered in Chapter 5, when I talk about managing the plan cache, and how and when query plans are reused.
Is the book geared towards a beginner/intermediate level user or do you have to be an advanced user to really utilize the information in this book?
The book is not geared towards beginners, but everyone should be able to get something out of it. It’s primarily geared to SQL Server developers and DBA’s who have been working with SQL Server for a while, and have encountered performance problems that they are trying to find solutions for.
With all the changes in SQL Server 2005, how critical has the tempdb become in regards to performance?
Tempdb has always been important. In SQL Server 2005, if you are using one of the snapshot-based isolation levels, you are going to have to be more aware of the demands placed on tempdb, both in the sizing requirements and the additional I/O resources needed. Fortunately, SQL Server 2005 provides tools to monitor tempdb, including a dozen new performance monitor counters, and a dynamic management view, sys.dm_db_file_space_usage, that keeps track of how much space in tempdb is being used for each of the different kind of object stored in tempdb.
I understand that this is the first time you wrote with a team of other writers; can you tell us something about that experience?
I initially thought that not having to write the whole volume by myself meant that I could get it done sooner, but that was not the case. Everyone had their own schedule and their own way of writing. The personal aspect of working with the other authors was great. I deeply respect all of the others and it was an honor to be working so closely with them. I had some concerns about the depth of coverage and I wondered whether all the chapters would end up being as deep as I hoped for, but that turned out not to be a major problem. The only real issues were agreeing on a common terminology and coding style, and even that wasn’t that big of an issue, because I got to do a final editing pass on everyone’s chapters.
What SQL Server books are on your bookshelf?
All of the Inside SQL Server books are there, of course, and all of Ken Henderson’s books. Bob Beauchemin’s book is in my car, to read while I am waiting for the ferryboat, and while on the ferry. I also have technical books that aren’t SQL Server specific, such as Jim Gray’s Transaction Processing, Russinovich’s and Solomon’s Windows Internals, Chris Date’s Introduction to Database Systems and Mike Stonebraker’s Readings in Database Systems.
Why do you write technical books?
I love working with SQL Server and trying to find out all I can about it. When I found that I could explain difficult concepts in a training environment, I thought I could do the same thing in a written format, and reach more people that way. I have always loved explaining things, ever since I was a teaching assistant for High School Math.
Will you be updating your books for SQL Server 2008?
I have just started meeting with my editors at Microsoft Press about SQL Server 2008, and it looks like a revision is in the plans. We’re really looking at it as just a revision, with the same structures as the current books, with straightforward changes and the inclusion of new features.
Name three things that are new in SQL Server 2005 that you find are the most valuable?
Dynamic Management Views, Dynamic Management Views, and Dynamic Management Views!
Oh, you wanted three different things? ;-) How about XML query plans and optimization hints. (I’m also very fond of many of the new TSQL constructs, but I was only talking about things that I cover in my new book.)
Name three things which are coming in SQL Server 2008 that you are most excited about?
You’ll have to ask me this next time. I have actually been avoiding SQL Server 2008 while I was getting my Query Tuning and Optimization book finished, because I didn’t want to get distracted.
Can you list any third party tools that you find useful to have as a SQL Server developer/admin?
I’ve tried a few other products, but usefully I find that it is much easier to just stick with the Microsoft line and use the tools provided with the product.
Name some of your favorite non-technical books.
Oh, I love to read. It would be impossible to list my favorite books, but I can tell you my favorite authors, most of whom write science fiction: Lois McMaster Bujold, Ursula LeGuin, Sheri Tepper, Orson Scott Card, Octavia Butler, Elizabeth Moon. I also love to read historical fiction like Leon Uris.
Thursday, October 11, 2007
SQL Server 2008 Whitepapers
There are some good katmai whitepapers appearing on microsoft.com.
The list is available here http://www.microsoft.com/sql/2008/learning/whitepapers.mspx.
Performance and Scale http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_perfandscale.mspx
Manageability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_manageability.mspx
Delivering location intelligence with spatial data http://www.microsoft.com/sql/techinfo/whitepapers/spatialdata.mspx
SQL Server 2008 Reporting Services http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_ssrs.mspx
What's new for XML in SQL Server 2008 http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_xml.mspx
Managing unstructured data http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_unstructured.mspx
Server consolidation http://www.microsoft.com/sql/techinfo/whitepapers/hosting_sql_srv_consol.mspx
Data warehousing
http://www.microsoft.com/sql/techinfo/whitepapers/sql-for-datawarehousing_2008.mspx
Data Programmability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_dp.mspx
The list is available here http://www.microsoft.com/sql/2008/learning/whitepapers.mspx.
Performance and Scale http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_perfandscale.mspx
Manageability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_manageability.mspx
Delivering location intelligence with spatial data http://www.microsoft.com/sql/techinfo/whitepapers/spatialdata.mspx
SQL Server 2008 Reporting Services http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_ssrs.mspx
What's new for XML in SQL Server 2008 http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_xml.mspx
Managing unstructured data http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_unstructured.mspx
Server consolidation http://www.microsoft.com/sql/techinfo/whitepapers/hosting_sql_srv_consol.mspx
Data warehousing
http://www.microsoft.com/sql/techinfo/whitepapers/sql-for-datawarehousing_2008.mspx
Data Programmability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_dp.mspx
Wednesday, October 10, 2007
SQL Teaser: Guess the output
What do you think will be the output?
DECLARE @d datetime
SET @d = '20071010'
SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)
After that run this to see how to get first and last days for years, quarters, months and weeks(be careful of ISO weeks!)
DECLARE @d datetime
SET @d = '20071010'
SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0) as FirstDayOfYear,
DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) as LastDayOfYear,
DATEADD(qq, DATEDIFF(qq, 0, @d), 0) as FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) as LastDayOfQuarter,
DATEADD(mm, DATEDIFF(mm, 0, @d), 0) as FirstDayOfMonth,
DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) as LastDayOfMonth,
DATEADD(wk, DATEDIFF(wk, 0, @d), 0) as FirstDayOfWeek,
DATEADD(wk, DATEDIFF(wk, 0, @d)+1, -1) as LastDayOfWeek
DECLARE @d datetime
SET @d = '20071010'
SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)
After that run this to see how to get first and last days for years, quarters, months and weeks(be careful of ISO weeks!)
DECLARE @d datetime
SET @d = '20071010'
SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0) as FirstDayOfYear,
DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) as LastDayOfYear,
DATEADD(qq, DATEDIFF(qq, 0, @d), 0) as FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) as LastDayOfQuarter,
DATEADD(mm, DATEDIFF(mm, 0, @d), 0) as FirstDayOfMonth,
DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) as LastDayOfMonth,
DATEADD(wk, DATEDIFF(wk, 0, @d), 0) as FirstDayOfWeek,
DATEADD(wk, DATEDIFF(wk, 0, @d)+1, -1) as LastDayOfWeek
Tuesday, October 09, 2007
Self-paced Course: What's New in Microsoft SQL Server 2008
In this clinic, you will learn about the new and enhanced features included in SQL Server 2008. You will explore the new data types and the data management features. Additionally, you will learn about the enhanced Integration Services, Analysis Services, and Reporting Services included in SQL Server 2008. This online clinic is composed of a rich multimedia experience.
To get the most out of this clinic, it is recommended that you have:
Objectives:
At the end of the course, students will be able to:
Modules & Lessons
Clinic Overview
Module Introduction
Navigation Overview
Clinic Information
What’s New in SQL Server 2008
Module Introduction
Overview of SQL Server 2008
Lesson Introduction
Enhancements in SQL Server 2008
New Features of SQL Server 2008
Self Test
Enterprise Data Platform
Lesson Introduction
Methods for Data Encryption
Security Auditing for Data Protection
Resource Management Features
Benefits of the Resource Governor Tool
System Analysis Features
Server Management Features
Self Test
Dynamic Development
Lesson Introduction
Data Entities in SQL Server 2008
Data Synchronizing Features
Self Test
Beyond Relational Databases
Lesson Introduction
New Data Types
Spatial Data Type
Table-Value Parameters in SQL Server 2008
Features of Integrated Full-Text Search in SQL Server 2008
Self Test
Pervasive Insight in Integration Services
Lesson Introduction
Data Integration Features
The MERGE Statement in SQL Server 2008
Self Test
Pervasive Insight in Analysis Services
Lesson Introduction
Enhanced Features in Analysis Services
How the Block Computation Feature Works
Self Test
Pervasive Insight in Reporting Services
Lesson Introduction
How Reporting Services Work
Features for Microsoft Office 2007 Integration
The Report Authoring Tool
Report Builder Enhancements
What Is Tablix?
Self Test
Module Summary
Glossary
Module Introduction
Start here: https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=128041
To get the most out of this clinic, it is recommended that you have:
- Knowledge of general SQL database concepts that are largely independent of a specific version of SQL Server
- Knowledge of Microsoft SQL Server 2005 features
- Knowledge of deploying and upgrading database solutions
- Knowledge of how to solve performance issues related to SQL Server hardware
- Performed the job role of a SQL Server 2005 database administrator or database developer
- Product or technology experience in Microsoft SQL Server 2000 or SQL Server 2005
Objectives:
At the end of the course, students will be able to:
- Describe the features of SQL Server 2008 and their benefits.
- Describe the features of enterprise data platform that help you to secure data in applications.
- Describe the dynamic development features that facilitate the development of database applications.
- Describe the features of SQL Server 2008 that provide data storage solutions beyond relational databases.
- Describe the enhanced features in SSIS that help you to integrate data effectively.
- Describe the enhanced features in SSAS that help you to improve the BI infrastructure.
- Describe the enhanced features in SSRS that help you to improve the scalability of the reporting engine.
Modules & Lessons
Clinic Overview
Module Introduction
Navigation Overview
Clinic Information
What’s New in SQL Server 2008
Module Introduction
Overview of SQL Server 2008
Lesson Introduction
Enhancements in SQL Server 2008
New Features of SQL Server 2008
Self Test
Enterprise Data Platform
Lesson Introduction
Methods for Data Encryption
Security Auditing for Data Protection
Resource Management Features
Benefits of the Resource Governor Tool
System Analysis Features
Server Management Features
Self Test
Dynamic Development
Lesson Introduction
Data Entities in SQL Server 2008
Data Synchronizing Features
Self Test
Beyond Relational Databases
Lesson Introduction
New Data Types
Spatial Data Type
Table-Value Parameters in SQL Server 2008
Features of Integrated Full-Text Search in SQL Server 2008
Self Test
Pervasive Insight in Integration Services
Lesson Introduction
Data Integration Features
The MERGE Statement in SQL Server 2008
Self Test
Pervasive Insight in Analysis Services
Lesson Introduction
Enhanced Features in Analysis Services
How the Block Computation Feature Works
Self Test
Pervasive Insight in Reporting Services
Lesson Introduction
How Reporting Services Work
Features for Microsoft Office 2007 Integration
The Report Authoring Tool
Report Builder Enhancements
What Is Tablix?
Self Test
Module Summary
Glossary
Module Introduction
Start here: https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=128041
Wednesday, October 03, 2007
LAMP + Porn = perfect Job?
Found this here:http://jobs.perl.org/job/6883
Senior Software Engineer – Linux/Apache/Mysql/Perl
We are a small, San Francisco-based company seeking a lead software engineer/architect to take over development of our subscription-based adult video web site. You will work with our fabulously talented product manager, marketer, and engineering support to improve our service and take it to new levels of traffic and success.
You must be extremely skilled in the following technologies, so that you can both keep the current site running and extend it:
Linux (packaging systems, network configuration, debugging, tweaking)
Perl (in the form of command line scripts, cgi scripts, and complete libraries)
Apache (building and tweaking the 1.3 variants, and the mod_perl module)
MySQL (SQL and performance tuning)
Java (from servlets to applications; including Hibernate, Spring, Log4j and other open source java libraries/frameworks)
Subversion
Additionally, you must be very familiar with the following technologies that you will encounter, or the ability to quickly come up to speed on them:
C/C++
Python
MPlayer/FFmpeg
Lucene
Lighttpd
Perlbal
You possess the skill-sets of both a system administrator and programmer. You can set up and configure a server to make it do what you want; you can discover, configure and compile applications to make them do what you want; and you can write the code to customize and tie all these things together. There is no part of a system that you are afraid to venture into, nor do you find application writing boring or too complex.
You are a developer who can do anything that you put your mind too; and you live and breathe open source software. You are goals focused, and take great pride in completing and optimizing working applications. You can communicate with business people to find out what needs to be done and how it should be prioritized, and you have the confidence and skill to make it happen. You know the latest tools and technologies that are available that might replace what we’ve got. You actively seek this information and are always looking for better ways to do things.
Finally, you have no problem serving up gigabytes and gigabytes of pornography. In fact, you find the challenge of pushing out a sustained 3Gb/s of pornography to be an extremely interesting engineering problem.
Required skills: Perl/mod_perl
LAMP
Senior Software Engineer – Linux/Apache/Mysql/Perl
We are a small, San Francisco-based company seeking a lead software engineer/architect to take over development of our subscription-based adult video web site. You will work with our fabulously talented product manager, marketer, and engineering support to improve our service and take it to new levels of traffic and success.
You must be extremely skilled in the following technologies, so that you can both keep the current site running and extend it:
Linux (packaging systems, network configuration, debugging, tweaking)
Perl (in the form of command line scripts, cgi scripts, and complete libraries)
Apache (building and tweaking the 1.3 variants, and the mod_perl module)
MySQL (SQL and performance tuning)
Java (from servlets to applications; including Hibernate, Spring, Log4j and other open source java libraries/frameworks)
Subversion
Additionally, you must be very familiar with the following technologies that you will encounter, or the ability to quickly come up to speed on them:
C/C++
Python
MPlayer/FFmpeg
Lucene
Lighttpd
Perlbal
You possess the skill-sets of both a system administrator and programmer. You can set up and configure a server to make it do what you want; you can discover, configure and compile applications to make them do what you want; and you can write the code to customize and tie all these things together. There is no part of a system that you are afraid to venture into, nor do you find application writing boring or too complex.
You are a developer who can do anything that you put your mind too; and you live and breathe open source software. You are goals focused, and take great pride in completing and optimizing working applications. You can communicate with business people to find out what needs to be done and how it should be prioritized, and you have the confidence and skill to make it happen. You know the latest tools and technologies that are available that might replace what we’ve got. You actively seek this information and are always looking for better ways to do things.
Finally, you have no problem serving up gigabytes and gigabytes of pornography. In fact, you find the challenge of pushing out a sustained 3Gb/s of pornography to be an extremely interesting engineering problem.
Required skills: Perl/mod_perl
LAMP
Tuesday, October 02, 2007
How to find out the recovery model for all databases on SQL Server 2000, 2005 and 2008
How do you find out the recovery model for all the databases on your SQL Server box?
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)
--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases
--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)
--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases
--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases
Monday, October 01, 2007
SQL Teaser: Guess the output
Here is a small teaser, can you guess the output?
SELECT d.c-d.b/d.a
FROM(SELECT 1,2,5)d(a,b,c)
SELECT d.c-d.b/d.a
FROM(SELECT 1,2,5)d(a,b,c)
Thursday, September 27, 2007
Screencast: SQL Server Analysis Services what's in it for me?
SQL Server Analysis Services is a very powerful tool, which was introduced with SQL Server 2000. In SQL Server 2005 it is far more powerful, but still underappreciated by a lot of developers. This is a quick and dirty attempt to convey the value of Analysis Services cubes in under ten minutes. Related Resources:
SQL Server Analysis Services Home
Microsoft Business Intelligence Home
SQL Server Analysis Services on MSDN
Watch the screencast(WMV)
SQL Server Analysis Services Home
Microsoft Business Intelligence Home
SQL Server Analysis Services on MSDN
Watch the screencast(WMV)
Friday, September 21, 2007
SQL Teaser PASS Special: Table Size
What will be the outcome of this script?
First we create a table with a total of 6000 bytes
Next we increase col2 from 1000 to 2000 bytes, this will give us a total of 7000 bytes
Finally we add col3 which has 1000 bytes, this will give us a total of 8000 bytes
First run these two statements
--Total size = 6000
CREATE TABLE TestSize (Col1 char(5000),col2 char(1000))
GO
--total size = 7000
ALTER TABLE TestSize
ALTER COLUMN col2 char(2000)
GO
Now what do you think will happen when you run this?
--total size should be 8000 bytes (5000 + 2000 + 1000)
ALTER TABLE TestSize
ADD Col3 char(1000)
GO
Now for bonus points. What book have I been reading.
Hint: the author is at PASS
Wednesday, September 19, 2007
SQL Injection Cheat Sheet
What is SQL Injection? From wikipedia: SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed
Here is a nice SQL injection cheat sheet. Currently only for MySQL and Microsoft SQL Server, some ORACLE and some PostgreSQL
http://ferruh.mavituna.com/makale/sql-injection-cheatsheet/
Table Of Contents
About SQL Injection Cheat Sheet
Syntax Reference, Sample Attacks and Dirty SQL Injection Tricks
Line Comments
SQL Injection Attack Samples
Inline Comments
Classical Inline Comment SQL Injection Attack Samples
MySQL Version Detection Sample Attacks
Stacking Queries
Language / Database Stacked Query Support Table
About MySQL and PHP
Stacked SQL Injection Attack Samples
If Statements
MySQL If Statement
SQL Server If Statement
If Statement SQL Injection Attack Samples
Using Integers
String Operations
String Concatenation
Strings without Quotes
Hex based SQL Injection Samples
String Modification & Related
Union Injections
UNION – Fixing Language Issues
Bypassing Login Screens
Enabling xp_cmdshell in SQL Server 2005
Other parts are not so well formatted but check out by yourself, drafts, notes and stuff, scroll down and see.
Here is a nice SQL injection cheat sheet. Currently only for MySQL and Microsoft SQL Server, some ORACLE and some PostgreSQL
http://ferruh.mavituna.com/makale/sql-injection-cheatsheet/
Table Of Contents
About SQL Injection Cheat Sheet
Syntax Reference, Sample Attacks and Dirty SQL Injection Tricks
Line Comments
SQL Injection Attack Samples
Inline Comments
Classical Inline Comment SQL Injection Attack Samples
MySQL Version Detection Sample Attacks
Stacking Queries
Language / Database Stacked Query Support Table
About MySQL and PHP
Stacked SQL Injection Attack Samples
If Statements
MySQL If Statement
SQL Server If Statement
If Statement SQL Injection Attack Samples
Using Integers
String Operations
String Concatenation
Strings without Quotes
Hex based SQL Injection Samples
String Modification & Related
Union Injections
UNION – Fixing Language Issues
Bypassing Login Screens
Enabling xp_cmdshell in SQL Server 2005
Other parts are not so well formatted but check out by yourself, drafts, notes and stuff, scroll down and see.
Tuesday, September 11, 2007
Cumulative update package 3 for SQL Server 2005 Service Pack 2 is available
Cumulative update package 3 for SQL Server 2005 Service Pack 2 is available.
How to obtain Cumulative Update 3 for SQL Server 2005 Service Pack 2
A supported cumulative update package is now available from Microsoft. However, it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2005 service pack that contains the hotfixes in this cumulative update package.
To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the cumulative update package. To submit an online request to obtain the cumulative update package, visit the following Microsoft Web site:
http://go.microsoft.com/?linkid=6294451
How to obtain Cumulative Update 3 for SQL Server 2005 Service Pack 2
A supported cumulative update package is now available from Microsoft. However, it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2005 service pack that contains the hotfixes in this cumulative update package.
To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the cumulative update package. To submit an online request to obtain the cumulative update package, visit the following Microsoft Web site:
http://go.microsoft.com/?linkid=6294451
Here is what is fixed:
50001581
938243 (http://support.microsoft.com/kb/938243/)
FIX: Error message when you run a full-text query against a catalog in SQL Server 2005: “The execution of a full-text query failed. The content index is corrupt."
50001585
938712 (http://support.microsoft.com/kb/938712/)
FIX: Some records in the fact table may not be processed when you process a dimension that contains many attributes or many members in SQL Server 2005
50001499
938962 (http://support.microsoft.com/kb/938962/)
FIX: You may receive an access violation or error 942 when you drop a database in SQL Server 2005
50001524
939563 (http://support.microsoft.com/kb/939563/)
FIX: Error message when you synchronize a merge replication in Microsoft SQL Server 2005: "MSmerge_del_
50001582
939942 (http://support.microsoft.com/kb/939942/)
FIX: You receive an error message when you try to access a report after you configure SQL Server 2005 Reporting Services to run under the SharePoint integrated mode
50001583
940128 (http://support.microsoft.com/kb/940128/)
FIX: You receive error 8623 when you run a complex query in SQL Server 2005
50001586
940129 (http://support.microsoft.com/kb/940129/)
FIX: An MDX query does not return results when you execute the query against a cube that contains an unnatural hierarchy in a dimension in SQL Server 2005 Analysis Services
50001517
940160 (http://support.microsoft.com/kb/940160/)
FIX: Error message when you deploy a SQL Server 2005 Analysis Service project that contains many cubes, and the cubes contain linked measure groups: "Unexpected internal error"
50001449
940210 (http://support.microsoft.com/kb/940210/)
FIX: Error message when you try to insert more than 3 megabytes of data into a distributed partitioned view in SQL Server 2005: "A system assertion check has failed"
50001447
940220 (http://support.microsoft.com/kb/940220/)
FIX: Error message when you run a SQL Server 2005 Integration Services package that contains an FTP task: “An error occurred in the requested FTP operation"
50001448
940221 (http://support.microsoft.com/kb/940221/)
FIX: Error message when you try to create an Oracle publication by using the New Publication Wizard in Microsoft SQL Server 2005 Service Pack 2: “OLE DB Provider "OraOLEDB.ORACLE" for Linked server
50001451
940223 (http://support.microsoft.com/kb/940223/)
FIX: Error message when you synchronize a subscription by using Windows Synchronization Manager in SQL Server 2005: “The merge process failed to get correct information about the Interactive Resolver component from the Registry"
50001416
940260 (http://support.microsoft.com/kb/940260/)
FIX: Error message when you use Service Broker in SQL Server 2005: "An error occurred while receiving data: '64(The specified network name is no longer available.)'"
50001435
940269 (http://support.microsoft.com/kb/940269/)
FIX: Error message when you try to edit a SQL Server Agent job or a maintenance plan by using SQL Server Management Studio in SQL Server 2005: "String or binary data would be truncated"
50001567
940281 (http://support.microsoft.com/kb/940281/)
FIX: An access violation may occur, and you may receive an error message, when you query the sys.dm_exe_sessions dynamic management view in SQL Server 2005
50001351
940370 (http://support.microsoft.com/kb/940370/)
FIX: The "User:" prefix is lost for the event information that is generated by a child package in SQL Server 2005 Integration Services after you install SQL Server 2005 Service Pack 2
50001382
940373 (http://support.microsoft.com/kb/940373/)
FIX: The performance of a Multidimensional Expressions query in SQL Server 2005 Analysis Services Service Pack 2 is much slower than the performance in earlier builds of SQL Server 2005 Analysis Services
50001433
940378 (http://support.microsoft.com/kb/940378/)
Fix: Unable to Change Transaction Isolation Level After Cursor Declaration
50001479
940384 (http://support.microsoft.com/kb/940384/)
FIX: You receive a System.InvalidCastException exception when you run an application that calls the Server.JobServer.Jobs.Contains method on a computer that has SQL Server 2005 Service Pack 2 installed
50001494
940386 (http://support.microsoft.com/kb/940386/)
FIX: You cannot install SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies on a beta version of Windows Server 2008-based computer
50001602
940545 (http://support.microsoft.com/kb/940545/)
FIX: The performance of insert operations against a table that contains an identity column may be slow in SQL Server 2005
50001589
940935 (http://support.microsoft.com/kb/940935/)
FIX: Error message when you run a parallel query in SQL Server 2005 on a computer that has multiple processors: “SQL Server Assertion: File:
50001599
940937 (http://support.microsoft.com/kb/940937/)
FIX: Error message when you try to update the index key columns of a non-unique clustered index in SQL Server 2005: " Cannot insert duplicate key row in object 'ObjectName' with unique index 'IndexName'"
50001609
940939 (http://support.microsoft.com/kb/940939/)
FIX: Data is not rolled back after you roll back a transaction that contains a writeback operation in SQL Server 2005 Analysis Services
50001629
940942 (http://support.microsoft.com/kb/940942/)
FIX: Error message when you run a stored procedure that references a table that is upgraded from SQL Server 2000 to SQL Server 2005: “A time-out occurred while waiting for buffer latch"
50001573
940949 (http://support.microsoft.com/kb/940949/)
FIX: You receive an error message when you run an UPDATE CUBE statement to update a cube in SQL Server 2005 Analysis Services
50001576
940962 (http://support.microsoft.com/kb/940962/)
FIX: When processing a dimension on SQL Server 2005 Analysis Services an error is raised with the following format: "The following file is corrupted: Physical file: \\?\
50001511
940126 (http://support.microsoft.com/kb/940126/)
FIX: Error 9003 is logged in the SQL Server error log file when you use log shipping in SQL Server 2005
50001436
940379 (http://support.microsoft.com/kb/940379/)
FIX: Error message when you use the UNLOAD and REWIND options to back up a database to a tape device in SQL Server 2005: "Operation on device '
50001412
940375 (http://support.microsoft.com/kb/940375/)
FIX: Error message when you use the Copy Database Wizard to move a database from SQL Server 2000 to SQL Server 2005
50001522
939562 (http://support.microsoft.com/kb/939562/)
FIX: Error message when you run a query that fires an INSTEAD OF trigger in SQL Server 2005 Service Pack 2: "Internal Query Processor Error The query processor could not produce a query plan"
50001224
937100 (http://support.microsoft.com/kb/937100/)
FIX: Error message when you run a SQL Server 2005 Integration Services package that contains a Script Component transformation: "Insufficient memory to continue the execution of the program"
50001415
940377 (http://support.microsoft.com/kb/940377/)
FIX: Error message when you process cubes for one of the named instances of SQL Server 2005 Analysis Services: "Error opening file"
50001523
938363 (http://support.microsoft.com/kb/938363/)
FIX: Data is not replicated to a subscriber in a different partition by using parameterized row filters in SQL Server 2005
50001529
940945 (http://support.microsoft.com/kb/940945/)
FIX: Performance is very slow when the same stored procedure is executed at the same time in many connections on a multiple-processor computer that is running SQL Server 2005
50001578
939285 (http://support.microsoft.com/kb/939285/)
FIX: Error message when you run a stored procedure that starts a transaction that contains a Transact-SQL statement in SQL Server 2005: "New request is not allowed to start because it should come with valid transaction descriptor"
50001525
938086 (http://support.microsoft.com/kb/938086/)
FIX: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005
50000872
936252 (http://support.microsoft.com/kb/936252/)
The file name of Cumulative Update 3 for SQL Server 2005 Service Pack 2 is incorrectly associated with Microsoft Knowledge Base article 936252
50000872
The MDX query performance is slow in SQL Server 2005 Analysis Services because SQL Server 2005 Analysis Services does not reuse the data cache
50001109
The dta utility stops unexpectedly and an exception occurs in SQL Server 2005
50001224
When you run a SQL Server 2005 Integration Services package that uses the VariableDispenser class, the package fails and you receive an error message
50001365
After you install SQL Server 2005 Service Pack 2, you receive error 8624 if the result set of a fast forward cursor contains a certain number of columns
50001368
After you install SQL Server 2005 Service Pack 2, the performance of a MDX query is 10 times slower than the performance on SQL Server 2005 Analysis Services build 1555
50001396
When you open a SQL Server 2005 Reporting Services report after you install SQL Server 2005 Service pack 2, the parameter toolbar and the report toolbar does not appear correctly if you specify the SP_Full value for the rc:StyleSheet URL access parameter on a report URL
50001412
Error message when you use the Copy Database Wizard to move a database from SQL Server 2000 to SQL Server 2005: "Cannot drop database "Database_Name" because it is currently in use.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"
50001414
Error message when you use the bcp utility together with the queryout option to bulk copy data from SQL Server 2005 to a file: "SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native Client]BCP host-files must contain at least onecolumn"
50001415
After you install SQL Server 2005 Service Pack 2, the Msmdredir.ini file is frequently updated by each instance of SQL Server 2005
50001436
Error message when you use the BACKUP DATABASE statement together with the UNLOAD option and with the REWIND option to back up a tape device in SQL Server 2005: "Operation on device 'TAPE0(<\\.\Tape0>)' exceeded retry count"
50001461
When some MDX queries are executed at the same for the same role or for the same user in SQL Server 2005 Analysis Services, the CPU usage is very high
50001475
When you open a report that contains a date and time picker (DTP) control in SQL Server 2005 Reporting Services, the format of the DTP control appears incorrectly
50001511
Error 9003 occurs when you restore a transaction log backup in SQL Server 2005
50001520
The query syntax of a report is changed when you run the report in SQL Server 2005 Reporting Services Service Pack 2 on SAP BW 3.5
50001522
Error 8624 occurs when you run a query in SQL Server 2005 Service Pack 2 (SP2) or later versions. However, you can successfully run the query in pre-SP2 version of SQL Server 2005
50001523
After you install SQL Server 2005 Service Pack 2, publications that use precomputed partitions can cause the non-convergence of data
50001525
Error message when you use SQL Server Agent to run jobs by using a proxy account: " SQLServer Error: 22046, Encryption error using CryptProtectData, CryptUnprotectData failed (1723)"
50001526
Using SQL Server Agent to run jobs in the context of a proxy account may fail with error "SQLServer Error: 22046, Encryption error using CryptProtectData, CryptUnprotectData failed (1723)"
50001529
The performance of SQL Server 2005 decreases because SQL Server 2005 is waiting for access to memory objects which is indicated by the CMEMTHREAD waittype
50001578
An exception occurs in SQL Native Client: "New request is not allowed to start because it should come with valid transaction descriptor"
50001579
When you use SQL Native Client to retrieve a value in a column of the TEXT data type, you obtain incorrect result if the value contains more than 1024 characters
50001580
Memory leak of the TokenAndPermAccessCheckResult entries occurs in SQL Server 2005
50001595
When you use SQL Native Client for a connection, the connection switches to use the auto-commit mode from the manually-commit mode
50001598
SQL Native Client overwrite error codes which causes that you do not receive informative error messages when some operations fails
50001639
The performance of an INSERT statement or an UPDATE statement that uses the result from a query is much slower in SQL Server 2005 Service Pack 2 than in SQL Server 2005 Service Pack 1 or earlier versions if the query uses the nodes method
50001164
FIX: Error message when you connect to an instance of SQL Server 2008 Analysis Services by using the AMO library that is included with SQL Server 2005 Service Pack 2 Analysis Services: "Cannot connect to Analysis Services version '10.0.1019.17'"
Monday, September 10, 2007
SQL Gotcha: Do you know what data type is used when running ad-hoc queries?
This is for SQL Server 2000 only, SQL Server 2005 is a lot smarter which is another reason to upgrade.
When running the following query you probably already know that 2 is converted to an int datatype
SELECT *
FROM Table
WHERE ID =2
What about the value 2222222222? Do you think since it can't fit into an int that it will be a bigint? Let's test that out.
First create this table.
CREATE TABLE TestAdHoc (id bigint primary key)
INSERT INTO TestAdHoc
SELECT 1 UNION
SELECT 2433253453453466666 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
Now let's run these 2 queries which return the same data
SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666
SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)
Now run the following SET statement and run the 2 queries again
SET SHOWPLAN_TEXT ON
SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666
SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)
And what do we see?
First Query
--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))
--Compute Scalar(DEFINE:([Expr1002]=Convert([@1])-1,
[Expr1003]=Convert([@1])+1, [Expr1004]=If (Convert([@1])-1=NULL)
then 0 else 6If (Convert([@1])+1=NULL) then 0 else 10))
--Constant Scan
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id] > [Expr1002] AND [TestAdHoc].[id] < [Expr1003]), WHERE:(Convert([TestAdHoc].[id])=[@1]) ORDERED FORWARD)
Second Query
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id]=2433253453453466666) ORDERED FORWARD)
The first query has a much different execution plan than the second query. The first execution plan has a lot more than the second execution plan and will be a little slower.
So how do you know what dataype the value is converted to? Here is a simple SQL query which I first saw on Louis Davidson's blog. Just run this query.
SELECT CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'BaseType') AS varchar(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Precision') AS varchar(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Scale') AS varchar(10)) + ')'
So the output is this numeric(19,0). So instead of a bigint SQL Server converts the value to a numeric data type.
Here is another query which demonstrates the different datatypes used.
SELECT CAST(SQL_VARIANT_PROPERTY(2,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(222222222,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(2222222222,'BaseType') AS varchar(20))
So when running ad-hoc queries it is always a good practice to use parameters or inline convert statements.
When running the following query you probably already know that 2 is converted to an int datatype
SELECT *
FROM Table
WHERE ID =2
What about the value 2222222222? Do you think since it can't fit into an int that it will be a bigint? Let's test that out.
First create this table.
CREATE TABLE TestAdHoc (id bigint primary key)
INSERT INTO TestAdHoc
SELECT 1 UNION
SELECT 2433253453453466666 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
Now let's run these 2 queries which return the same data
SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666
SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)
Now run the following SET statement and run the 2 queries again
SET SHOWPLAN_TEXT ON
SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666
SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)
And what do we see?
First Query
--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))
--Compute Scalar(DEFINE:([Expr1002]=Convert([@1])-1,
[Expr1003]=Convert([@1])+1, [Expr1004]=If (Convert([@1])-1=NULL)
then 0 else 6If (Convert([@1])+1=NULL) then 0 else 10))
--Constant Scan
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id] > [Expr1002] AND [TestAdHoc].[id] < [Expr1003]), WHERE:(Convert([TestAdHoc].[id])=[@1]) ORDERED FORWARD)
Second Query
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id]=2433253453453466666) ORDERED FORWARD)
The first query has a much different execution plan than the second query. The first execution plan has a lot more than the second execution plan and will be a little slower.
So how do you know what dataype the value is converted to? Here is a simple SQL query which I first saw on Louis Davidson's blog. Just run this query.
SELECT CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'BaseType') AS varchar(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Precision') AS varchar(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Scale') AS varchar(10)) + ')'
So the output is this numeric(19,0). So instead of a bigint SQL Server converts the value to a numeric data type.
Here is another query which demonstrates the different datatypes used.
SELECT CAST(SQL_VARIANT_PROPERTY(2,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(222222222,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(2222222222,'BaseType') AS varchar(20))
So when running ad-hoc queries it is always a good practice to use parameters or inline convert statements.
Wednesday, September 05, 2007
Microsoft SQL Server 2008 CTP 4 Released
Microsoft SQL Server 2008 CTP 4 has been released 20070831 (yes that is ISO format)
So I have been sleeping for the last couple of days and missed this. Connect didn't email me either ;-(
This download comes as a pre-configured VHD. This allows you to trial SQL Server 2008 CTP 4 in a virtual environment.
Get it here: http://www.microsoft.com/downloads/details.aspx?familyid=6a39affa-db6e-48a9-82e4-4efd6705f4a6&displaylang=en&tm
So I have been sleeping for the last couple of days and missed this. Connect didn't email me either ;-(
This download comes as a pre-configured VHD. This allows you to trial SQL Server 2008 CTP 4 in a virtual environment.
Get it here: http://www.microsoft.com/downloads/details.aspx?familyid=6a39affa-db6e-48a9-82e4-4efd6705f4a6&displaylang=en&tm
Friday, August 31, 2007
Download LINQPad
Can't wait for C# 3.0 and LINQ? Well you don't have to! Dynamically query SQL databases today in LINQ: no more struggling with antiquated SQL. Download LINQPad and kiss goodbye to SQL Management Studio: LINQPad supports LINQ to objects, LINQ to SQL and LINQ to XML—in fact, everything in C# 3.0 and .NET Framework 3.5. LINQPad is also a terrific learning tool for experimenting with this exciting new technology.
No installation required: LINQPad ships as a simple "click and run" self-updating 300KB executable. Instant LINQ!
Download it here: http://www.albahari.com/linqpad.html
No installation required: LINQPad ships as a simple "click and run" self-updating 300KB executable. Instant LINQ!
Download it here: http://www.albahari.com/linqpad.html
Friday, August 24, 2007
Spam so bad it is funny
Hahaha, is this a babelfish translation?
FROM: BILLY BANGURAABUJA,NIGERIA
E-MAIL: billy_bangura2005@yahoo.co.uk
DEAR ONE
PERMIT ME TO INFORM YOU OF MY DESIRE OF GOING INTO BUSINESS RELATIONSHIP WITH YOU. I GOT YOUR CONTACT THROUGH MY CAREFUL SEARCH FOR TRUST WORTHY ASSISTANCE IN THE NET.
I PRAYED OVER IT AND SELECTED YOUR NAME AMONG OTHER (3) NAMES I GOT ALSO DUE TO IT'S ESTEEMING NATURE AND THE RECOMMENDATIONS GIVEN TO ME AS A REPUTABLE AND TRUST WORTHY PERSON I CAN DO BUSINESS WITH AND BY THEIR RECOMMENDATIONS I MUST NOT HESITATE TO CONFIDE IN YOU FOR THIS SIMPLE AND SINCERE BUSINESS.
I AM BILLY BANGURA PRESENTLY A POLITICAL REFUGEE IN ABUJA NIGERIA THE SON OF THE FORMER MINISTER OF FINANCE OF SIERRA LEONE, MR THAIMU BANGURA A GOLD DEALER.
DURING THE RECENT CRISIS IN MY COUNTRY (SIERRA LEONE), MY LATE FATHER WAS CAPTURED AND KILLED BY THE REBELS WHO ACCUSED HIM OF MIS-APPROPRIATION OF FUND AND EMBEZZLEMENT.
SHORTLY AFTER HIS DEATH, I ESCAPED TO NIGERIA-WEST AFRICA REGION FOR SATETY.
HE [MY LATE FATHER] LEFT SOME DOCUMENT WITH ME INDICATING CONSIGNMENT DEPOSIT MADE WITH A SECURITY COMPANY HERE IN NIGERIA BEFORE HIS DEATH, WHICH THE CONTENT US $50M {FIFTY MILLION UNITED STATES DOLLARS}.
THE BOX WAS DEPOSITED FOR SAFE-KEEPING AND YOUR ASSISTANCE IS REQUIRED BECAUSE MY STATUS AS POLITICAL REFUGEE DOSE NOT PERMIT ME TO HANDLE LARGE SUM OF MONEY HERE, AND YOU WILL ALSO BE EXCEPTED TO PROVIDE ME WITH INFORMATION ON INVESTMENT OPPORTUNITIES IN YOUR COUNTRY.
I DO NOT WISH TO CONTACT ANY OF MY LATE FATHER'S BUSINESS COMRADES/PARTNER BECAUSE I DO NOT KNOW WHO EXACTLY CAUSED HIS DEATH.
WHEN MY MOTHER DIED ON THE 21ST OCTOBER 1986, MY FATHER TOOK ME SO SPECIAL BECAUSE I AM MOTHERLESS.
HE ALSO EXPLAINED TO ME THAT IT WAS BECAUSE OF THIS WEALTH THAT HE WAS ACCUSED BY HIS ASSOCIATES, THAT I SHOULD SEEK FOR A FOREIGN PARTNER IN A COUNTRY OF MY CHOICE WHERE I WILL TRANSFER THIS MONEY AND USE IT FOR INVESTMENT PURPOSE, (SUCH AS REAL ESTATE MANAGEMENT). I AM HONOURABLY SEEKING YOUR ASSISTANCE IN THE FOLLOWING WAYS.
1) TO SERVE AS THE GUARDIAN OF THIS FUNDS IN YOUR COUNTRY SINCE I AM A BOY OF 28 YEARS OLD.
2) TO MAKE ARRANGEMENT FOR ME TO COME OVER TO YOUR COUNTRY TO FURTHER MY EDUCATION AND TO SECURE A RESIDENTIAL PERMIT FOR ME IN YOUR COUNTRY.
3) I AM SOLICITING FOR YOUR PERSONAL ASSISTANCE WITH TOP ARRANGE AND TO RECEIVE THIS MONEY ON BEHAIF OF ME AND ENSURE THAT THE MONEY IS UTILIZED WISELY IN A COUNTRY OF STABLE ECONOMY LIKE YOUR COUNTRY. THE PURPOSE OF THE MONEY WILL BE FOR A VIABLE INVESTMENT AFTER WE HAVE COLLECTED THE MONEY FROM THE SECURITY VAULT COMPANY.
MY REASON OF CONTACTING YOU IS FOR YOUR CO-OPERATION FOR YOU TO ASSIST ME IN THE REMOVING OF THE FUND OUT OF THE SECURITY COMPANY IN YOUR FAVOUR AND MY AS WELL. MOREOVER, TO HELP ME USE THE FUND CAREFULLY FOR INVESTMENT. ALL THE NECESSARY DOCUMENT ARE AT MY REACH. WITH CONFIDENCE IN YOU AS A TRUST-WORTHY PERSON TO ACT IN GOOD FAITH! I WILL INTRODUCE YOU TO AN ATTORNEY AS MY FATHER'S FOREIGN PARTNER WHO WILL ASSIST YOU WITH LEGAL PROCEEDINGS.
SINCE YOUR PARTICULARS WILL APPEAR EVERY VITAL DOCUMENT AS THE OWNER OF THE FUND THIS TRANSACTION WILL ATTRACT 30% FOR THE ASSIST YOU ARE RENDERING.
FURTHERMORE, YOU CAN INDICATE YOUR OPTION TOWARDS ASSISTING ME, AS I BELIEVE THAT THIS TRANSACTION WOULD BE CONCLUDED WITHIN SEVEN (7) DAYS YOU SIGNIFY INTEREST TO ASSIST ME AND ENDEAVOUR TO FURNISH ME WITH YOUR TELEPHONE AND FAX NUMBER FOR EASY COMMUNICATION.
THANKS AND GOD BLESS, HOPING TO HEAR FROM YOU SOONEST.
IN GOD WE TRUST.
YOURS SINCERELY,
BILLY BANGURA
NB: PLEASE REPLY ME THROUGH THIS MY PRIVATE E-MAIL (billy_bangura2005@yahoo.co.uk) FOR SECURITY REASON. THANKS
FROM: BILLY BANGURAABUJA,NIGERIA
E-MAIL: billy_bangura2005@yahoo.co.uk
DEAR ONE
PERMIT ME TO INFORM YOU OF MY DESIRE OF GOING INTO BUSINESS RELATIONSHIP WITH YOU. I GOT YOUR CONTACT THROUGH MY CAREFUL SEARCH FOR TRUST WORTHY ASSISTANCE IN THE NET.
I PRAYED OVER IT AND SELECTED YOUR NAME AMONG OTHER (3) NAMES I GOT ALSO DUE TO IT'S ESTEEMING NATURE AND THE RECOMMENDATIONS GIVEN TO ME AS A REPUTABLE AND TRUST WORTHY PERSON I CAN DO BUSINESS WITH AND BY THEIR RECOMMENDATIONS I MUST NOT HESITATE TO CONFIDE IN YOU FOR THIS SIMPLE AND SINCERE BUSINESS.
I AM BILLY BANGURA PRESENTLY A POLITICAL REFUGEE IN ABUJA NIGERIA THE SON OF THE FORMER MINISTER OF FINANCE OF SIERRA LEONE, MR THAIMU BANGURA A GOLD DEALER.
DURING THE RECENT CRISIS IN MY COUNTRY (SIERRA LEONE), MY LATE FATHER WAS CAPTURED AND KILLED BY THE REBELS WHO ACCUSED HIM OF MIS-APPROPRIATION OF FUND AND EMBEZZLEMENT.
SHORTLY AFTER HIS DEATH, I ESCAPED TO NIGERIA-WEST AFRICA REGION FOR SATETY.
HE [MY LATE FATHER] LEFT SOME DOCUMENT WITH ME INDICATING CONSIGNMENT DEPOSIT MADE WITH A SECURITY COMPANY HERE IN NIGERIA BEFORE HIS DEATH, WHICH THE CONTENT US $50M {FIFTY MILLION UNITED STATES DOLLARS}.
THE BOX WAS DEPOSITED FOR SAFE-KEEPING AND YOUR ASSISTANCE IS REQUIRED BECAUSE MY STATUS AS POLITICAL REFUGEE DOSE NOT PERMIT ME TO HANDLE LARGE SUM OF MONEY HERE, AND YOU WILL ALSO BE EXCEPTED TO PROVIDE ME WITH INFORMATION ON INVESTMENT OPPORTUNITIES IN YOUR COUNTRY.
I DO NOT WISH TO CONTACT ANY OF MY LATE FATHER'S BUSINESS COMRADES/PARTNER BECAUSE I DO NOT KNOW WHO EXACTLY CAUSED HIS DEATH.
WHEN MY MOTHER DIED ON THE 21ST OCTOBER 1986, MY FATHER TOOK ME SO SPECIAL BECAUSE I AM MOTHERLESS.
HE ALSO EXPLAINED TO ME THAT IT WAS BECAUSE OF THIS WEALTH THAT HE WAS ACCUSED BY HIS ASSOCIATES, THAT I SHOULD SEEK FOR A FOREIGN PARTNER IN A COUNTRY OF MY CHOICE WHERE I WILL TRANSFER THIS MONEY AND USE IT FOR INVESTMENT PURPOSE, (SUCH AS REAL ESTATE MANAGEMENT). I AM HONOURABLY SEEKING YOUR ASSISTANCE IN THE FOLLOWING WAYS.
1) TO SERVE AS THE GUARDIAN OF THIS FUNDS IN YOUR COUNTRY SINCE I AM A BOY OF 28 YEARS OLD.
2) TO MAKE ARRANGEMENT FOR ME TO COME OVER TO YOUR COUNTRY TO FURTHER MY EDUCATION AND TO SECURE A RESIDENTIAL PERMIT FOR ME IN YOUR COUNTRY.
3) I AM SOLICITING FOR YOUR PERSONAL ASSISTANCE WITH TOP ARRANGE AND TO RECEIVE THIS MONEY ON BEHAIF OF ME AND ENSURE THAT THE MONEY IS UTILIZED WISELY IN A COUNTRY OF STABLE ECONOMY LIKE YOUR COUNTRY. THE PURPOSE OF THE MONEY WILL BE FOR A VIABLE INVESTMENT AFTER WE HAVE COLLECTED THE MONEY FROM THE SECURITY VAULT COMPANY.
MY REASON OF CONTACTING YOU IS FOR YOUR CO-OPERATION FOR YOU TO ASSIST ME IN THE REMOVING OF THE FUND OUT OF THE SECURITY COMPANY IN YOUR FAVOUR AND MY AS WELL. MOREOVER, TO HELP ME USE THE FUND CAREFULLY FOR INVESTMENT. ALL THE NECESSARY DOCUMENT ARE AT MY REACH. WITH CONFIDENCE IN YOU AS A TRUST-WORTHY PERSON TO ACT IN GOOD FAITH! I WILL INTRODUCE YOU TO AN ATTORNEY AS MY FATHER'S FOREIGN PARTNER WHO WILL ASSIST YOU WITH LEGAL PROCEEDINGS.
SINCE YOUR PARTICULARS WILL APPEAR EVERY VITAL DOCUMENT AS THE OWNER OF THE FUND THIS TRANSACTION WILL ATTRACT 30% FOR THE ASSIST YOU ARE RENDERING.
FURTHERMORE, YOU CAN INDICATE YOUR OPTION TOWARDS ASSISTING ME, AS I BELIEVE THAT THIS TRANSACTION WOULD BE CONCLUDED WITHIN SEVEN (7) DAYS YOU SIGNIFY INTEREST TO ASSIST ME AND ENDEAVOUR TO FURNISH ME WITH YOUR TELEPHONE AND FAX NUMBER FOR EASY COMMUNICATION.
THANKS AND GOD BLESS, HOPING TO HEAR FROM YOU SOONEST.
IN GOD WE TRUST.
YOURS SINCERELY,
BILLY BANGURA
NB: PLEASE REPLY ME THROUGH THIS MY PRIVATE E-MAIL (billy_bangura2005@yahoo.co.uk) FOR SECURITY REASON. THANKS
Thursday, August 23, 2007
Summer SQL Teaser #13 Numeric
Hi and welcome to another fascinating SQL summer teaser. Summer it is except inPrinceton where it was 50 degrees this week.
There was no teaser last week because of a death in the family, I had to go to a wake and a funeral last week. That is why the teaser will be posted on a Thursday this week ;-)
look at these values
$55.69
1.4e35
2d4
3.7
412
How many numeric values do you see? What do you think SQL Server's ISNUMERIC function will return for those values?
Let's find out, run the following code
CREATE TABLE #Temp (Data varchar(18))
INSERT INTO #Temp VALUES('$55.69')
INSERT INTO #Temp VALUES('1.4e35')
INSERT INTO #Temp VALUES('2d4')
INSERT INTO #Temp VALUES('3.7')
INSERT INTO #Temp VALUES('412')
INSERT INTO #Temp VALUES(CHAR(9)) --tab
Now without running this try to guess which values will be 1 and which 0. I added a bonus ISNUMERIC(ColumnName + 'e0') function. What do you think that will do? Remember first try to guess and then run the code. Any surprises?
SELECT Data,
ISNUMERIC(Data) AS [IsNumeric],
ISNUMERIC(Data + 'e0') AS IsReallyNumeric
FROM #Temp
There was no teaser last week because of a death in the family, I had to go to a wake and a funeral last week. That is why the teaser will be posted on a Thursday this week ;-)
look at these values
$55.69
1.4e35
2d4
3.7
412
How many numeric values do you see? What do you think SQL Server's ISNUMERIC function will return for those values?
Let's find out, run the following code
CREATE TABLE #Temp (Data varchar(18))
INSERT INTO #Temp VALUES('$55.69')
INSERT INTO #Temp VALUES('1.4e35')
INSERT INTO #Temp VALUES('2d4')
INSERT INTO #Temp VALUES('3.7')
INSERT INTO #Temp VALUES('412')
INSERT INTO #Temp VALUES(CHAR(9)) --tab
Now without running this try to guess which values will be 1 and which 0. I added a bonus ISNUMERIC(ColumnName + 'e0') function. What do you think that will do? Remember first try to guess and then run the code. Any surprises?
SELECT Data,
ISNUMERIC(Data) AS [IsNumeric],
ISNUMERIC(Data + 'e0') AS IsReallyNumeric
FROM #Temp
Wednesday, August 22, 2007
geekSpeak recording - Real World SQL Server Integration Services with Matthew Roche
Channel 9 has posted a SQL Server 2005 Integration Services screen cast. From the site:
This geekSpeak is all about SQL Server 2005 Integration Services. We are lucky to have a real expert from the industry, Matthew Roche, to share his experiences with us. Matthew shows us how SSIS is a real development studio, since it's an instance of Visual Studio 2005, with new designers. It's very familiar for folks who are moving from a traditional development environment. He gets us familiar with tools and techniques, solutions and projects, like the drag-and-drop approach to building packages, opportunities to include scripts, and develop your own components as .NET Assemblies.
Matthew answers some great questions around performance, programmability. He helps us understand the distinction between data flows and control flows, and how best to manage SSIS packages. He offers some useful guidance on what scenarios merit what choices for moving data from one place to another - be it .NET app, SSIS, TSQL, BCP and so on.But the most important reason to watch is to find out what Matthew would "chew off his own mouse finger" to avoid having to do. :D
Be sure to check the geekSpeak blog for upcoming geekSpeaks!
Watch the screencast(WMV)
This geekSpeak is all about SQL Server 2005 Integration Services. We are lucky to have a real expert from the industry, Matthew Roche, to share his experiences with us. Matthew shows us how SSIS is a real development studio, since it's an instance of Visual Studio 2005, with new designers. It's very familiar for folks who are moving from a traditional development environment. He gets us familiar with tools and techniques, solutions and projects, like the drag-and-drop approach to building packages, opportunities to include scripts, and develop your own components as .NET Assemblies.
Matthew answers some great questions around performance, programmability. He helps us understand the distinction between data flows and control flows, and how best to manage SSIS packages. He offers some useful guidance on what scenarios merit what choices for moving data from one place to another - be it .NET app, SSIS, TSQL, BCP and so on.But the most important reason to watch is to find out what Matthew would "chew off his own mouse finger" to avoid having to do. :D
Be sure to check the geekSpeak blog for upcoming geekSpeaks!
Watch the screencast(WMV)
Subscribe to:
Posts (Atom)