Klaus Aschenbrenner - .NET German

Unique and non-unique non-clustered indexes on a unique clustered index

Klaus Aschenbrenner — Tue, 31 Aug 2010 16:38:26 GMT

In the last weblog post I have talked about the difference of unique and non-unique clustered indexes. As you have seen SQL Server uses an internal overhead of 4 bytes (the so-called uniquifier) to make non-unique clustered index rows unique. Today I want to work out the difference between unique and non-unique non-clustered indexes defined on a table with a unique clustered index. As you already know SQL Server creates a unique clustered index when you define the PRIMARY KEY constraint on a table. On the other hand you can use the CREATE UNIQUE CLUSTERED INDEX statement to create a unique clustered index on a table. The following listing creates our customers table, creates a unique clustered index on it, and finally creates one unique- and one non-unique non-clustered index on that table.

-- Create a table with 393 length + 7 bytes overhead = 400 bytes
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24
CREATE TABLE Customers
(
CustomerID INT NOT NULL,
CustomerName CHAR(100) NOT NULL,
CustomerAddress CHAR(100) NOT NULL,
Comments CHAR(189) NOT NULL
)
GO

-- Create a unique clustered index on the previous created table
CREATE UNIQUE CLUSTERED INDEX idx_Customers ON Customers(CustomerID)
GO

-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)

SET @i += 1
END
GO

-- Create a unique non clustered index on the clustered table
CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueNCI_CustomerID
ON Customers(CustomerName)
GO

-- Create a non-unique non clustered index on the clustered table
CREATE NONCLUSTERED INDEX idx_NonUniqueNCI_CustomerID
ON Customers(CustomerName)
GO

After the creation of both non-clustered indexes you can use the DMV sys.dm_db_index_physical_stats to get some information about the indexes. When you look into the DMV, you can see that the unique non-clustered index has a record length of 107 bytes and the non-unique non-clustered index has a record length of 111 bytes. So again, there must be a difference in the internal storage format of both indexes! Let's analyze it and start with the unique non-clustered index.

In my case the index root page of the unique non-clustered index is 4370, so I can dump it out very easily with the DBCC IND command:

DBCC PAGE(UniqueClusteredIndexStructure_NonClusteredIndex, 1, 4370, 3)
GO

As you can see from the following figure each index record contains the non-clustered key (which is unique in this case) – the column CustomerName:

When you examine the byte by byte representation of the unique non-clustered index record, you can see that SQL Server uses here the following bytes:

1 Byte: Status Bits
n Bytes: Unique Non-Clustered Index Key – in this case 100 bytes
4 Bytes: PageID
2 Bytes: FileID

In sum SQL Server uses the above mentioned 107 bytes per each index record on each non-leaf level of the unique non-clustered index. So again, the length of your non-clustered index key has an impact on how many rows SQL Server can store on an index page. So a CHAR(100) – like in this example – would be not a very good idea…

When you are walking down the unique non-clustered index until you reach the leaf-level of the index always stores the above mentioned 107 bytes per each index row – nothing more. When you finally dump out the leaf-level of the non-clustered index, you get the following picture:

As you can see here, SQL Server stores here at the leaf-level directly the clustered key – in our case the value of the column CustomerID. This value is for SQL Server the pointer to the corresponding record in the clustered index. With this value in the hand, SQL Server can now find the record in the clustered index – through a Clustered Index Seek operator. This is a big difference compared to non-clustered indexes defined on a heap table. Because in a heap table, SQL Server uses at the leaf-level the HEAP RID to point DIRECTLY to the corresponding data page where the record is stored. Therefore SQL Server can directly read the correct data page without accessing an additional index!

This also implies that SQL Server can find a record through a non-clustered index on a heap table faster than a record through a non-clustered index on a clustered table, because SQL Server don't have to execute the additional Clustered Index Seek operator. So the correct row can be found with less page reads on a heap table. But please don't over estimate this detail, and think that you will get a performance benefit by using non-clustered indexes on heap tables. The fact is that SQL Server always tries to store the index pages in the Buffer Manager, so it's really very cheap for SQL Server to do this additional Clustered Index Seek to get the correct record from the clustered index back.

Let's now analyze our non-unique non-clustered index. When you dump out the index root page, you can see that SQL Server stores here the non-clustered index key and also the clustered index key, which is different from the previous example with the unique non-clustered index:

SQL Server needs here the unique clustered index key to make each non-unique non-clustered index key unique. This behavior is done on EACH LEVEL of the non-unique non-clustered index, from the index root page down to the leaf-level. This means that you have a huge storage overhead, because SQL Server stores in EACH INDEX RECORD also your unique clustered key besides the non-unique non-clustered index key. So when you have a badly chosen clustered key (like CHAR(100), etc.) it will even get much more worse for you! When you analyze the index row you can see that SQL Server uses the following bytes for the storage:

1 Byte: Status Bits
n Bytes: Non-unique Non-Clustered Index Key – in this case 100 bytes
n Bytes: Unique Clustered Index Key – in this case 4 bytes for the integer value
4 Bytes: PageID
2 Bytes: FileID

When you sum up those bytes, you will get the 111 bytes mentioned earlier. So please keep this additional storage overhead in your head when using non-unique non-clustered indexes, because it impacts your non-clustered indexes on each level! You can download the T-SQL script for this posting here.

In the next installment of this series we will have finally a look into the differences of unique/non-unique non-clustered indexes defined on a non-unique clustered index. Stay tuned J

-Klaus

SQLdays 2010 – Fachkonferenz rund um SQL Server & Business Intelligence

Klaus Aschenbrenner — Mon, 30 Aug 2010 10:10:07 GMT

19.-20. Oktober 2010, Rosenheim

SQLdays ist die Konferenz für die deutschsprachige SQL Server Community vom 19.-20. Oktober 2010 in Rosenheim. Freuen Sie sich auf drei parallele Tracks mit über 20 Sessions zu SQL Server Administration, Entwicklung und Business Intelligence. Treffen Sie Datenbank- und BI-Experten wie Klaus Aschenbrenner, Markus Raatz, Steffen Krause, Thomas Grohser oder Willfried Färber persönlich und erfahren Sie News aus erster Hand und wertvolle Tipps und Tricks aus der Praxis. In ganztägigen Workshops vor und nach der Konferenz haben Sie zudem die Gelegenheit spezielle Themen nochmal bis ins Detail zu vertiefen.

Sichern Sie sich jetzt Ihre Teilnahme unter www.SQLdays.net

Unique/Non-Unique Clustered Indexes

Klaus Aschenbrenner — Thu, 19 Aug 2010 12:01:48 GMT

In the last blog post I have talked about unique/non-unique clustered indexes on a heap table. A table without a clustered index is called a heap table in SQL Server. When you define a clustered index on such a table, the table data gets structured and is therefore referred as clustered table. In this blog post I want to talk about the differences in unique and non-unique clustered indexes, and what are the storage impacts between those 2 types of clustered indexes.

As a prerequisite I assume that you have a basic understanding of clustered indexes, and that you know the difference between heap and clustered tables, and how your data pages are structured when a clustered index is defined on a table.

Let's start by looking on a unique clustered index. With SQL Server you have several possibilities to define a unique clustered index. The first way – the easy one – is to define a PRIMARY KEY constraint on a column. SQL Server enforces this PRIMARY KEY constraint through the creation of a unique clustered index on that table and that column. The another option is to create a unique clustered index through the CREATE CLUSTERED INDEX statement – but when you don't specify the UNIQUE property, SQL Server will create a non-unique clustered index by default for you! The following code fragment creates the Customers table that you already know from the previous blog posting, but this time we create a PRIMARY KEY constraint on the column CustomerID. Therefore SQL Server creates a unique clustered index on that table and sorts the data pages in the leaf level according the values in the column CustomerID.

-- Create a table with 393 length + 7 bytes overhead = 400 bytes
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24
CREATE TABLE Customers
(
CustomerID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
CustomerName CHAR(100) NOT NULL,
CustomerAddress CHAR(100) NOT NULL,
Comments CHAR(189) NOT NULL
)
GO

-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO Customers VALUES
(
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)

SET @i += 1
END
GO

After we have identified the index root page (through the use of the DBCC IND command), we can dump out that page with the DBCC PAGE command. In my case the index root page is 775:

DBCC PAGE(UniqueClusteredIndexStructure, 1, 775, 3)
GO

As you can see from the following figure each index record contains the clustered key, in this case the value of the column CustomerID.

When you examine the byte by byte representation of a clustered index record, you can see that SQL Server uses here the following bytes:

1 byte: Status Bits
n bytes: Clustered Key – in this case 4 bytes
4 bytes: PageID
2 bytes: FileID

As you can see the length of the clustered key has a direct relationship of the length of an index record. This mean as smaller your clustered key is, the more index record can be put onto an index page, and therefore your clustered index will be much more compact and will perform faster and are easier to maintain. When you walk down your clustered index you will see that all intermediate levels have the same storage format as described above. There are no differences on each level, expect the index leaf level, because this level contains your actual logically ordered data pages.

