36 Chambers – The Legendary Journeys: Execution to the max!

May 30, 2014

Catching Me In June

Filed under: Database Administration, Where's Poochy? — Kevin Feasel @ 6:00 pm

This June won’t be too busy for me conference-wise, but I do have a couple talks.

First of all, I’m going to be at SQL Server #299 in Columbus, Ohio on Saturday, June 14th.  I will have the chance to give two talks there, one on tally tables and the other on using the APPLY operator.  I’ve given both of those talks before, so this should go pretty well.

After that, I’m giving my In-Memory OLTP talk to the Triangle SQL Server Users Group on Tuesday, June 18th.

That should just about cover it event-wise for next month.

May 27, 2014

Identity Integers In SQL Server In-Memory OLTP

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

There’s a piece of no-longer-correct information floating around on the Internet, and I wanted to put up a blog post to point out the correct answer.  A lot of people say that identity integers are not allowed in SQL Server 2014’s In-Memory OLTP (i.e., Hekaton).  In fact, identity integers are allowed and are fully supported in the full release of SQL Server 2014.

Let’s create a new In-Memory OLTP table using an identity integer as the primary key.

CREATE TABLE dbo.IdentityTest
(
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 500000),
	IncidentNumber VARCHAR(19)
)
WITH
(
	MEMORY_OPTIMIZED = ON,
	DURABILITY = SCHEMA_ONLY
);

From there, I want to show you that you can certainly insert into a table with an identity column:

INSERT INTO dbo.IdentityTest(IncidentNumber) VALUES ('This can''t work!');
SELECT * FROM dbo.IdentityTest;

If you run it locally, you’ll get back the row you expect, proving that it absolutely can work.

“But wait,” you might say, “what about natively compiled stored procedures?” We know that natively compiled stored procedures are neutered in V1, so that’s a good question. Let’s gin up another example:

CREATE PROCEDURE dbo.dbo.IdentityTest_NativeProcedure
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english')

	INSERT INTO dbo.IdentityTest(IncidentNumber) VALUES ('This can''t work!');

END
GO

EXEC dbo.dbo.IdentityTest_NativeProcedure;
SELECT * FROM dbo.IdentityTest;

If you run this, you’ll see that even a natively compiled stored procedure successfully uses an identity column. Given that you can’t get FOR NEXT VALUE on a sequence in a natively compiled stored procedure, this is an important finding.

Now let’s clean up the junk:

DROP PROCEDURE dbo.IdentityTest_NativeProcedure;
DROP TABLE dbo.IdentityTest;

May 25, 2014

Conferences Over…For This Month

Filed under: Database Administration, Where's Poochy? — Kevin Feasel @ 6:04 pm

This past week has been a busy one for me.

First was Carolinacon, which was fantastic and whet my appetite for Derbycon.

After that, I had a chance to hear Grant Fritchey speak about continuous deployment on Tuesday.  This was an early version of the talk, but he did a good job.

On Wednesday, I gave my own talk on In-Memory OLTP in SQL Server 2014.  That went really well, even though it lasted over two hours.  I was exhausted by the end of it, but there were a number of great questions.  Most of the questions boiled down to, “Why would I actually use this in a production environment?”  And that’s a fair question…

Finally, I gave the same talk on Friday to my co-workers, but in a condensed, one-hour format.  I had to pack in a lot of detail, but I think they got the gist of it.

That’s all for this month.

April 27, 2014

Upcoming Events

Filed under: Database Administration, Programming & Work, Wacky Theories — Kevin Feasel @ 6:00 pm

First, a plug.  The folks at Red Gate have re-published my Tribal SQL chapter as a standalone article.  If you like it, go out and get a copy of the book, as there are a number of excellent authors there.

  • On May 16-18, I will be attending CarolinaCon.  It’s hard to beat $20 for a weekend of security talks.
  • On May 21st, I am going to present to the TriNUG Data SIG on In-Memory OLTP in SQL Server 2014 (AKA, Hekaton).
  • On June 14th, I am going to attend SQL Saturday #299 in Columbus.  I’m not sure yet if I’ll have the opportunity to present there, however.

Over the next few weeks, I plan to start putting up some more technical posts as I look into Hekaton and a couple of other topics.  The big one that I have is an analogy:  F# is to development DBAs what Powershell is to production DBAs.  I want to introduce F# as a set-based functional language which plays very nicely with a SQL Server infrastructure and provides a shallower learning curve than C#.

Also, now that the weather is warming up, it’s time to start hitting the trails and visiting parks.  This means I should be able to start taking more photographs again.

April 22, 2014

Accessing The INSERTED And DELETED Pseudo-Tables In A Trigger’s CATCH Block

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

At work recently, we had a scenario come up in which we wanted to figure out which specific values are causing a trigger to roll back execution of a stored procedure.  I decided to put together a test case to see if I could still access the DELETED pseudo-table from within a trigger’s CATCH block.  It turns out that you can.

CREATE TABLE [Test]
(
	Id INT,
	Val CHAR(5)
);

INSERT into [dbo].[Test]
(
	Id,
	Val
)
VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc'), (4, 'ddddd');

CREATE TRIGGER tr_test on [Test]
after update
AS
BEGIN
	BEGIN TRY
		IF EXISTS(SELECT * FROM DELETED WHERE Id = 5)
		BEGIN
			THROW 50000, 'Ayup', 1;
		END
	END try
	BEGIN catch
		SELECT
			*
		FROM DELETED;
	END catch
