How would you represent a temporal many-to-many relation in SQL? Under non-temporal circumstances one would use a junction table (aka link/bridge/map) to connect the two sides. Is adding temporal tracking as simple as including a ValidStart and ValidEnd columns on the junction table? If you have done this, what issues (if any) did you r...
30 million distinct phrases, not documents, ranging from one word to a 10 word sentence and I need to support word/phrase searching. Basically what where contains(phrase, "'book' or 'stack overflow'") offers. I have an instance of SQL Server 2005 (32 bit, 4 proc, 4gb) going against several full text catalogs and performance is awful for...
Hi, further to these two questions, is there a way to disable the messages that may get sent along with the resultset in SQL2008? (please note this is nothing to do with the ANSI_WARNINGS setting. Or NOCOUNT.) Thanks for any help. Edit: It's not a problem with compatibility settings or table owners. And it's nothing to do with NOCOUNT...
I'm helping implement a SQL Server 2008 database and I have several columns that I'd like to default to the next number in a sequence, but still be able to insert numbers by hand into it at will. In PostgreSQL this is done with the SERIAL datatype and a sequence. Unfortunately those things do not exist in SQL Server. Here's what I know W...
I need to remove diagramming support tables, stored procs, views, etc from SQL Servrer using TSQL script. Is there such a script available? SQL 2005 and 2008. ...
I've just installed Microsoft SQL Server Express 2008 (with Reporting Services). My installation is side by side with SQL Server 2005. So far creating reports and testing them in the development environment has worked fine, however I can't get the web server or report manager working. In the Reporting Services Configuration Manager, i...
I have a list of records in my database and each record is associated with a zip code. What is the "best-practice" for querying all the records in my database to find all entries that are within n miles of another zip code? Each zip code has a lat/long associated with it in the database so I know I'll have to use that. However, I can'...
I've built a very simple chatroom-like ASP.NET application which displays current Online/Offline users: I have a Table with a DateTime column used as a TimeStamp. Every time a user causes a Postback or similar Get event, I update the TimeStamp. I want to, on the server, create a periodic process of some sort that I can use to check how ...
Hi, I'm installing a new SQL Server 2008 server and are having some problems getting any usable information regarding different collations. I have searched SQL Server BOL and google'ed for an answer but can't seem to be able to find any usable information. What is the difference between the Windows Collation "Finnish_Swedish_100" and ...
In my application people will upload resumes and they can update the resumes I am storing. The resumes use a file stream in a database. That is working fine. But now I want to track versions of the uploaded resumes to find the latest and previous resume. What is the best way to do this? ...
I'm encountering a very strange problem concerning what appears to be a corrupt index of some kind. Not corrupt in the sense that dbcc checkdb will pick it up, but corrupt in the sense that it has rows that it shouldn't have. I have two tables, TableA and TableB. For the purposes of my application, some rows are considered functionally ...
Now that SQL Server 2008 has full text search built in. I'm looking to use it to power my website's search. I'm also looking at using ADO.NET entity framework for my ORM but I was wondering how do you do full text search (FTS) with Linq to ADO.NET entity framework? Is there any support in ADO.NET entity framework or am I stuck using the...
I would like to have a chart in SQL Server Reporting Services 2008 show the top X values and sum/group the rest as other. Is this possible without doing the grouping in T-SQL? Example: A query brings back the total sales from 50 US states. I want to show the five states that have the most sales and group the rest as "All Other States" ...
I have some data in my user database that I would prefer to be encrypted. Most of the data will need to be decrypted when requested, but there are also passwords that can stay encrypted (in the old days we would use pwdcompare but I believe this is obsolete now). I have followed the steps here, so I have now successfully encrypted my da...
We have a setup where we have multiple instances of an application - one instance for each customer. We call a lot of our reports via URL, passing in parameters on the querystring. Early on, when we were on 2005, we identified a problem with this: I could change my querystring a bit and get into someone else's data. We got around the...
I have two tables, one an import table, the other a FK constraint on the table the import table will eventually be put into. In the import table a user can provide a list of semicolon separated values that correspond to values in the 2nd table. So we're looking at something like this: TABLE 1 ID | Column1 1 | A; B; C; D TABLE 2 ID ...
Has anyone implemented replication between SQL Server 2008 and MSDE2000? Does the merge replication work between these two databases? ...
sorry for asking this, but i'm runnin' out of ideas i have this table: [id] [pid] [vid] 1 4 6844 1 5 6743 2 3 855 2 6 888 ... how to i query this eg.table to get the following result: [id] [pid] [vid] 1 5 6743 2 6 888 i want to get the ...
When you have a table with a Geometry type field (or Geography), you can call the Reduce() function to have the shapes reduced in number of vertices. Works very nicely. However, when sevral of the polygons are adjacent (they share parts of their borders), the reduced polygons do not necessarily do so. Example The SQL batch: CREATE TA...
I'm looking into running a single instance web application on SQL Server 2008. What are the specific advantages that the Enterprise version has over the Standard version in terms of speed. I'm not looking at the management and reporting side of things, which I understand Enterprise is much better at, but just at raw speed point of view...