EDB On WM. Do Do Sort Orders Generate Index Tables?
Aug 6, 2007
Hi,
I am developing database support for my application and I am using EDB. I would like to use sort orders to make the code easier but I was wondering if sort orders generate index tables. I mean, do they only sort the records during addition or they also generate index tables to speed up queries. In other words, do they make the database file bigger?
In the MSDN documentation I can not find anything on this.
Hi There i have two windows 2000 servers which are both running SQL and i would like to restore a backup from one server to the other. Which in my opinion should be an easy task but when i go into the restore option and point it at the file i would like to restore i get the follwoing error
"The database you are attempting to restore was backed up under a different sort order ID (52) than the one you are currently using on this server (50) and at least one of them is a non binary sort order. Backup or restore operation operation terminating abnormally."
The server that i am trying to restore to already has databases on this so i cannot just reinstall SQL and change the sort order not that id know how to do that but this is what i have read.
Is htere anyway that i can put insome script for the database to fix this ???
Hello all- Maybe someone has some insight on this... I currently have some hash values (SHA512 through Enterprise Library) that I'm trying to compare to data in our database. When I sort the values on the pipeline (DT_STR, 1252) with a Sort and compare them against values coming out of an OLE DB Source (SQL_Latin_1_General_CP1_CI_AS) with a Merge Join, any hash that has a '/' in it is treated as inequal to the one in the database.
For even more fun, when I change the sort/merge join to a Lookup, everything (seems) to check out. Is the 1252 code page not the same as SQL_Latin_1_General_CP1_CI_AS? Any other reasons this might be happening?
Just wanted to check. I've got a SQL 7.0 box on NT4 with about 40 databases (each about 500Mb-1Gb) which I want to move to SQL2000.
The main problem is, the 7.0 box was built with a sort order not officially supported by the finance software that uses the databases, so I want to build the SQL2000 box with the default sort orders and character sets.
I've done this in a test environment by copying the .mdf files accross and attaching them to the SQL2000 box, and this all seems to work. But is this advisable, and will there be any problems?
Other than the error log is there an easy way to find the sort order and chracter set of an installed SQL Server. Also after finding the numbers is there a good reference to tell you what these numbers mean. Thanks
I have a unique index based on the following columns:ProjectID (int)MaterialCatalogID (int)Material catalogues are pretty much static but projects are dynamic andpeople are most likely to be working on the latest project so wouldusing a descending sort on the ProjectID in the index gain anyperformance?
In my Report, i have a grouping on project and Drill down on Project Cloumn only,now i need to implement interactive sort on rest of the columns,i implemented But it seems not working. can any one help me ..................... It is urgent requirement for me. rgds, kousik.
This script generates a format file for every table in a database.
It's set up for fixed width files, not too common in this world...but they can easily be modified.
Also the do not at this time handle text or image columns.
Any hints/advice here would be great.
I'll post it when I figure it out.
happy bcping..
SELECT FORMAT_CARD FROM ( SELECT '7.0' AS FORMAT_CARD , TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' UNION ALL SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD , c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND TABLE_TYPE = 'BASE TABLE' GROUP BY c.TABLE_NAME UNION ALL SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9) + CONVERT(varchar(5), CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH WHEN DATA_TYPE = 'int' THEN 14 WHEN DATA_TYPE = 'smallint' THEN 7 WHEN DATA_TYPE = 'tinyint' THEN 3 WHEN DATA_TYPE = 'bit' THEN 1 ELSE 26 END) + CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD , c.TABLE_NAME, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME AND c.table_schema = t.table_schema AND TABLE_TYPE = 'BASE TABLE' WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.Columns i WHERE i.TABLE_NAME = c.TABLE_NAME) UNION ALL SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5), CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH WHEN DATA_TYPE = 'int' THEN 14 WHEN DATA_TYPE = 'smallint' THEN 7 WHEN DATA_TYPE = 'tinyint' THEN 3 WHEN DATA_TYPE = 'bit' THEN 1 ELSE 26 END) + char(9)+'" "'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD , c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND TABLE_TYPE = 'BASE TABLE' WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.Columns i WHERE i.TABLE_NAME = c.TABLE_NAME) )AS XXX ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:
From Server A: sp_addlinkedserver B INSERT dbo.ResultsTable SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID sp_dropserver B
In SSIS one of the possible solutions is to use a package which does the following: OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT
The problem with this approach is that it's extremely slow for large datafiles (50M records each)
Questions:
1) In the procedure above could the SORT step be avoided? 2) Is there another approach to run cross-servers JOIN in SSIS?
I have an Infopath form to edit the values in the NotifyMembers table. I want to list the email addresses alphabetically when the query returns data to the form. Unfortunately, I use EmailAddressID as an int in the table, so I need to join? the NotifyMember table with the EMailAddress table, which has a column EMail which is the address. Both have EmailAddressID as common fields.
I can't get the Shape command (edit SQL in the Datasource) to be happy with any syntax that I have tried.
For (non-working) example:
select "GroupCode","EmailAddressID","DelBit" from "dbo"."NotifyMembers" as "NotifyMembers" Join "dbo"."NotifyMembers"."EmailAddressID" On "dbo"."EmailAddress"."EmailAddressID" = "dbo"."NotifyMembers"."EmailAddressID" order by "dbo"."EmailAddress"."Email"
i'm not able to generate a sql script with the data in the tables! using sql express. i need this script to ganerate the same database with its datas in a new sql server. thanks in advance
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
Please help, i am really really struggling for a logic to handle 100's of reports we have via button click from asp.net webform. in the button click i am constructing the url : ************************************************************************************************************** http://localhost/reportserver?/MyReports/StatusReport&UserID=1&ContractID=1&subcode=null *************************************************************************************************************
I have a table would like to maintain the parameters required for the chosen report: when the user chooses from list box on the webform for StatusReport, immedeately it fetches the parameters related to Statusreport and gets everything which is stored with a space in between for each parameter, for this report i have 3 parameters: UserID ContractID subcode
now how can i construct the string based on the above parameters , i am using vb.net as code behind for my webform(asp.net)
please any ideas will help me achieve the logic. please help thank you all very much.
I am trying to load the data from one data base to another using the Task->Generate Script->tables (data only). So I saved the script of the data and when tried to run the script to the destination DB, getting the below error when ran the script.
Error: Visual Studio has encountered an exception. This may be caused by an extension.You can get more information by running the application together with the /log parameter on the command line, and then examining the file'C:UsersKK67AppDataRoamingMicrosoftAppEnv10.0ActivityLog.xm'. When tried opening the folder using the above path... don't see anything for AppData folder from that path.
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC, case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC, case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC, case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end, case when @SortColumn = 'FileDataID' then FileDataID end, case when @SortColumn = 'Field2' then FileDesc when @SortColumn = 'VersionNotes' then VersionNotes end
case when @SortOrder = 'DESC' then DESC when @SortOrder = 'ASC' then ASC end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
I am trying to index through the columns of MyTable so I can do the same work on all columns. I know how to get the column names from MyTable but when I use @MyColName in the SELECT statement to get MyTable Column 0 Row values I get a table with the column name in each row cell. I can't get the syntax correct to return the value in each cell for that column. This is a extremely simplified example !!!!!!DECLARE @MyColName nvarchar(30) --Get the MyTable Column 0 NameSELECT @MyColName = Col_Name(Object_ID('MyTable'), 0) --Display the MyTable Column 0 Row valuesSELECT @MyColName FROM MyTable --This is the syntax I can not get correct
This database sits on SQL server 2000. The problem is with a copy of the production database. My concern is that if it is a hardware issue then I have more problems on my hand. Bellow are the errors. teh first one is from the SQL server log the second is using DBCC CHECKDB
Point to note : this a VMware server.
I/O error (torn page) detected during read at offset 0x0000000093a000 in file 'E:Program FilesMicrosoft SQL ServerMSSQLdataPPFILESSQLT.mdf'..
*-*-*-*-
DBCC results for 'ppfilessqlt'. DBCC results for 'sysobjects'. There are 156 rows in 3 pages for object 'sysobjects'. DBCC results for 'sysindexes'. There are 181 rows in 12 pages for object 'sysindexes'. DBCC results for 'syscolumns'. There are 819 rows in 14 pages for object 'syscolumns'. DBCC results for 'systypes'. There are 26 rows in 1 pages for object 'systypes'. DBCC results for 'syscomments'. There are 173 rows in 15 pages for object 'syscomments'. DBCC results for 'sysfiles1'. There are 2 rows in 1 pages for object 'sysfiles1'. DBCC results for 'syspermissions'. There are 83 rows in 1 pages for object 'syspermissions'. DBCC results for 'sysusers'. There are 21 rows in 1 pages for object 'sysusers'. DBCC results for 'sysproperties'. There are 1350 rows in 69 pages for object 'sysproperties'. DBCC results for 'sysdepends'. There are 508 rows in 3 pages for object 'sysdepends'. DBCC results for 'sysreferences'. There are 0 rows in 1 pages for object 'sysreferences'. DBCC results for 'sysfulltextcatalogs'. There are 0 rows in 0 pages for object 'sysfulltextcatalogs'. DBCC results for 'sysfulltextnotify'. There are 0 rows in 0 pages for object 'sysfulltextnotify'. DBCC results for 'sysfilegroups'. There are 1 rows in 1 pages for object 'sysfilegroups'. DBCC results for 'PPFILES_L#MGP100'. There are 9 rows in 1 pages for object 'PPFILES_L#MGP100'. DBCC results for 'PPFILES_L#OHP100'. There are 65966 rows in 1627 pages for object 'PPFILES_L#OHP100'. DBCC results for 'MTDPromoSale'. There are 0 rows in 0 pages for object 'MTDPromoSale'. DBCC results for 'PPFILES_L#PGP100'. There are 1028 rows in 21 pages for object 'PPFILES_L#PGP100'. DBCC results for 'PPFILES_L#PRP100'. There are 2772 rows in 65 pages for object 'PPFILES_L#PRP100'. DBCC results for 'CalendarMaster'. There are 6 rows in 1 pages for object 'CalendarMaster'. DBCC results for 'PPFILES_L#RLP100'. There are 73 rows in 2 pages for object 'PPFILES_L#RLP100'. DBCC results for '~TMPCLP15451'. There are 480 rows in 10 pages for object '~TMPCLP15451'. DBCC results for 'PPFILES_L#POP100'. There are 57 rows in 1 pages for object 'PPFILES_L#POP100'. DBCC results for 'PPFILES_L#RUP100'. There are 4 rows in 1 pages for object 'PPFILES_L#RUP100'. DBCC results for 'PPFILES_L#SLP150'. There are 0 rows in 1 pages for object 'PPFILES_L#SLP150'. DBCC results for 'PPFILES_L#VIP100'. There are 0 rows in 0 pages for object 'PPFILES_L#VIP100'. DBCC results for 'Divisions'. There are 10 rows in 1 pages for object 'Divisions'. DBCC results for 'Brand'. There are 121 rows in 2 pages for object 'Brand'. DBCC results for 'UserList'. There are 20 rows in 1 pages for object 'UserList'. DBCC results for 'ItemProposal_H'. There are 18 rows in 1 pages for object 'ItemProposal_H'. DBCC results for 'MTDSale'. There are 138 rows in 2 pages for object 'MTDSale'. DBCC results for 'SystemSetup'. There are 199 rows in 2 pages for object 'SystemSetup'. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1726629194, index ID 0: Page (1:1181) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 1726629194, index ID 0, page (1:1181). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. DBCC results for 'PPFILES_L#SYP100'. There are 188 rows in 2 pages for object 'PPFILES_L#SYP100'. DBCC results for 'PPFILES_L#CSP100'. There are 11130 rows in 521 pages for object 'PPFILES_L#CSP100'. DBCC results for 'Product'. There are 10640 rows in 133 pages for object 'Product'. DBCC results for 'ProductSetup'. There are 15974 rows in 215 pages for object 'ProductSetup'. DBCC results for 'PPFILES_L#ODP100'. There are 1557837 rows in 16352 pages for object 'PPFILES_L#ODP100'. CHECKDB found 0 allocation errors and 2 consistency errors in table 'PPFILES_L#ODP100' (object ID 1726629194). DBCC results for 'ItemProposal_H_temp'. There are 6 rows in 1 pages for object 'ItemProposal_H_temp'. DBCC results for 'ItemProposal_D_Temp'. There are 102 rows in 2 pages for object 'ItemProposal_D_Temp'. DBCC results for 'Messages'. There are 9 rows in 1 pages for object 'Messages'. DBCC results for 'ItemProposal_D'. There are 543 rows in 8 pages for object 'ItemProposal_D'. DBCC results for 'dtproperties'. There are 7 rows in 1 pages for object 'dtproperties'. DBCC results for 'AnimationCode'. There are 128 rows in 2 pages for object 'AnimationCode'. DBCC results for 'L#RLP100_Back'. There are 23235 rows in 664 pages for object 'L#RLP100_Back'. DBCC results for 'Calendar'. There are 81 rows in 1 pages for object 'Calendar'. DBCC results for 'L#CSP100_Back'. There are 486 rows in 35 pages for object 'L#CSP100_Back'. CHECKDB found 0 allocation errors and 2 consistency errors in database 'PPFILESSQLT'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PPFILESSQLT ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It's been always said that it is best to put index on commonly joined fields in the table. But putting too much index on the table would cause the table to be slow on insert and update.
My question is, how do you deal with your fields that uses look up tables? Like for example for these fields
Those fields don't come a big part in the table, though when I query the table I always join them with their respective primary table to get their respective text value. Do I still need to put Index & FK relationships to these fields?
What fields are normally good candidates for index or fk relationships?
SQL SERVER 2000System let's you alter the system tables and add indexes. However, it won'tlet you drop the index afterward.Anybody know how to drop an index on a system table?Thanks,Kevin
In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!
This I can't get to work:
DECLARE @TableName nvarchar(100) SET @TableName = 'Account'
USE database; GO ALTER INDEX ALL ON @TableName REBUILD GO
What is the best procedure/sequence to reduce some tables containing large number of rows of a SQL 2000 server? The idea is first to check which tables grow extremely fast (all statistics, user or log tables), reduce the table according to the number of months the user wishes to keep in the table. As a second step backup remaining rows of table as txt files on harddisk (using DTS), UPDATE STATISTICS and re-indexing reduced table. Run DTS Package every month once (delete oldest month and backup newest month) and do the same as above to keep size of tables adequate. What is a fast way to reduce number of rows of a large table - the following example produces an error (timeout expired) of my ADO connection when executing: SET @str = 'DELETE FROM ' + @ProcessTable + ' WHERE ' + @SelectedColumn + ' < DATEADD (m,' +' -' + @KeepMonthsInDatabase + ', + GETDATE())' EXEC (@str) Adding ConnectionTimout = 0 did not help unfortunately.
What is the best way to re-index the table just maintained?
There is a index: CustomerInfo_1 with keys: customerId, EnteryDate DESC I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored? I tried system tables such as sysindexes and sysindexkeys tables. But could not find it. Any help in this regard will be truly appreciated.
There is a index: CustomerInfo_1 with keys: customerId, EnteryDate DESC I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored? I tried system tables such as sysindexes and sysindexkeys tables. But could not find it. Any help in this regard will be truly appreciated.
Is there any reason why only heaps can have forwarded records while tables with clustered index have to resort to page split when a page becomes saturated with data due to an update or insertion? Both have drawbacks, forwarded records add another level of indirection, leading to increased I/O overhead, while page split may cause further split in the parent pages which can cascade even further. May be that the cascading is a one-time only cost to be incurred, while the indirection overhead repeats everytime there is a query. Then why not go for page split in heap also? The additional overhead to be had for adding another pointer to the IAM page is also one-time.
get a list of indexes on all tables in all dbs on a SQL server.
If the index property to allow page locks is off, then turn it on, re-index and turn it off again. My problem is: i want to use ' Use <db>' statement in the middle of my script but it is not working.I tried using dynamic SQL with
set @cmd='use '+ @dbname exec (@cmd) But this is not working. Can we use 'use' statement in the middle of a script? If not what is the alternative?
My script looks as follows:
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
declare @Index varchar(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd='use '+@Database
print (@cmd)
exec (@cmd)
declare IndexCursor CURSOR for select name from sys.indexes where object_id=object_id(@Table)
open IndexCursor
fetch next from IndexCursor into @Index
print (@table)
--select name from sys.indexes where object_id=object_id(@Table)
print (@index)
WHILE @@FETCH_STATUS = 0
begin
if (INDEXPROPERTY(OBJECT_ID(@Table),@Index,'IsPageLockDisallowed')=1)
begin
print (@Index + ' page locking off')
-- SET @cmd='ALTER INDEX '+@Index +' ON '+@Table+' SET (ALLOW_PAGE_LOCKS = ON) reorganize
-- ALTER INDEX '+@Index +' ON '+@Table+' SET (ALLOW_PAGE_LOCKS = OFF)'
end
else
begin
print (@Index + ' page locking on')
-- SET @cmd='ALTER INDEX '+@Index +' ON '+@Table+' reorganize'
