3,609 reputation
21733
bio website
location Bern, Switzerland
age 48
visits member for 3 years, 5 months
seen 16 hours ago

Note to all SO beginner:

Please read (and embrace!) Jon Skeet's helpful hints on how to write a good question (or at least his short version here ) - one that has a chance that someone can answer it.


A Pascal/Delphi programmer and database freak at heart, I work as a C# developer on line-of-business applications (mostly ASP.NET).

I'm interested mostly in distributed systems (WCF), database (design and SQL), and Active Directory.


I'm an MCPD .NET 4 Web Developer, MCPD .NET 4 Windows Developer, MCPD Enterprise Application Developer (.NET 3.5), and MCTS for WCF, ADO.NET, Winforms and ASP.NET.


2d
comment How to fix the SQL from SQL Server to Oracle?
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was discontinued with the ANSI-92 SQL Standard (more than 20 years ago)
Jun
20
comment Efficient query for an advanced EAV model
@AaronBertrand: thanks for your post - but I'm still not at all convinced... the messy (and often hopelessly ineffective) queries, the inability to store data in its native format / datatype (everything has to be "squeezed" into a string format) ...... too many drawbacks for my taste!
Jun
20
comment Identity column as clustured index bad idea?
Read Kimberly Tripp's blog posts on clustered index - she's the Queen of Indexing in the SQL Server world, and her basic recommendation is always to use INT IDENTITY as the primary (and clustered) key on almost any table. It's a well recommend best practice and usually works just fine. Cases where this is not a good idea are relatively rare in my opinion.
Jun
20
comment Problem creating database using default mdf and ldf path
Instead of SET @logpath = (select ..., I'd recommend to just use SELECT @logpath = ....
Jun
20
comment Efficient query for an advanced EAV model
See Five Simple Database Design Errors You Should Avoid (point #3) for a discussion of why EAV is really really bad, and see Bad CaRMa for a true horror story of an EAV system design that ruined a thriving company. In brief: don't use EAV - and don't expect a good solution for dealing with an EAV...
May
20
comment SQL SERVER 2008 Cursor Problem
the best way to tune a cursor is to get rid of it :-) That keeps coming back as a recurring insight over and over again! :-) Great to hear you got your performance to a useful level!
May
20
comment SQL SERVER 2008 Cursor Problem
WHY OH WHY a cursor for something as simple as this?? Use a CTE and a proper, set-based approach to AVOID cursors altogether!
May
9
comment Trigger to update a date field on one table when another table is updated
Please do not put code samples or sample data into comments - since you cannot format it, it's extremely hard to read it.... Instead: update your question by editing it to provide that additional information! Thank you.
May
6
comment Designing a database with Ints
YES! Physical database design does matter - even today in 2014!! Check out Kim Tripp's interview on .NET Rocks - and if you have a Pluralsight video subscription, check out her Pluralsight course on "Why physical database design matters" - you won't ever ask such a question again after that!!
Apr
21
comment Get into SQL Server 2008 with no management studio and no sqlcmd
@MikeFal: sqlcmd replaced osql in SQL Server 2005, actually ...
Apr
18
comment New SQL Server 2008 instance, load old instance DB's
MDB is MS Access - not SQL Server. SQL Server data files are .mdf (and .ndf), transaction log files are .ldf
Apr
7
comment How control 27 locations and 27 databases?
You might need a third-party tool like Spotlight on SQL Server to handle such a job
Apr
7
comment How control 27 locations and 27 databases?
There is no SQL Server 2012 R2 - there are versions 2008 R2, or then 2012, and now just recently 2014 was released ....
Apr
5
comment Which should I use on my DB? - Clustered Index or Non Clustered Index or Both
To be very clear: the primary key in SQL Server DOES NOT determine the physical ordering of the table! It's the clustering key which does that. By default, the primary key is also the clustering key - but it doesn't have to be. The primary key is "just" a logical construct to uniquely and reliably identify every single row in your table.
Apr
3
comment Database Normalization: A Case Of The NULLs
Another option could be to make the column non-nullable, and define a "magic" value that sort of "takes over" the meaning of NULL - like -1 for an INT column. It's sometimes frowned upon - but it makes your queries a lot easier if you can rely on an INT value to always be there for sure.
Apr
3
comment SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012
Shrinking the database can totally screw up your indexes and can massive fragmentation. If you insist on keeping that step, do this before you reorganize & rebuild your indexes, so that those indexes can be defragmented after shrinking the database. Or leave out that step altogether.
Mar
19
comment Can I recover a single table from a SQL Server 2012 database backup
You cannot really restore just a single table out of a SQL Server backup - SQL Server backups are all-or-nothing.
Mar
18
comment Efficient way to Add NON-CLUSTERED Index?
Try your two approaches, measure performance -> pick the better option!
Mar
15
comment Update Statement takes forever to complete
If you're trying to update 35'000 rows at once, you might run into the problem that SQL Server will use a lock escalation and will attempt to lock the entire table for an update. If something else is already happening at the same time, SQL Server might just not be able to get that table-wide, update/exclusive lock - so it'll wait until it does get that lock... you can get around this by updating no more than 5000 rows at once
Mar
13
comment SQL Server 2008 R2: cannot attach database
Which part of file not found do you not understand? The file you're looking for to attach isn't there ... Are you trying to attach to a remote SQL Server instance, and the .mdf file is on your local `C:` drive? That won't work! The file must be on the server machine - not your local PC ....