All Questions

Tagged with
Filter by
Sorted by
Tagged with
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?
Eddie Groves's user avatar
  • 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 ...
nettoon493's user avatar
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 ...
Jeff Atwood's user avatar
  • 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)?
odiseh's user avatar
  • 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 ...
xorpower's user avatar
  • 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.
Serhio g. Lazin's user avatar
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: ...
Beginner's user avatar
  • 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:...
aston_zh's user avatar
  • 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 ...
Fearghal's user avatar
  • 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 ...
tbag's user avatar
  • 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 ...
Henrik Staun Poulsen's user avatar
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(@...
Brann's user avatar
  • 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, ...
JOE SKEET's user avatar
  • 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'); ...
melbic's user avatar
  • 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 ...
Francisco Carvalho's user avatar
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 ...
George2's user avatar
  • 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(...
Matt P.'s user avatar
  • 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 ...
cnd's user avatar
  • 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 ...
bleepzter's user avatar
  • 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?
juur's user avatar
  • 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 ...
Vaccano's user avatar
  • 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/...
The Muffin Man's user avatar
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 ...
Abdulsalam Elsharif's user avatar
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: "...
javauser71's user avatar
  • 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'
yogesh9239's user avatar
  • 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 ...
Casey's user avatar
  • 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 ...
user avatar
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-...
jay's user avatar
  • 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 ...
Michael A's user avatar
  • 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....
Nils Anders's user avatar
  • 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 ...
Chad Decker's user avatar
  • 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([...
cnd's user avatar
  • 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 ...
Tassisto's user avatar
  • 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 ...
user avatar
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 ...
Grasshopper's user avatar
  • 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 ...
dublintech's user avatar
  • 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 ...
Rajaram Shelar's user avatar
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?
CrazyMouse's user avatar
  • 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 ...
AndrewC's user avatar
  • 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 ...
user3211705's user avatar
  • 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....
Adeel ASIF's user avatar
  • 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. ...
user avatar
206 votes
6 answers
170k views

How to find current transaction level?

How do you find current database's transaction level on SQL Server?
dance2die's user avatar
  • 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.[...
PriceCheaperton's user avatar
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 ...
Diego's user avatar
  • 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 ...
Steam's user avatar
  • 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 ...
abatishchev's user avatar
  • 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?
Ganesamoorthy's user avatar
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 ...
Lee Grindon's user avatar
  • 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 ...
JBurace's user avatar
  • 5,373

1
2 3 4 5
522