Let's have now a look onto non-unique clustered indexes in SQL Server and how they differ from unique clustered indexes. To demonstrate this kind of indexes, I have just recreated the Customers table and created a non-unique clustered index on that table through the CREATE CLUSTERED INDEX statement:

Finally I have inserted 80.000 records, where the column CustomerID (the clustered key) is not unique anymore:

-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 20000)
BEGIN
INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)

INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)

INSERT INTO Customers VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)
>

SET @i += 1
END
GO

When you now dump out the root index page of the non-unique clustered index, you get the following result:

As you can see, SQL Server returns here an additional column named UNIQUIFIER (key). This column is used by SQL Server to make a non-unique clustered key unique. Behind the scenes it is a 4 byte long integer value starting at 0. E.g. when you have 2 customers with the ID 1380 the first record gets the uniquifier value 0 and the second one gets the uniquifier value of 1. But SQL Server only stores the uniquifier in the navigation structure of an index (all levels above the leaf level), when the uniquifier is not equal to 0. SQL Server only includes uniquifier values of 0 in the navigation structure of a non-unique clustered index, which means that the navigation structure will never store the uniquifier physically. The only place where the uniquifier is stored in a non-unique clustered index is on the data pages, where the actual data records are stored. The following figure shows a data page dump of our clustered index, where you can also see the stored uniquifier.

So the only difference between a unique and non-unique clustered index is on the data pages, because when using a non-unique clustered index, SQL Server will use the 4 byte long uniquifier to make them unique, which is a small storage overhead that you have to keep in mind, when working with non-unique clustered indexes. You can download the T-SQL script for this posting here.

In the next posting we will work out the differences between unique/non-unique non-clustered indexes defined on unique clustered indexes. Stay tuned :-)

-Klaus

Unique and non-unique SQL Server indexes on a heap table

Klaus Aschenbrenner — Wed, 18 Aug 2010 16:04:49 GMT

In the upcoming weblog postings I want to work out the differences between unique and non-unique indexes in SQL Server. I assume that you already know the concepts about clustered- and non clustered indexes and how they are used in SQL Server.

In the past I've done a lot of trainings and consulting regarding SQL Server performance tuning and it seems that some people doesn't know the differences and implications between unique and non-unique indexes. And as you will see in the upcoming postings there are really big differences how SQL Server stores those two variants that impact the size and the efficiency of your indexes.

Let's start today with unique and non unique non clustered indexes on a table without a clustered index, a so-called heap table in SQL Server. The following listing shows how to create our test table and populate it with 80.000 records. Each record needs 400 bytes, therefore SQL Server can put 20 records on each data page. This means that our heap table contains 4.000 data pages and 1 IAM page.

-- Create a table with 393 length + 7 bytes overhead = 400 bytes
-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24
CREATE TABLE CustomersHeap
(
CustomerID INT NOT NULL,
CustomerName CHAR(100) NOT NULL,
CustomerAddress CHAR(100) NOT NULL,
Comments CHAR(189) NOT NULL
)
GO

-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
INSERT INTO CustomersHeap VALUES
(
@i,
'CustomerName' + CAST(@i AS CHAR),
'CustomerAddress' + CAST(@i AS CHAR),
'Comments' + CAST(@i AS CHAR)
)

SET @i += 1
END
GO

-- Retrieve physical information about the heap table
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('NonClusteredIndexStructureHeap'),
OBJECT_ID('CustomersHeap'),
NULL,
NULL,
'DETAILED'
)
GO

After the creation of the heap table and the data loading, you can now define a unique and non-unique non-clustered index on the column CustomerID of our heap table. We will define both indexes on the same column so that we can analyze the differences between unique- and non-unique non-clustered indexes.

-- Create a unique non clustered index
CREATE UNIQUE NONCLUSTERED INDEX IDX_UniqueNCI_CustomerID
ON CustomersHeap(CustomerID)
GO

-- Create a non-unique non clustered index
CREATE NONCLUSTERED INDEX IDX_NonUniqueNCI_CustomerID
ON CustomersHeap(CustomerID)
GO

If you want to define a unique non-clustered index on a column that doesn't contain unique data, you will get back an error message from SQL Server. Important to know is that SQL Server creates a non-unique non-clustered index if you don't specify the UNIQUE property when creating a non-clustered index. So by default you will always get a non-unique non-clustered index!

After the creation of both indexes you can analyze their size, their index depth, their size etc. with the DMV sys.dm_db_index_physical_stats. You can to pass in as the 3rd parameter the index-id. The IDs of all non-clustered indexes starts at 2, therefore the first non-clustered index gets the ID 2 and the second one the ID 3.

-- Retrieve physical information about the unique non-clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('NonClusteredIndexStructureHeap'),
OBJECT_ID('CustomersHeap'),
2,
NULL,
'DETAILED'
)
GO

-- Retrieve physical information about the non-unique non-clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
DB_ID('NonClusteredIndexStructureHeap'),
OBJECT_ID('CustomersHeap'),
3,
NULL,
'DETAILED'
)
GO

As you can see from both outputs, the index root page of the unique non-clustered index is occupied of around 24%, where the index root page of the non-unique non-clustered index is occupied of around 39%, so there must be a difference in the storage format of unique/non-unique non-clustered indexes on a heap table! In the next step we create a simple helper table that stores the output of the DBCC IND command. The structure of this helper table is directly taken from the excellent book SQL Server 2008 Internals.

-- Create a helper table
CREATE TABLE sp_table_pages
(
PageFID TINYINT,
PagePID INT,
IAMFID TINYINT,
IAMPID INT,
ObjectID INT,
IndexID TINYINT,
PartitionNumber TINYINT,
PartitionID BIGINT,
iam_chain_type VARCHAR(30),
PageType TINYINT,
IndexLevel TINYINT,
NextPageFID TINYINT,
NextPagePID INT,
PrevPageFID TINYINT,
PrevPagePID INT,
PRIMARY KEY (PageFID, PagePID)
)
GO

After the creation of this helper table we can dump out all pages that are belonging to our non-clustered indexes to this helper table with the following two calls to DBCC INC in combination with the INSERT INTO statement:

-- Write everything in a table for further analysis
INSERT INTO sp_table_pages
EXEC('DBCC IND(NonClusteredIndexStructureHeap, CustomersHeap, 2)')
GO

-- Write everything in a table for further analysis
INSERT INTO sp_table_pages
EXEC('DBCC IND(NonClusteredIndexStructureHeap, CustomersHeap, 3)')
GO

Now we can start analyzing our non-clustered indexes by using the undocumented DBCC PAGE command. You can find more information about this great command on Paul Randal's weblog. To get some information back from DBCC PAGE you have to enable the flag 3604 of DBCC:

DBCC TRACEON(3604)
GO

Let's dump out the index root page of our unique non-clustered index by the following command:

DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4192, 3)
GO

This will result in the following result in SQL Server Management Studio:

As you can see from this screenshot SQL Server stores the child page of the B-tree where the minimum key of the non-clustered index is located. The child page 4161 contains for example the record with the minimum key of 540 up to the maximum key of 1078. When you dump out the index root page with the dump option 1 you get the byte by byte representation of all index records on the index root page:

DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4192, 1)
GO

SQL Server needs here 11 bytes for storing an index row. These 11 bytes are storing the following information:

1 byte: Status Bits
4 bytes: Customer ID, like 540
4 bytes: child PageID, like 4161
2 bytes: FileID, like 1

As you can see it's up to the length of the non-clustered key how long an index row is. This also means that SQL Server is able to store more index rows on an index page if you choose a smaller non-clustered key. If you choose for example a CHAR(100) as a non-clustered index key, then SQL Server needs more index pages for your non-clustered index, which is not so efficient as using a smaller index key. The T-SQL script enclosed to this posting shows you how you can decode those bytes from the hexadecimal representation.

Finally you can dump out the child page 4161, which is located on the leaf-level of the non-clustered index.

DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4161, 3)
GO

As you can see from the figure, SQL Server stores for each index key on which data page and on which slot the corresponding record is located. Because we have not defined a clustered index on our table, SQL Server uses here the RID (Row Identifier) to point to the correct record on the data page. Index pages on the leaf-level on a heap table are different from leaf-level index pages defined on a clustered table (a table that contains a clustered index).When you dump out the leaf-level index page of the non-clustered index you can see that SQL Server needs 13 bytes per index row:

1 byte: Status Bits
4 bytes: CustomerID, like 540
4 bytes: PageID, like 178,
2 bytes: FileID, like 1
2 bytes: Slot number, like 19

Finally with this information in your hand, it is very easy to locate the correct record on the data page, because you know the PageID, FileID, and also the slot number where the record on the data page is located. Easy, isn't it?

