This is puzzling me - in SQL Server 2005/2008, I can specify the default file group which is then used for all data tables. I can also specify another filegroup for all BLOB type fields. But I cannot figure out if and how I can specify a default filegroup for indices..... My setup usually is: * primary filegroup - nothing but the syste...
Most examples dealing with table partitions, create the table on the partition scheme. For example: create table SomeTable ( Id int not null , DueDate DateTime not null ) on MyPartitionScheme(DueDate) Where MyPartitionScheme is a predefined partition scheme. If I have a table that already exists, possibly with data with it. Can...
Is there an easy way to combine two SQL Server 2005 databases into a single database? I want to take database 'A' and database 'B' and merge them both entirely into database 'C'. Thanks! ...
Let's say I'm getting a large (2 million rows?) amount of data that's supposed to be static and unchanging. Supposed to be. And this data gets republished monthly. What methods are available to 1) be aware of what data points have changed from month to month and 2) consume the data given a point in time? Solution 1) Naively save every s...
Does it have any sense (except of server side validation XML/schema/dtd) to store XML in XML type instead of text/varchar/ntext? I'm not planning to do any XML manipulation on database side. Purpose of my investigation is to decrease database size. Can a using an XML data type for untyped XML for the purpose? Which other pros and cons i...
Ok so I am writing a report against a third party database which is in sql server 2005. For the most part its normalized except for one field in one table. They have a table of users (which includes groups.) This table has a UserID field (PK), a IsGroup field (bit) , a members field (text) this members field has a comma separated list...
When you delete an obsolete object from a project/solution, what's the best way to transfer this change into production? Does deploying have an option for this? EDIT: This is a database project in Visual Studio 2008, deploying to SQL Server 2005. Thanks ...
I'm using Linq2Entity for most of my database operations. However for database creation, table creation and initial data insertion I use plain SQL files. Therefore I need both an SqlConnection and an EntityConnection. Unfortunately the Entity Framework starts complaining that the Sql Server is not listening on the other end of the pipe. ...
What is the correct proceedure when deploying an ASP.net MVC application? I am using the built in forms based authentication and deploying using the publish function in VS2008 but when deploying it doesn't seem to deploy the ASPNETDB to the server and I end up with errors like An error occurred during the execution of the SQL file 'Ins...
Hey, I need to create a stored procedure that upon exceution checks if any new rows have been added to a table within the past 12 hours. If not, an warning email must be sent to a recipient. I have the procedures for sending the email, but the problem is the query itself. I imagine I'd have to make an sql command that uses current date...
What's the recommended way to create a type in MSSQL 2005 with a default value and a check constraint to be used in several columns? I don't want to add a check constraint and a default value for every single column. My first idea was to create a default and a rule to bind to this type but books online clearly states that we should avo...
I have a Dictionary which I'd like to use like an IN clause within a SQL query. I have a Linq-To-SQL query where I would like to use this Dictionary's Keys to check fields in the rows for the query. E.g. bool result = DataContext.Table.Any(res => MyDictionary.ContainsKey(res.field1)); In effect this is similar to exists(sel...
I was once told that instead of having to have my host register a SQL Server database file + user/password it is possible instead to put my MDB file into my website's APP_DATA and connect to it directly. How do I do this? I tried using the SqlConnection's connection wizard and set DataSource : Microsoft SQL Server Database File (SqlCli...
I'm not clear about the interaction between database schema changes and differential backups on sql 2005. Lets say I do a Full backup right right now. Then I perform some schema changes. Then I do a diff backup. What happens? Do I need to create another FULL backup? Are my schema changes and any data in those new schema bits included...
Let's say you're running an UPDATE statement on a table, but the information you're putting into this base table is from some other auxiliary table. Normally, you would JOIN the data and not expect the rows in the UPDATE statement's FROM clause to multiply, maintaining that one new row maps to one old row in the base table. But I was wo...
Due to requirements of a piece of software I am planning on installing, I have to change the Identity the IIS Application Pool for SQL Reporting Services 2005 is running under. Unfortunately, I am unable to find any information of the ramifications of doing so. I was hoping someone here would be able to point me to some information o...
I have a situation in which I have several related/cascaded tables. Lets say all 1-to-many relationships cascading down table1, table2, table3, table4, etc. What I have are default rows in the tables. They start with 1 record in table1 and have 1 or more related records in other tables. What I am looking for is an easy way to replica...
EDIT: I have added "Slug" column to address performance issues on specific record selection. I have following columns in my table. Id Int - Primary key (identity, clustered by default) Slug varchar(100) ... EntryDate DateTime Most of the time, I'm ordering the select statement by EntryDate like below. Select T.Id, T.Slug, ..., T.Ent...
I used an Update statement inside a procedure to update a table which has an update trigger. Does the update statement complete after the trigger completes or what? ...
I have been wondering what the best practices or ramifications are in setting up the PK in a M2M table in SQL Server. For instance: I have 2 tables Users Roles I am making a new table UserRole Which has 2 fields RoleId & UserID now should I create a UserRoleID as the PK and make UserID and RoleID the FKs make the PK UserID ...