Blog ala Vidar
SQL, AppFrame and other cool technologies
SQL Updates
Posted by on August 9, 2011
It’s been way too long since my last blog post, so I figured it was about time to give a little SQL update on things I’ve learned the last months.
After attending NDC, watching Peter Myers’ sessions about SQL Server Analysis Services and data mining, I’ve played A LOT with SSAS. It’s way different compared to T-SQL, but it’s a lot of fun and a nice challenge. Soon got a cube working as I want it, to show off to the department managers at the company I work at. Hopefully I’ve done a good enough job to encourage them to use it, and maybe even recommend to customers. We haven’t used SSAS too much yet, but that might be about to change.
I’ve also updated my scripts library to include “FIND – Space Usage By Table”. You can use this if you’ve done big clean-ups in your databases, and what to “shrink” some tables. “RECREATE – Foreign Keys” – I wrote this one to make automatic data-transfer easier. You run the first part to store the foreign keys in a temporary table, then remove them. Then you can do all your data-transfer-stuff without any issues. Then just run the rest of the script to create the foreign keys again.
NB!!! These are of course scripts without any warranties. I’m sharing them because you might need something similar, but always look over scripts you find on the internet. Not just mine, but everyone else’s too!
Last but not least, I’ve played some with SQL Server 2011 CTP3, codenamed Denali. The first thing you notice after installing it, if you haven’t already tested CTP1 (CTP2 was not public), is the new integration with Visual Studio 2010. Pretty! Not a big difference from Management Studio, but everything is now in VS; BI tools and Management Studio. I’ve already written about some new exciting features in Denali, but here’s some I didn’t mention, or didn’t know of at the time:
- A new edition/version called Express Local DB Runtime. Zero-configuration but full support for all programmability features.
- Books online has changed. It was awful in CTP1, but I think I like the version in CTP3.
- In-Memory Column-store indexes aka Apollo – Read more about it here.
- New restore options. Now you got a timeline to choose from!
- A couple of new functions, Analytic, Conversion, THROW. Full list of new functions.
- You’re now able to format the result set from EXECUTE. For example returning XML.
Googling “SQL Server Denali CTP 3 new features” will give you a lot more features.
NDC 2011
Posted by on June 17, 2011
Tuesday, June 7th I packed my bags, put my ass on a plane (not literary!) and went to Oslo to attend Norwegian Developers Conference; NDC 2011. I’ll skip the boring details about all the cool stuff I took pictures of (including the Opera house) and go straight to the conference. It was AWESOME! Good sessions, impressive list of speakers, many NNUG people, good food and very good coffee.
The conference was started by Scott “The Gu” Guthrie, talking about Azure. Although I knew more or less everything he was talking about, it was cool seeing him live. For me, seeing him live is like normal people seeing royal people. After his session I attended Maarten Balliauw‘s Azure sessions. A couple of interesting bullet points, but nothing big.
Then lunch came along, and I went to another room to watch another Belgian; Gill Cleeren. I’ve followed this guy for a while on Twitter, and I didn’t have any idea what LightSwitch was, so “Switch on the LightSwitch” sounded interesting. Mr Cleeren seemed much more relaxed and more knowledgable, so I enjoyed his talk, even though LightSwitch wasn’t that cool.
Next slot was full of things I’m not interested in, so I used this hour to go around the exhibition hall in the center of Oslo Spektrum. Talking to JetBrain, EDB ErgoGroup (where Rune Grothaug, the guy who was NNUG’s main contact in Microsoft Norge before Petri Wilhelmsen) and a couple of other booths. I also got to meet Petri for the first time, after annoying him with e-mails and stuff for several months.
NNUG had its own “booth” with some chairs and some sofa/puffy-chairs which I have no idea how to describe, but they were awesome for just laying there, relaxing my feet. Last but not least, they’d set up a computer with TrackMania. I of course had to help them get some gold medals!
Enough chit-chat, after my break I went to Peter Myers’ talks about SQL Server Analysis Services (SSAS) and Data Mining. MIND-BLOWING! Seriously, I was so excited by these sessions, my heart rate was at least 200 bpm. I couldn’t get to my hotel room fast enough to start playing with it, but as I discovered, it wouldn’t be that easy. 
First I wasn’t able to start SSAS and after a lot of trying and failing I figured out my SQL Server 2011 CTP trial had ended. Perfect. So, I had to uninstall 2011. Then off to download 2008 R2 because I didn’t have the ISO on my computer. Downloading 4-5GB on a shared internet connection takes a long time. To be specific; 4 hours. Finally got the ISO, and then found out that my BI-tools installation was fucked up. So, then I had to uninstall VS 2010, VS 2008 and THEN re-install 2008 and SQL Server 2008 R2. By then, I’d of course forgotten all the cool things Peter Myers talked about, so I just had to give up and get some sleep.
The other days were also interesting, but not as mind-blowing. Watching Richard Campbell and Carl Franklin on the panel discussion was awesome. Also, Carl Franklin sitting Oslo Spektrum, jamming on his guitar and singing, was perfect.
Now, a week after, they’ve released most of the videos, and the torrent will be available as soon as they’ve published all videos. Here’s a list of the sessions I attended, and those I’m going to watch later.
Sessions I attended
Day 1
- Keynote – Scott Guthrie
- Windows Azure – Under the hood – Maarten Balliauw
- Architecting for a cost effective Windows Azure solution – Maarten Balliauw
- Switch on the LightSwitch – Gill Cleeren
- Introduction to Analysis Services 2008 R2 Cubes – Peter Myers
- Taking Your Application to the Next Level with Data Mining – Peter Myers
Day 2
- Quality without Testing!! – Jason Bryant
- Why Web Performance Matters – Richard Campbell
- A developers guide to encryption – Barry Dorrans
- Mobile Panel Discussion – .NET Rocks!
Day 3
- Producing great video and audio on a budget – Carl Franklin
- CQRS in the wild – Komplett.no case study – Einar Ingebrigtsen
- Agile Patterns: Agile Estimation – Stephen Forte
Sessions I’m going to watch
Day 1
- Fundamental Design Principles for UI Developers – Billy Hollis
- JavaScript, jQuery & Ajax with ASP.NET – Damian Edwards
- Clojure – The Last Programming Language – Robert C. Martin
- Don’t get stung – an introduction to web security – Barry Dorrans
- Introduction to F#, a multiple paradigm language – Amanda Laucher
- ASP.NET MVC 3, EF Code First, NuGet, and IIS Express – Part 1 – Scott Guthrie
- ASP.NET MVC 3, EF Code First, NuGet, and IIS Express – Part 2 – Scott Guthrie
- Async 101 – Jon Skeet
- Practical OData with and without Entity Framework – Vagif Abilov
- Document databases with ASP.NET MVC – Rob Ashton
- ECMAScript 5: The New Parts – Douglas Crockford
- Let me introduce my Moncai! – Dale Ragan
Day 2
- Unplugged – Scott Guthrie
- HTML5: Why, what, who, when, how? – Bruce Lawson
- HTML5 before your very eyes – Bruce Lawson
- New Developer Features in Windows Phone “Mango” – Sean McKenna
- HTML5: Huh – what is it good for? – Remy Sharp
- Making a beautiful and rock-solid Windows Phone application – Sean McKenna
- Who needs a service bus anyway? – Udi Dahan
- Mono and Mobile – Chris Hardy
- WTF is a Monad? – Robert C. Martin
- jQuery 101 – Rod Paddock
- jQuery 102 – Rod Paddock
- Cross platform .NET in practice. An end-to-end example of the same app implemented across iPad, iPhone, WP7 and Android – Jonas Follesø
- Quality – Douglas Crockford
- Wake Up and Smell The Coffee – Anders Norås
Day 3
- Node.js – Ryan Dahl
- Introduction to RavenDB – Rob Ashton
- Async Deep Dive – Jon Skeet
- HTML5 – Offline Business Applications for Desktops, Tablets and Phones – Ingo Rammer
- Designing a JavaScript application – Christian Johansen
- Dr. CQRS or: How I Learned to Stop CRUDing and Love the Domain Model – Fredrik Kalseth
- Entity Framework 4 – Get started now – Alf Kåre Lefdal
- Top 10 things to learn from Clojure that will make you a better developer in any language – Martin Jul
- Avoiding Cross site scripting – not as easy as you might think – Erlend Oftedal
- Raven DB by Example – Emil Cardell
- Developing .NET Applications for the Mac App Store – Michael Hutchinson
To download the sessions, go to http://ndc2011.no/agenda.aspx?cat=1071&id=-1&day=3726
Photography
Posted by on June 6, 2011
This Easter I started taking pictures, or as Rick Sammon (well known photo-dude) likes to say; “making pictures”. This is an awesome hobby, which basically have moved me away from my xbox and out into the wild. Or, at least semi-wild.
My kit is a Canon 450D. I normally use a Sigma 18-200mm f/3.5-6.5 DC OS HSM which I’m really satisfied with, but I also got a wide angle Sigma 10-20mm 1:4-5.6 EX DC HSM.
Add me as a contact on Flickr: VidarNordnes. Here’s some of the pictures I’m most satisfied with.
Thanks to my mentor Aleksandr Naroznij for always reminding me what I’ve done wrong.
Last, but not least, some sites I’ve found very helpful.
COLUMNS_UPDATED
Posted by on May 9, 2011
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
Mac OS X vs Windows 7
Posted by on April 4, 2011
Late last year I bought a Mac Mini to check out what all the fuzz was all about. After a month, I upgraded to a 27″ iMac because the Mac Mini was painfully slow. With a 5400 rpm disk and 2GB RAM you can’t do much more than surfing in my opinion.
Anyhow, I bought an iMac with a bit better specs:
- 2.93GHz Quad Core Intel Core i7
- 16GB 1333MHz DDR3 SDRAM
- 256GB SSD + 1TB SATA disks
- ATI Radeon HD 5750 1GB GDDR5-RAM
- 2560 x 1440 screen resolution
I also bought a cheap 27″ Benq screen so I have more space to play around with.
My main focus on this computer was to get really familiar with Garageband, one of the applications delivered with every Mac. First impression was just “WHOW!”. It still is. Apple has done a really good job at creating an application for hobby musicians. I can easily record one or multiple tracks from my keyboard, guitars or microphones. It even helps me get my track’s beat synchronized with something called groove matching. Check out the little intro video if you’re a hobby musician. I bet you’ll be impressed!
iLife (the package which is delivered with every Mac) also contains iPhoto and iMovie. I haven’t really tried iMovie, but iPhoto is simple and fun.
So, what does a Windows-guy have to say about the operating system, Mac OS X? Short answer is I don’t like it. I’m used to Windows 7, and to be honest, I can’t see why people think Mac OS X is more userfriendly. Sure, the control panel is much more tidy, you don’t have to think about “C:, D:” etc, but that’s about it in my opinion. I use the keyboard for more or less everything in Windows. Switching apps, minimizing, maximizing, docking windows to the left or right. There are probably the same amount of keyboard shortcuts in Mac OS X, but some things are just plain stupid. For instance, if you get a message box with a Yes/No, the focus is not in message box. You have to use your mouse, because No is _ALWAYS_ the default, and in 99.99% of the cases, I’m clicking yes with my mouse. 
Also, resizing windows is a big pain. For some reason, I always place windows in the bottom right corner. What if you want to resize that window? Well, you’ll have to drag it to the middle of the screen, and then resize it in the bottom right corner, because in OS X you can’t resize from any angle. I hear this is “fixed” in Lion, the next version, though. Everything else is more or less the same as in Windows. You’ve got something similar to a start menu, a taskbar (dock) and a file menu. The fact that Chrome, VLC and other often used applications are the same on Mac, you can’t really tell the big difference. Especially if you install Parallels. If you’re familiar with XP-mode in Windows 7, you’ve already seen this. Parallels makes it seamless to run Windows applications on your Mac, and with Coherence mode they work just like Mac apps. They even mimic the keyboard-shortcuts. So, the applications that aren’t supported on OS X, I simply just run in the Windows 7 instance. That’s a big plus since the only word I can use about Outlook for Mac is; USELESS!
My conclusion is; As an operating system, Windows kicks OS X’s ass! But, the application package you get with Mac (iLife) is just awesome for “creative people” that like to play music, take pictures or make movies.
5 minutes interview #29 – Aleksandr Narožnij
Posted by on March 24, 2011
First time I met Aleksandr was my first visit to Klaipeda, Lithuania. He was kind enough to pick me up at the airport in Palanga (about 45 minutes by car), even though it was late at night. Before I met him, he had been on my team at Omega AS, and from day 1 I knew we’d be good friends. Aleksandr has been my guide every time I’ve been in Lithuania, taking me EVERYWHERE, helping me buy boxers (yes, believe it or not, this is REALLY hard to find when you’re out of clean ones) and generally just being awesome! Aleksandr joined Omega in 2006 as a trainee while finishing his studies. He finished these 1,5 years ago, and has been working with PIMS in the Products-team since then.
What did you study?
I’ve been studying Computer Science at Klaipeda University for six years. I have defended the thesis “Efficiency of database table’s indexes” for my bachelor and “Implementation of decision making process’ rules model in a software system based on models transformation” for the master degree. I got excellent grades on both – true story 🙂
Studies were mainly held at the faculty of Natural Science and Mathematics; so you can imagine how many additional “important” subjects did we have: ecology, math of all various types and even social behavior deviation 🙂 Of course, I don’t remember much of what I’ve been taught, but I’m grateful to all my tutors for helping me master the main skill – skill of studying on my own.
What’s the biggest difference between being a trainee and a full-time employee in Omega UAB?
Trainee position in UAB is a dream job. After passing the standard Certification Program you are normally given some real projects to work with, however you are free to use part of your working time for exploring how things work. I came to Omega with some basic theoretical knowledge and learnt pretty much everything in practice. Many thanks to Vidar – he spent much time and effort with us trainees.
I still “google” a lot, but now I don’t have enough time to go deep into details – that’s the main difference.
At work, what’s in and what’s out of your comfort zone?
I’m a kind of a SQL guy, if you like. Anything directly related to SQL Server is what I really like working with, especially when it comes to tricky data-model questions and realization. I believe that well modeled and strictly implemented database integrity is a basis of any system of a perfect sound. There is so much to learn in the field; therefore I’ve started preparing myself for Microsoft exams.
I’m not a Web-guy for sure. I guess Web is the future, but at the moment I do not like working with web-apps.
You’re good at focusing on your projects. What’s your secret?
There is no secret – I like what I do and I do it with all responsibility. My projects are most often quite interesting and challenging. When your job is interesting and gives you satisfaction – that’s all you need to do it well. Even if I happen to work with some routine tasks, I always try making it interesting for myself.
You like taking pictures. Any favorite genre(s)?
Oh, yeah – I like the process of taking pictures. Especially when you are lucky enough to “catch” the right moment or emotion. I am always happy when I succeed in sharing the mood of the moment via a picture.
I was planning to organize something similar to a photo-club, which guys are having in Ølensvåg. Still have a desire for it, so – coming soon 🙂
Take a look at Aleksandr’s flickr album here.
SQL Server Licensing
Posted by on March 15, 2011
Occasionally I get questions related to licensing, which is boring but necessary to know a bit about. There are many resources out there to help, but some might be more confusing than others. Therefore I’m posting this to give you a quick overview of how it works.
Two licensing types
Processor License; For physical servers you need one license per physical CPU. If you buy two quad-core CPUs, that’s two physical CPUs, not eight. In other words, you need 2 processor licenses. Now, if you have a virtual server, it’s a bit different. There you need one license per virtual CPU assigned to the virtual machine. If you have a virtual hosting machine, with two quad cores, running 4 virtual servers which each has access to 2 cores, and each of those virtual servers are running SQL Server, you need 4 * 2 processor licenses. Processor licensing is recommended if you don’t know how many users or devices that’s going to connect to your server(s), or the number of connected users/devices are so high it’s less expensive to buy processor licenses.
CALs; Using CAL-licensing you first need a server software license, and then one CAL per user or device connecting. NB! This is not concurrent users or devices. And, this is counting physical users/devices, not the number of SQL Server logins on your server.
Pricing
| Edition | Per Processor | Software | CAL |
|---|---|---|---|
| Enterprise | $27,495 | $8,592 | $164 |
| Standard | $7,171 | $898 | $164 |
Editions
There are many editions of SQL Server, as most of Microsoft’s server products. Here are the most common ones.
- Compact Edition; Mostly used in embedded system to help caching data, syncing or similar. Limited to 4 GB. This is completely free, but isn’t an adequate server.
- Express Edition; Used for small databases with few users. Free, but limited to 1 CPU, 1 GB RAM and 10 GB (data file, not counting the log file).
- Developer Edition; Same as Enterprise, no physical (or feature) limitations, but license limits you to development and testing only. $27 per developer, unlimited computers.
- Standard Edition; Most common in medium size companies. Limited to 4 CPUs and 64 GB RAM. Some feature limitations. Pricing: see table above.
- Enterprise Edition; Most common in big size companies. Limited to 8 CPUs and 2 TB RAM. Pricing: see table above.
To see which features are in each edition, please visit Microsoft’s “Edition Compare” at http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
Example
A world-wide customer needs a SQL Server to run PIMS and other systems. They have already bought a server with 4 x quad-core CPU and put it in their in-house data center. All their offices are connected to their HQ with very good connections so no need for replication or similar. This customer has about 1.000 employees, each connecting to one or more of the systems on their new SQL Server. Enterprise Edition is probably the one you want to go for.
| Server + CALs | |||
|---|---|---|---|
| License | Price per | # | Total |
| Server | $8,592 | 1 | $8,592 |
| CALs | $164 | 1.000 | $164,000 |
| $172,592 | |||
| Per Processor | ||
|---|---|---|
| Price per | # | Total |
| $27,495 | 4 | $109,980 |
As you see in the tables, 2 physical CPUs are much cheaper than 1.000 CALs. In addition, you don’t have to worry about the customer adding new employees to the systems. Be aware though that the server license is per OSE (Operating System Environment), so if you run multiple virtual Windows Servers with SQL Server installed on each of them, you need one server license per virtual machine.
If you want to read more details visit Microsoft’s SQL Server information pages:
http://www.microsoft.com/sqlserver/2008/en/us/licensing-faq.aspx#licensing
http://www.microsoft.com/sqlserver/2008/en/us/pricing.aspx
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
5 minutes interview #28 – Roger Gullhaug
Posted by on March 14, 2011
Roger is one of the board members in NNUG Haugesund. He’s active both in the user group and on twitter, which I really like. In addition to being an active community member, Roger works at Hatteland Computer as one of the technical dudes, creating their new framework in Silverlight. Since he’s a Silverlight-guru, I’ve exploited his knowledge when I was testing out developing in Silverlight. Very helpful guy, and might I add; VERY patient with me 🙂
Can you give us a little insight on what you do at Hatteland Computer?
I’m working as a developer in Hatteland Computer. For the last 2 years I have mainly worked with Silverlight. I have been technical responsible for our Silverlight client framework which will be used for all Silverlight development in Hatteland Computer in the future.The framework is focusing on solving the challenges of writing a large system in Silverlight. We have a plugin-based architecture which makes it easy to split a large system up into small maintainable pieces which can be deployed independently. Other main areas of the framework are to make it easy to connect to the backend, make it easy to develop multi lingual applications and take care of everything which has to do with navigation. The framework also includes a Widget framework which makes us able to easy develop customizable dashboards. In addition to writing the framework I have also worked on developing a new user interface for our Rambase ERP system. Our framework is built according to the needs for this new user interface. It has been a very interesting project, and we now have released the first beta version to some selected customers.
[youtube:http://www.youtube.com/watch?v=iyVDMp2bL9c%5DHatteland is famous for RamBase. Could you tell us a bit about it?
Rambase is an ERP system developed by Hatteland Computer. It is a complete business system made up of modules for sales, customers (CRM), purchasing, warehousing, production, auctions, economics, specialized trade (retail), human resources and web shops. In addition to being an ERP system, Rambase is also the name of the database used by the Rambase ERP system. It’s a database system developed by Hatteland Computer where all data is stored in RAM.Hatteland Computer also has another product called Autostore. Before starting working with the Silverlight framework I worked on Autostore. I’m still a little involved in this, but not much anymore. It’s a very cool product. Take a look at this video.
What do you think about the community here in Haugesund?
The community in Haugesund is small, but good. When I was asked if I was interested in being part of NNUG Haugesund I didn’t hesitated. I hope the developer community will grow in the future. It’s really great to have someone like you Vidar in NNUG Haugesund, and I hope you will keep up the good work to bring NNUG Haugesund forward.
When are you going to have a session for us?
Hmm…. Suddenly it happens… but I’m a little shy when it comes to talking in front of many people. If I would hold a NNUG session it has to be on something I know really well.
What do you do when you’re not playing with Silverlight?
I have a wife and two children (3 and 6 years old) and I try to spend much time with them. I also got a new hobby – floor ball. I play together with some people from my neighborhood. Last summer I started cycling, and now that the spring is near I look forward to get on the bike again.
Links:
Blog
@RGullhaug
Dynamic SQL
Posted by on March 10, 2011
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
5 minutes interview #27 – Jan Ove Halvorsen
Posted by on March 7, 2011
This is one of the most active guys in the community here in Haugesund. When I was thinking about starting a Microsoft-related User Group in the district, he had the same plans. He sent me an email asking if I was interested in starting it with him. That is now 3 years ago, and the UG is still up and running. Now I’m the chapter lead, and he’s the one responsible for communication with the other chapters in Norway etc. We might switch on these roles. He’s held several sessions both for NNUG and Omega AS, and we hope he’ll continue with this! Lately he joined a new company called Appex AS together with another board member of NNUG Haugesund; Tore Fremmersvik.
What’s your role in Appex?
My official title is “Technology Director”. However, short-term I am first and foremost a fulltime consultant delivering system development services to pretty much the same customers I was already involved with before joining Appex. Longer term it is our plan that my role will be gradually more and more in line with my title. I will be responsible for the quality of our strategic decisions on technology and methodology. It means I will not necessarily take the decisions, but will be responsible for the quality of the process leading up to a decision, and the implementation of decisions in these areas. This also includes being responsible for our plans related to continuous improvement of our competence and knowledge. Having said that, I anyway guess I will continue to do consulting in parallel for some years still.
What did you do before you joined Appex?
My education is actually within the areas (micro-)electronics, physics and mathematics. However, all my professional life (20+ years) has been mostly about software. I worked at Hatteland pretty much all through the 90’s, where I was involved in building the famous RamBase system. In 2000 I joined Hydro Aluminium’s Rolled Products sector as a senior systems consultant. During those years I was heavily involved in a major project called MACH2. MACH2 is a production management system used by the two Norwegian rolling mills, at Karmøy and in Holmestrand. In 2005 I took on the challenge as IS/IT-Director for these two plants, with full responsibility for the whole IS/IT domain. But the geek in me conquered in the end. I really enjoyed that position, but felt I drifted farther and farther away from what I love the most: programming. So, in 2007 I left Hydro and set up my own consultancy, Halvorsen Consulting AS. I had three nice years doing various software development tasks for a small number of clients, before I decided to join Appex less than half a year ago.
Why do you like being active in our community?
I have always been focused on and interested in knowledge sharing. And the fact is that the process of diving deep into a new subject, build up insight on that subject and then share that insight with others is a remarkably giving thing to do. I actually feel I get more back from doing something like that than I’m giving away. This may sound like something I’m obliged to say, but it’s actually true. However, I realize this takes some confidence to do. I would never even consider doing that unless I feel the subject is something I could master. If you asked me to learn to play the piano and have a NNUG session about it, I would shake my head wildly. But ask me to have a NNUG session about LINQ (in C# !!), and you will see a big smile…
Are you planning any new sessions for NNUG?
Since I’m these days involved in a deep romance with a beauty called Silverlight, I can see something related to that on the horizon… I’m also very excited about what is happening on the parallel programming area with .Net/C#, both in .Net/C# 4 and even more so in .Net/C# 5, so something related to that could also be coming up…
What do you do when you’re not in front of your computer?
I have a family, who by the way is quite forgiving when it comes to romances like the one mentioned above, so most of the not-in-front-of-the-computer time takes place in the family domain. I am also an online gamer (hmmm… that I guess in fact counts as in-front-of-the-computer time…). Lord of the Rings Online (LOTRO) it is. I have been playing the same elf character, a so-called “tank”, actually for 2-3 years now. I love the social dimension of online gaming, although it in periods sadly has to get very low priority. As a long-term admirer of J. R. R. Tolkien’s fantasy world I just had to try when the game was launched, and I have since then been lost. Quite interesting to be questing through Middle-Earth, fighting hordes of nasty orcs, goblins and worse creatures, at the age of 40+, together with people of all ages from all around Europe, and even beyond.
Blog ala Vidar 