‘SQL Server 2000’ Archives
Example – Buliding a sample data warehouse using SQL Server
This post is about an example of populating fact tables in Funds data warehouse. All the data from dbo.tranhistory was extracted along with the foreign keys for four dimensions Managers, Offices, Funds, and Accounts. In writing this post we followed the Funds Database example in SQL Server OLAP Developer’s Guide by William C Amo published [...]
Data integrity example
An example of a data integrity mechanism is the parent and child relationship of related records. If a parent record owns one or more related child records all of the referential integrity processes are handled by the database itself, which automatically insures the accuracy and integrity of the data so that no child record can exist without a [...]
System Databases and Data in SQL Server
The purpose of this post is clarify the Master, Model, Temp db and MSDB differences. Many people who are experts in SqL Server sometimes overlook this information which is frequently asked in interviews. Microsoft® SQL Server™ 2000 systems have four system databases: master The master database records all of the system level [...]
Rules and Check constraint
Rules are a backward-compatibility feature that perform some of the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict the values in a column. CHECK constraints are also more concise than rules; there can only be one rule applied to a column, but multiple CHECK constraints can be applied. CHECK [...]
Difference between Index and Primary Key
This post is on very important topic - "Difference between Index and Key" and is explained in very clear and easy terms by Michael J. Hernandez in his book Database Design for Mer Mortals on page 61. We are emphasizing in this post that most of the IT books lacks clarity or makes concepts more complicated resultantly negative impact on the [...]
Temp table a useful tool in many reports
Reporting the payments received against shipment’s C&F value. In this example our client was facing an issue where shipments are made and C&F value amount is taken as Accounts Receivable, payments are received in 2 to 3 installments. Management wants to know how much is outstanding against each shipment C&F Invoice. In [...]
Real world example of usage of Case statement in SQL Server Stored Procedure
This post is our practical experience of using Case statement in Calculating monthly overtime amount. Issue was actually usage of different no hours for Overtime worked for different departments. In this case Security departments overtime was calculated based on 12 hours and in all other departments base was 8 hours. Salary was categorize [...]
Practicality of Floor function in SQL Server
We are writing this post to share our experience of usefulness of Floor function. We use the floor function in calculating product manufactured and raw material wastage in the process. The problem was to calculate how many units of a product can be manufactured from certain weight of a raw material. We were getting answer like 8.67 products [...]
Precision and Scale in Sql Server
We are writing this post because recently we received a query regarding automatic rounding in quantity column. The user was previously using MS Access and they upgraded to SQL Server database. Although solution is simple but sometimes it becomes difficult to resolve when confronting the problem. SQL Server Books online very well addressed the [...]
Correlated subquery
Correlated subquery (also known as a repeating subquery) is a subquery where selection criterion of inner query refers to values in the outer query. One of the restriction is that inner query cannot return more than one row matching the outer row; Example; USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper [...]
