All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

1
vote
0answers
705 views

Efficiently retrieve big amount of data based on date range

Table EVENTS TimeStamp |O.Name |Location |Source | 2008-11-12 |Pretorian|Európe |England | 2009-12-24 |Lex Rosa |Európe |Italy | 2010-01-01 |Nasdaq |USA |Whasington | ...
1
vote
1answer
196 views

Consolidating indexes

I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
1
vote
3answers
1k views

PreparedStatement caching size on Oracle and SQL Server

I understand that PreparedStatements are complied and cached by database servers. I would like to know if all the preparedStatements are complied and cached on the server, in which case what is the ...
1
vote
4answers
5k views

Advice for ETL from Oracle to SQL Server?

I'm building a data mart in SQL Server which needs to extract each month from an Oracle database. Currently this is done by exporting to flat files, then running all the inserts/updates in SQL ...
-6
votes
2answers
1k views

Versioning data in Databases [closed]

Is there any ready mechanism in Oracle, SQL Server, PostgreSql or OR framework (C#), which can version information in a database? By versioning informartion I mean that e.g. I have table Person (id, ...
1
vote
2answers
121 views

Problem with a surrogate key?

I am working with an application for students management. For the student's table I used a surrogate key (auto increment id). The problem I face with the surrogate key is when the user wants to show ...
2
votes
2answers
3k views

Only one NULL for one part of primary key

I have two date columns : "start date" and "finish date". I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one ...
1
vote
1answer
183 views

When to use identity increment?

Many times I face these three choices to work with IDs: Using an auto-increment identity column? Suggesting a new codification to use (eg, for students: first 4 position is the year when the student ...
3
votes
1answer
811 views

check constraint that has dynamic list

I need to create a check constraint, the problem i face is that the list of values accepted by the constraint changes in the future (ex:now "red,green and blue" in the next month "red,green,blue and ...
1
vote
1answer
264 views

Can I run a Report Server and a Mirror server from a working Log Shipping secondary instance? [closed]

Our setup: City A - Primary server for logshipping City B - Secondary server for logshipping Now there is a lot of data being updated from A - B. And the server in City B is underutilized, and it ...
0
votes
4answers
682 views

which one is the fastest way of moving database form local to live server in sql server

I am using SQL Server 2008, I have a big database of almost 500 MB. Now I wanted to upload or deploy this DB to the Live server. Currently I am generating scripts and then uploading them to live and ...
4
votes
2answers
2k views

How to find which edition the SQL Server installation media belongs to

Without installing SQL Server media 2008 and 2012, how can we know that the media belongs to a certain edition (like Developer, Standard or Enterprise)?
2
votes
1answer
114 views

Find a string in a SQL Server database

I have an application which has logged an error like Object <obj id> generated an exception. The application store all its information in a SQL Server database which contains 100+ tables. ...
1
vote
3answers
971 views

SQL Server xml performance

I have a table with 21 columns. The majority are numerical and the rest are nvarchar. I need to add 4 columns that will contain XML data for each row. The XML data for each row can be anywhere from ...
0
votes
1answer
229 views

Load balancing after clustering is failed in SQL Server cluster

This is what I have been asked today on my interview for a SQL Server DBA position: It is an active - active cluster configuration. The primary node is in NY and the other node is in Rio. The NY ...
2
votes
1answer
6k views

The Cluster service failed to bring clustered service or application 'SQL Server (MSSQLSERVER)' completely online or offline

SQL Server service restarted/stopped, which cause the production database down n find this errors in System log. ERROR5:The Cluster service failed to bring clustered service or application 'SQL ...
8
votes
1answer
62k views

Find all the queries executed recently on a database

[I am a beginner level T-SQL Programmer] [..and hopefully I am on the right stack exchange site] I would like to get a list of all the queries I executed (in the least, those I executed today since ...
2
votes
1answer
33 views

Is it possible to use two different drivers for sql server on the same machine. Say 2000 and 2008

I am trying to use two versions of sql server on the same machine. I have sql server 2000 driver installed on the system. Can I also install sql server 2008 driver on the same machine and use both ...
5
votes
1answer
530 views

SQL Server 2005 Replication

I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription. server 1 windows 2003 server 192.168.10.1 connected ...
0
votes
1answer
903 views

Backup failure in SQL Server 2005

I'm being unable to perform a backup in SQL Server 2005 installed on Windows 7 local machine. I was initially able to do so but as of now, every time I try I get the following message: Backup ...
9
votes
1answer
5k views

SSIS 2008 Package Configurations being ignored

With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time ...
7
votes
2answers
5k views

Is there a better option than Union All for multiple selects from the same row?

Example Schema: CREATE TABLE [dbo].[Base]( [ID] [int] IDENTITY(1,1) NOT NULL, [Option1ID] [int] NULL, [Option2ID] [int] NULL, [Option3ID] [int] NULL, [Option1Name] [varchar] NULL, [Option2Name] ...
7
votes
2answers
5k views

How might a corrupt partition in TempDB result in DBCC CHECKDB reporting no issue?

One of our SQL Servers reported the following error recently: DATE/TIME: 2/25/2013 9:15:14 PM DESCRIPTION: No catalog entry found for partition ID 9079262474267394048 in database 2. The ...
9
votes
4answers
833 views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 ...
4
votes
3answers
6k views

What is ERROR_STATE() in SQL Server and how it can be used?

I read that ERROR_STATE() can help to distinguish between different states/locations in the source code where same type of error can occur. But it is not really clear to me that how it can be useful. ...
13
votes
6answers
27k views

SQL Server 2012 slower than 2008

I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL ...
4
votes
3answers
4k views

Create procedure in else block on SQL Server

I have a SQL script in which I generate a database and its tables, stored procedures, views, etc. I have used following script to generate the database and all the things. I was able to generate ...
3
votes
1answer
4k views

Select the top 5 records and the specific column from each table in the same database

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select top 10 * from {table_name} So, is it possible to display the first 10 records PLUS only ...
3
votes
2answers
6k views

difference between UPDLOCK and FOR UPDATE

given the following code: Set conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") conn.BeginTrans ... //This is the line it is about RS.Open "SELECT a,b FROM c FOR ...
3
votes
3answers
3k views

How to check for mutiple rows with same data?

Can anyone advise how do I check multiple rows with same data? E.g. I have 2 rows with following values: Cola Colb Colc TYPE_APPLE 123 abc Colb Colb Colc TYPE_APPLE 123 ...
0
votes
2answers
75 views

some tips on setting up sql server database server

I am currently setting up a new SQL Server database server with databases. It is supplied by a third party company. I need to request access for them around ports etc. I would like to be able ...
29
votes
5answers
17k views

Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?

I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ Why ...
0
votes
2answers
926 views

Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP

I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment. I have a web based form with multiple ...
1
vote
2answers
233 views

Suggestion for storing a lot of simple data

I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date. The way I planned on laying out the database was as follows: 2 Tables ...
5
votes
1answer
161 views

SQL Server 2005 not collecting logins in the log file

I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine. I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and ...
7
votes
2answers
1k views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get ...
4
votes
2answers
1k views

What utilities can I use to simulate I/O load for a DB restore and DBCC check?

I'm trying to figure out the peak load I can sustain on new hardware I procured for validating backups and performing DBCC checks. I've been using Crystal Diskmark to get throughput stats which helped ...
2
votes
1answer
850 views

Pervasive linked server provider missing

I am having the same problem as this question. I installed Pervasive v11 (which does have 64bit ODBC drivers) and I still do not see Pervasive listed as a provider when trying to create a linked ...
4
votes
2answers
812 views

Reseed a newly created table

I have create a new table on SQL Server 2008. This table has an identity column, defined like this: [ID] [int] IDENTITY(1,1) NOT NULL However when I insert any data, the first record starts from ...
0
votes
2answers
901 views

Output count percentage in 2 decimal places

I have the following sql query code and I can not get to execute the desired output. SELECT Deal_type, Price_type, cast(sum(Deal_type)/count(Deal_type)*1.0 as decimal(12,2)) as results FROM ...
2
votes
1answer
519 views

Log all than queries taking more than X seconds

I have a database Microsoft SQL Server 2008r2. I have some applications in different programming languages. I would like to log all the queries that take more than X seconds. I guess that the I ...
5
votes
1answer
2k views

Specifying the failover partner when using Availability Groups

We have a situation where we are using replicated databases in an environment that's making use of Availability Groups. As we wanted no manual intervention when failing over, we decided to set up ...
4
votes
2answers
6k views

Why is SQL Server memory showing in task manager

I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager. However I have recently been doing some testing on SQL Server that ...
7
votes
4answers
8k views

SQL Server - Separating data, log, and TempDB files on a SAN

I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
0
votes
2answers
94 views

How to organize a single query to several sql servers?

I need to make an inner join sql query to several SQL Servers (one is 2000 and another is 2008 R2). The query is like the following: SELECT k.ID, k.NAME, k.BIRTH, l.PROFILEID FROM ...
2
votes
3answers
6k views

How to import table's data in MySql from Sql Server?

I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error. Here this error may be ...
4
votes
2answers
3k views

Subquery in the VALUES clause

Here's a real-world schema: CREATE TABLE [dbo].[Setting] ( [ID] [BIGINT] NOT NULL, [Module] [NVARCHAR](400) NOT NULL, [Property] [NVARCHAR](400) NOT NULL, [Value] ...
4
votes
1answer
1k views

How do I create a CLR UDF with external name specifying namespace, class, and subclass?

I am creating a CLR UDF using external name and second level nested class names seem not to be permitted. Is this the case? When using the following syntax and including a first level class name and ...
2
votes
1answer
481 views

Named Pipe Provider Error code 40 [closed]

I have literally tried everything, from enabling named pipe to adding exception to ports in the firewall, to everything possible in surface configuration. I can connect to the SQL instance(using TCP ...
3
votes
1answer
79 views

Locating row in index page

In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below. How SQL check whether some index key belong to ...