SQL Server stored procedures best practices

Today a post about SQL Server naming conventions. I've gathered these naming conventions in the last couple of years and i've implemented them at projects at my customers. This is the fourth post in a serie of best practice posts (part I, part II, part III and part IV ). As said, this post is about naming conventions and these naming conventions can be subdivided in server objects-, database objects- and T-SQL naming conventions.

• Naamconvention according to Camel Casing.
• Never prefix a stored procedure with sp_.
• Think about schemas when you're using a lot prefixing in the names of stored stored procedures.
• Create stored procedures in the database where they're used.
• Stored procedures that are used in OLEDB Sources has the following conventions usp<manipulation>_<name>.
• Use 'par' as a prefix for parameter variables (eg @intParTableName). This will help reading stored procedures and it distinguishes SP variables and parameter variables.
• Write comment if something is not quite clear. Length of comment doesn't effect performance.
• Always write case consistent names in your code. Meaning that when you move your code from Case insensitive database to a case sensitive database it will still work.
• Add a debug parameter to a stored procedure. This could be a bit type. When you pass a 1 all SQL statements are printed and nothing is executed. This way you can debug your stored procedure and it's even more helpfull when you're using dynaminc SQL.
• If you're using multiple times a function in a stored procedure, store the result once in a variable and use this variable instead.
• Make sure that  a stored procedure always return a returnvalue. Return values are standardized and are used for returning the status of the execution of a stored procedure. Use the OUTPUT parameter for returning data.
• Use SET NOCOUNT ON at the beginning of a stored procedure (and triggers) as will messsage be suppressed, like  '(1 row(s) affected)' . this enhance performance and reduces netwerktraffic
• Use a less possible SET statements because they can issue a recompilation of the stored procedure.
• Use a proper length of input parameter variable. Using a to large input parameter variable can cause SQL injection and it reduces memory usage.
• don't use WITH RECOMPILE with your stored procedures
• Keep your stored procedures short. Break long stored procedures in smaller stored procedures.
• Use as less possible "WITH ENCRYPTION". Only use this when end users have access to the database and/or when you're using source control software.

Greetz,
Hennie

SSAS : Connecting to a cube with excel

Hi,

For one of my clients i'm building a cube for analysis of  information. An interesting project because the data is never analyzed before. The situation is as follows; there are datasources which has been developed in the last couple of years and there is data gathered in earlier OLAP development projects. The trick is to combine these measures into an analysis tool with a cube, reporting services and (off course) excel.

This blog is about how to connect to an analysis cube with Excel. I'm not talking about building a cube but on how to connect to the cube with Excel.

When to choose for a cube:

• A technical reason : performance. The performance of aggregated data is very well certainly in case of huge amounts of data. 
• Pivoting possiblility. In my opinion a very handy functionality of cubes. The flexibility of dynamically playing with data is very handy for getting insight in your data.
• Seamless integration with Excel. The integration with excel is great.
• Prototyping. Certainly in combination with excel you can let the key users play with the data to get some insights, thoughts and ideas. When ideas have come to mind, you can build the reports and dashboard on it.

Okay lets start building an Excel sheet based on a cube. If reporting accesses to cubes can be provided from Microsoft Excel, then report building can be performed by an end user. Majority of the time, using this method, users can construct reports the way they wish. Improvements in Excel 2007 have provided a number of new fancy features that can be used with cubes.

Please note that this article does not cover how to build SQL Server Analysis Services (SSAS) cubes. Also i'm assuming that you have your cube deployed on a proper server.

1. Open Excel and goto 'Data'' and select "From other sources". Below you can see some screenshots (in dutch):

2. Select the right cube from the list :

 3. Store the file with the connection information on a certain location

4. And now it's possible to drag certain values from the cube into Excel

Okay that's it for now.

Greetz,

Hennie

SSRS: Report data window lost?

Hi.

Ever lost your "Report Data" window that shows Datasets in Reporting Services 2008 and in Reporting Services 2008 R2. I searched the menus for hours...okay for minutes and then googled about this issue. And you know what ? There is no menu option for this. Nope. Only a shortcut.

CTRL-ALT-D

is the magic.

Greetz,

Hennie

Best practices SQL Server naming conventions

Hi,

Today a post about SQL Server naming conventions. I've gathered these naming conventions in the last couple of years and i've implemented them at projects at my customers. This is the fourth post in a serie of best practice posts (part I, part II and part III). As said, this post is about naming conventions and these naming conventions can be subdivided in server objects-, database objects- and T-SQL naming conventions.

