Blog ala Vidar

SQL, AppFrame and other cool technologies

Tag Archives: Tips

COLUMNS_UPDATED

From SQL Server 2000, there’s been support for a function called COLUMNS_UPDATED. I haven’t used it much, but I’ve written triggers using it a couple of times, and every time I do that, I’ve forgot how it works. Now, I’m going to put it here on my blog 🙂

So, basically, this function returns which fields are actually updated. This can be useful if for instance you want users to be able to update one column, but not the others on a table. Instead of doing:

IF UPDATE(AllowedField) 
	AND NOT UPDATE(NotAllowedField1) 
	AND NOT UPDATE(NotAllowedField2) 
	AND NOT UPDATE(NotAllowedField3)
BEGIN
	RAISERROR('You are not allowed to change fields other than: AllowedField', 18, 1)
	ROLLBACK TRANSACTION
	RETURN
END

The problem with this solution is of course if you add new fields, you’re more or less proper-fucked.

CREATE TABLE test (
	NotAllowedField1 NVARCHAR(128),
	NotAllowedField2 NVARCHAR(128),
	NotAllowedField3 NVARCHAR(128),
	AllowedField NVARCHAR(128)
)
GO

INSERT INTO test
  (NotAllowedField1, NotAllowedField2, NotAllowedField3, AllowedField) 
VALUES
  ('aaaa', 'aaaa', 'aaaa', 'aaaa')
GO 5

CREATE TRIGGER test_ITrig ON test 
AFTER UPDATE 
AS
BEGIN
	DECLARE @AllowedFieldName AS NVARCHAR(128)
	DECLARE @ColumnNumber AS INT

	SET @AllowedFieldName = 'AllowedField'
	SET @ColumnNumber = (SELECT colid 
						FROM sys.syscolumns 
						WHERE id = OBJECT_ID('test') 
						  AND name = @AllowedFieldName)

	IF (COLUMNS_UPDATED() ^ POWER(2, ((@ColumnNumber) - 1))) > 0
	BEGIN
		RAISERROR('You are not allowed to change fields other than: %s', 
				18, 1, @AllowedFieldName)
		ROLLBACK TRANSACTION
		RETURN
	END
END
GO


-- Not allowed single
UPDATE test
	SET NotAllowedField1 = 'bbbb'

-- Not allowed multiple
UPDATE test
	SET
	  AllowedField = 'bbbb',
	  NotAllowedField2 = 'bbbb',
	  NotAllowedField3 = 'bbbb'

-- Allowed single
UPDATE test
	SET AllowedField = 'bbbb'


-- Clean-up
DROP TRIGGER test_ITrig
GO
DROP TABLE test
GO
Advertisements

Dynamic SQL

Brrrrrrhhhh, that sounds nasty. It is, but sometimes there are no other ways of solving a problem. Here’s a couple of tricks that might help you on your way, and some tricks on how to use static SQL where you might have thought you needed dynamic SQL.

SET NOCOUNT ON

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @TableName AS NVARCHAR(128)

SELECT name AS TableName, CAST(NULL AS INT) AS RowsCount
	INTO ##tables
	FROM sys.sysobjects
	WHERE xtype = 'U'

