Putting Books Online Offline

One of the early announces of MS SQL Server 2012 I remember is that Books Online (BOL) would only be available online. And of course it makes sense. If the SQL Server team wants to keep the library up to date it makes no sense shipping it with the install media. But as a consultant I need BOL to be installed on my personal laptop because it often happens that I’m not allowed on the customers network with an external machine. So no network, means no internet and means no Books Online. But lucky me it’s easy to have BOL offline available.
This is how it works.
Open Management Studio and click Help > Manage Help Settings

In the Help Library Manager click Install content from online

Scroll down in the Install Content from Online screen to the SQL Server 2012 section and click Add in the Actions column. Add additional content if desired and click update.

After a couple of minutes Books Online is offline available.

Advertisements

Interupted Identity Insert behaviour

While teaching the Implementing & maintaining a Microsoft SQL Server 2008 Fasttrack I got a very simple question. But it’s often these simple questions that make you doubt. The question was: “What if you manually insert some values in an Identity column? Will SQL Server autonumber from the new values or…”. I never had to worry on production servers about this and I was pretty sure SQL Server would be able to cope with manual Identity Inserts but I don’t want to give answers to my students that I’m not sure off. So it was demo time:

First of all a table with an Identity column was created:

CREATE TABLEIdentityTest (
Autoseed int IDENTITY (1,1),
Comment varchar (20)
)
GO

Next I insert some rows that automatically get a value in the Autoseed column:

INSERT INTO IdentityTest
VALUES (‘autoinsert’)
GO 75

If I try to insert some values in the autoinsert column I get an error message:

INSERT INTO IdentityTest(Autoseed,Comment)
VALUES (76,’manual’),(77,’manual’)
GO

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘IdentityTest’ when IDENTITY_INSERT is set to OFF.

 To insert some values manually I need to enable Identity inserts on the table with the following statement:

SET IDENTITY_INSERT IdentityTest ON
GO

Now I can do the insert. Note that when doing an Identity insert I need to specify the columnlist so this will work:

INSERT INTO IdentityTest(Autoseed,Comment)
VALUES (76,’manual’),(77,’manual’)
GO

But this will not work:

INSERT INTO IdentityTest
VALUES (76,’manual’),(77,’manual’)
GO

EXTRA TIP:
Dragging the column folder from object explorer to the Query window will automatically give the columnlist of the table.

Now I have two manual lines in my table. To continue my demo I need to put the auto seed back on:

SET IDENTITY_INSERT IdentityTest OFF
GO

To make it even more complicated I also delete some rows in the table to create some gaps:

DELETE FROM IdentityTest
WHERE Autoseed IN (69,77)

Now it’s time to add some rows. Note that 77 was the highest inserted number but was deleted in the previous script.:

INSERT INTO IdentityTest  VALUES (‘autoinsert’)
GO 10

Now querying the table will give the following results:

SELECT MAX(autoseed)
FROM IdentityTest

Results in 87 so the auto increment started at 78 meaning tot 77 was not reused although it was deleted

SELECT * from IdentityTest
WHERE Autoseed IN (69,77)

Will give no results so the empty gaps are not filled up again.

SELECT * FROM IdentityTest

Shows what happens on heaps (tables with no clustered index). Value 78 will be on position 69 because it will fill the physical gap on the datapage.

%d bloggers like this: