Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
6
votes
1answer
405 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%'
...
14
votes
4answers
11k 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. Thank you!
3
votes
2answers
266 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 ...
1
vote
2answers
175 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
...
3
votes
2answers
1k 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?
3
votes
1answer
851 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 ...
3
votes
4answers
3k 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
votes
1answer
776 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?
14
votes
3answers
496 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 ...
5
votes
1answer
2k 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 ...
6
votes
6answers
1k 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.
...
3
votes
3answers
586 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 ...
15
votes
7answers
12k 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 ...
8
votes
3answers
684 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 ...
7
votes
2answers
2k 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 ...
2
votes
4answers
2k 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 ...
5
votes
1answer
1k 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 ...
3
votes
2answers
539 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 ...
5
votes
2answers
4k 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?
3
votes
2answers
809 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.
...
4
votes
1answer
542 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 ...
6
votes
1answer
532 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 ?
12
votes
5answers
2k 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 ...
9
votes
2answers
459 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 ...
14
votes
3answers
7k 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
...
4
votes
5answers
5k 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 ...
2
votes
3answers
614 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 ...
3
votes
1answer
919 views
When should I use Full-Text Search?
When should I use Full-Text Search?
Instead of using SELECT * FROM myTABLE WHERE myCOLUMN LIKE '*something'
3
votes
2answers
277 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 ...
3
votes
1answer
426 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 ...
1
vote
1answer
498 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.
...
1
vote
2answers
410 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 ...
3
votes
3answers
512 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 ...
3
votes
3answers
2k 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 ...
3
votes
1answer
3k 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 ...
2
votes
2answers
1k 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 ...
8
votes
4answers
4k 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 ...
2
votes
1answer
215 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 ...
9
votes
1answer
445 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 ...
4
votes
1answer
326 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 ...
3
votes
2answers
692 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 ...
5
votes
3answers
900 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 ...
0
votes
0answers
413 views
Whats better: identity columns or generated unique id values?
I am in middle of a debate about whether it’s better to use identity columns as a primary key on a DB table or have a generated value manually created.
I am arguing for the sql managed identity ...
3
votes
1answer
438 views
Best way to automate sql server backup & restore from prod to dev?
I have a development server, and a production server. The dev server is inside our office at work inside our LAN.
The production server is a remotely hosted windows pc, that is connected to our web ...
3
votes
2answers
285 views
Searching for a Data Dictionary for SQL or Oracle
I am looking for metadata specifications (data dictionary) for SQL Server and Oracle databases.
By metadata map, I mean a definitive description of what objects make up database model and their ...
5
votes
1answer
143 views
In a Trigger, can I determine if a column was explicitly set to a values or not mentioned in update statement?
Given a Table with a column col1 like this:
create table MyTest (
col1 int NULL,
col2 decimal(7, 2) NULL
);
insert into MyTest values ( 1, 1.1);
Is there a way for a trigger for update to ...
2
votes
3answers
726 views
SQL Server Index Fragmentation
When should you rebuild an index? vs reorganize?
What index fragmentation value is considered healthy?
15
votes
4answers
1k views
Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?
Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.
The database development started in ...
10
votes
2answers
3k views
What are the best practices for running SQL Server in a Virtual Machine?
What are the best practices for running SQL Server in a Virtual Machine? My on-line transaction activities are very low, but there is a high amount of data processing for the purpose of providing ...
2
votes
1answer
1k views
bcp in of datetime type data results in an “Invalid date / time format”
I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited.
I then bcp in to a truncated table ...