Let's move on now to non-unique non-clustered indexes. Earlier we have already created such an index, which gets the index-id of 3 from SQL Server, because it's the second non-clustered index we have defined. In my case the index root page of the non-unique non-clustered index is located on page 4264, therefore I dump it out with the following command:

DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4264, 3)
GO

But wait! Now the result from DBCC PAGE on the root index page on a non-unique non-clustered index is different! As you can see SQL Server returns here an additional column named "HEAP RID (key)". The value in this column is used to make your non-unique non-clustered index unique. The HEAP RID column uses 8 additional bytes in your index row, which encodes the following information that are granted to be unique on a heap table:

4 bytes: PageID, like 178
2 bytes: FileID, like 1
2 bytes: Slot number, like 19

The overead of a non-unique non-clustered index on a heap table costs you 8 additional bytes per index row - on all index levels, expect the leaf-level, because SQL Server stores here always the HEAP RID as you have seen previously! So please keep this 8 bytes of additional index record overhead in mind, when you create non-clustered indexed that are NOT unique! And as I have said earlier, they are NOT unique by default!!!

In this example your non-unique non-clustered index is about 2 times bigger than the unique non-clustered index, because the unique index needs 11 bytes and the non-unique index needs 19 bytes (overhead of 8 bytes). When you look back to the output of the DMV sys.dm_db_index_physical_stats you can see that the index root page of the unique non-clustered index has a page space usage of around 24% where the index root page of the non-unique non-clustered index has a page space usage of around 39%. This will make a big difference on large non-clustered indexes!

So if you are just defining non-clustered indexes with

CREATE NONCLUSTERED INDEX ...

without thinking about the uniqueness of your data, you are wasting a lot of storage in your non-clustered indexes which also impacts the performance of your non-clustered indexes and their ongoing maintenance.
You can download the T-SQL script for this posting here.

In the next installment of this series we will have a look into the differences of unique clustered indexes and unique/non unique non-clustered indexes. Stay tuned :-)

-Klaus

Slides & Samples from my Solid Quality Summit sessions & workshop

Klaus Aschenbrenner — Sat, 26 Jun 2010 08:45:16 GMT

As announced in my sessions and my workshop at the Solid Quality Summit in Vienna, you can download the materials from here:

Thanks for attending my sessions and have fun :-)

-Klaus

Slides & Samples for my Developer Camp Workshop

Klaus Aschenbrenner — Mon, 26 Apr 2010 08:55:38 GMT

As announced in my Developer Camp workshop in Berlin you can download my workshop material from here:

SQLCLR/Service Broker

Thanks for attending my workshop and have fun :-)

-Klaus

Slides & Samples for my European PASS session

Klaus Aschenbrenner — Mon, 26 Apr 2010 08:51:41 GMT

As announced in my European PASS session you can download my session material from here:

FILESTREAM Storage Attribute

Thanks for attending my session and have fun :-)

-Klaus

Philip Aschenbrenner

Klaus Aschenbrenner — Sat, 06 Feb 2010 09:54:58 GMT

Born on February 4, 2010 – 09:08
3210 gr
51 cm
All systems are up & running :-)

-Philip, Karin & Klaus

Flightdeck Breitenlee – a Boeing 737-800 Flight Simulator

Klaus Aschenbrenner — Wed, 13 Jan 2010 09:08:13 GMT

It’s 4:30pm on a nice autumn day, the sun stands deep in the west of Vienna, and during it’s decent the horizon’s color slowly changes to red. In the next minutes we will get a wonderful sunset here in Vienna. Unfortunately my copilot and I have no time to relax and join the sunset, because we are currently sitting in a Boeing 737-800 and our lineup at the runway 29 in Vienna Schwechat was a few minutes ago. A few seconds ago, Schwechat tower has given us the clearance for takeoff with the following impressive words: “OE-AKS, cleared for takeoff on runway 29”.

We have successfully completed our prestart checklists, my left hand holds the yoke of the Boeing 737-800, and my right hand is on the throttle lever, which I move very carefully forward. After both engines have established, I release the parking brake, and activate the auto-throttle, which takes the control and management of both engines. A few seconds after our acceleration, my copilot calls out “80 knots” to inform me that both speed indicators are showing the same speed. A few seconds after, my cocpilot calls-out “V1” and I take my right hand away from the throttle lever – now there is no return for us – we have to start whatever event occurs!

After a few seconds, my copilot calls-out “VR” and I rotate the nose of the Boeing for around 3° per seconds after we have reached an angle of climb of around 15°. After our climb angle is positive, my copilot retracts the landing gear, and I activate the auto-pilot, which flies us with the SID “SITNI 4C”, which we have programmed earlier on the FMC, through the west out of the airport Vienna. But then, a few seconds before we reach our next waypoint, the unavoidable occurs, for which we had danger till the takeoff in Vienna: the cockpit door opens, and someone says to us: “Honey, dinner is ready, will you now come!?”

You have though that this story occurred in a real Boeing 737-800? You are completely wrong – welcome to Flightdeck Breitenlee, a Boeing 737-800 flight simulator in Vienna! The Flightdeck Breitenlee is a home-build Boeing 737-800 flight simulator, planned, builded and programmed by Klaus Aschenbrenner, which is now rented for your flight experiences. Currently the Flightdeck Breitenlee is driven by 5 high-end computers, and in the final step the flight simulator will have around 9 – 10 computers working in a network!

Who hasn’t dreamed as a child, to fly a big airliner such as a Boeing or an Airbus around the world? But unfortunately this child dream doesn’t come alive for some of us. On the other hand, till 9/11 you had the chance to walk into the cockpit during a flight and join the crew on the so-called “Jump seat” and watch their work in the air. But after 9/11 there is now no chance to get into the cockpit anymore… The only left option is to visit a flight simulator, like the A320 flight simulator at the Vienna Aviation Campus hosted by Lufthansa Flight Training. But a whole flight hour costs here around € 800 – of course without any flight instructor! So it’s not a real alternative…

The Flightdeck Breitenlee provides you for a fair price an almost high realistic simulated Boeing 737-800 flight simulator, based on the Microsoft Flight Simulator FSX. The whole building time of the flight simulator took around 2 years, where the planning and the research already started in the beginnings of the year 2006. Currently the Flightdeck Breitenlee offers you the following systems for your VFR/IFR flights:

The FMC (Flight Management Computer) simulates the whole pages of a Smiths CDU and enables you a complete flight planning with the creation of the necessary flight routes.
The MCP (Mode Control Panel) includes the auto-pilot and simulates the following modes:
- LNAV (in combination with the FMC)^
- VNAV (in combination with the FMC)
- HDG SEL
- LVL CHG
- VOR LOC
- APP
- ALT HOLD
- V/S
- SPEED
- N1
Through the EFIS you can control the ND display. The following modes are supported:
- APP
- VOR
- MAP
- PLAN
The motorized (yes, the throttle levers are moving during auto-throttle mode!) throttle quadrant includes:
- 2 motorized throttle levers
- Parking brake
- Speed-Brake
- Flaps-Lever
The MIP (Main Instrument Panel) includes:
- 3 LCD monitors, which represents the whole glass-cockpit of the Boeing 737-800
- All annunicators are fully simulated
- Gear-Lever
- Flaps-Gauge
- Realistic Auto-Break System (RTO, 1, 2, 3, MAX)
The center pedestal includes
- 2x NAV Panel
- 2x COM Panel
- 2x ADF Panel
- 2x Mic Selectors
- Weather radar
- TCAS Transponder
- 1x SELCAL
- Cargo Fire Panel
The Instructor Station concluces the Flightdeck Breitenlee, with which all possible failures and errors can be introduced into the ongoing flight. You can also control the behavior of the weather within a few seconds. Do you wanted to do a CAT III landing in Vienna with fog and heavy crosswind on Runway 11? For the simulator it’s not a problem – and for you?

For further information about the Flightdeck Breitenlee you can directly contact Klaus Aschenbrenner, where you can also arrange the boarding for your first Boeing 737-800 flight on the pilot’s seat!

Klaus Aschenbrenner
Pichlgasse 16/6
A-1220 Wien
http://flightdeck.csharp.at
Klaus.Aschenbrenner@csharp.at
+43 676 833 04 341

The center pedestal

The throttle quadrant

The EICAS

The PFD and ND

COM-, NAV- and ADF panels

The keypad of the CDU

Auto Break and Flaps Panel

The MCP

Slides & Samples for my SQLdays sessions

Klaus Aschenbrenner — Sat, 03 Oct 2009 08:05:00 GMT

As announced in my SQLdays sessions and in my workshop you can download my session materials from here:

Have fun :-)

-Klaus

Takeoff to a new dimension

Klaus Aschenbrenner — Thu, 03 Sep 2009 16:15:01 GMT