WHILE EXISTS (SELECT * FROM ##tables WHERE RowsCount IS NULL)
BEGIN
	SELECT TOP 1
	  @TableName = TableName
		FROM ##tables
		WHERE RowsCount IS NULL

	BEGIN TRY
		SET @SQL = 'UPDATE ##tables '
		SET @SQL = @SQL + '	SET RowsCount = (SELECT COUNT(*) FROM  [' + @TableName + '])'
		SET @SQL = @SQL + ' WHERE TableName = ''' + @TableName + ''''
		EXEC (@SQL)
	END TRY
	BEGIN CATCH
		UPDATE ##tables
			SET RowsCount = 0
			WHERE TableName = @TableName
	END CATCH
END

SELECT *
	FROM ##tables
	ORDER BY RowsCount DESC

DROP TABLE ##tables

This is one way of looping through tables, doing something with each. In this case doing a COUNT(*), but you can also use it to update statistics or similar.

There’s another way of doing it. NB! This is using sp_MSForEachTable which is an UNDOCUMENTED stored procedure, so use it on your own risk (it might get depricated with no notice!).

DECLARE @SQL AS NVARCHAR(MAX)

CREATE TABLE ##tables2 (
	TableName NVARCHAR(128),
	RowsCount INT
)

SET @SQL = 'INSERT INTO ##tables2 (TableName, RowsCount) '
SET @SQL = @SQL + 'SELECT ''?'', COUNT(*) FROM ?'
EXEC sp_MSForEachTable @SQL

SELECT *
	FROM ##tables2
	ORDER BY RowsCount DESC

DROP TABLE ##tables2

As you see, this is much less code. You can also do the same for databases with sp_MSForEachDb.

Another cool thing you can do with dynamic SQL is to get values back from it. In other words, we can rewrite our little script which counts records in each table to execute the COUNT in dynamic, but get the results from this and updating our table in static SQL. This way, we don’t have to use global (##tables) tables.

SET NOCOUNT ON

DECLARE @RowsCount AS INT
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @TableName AS NVARCHAR(128)

SELECT name AS TableName, CAST(NULL AS INT) AS RowsCount
	INTO #tables
	FROM sys.sysobjects
	WHERE xtype = 'U'

WHILE EXISTS (SELECT * FROM #tables WHERE RowsCount IS NULL)
BEGIN
	SELECT TOP 1
	  @TableName = TableName
		FROM #tables
		WHERE RowsCount IS NULL

	BEGIN TRY
		SET @SQL = 'SET @RowsCount = (SELECT COUNT(*) FROM [' + @TableName + '])'
		EXEC sp_executesql 
			@SQL, 
			N'@RowsCount INT OUTPUT', 
			@RowsCount OUTPUT

		UPDATE #tables
			SET RowsCount = @RowsCount
			WHERE TableName = @TableName
	END TRY
	BEGIN CATCH
		UPDATE #tables
			SET RowsCount = 0
			WHERE TableName = @TableName
	END CATCH
END

SELECT *
	FROM #tables
	ORDER BY RowsCount DESC

DROP TABLE #tables

Outlook tips and tricks

Now that it’s official that Omega’s running Exchange I can share a couple of tips and tricks I’ve found useful. First, let’s start with how to integrate with TeamDocuments. There’s a TeamDoc called “Outlook-plug-in”. If you don’t got access to it, just let me know and I’ll add you. Under Build, you’ll find a 2007 version, which also works on 2010. Install it and then open Outlook. You’ll notice you’ve got a couple of new icons in the “Add-Ins” tab. Press Config, and choose what settings you want. Here you can select calendars to sync, if to enable TeamDoc inside Outlook and to sync contacts. After clicking “OK”, just click on the connect button and it will start to synchronize!

Twitter
There’s a plug-in called TwInbox that you can use to get tweets inside Outlook. It’s pretty easy to set up, but be aware; if you’re following many people (like I do), it can get really naggy, so I’ve unchecked “Home” in preferences, so I only get mentions, directs etc. If I got spare time I only just go to twitter.com 🙂

LinkedIn
There’s also a plug-in for LinkedIn. This is not like TwInbox where you get the messages as mails or similar, but it integrates nicely in contacts and in for example mails showing the profile-pictures of those you’re connected to etc.

RSS Feeds
At the moment I’m subscribing to 218 RSS feeds. I thought it would be a great idea to get them inside Outlook, but found it to be not as good as Google Reader. The main problem was that although I marked a feed as read, it came with the same items over and over again. Pretty annoying. So, therefore I removed all subscriptions (don’t worry, I exported an OPML file from Google Reader) and right clicked the “RSS Feeds” folder, Properties, Home Page and there I put http://www.google.com/reader/ as the address. Now I can at least get Google Reader inside Outlook 🙂

Windows 7 Keyboard Shortcuts

Yes, I know. There are thousand posts out there with the same name, but for some reason many people haven’t seen them. Here’s a list of my favorite keyboard shortcuts in Windows 7:

Win + D – Show Desktop (minimize all windows)
Win + E – Open Computer
Win + L – Lock your computer
Win + P – Choose presentation display mode (computer only, duplicate etc)
Win + R – Open the Run dialog
Win + Space – “Aero Peak”
Win + Up – Maximize Window
Win + Left/Right – Dock the window to the left or right side of the screen
Win + Down – “Unmaximize” or minimize the window
Win + +/- – Zoom in/out
Ctrl + Shift + N – New folder

And then you’ve got a couple of shortcuts that are nice to know:
Ctrl + Shift + Esc – Open Task Manager
Win + Pause – System Properties (equal to right click computer, properties)

And then some which we’ve used for ages:
Ctrl + Esc – Equal to windows key (or clicking the start menu)
Alt + Tab – Cycle Through Windows
Alt + Shift + Tab – Cycle Through Windows, counter clock
Ctrl + Tab – Cycle through tabs in application.
Ctrl + Shift + Tab – Cycle through tabs in application, counter clock
Ctrl + C – Copy
Ctrl + X – Cut
Ctrl + V – Paste

For the complete list, visit this link: http://windows.microsoft.com/en-US/Windows7/Keyboard-shortcuts

Keyboard shortcuts in SSMS

You can save a lot of time using simple keyboard shortcuts. Here’s a list of my favorite shortcuts in SQL Server Management Studio. NB!!! You might have to go in to Tools –> Options –> Environment –> Keyboard and press “Reset to default”.

Action Shortcut
New Query Window CTRL + N
Close window CTRL + F4
Delete all text in current tab CTRL + SHIFT + DEL
Selected text to UPPER case CTRL + SHIFT + U
Selected text to lower case CTRL + SHIFT + L
Selected text to comment CTRL + K, CTRL + C
Uncomment selected text CTRL + K, CTRL + U
Info of selected object (text) ALT + F1
Include actual execution plan CTRL + M
Switch between tabs CTRL + TAB
Object Explorer Details F7
Object Explorer F8