SQL Server Planet

In order to create a view, use the “Create View” statement using the following convention:

CREATE VIEW dbo.vStoreAccountNum
AS
SELECT AccountNumber
FROM Sales.Customer
WHERE CustomerType = 'S'

Views are essentially stored SELECT Statements. They do not offer any speed advantage over regular SELECT statements. And they are not faster than a SELECT statement executed from within a stored procedure. They are mostly good for hiding complicated logic, or for providing an additional layer of abstraction from your source tables as a security measure. Along with a good indexing strategy, views can be used to simplify highly normalized tables.

The more joins within a view, and the more columns it returns back, the slower it is going to be. If you use views, try to make them only perform one function and only return back a finite number of columns. You don’t want views that return back a lot of columns when the majority of your queries only use one or two columns.

Here are some Do’s and Don’ts:

• Do use views if you want Data Analysts to access common queries.
• Do make sure all the join columns in a view contain indexes.
• Do Not call views from within views.
• Try not to return a lot of columns within a view if the view is referenced a lot.
• Do Not use SELECT * within a view.  Specify the columns names explicitly.

Indexed (or Materialized Views)

A materialized view is a view that has been indexed and has essentially been transformed into a dynamically updated table.  This kind of view requires a special clause called SchemaBinding which disallows changes to occur on the base tables it references.  An indexed view requires a column that can be used as a unique index, which in turn acts as the clustered index for which all the data is aligned.

I personally try not to create indexed views because of the potential for maintenance nightmares.  When schemabinding is placed on a view, none of the tables it references can be changed unless the view is dropped.  Once the base tables are modified, the view needs to be recreated.  The other issue I have found with indexed views, is the optimizers unwillingness to utilize them.  Instead, most of the time in order to get the optimizer to use the indexed view, I would have to specify the WITH NOEXPAND hint.  If I did not, then the optimizer would use the base tables instead.  This is cumbersome because in the cases I’ve needed to use an indexed view, my goal was to rename the original table referenced by many procedures, and create an indexed view in it’s stead, only to find I would have to modify the procedures also to use the NOEXPAND hint.

Nevertheless, they do have uses, for the most part however, I prefer using staging tables that I can maintain on my own, and that get updated on set schedules.

If you are going to use a schemabound view, specify the following:

CREATE VIEW dbo.vStoreAccountNum
WITH SCHEMABINDING
AS
SELECT AccountNumber
FROM Sales.Customer
WHERE CustomerType = 'S'

Now that the view is schemabound, you need to create a unique clustered index on it.

CREATE UNIQUE CLUSTERED INDEX IDX_vStoreAccountNum ON dbo.vStoreAccountNum(AccountNumber)

Now the view is schema bound. Some notes on SchemaBound views.

1. They cannot contain non deterministic functions EX: GETDATE(), DateDiff()
2. You cannot use Count() instead use COUNT_BIG()
3. You cannot use UNION
4. You cannot use DISTINCT
5. You cannot use SubQueries
6. You cannot use Self-Joins
7. You cannot use Complex Aggregate function

As you can see, make sure not to code yourself into a corner using Indexed Views.

The SQL Replace function replaces a value within a string with another value. The final output is the original string with the replaced value. There are three parameters to the Replace function. The first is the original string. The next parameter is the string to replace. And the last parameter is the value to replace the string with.

Here is an example:

SELECT
	REPLACE
	(
		'original string',	-- Original String
		'string',			-- String to replace
		'replaced'			-- What to replace with
	)

The output from this is: “original replaced”.

One thing you need to watch out for is that if any of the parameters in the replace statement are NULL, then the string that is returned will also be NULL. So if passing parameters dynamically and there is a chance of NULL, make sure to use the ISNULL function and turn it into an empty string.

You can also sometimes get away with using REPLACE rather than concatenating strings. I’ve done this before when you have a templated piece of text that needs values injected into it.

DECLARE @ResponseMessage varchar(max);

SET @ResponseMessage = 'Dear [firstname], thank you for signing up for [subscription].';

SELECT @ResponseMessage =
	REPLACE
	(
		@ResponseMessage,
		'[firstname]',
		pc.FirstName
	)
FROM Person.Contact pc
WHERE ContactID = 1;

SELECT @ResponseMessage;

Though it may not seem like much of a big deal, when dealing with many lines of concatenated text, it comes out much cleaner.

Although there are a few ways to call a stored procedure, only one way is actually recommended. The best way is to explicitly specify the stored procedure parameters in the Execute statement. You do this because if the order of the parameters were to change, then the procedure would error out (or even worse pass the incorrect values to the parameters).

Let’s create a stored procedure as an example:

CREATE PROCEDURE spReturnFullName
(
	@FirstName varchar(50),
	@LastName varchar(50),
	@Age int,
	@ClientName varchar(100) OUTPUT
)
AS
SET NOCOUNT ON
BEGIN

	SELECT
		@ClientName = @FirstName + @LastName

END

Now that we have our procedure, let’s call the procedure using EXECUTE. We can also use the EXEC statement as a replacement for Execute, it is merely a simplified way to call the same statment. So below we are specifically specifying the parameters of the procedure. Unfortunately, SQL Server allows procedures to be executed without specifying the parameter name. The way we are doing it below is not only self-documenting, but as mentioned, it will not allow the wrong values to be passed to a parameter.

DECLARE @ClientNameReturn varchar(100)

EXEC spReturnFullName
	@FirstName = 'Vincent',
	@LastName = 'Vega',
	@Age = 40,
	@ClientName = @ClientNameReturn OUTPUT

SELECT @ClientNameReturn

This procedure also has an output parameter, which is denoted by the OUTPUT reserved word.