As you might already know from my previous blog postings, I’m currently building a fully-sized Boeing 737-800 flight simulator based on Microsoft Flight Simulator X. Please refer to this blog category for more information and pictures. The feedback along the several past months about this eciting project was very great and constructive:

“Klaus, you are just insane!”
“Keep going”
“Nice idea, when can I come flying?”
“Just great, when you arrange the next flying evening – I’m bringing the beer :-)”
“It can’t be described with words – it’s just fabulous”
“Can you bring your flight simulator to my next customer event? I think they will like it…”

As you can see from this feedback, I think I’m on the right direction with this amazing project. But when I looked at this feedback, I was thinking a lot of time about the last statement: “Can you bring your flight simulator to my next customer event? I think they will like it…”. The (short) outcome of the thinking process is easy – with the current configuration and the big size of the flight simulator, I’m not able to move it out of the room – so there’s no chance to move it out of my house…

But, there was more! The other outcome of my thinking process leaded me to the following idea: I’m currently building a 2nd “mobile” version of the flight simulator which can be taken from one place to other places very easy, because it is based on several distinct modules, which can be disassembled very easily. This 2nd version of my flight simulator just consists and simulates the pilot side of the Boeing 737-800, and NOT the co-pilot side, which is far enough for this “simplified” version. With this version, which will be available within the next 1 – 2 months (it mainly depends on my workload in my real-life), I’m then able to “fly” from event to event – AND also to YOUR events!

So if you think that this flight simulator will be an attractive/amazing option for YOUR event, just drop me line at Klaus.Aschenbrenner@csharp.at, and I will get back to you to discuss further details, how we can bring your event to a new dimension through flight simulation technology :-)

-Klaus

Einladung zum SQL Server 2008 Developers Summit

Klaus Aschenbrenner — Thu, 20 Aug 2009 17:13:21 GMT

SQL Server 2008 Developers Summit - Das Power-Event zur SQL Server 2008 Programmierung!
Lernen Sie konzentriert das, was Sie zur SQL Server 2008 Programmierung benötigen.
Sichern Sie sich diesen Herbst Ihren Technologievorsprung zum SQL Server 2008!

Zielpublikum: SQL Server 2005 Programmierer/innen
Termine: Montag 14.12.2009 bis Freitag 18.12.2009
Ort: Palais Strudlhof, Pasteurgasse 1, A-1090 Wien, Anfahrtsinformationen
Kosten: € 1.300,00 (im Betrag ist keine Umsatzsteuer enthalten)
Es ist ein eigenes Notebook mitzubringen!

Tagesablauf:

>

09:00 - 13:00 Workshops
13:00 - 14.00 Mittagessen
14:00 - 17:00 Workshops
Abends: Abendprogramm zum Ausklang des Tages

Das ergibt 5 x 8 Stunden gepowertes SQL Server 2008 Know-How zum Preis von nur € 1.300,00. Dieser Preis beinhaltet:

5 Tage Power-Workshops zum .NET SQL Server 2008
Aktuelles Buch zum SQL Server 2008
Begrüßungskaffee/Tee
Vormittagsjause
3-gängiges Mittagessen
Nachmittagsjause
Konferenzgetränke

Erfahren Sie alles, was Sie zur SQL Server 2008 Entwicklung in der tagtäglichen Arbeit benötigen!

Top-Themen:

Resource Governor
Policy based Management
Data Collector
Change Data Capture
T-SQL 2008
Business Intelligence Inside/Outside
Managed SQL Server 2008 Programmierung
Preview: SQL Server 2008 R2!

Weitere Informationen zum genauen Inhalt und zur Anmeldung finden Sie unter http://developers-summit.csharp.at

-Klaus

Maiden Flight of OS-AKS

Klaus Aschenbrenner — Wed, 19 Aug 2009 18:37:38 GMT

Here are some impressions of my first maiden flight in my own Boeing 737-800 flight simulator. I’m currently running the whole Project Magenta software suite for the captain side consisting of the MCP, the PFD, the ND, the EICAS, and the FMC. As a computer hardware foundation I’m using 4 PCs dedicated to the following roles:

PC #1: Run’s the flight simulator with FSUIPC
PC #2: Run’s the Glass Cockpit consisting of PFD and ND, and the MCP software
PC #3: Runs’ the lower and upper EICAS software
PC #4: Run’s the FMC software

During the maiden flight with this flight configuration was situated around the area of Vienna – LOWW. I departured at RW 29 heading to the west of Vienna.

As you can see I’m the auto pilot has taken me to 9000ft, with a cruising speed of 300kt with the heading 295. (Yes, I know there’s the speed restriction of 250kt under 10.000ft, but I’m in a simuator :-)). Here you can see the programmed FMC for the SITN2X SID from RW 29 in LOWW (but only for demonstration purposes, because I didn’t activated the programmed route)

After crossing Tulln (LOXT), I was on course towards Langenlois (LOAG) where I returned back home on course 114 heading to RW 11 in LOWW.

As you can see the auto pilot (in a single channel approach) is establishing the plane on the glideslope and localizer of RW 11. We are 6,5 miles away from the touchdown point at around 2600ft at 210kt.

We are cleared to land :-)

Last corrections are done by the auto pilot…

Everything is fine.

Stay tuned :-)

-Klaus

Finished my Solid Quality Summit presentations

Klaus Aschenbrenner — Sat, 20 Jun 2009 11:51:13 GMT

After a long time of work, I finally finished today my presentations for the Solid Quality Summit which takes place next week here in Vienna. I’m presenting the following sessions:

ADO.NET Enhancements
Spatial Data support in SQL Server 2008
Service Broker Scaleout scenarios
T-SQL Enhancements in SQL Server 2008

On friday I’m doing a whole day workshop about the SQLCLR integration in SQL Server 2008. As you can see, I have a lot of work to do next week. I’m looking forward to next week, to see many good friends from Solid Quality :-)

-Klaus

Just married!

Klaus Aschenbrenner — Wed, 17 Jun 2009 19:27:36 GMT

On May 23, we (Karin & I) had our wedding in Salzburg. Enclosed some impressions:

For our entertainment Tricky Niki joined us. Niki is a magician specialized in card tricks, pickpocketing and ventriloquism. The following picture shows Niki with Emil during his ventriloquism act :-)

So if you have a party, wedding etc, and need some entertainment don’t hesitate to contact Niki - just trust me: you will have a lot of fun!!!

-Klaus & Karin

Sample code for my European PASS conference sessions

Klaus Aschenbrenner — Thu, 30 Apr 2009 14:21:09 GMT

As announced in both of my sessions at the European PASS conference in Neuss/Germany last week, you can download the sample code here:

T-SQL enhancements in SQL Server 2008
Spatial data support in SQL Server 2008

I hope you have enjoyed my sessions :-)

-Klaus

Solid Quality Summit 2009

Klaus Aschenbrenner — Wed, 29 Apr 2009 21:19:27 GMT

Wir nähern uns mit großen Schritten dem Solid Quality Summit vom 22. bis 26. Juni, dem SQL Server Event des Jahres in Wien. Nun ist auch der Session Schedule fixiert. Einige Mentoren wie Dejan Sarka, Davide Mauri oder Klaus Aschenbrenner schleifen auf der European PASS Conference in Neuss gerade Ihre Sessions zurecht, um für das anspruchsvolle Publikum in Wien optimal vorbereitet zu sein. Aber auch Itzik Ben-Gan, dessen Buch „SQL Server 2008 Query Tuning“ soeben erschienen ist, Herbert Albert, Gianluca Hotz und die anderen Vortragenden scharen bereits in den Startlöchern, um Sie an ihren neuesten Erkenntnissen teilhaben zu lassen. Erleben Sie die einzigartige Kombination aus High-End Content und familiärer Atmosphäre beim Solid Quality Summit in Wien!
Alle Detailinformationen zum Summit finden Sie hier.
Anmeldeseite:.

Seminare:
Montag, 22.06.2009:
Itzik Ben-Gan: Inside T-SQL for SQL Server 2005 and 2008 (Room1)
Herbert Albert: Disaster Recovery (Room2)

Freitag, 30.06.2009:
Dejan Sarka: Data Modelling Essentials (Room1)
Klaus Aschenbrenner: Managed Code Development with SQL Server 2008 (Room2)

Session Schedule:

Day

Time

Speaker

Room1

Speaker

Room2

23.06.2009

09:00-10:15

Itzik Ben-Gan

Minimally Logged Inserts and other Data Modification Enhancements in SQL Server 2008

Dejan Sarka

Advances in Relational Databases

10:30-11:45

Aaron Johal

Help! My operational system is getting slower and slower!

Helmut Knappe

Implementing Many-to-many Relationships in SQL Server Analysis Services to Model Complex Business Solutions

13:30-14:45

Itzik Ben-Gan

Islands and Gaps Problems

Michael Sass

Meta Data Locks - Analysis and Performance Problems

15:00-16:15

Aaron Johal

Using Visual Studio Team System 2008 Database Edition to resolve the data perception