There are a lot advantages when you stick to them. One anecdote about a customer who said to me: "a naming convention would be very helpful because when i'm building queries i don't see the difference between tables and views, at first glance.". Another remark about naming convention is that you can combine the naming conventions with other naming conventions e.g. tblODS<table> or tblDimCustomer. This way you can combine your technical naming convention with a more functional naming convention.

SQL Server Server objects

No.	Subject	Prefix	Naming convention	Example
1	Database	db	db<name>	dbODS
2	Job	jb	jb<Jobname>	jbSSIS_RunLoadPackage
3	Linked Server	ls	ls<linkedservername>	lsServer01
4	Trigger(Delete)	td	td<name>	tdAppointment
5	Trigger(Insert)	ti	ti<name>	tiCustomer
6	Trigger(Update)	tu	tu<name>	tuAppointment

SQL Server Database objects

No.	Subject	Prefix	Naming convention	Example
1	Aggregate function	af	af<Name>	afTotalSales
2	Assemblies	as	ass<Name>	assDetermineIPs
3	Attribuut	-	<name>	PatientName
4	Database data file(s)	data#	<database>_data#	dbAdventureWorks_data1
5	Database log file	log	<database>_log	dbAdventureWorks_log
6	Default	df	df<name>	dfDate
7	Filegroup	fg	fg<name>	fgdbAdventureWorks_Index
				fgdbAdventureWorks_Data
				fgdbAdventureWorks_Log
8	Filestream	fs	fs<name>	fsInvoiceDoc
9	Foreign key	FKx_	FKx_<sourcetable>_<goaltable>_<fieldname(s)>#
10	Index (not unique, non clustered)	IDX_	IDX_<name>_<column>
11	Index (unique, non clustered)	UDX_	UDX_<name>_<column>
12	Index (not unique, clustered)	CIX_	CIX_<name>_<column>
13	Index (unique, clustered)	CUX_	CUX_<name>_<column>
14	Primary key	PK_	PK_<name>	PK_KlantID
15	Scalar valued function	scf	scf<name>	scfDetermineNumberOfCustomers
16	Schema	sch	sch<name>	schOrders
17	Stored procedure	usp	usp<projectname>_<name>	uspArchive_DetermineNumberOfOrders
18	Synoniems	syn	syn<name>
19	Table Valued function	tbl	tbl<name>	tblCustomers
20	Table Valued function	tvf	tvf<name>	tvfDeterminecustomers
21	Temporary Table	#tmp	#tmp<name>	#tmpPatient25Years
22	Type	udd	udd<name>	uddPostcode
23	Type	tp	tp<name>	tpPosInt
24	View	vw	vw<name>	vwGetOpenOrders

T-SQL

No.	Subject	Prefix	Naming convention	Example
1	Bigint	inb	inb<Variabelename>	inbPatientId
2	Binary	bin	bin<Variabelename>	binMessage
3	Bit	bit	bit<Variabelename>	bitOK
4	Char	chr	chr<Variabelename>	chrPatientname
5	Cursor	cur	cur<Variabelename>	curField
6	Datetime	dtm	dtm<Variabelename>	dtmInsertDate
7	Float	flt	flt<Variabelename>	fltXvalue
8	Integer	int	int<Variabelename>	intPatientID
9	Money	mny	mny<Variabelename>	mnyTotalAmount
10	Nchar	chrn	chrn<Variabelename>	chrnPatientname
11	Ntext	txtn	txtn<Variabelename>	txtMessage
12	Numeric of decimal	dec	dec<Variabelename>	decAmount
13	Nvarchar	chvn	chvn<Variabelename>	chvnPatientname
14	Real	rea	rea<Variabelename>	reaAmount
15	Smalldatetime	dts	dts<Variabelename>	dtsInsertDate
16	Smallint	ins	ins<Variabelename>	insSubcategorieID
17	Smallmoney	mns	mns<Variabelename>	mnsAmount
18	Sql_variant	var	var<Variabelename>	varFreevalue
19	Table variable	tav	tav<name>	tavCustomers
20	Text	txt	txt<Variabelename>	txtReport
21	Timestamp	tsp	tsp<Variabelename>	tspDeleteDate
22	Tinyint	iny	iny<Variabelename>	inyCategorieID
23	Uniqueidentifier	guid	guid<Variabelename>	guidKeu
24	Varbinary	biv	biv<Variabelename>	bivMessage
25	Varchar	chv	chv<Variabelename>	chvPatientname

Greetz
Hennie