You have an overnight job which is triggered by the arrival of some external data. You keep a record of the date and time that the overnight job is run. Someone wants to know what the average start time of the job is. You can’t use the average function on dates and times in T-SQL so you need to write some custom code to calculate the average time the job starts.
First, you need to identify the point in time that you want the calculation to consider the start of a new day. For a job which runs during the day could be 0 – indicating midnight. For an overnight job you would probably want to consider the possibility that the job could be started the next morning. In that situation you might choose 18 – indicating 6pm as the start of a new day.
Then, in the calculation itself:
- identify the number of seconds since the day division
- get the average of number of seconds
- add to a date time and extract the time portion
The four principles of defensive programming are
- Detail your assumptions
- Test rigorously against user cases
- Layout code in modules
- Reuse code
I recently discovered that the space character in ascii has an evil twin. Like all evil twins it looks exactly the same but it does evil…
Here they are in SSMS:
They look the same – why is there another space character in ascii?
The second space is a non-breaking space character – so any program displaying the text should not separate a line at that point.
Why is ascii 160 evil?
The other day I was trying to extract some data into xml but I kept getting an illegal xml character error.
The source data had a single ascii 160 character in it, but all the other spaces were ascii 32. Even when you find the row of data which has the non breaking space it still requires a lot of work to actually find where the character is!
How can you solve this error?
The best way is to make sure that the data going into your system is cleaned of the non breaking space – if you intend to extract it in XML.
Another possibility is to replace non breaking spaces with breaking spaces in the output.
Or, if the system that will receive the XML is happy with it, you could change the encoding to UTF-16.
There is no contest. Catalog views are the winner.
They were introduced in SQL Server 2005 and they, along with Dynamic Management Objects, are the recommended way of accessing SQL Server metadata. However, compatibility views are still available in SQL Server and it is useful to be aware of the differences.
|Compatibility Views||Catalog Views|
|Exist in sys schema||Exist in sys schema|
Prefixed by ‘sys’
For example: select * from sysdatabases
Or: select * from sys.sysdatabases
Both access the same sysdatabases compatibility view
NOT prefixed by ‘sys’
For example: select * from sys.databases
Many to many relationships frequently occur in OLTP databases. For instance, consider a retail system which stores the details of products in one table. Each product could belong to one or more product types; equally, each product type could apply to more than one product. This is often represented in an OLTP database as:
However, in a data warehouse this structure is likely to cause performance problems.
Assuming Products has it’s own dimension in the DW. Here are some alternatives for storing the associated Product Type data:
|Proposed structure||Issues to condsider|
|Add a bitmap column to the Product dimension||
|Add an xml column to the Product dimension||
|Add a bridging table to a dimension||
|Add a bridging table to a fact table||