Helmut Knappe

Charts in Reporting Services 2008

16:30-17:45

Itzik Ben-Gan

Advanced T-SQL Tips & Tricks

Andreas Schindler

Best Practice ETL

24.06.2009

09:00-10:15

Klaus Aschenbrenner

Scaleout scenarios with SQL Server & Service Broker

Dejan Sarka

Understanding XQuery,

10:30-11:45

Itzik Ben-Gan

Using the HIERARCHYID Datatype in SQL Server 2008

Davide Mauri

Set Based solution: an approach for developers

13:30-14:45

Klaus Aschenbrenner

T-SQL Erweiterungen im SQL Server 2008

Gianluca Hotz & Herbert Albert

Implementing SQL Server 2008 Policy Based Management

15:00-16:15

Itzik Ben-Gan

Aggregating Data in SQL Server 2008 Using Grouping Sets

Gianluca Hotz & Herbert Albert

SQL Server 2008 Policy Based Management and multi-server administration

16:30-17:45

Klaus Aschenbrenner

ADO.NET Enhancements für SQL Server 2005/2008

Davide Mauri

Instrumenting, Monitoring and Auditing of SSIS ETL Solutions

25.06.2009

09:00-10:15

Dejan Sarka

Design Myths

Andreas Schindler

Solving Business Problems with MDX

10:30-11:45

Helmut Knappe

Business Intelligence Myths

Aaron Johal

Why must I shoehorn my data into tables? I need total flexibility to develop in an agile way!

13:30-14:45

Dejan Sarka

Temporal Data in SQL Server 2008

Andreas Schindler

Change Management für Analysis Services Cubes

15:00-16:15

Gianluca Hotz

Oracle to SQL Server migration for Developers I

Helmut Knappe

Using Windows PowerShell in BI

16:30-17:45

Gianluca Hotz

Oracle to SQL Server migration for Developers II

Klaus Aschenbrenner

SQL Server 2008 und der GEOGRAPHY Datentyp

Preise:
Full Summit 5 Tage inkl. 1Day Classes(regulär): 1.990,00 Euro (exkl. Ust.)
Full Summit 5 Tage inkl. 1Day Classes(Early Bird): 1.790,00 Euro (exkl. Ust.)
Early Bird bis 11.05.2009
1Day Class only 590,00 Euro (exkl. Ust.)
Slim Conference 3 Tage 1.490,00 Euro (exkl. Ust.)

Anmeldung:
Für Gruppen ab 3 Teilnehmern gibt es spezielle Ermäßigungen. Näheres dazu erfahren Sie per Mail unter flechnitz@solidq.com bzw. am Telefon unter +43 (676) 5397927
Bitte beachten Sie bei der Anmeldung für 1Day Classes, dass Full Conference Teilnehmer bevorzugt behandelt werden, sollten die vorhandenen Plätze knapp werden.

Ort:
it-versity
Schottenfeldgasse 69
1070 Wien
Wir freuen uns darauf, Sie im Juni beim Solid Quality Summit 2009 in Wien, dem SQL Server-Event des Jahres begrüßen zu dürfen.

Hardware & software partners for Flight Simulation

Klaus Aschenbrenner — Mon, 27 Apr 2009 17:42:14 GMT

You can’t build the entire home cockpit from scratch without any partners in the hardware and software industry. Let’s have a first look on the hardware partners I’m working with.

OpenCockpits
Conrad Electronics
Simparts.de

OpenCockpits
You can find them at http://www.opencockpits.com. What they are offering you is just amazing: they have build hardware cards, which interact with external hardware switches. Their hardware cards are connected through a USB connection to the computer, where the software is running which is interacting with the hardware and the flight simulator software. Further more they also sell panels on which you can mount the hardware switches. The panels are as real as possible and very cheap and a very, very good starting point for building home cockpits. I have currently running the following hardware cards from Open Cockpits:

Master Card
USB Expansion Card
USB Keys Card
USB Servo Motor Card

Conrad Electronics
Just go to http://www.conrad.at and you will find a lot of switches, LEDs etc. that you will need for building your home cockpit. As you will see in the next weblog posts, you can build around 80% of your home cockpit with hardware switches from Conrad Electronics. But don’t tell them what you are doing with the bought hardware, they look a little bit crazy, when you tell them that you are building a Boeing 737-800 flight simulator with their hardware *gggg*

Simparts.de
I very interesting supplier I found in the last weeks. If you need hardware that you can’t find at Conrad Electronics, just try http://www.simparts.de. They have for example a dual encoder with a push button, which you need for the EFIS (EFIS: Electrical Flight Information System).

Here are the software partners I’m working with:

Project Magenta
Microsoft

Project Magenta
They (http://www.projectmagenta.com) are providing you the whole system logic for your home cockpit in the needed deep. The “problem” with Microsoft Flight Simulator X is, that you can access the internal functionality very easily from the outside world, but some functionalities are not completed 100%. And this is the point where Project Magenta starts. They provide you every hardware circuit and hardware logic which is available on an real airliner in software. So you can attach your hardware switches to their system logic and their system logic is working against Flight Simulator X. When you for example are pressing a button on the overhead panel, Flight Simulator X just changes the state of the associated variable inside it’s own software. But with Project Magenta they are also checking other variables and other environmental requirements regarding the official Boeing operation manuals. Furthermore Project Magenta provides you external software visualizations for the whole glass cockpit of the Boeing and Airbus fleet – very amazing.

Microsoft
I’m using the Microsoft Flight Simulator X currently on Windows Vista as the flight simulation system. Furthermore I use the .NET framework to program my hardware switches against the Project Magenta software. This means the following: I’m changing a real hardware switch => my programmed software obtains this hardware event => my programmed software changes a variable inside Project Magenta => Project Magenta provides it’s own simulation logic and finally changes the correct variable inside Flight Simulator X. In the next weblog post we will have a more detailed look on this architecture I have chosen.

-Klaus

What you have to know to succesful build a home cockpit

Klaus Aschenbrenner — Mon, 27 Apr 2009 17:21:45 GMT

Building a Boeing 737-800 is one of the most challenging activities I’ve done in my whole life. If you want to be successful, you need to know a lot in the following areas (or do several months of internet research as in my case):

Construction engineering
Electricals
Software programming (that’s my main area)
Flight experience on a Boeing 737-800 – the most complicated point ;-)

Let’s have a look on each of these topics.

Construction engineering
When we speak about building a full scale flight simulator, we are talking about building the whole (!) cockpit shell in REAL dimensions. In my case I have a dedicated room in the basement of my house (which we build in the year 2007…), which has enough room for a whole cockpit shell. The main structure of my MIP (main instrument panel) currently consits of MDF panels (MDF is the english word for “Spannholzplatten” in german :-)). Now you would ask: and from where I get the real dimensions of the cockpit shell? Answer: internet research!

The big problem here is, that you can’t search to site xyz, and read everything about building cockpit shells. You need to visit several different sites, and combine their concepts to your final solution, which works best for you. There is no complete solution which fits for everyone – unfortunately. But that’s the great aspect of doing such things: reseraching, making prototypes, checking them against requirements and refine them, until you have found the ultimate solution.

Electricals
Another very important thing about building flight simulation cockpits is hardware engineering. You are working directly with the bits & bytes of your hardware switches instead of using great APIs (application programming interfaces) around these “input types”. In my case it took me several weeks, until I was able to use a hardware switch (bought at the electrical shop Conrad) to move up and down the gear – but it’s amazing when you have done it the correct way. In subsequent weblog posts I’ll look at the different hardware switches you will need at least to build a fully functional cockpit. You also need to know something about the inner workings of electronics and your used hardware switches, because when you have problems, you can’t just attach a debugger and debug the problem. Here you are working with wires and voltage!

Software programming
As soon as you have build your hardware interface, you have to program the whole thing to interact with the real flight simulation software – in my case with Microsoft Flight Simulator X. I have a long history with Microsoft technologies and especially with the .NET framework, therefore I’ve build a managed interface wrapper assembly around my hardware switches, with which I can interact with the switches and output controls (like LEDs) in an event driven approach. If you do not know something about software programming, don’t try to build a home cockpit.

