sql-server

What SqlDbType maps to varChar(max)?

What SqlDbType maps to varChar(max)? SqlDbType.VarChar says that it is limited to 8K, though that may be a documentation error. ...

SQL Server For Xml Path

I am returning an xml result for a query. Given two tables: **Foo** FooId int FooName varchar(10) **Bar** BarId int FooId int (FK) BarName varchar(10) I create a select statement like: SELECT FooId, FooName, ( SELECT BarId, FooId, BarName FROM Bar WHERE Bar.FooId = Foo.FooId AND...

Do 1 to 1 relations on db tables smell?

I have a table that has a bunch of fields. The fields can be broken into logical groups - like a job's project manager info. The groupings themselves aren't really entity candidates as they don't and shouldn't have their own PKs. For now, to group them, the fields have prefixes (PmFirstName for example) but I'm considering breaking the...

What does sp_reset_connection do?

sp_reset_connection seems to be called by SQL Server connection pooling, to ensure that connections reused from the pool have most of their settings reset. Does anyone know exactly what it does and doesn't do though? eg I see from this post that it doesn't reset the transaction isolation level ...

SQL Server: Table Variables with an Alias in a Delete From Statement

I want to delete rows from a SQL Server 2000/2005 table variable based on the presence of other rows in the same table (delete all 0 count rows if a non-0 count row exists with the same date). Here is a simplified example that should only delete the row added first: declare @O table ( Month datetime, ACount int NULL ) insert i...

problem with setting formula in column

Hi all, I have problem with setting the formula in particular field.I am using sql database have been added from->add new item->sql database.I have orders table with following field ordernum primary key orderdate smalldatetime, custemail varchar(50), subtotal money, salestax money, shipping money, total AS(subtotal+salestax+shipping) Ho...

Is there any size limit for the values returned from a Stored Procedure in SQL 2005?

I am trying to return Xml value from SQL Server 2005 using For Xml. Is there any limit in the size of xml returned from SQL Server? I will be using ExecuteXmlReader to get the value in my C# code. Also, is there any limit on the data that can hold in an XmlReader? Any thoughts... ...

Best-Practices for using schemas in SQL Server (2008)

I can see in the AdventureWorks database that different schemas are used to group tables. Why is this done (security, ...?) and are there best-practices I can find? thx, Lieven Cardoen ...

Saving / Caching Stored Procedure results for better performance? (SQL Server 2005)

I have a SP that has been worked on my 2 people now that still takes 2 minutes or more to run. Is there a way to have these pre run and stored in cache or somewhere else so when my client needs to look at this data in a web browser he doesn't want to hang himself or me? I am no where near a DBA so I am kind of at the mercy of who I hir...

Run db query (sql server 2005) with ajax. Is it possible?

I never worked with ajax. I need to know if it is possible to use ajax to run a query on the db (sql server 2005). My target is to run a query with refreshing the page. Have you got any ideas? ...

Should I use foreign keys?

Duplicate of What’s wrong with foreign keys? I use MS Sql Server with a large database about 4 GB data. I search around the web why I should use foreign keys. by now I only indexed the keys used to join tables. Performance is all fine, dataintegrety is no problem. Should I use foreign keys? Will I get even more performance with fo...

Find Locked Table in SQL Server

How can we find which table is locked in the database? Please, suggest. ...

partition function in SQL Server 2005

In MSDN about partition function from here, $PARTITION(Transact-SQL). I am confused about what the below sample is doing underlying. My understanding is, this SQL statement will iterate all rows in table Production.TransactionHistory, and since for all the rows which will mapping to the same partition, $PARTITION.TransactionRangePF1(Tra...

Copying data from a local database to a remote one

I'm writing a system at the moment that needs to copy data from a clients locally hosted SQL database to a hosted server database. Most of the data in the local database is copied to the live one, though optimisations are made to reduce the amount of actual data required to be sent. What is the best way of sending this data from one dat...

SQL Server: How do you return the column names from a table?

Hi, Please can someone show me how I would return the column names of a table using SQL server 2008? i.e. a table contains the columns id, name, address, country and I want to return these as data? Thank you ...

Backup and Restore MSSQL Server 2005 Database with FULL Diff from C#

Hi, as i said in the Title i wanna backup with full dif and restore it from c# .. Is there any examples for me? ...

Estimating database size

I was wondering what you do when developing a new application in terms of estimating database size. E.g. I am planning to launch a website, and I am having a hard time estimating what size I could expect my database to grow. I don't expect you to tell me what size my database will be, but I'd like to know if there are general principles...

How to convert a SQL subquery to a join

I have two tables with a 1:n relationship: "content" and "versioned-content-data" (for example, an article entity and all the versions created of that article). I would like to create a view that displays the top version of each "content". Currently I use this query (with a simple subquery): SELECT t1.id, t1.title, t1.con...

Migrate Access 2007 Database Application to SQL Server 2005 using SSMA - Issues...

I have managed to get SQL Server 2005 Express up and running on my computer Ok in order to do some testing before trying this in the "Real World". I have a fairly large MS Access 2007 Database application I need to migrate to SQL Server retaining the "Front End" as the user interface. (The app' is already a "split" database with a Fr...

Should the data access layer contain business logic?

I've seen a trend to move business logic out of the data access layer (stored procedures, LINQ, etc.) and into a business logic component layer (like C# objects). Is this considered the "right" way to do things these days? If so, does this mean that some database developer positions may be eliminated in favor of more middle-tier codi...