Most technology blog posts need to show some sort of code samples to demonstrate/explain something, but we often see code that is correct, but not really handy for a user to try it. I thought of outlining a few things that I learned over period of time.
Code sample should contain everything needed to run it by a user.
Many years ago, if some one had asked me to write an example demonstrating the ROW_NUMBER() function, I would have done something like the following:
/*
Hey guys, the following code demonstrates the ROW_NUMBER()
function that generates a serial number for customers. The
serial number resets after each city
Run the following query in Adventureworks database
*/
SELECT
ROW_NUMBER() OVER(PARTITION BY City ORDER BY CustomerName) AS Sr,
CustomerName
FROM Customers
Note that the query may be correct, but it may not run if a user does not have AdventureWorks database (or whatever database we ask user to use) does not exist in their computer. I can always argue that "if they want to run it, they should install it, what is wrong in it?"
However, it may not be handy for a normal user who may just want to try the code quickly. If you ask me today to write the same query, I would write something as follows.
CREATE TABLE Customers (
Seq INT IDENTITY PRIMARY KEY,
CustomerName VARCHAR(20),
City VARCHAR(20)
)
INSERT INTO Customers(CustomerName, City)
SELECT 'Jacob','Ahmedabad' UNION ALL
SELECT 'Pinal','Bangalore' UNION ALL
SELECT 'Vinod','Bangalore' UNION ALL
SELECT 'Tejas','Ahmedabad'
SELECT
ROW_NUMBER() OVER(PARTITION BY City ORDER BY CustomerName) AS Sr,
CustomerName
FROM Customers
Note that this version does not have any dependency. Users can just copy this, paste into their SSMS and can simply run it. Isn't that better than the original version?
What if the user wants to run it again?
Some users might want to run the code more than once, and in that case the above code will throw an error because the table already exists after the first execution. So we may want to add a drop/create statement into the code.
IF OBJECT_ID('Customers','U') IS NOT NULL BEGIN
DROP TABLE Customers
END
CREATE TABLE Customers (
Seq INT IDENTITY PRIMARY KEY,
CustomerName VARCHAR(20),
City VARCHAR(20)
)
Wait..Wait..!!! there is a hidden danger here. Most people have a tendency to copy and paste the code to their SSMS and HIT F5 to run it. Unfortunately, some times they may paste it into their active window with an active connection to a database that they need for work/production. And if by chance a table with the same name exists on their database...that will be gone!!!
In most cases, this could be avoided by changing the context to TEMPDB before executing the code. So you might want to include a USE TEMDB statement at the beginning as given below.
USE Tempdb
GO
IF OBJECT_ID('Customers','U') IS NOT NULL BEGIN
DROP TABLE Customers
END
CREATE TABLE Customers (
Seq INT IDENTITY PRIMARY KEY,
CustomerName VARCHAR(20),
City VARCHAR(20)
)
Include results whenever possible
The query we saw earlier demonstrates the usage of the ROW_NUMBER() function. However, the user will need to run the query to see the results. A user may not have access right away to the software platform required to run the code when reading it (in our case it is SSMS). What if I am reading it on my phone or while travelling on my iPad etc?
I believe that the code we saw earlier will look more complete and comprehensible by just adding the result of the query as given in the following example.
USE Tempdb
GO
IF OBJECT_ID('Customers','U') IS NOT NULL BEGIN
DROP TABLE Customers
END
CREATE TABLE Customers (
Seq INT IDENTITY PRIMARY KEY,
CustomerName VARCHAR(20),
City VARCHAR(20)
)
INSERT INTO Customers(CustomerName, City)
SELECT 'Jacob','Ahmedabad' UNION ALL
SELECT 'Pinal','Bangalore' UNION ALL
SELECT 'Vinod','Bangalore' UNION ALL
SELECT 'Tejas','Ahmedabad'
SELECT
ROW_NUMBER() OVER(PARTITION BY City ORDER BY CustomerName) AS Sr,
CustomerName
FROM Customers
/*
Sr CustomerName
-------------------- --------------------
1 Jacob
2 Tejas
1 Pinal
2 Vinod
*/
Try not to use Images to show code samples!
I am not a great fan of writing code, taking a screen shot and posting it in a blog. I see two problems with that approach.
- A user will not be able to copy and paste the code
- The code will not be visible to a search engine.
So my recommendation is NOT to post images of code samples.