Flight experience on a Boeing 737-800
That’s the most complicated part to achieve in real world, and after 9/11 it’s very hard (or let’s say: impossible!) to get in any cockpit during the flight :-(. You have only 2 options here:

Research through the internet: there are great Boeing operation manuals available for download, but they are just describing each switch in the cockpit. They don’t tell you the interdependencies between the switches and how one switch will affect another one in the cockpit.
Try to find a friend (or a person who will be your new friend) who is flying a Boeing 737-800. I’m currently going for this way, and talking to any pilot I can see when I’m on my business trips. Unfortunately our local airline (Austrian Airlines) only flies A-319 & A-320 within Europe (short haul distances) and B-767 & B-777 to US, Australia, China (long haul distances) etc. So currently I have no access to a person who flies the 737-800.

In the next weblog post I’ll describe the several hardware and software partners I’m working togehter to build this amazing project.

-Klaus

Current pictures of my Boeing 737-800 flight simulator

Klaus Aschenbrenner — Tue, 21 Apr 2009 18:33:47 GMT

As you already might know, I’ve found a new hobby in the last few years – I’m currently building a fully functional fixed-based Boeing 737-800 flight simulator. I’ve started this project around January 2006 with a very, very long research phase (around 18 months). During the christmas holidays of 2008 I decided to put this amazing project into reality and started building the main structure of my flight simulator and several hardware interfaces that are working together with Microsoft Flight Simulator X. Here you will see some impressions of the current status. In the next ongoing weblog posts I will dive into the technical details of my flight simulator, and I will show you how things are done and implemented.

Figure 1: The MIP (main instrument panel) of the Boeing 737-800 flight simulator in >>>REAL<<< dimensions.

Figure 2: The pilot’s side of the MIP. The left window frame is the PFD (primary flight display), where the window right window frame next to the PFD is the ND (navigational display).

Figure 3: The auto-break/flaps panel.

Figure 4: The upper part of the landing gear.

Figure 5: The lower part of the landing gear.

Figure 6: The inner workings of the MCP (Mode Control Panel), aka Auto-Pilot. It took me around 3 months to build and program it.

Figure 7: Parts of the MCP from the front.

Stay tuned for further more detailed and technical information :-)

-Klaus

Why it was so noisy on my weblog

Klaus Aschenbrenner — Thu, 12 Mar 2009 22:17:02 GMT

Well, a very good question: the last months were very stressful for me, and therefore I had to take priorities, and blogging wasn't the number one priority. But things are changing. By now I'm trying to post a lot more than in the past (which is very easy, as you can see from the history of my blog...)

Since february I'm employed as an software architect & consultant at EDS, an HP company in Vienna. I've been now around 6 weeks with EDS, and it's very amazing to seen how such a global player is working. And also the combination with HP will be very nice - just trust me :-) Currently I'm doing a lot of SQL Server related stuff (my main topic, as you might already know), and also some things with .NET related technologies, evangelizing some great internal EDS technologies in Europe.

Here's also my upcoming conference schedule for the next weeks and months, where you can meet me face-2-face:

DevWeek Conference London, UK (23. - 27. march): I'm doing one session about ADO.NET enhancements for SQL Server 2005/2008 and one session about distributed applications with the .NET framework 3.5
European PASS Conference Neuss, Germany (23. - 24. april): I'm doing 2 session about SQL Server 2008: GEOGRAPHY data type and T-SQL enhancements
Solid Quality Summit Vienna (June): I'm doing 4 (!) SQL Server related sessions together with my very good friends at Solid Quality Learning

See you!

-Klaus

Finished my DevWeek preparations

Klaus Aschenbrenner — Thu, 12 Mar 2009 22:08:37 GMT

A few minutes ago I finished my preparations for the upcoming DevWeek conference by the end of March in London, UK. One topic that I'll be talking about is building a fully distributed .NET application based on the .NET framework 3.5 technologies, WCF, WF, and WPF.

I'll show the following things:

How to use the "Workflow Services" technology from the .NET framework 3.5
How to store workflow definitions in a database, so that business analysts can change them according to their (changing) business requirements
How to write a workflow designer, that can be used by the business analysts to change the workflow definitions that are stored in the database
How to use the workflow monitor application from the SDK to monitor the current running and closed workflow instances
How to use the TrackingProfileDesigner application from the SDK to configure tracking profiles for the running workflow instances

All in all: it's just an awesome sample how to use the current .NET framework 3.5 technology stack!

See you there!

-Klaus

Slides for Team System Conference, Munich/Germany

Klaus Aschenbrenner — Sun, 27 Apr 2008 07:48:55 GMT

Thanks for attending my session about Team Build 2008 at the Team System Conference in Munich/Germany this week. As announced in my session, you can download the slides here. Enjoy it! :-)
-Klaus

Slides & Demos for European PASS Conference, Neuss/Germany

Klaus Aschenbrenner — Thu, 17 Apr 2008 18:02:31 GMT

Thanks for attending my sessions at the European PASS conference in Neuss/Germany this week. As announced in both of my sessions, you can download the whole material (Slides & Demos) for both sessions here. Enjoy it! :-)

-Klaus

Einladung zum SQL Server 2008 Business Intelligence Developers Summit

Klaus Aschenbrenner — Tue, 15 Apr 2008 18:26:13 GMT

Lernen Sie konzentriert das, was Sie zur BI Programmierung mit dem SQL Server 2008 benötigen.
Sichern Sie sich Ihren Technologievorsprung zur SQL Server 2008 BI Programmierung bereits diesen Sommer!

Zielpublikum: SQL Server 2000/2005 Programmierer
Termine: Montag 02.06.2008 bis Freitag 06.06.2008
Ort: Palais Strudlhof, Pasteurgasse 1, A-1090 Wien, Anfahrtsinformationen
Kosten: € 1.300,00 (im Betrag ist keine Umsatzsteuer enthalten)
Rabatt: Ab 3 Anmeldungen pro Firma wird Ihnen ein Rabatt von 10% pro Teilnehmer gewährt!

Es ist ein eigenes Notebook mitzubringen!

Tagesablauf:

>

09:00 - 13:00 Workshops
13:00 - 14.00 Mittagessen
14:00 - 18:00 Workshops
Abends: Abendprogramm zum Ausklang des Tages

Das ergibt 5 x 8 Stunden gepowertes SQL Server 2008 BI Know-How zum Preis von nur € 1.300,00. Dieser Preis beinhaltet:

5 Tage Power-Workshops zur SQL Server 2008 BI Programmierung
Begrüßungskaffee/Tee
Vormittagsjause
3-gängiges Mittagessen
Nachmittagsjause
Konferenzgetränke

Erfahren Sie alles was Sie zur SQL Server 2008 BI Entwicklung in der tagtäglichen Arbeit benötigen!

Warum teilnehmen?
Mit dem SQL Server 2008 stellt Microsoft eine umfassende Entwicklungsumgebung für Business Intelligence Anwendungen auf der Microsoft Plattform zur Verfügung. Das SQL Server 2008 BI Developers Summit ist daher eine ausgezeichnete Möglichkeit, sich schnell und praxisgerecht auf die BI Programmierung mit dem SQL Server 2008 ausbilden zu lassen. Bei dem Speaker (Willfried Färber, SQL Server MVP) handelt es sich um einen international anerkannten Experten, der sich bereits seit über 10 Jahren mit dem SQL Server und der Business Intelligence Entwicklung beschäftigt, und daher bereits umfangreiches Know-How aufbauen konnte und dieses in ausgezeichneter Qualität an die Teilnehmer weitergibt.

Top-Themen:

Theoretischer Hintergrund zu Data Marts und dem Data Warehouse
Planung, Konzept und Entwurf eines Data Warehouse
Einführung in die Integration Services
Erstellen von Ladejobs für ein Data Warehouse
Einführung in die Analysis Services
Einführung in MDX
Erstellung und Validierung eines Cubes

Weitere Informationen finden Sie unter der Event-Homepage.

-Klaus

Demos for .NET User Group Austria Talk, Vienna

Klaus Aschenbrenner — Fri, 28 Mar 2008 18:06:11 GMT

Thanks for attending my SQL Server 2008 introduction session on Thursday 28 in Vienna at the .NET User Group Austria, hosted by Christian Nagel. As announced in my session, you can download the samples from my session here.

-Klaus

Freie Plätze .NET 3.5 Developers Summit

Klaus Aschenbrenner — Tue, 25 Mar 2008 18:45:31 GMT

Für das .NET 3.5 Developers Summit, dass ich vom 21.04.2008 - 25.04.2008 gemeinsam mit Christian Nagel veranstalte, gibt es noch einige freie Plätze. Wer also daran Interesse hat, meldet sich einfach bei mir.

-Klaus

Solid Quality Summit Vienna, May 26 - May 30

Klaus Aschenbrenner — Tue, 25 Mar 2008 18:42:04 GMT

In May my friends from Solid Quality are delivering the best SQL event in Vienna this year, the Solid Quality Summit Vienna! If you want to get up-to-date with SQL Server 2005/2008, don't miss this event and the following very great speakers:

Itzik Ben-Gan

Dejan Sarka

Gianluca Hotz

Davide Mauri

Maciej Pilecki

Herbert Albert

Helmut Knappe

Andreas Schindler

Karol Papaj

Klaus Aschenbrenner :-)

As you can see I'm also particating at this event with 2 sessions: SQLCLR development and (of course!) SQL Service Broker :-)

See you there!

-Klaus

SQL Server 2008 {JumpStart}

Klaus Aschenbrenner — Tue, 25 Mar 2008 18:36:55 GMT

