# Tuesday, 15 January 2008

A while back I wrote about Overridable IDENTITY or the order of things, offering a solution to store data in natural order. This solution worked form me in a couple of projects, until I recently had to reload a table... so the basic procedure would be to;

  • Create a new table based on the same definition
  • Do an INSERT new_table SELECT ... FROM old_table
  • DROP TABLE old_table
  • sp_rename 'new_table', 'old_table'

But this does not produce the proper result, as the most recent inserted identity prior to the insert will be applied for the default.

The trick is to make every insert an independent action, so the IDENTITY_CURRENT is properly set for every row; make it run in a cursor. Yeah, I know.... performance, but that pain is largely taken away by making it a FAST_FORWARD. Besides, if anyone has a better suggestion, I'm all ears. So instead of a INSERT ... SELECT, the CURSOR is opened for the selection and the new table is inserted based on the fetched rows.

Altogether in the attached script;

  • Create the soon to be table.
  • Populate the table.
  • Create the new table, pay attention to the default.
  • Use the cursor to fill the new table.
  • Drop the original table and rename the new table to the name of the original table, don't forget to recreate the default.
Tuesday, 15 January 2008 21:53:29 (W. Europe Standard Time, UTC+01:00)
# Monday, 14 January 2008
 #
 

... or at least didn't feel things were important enough to post, at least that's the excuse for not writing here for well over a month.

In the mean time, beta-season is opened again and I registered for the 71-647. However, I won't go trough the same depth of preparation as I did for the 70-649... I'll just go in and try to make it on my Windows 2003 and 70-649 prep-knowledge ;-).

The other exam I registered for is the 70-445 and I'm planning to take the 70-446 later this year. Just to get myself started for the preparation of this exam, I collected some links to hold on to:

and I'll be using the Microsoft courseware for the courses 2791, 2792, 2793, 2794 and the MCTS Self-Paced Training Kit (Exam 70-445): Microsoft® SQL Server™ 2005 Business Intelligence—Implementation and Maintenance.

That should keep me busy for a while again...

Monday, 14 January 2008 20:49:23 (W. Europe Standard Time, UTC+01:00)
# Thursday, 06 December 2007

One of the things that surface in module 2 of the 2780B course are the predefined reports you can use to get more info on what your system is doing. Good thing is, you can run these directly from Management Studio without Reporting Services. Bad thing, you don’t know what the reports are showing exactly (as in, you don’t know the report definition). That is, we can find out what the reports show since Microsoft released the report definitions, so you can reproduce what the report is showing. Also in Service Pack 2, Microsoft introduced the option to create your own custom reports… and that is just what one of the Microsoft Support Engineers did (performance dashboard). One of the community members also noticed and blogged about this new option and created his own DBA_Dashboard full of useful metrics. Thanks Greg, the effort is much appreciated, do keep up the good work for the next version.

Technorati tags: ,
Thursday, 06 December 2007 10:56:58 (W. Europe Standard Time, UTC+01:00)
# Thursday, 22 November 2007

About half a year ago I attended a webcast about ICE, today I can update that post with a link to the white paper. Maybe more on it later... when I found the time to actually read it.

Technorati tags: ,
Thursday, 22 November 2007 23:46:47 (W. Europe Standard Time, UTC+01:00)
# Tuesday, 13 November 2007

Bah, for the forth time in a week I encounter a star in the select-list of a view. So if no-one told you yet; when creating views, don’t do a SELECT * FROM source, it can (and in time will) hurt you badly. Let’s show by creating a table and two views based on that table;

CREATE TABLE tblSource(
    Col1 nchar(10),
    Col2 nchar(10))
GO
CREATE VIEW vwBad
AS
SELECT * FROM tblSource
GO
CREATE VIEW vwGood
AS
SELECT Col1, Col2 FROM tblSource
GO
INSERT tblSource (Col1, Col2) VALUES ('Column 1', 'Column 2')
GO

So far, so good. Selecting from both views shows you the expected results:

SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO

Now add a column to the table, and add some data for that column;

ALTER TABLE tblSource
    ADD Col3 tinyint
GO
UPDATE tblSource
SET Col3 = 3
GO

SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO

vwGood still behaves as expected, but vwBad doesn’t return ALL columns as you may have expected based on the *. Now let's see what happens if a column is removed from the table;

ALTER TABLE tblSource
    DROP COLUMN Col2
GO

SELECT 'vwGood' view_name, * FROM vwGood
GO
SELECT 'vwBad' view_name, * FROM vwBad
GO

You will notice that vwGood throws an error (rightfully so, it is instructed to select from a non-existing column), but vwBad doesn’t… it pretends Col3 is Col2. Based on the data, we know better. However this might not be so obvious if based on this change the purchase price is shown as sales price due to changes in the data model and your company starts losing money.

I bet now you want to know which views contain * so you can code them properly, try this query if you're on SQL Server 2005:

SELECT v.name view_name
    , s.name schema_name
    , u.name user_name
    , v.create_date
    , v.modify_date
    , sc.text
FROM sys.views v
    INNER JOIN sys.syscomments sc ON v.object_id = sc.id
    INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
    INNER JOIN sys.sysusers u ON s.principal_id = u.uid
WHERE sc.text LIKE '%*%'

And this one for SQL Server 2000:

SELECT so.name view_name
    , u.name user_name
    , so.crdate create_date
    , sc.text
FROM dbo.sysobjects so
    INNER JOIN dbo.syscomments sc ON so.uid = sc.uid
    INNER JOIN dbo.sysusers u ON so.uid = u.uid
WHERE sc.text LIKE '%*%' AND v.type = 'V'

And don't forget to clean up the example:

DROP VIEW vwGood
GO
DROP VIEW vwBad
GO
DROP TABLE tblSource
GO

Technorati tags: ,
Tuesday, 13 November 2007 12:35:23 (W. Europe Standard Time, UTC+01:00)
# Friday, 09 November 2007

As I wrote before, I passed my beta-exam and am well underway. Because of my experience preparing for the exam, the folks from NewLevel asked if I could do a presentation for them on Windows Server 2008... for marketing sake.

I said yes, so if you're interested, available on November 27th (13:30 - 16:00) and can be in Amersfoort:

  • Overview of product features and why you might want to use them.
  • How these features map to the new generation certifications and what this new generation certification could mean to you.
  • Training options to prepare for the Windows Server 2008 certifications and job-roles.
  • Besides all the talking, a demonstration deploying Network Access Protection in Windows Server 2008.

If you're interested, contact NewLevel by phone +31 73 599 0 150 or mail to Rein Floris at NewLevel. The presentation will be in Dutch an a small fee is charged.

Friday, 09 November 2007 20:05:57 (W. Europe Standard Time, UTC+01:00)
# Monday, 05 November 2007

Just visited the prometric-website, I've passed my 71-649. Also did a quick check on my MCP-transcript, but it's not showing there, yet! The preparation paid off.

Technorati tags: , , ,
Monday, 05 November 2007 23:06:34 (W. Europe Standard Time, UTC+01:00)

If an SQL Server 2005 database has multiple snapshots, do the first changes get written to all snapshots or only to the most recent? Good question, let's make the answer:

The following scenario is based on this sql-script. It assumes you have the AdventureWorks and your database engine uses C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Be careful, the script changes some data in AdventureWorks, so some examples from books-online may behave unexpected after this demo.

The scenario is in four stages;

  1. The first snapshot is created and a query displays the sizes of the snapshot file on disk (BytesOnDisk) and the internal size (BytesWritten).
  2. Person.Contact is updated, the Firstname and LastName for all records are swapped, practically resulting in the whole table being written to the snapshot before the update. Right after this update, the second snapshot is created and the query again shows the information about the (now 2) snapshots. The first snapshot holding the Person.Contact table before the update, the second being still empty.
  3. Person.Address is updated, AddressLine1 and AddressLine2 are swapped (and NULLs for AddressLine2 are set to empty strings). The query following this update shows both snapshots have grown, proving all snapshots get written (and we have the answer!!!).
  4. To show a snapshot only processes the changes once since it was created, the FirstName and LastName columns in Person.Contact are swapped again. This time, only the second snapshot gets written to (you may notice one or two data pages (8192 bytes) being added to the first snapshot, an insignificant change compared to previous changes).
Monday, 05 November 2007 22:36:03 (W. Europe Standard Time, UTC+01:00)
# Monday, 29 October 2007

Today's question of the course (2780B), how do licensing options mix with multiple instances. The course points out the choices on installation;

  • Per processor
  • Per Server
    • Device Client Access License
    • User Client Access License

That's about it, but as always, MS-Licensing is more complicated than a single page in the text-book. If you want to know it all, there is an SQL Server licensing white-paper, covering;

  • Virtualization / Multi-instances
  • Multi-core processors
  • Fail-over technologies (Cluster, Database Mirroring, Log Shipping)
  • Multiplexing
  • Separation of components over multiple servers.
  • SQL vs Workgroup CAL's
  • Special programs for ISV's and hosting providers
  • Downgrading

For just Virtualization and Multi-instancing, read this section of the above white-paper. For more info on licenses and pricing, visit How to buy SQL Server. Keep in mind that pricing and licensing may vary based on your location.

Monday, 29 October 2007 18:23:37 (W. Europe Standard Time, UTC+01:00)
# Thursday, 04 October 2007

... and other Microsoft exams. Since it's Microsoft's objective to have Performance Based Testing (a.k.a. simulations) in all MCTS exams, you'll want to have some peace of mind as to how they are scored (and about the relevance towards the total exam).

Read the full story at Trika's blog, I'm pretty sure more questions and answers will appear in the comments.

Thursday, 04 October 2007 10:20:32 (W. Europe Daylight Time, UTC+02:00)