|
|
|
|
-
It's a common practice for people who write T-SQL scripts to test each
statement one at a time before finally running the entire script. This can lead
to unexpected surprises. When each statement in a script is executed one at a
time, each is separate batch. If there are no GO statements, the entire script
executed as whole is a single batch. Let's look at some code.
/*
Execute the following three queries as a batch.
The third query will fail. The error message
says #test exists even though the previous query was a drop
table.
But if you execute all three queries one at
time (assuming #test doesn't exist when you start),
all three queries will
succeed.
Or put a go after each statement and all
three will work. */
select 1 as a into #test;
drop table #test;
select
1 as a into
#test;
Most people looking at the code expect that if all three statements were
highlighted and F5 pressed, all three statements would execute successfully
without any error messages. Instead, the following error appears:
Msg
2714, Level 16, State 1, Line 3
There
is already an object named '#test' in the database.
The third statement fails. But if each statement is highlighted and executed
one by one, each statement succeeds. For more fun, execute this statement by
itself after executing the first three statements as a single batch.
select * from #test;
Msg
208, Level 16, State 0, Line 1
Invalid
object name '#test'.
Now you've seen error messages saying that your temporary table exists and
that it doesn't exist! It makes me think of Schrödinger's
cat. Schrödinger had it easy - his quantum mechanics theories weren't
complicated by T-SQL. Now change the code to use a permanent
table instead of a temporary table and everything works in a single batch:
select 1 as a into test;
drop table test;
select
1 as a into
test;
It's important to understand how things work. What do we do when a complete
script fails? We usually start executing it a statement at a time until we find
the problem. In a scenario like this, no problem will be found when executing a
single statement at a time. When testing, it's important to understand that
executing a statement at a time is not always predictive of executing all
statements as a single batch.
|
-
As I prepare for my SQL PASS 2009 presentation "Solving the Bill of Materials Problem", I'm trying to make sure I use colors that are visible to people with colorblindness. I've found two tools to help accomplish this. Colorlab available at http://colorlab.wickline.org/colorblind/colorlab/ renders the standard web color palette as seen by people with various color vision deficiencies. Vischeck http://www.vischeck.com/vischeck/vischeckImage.php allows you to upload your own images (e.g., PowerPoint presentation, screen capture of SSMS) and see how they'd appear to people with any of three different types of color vision deficiency. My goal is to have SSMS configured so as many people as possible can easily see the code.
|
-
Microsoft finally announced the XP Mode optional feature for Windows 7: http://windowsteamblog.com/blogs/business/archive/2009/04/24/coming-soon-windows-xp-mode-and-windows-virtual-pc.aspx. By adding an XP virtual machine to your Windows 7 installation, you have the perfect fallback position for applications that won't run under Windows 7. Just install an application in the XP virtual machine and publish it to your Windows 7 All Programs menu. When the published application runs, it will appear to be running from Windows 7, but it will really be running from the XP virtual machine. You don't provide XP media or a license. This is a free addition to WIndows 7.
|
-
When writing queries, we often can think of more than one way to solve a problem. There is danger in assuming that two different queries are logically equivalent when they return the same results. For example, just because a database has referential integrity doesn't mean that it was always present or always enforced over the life of the database. Sometimes a query implicitly assumes that all data is good - that no constraints have ever been violated.
The sample code creates three tables that logically have referential integrity. The child table is a child of the parent table and the parent table is a child of the grandparent table. To help you navigate from table to table, data values show the data lineage as described below.
- parentId values 101 and 201 have a grandparentId of 1
- childId values 10203 and 10203 have a parentId of 203 and a grandparentId of 3
Logically, although not actually enforced with foreign key constraints in the sample code, these foreign key relationships exist:
Run the following three statements to create the tables:
create table grandparent (grandparentId int);
create table parent (parentId int, grandparentId int);
create table child (childId int, parentId int);
As the comments in the code indicate, you must choose the correct syntax for the inserts appropriate for your version of SQL Server. Of the 20 rows of child table rows inserted, 6 rows ultimately relate back to a grandparentId of 2.
/* Do only one set of inserts using the syntax appropriate for your server. */
/* SQL Server 2008 syntax */
insert into grandparent values (1),(2),(3);
insert into parent values (101,1),(201,1);
insert into parent values (102,2),(202,2),(302,2);
insert into parent values (103,3),(203,3),(303,3);
insert into child values (10101,101),(20101,101),(30101,101);
insert into child values (10201,201),(20201,201),(30201,201);
insert into child values (10102,102),(20102,102),(30102,102);
insert into child values (10302,302),(20302,302),(30302,302);
insert into child values (10103,103),(20103,103);
insert into child values (10203,203),(20203,203),(30203,203);
insert into child values (10303,303),(20303,303),(30303,303);
insert into child values (10301,301);
insert into child values (10401,401);
/* pre-SQL Server 2008 syntax */
--insert into grandparent values (1);
--insert into grandparent values (2);
--insert into grandparent values (3);
--insert into parent values (101,1);
--insert into parent values (201,1);
--insert into parent values (102,2);
--insert into parent values (202,2);
--insert into parent values (302,2);
--insert into parent values (103,3);
--insert into parent values (203,3);
--insert into parent values (303,3);
--insert into child values (10101,101);
--insert into child values (20101,101);
--insert into child values (30101,101);
--insert into child values (10201,201);
--insert into child values (20201,201);
--insert into child values (30201,201);
--insert into child values (10102,102);
--insert into child values (20102,102);
--insert into child values (30102,102);
--insert into child values (10302,302);
--insert into child values (20302,302);
--insert into child values (30302,302);
--insert into child values (10103,103);
--insert into child values (20103,103);
--insert into child values (10203,203);
--insert into child values (20203,203);
--insert into child values (30203,203);
--insert into child values (10303,303);
--insert into child values (20303,303);
--insert into child values (30303,303);
A Semi Join query is executed to find all of the child table rows that do not have a grandparentId of 2.
select * from child
where exists
(
select *
from parent
inner join grandparent
on parent.grandparentId = grandparent.grandparentId
where grandparent.grandparentId <> 2
and parent.parentId = child.parentId
);
An Anti Semi Join is used to return the exact same 14 row result set:
select * from child
where not exists
(
select *
from parent
inner join grandparent
on parent.grandparentId = grandparent.grandparentId
where grandparent.grandparentId = 2
and parent.parentId = child.parentId
);
If both queries return the same results and the results are correct (which they are), are both queries correct?
Foreign key constraints can be created and enabled as defined previously because there are no orphans. Since no foreign key constraints have been defined, we are free to insert orphans. Run these two statements to create two orphaned child rows:
insert into child values (10301,301);
insert into child values (10401,401);
Rerunning the Semi Join query returns the same original 14 row result set. Rerunning the Anti Semi Join query returns a 16 row result set that includes the two child rows. As a consultant, sometimes I encounter databases that have referential integrity and always intended to have referential integrity and thus shouldn't have any orphans, but they do. Failing to consider orphaned data can lead to unanticipated and even undesirable results. Certainly a query that yields undesirable results is incorrect.
As you've seen, when orphans are present, superficially equivalent queries can produce different results. Which result is correct is determined by your requirements. Now consider which query pattern is correct if the requirement is to delete all data that doesn't have a grandparentId of 2. The Anti Semi Join pattern is the correct choice for that requirement because it deletes the orphans, which the Semi Join does not. Here is the code to do this:
delete from child
where parentId not in
(
select parentId
from parent
inner join grandparent
on parent.grandparentId = grandparent.grandparentId
where grandparent.grandparentId = 2
and parent.parentId = child.parentId
);
The differences between the Semi Join and the Anti Semi Join can be taken advantage of to find orphans. By combining the queries with EXCEPT, the orphans are easily identified. Notice that the Anti Semi Join is first and EXCEPT is used to subtract the Semi Join's result set from the Anti Semi Join's result set.
select * from child
where not exists
(
select *
from parent
inner join grandparent
on parent.grandparentId = grandparent.grandparentId
where grandparent.grandparentId = 2
and parent.parentId = child.parentId
)
EXCEPT
select * from child
where exists
(
select *
from parent
inner join grandparent
on parent.grandparentId = grandparent.grandparentId
where grandparent.grandparentId <> 2
and parent.parentId = child.parentId
);
When searching for orphans using this technique, I have a habit of doing the EXCEPT twice. I do this to check my work. I know which query to subtract from which, but sometimes I'll make a copy/paste mistake. By running EXCEPT both ways, I'll catch the careless error.
In case you're wondering about my style choice of Anti Semi Join instead of something like antisemijoin, it came from Microsoft's style choice used in the graphical query plan in SSMS.
|
-
Right after doing a hard reset of my Windows Mobile phone, I realized that my phone’s contacts hadn’t been backed up. Now that’s a disaster! I had Kalen Delaney’s and Paul Nielsen’s cell phone numbers on my cell phone – not something to lose. From the MVP newsgroup for Windows Mobile devices, I found out about the beta version of Microsoft MyPhone. It’s a limited beta and it took a few days before my application was accepted. After installing MyPhone on my phone, I used MyPhone’s sync feature to backup my data to the cloud. I can now resync my Windows Mobile phone from the web. Even if I lose or damage my phone, I could sync a new phone from the MyPhone website and be back in business quickly.
Speaking of damaging a phone, have you ever wondered what to do with a phone that gets wet? Assuming you drop your phone into relatively clean fresh water, one of my neighbors has a repair technique that has served her very well more than once. She boils the water out. Think back to your physics class. Boiling doesn’t have to mean hot. Lower the atmospheric pressure and the boiling point temperature is depressed. My neighbor rushes her wet electronic devices over to her father’s house. He puts the phone or other device in a bell jar and uses a vacuum pump to reduce the pressure until water is boiling at room temperature. After a short period of time, all of the water in the phone is boiled away.
|
-
Following up on yesterday's post from Louis, sometimes you encounter databases with missing referential integrity constraints. Even when the database is intended to have RI, accidents happen. Maybe a script dropped RI constraints before a bulk load and didn't quite get all of the constraints recreated. Whatever the reason, it's good to have a script to identify tables that are not referenced and do not reference other tables. Here's a script that works on SQL Server 2005 and 2008 (updated to no longer use sysreferences as per Adam Machanic's suggestion):
SELECT sys.objects.name
FROM sys.objects
LEFT JOIN sys.foreign_keys referenced
ON sys.objects.object_id = referenced.referenced_object_id
LEFT JOIN sys.foreign_keys parent
ON sys.objects.object_id = parent.parent_object_id
WHERE sys.objects.type = 'U'
AND referenced.referenced_object_id IS NULL
AND parent.parent_object_id IS NULL
Louis did a good job explaining the importance of using the database engine to protect the integrity of the data. As a consultant, I've seen lots of databases with varying degrees of referential integrity. It never works out well in the long run when referential integrity isn't enforced in the database. Application code - even if it is christened object relational mapping - is no substitute for imposing referential integrity checks in the database. A relational database is more than a data storage repository - and it should be.
|
-
The last completely successful picture upload to my blog was on November 16, 2008. My first failed attempt at uploading a picture was on November 23, 2008. I never did figure out how to upload a picture using the built-in features of the Community Server software that runs this site. That's why I switched to Windows Live Writer. It worked very well for me at first. Then it started giving me error messages that look like the screen capture posted here: http://atomsite.net/blog/2009/01/13/PictureUploadBugWLW09.xhtml After changing a setting, I was able to suppress the error message about the thumbnail file not posting, but I still can't upload a file.
This Live Writer problem affects people using a variety of blogging servers. It's rare, but hardly unique to me. Peter DeBetta couldn't find anything wrong with my settings or any problems on the Community Server. I've installed Live Writer into absolutely pristine virtual machines of XP, Vista, 2008, and Windows 7. Same problem every time.
Although I would like to get Live Writer fixed, what I really want to do is to be able to post pictures. I have a significant number of blog posts I want to make but can't because they require screen captures. Live Writer has nice plugins for doing things like preserving the color coding from SSMS. Before switching to Live Writer, I was color coding my posts by hand. I don't want to go back to doing that.
I posted to the MVP private newsgroup for Windows Live, but no help was obtained. No luck from the public post at http://social.microsoft.com/Forums/en-US/writergeneral/thread/7c9ac805-57d5-49ce-8da4-d3ba3a752cec/, either.
Please help me get fully engaged in the blogging process again.
|
-
With all of the public outcry against executive compensation and irresponsible corporate behavior, April Fool's Day seems like the perfect day for offering solutions. My solution is to link executive pay to lottery ticket sales. Employees often have a good sense of when things are amiss. I've actually observed the formation of lottery ticket pools at work when things aren't going well. Large employers with cafeterias on the premises could add lottery ticket dispensers. When the employees are paying for their lunches, they could add a lottery ticket to their purchases. Executive compensation should be inversely proportionate to lottery ticket purchases. When sales are up, the workers realize that something is amiss and are desperate for a way out - no matter how improbable. After all, you can't fool all of the employees all of the time.
|
-
My boss and coworkers are wondering what valuable things I've learned at the 2009 MVP Global Summit. I have learned all kinds of really interesting things about Windows 7, all highly NDA except for one thing. You know that fish that appears on the Windows 7 beta desktop? It is a beta fish (species betta splendens), a fighting fish (not gonna take any disrespect from a hip Apple guy) known for blowing bubbles. Count the bubbles - there are seven.
|
-
How many people can say that they’ve done a manual full table scan for their valentine? I can. When I first met my wife, she introduced herself as “[FirstName] + [LastName]”, however my brain encountered an “OH, WOW!” error and stopped processing after her [FirstName]. I didn’t want to ask her or anybody else what her [LastName] was, lest she think I wasn’t paying enough attention to remember (when, really, the exact opposite was true). In today’s high-tech world, this would not have been a problem, but we met long before the internet and search engines. Over the next few months, I saw my wife many times at church and at school (I was a graduate student; she was on staff). But never was her [LastName] again presented to me. One day, she stopped by my townhouse when I wasn’t home. She left a note that only said “Call me.”, followed by her [PhoneNumber] and her [FirstName]. I wanted to keep my secret safe, so I had only one option – the phone book. I spent the next several hours conducting a manual scan of the Galveston white pages. I started on the first page and just kept reading until I found her phone number. It appeared in the W’s. At last armed with the knowledge of my valentine’s [LastName], I made the call. We’ve been married over 25 years now.
|
-
I'm talking about installing programs on Windows 7, not installing Windows 7 itself. Keep in mind that the Windows 7 family includes both the Windows 7 client desktop and Windows Server 2008 R2.
Some programs will not install on Windows 7 even though they'll run just fine without any problems whatsoever. When an msi file has a LaunchCondition that excludes Windows 7, the installation will fail. Here's an example of a LaunchCondition that prevents an application from being installed on Windows 7:
(VersionNT = 501 And ServicePackLevel > 1) or (VersionNT = 502) or (VersionNT = 600)
This can easily be fixed by using Orca, a free utility from Microsoft. Orca is part of the Windows SDK. You can download and install the entire Windows SDK for Windows Server 2008 and .NET Framework 3.5 iso (1.3 GB) from Microsoft or just Orca (1.8 MB) from Softpedia. If you download the SDK, you only need to install the Win32 Developer Tools part of it. Within C:\Program Files\Microsoft SDKs\Windows\...\Bin, find Orca.msi and install it. Although Orca is an application that is installed, it can also work as a portable application. After installing Orca, I was able to copy the Orca files to a USB memory stick and run Orca without installing it. It's part of my DBA toolkit on a stick that I wrote about previously.
To edit an msi file using Orca, you'll need to Run as administrator on Vista, 2008, and Windows 7. Select the LaunchCondition in the left pane and then select the problematic condition in the right pane. Notice that you have two options for implementing a workaround:
1. Delete the condition altogether (Right-click the condition and select Drop Row).
2. Edit the condition (set focus to the cell and edit the text of the Condition).
In this example, either of these corrections allows the installer to succeed on Windows 7:
(VersionNT = 501 And ServicePackLevel > 1) or (VersionNT = 502) or (VersionNT = 600) or (VersionNT = 601)
(VersionNT = 501 And ServicePackLevel > 1) or (VersionNT = 502) or (VersionNT >= 600)
If you are editing an msi that is deployed to customers, it's best to specify which versions of Windows that you know will work. If you want to leave things open ended, you can switch the = to >= as shown in the second example. If you're just doing some quick testing of Windows 7, I recommend simply dropping the row.
After completing your edits, save your changes and then exit Orca. The edited msi file is now ready to be successfully run in the Windows 7.
|
-
From an environmental standpoint, it's undesirable to use a physical DVD for installing an operating system. It's also not necessary. Instead of installing Windows Server 2008 R2 beta from a DVD, I used a USB flash drive. A USB flash drive can be made into a substitute for a DVD in just three easy steps without using DISKPART commands. 1. Attach the USB drive. Format it as NTFS. 2. Mount your iso file using MagicDisc, Daemon Tools, or Virtual CloneDrive. 3. At an elevated command prompt, enter the following: xcopy d:\*.* /s /e /h /f g:\ where:
D is the drive letter for your mounted iso file (this could also be a physical optical drive with an actual DVD)
G is the drive letter for your USB flash drive. If your drive letter mappings are different, make the appropriate substitutions. XCOPY switches are described here. 4. Attach your USB drive to the machine in question and boot. You may have to go into setup to force the machine to boot from the USB device. You may also have to enable booting from USB in the BIOS. In addition to using USB flash drives, you can use laptop drives attached via a USB adapter or external hard drive case. Not only will you save the environment, you will also save time because optical devices are relatively slow. I typically have old laptop drives preformatted and ready for an XCOPY.
|
-
My first post on getting started with SQL Server 2008 spatial data showed the results of displaying geography data. Before moving forward to advanced topics, it's time to pull back and learn the basics. It's easier to learn the fundamentals by using the other spatial data type, geometry. We're going to figuratively get some graph paper and remember our high school algebra and geometry lessons. Don't worry, everybody's going to get a good grade! The spatial data types and objects are described here in Books Online. We'll begin by creating a square box by using the polygon object. Although it is true that it takes four points to define a square, it takes five points to define a square using the polygon object. That's because we need to close the square by using the first point as the fifth and last point. Think of it as describing a path you are walking. After getting to the fourth point of the square, you want to walk back to the first point to close the loop. Here's the code to make and display a square two units in length on each side. declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
select @smallBox; 
A single box isn't an interesting as two boxes, especially when the two boxes have something in common. We'll continue by creating a larger box that partially intersects the first box. declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'; select @smallBox, @largeBox; We see the result result as before. That's not what we want to see. We want to see both polygons, both squares. 
In the previous code snippet, we treated the spatial objects as columns. That's why we didn't achieve the desired result. If the query is modified to return the large box first, only the large box appears. It is necessary to treat the spatial objects as rows to see all of them. declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'; select @smallBox
union all
select @largeBox; Now we see both polygons. Notice that the intersection of the two squares is a darker shade and easily identified visually. This is only a visual effect. To programmatically define and work with the intersection of the two polygons, a spatial data method call is required. 
To compute the intersection of two polygons, the STIntersection method is used. You can read more about geometry method calls here in Books Online. select @smallBox.STIntersection(@largeBox); If you modify the query slightly, you can get the coordinates of the polygon defining the intersection. declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'; select @smallBox.STIntersection(@largeBox).ToString(); POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1)) Although the following query doesn't produce significantly different results in the spatial viewer, it is different because it is actually displaying three distinct polygons. declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'; select @smallBox
union all
select @largeBox
union all
select @smallBox.STIntersection(@largeBox); 
Change the query as shown and use the STUnion operator instead of STIntersection. declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'; select @smallBox.STUnion(@largeBox); 
Most geometry method calls don't return visual results. For example, STArea returns a scalar value for the area. By inspection, you can see that the total area of the union of the two square boxes is 12. STArea computes the area of a spatial object: declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))';
select @smallBox.STUnion(@largeBox);
12 Other methods are Boolean and return either a 1 or a 0. Instead of seeing the intersection of two polygons, sometimes all you need to know is if the polygons intersect. declare @smallBox geometry = 'polygon((0 0, 0 2, 2 2, 2 0, 0 0))';
declare @largeBox geometry = 'polygon((1 1, 1 4, 4 4, 4 1, 1 1))'; select @smallBox.STIntersects(@largeBox);
1 More examples will be posted later.
|
-
-
Everybody knows local temporary tables have scope only within a connection. Drop the connection and it goes away. The connection is at the server level, not at the database level. People commonly say things like "I'm connected to the database" when they are actually connected to the server. In SSMS, you right-click on a specific database and select the option to open a new query window. Since you initiation the connection from a specific database name in the Object Explorer, it does seem like the database owns the connection. But the database doesn't own the connection in any way.
When you have a connection to a user database and create a local temporary table, the temporary table is created in tempdb. It's true that queries issued against your user database can access the temporary table, but they aren't related to each other, they are only sharing the same server connection. You can drop your database completely, but it does not affect any temporary objects you created in tempdb.
Creating a different user database doesn't change anything if all of the work is done within the same query window in SSMS. Although you are attempting to create a temporary table from a different user database, the fact remains that tempdb is the same and is being referenced from the same connection. Referring to the code sample below, the fact that it seems like you issued the second create table statement "from" database DEMO2 is irrelevant. The temporary table is issued from the server connection, not from the database you issued the USE statement against.
A user database named DEMO1 is created and a USE statement sets the query context to the DEMO1 database. Next, a temporary table is created. After that, the user database is dropped. Because your connection to tempdb persists, your temporary table persists. The point is that although your connection's context is the DEMO1 database, the temporary table really doesn't have anything to do with DEMO1. Although it arguably appears to have been created from demo1, it was created on the server in tempdb. The USE DEMO1 statement is irrelevant, it doesn't affect temporary objects in tempdb. The DEMO1 database doesn't in any manner have ownership of temporary objects in tempdb. Only the connection to the server owns the temporary objects. If you want to be able to create that local temporary table a second time without seeing an error message, you'll either have to drop it first or open a completely new query window in SSMS. If you don't, you'll see something like this:
Msg 2714, Level 16, State 6, Line 2
There is already an object named '#demo' in the database.
Thanks to a reader for pointing out corrections to this post. Here's some code you can play with to see for yourself.
use master
go
create database demo1 on primary
( name = demo1, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo1.mdf' ) -- alter filenames for your environment
log on
( name = demo1_log, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo1_log.ldf' )
go
use demo1
go
create table #demo (a int) -- succeeds
go
use master
go
alter database demo1 set single_user with rollback immediate
drop database demo1
go
create database demo2 on primary
( name = demo2, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo2.mdf' )
log on
( name = demo2_log, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo2_log.ldf' )
go
use demo2
go
create table #demo (a int) -- fails
go
use master
go
alter database demo2 set single_user with rollback immediate
drop database demo2
go
|
|
|
|
|
|