Truncate Extra 0's after 2 Decimal Points

Recently I have faced a problem and spent few hours to find its solution.

Problem : Using Database in financial applications. Sometimes there are requirements for division of columns. Like finding Daily transaction summery or finding Percentage of some values etc. During this division SQL SERVER returns data with '0' as postfix even if you apply round() function. But there are requirements to show data till  2 decimal places.

Solution : There are multiple solutions for this problem but most easy way i have found is to declare a column or convert the column to a float value. It will return decimal values with respect to Round() function.

Example : Find the Attached Image as an Example

I have Declared a table variable and I have column data types as Money and Float respectively. During insertion of Data i have inserted different values in both column which have decimal values up to 2 and more. As you can see during Insertion to a fixed value DecimalDivide column returns data up to 2 decimal and many 0 Zeros leading to the value. But FloatDivide column returns data only to 2 decimal places.

SQL SERVER 2008 Indexed Views Limitations

I have recently faced a problem for creating Materialized (Indexed) View using Microsoft SQL SERVER 2008.
I have found some limitations for creating Indexed view.

1-  Cross Database  Query Not Allowed

You cannot create a SCHEMABINDING view calling a query from other database. You can see error in attached image

2 - * Cannot be Used in Query of an Indexed View

3- Schema name must be mentioned before creating view
you can create a normal view without mention table schema if it is calling from same database. but while creating Indexed view you have to mention schema name before table name as shown in figure.

4 - All Views must be Schema bound if used in your Indexed View
If you are creating a view using another view as your From clause then you have to re-create your already created view as SCHEMABINDING view. As you can see in figure if you want to bind your CrossDbView2 as Indexed then you have to create CrossDBView as Indexed.

5 - Indexed View cannot be Altered. You cannot drop a View if it is using in any other Indexed View.

I have found these limitations while creating Indexed view. If you have more information please share with me.

Common uses of SQL SERVER Undocumented Stored Procedure sp_MSforeachtable

I have recently faced completed some data migration tasks for a project. During different problems I have found some uses of sp_MSforeachtable store procedure to make migration steps easy. I have found following problems

Problems:

1. During data migration I have faced “Constraint” Problem. Like Foreign Key or Unique Key

2. I have multiple triggers that fires on data insertion and I need to shut down all before migration

3. I need to know space used for each table and there are more than 1000 tables in database

4. During 2^nd Cycle for migration I need to delete data from all existing tables and then reinsert again and this will increase code and time to insert delete statement before each table

and lot of others as well. Then I find one solution for all my problems and am sp_MSforeachtable procedure. This is an undocumented procedure in master database. sp_MSforeachtable can be used to loop through all the tables in your database. I have used following as my solutions. There are some other uses of this procedures and  mentioned below

1. Disable all constraints of all tables in your database

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

After migration enable aa constraints again

EXEC sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL"

2. Disable/Enable all Triggers on all tables in your database

EXEC sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

EXEC sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL"

3. Space Used of all tables in your database

EXEC sp_MSforeachtable "EXEC sp_spaceused ?"

4. Return Number of Rows for each table

EXEC sp_MSforeachtable "Select ''?'',count(*) as TotalRows from ?"

5. Rebuild all indexes for all tables

EXEC sp_MSforeachtable "print '?' DBCC DBREINDEX('?','',80)"

Use alter index instead of reindex in future versions of sql server.

6. Delete data from all tables of your database

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

EXEC sp_MSforeachtable "DELETE FROM ?"

EXEC sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL"

7. Re claim identity for all columns

EXEC sp_MSforeachtable "

IF OBJECTPROPERTY(OBJECT_ID(''?'',''TableHasIdentity'')  = 1

DBCC CHECKIDENT(''?'', RESEED, 0 ) "

You need to replace schema name before each table to execute this query

8- Reclaim space from dropped variable length columns in tables or indexed views.

EXEC sp_MSforeachtable "DBCC CLEANTABLE(0, ''?'') WITH NO_INFOMSGS;" not tested

9- Update statistics

EXEC sp_MSforeachtable "Update Statistics ? WITH ALL"

10- Generating Insert statement for all tables

EXEC sp_MSforeachtable "exec up_generate_insert ?"

Where sp_generate_insert is a user created procedure that return insert statements for parameter value

I have gathered information from my experience and other blogs like

http://bytes.com/topic/sql-server/answers/492005-alter-table-nocheck-constraint-still-some-dependencies

and

suprotim agarwals blog: for common uses of the undocumented Stored Procedure