If you want to get up-to-date with SQL Server 2008, I recommend you to visit the website SQL Server 2008 {JumpStart}. This site contains 29 Hands-On-labs around all the new features of SQL Server 2008. The SQL Server 2008 {JumpStart} event was an event delivered by SQLskills.com internally for Microsoft employees and several selected MVPs. So get your chance, grab the content from the website, and start playing around with SQL Server 2008!

-Klaus

Einladung zum .NET 3.5 Developers Summit

Klaus Aschenbrenner — Wed, 05 Mar 2008 19:28:04 GMT

Lernen Sie konzentriert das, was Sie zur .NET Framework 3.5 Programmierung benötigen.
Sichern Sie sich Ihren Technologievorsprung zum .NET Framework 3.5 bereits diesen Frühling!

Zielpublikum: .NET Framework 3.0 Programmierer/innen
Termin: 21.04.2008 – 25.04.2008
Ort: Palais Strudlhof, Pasteurgasse 1, A-1090 Wien, Anfahrtsinformationen
Kosten: € 1.300,00 (im Betrag ist keine Umsatzsteuer enthalten)
Vortragende: Klaus Aschenbrenner, Christian Nagel

Es ist ein eigenes Notebook mitzubringen!

Tagesablauf:

09:00 - 13:00 Workshops
13:00 - 14.00 Mittagessen
14:00 - 17:00 Workshops
Abends (optional): gemeinsames Abendessen zum Ausklang des Tages

Das ergibt 5 x 8 Stunden gepowertes .NET Framework 3.5 Know-How zum Preis von nur € 1.300,00. Dieser Preis beinhaltet:

Konferenztasche
Begrüßungskaffee/Tee
Vormittagsjause
3-gängiges Mittagessen
Nachmittagsjause
Konferenzgetränke

Erfahren Sie alles was Sie .NET Framework 3.5 Entwicklung in der tagtäglichen Arbeit benötigen: angefangen bei LINQ, dem ADO.NET Entity Framework, den WCF & WF Erweiterungen, der Managed AddIn Programmierung, ASP.NET AJAX und der Managed SQL Server 2008 Programmierung.
Warum teilnehmen?
Mit dem .NET Framework 3.5 stellt Microsoft die 4. Generation der .NET Laufzeitumgebung zur Verfügung. Das .NET Framework 3.5 Developers Summit ist daher eine ausgezeichnete Möglichkeit, sich schnell und praxisgerecht auf das .NET Framework 3.5 ausbilden zu lassen. Bei den Speakern handelt es sich um international anerkannte Experten, die sich bereits seit über 2 Jahren mit dem .NET Framework 3.5 und dem SQL Server 2008 beschäftigen, und daher bereits umfangreiches Know-How aufbauen konnten und dieses in ausgezeichneter Qualität an die Teilnehmer weitergeben.
Top-Themen:

LINQ

LINQ to SQL
LINQ to XML

ADO.NET Entity Framework
Cryptography Next Generation
WCF & WF Erweiterungen
Managed AddIn Programmierung
ASP.NET AJAX
Managed SQL Server 2008 Programmierung

Weitere Informationen können Sie der Konferenz-Homepage unter http://developers-summit.csharp.at entnehmen.
Anmeldungen werden unter Klaus.Aschenbrenner@csharp.at entgegengenommen.

-Klaus

Upcoming Conferences

Klaus Aschenbrenner — Fri, 22 Feb 2008 20:26:22 GMT

Within the next months I'm doing some sessions at several conferences around the world:

DevWeek Conference (March 11 - March 14, London/UK)

Building asynchronous, distributed Service Broker applications

Scaleout scenarios with Service Broker

Team System Conference (April 22 - April 24, Munich/Germany)

Using Team Build 2008

European SQLPASS Conference (April 14 - April 16, Neuss/Germany)

ADO.NET Enhancements for SQL Server 2005

SQL Server 2008 and the GEOGRAPHY data type

DevTeach Conference (May 12 - May 16, Toronto/Canada)

Building asynchronous, distributed Service Broker applications

Service Broker enhancements in SQL Server 2008

Workflow driven Service Broker solutions

I'm looking forward to meet you at one of these events :-)

-Klaus

SQL Server 2008 February CTP released

Klaus Aschenbrenner — Wed, 20 Feb 2008 21:06:31 GMT

Today Microsoft has released the February CTP of SQL Server 2008. You can download the new CTP version from here.

-Klaus

HTTP Endpoints are deprecated in SQL Server 2008

Klaus Aschenbrenner — Mon, 18 Feb 2008 19:45:54 GMT

According to Bob, HTTP endpoints are deprecated in SQL Server 2008. With HTTP endpoints you had the possibility to expose stored procedures and stored functions as SOAP web services to the outside world of SQL Server 2005. But with SQL Server 2008 you have to choose some other ways, if you want to accomplish the same thing.

You could use ADO.NET Data Services - formerly known as Project Astoria. With ADO.NET Data Services you can implement RESTful services over any type of data. You can also supply directly a stored procedure as input. So it seems that ADO.NET Data Services is the replacement for HTTP endpoints in SQL Server 2008 and any further version. This means also that the .NET runtime is getting more and more power on the Microsoft platform, because everything has to be done with .NET - also exposing stored procedures from SQL Server...

-Klaus

Restpätze SQL Server 2008 Developer Day

Klaus Aschenbrenner — Tue, 05 Feb 2008 19:23:07 GMT

Für den SQL Server 2008 Developer Day sind noch ein paar Restplätze frei - wer also daran Interesse hat, kann mir eine Email schreiben.

-Klaus

Visual Studio 2008 Database Edition & SQL Server 2008 November CTP

Klaus Aschenbrenner — Tue, 11 Dec 2007 21:32:24 GMT

Die kürzeste Antwort zum Betreff meines Blog-Postings ist NEIN, Visual Studio 2008 und SQL Server 2008 November CTP können NICHT miteinander zusammenarbeiten. Sobald ich den Connection-String für die Design-Time Validation Database auf die November CTP Instanz verweisen lasse, und ein Datenbankprojekt anlegen möchte (ebenfalls auf der November CTP Instanz), bekomme ich eine Fehlermeldung, dass sich das Visual Studio 2008 nicht zur November CTP Instanz verbinden kann.

Wenn ich aber den Connection-String der Design-Time Validation Database auf eine SQL Server 2005 (SP2) Instanz verweise, funktionierts interessanterweise, obwohl das eigentliche Datenbankprojekt auf der November CTP Instanz ohne Probleme angelegt wird...

-Klaus

SP1 Download für MOSS, WSS 3.0 und Office 2007

Klaus Aschenbrenner — Tue, 11 Dec 2007 21:19:15 GMT

Office 2007 SP1
http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997-25DA01F388F5&displaylang=en

SharePoint Designer SP1
http://www.microsoft.com/downloads/details.aspx?FamilyId=B57C805D-2821-4625-A6F1-80725267F887&displaylang=en

WSS 3.0 SP1
http://www.microsoft.com/downloads/details.aspx?FamilyId=4191A531-A2E9-45E4-B71E-5B0B17108BD2&displaylang=en

MOSS 2007 SP1
http://www.microsoft.com/downloads/details.aspx?FamilyId=AD59175C-AD6A-4027-8C2F-DB25322F791B&displaylang=en

-Klaus

Pro SQL Server 2008 Service Broker - PreOrder

Klaus Aschenbrenner — Mon, 10 Dec 2007 17:12:44 GMT

Seit heute gibt es auf Amazon.com mein neues Buch Pro SQL Server 2008 Service Broker zum Vorbestellen - wär doch ein schönes Weihnachtsgeschenk, oder? ;-) Wenn alles beim SQL Server Team glatt läuft, und der SQL Server 2008 im Q1/Q2 2008 erscheint, wird mein neues Buch ca. im Juni 2008 erscheinen.

Die Ausage "Mein Buch" stimmt ja nicht mehr ganz, da ich Remus Rusanu als Gast-Autor in dieser Ausgabe begrüßen darf. Remus war in den letzten Jahren bei Microsoft (in Redmond) als Software Engineer angestellt und hat hier direkt beim SQL Server Team am Service Broker mitentwickelt. Remus schreibt das komplette 12. Kapitel, dass sich der Administration und dem Troubleshooting von Service Broker Anwendungen widmet. Remus wird hier auch auf das Tool ssbdiagnose.exe eingehen, mit dem Sie in verteilten Service Broker Anwendungen Konfigurationsprobleme leichter finden können. Macht auch Sinn, dass Remus dieses Kapitel schreibt, da er derjenige ist, der ssbdiagnose.exe implementiert hat ;-)

-Klaus

Warum SQL Server 2008 nicht den IIS verwendet

Klaus Aschenbrenner — Tue, 04 Dec 2007 21:54:44 GMT

