How much of a page is occupied by nvarchar(X)?
What are the storage requirements for nvarchar(X)? So for example, if the value in a column is much smaller than X, how much is actually stored in the database page? ...
sql-server
How to stop a cpu/time consuming running query on SQL Server!
Hi All Let's imagine I have several hundred queries running on my sql server. Now I want to protect my server against bad queries by stopping these queries automatically. How can I achieve this behavior in SQL Server 2005 without using profiler and KILL command? Thanks, Salman Shehbaz. ...
SQL Server: Event does not reference any tables (Tuning Advisor warning)
I have an application written in C# which uses Linq2SQL for communicating with the SQL Server. There are some queries that run a bit (very) slow, and I figure it probably need some indexes to speed things up. But I don't really know how to do that or on what or where or what I should or should not do. So I was thinking I could ask here,...
Why is this query faster with multiple selects rather than using between?
I have a table in Sql Server 2008 Express which contains 18 million records. The structure looks something like this (simplified): Id, GroupId, Value, Created Id is the primary key with a clustered index GroupId is a non-clustered index In this case, every 10 rows get a new groupId meaning that records 1-10 have GroupId 1, records 11-...
Tables created by default in user schema
In Sql Server 2008, when I create a table without schema prefix: create table mytable ( id int identify ) it usually ends up in the schema dbo, with name dbo.mytable. However, on one of our servers, the tabel ends up belonging to me: andomar.mytable Which configuration difference could explain this? ...
SqlParameter with Nullable value give error while ExecuteNonQuery?
I have an sql query that has a parameter that can be null in the database (Sql Server). The update method work fine until that user put a blank in the field, this produce a null value for the DataTime object (this object is nullable). The problem is when the dbCommand.ExecuteNonQuery();. Here is how I build the parameter for this field:...
Is there any sense in using byte sized column lengths?
MS SQL Server 2000 I've realized that in the last couple of tables I've created, I've been defining the varchar length of columns by power of 2. So, I've got one column that is length 1024 or 512 or 256. I realized though that depending on the length of the other columns in the table, this probably doesn't matter at all. Is there any...
Using openrowset to read an Excel file into a temp table; how do I reference that table?
I'm trying to write a stored procedure that will read an Excel file into a temp table, then massage some of the data in that table, then insert selected rows from that table into a permanent table. So, it starts like this: SET @SQL = "select * into #mytemptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+@file+";HD...
Can Biztalk use a remote MSQL server in a different domain without a trust relationship?
I have a remote MSQL instance that I want to use with biztalk. My machine running biztalk cannot join the same domain as the MSSQL instance, nor can we create a trust relationship between the two windows domains. Is there a way to configure biztalk to authenticate? Username/password (like any other DB would use) would be perfectly fin...
Id or [TableName]Id as primary key / entity identifier
Is it preferred to use "Id" as the column name for a primary key or "[TableName]Id" as a naming convention? Table: Account Primary Key: Id -- versus -- Table: Account Primary Key: AccountId It seems to be split about 50% / 50% in the implementations that I've seen. What are the advantages and disadvantages in each approac...
How can I attach to and debug a running SQL Server stored procedure?
I am investigating an odd error from a SQL Server 2005 stored procedure which I cannot reproduce by calling it directly from Management Studio. Therefore I'd like to be able to: set a breakpoint in the stored procedure wait for the procedure to be called externally and the breakpoint hit see the values of the passed-in parameters step...
Move SQL Server data in limited (1000 row) chunks
I'm writing a process that archives rows from a SQL Server table based on a datetime column. I want to move all the rows with a date before X, but the problem is that there are millions of rows for each date, so doing a BEGIN TRANSACTION...INSERT...DELETE...COMMIT for each date takes too long, and locks up the database for other users. ...
Most efficient and reliable way to get Unix files into SQL Server
I've got three files on AIX that I need to import into a SQL Server 2005 database. The files are created by an AIX script that I have control over. There are several options that I know will work, but I'd like to find out what others have done before and what works best. Here are the options I'm looking at. Have the AIX script sftp th...
how would you go about automating finding all the procs that a proc calls, along with their text?
Say I have a stored procedure, ProcA. It calls ProcB some of the time, ProcC all of the time and ProcD if it hits an error. ProcB calls ProcE every time. ProcC calls ProcF if it hits an error. How could I automate getting the text of ProcA along with the text of all the procs that it calls and regress all the way down the tree (A down ...
How can I find down-stream dependencies for Views?
On SQL Server 2005, you're supposed to be able to find dependencies in Manglement Studio by right-clicking an object and choosing "View Dependencies". The dialog gives you a choice of "Objects that depend on this" (what I'm calling down-stream) or "Objects that this depends on" (up-stream). Both directions seem to work adequately for Ta...
Will a SQL Server Job skip a scheduled run if it is already running?
The title pretty much says it all - if you schedule a SQL Server job to run every X number of minutes, and it does not finish the previous call before the # of minutes is up, will it skip the run since it is already running, or will it run two instances of the job doing the same steps? ...
Most approprieted index for short-lived columns
In my current project, some tables have a column named "changed", which indicates if the the current line had been changed since the last check. All the insert and update statements includes this column. Every hour, I run a schedulated task that queries all changed rows, do some stuff with those rows and then sets null to it's "changed"...
Change Owner of Database Diagram in SQL Server 2005
I need to change the owner of a database diagram in SQL Server 2005. Currently it is owned by me (domain\username.diagramName) and I would like to change it to be owned by dbo (dbo.diagramName). I thought I could use sp_changeobjectowner, but I believe that is only for tables, stored procedures, etc... I think this is pretty easy, I just...
SQL Server checkpoints
can anyone explain when SQL Server issues a checkpoint? ...
conditional unique constraint
I have a situation where i need to enforce a unique constraint on a set of columns, but only for one value of a column. so for example i have a table like Table(ID, Name, RecordStatus) Record status can only have a value 1 or 2 (active or deleted), and i want to create a unique constraint on ID, RecordStatus only when RecordStatus = 1,...