Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
33,669
questions
7
votes
2
answers
537
views
In Oracle, can we restore a database onto the same instance with a different name?
In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different ...
- 5,962
5
votes
3
answers
11k
views
Query to normalize table/combine row text
I have a table (call it oldTable) with columns like so:
ID (int),Rank (int),TextLineNumber (int),SomeText (varchar)
The primarykey is multi-part: ID+Rank+TextLineNumber.
I'm trying to transform/...
- 53
13
votes
4
answers
11k
views
Dropping a constraint (index) on a column
How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...
- 231
7
votes
1
answer
893
views
Dependant T-SQL comparison collation
Is there any way to set the collation for a string comparison based on an independent variable or column value? Something akin to
SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
...
- 173
43
votes
4
answers
83k
views
SQL Server "Suspect" database?
What do you do when you have a database that is marked as Suspect?
Restore from the last backup?
Please advise.
- 1,371
4
votes
2
answers
965
views
How can I extract a lessequal char from SQL_Latin1_General_CP1_CI_AS?
When I create a table like this
create table char_test(
item varchar(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
I can store varchars containing ≥
insert into char_test values ('≥');
When I ...
- 12.2k
2
votes
2
answers
2k
views
Best index strategies for read-only table?
I have a table in SQL Server which has following characteristics:
will contain about 1.2M records.
first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each
the ...
- 133
3
votes
2
answers
11k
views
datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float
What datatypes could be given to this value --> 45.58
Could these be given:
numeric
money
decimal
float
What's the difference then? Which one would be the best to use?
- 131
5
votes
1
answer
2k
views
Why is this rollback needed when using sp_addextendedproperty in a stored procedure?
In enter link description here I showed, how I document databases.
To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls.
But lately I wanted to catch ...
- 12.2k
7
votes
4
answers
14k
views
Property PopulationStatus is not available for FullTextCatalog... How to fix?
I've created a new catalog (case insensitive, make default catalog), when I try to right click > properties to add tables I get this error:
Property PopulationStatus is not available for ...
- 3,433
3
votes
1
answer
3k
views
A nonrecoverable I/O error occurred on file after DB Shrink
After shrinking an SQL Server database & files, I encountered an error message saying "A nonrecoverable I/O error occurred on file" while performing database backup.
Why am I getting this error?
someguy198650
18
votes
3
answers
1k
views
How to store 'n' days of web server logs in Sql Server?
For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time.
We have a daemon ...
- 283
10
votes
1
answer
6k
views
SQL Server Deadlock Graph - Table, Page or Row Lock?
Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know ...
- 1,875
7
votes
6
answers
2k
views
SQL Server 2005 Huge DB issue
My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up.
...
- 253
4
votes
3
answers
1k
views
Most suitable DB Architecture for real-time high-volume processing API
I've got a service that should support real-time interaction between many users (thousands real-time). I use .NET 4 with MSSQL 2008 on IIS 7.5.
The DB consists of only a few tables without high usage ...
- 239
35
votes
7
answers
78k
views
How can I tell if a SQL Server database is still being used?
We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.
How can I tell if they are still being used by users or a web application?
I found a forum ...
- 1,040
9
votes
3
answers
959
views
Speed impact of using varchar in SQL Server 2008
In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
- 193
2
votes
2
answers
570
views
Database Mirroring Failure Reasons
I was wondering if there is any information on what scenarios might cause a DB Mirror to have to be recreated?
I am attempting to research how sturdy the technology is and under what scenarios do we ...
- 161
3
votes
2
answers
390
views
Comparing two XML query plans
Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did ...
- 11.2k
15
votes
2
answers
9k
views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
- 17.9k
4
votes
4
answers
5k
views
SQL Server 2008 R2 Ent trial expired move to express?
So someone let our SQL server 2008 R2 enterprise trial expire on a web server. It was part of a plan to restart a project and move everything we had over to MySql instead. We don't need anything ...
- 235
10
votes
1
answer
6k
views
Print Parameters in Dynamic SQL
I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement.
EG:
Declare @SQL nvarchar(max), @Params ...
- 294
5
votes
2
answers
1k
views
DMV Queries as Perfmon Counters
Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters.
For example, take:
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;
Most ...
- 2,335
29
votes
5
answers
30k
views
How do I switch off SCHEMABINDING for a view without recreating it?
How do I switch off SCHEMABINDING for a view without recreating it?
- 6,702
5
votes
2
answers
6k
views
tsql: table data size won't shrink after large row deletion
i have a table with 12 months+ data in it.
I backup the table then delete all rows keeping only rows inserted in the last month.
But the table size won't shrink, nor the underlying physical file.
...
- 221
7
votes
1
answer
2k
views
Nonclustered index column order
I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order.
If I run the following query:
select
col1,col2
from ...
- 1,203
4
votes
1
answer
654
views
Impact of an Asynchronous Mirror / Full Recovery Model on Performance
For an asynchronous mirror in SQL Server 2008 R2, using the full recovery model is required.
Assuming that the network and disk IO on both sides of the Mirror can keep up with the transaction logs ...
- 2,335
14
votes
1
answer
13k
views
sp_WhoIsActive and get_plans -- Actual Plan?
Does Adam Machanic's sp_WhoIsActive return the actual query plan or the estimated plan when run with @get_plans=1 ?
- 2,335
31
votes
5
answers
11k
views
Why use an int as a lookup table's primary key?
I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).
I understand that using a ...
- 413
11
votes
2
answers
1k
views
A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?
We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.
I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
- 809
42
votes
3
answers
81k
views
Execution Plan Basics -- Hash Match Confusion
I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join:
select Posts.Title, Users.DisplayName
From Posts JOIN Users on
...
- 2,335
11
votes
5
answers
30k
views
User Permissions Messed Up following a Backup -> Restore Operation
I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
- 809
5
votes
3
answers
2k
views
How does NHibernate handle execution plan?
I heard from a podcast that there are no ORMs that have a good solution for execution plan reuse. It will lead to increased execution plan cache which affects the performance.
How does NHibernate ...
- 633
10
votes
1
answer
5k
views
When should I use Full-Text Search?
When should I use Full-Text Search instead of using a LIKE search, such as
SELECT * FROM myTABLE WHERE myCOLUMN LIKE '*something'
- 633
9
votes
2
answers
2k
views
Resources for SQL Server developer migrating to Oracle
What resources can you recommend for a SQL Server developer wanting to learn Oracle basics?
I am looking for a comprehensive whitepaper or blog post describing differences between these systems and ...
- 1,485
4
votes
2
answers
3k
views
SQL Server DR Plan - Test Documentation
I have been tasked with creating a document for the testing of our DR/BC Plan for our SQL Servers. We are using DB Mirroring (tier-1) and transaction log shipping (tier-2) and for tier-3 databases we ...
- 161
5
votes
1
answer
2k
views
Oracle query against SQL Server database giving inconsistent results
We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
- 23.8k
1
vote
1
answer
2k
views
SQL Server 2008 R2 - how to repair System Views
I have SQL Server 2008 R2 database, which is corrupted. Dbcc checkdb command gives result:
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts.
...
- 171
2
votes
2
answers
857
views
SQL 2008 - DBCHECK Failed
I am using SQL Server 2008 and have a Maintenance Plan setup that does a DBCHECK before the nightly backup.
This has been working fine for months. It has started failing. If I run a DBCHECK on the ...
MartGriff
5
votes
3
answers
110k
views
SQL Server 2008 R2 (Suspect) mode - how to repair?
I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this query:
EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER ...
- 171
3
votes
3
answers
2k
views
ms sql differential backup size jumps
We are doing a full backup once a week and a daily diff between that. The total db is 80Gb, the diffs start out under 1Gb, then gradually grow to approximately 3-5Gb which is ok, but then all of a ...
- 451
5
votes
3
answers
4k
views
Port number change
We are planning on setting a static port for SQL Server 2005/2008 failover clustering. Any guide on what port to choose/select for 4 nodes of each cluster?(Active/Active)
I am also thinking some ...
- 1,371
5
votes
1
answer
12k
views
How do I calculate values based on the previous row after skipping the first 12 rows?
I'm looking for help to write this query. I have included some sample data below. My initial premise is that all the values from the 13th row onward will be dynamic and the first 12 rows will be ...
user1234
2
votes
2
answers
2k
views
SQL 2008 R2 3rd Party Peer-to-Peer Replication, Global Site Distribution
We are looking at hosting 3 globally distributed SQL Server installations at different data centers. The intent is that Site A will serve web traffic and data for a specific region, same with Site B ...
- 23
39
votes
5
answers
84k
views
Methods of speeding up a huge DELETE FROM <table> with no clauses
Using SQL Server 2005.
I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
- 1,875
2
votes
1
answer
317
views
Are there other sites still using SQL Server with codepage 850 or codepage 437 for their sql scripts?
Using one of these codepages is a disaster when scripts are edited by Visual Studio or TFS, but sometimes it is difficult to change old standards.
What must be observed, when changing to the current ...
- 23
11
votes
1
answer
1k
views
SQL Server 2008 R2 Dirty reads - how non-atomic?
I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but:
Can a row appear as ...
7
votes
1
answer
598
views
convert legacy showplan text into graphical format
Does anyone know of a way to convert legacy showplan text into a graphical format?
In other words, can I take the StmtText returned from SET SHOWPLAN_TEXT ON and convert that to show me a graphical ...
- 6,359
5
votes
2
answers
2k
views
Should we use SQL Server partitions for a multi-tenant database design?
My organisation is creating a multi-tenant data architecture to support analysis of data uploaded by multiple different clients. The database is Microsoft SQL Server.
It has been suggested that ...
- 151
6
votes
3
answers
1k
views
Sub queries or joins?
Whats better in terms of SQL server efficiency; to use sub queries or joins?
I know uncorrelated are better than correlated sub-queries. But what about joins?
The SQL becomes more readable and ...
- 2,032