Brian hat einen sehr guten Blogeintrag verfasst, der beschreibt warum der SQL Server 2008 nicht mehr den IIS für die Reporting Services (Report Manager, Report Web Services) verwendet. Im SQL Server 2008 werden die Reporting Services direkt durch die Verwendung von HTTP.SYS gehostet.

-Klaus

SQL Server 2008 Developer Day

Klaus Aschenbrenner — Sun, 02 Dec 2007 12:46:06 GMT

Am 28.02.2008 veranstalte ich den SQL Server 2008 Developer Day in Wien.

Der SQL Server 2008 Developer Day ist eine ausgezeichnete Möglichkeit, sich schnell und praxisgerecht auf den SQL Server 2008 ausbilden zu lassen. Bei dem Speaker handelt es sich um einen international anerkannten Experten, der sich bereits 1 - 2 Jahre professionell mit dem SQL Server 2008 beschäftigt, und daher bereits umfangreiches Know-How in diesem Bereich aufbauen konnte, und dieses in ausgezeichneter Qualität an die Teilnehmer weitergibt.

Themen

Die neuen SQL Server 2008 Datentypen

DATE, TIME

FILESTREAM

Spatial

HierarchyID

Table Valued Constructors

Table Valued Parameters

Data Compression

MERGE T-SQL Statement

Grouping Sets

SQL Server Extended Events

Change Data Capture

Declarative Management Framework

Die Anmeldung zum SQL Server 2008 Developer Day erfolgt hier.

-Klaus

Deprecated Features im SQL Server 2008

Klaus Aschenbrenner — Tue, 20 Nov 2007 22:04:07 GMT

Mit dem SQL Server 2008 entfernt Microsoft einige Features, die teilweise mit dem SQL Server 2005 Einzug gehalten haben:

SQL Server Notification Services
Die grundlegenden Szenarien werden durch die Reporting Services unterstützt, für alle weiterführenden Szenarien verweist Microsoft auf die nächste SQL Server Version nach 2008... :-(

SQL-DMO
Gibts in der Express Edition nicht mehr, wird durch die SQL Server Management Objects (SMO) abgelöst. Muss also unbedingt bei Neuentwicklungen berücksichtigt werden!

Surface Area Configuration Tool
Tja, das nette Helferlein, dass mit dem SQL Server 2005 das Licht der Welt erblickt hat, wird mit dem SQL Server 2008 nun ein paar Jahre später wieder zu Grabe getragen. Hier eine Aufstellung, wie die Funktionalitäten trotzdem angesprochen werden können:

Protokolle, Verbindungen und Start-Optionen:
Können über den SQL Configuration Manager eingestellt werden

Datenbank-Engine Features
Hier verweist Microsoft auf das Declarative Management Framework vom SQL Server 2008

SSAS Features
Können über die Eigenschaftenseiten im SQL Server Management Studio bearbeitet werden

SSRS Feature
Können direkt über die RSReportServer.config geändert werden - ist ja der echte Hammer, oder? ;-)

-Klaus

SQL Server 2008 November CTP verfügbar

Klaus Aschenbrenner — Mon, 19 Nov 2007 18:19:23 GMT

Seit einigen Stunden ist der November CTP vom SQL Server 2008 zum Download verfügbar.

-Klaus

Meine Vorträge bei den SQLdays 2007 in München

Klaus Aschenbrenner — Mon, 05 Nov 2007 15:22:31 GMT

Heute und morgen halte ich 2 Vorträge zum SQL Service Broker bei den SQLdays in München:

Verteilte, zuverlässige Anwendungs-Architekturen mit dem SQL Service Broker

Zuverlässige Smart Clients mit dem SQL Service Broker

Unter http://www.csharp.at/downloads/sqldays2007.zip finden Sie meine Präsentationen und Beispiele zum Download. Ich würde mich über Ihr Feedback zu den Vorträgen sehr freuen.

-Klaus

SQL Server 2008 Juli CTP

Klaus Aschenbrenner — Wed, 01 Aug 2007 06:34:42 GMT

Heute hat Microsoft die Juli CTP Version vom SQL Server 2008 released. Der Download ist hier zu finden, und weitere Informationen (inkl. Books Online) gibt's hier.

-klaus

Einladung zum .NET 3.0 Developers Summit

Klaus Aschenbrenner — Thu, 26 Jul 2007 06:46:38 GMT

Ende 2006 hat Microsoft das .NET Framework 3.0 auf den Markt gebracht. Grund genug um an dem .NET 3.0 Developers Summit teilzunehmen um sich für die .NET Framework 3.0 Programmierung fitt zu machen!
Mit dem .NET Framework 3.0 stellt Microsoft die 3. Generation der .NET Laufzeitumgebung zur Verfügung. Das .NET Framework 3.0 Developers Summit ist daher eine ausgezeichnete Möglichkeit, sich schnell und praxisgerecht auf das .NET Framework 3.0 ausbilden zu lassen. Bei den Speakern handelt es sich um international anerkannte Experten, die sich bereits seit mehreren Jahren mit dem .NET Framework 3.0 beschäftigen, und daher bereits umfangreiches Know-How aufbauen konnten und dieses in ausgezeichneter Qualität an die Teilnehmer weitergeben.

Zielpublikum: .NET 2.0 Programmierer/innen
Termine: Montag 24.09.2007 bis Freitag 28.09.2007 im Palais Strudlhof, Pasteurgasse 1, A-1090 Wien
Kosten: € 1.300,00 (im Betrag ist keine Umsatzsteuer enthalten)

Tagesablauf:
09:00 - 13:00 Workshops
13:00 - 14.00 Mittagessen
14:00 - 17:00 Workshops

Abends: Abendprogramm zum Ausklang des Tages
Das ergibt 5 x 8 Stunden gepowertes .NET Framework 3.0 Know-How zum Preis von nur € 1.300,00 inkl. Mittagessen und Pausenverpflegungen

Anmeldungen werden unter Angabe der Adress- und Rechnungsinformationen unter Klaus.Aschenbrenner@csharp.at entgegengenommen und gelten als verbindlich.
Weitere Informationen zum genauen Programmablauf und zu den verschiedenen Workshops können der Veranstaltungshomepage unter http://developers-summit.csharp.at entnommen werden.
Ich freue mich auf deine Teilnahme und auf eine interessante Woche mit dem .NET Framework 3.0.

-Klaus

Day	Time	Speaker	Room1	Speaker	Room2
23.06.2009	09:00-10:15	Itzik Ben-Gan	Minimally Logged Inserts and other Data Modification Enhancements in SQL Server 2008	Dejan Sarka	Advances in Relational Databases
10:30-11:45	Aaron Johal	Help! My operational system is getting slower and slower!	Helmut Knappe	Implementing Many-to-many Relationships in SQL Server Analysis Services to Model Complex Business Solutions
13:30-14:45	Itzik Ben-Gan	Islands and Gaps Problems	Michael Sass	Meta Data Locks - Analysis and Performance Problems
15:00-16:15	Aaron Johal	Using Visual Studio Team System 2008 Database Edition to resolve the data perception	Helmut Knappe	Charts in Reporting Services 2008
16:30-17:45	Itzik Ben-Gan	Advanced T-SQL Tips & Tricks	Andreas Schindler	Best Practice ETL

24.06.2009	09:00-10:15	Klaus Aschenbrenner	Scaleout scenarios with SQL Server & Service Broker	Dejan Sarka	Understanding XQuery,
10:30-11:45	Itzik Ben-Gan	Using the HIERARCHYID Datatype in SQL Server 2008	Davide Mauri	Set Based solution: an approach for developers
13:30-14:45	Klaus Aschenbrenner	T-SQL Erweiterungen im SQL Server 2008	Gianluca Hotz & Herbert Albert	Implementing SQL Server 2008 Policy Based Management
15:00-16:15	Itzik Ben-Gan	Aggregating Data in SQL Server 2008 Using Grouping Sets	Gianluca Hotz & Herbert Albert	SQL Server 2008 Policy Based Management and multi-server administration
16:30-17:45	Klaus Aschenbrenner	ADO.NET Enhancements für SQL Server 2005/2008	Davide Mauri	Instrumenting, Monitoring and Auditing of SSIS ETL Solutions

25.06.2009	09:00-10:15	Dejan Sarka	Design Myths	Andreas Schindler	Solving Business Problems with MDX
10:30-11:45	Helmut Knappe	Business Intelligence Myths	Aaron Johal	Why must I shoehorn my data into tables? I need total flexibility to develop in an agile way!
13:30-14:45	Dejan Sarka	Temporal Data in SQL Server 2008	Andreas Schindler	Change Management für Analysis Services Cubes
15:00-16:15	Gianluca Hotz	Oracle to SQL Server migration for Developers I	Helmut Knappe	Using Windows PowerShell in BI
16:30-17:45	Gianluca Hotz	Oracle to SQL Server migration for Developers II	Klaus Aschenbrenner	SQL Server 2008 und der GEOGRAPHY Datentyp