I have a SQL Server 2000 database. When I run the following command select * from sysindexkeys This display the appropriate records. I then do a DBCC command for the sysindexkeys. It doesn't display anything. Strange there is no page having the sysindexkeys records. Then how the query display the list of records. ...
I am having 2 .bak files for the same database in MSSQL Server. One is the test snapshot and the other is the production snapshot. The test one is about 500 MB and the production one is about 10 GB. This is because the Test one has many tables truncated. When I restore them in sql server a .mdf file is created at \Microsoft SQL Server\MS...
I am running an archive script which deletes rows from a large (~50m record DB) based on the date they were entered. The date field is the clustered index on the table, and thus what I'm applying my conditional statement to. I am running this delete in a while loop, trying anything from 1000 to 100,000 records in a batch. Regardless of ...
There are 4 functions provided in SQL Server 2008 for full-text search: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE How to use it them with Linq ? ...
To eliminate the potential problems with triggers, what are some of the alternatives one may use to get the same functionality of reacting to an event fired on a INSERT action? I have a database that needs to have some additional values added on insert. The INSERT is controlled by compiled code and cannot be changed. EXAMPLE: The progr...
I'm updating tables with millions of records and I need to be as efficient as possible. Is there a point at which adding more criteria to the where clause will actually hurt rather than help? For example, if know I want to set a column to 3 I could use this query: update mytable set col = 3 Or I could update the record only if it's d...
Why not integrated Soundex into Full-Text search in SQL server? Those functions good for SQL query and easy to use for traditional ASP.NET app, but it is very hard to use it on Silverlight app because SL app use Linq to get data and Stored Procedure is very hard to use. (Complex datatype for result set is not supported yet). How to res...
Hi, I'm planning on moving a database from C: drive to E: drive because the database is growing and the C: drive does not have enough capacity to handle that. I wonder if I need to changes anything in the connection on the web.config page in order to access the database. The database still has the same name, is still on the same serv...
Say if I have a table with 2 column - ID, money. I want to do a query to find out how many ID have money less than 100, 200, 300, ..., 10000 How should I do this in SQL Server? Will I need to use variables? ...
Hi, Ive been working all day on optimizing a SQL Server 2000 db table with ~9million rows. My only db experience has been with tables with a few hundred rows, so I've never really had to deal with optimization. I am doing selects and updates based on a 21 digit number. Using an indexed char(21) type, the queries take more then 2 secon...
I'm looking to write a few small tools for managing table content for an existing SQL Server 2005 DB. I have a few dozen tables of reference content for an application that is deployed on many client databases (often for different schema versions) and I want to build a few python scripts to export, import, diff, and merge this content ac...
Hi Guys, I'm looking for some advice to how to get the indexes running better on this query... SQL Server 2005/8 some customers have 5 some 8... SELECT sales.ChainStoreId, sales.CashBoxId, dbo.DateOnly2(sales.BonDate), MAX(sales.BonDate), SUM(sales.SumPrice) FROM [BACK_CDM_CLEAN_BOL...
Hi, i have a database in sql 2000. When i open the system table sysindexkeys, I see many records. However, when i use dbcc ind command for this table, i do not get any information regarding the table. i also used DBCC TRACEON (3604) heres what i did : - Declare @DBID Int, @TableID Int Select @DBID = db_id(), @TableID = object_id('sys...
Hi all, I want to create a table trigger for insert and update. How can I get the values of the current record that is inserted/updated? ...
Hi all expert, I would like to know what is the maximum size of varchar in SQL Server 2000. While I was googling somewhere its written 8000 characters and somewhere its written 8060 bytes. which one is correct??? Pls confirm me.. Thanks, Kumar ...
I get an error: Subqueries are not allowed in this context. Only scalar expressions are allowed. Why are subqueries not allowed? I'm just trying to move some value over to another row in the same database. Is there another way of expression this quickly? (it's just a one-time operation...) INSERT INTO Html_Content (pageid, ht...
I need to write a stored procedure to insert a row into a table. The table has an Identity column called ID. My question is should I return the value and if so, how? As far as I can see, I could return it as the return value, I could return it as an out parameter, or I could do nothing with it. What would a DB programmer do as a...
Hi all, I am looking for a way to call a stored procedure for each record of a select statement. SELECT @SomeIds = ( SELECT spro.Id FROM SomeTable as spro INNER JOIN [Address] addr ON addr.Id = spro.Id INNER JOIN City cty ON cty.CityId = addr.CityId WHERE cty.CityId = @CityId ) WHILE @SomeIds IS NOT NULL BEGIN ...
I´m using SQL Server and programming a blogtool. I want to store texts longer than 4000 chars and a want it to be fast searchable. Is ntext the right datatype to use? ...
I have just installed Sql Server Compact Edition. To my surprise, we can'nt use stored procedure in sql server CE. Do I have any alternative of Stored Procedure in Sql Server CE. I am strongly obseesed with stored procedure, I can'nt think of an application without stored procedures. Please help, Thanks in advance. Edit: Can I use Ma...