Back to Basics
We’ve all written a CASE expression (yes, it’s an expression and not a statement) or two every now and then. But did you know there are actually 2 formats you can write the CASE expression in? This actually bit me when I was trying to add some new functionality to an old stored procedure. In some rare cases the stored procedure just didn’t work correctly. After a quick look it turned out to be a CASE expression problem when dealing with NULLS. In the first format we make simple “equals to” comparisons to a value: ...
Two years ago 59 SQL Server MVP's came together and helped make one of the best book on SQL Server out there. Each chapter was written by an MVP about a part of SQL Server they loved working with. This resulted in superb quality content and excellent ratings from the readers. To top it off all earnings went to a good cause, the War Child International organization. That book was SQL Server MVP Deep Dives.
This year 63 SQL Server MVPs, me included, decided it was time do repeat the success of the first book.
Let me introduce you the:
SQL...
SQL injection is a method by which a hacker gains access to the database server by injecting specially formatted data through the user interface input fields. In the last few years we have witnessed a huge increase in the number of reported SQL injection attacks, many of which caused a great deal of damage.
A SQL injection attack takes many guises, but the underlying method is always the same. The specially formatted data starts with an apostrophe (') to end the string column (usually username) check, continues with malicious SQL, and then ends with the SQL comment mark (--) in order...
I was wondering what i should write about for my 200th blog post. And I’m in luck because Karma’s got my back and that’s why yesterday we had an interesting case of plagiarizing. So let’s get optimistic! A relatively new blogger called SQLDigs copied this post of mine about new version of SSMS Tools Pack. Because there was no trackback or any other link back to my blog I had no idea until David Levy (Blog|Twitter) told me about it. As I’ve looked over the other content of the blog I’ve noticed quite a few familiar paragraphs of...
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples This is the third...
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples This is a second...
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples With that out of...
A few days ago Adam Machanic proposed a great idea about a T-SQL Tuesday. Every Tuesday there would be a topic bloggers all around would post about. Chosen as the first topic was the date and time stuff in SQL Server.
Because there’s already all this great content out there I’m not going to repeat it but I’m going to put together a ton of datetime resources from SQLTeam forums and blogs as a resource you can use in the future.
Michael Valentine Jones
MVJ as we like to call him in the forums has a thing for datetime manipulation. He has...
Simply put:
I prefer using surrogate keys because natural keys are by default a subject to change which is a bad behavior for a row identifier.
But let’s dig a bit deeper into each key type to see why this is. Here’s a little table with column names that tell us what kind of a key each column is.
Surrogate keys
A surrogate key is a row identifier that has no connection to the data attributes in the row but simply makes the whole row unique. And that property is also the downside of it. Because it has no connection to the data...
Today I had an interesting problem.
If I rebuilt my solution every thing would succeed but after that if i pressed F5 (build) the whole solution would get built again but it would error out. Now this doesn’t make any sense because the difference between Build and Rebuild is that Rebuild always compiles and links all files + all dependencies, while Build only compiles and links files that have changed since the last build. So everything should be fine. The thing about our solution is that the we have some post build events that do some IL merge, etc… the...
There are times when you want to have one .cs file in multiple projects. However if you do Add existing item you’ll notice that the file is copied to each project’s folder. This is not what we want.
The solutions is of course pretty simple once you know where to look. In the Add existing item dialog you have to add the file as a Link as is shown on the picture:
And there you go. you can have one file in multiple projects.
Let us start with a simple question: What is the goal of software development, be it database or .Net (or any other language)? The first answer would be: Customer satisfaction! And you'd be right. However there's more to customer satisfaction then the immediate product delivery effect. We have to think about future change requests, maintenance periods, etc... Almost every business application out there consists of 2 basic parts: database back end and some kind of front end that consumes the data. In our case the front end is anything with access to the database. In regard to future code...
Probably everyone is familiar with the Count(*) function in SQL Server.
But there seems to be a great deal of confusion amongst youngsters (SQL wise) about how all its possible options work.
Let us banish the confusion back to the dark realms where in belongs to:
DECLARE @t TABLE (val INT)
INSERT INTO @t
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 4 UNION ALL
SELECT 4 UNION ALL
SELECT 5
SELECT COUNT(*) AS CountAll, -- counts all rows
COUNT(val) AS CountAllNoNull, -- counts rows that don't contain NULL
COUNT(DISTINCT val)...
it's my opinion that today we unfortunately live in a world that requires some kind of harder problem solving skills from maybe 20% of the population. And I'm being very very generous. So putting that percentage with the question in the title would give an answer similar to: "Because not every one can do it." DOH, right? I'd say you're wrong since everyone had a problem to solve at least once in a lifetime. So what's the problem? Why is it perceived as hard? Let's take a little trip through your past. When you were just an infant, what was...
I've written an article here on SQL Team about locking in SQL Server.
It's an introductory article that shows lock modes, lock granularity and lock compatibility matrix
and it will be followed by a few more advanced ones on the topic of locking.
Part 1: Introduction to locking in SQL Server 2005
Indexes are a constant problem in understanding for beginners (and the "not so beginners") in the database world. And don't you just love the
hardcore mathematical explanation of B-Trees and their traversal. Personaly I much rather have visual props and a story to support an explanation.
Appealing visual props are even better. That's how this post originated. So let us begin!
Imagine you live in a pre-computer-in-every-nook-and-crane world (around 1960's :)).
You wake up one morning with a huge craving to read Agatha Christie's books. So you go to the library which has a few million books.
You walk up to the cute librarian (look...