zaterdag 30 april 2011

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

zaterdag 23 april 2011

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

zondag 17 april 2011

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

zaterdag 16 april 2011

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.SubjectPrefixNaming conventionExample
1Databasedbdb<name>dbODS
2Jobjbjb<Jobname>jbSSIS_RunLoadPackage
3Linked Serverlsls<linkedservername>lsServer01
4Trigger(Delete)tdtd<name>tdAppointment
5Trigger(Insert)titi<name>tiCustomer
6Trigger(Update)tutu<name>tuAppointment


SQL Server Database objects

No.SubjectPrefixNaming conventionExample
1Aggregate functionafaf<Name>afTotalSales
2Assembliesasass<Name>assDetermineIPs
3Attribuut<name>PatientName
4Database data file(s)data#<database>_data#dbAdventureWorks_data1
5Database log filelog<database>_logdbAdventureWorks_log
6Defaultdfdf<name>dfDate
7Filegroupfgfg<name>fgdbAdventureWorks_Index
fgdbAdventureWorks_Data
fgdbAdventureWorks_Log
8Filestreamfsfs<name>fsInvoiceDoc
9Foreign keyFKx_FKx_<sourcetable>_<goaltable>_<fieldname(s)># 
10Index (not unique, non clustered)IDX_IDX_<name>_<column>
11Index (unique, non clustered)UDX_UDX_<name>_<column>
12Index (not unique, clustered)CIX_CIX_<name>_<column>
13Index (unique, clustered)CUX_CUX_<name>_<column>
14Primary keyPK_PK_<name>PK_KlantID
15Scalar valued functionscfscf<name>scfDetermineNumberOfCustomers
16Schemaschsch<name>schOrders
17Stored procedureuspusp<projectname>_<name>uspArchive_DetermineNumberOfOrders
18Synoniemssynsyn<name>
19Table Valued functiontbltbl<name>tblCustomers
20Table Valued functiontvftvf<name>tvfDeterminecustomers
21Temporary Table#tmp#tmp<name>#tmpPatient25Years
22Typeuddudd<name>uddPostcode
23Typetptp<name>tpPosInt
24Viewvwvw<name>vwGetOpenOrders

T-SQL

No.SubjectPrefixNaming conventionExample
1Bigintinbinb<Variabelename>inbPatientId
2Binarybinbin<Variabelename>binMessage
3Bitbitbit<Variabelename>bitOK
4Charchrchr<Variabelename>chrPatientname
5Cursorcurcur<Variabelename>curField
6Datetimedtmdtm<Variabelename>dtmInsertDate
7Floatfltflt<Variabelename>fltXvalue
8Integerintint<Variabelename>intPatientID
9Moneymnymny<Variabelename>mnyTotalAmount
10Ncharchrnchrn<Variabelename>chrnPatientname
11Ntexttxtntxtn<Variabelename>txtMessage
12Numeric of decimaldecdec<Variabelename>decAmount
13Nvarcharchvnchvn<Variabelename>chvnPatientname
14Realrearea<Variabelename>reaAmount
15Smalldatetimedtsdts<Variabelename>dtsInsertDate
16Smallintinsins<Variabelename>insSubcategorieID
17Smallmoneymnsmns<Variabelename>mnsAmount
18Sql_variantvarvar<Variabelename>varFreevalue
19Table variabletavtav<name>tavCustomers
20Texttxttxt<Variabelename>txtReport
21Timestamptsptsp<Variabelename>tspDeleteDate
22Tinyintinyiny<Variabelename>inyCategorieID
23Uniqueidentifierguidguid<Variabelename>guidKeu
24Varbinarybivbiv<Variabelename>bivMessage
25Varcharchvchv<Variabelename>chvPatientname


Greetz
Hennie