InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 00:00:58 GMT20http://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxThe query will scan every row in the table unless the column happens to be the leading column in an index. Scanning large tables can impact performance. You may want to restore a backup of the database to a non-production location and do your research there. It would be advisable to test the fixes there as well, then run CHECKDB to ensure you have resolved the data issue, and then run those fixes in production.Fri, 22 Feb 2013 07:08:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxsome tables doesn't have any column with primary key.... if i run that script on production, it will cause any performance issues ??Fri, 22 Feb 2013 01:02:06 GMTLavanyasrihttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspx[quote][b]Lavanyasri (2/21/2013)[/b][hr]Thanks to all.I go through the link , now my issue with the decimal data typeSELECT col1 FROM table2WHERE col2 > 9999999999.99999 OR col1 < -9999999999.99999In this script col1 is primary key and col2 is the column from eroor.The same thing i checked for my issue,some tables doesn't have any column with primary key, and some tables have clumn primary key but its datatype is int not decimal .suggest me how to get data_purity values using the above script ..Col1 and col2 should be same data type (decimal)??Lavanyasri[/quote]The above code looks like a typo error what you need to check is [code="sql"]SELECT col1 FROM table2WHERE col2 > 9999999999.99999 OR col2 < -9999999999.99999[/code]Here Col1 is the primary key and col2 is the decimel you need to check.Col1(PK) doesnt need to be of the same datatype. You need this column to uniquely idenntify the corrupt rows,decide on what to do with them Thu, 21 Feb 2013 02:43:46 GMTjoeroshanhttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxThanks to all.I go through the link , now my issue with the decimal data typeSELECT col1 FROM table2WHERE col2 > 9999999999.99999 OR col1 < -9999999999.99999In this script col1 is primary key and col2 is the column from eroor.The same thing i checked for my issue,some tables doesn't have any column with primary key, and some tables have clumn primary key but its datatype is int not decimal .suggest me how to get data_purity values using the above script ..Col1 and col2 should be same data type (decimal)??LavanyasriThu, 21 Feb 2013 02:29:03 GMTLavanyasrihttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxLet's make that link an actual link:[u][url=http://support.microsoft.com/kb/923247]http://support.microsoft.com/kb/923247[/url][/u]The article Gail linked to above has a more "to the point" explanation of why these values can exist in your database, just search for the section named "Data Purity errors."Wed, 20 Feb 2013 08:44:16 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxSame link to the kb that I posted above.Wed, 20 Feb 2013 07:56:49 GMTraadeehttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxTake a look at this article. [url]http://www.sqlservercentral.com/articles/65804/[/url], there's a section on data purity errors and a like to a kb article that goes into detail on fixing themWed, 20 Feb 2013 07:25:55 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxPrimary key maybe.Something that helps you identify the row. You could also narrow down the problem if above isn't working for you.Select column from xxwhere column between 1 and 100.if it doesnt fail continue to between 100 and 1000 until you narrow it down.In the end you will find the row causing the problem and then run an updatestatment on that row that fixes the out of range value.Wed, 20 Feb 2013 07:17:31 GMTraadeehttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxwhat is col1 in this script??Wed, 20 Feb 2013 06:55:37 GMTLavanyasrihttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxBasically you have to identify the rows that contain the invalid value and update them with a valid value.Have a look at:Troubleshooting DBCC error 2570 in SQL Server 2005 and later versionshttp://support.microsoft.com/kb/923247Look for:Finding Rows with Invalid Values Using T-SQL QueriesDecimal and Numeric data type:--SELECT col1 FROM table2WHERE col2 > 9999999999.99999 OR col1 < -9999999999.99999--Keep in mind that you will need to adjust the values based on the precision and scale with which you have defined the decimal or numeric column. In the above example, the column was defined as col2 decimal(15,5).Wed, 20 Feb 2013 06:29:37 GMTraadeehttp://www.sqlservercentral.com/Forums/Topic1422021-1550-1.aspxHi TeamLast week we run dbcc command on few databases and found the below consistency errors in the database ,no allocation errors .Please see below error and provide u r inputs .The error due to decimal data type for column 'amount1' ,i checked the table and the column is likeamount1 (decimal(13,4),null) ...************************DBCC results for 'table1'.Msg 2570, Level 16, State 3, Line 1Page (12:3592881), slot 119 in object ID 6447247, index ID 1,partition ID 72057598160928768, alloc unit ID 72057598189436928 (type"In-row data"). Column "Amount1" value is out of range for data type"decimal". Update column to a legal value.Wed, 20 Feb 2013 04:19:55 GMTLavanyasri