Questions tagged [sql-server-2008]

Use this tag for questions specific to the 2008 version of Microsoft's SQL Server.

Extended support for SQL Server 2008 and 2008 R2 have ended on July 9, 2019. That means the end of regular security updates. (source: SQL Server 2008 End of Support.)

If you're still using SQL Server 2008, you should seroiusly consider upgrading your SQL Server to a supported version.

SQL Server 2008 (codename Katmai, version number 10.00) was released on August 6 2008, and aims to make data management self-tuning, self organizing, and self-maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 also includes support for structured and semi-structured data, including digital media formats for pictures, audio, video, and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them. According to Paul Flessner, Senior Vice President, Server Applications, Microsoft Corp., SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc as well as perform search, query, analysis, sharing, and synchronization across all data types.

Other new data types include specialized date and time types and a Spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the new FILESTREAM data type, which can be used to reference any file stored on the file system. Structured data and metadata about the file is stored in the SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL; doing the latter accesses the file data as a BLOB. Backing up and restoring the database backs up or restores the referenced files as well. SQL Server 2008 also natively supports hierarchical data and includes T-SQL constructs to directly deal with them, without using recursive queries.

The Full-text search functionality has been integrated with the database engine. According to a Microsoft technical article, this simplifies management and improves performance. Spatial data will be stored in two types. A "Flat Earth" (GEOMETRY or planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane. A "Round Earth" data type (GEOGRAPHY) uses an ellipsoidal model in which the Earth is defined as a single continuous entity which does not suffer from the singularities such as the international dateline, poles, or map projection zone "edges". Approximately 70 methods are available to represent spatial operations for the Open Geospatial Consortium Simple Features for SQL, Version 1.1.

SQL Server includes better compression features, which also helps in improving scalability. It enhanced the indexing algorithms and introduced the notion of filtered indexes. It also includes Resource Governor that allows reserving resources for certain users or workflows. It also includes capabilities for transparent encryption of data (TDE) as well as compression of backups. SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data Model. SQL Server Reporting Services will gain charting capabilities from the integration of the data visualization products from Dundas Data Visualization, Inc., which was acquired by Microsoft. On the management side, SQL Server 2008 includes the Declarative Management Framework which allows configuring policies and constraints, on the entire database or certain tables, declaratively. The version of SQL Server Management Studio included with SQL Server 2008 supports IntelliSense for SQL queries against a SQL Server 2008 Database Engine. SQL Server 2008 also makes the databases available via Windows PowerShell providers and management functionality available as Cmdlets, so that the server and all the running instances can be managed from Windows PowerShell.


53837 questions
46 answers

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
  • 33,851
  • 14
  • 47
  • 48
32 answers

How to check if a column exists in a SQL Server table

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND…
  • 21,919
  • 3
  • 20
  • 23
14 answers

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 JOIN Employee ON…
  • 17,733
  • 7
  • 30
  • 45
41 answers

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 DESC In this query, the number of arguments could be…
Jeff Atwood
  • 63,320
  • 48
  • 150
  • 153
23 answers

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)?
  • 25,407
  • 33
  • 108
  • 151
25 answers

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 table has to have a primary key and identity…
  • 17,975
  • 51
  • 129
  • 180
15 answers

Sql Server 'Saving changes is not permitted' error ► Prevent saving changes that require table re-creation

When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error: Saving changes is not permitted. The change you have made requires the following table to be dropped…
  • 9,705
  • 4
  • 18
  • 12
11 answers

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
  • 9,442
  • 6
  • 25
  • 33
7 answers

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: SET IDENTITY_INSERT Database. dbo. Baskets ON Then…
  • 28,539
  • 63
  • 155
  • 235
9 answers

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:\temp\123\abc\111 2 …
  • 6,543
  • 4
  • 20
  • 23
28 answers

How to delete duplicate rows in SQL Server?

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 2 2 2 sally 2 2 2 2 2 2 I…
  • 10,569
  • 17
  • 55
  • 97
8 answers

Why use the INCLUDE clause when creating an index?

While studying for the 70-433 exam I noticed you can create a covering index in one of the following two ways. CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3) -- OR -- CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3) The INCLUDE clause is…
  • 12,404
  • 7
  • 33
  • 28
5 answers

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…
  • 4,913
  • 3
  • 13
  • 8
14 answers

How to set a default value for an existing column

This isn't working in SQL Server 2008: ALTER TABLE Employee ALTER COLUMN CityBorn SET DEFAULT 'SANDNES' The error is: Incorrect syntax near the keyword 'SET'. What am I doing wrong?
Nakul Chaudhary
  • 25,572
  • 15
  • 44
  • 47
19 answers

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 do either of the following: Add a where clause so…
Henrik Staun Poulsen
  • 13,154
  • 4
  • 23
  • 26
2 3
99 100