END
GO

--This first run should NOT result in any errors.
UPDATE dbo.Test
SET
	Id = Id + 4;

--This second run SHOULD result in an error, showing us 4 records.
UPDATE dbo.Test
SET
	Id = Id + 4;

TRUNCATE TABLE Test;
DROP TABLE Test;

The trick here is that if you manually issue a ROLLBACK statement inside your CATCH block, you need to do it after collecting the information you need from the DELETED and INSERTED psuedo-tables, as those get cleared out upon rollback. If you insert those records into a variable (either a table variable or a regular variable using something like FOR XML PATH to concatenate the results), you can continue to use them in the CATCH block after you roll back your statement. That way, you can log the error without having that log roll back.

April 15, 2014

Gaps And Islands

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

Not too long ago, I helped a co-worker solve a problem.  The quick version of the problem is, we want to collate together common occurrences of an event.  In this case, it’s log records by IP address.  If the same IP address shows up 3 times in a row, we want to know that there were 3 events, followed by the next IP, and so on.  If that first IP shows up later on, it’s a new chain.

This is another way of formulating the island problem.  Itzik Ben-Gan covers it wonderfully in his chapter on gaps and islands in SQL Server MVP Deep Dives, which chapter Manning has made available for free.

In my case, here’s a sample and the solution:

CREATE TABLE #Temp
(
	LogID INT,
	ErrorDate DATETIME,
	IPAddress VARCHAR(50)
);

INSERT into dbo.#Temp
(
	LogID,
	ErrorDate,
	IPAddress
)
VALUES
	(173371, '2014-04-07 09:47:35', 'IP 1'),
	(173372, '2014-04-07 09:47:36', 'IP 1'),
	(173373, '2014-04-07 09:47:37', 'IP 1'),
	(173374, '2014-04-07 10:01:35', 'IP 2'),
	(173375, '2014-04-07 10:02:35', 'IP 3'),
	(173376, '2014-04-07 10:03:04', 'IP 3'),
	(173377, '2014-04-07 10:03:05', 'IP 3'),
	(173378, '2014-04-07 10:03:18', 'IP 3'),
	(173379, '2014-04-07 10:18:26', 'IP 1'),
	(173380, '2014-04-07 10:18:26', 'IP 1'),
	(173381, '2014-04-07 10:18:26', 'IP 1'),
	(173382, '2014-04-07 10:18:27', 'IP 3');

WITH records AS
(
	SELECT
		LogID,
		ErrorDate,
		IPAddress,
		ROW_NUMBER() OVER (ORDER BY LogID) - ROW_NUMBER() OVER (ORDER BY IPAddress, LogID) AS grp
	FROM
		#Temp
)
SELECT
	MIN(LogID) AS MinimumID,
	MAX(LogID) AS MaximumID,
	COUNT(*) AS NumberOfRows,
	IPAddress
FROM
	records
GROUP BY
	IPAddress,
	grp
ORDER BY
	MinimumID;

The trick here is that we use two separate ROW_NUMBER calls to generate a difference in values.  The first part of the line (ROW_NUMBER() OVER (ORDER BY LogID)) generates a unique row number for each record, ordered by LogID.  The second ROW_NUMBER() operation generates a unique row number for each line, but it’s ordered by IP Address first and LogID second.  This means that the six records with an IP address of 1 will be clustered together and numbered 1-6.  We can take the difference between these two numbers to get a unique cluster, and that (which we call grp) lets us separate each IP address cluster out, solving the island problem with a single read of the data, much more efficiently than any row-by-row or self-joining solution could ever come up with.

April 13, 2014

New Presentation: SQL Injection

Filed under: (In)Security, Database Administration — Kevin Feasel @ 6:00 pm

I will be presenting for the PASS Security virtual chapter on Thursday, April 17, 2014 at 1 PM Eastern.  Attendees can go to the link to register for the webinar.  The talk is entitled “Understanding and Eliminating SQL Injection” and here is the abstract:

Over the past several years, hacktivists, criminals, and people just “out for lulz” have managed to find sensitive data owned by organizations like Sony, Yahoo, NASA, and the U.S. army, among many others. In all of these cases, the attackers exploited websites using SQL injection attacks.

SQL injection is at the top of the Open Web Application Security Project (OWASP) top 10 list and is an important part of one of the SANS 20 critical security controls. This talk will go into what SQL injection is, how attackers can use it, and how to secure your sites so that your CIO and CISO never show up on the evening news.

Although the talk will focus on using the Microsoft stack (IIS, ASP.Net, and SQL Server), the lessons will apply to all web systems everywhere.

April 10, 2014

SQL Saturday Update

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

SQL Saturday #320 will officially be at Wake Tech North.  We’ve signed the paperwork and will have room for 10 simultaneous sessions.

I’m quite excited about the event and will probably have a couple more updates as we get closer to the go-live date.

April 1, 2014

Happy Release Day

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

SQL Server 2014 is out.  See what’s new and start preparing for upgrades.

March 23, 2014

SQL Saturday Richmond Roundup

Filed under: Database Administration, Where's Poochy? — Kevin Feasel @ 7:27 pm

SQL Saturday #277 in Richmond, Virginia was yesterday.  The RVA PASS chapter put on a great event and I can only hope we do as good a job in Raleigh come September.

My session seemed to go pretty well.  I had a (small) room filled with people and I definitely brought the frenetic energy that I wanted to.  The next presentation will be at the TriNUG Data SIG in April.

Older Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 99 other followers