Difference between login and user in sql server
CREATE USER [anc] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[anc] GO what is the difference between the user and the login. i am not clear as to y do we create these kind of users?? ...
sql-server
How can i qualify fields in bcp.exe with quotes
I can specify a comma field seperator in BCP by using the options -c -t , How can i enclose the fields in quaotations (") ...
Avoiding race-condition when manually implementing IDENTITY-like increment for a SQL Server DB column
I'm building an ASP.NET MVC 2 site that uses LINQ to SQL. In one of the places where my site accesses the DB, I think a race condition is possible. DB Architecture Here are some of the columns of the relevant DB table, named Revisions: RevisionID - bigint, IDENTITY, PK PostID - bigint, FK to PK of Posts table EditNumber - int Revis...
SQL Server query degrades over time from 0 to 60msec
Hi guys. I need some help in explaining this behavior in SQL Server 2008. I have a simple c++ program that is running a query in a loop. Here is the pseudocode of the program. myTempid = 0; while (1) { execQuery(select a.id from a,b,c,d where a.id = b.id and b.id = c.id and c.i...
How to perform a select in a single statement?
I have a Sql Server 2005 table with following data: idHearing is the primary key (identity), idCase is a foreign key that may duplicate. StartDate and StartTime specify the date and time of the event - both fields are of DateTime type (for whatever reason these are separate fields). All StartDate data has a time of 12:00:00. All St...
SQL Server book recommendation
Hello Experts, I'm just a beginner in development field.Looking for SQL Server books. Please tell me which is good book to study from. Any suggestion and pointer will be really helpful . thanks ...
SQL 2008: getting rows with a join to an XML field?
Hi there, Not sure if this question makes for some poor performance down the track, but seems to at least feel "a better way" right now.. What I am trying to do is this: I have a table called CONTACTS which amongst other things has a primary key field called memberID I also have an XML field which contains the ID's of your friends (fo...
Did not recognize the correct database after WCF project host in IIS 7
A WCF project consist of all the logic behind a desktop application (WPF) and it works well when I run it on VS 2008 with WCF selfhost. The desktop application consume services expose by the WCF and desktop application users have to login to the app using his username and password. WCF is connect with SQL Server 2008 database through LI...
TSQL how do you iterate through rows while parsing them?
Sorry for the poor question wording I wasn't sure how to describe this. I want to iterate through every row in a table and while doing so, extract a column, parse the varchar that is in it and depending on what it finds insert rows into another table. Something along the lines of this: DECLARE @string varchar(max); foreach row in (selec...
SQL Server: What would be the appropriate way to define the relationships (borders) between states?
Suppose you have to build a DB tables that depicts states and their borders between each other. Let's say the States table would look like: States: Name(PK), Size, etc... What would be the appropriate way to define the relationships (borders) between states? I came up with three alternatives - Defining a Borders table with primary k...
Optimizing query and/or data model for calendar app
Our calendar application represents an appointment domain as: Appointment ID (PK) StartDateTime EndDateTime ... AppointmentRole AppointmentID (FK) PersonOrGroupID (FK) /* joins to a person/group, outside the scope of this question */ Role ... Appointment has a 1 to many relationship with AppointmentRoles. Ea...
Prevent duplicate rows being inserted
I'm trying to use an SQL insert statement to migrate rows from a table in one database to a table in a different database. The statement works until I add a unique index on the destination table and at that point I'm struggling to get the insert statement to be able to exclude the duplicates. Here's what I though should work: INSERT INT...
How does SQL Server store a composite primary key?
The limit of my understanding how a database stores a primary key is a btree based on the clustered key with each node storing the rest of the columns. I have not updated this understanding since university last century and would love to know how SQL Server stores a composite primary key compared to a single primary key. Can anyone hel...
SQL Server Performance: Non-clustered Index + INCLUDE columns vs. Clustered Index - equivalent?
Hello SQL Server engine experts; please share with us a bit of your insight... As I understand it, INCLUDE columns on a non-clustered index allow additional, non-key data to be stored with the index pages. I am well aware of the performance benefits a clustered index has over a non-clustered index simply due to the 1 fewer step the eng...
Performance of returning entire tables containing blog text as opposed to selecting specific columns
I think this is a pretty common scenario: I have a webpage that's returning links and excerpts to the 10 most recent blog entries. If I just queried the entire table, I could use my ORM mapped object, but I'd be downloading all the blog text. If I restricted the query to just the columns that I need, I'd be defining another class that'...
How to save print variable into another variable in SQL Server?
Hi Here is my script grab from net declare @col varchar(255), @cmd varchar(max) DECLARE getinfo cursor for SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID WHERE t.Name = 'tblDeductions' AND c.name like 'fld%name' OPEN getinfo FETCH NEXT FROM getinfo into @col WHILE @@FETCH_ST...
LIKE clause vs. IN clause--which is better?
Which one is better using condition in where statement or in join statement? Can anyone please help? ...
Master database DB STARTUP problem
I have a SQL Server 2008 database and I have a problem with this database that I don't understand. The steps that caused the problems are: I ran a SQL query to update a table called authors from another table called authorAff The authors table is 123,385,300 records and the authorsAff table is 139,036,077 The query took about 7 days e...
How to determine of an SQL table row contains values other than NULL
This is the problem: I have a table with unknown number of columns. all the columns are real. Assuming there is only one row in that table, I need a method to determine if there is a value other than NULL in that table/row. I don't know the number of columns nor their name at run-time (and don't want to use c cursor) SQL Server 2005 ...
SQL Server queries case sensitivity
hi I have this database: abcDEF ABCdef abcdef if I write: select * from MyTbl where A='ABCdef' how to get: ABCdef and how to get: abcDEF ABCdef abcdef Thanks in advance forgot to write - sqlCE ...