All Questions
Tagged with sql sql-server-2008
26,072
questions
2181
votes
47
answers
3.7m
views
How to return only the Date from a SQL Server DateTime datatype
SELECT GETDATE()
Returns: 2008-09-22 15:24:13.790
I want that date part without the time part: 2008-09-22 00:00:00.000
How can I get that?
- 34.4k
1739
votes
14
answers
2.1m
views
How can I delete using INNER JOIN with SQL Server?
I want to delete using INNER JOIN in SQL Server 2008.
But I get this error:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'INNER'.
My code:
DELETE
FROM WorkRecord2
INNER ...
- 18k
1139
votes
41
answers
445k
views
Parameterize an SQL IN clause
How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count ...
- 63.7k
1042
votes
24
answers
3.1m
views
How can I get column names from a table in SQL Server?
I want to query the name of all columns of a table. I found how to do this in:
Oracle
MySQL
PostgreSQL
But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?
- 26k
1011
votes
25
answers
1.7m
views
Reset identity seed after deleting records in SQL Server
I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each ...
- 18.5k
803
votes
11
answers
1.5m
views
Rename column SQL Server 2008
I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL.
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
This statement doesn't work.
- 9,512
625
votes
7
answers
1.6m
views
How to turn IDENTITY_INSERT on and off using SQL Server 2008?
Why am I getting an error doing an insert when IDENTITY_INSERT is set to OFF?
How do I turn it on properly in SQL Server 2008? Is it by using SQL Server Management Studio?
I have run this query:
...
- 29k
613
votes
9
answers
1.3m
views
UPDATE and REPLACE part of a string
I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column.
Example of Table:
ID Value
---------------------------------
1 c:...
- 6,693
585
votes
29
answers
1.4m
views
Delete duplicate rows keeping the first row
How can I delete duplicate rows where no unique row id exists?
My table is
col1 col2 col3 col4 col5 col6 col7
john 1 1 1 1 1 1
john 1 1 1 1 1 1
sally 2 2 2 ...
- 10.9k
496
votes
5
answers
1.4m
views
Efficiently convert rows to columns in sql server
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records.
This is my example:
Id
Value
ColumnName
1
John
...
- 5,003
463
votes
19
answers
1.2m
views
How to avoid the "divide by zero" error in SQL?
I have this error message:
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
What is the best way to write SQL code so that I will never see this error message again?
I could ...
- 13.5k
429
votes
35
answers
1.4m
views
How can I select the first day of a month in SQL?
How can one select the first day of the month of a given DateTime variable?
I know it's quite easy to do using this kind of code:
select CAST(CAST(YEAR(@mydate) AS VARCHAR(4))
+ '/' + CAST(MONTH(@...
- 32k
429
votes
9
answers
802k
views
Select statement to find duplicates on certain fields
Can you help me with SQL statements to find duplicates on multiple fields?
For example, in pseudo code:
select count(field1,field2,field3)
from table
where the combination of field1, field2, ...
- 8,030
422
votes
15
answers
814k
views
SQL Server - Return value after INSERT
I'm trying to get a the key-value back after an INSERT-statement.
Example:
I've got a table with the attributes name and id. id is a generated value.
INSERT INTO table (name) VALUES('bob');
...
- 12.1k
413
votes
14
answers
1.1m
views
SQL Server Insert if not exists
I want to insert data into my table, but insert only data that doesn't already exist in my database.
Here is my code:
ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
(@_DE nvarchar(50),
@_ASSUNTO ...
- 4,261
406
votes
8
answers
1.5m
views
How do I see active SQL Server connections?
I am using SQL Server 2008 Enterprise. I want to see any active SQL Server connections, and the related information of all the connections, like from which IP address, connect to which database or ...
- 45.2k
378
votes
10
answers
373k
views
Connect different Windows User in SQL Server Management Studio (2005 or later)
Is there a way in SQL Server Management Studio 2005 (or later) to change the Windows Authentication user (as you could in SQL Server 2000 and older)?
This is the general connection properties dialog(...
- 3,781
355
votes
3
answers
411k
views
Can I use multiple "with"?
Just for example:
With DependencedIncidents AS
(
SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
(
SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A
CROSS ...
- 33.2k
337
votes
7
answers
1.0m
views
SQL Server SELECT INTO @variable?
I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine:
CREATE PROCEDURE [dbo].[Item_AddItem]
@CustomerId uniqueidentifier,
@Description ...
- 9,837
331
votes
5
answers
445k
views
SET versus SELECT when assigning variables?
What are the differences between the SET and SELECT statements when assigning variables in T-SQL?
- 5,703
323
votes
15
answers
801k
views
Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).
Sometimes when I run my build I get this error:
ALTER DATABASE failed because a ...
- 80.3k
307
votes
9
answers
444k
views
How can I group by date time column without taking time into consideration
I have a bunch of product orders and I'm trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration?
3/8/...
- 19.8k
294
votes
21
answers
794k
views
How to update Identity Column in SQL Server?
I have SQL Server database and I want to change the identity column because it started
with a big number 10010 and it's related with another table, now I have 200 records and I want to fix this issue ...
- 4,941
293
votes
3
answers
290k
views
How to write UPDATE SQL with Table alias in SQL Server 2008?
I have a very basic UPDATE SQL -
UPDATE HOLD_TABLE Q SET Q.TITLE = 'TEST' WHERE Q.ID = 101;
This query runs fine in Oracle, Derby, MySQL - but it fails in SQL server 2008
with following error:
"...
- 5,139
276
votes
6
answers
1.3m
views
How to create a table from select query result in SQL Server 2008 [duplicate]
I want to create a table from select query result in SQL Server, I tried
create table temp AS select.....
but I got an error
Incorrect syntax near the keyword 'AS'
- 3,010
272
votes
16
answers
923k
views
sql query to return differences between two tables
I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the ...
- 12.2k
271
votes
12
answers
621k
views
Check if a row exists, otherwise insert
I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction.
This is for a booking system, so it must be atomic ...
Robert
264
votes
12
answers
996k
views
SQL-script: How to write ALTER statements to set Primary key on an existing table?
I have an existing table called Person which already has existing 1000 rows of data. In this table I have 5 fields:
personId
Pname
PMid
Pdescription
Pamt
The table already has an existing compound-...
- 3,789
253
votes
10
answers
574k
views
Changing the maximum length of a varchar column?
I'm trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I've dropped and re-created tables before but I've never been exposed to the alter statement ...
- 9,700
252
votes
11
answers
1.5m
views
How do I do multiple CASE WHEN conditions using SQL Server 2008?
What I'm trying to do is use more than one CASE WHEN condition for the same column.
Here is my code for the query:
SELECT Url='',
p.ArtNo,
p.[Description],
p....
- 4,332
238
votes
6
answers
341k
views
OPTION (RECOMPILE) is Always Faster; Why?
I encountered an odd situation where appending OPTION (RECOMPILE) to my query causes it to run in half a second, while omitting it causes the query to take well over five minutes.
This is the case ...
- 6,087
236
votes
18
answers
469k
views
How can I group time by hour or by 10 minutes?
Like when I do
SELECT [Date]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY [Date]
How can I specify the group period? I'm using MS SQL 2008.
I've tried this, both with % 10 and / 10.
SELECT MIN([...
- 33.2k
231
votes
17
answers
1.4m
views
How to select the last record of a table in SQL?
This is a sample code to select all records from a table. Can someone show me how to select the last record of that table?
select * from table
When I use: SELECT * FROM TABLE ORDER BY ID DESC LIMIT
...
- 10.1k
231
votes
5
answers
180k
views
is of a type that is invalid for use as a key column in an index
I have an error at
Column 'key' in table 'misc_info' is of a type that is invalid for use as a key column in an index.
where key is a nvarchar(max). A quick google search finds that the maximum ...
user34537
228
votes
7
answers
403k
views
How to Select Every Row Where Column Value is NOT Distinct
I need to run a select statement that returns all rows where the value of a column is not distinct (e.g. EmailAddress).
For example, if the table looks like below:
CustomerName EmailAddress
...
- 4,817
226
votes
10
answers
252k
views
Saving results with headers in SQL Server Management Studio
I am using SQL Server Management Studio.
I wish to save the results of a query to an excel file.
I choose "save as" and then save to CSV file which I can open in excel. All good except I am ...
- 17.3k
224
votes
11
answers
738k
views
Copy data into another table
How to copy/append data from one table into another table with same schema in SQL Server?
Edit:
let's say there is a query
select *
into table1
from table2
where 1=1
which creates table1 with ...
- 7,747
224
votes
8
answers
313k
views
Modify table: How to change 'Allow Nulls' attribute from not null to allow null
How to change one attribute in a table using T-SQL to allow nulls (not null --> null)? Alter table maybe?
- 2,527
213
votes
9
answers
225k
views
Multi-statement Table Valued Function vs Inline Table Valued Function
A few examples to show, just incase:
Inline Table Valued
CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
FROM Sales.Sales a INNER JOIN ...
- 6,700
210
votes
42
answers
412k
views
How to generate a range of numbers between two numbers?
I have two numbers as input from the user, like for example 1000 and 1050.
How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:
1000
1001
...
- 2,458
208
votes
13
answers
775k
views
Select SQL Server database size
how can i query my sql server to only get the size of database?
I used this :
use "MY_DB"
exec sp_spaceused
I got this :
database_name database_size unallocated space
My_DB 17899....
- 3,424
206
votes
17
answers
459k
views
How can I find out what FOREIGN KEY constraint references a table in SQL Server?
I am trying to drop a table but getting the following message:
Msg 3726, Level 16, State 1, Line 3
Could not drop object 'dbo.UserProfile' because it is referenced by a FOREIGN KEY constraint.
...
user1943020
206
votes
6
answers
170k
views
How to find current transaction level?
How do you find current database's transaction level on SQL Server?
- 36.5k
205
votes
11
answers
452k
views
CROSS JOIN vs INNER JOIN in SQL
What is the difference between CROSS JOIN and INNER JOIN?
CROSS JOIN:
SELECT
Movies.CustomerID, Movies.Movie, Customers.Age,
Customers.Gender, Customers.[Education Level],
Customers.[...
- 5,251
204
votes
21
answers
983k
views
SQL Server SELECT LAST N Rows
This is a known question but the best solution I've found is something like:
SELECT TOP N *
FROM MyTable
ORDER BY Id DESC
I've a table with lots of rows. It is not a posibility to use that query ...
- 16.5k
203
votes
18
answers
406k
views
SQL-Server: Error - Exclusive access could not be obtained because the database is in use
I am actually trying to make a script (in Sql Server 2008) to restore one database from one backup file. I made the following code and I am getting an error -
Msg 3101, Level 16, State 1, Line 3
...
- 9,616
203
votes
5
answers
145k
views
How to check existence of user-define table type in SQL Server 2008?
I have a user-defined table type. I want to check it's existence before editing in a patch using OBJECT_ID(name, type) function.
What type from the enumeration should be passed for user-defined table ...
- 99.3k
197
votes
8
answers
383k
views
How to create composite primary key in SQL Server 2008
I want to create tables in SQL Server 2008, but I don't know how to create composite primary key. How can I achieve this?
- 3,653
191
votes
27
answers
669k
views
T-SQL split string
I have a SQL Server 2008 R2 column containing a string which I need to split by a comma. I have seen many answers on StackOverflow but none of them works in R2. I have made sure I have select ...
- 2,157
187
votes
10
answers
243k
views
SQL "between" not inclusive
I have a query like this:
SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01' AND '2013-05-01'
But this gives no results even though there is data on the 1st.
created_at looks like 2013-05-01 ...
- 5,373