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).
8
votes
3answers
670 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
1answer
144 views
SQL Server Transaction Backups vs Logs
I've inherited a moderately-sized business-critical SQL Server 2008 database and am trying to wrap my head around backup planning. (I'm a developer, not a DBA.)
The way our system is set up right now ...
7
votes
3answers
279 views
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
7
votes
1answer
379 views
What factors go into an Indexed View's Clustered Index being selected?
Breifly
What factors go into they query optimizer's selection of an indexed view's index?
For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
7
votes
1answer
878 views
Broken NTFS Permissions for MSSQL$SQLEXPRESS
I have been "securing" our database server. To do this I removed some permissions from the folders that the SQL Express install created.
D:\SQL
D:\SQL\Data
D:\SQL\Backup
etc..
I kept the ...
7
votes
1answer
196 views
Tracking stored procedure usage
Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
6
votes
1answer
205 views
problem running xp_cmdshell when Application Name set in connection string
I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL ...
6
votes
2answers
88 views
RID vs INCLUDE on a large field
I have a table that stores notes
create tblNote(
Id int identity(1,1),
ParentId int ,
ParentType varchar(32),
NoteType varchar(32),
Note varchar(max),
CreatedBy ...
6
votes
2answers
397 views
SQL Server 2012 Availability Group is it “AlwaysON”?
In a traditional SQL Server clustering when failover occurs, all client that are connected to SQL Server failover instance looses the connection and each client must reestablish a new connection to ...
6
votes
1answer
1k views
Recover accidentally deleted rows from a table
My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup.
Is it possible to restore the deleted ...
6
votes
2answers
182 views
SQL Server Internal Inconsistency on GROUP BY
I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS.
Now I can select records from a table-valued function to retrieve sales records, but using a ...
6
votes
4answers
420 views
How can I tell if my SQL Server DB performance is hardware-limited?
Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test ...
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.
...
5
votes
1answer
212 views
How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?
We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day.
The problem is, the differential backups don't always seem to ...
5
votes
2answers
770 views
Why index REBUILD does not reduce index fragmentatation?
I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
5
votes
3answers
11k views
Combine column from multiple rows into single row
I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
5
votes
2answers
972 views
Data type for storing an array of flags (a bitmap/bit array)
I need to store a bit array for each record of a table, supporting the following operations:
Testing if a bit is set, and setting a bit (using SQL)
Querying and setting the value using ADO 2.8 (not ...
5
votes
2answers
12k views
Sql Server database in recovery: How to stop, Take ofline or delete?
I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so.
I need to either stop the recovery, or kill it in some way.
I dont care one ...
5
votes
2answers
954 views
SQL Server Partitioning vs Indexes on Separate Filegroups
I am just about to start on an excercise to reorganise a partitioned table and I was looking for best practises or recommendations.
Let us say I have a billion row table and 8 logical disks available ...
5
votes
4answers
1k views
Slow temp table drops in sql 2005
I've run into a problem on our production sql server where temp table objects take a long time to drop (obvious when small and synchronous drop is used). I can't reproduce this on other sql servers ...
5
votes
1answer
748 views
Send email when certain Data is being inserted in MS SQL Server
I realize that there is an integrated email notification system in SQLServer, where I can configure it to send an email using my organization's Exchange server.
I want to use that to send email on ...
5
votes
5answers
866 views
What security threats does the SA account and other known account names pose to security?
Does an known account name like sa, pose a security threat to database? When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout ...
4
votes
1answer
132 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
4
votes
1answer
1k views
How and when does SQL Agent update next_run_date/next_run_time values?
I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific ...
4
votes
2answers
460 views
What are the differences between SQL I/O Sim and SQL IO tools?
I want to test my different SQL Server alternatives with a benchmark tool.
I found two tools by Microsoft:
SQLIO Disk Subsystem Benchmark Tool
SQL IO Sim
What are the differences between them? is ...
4
votes
3answers
346 views
Some book suggestions for performance tuning
I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
4
votes
2answers
137 views
Running different instances of SQL Server from different networks on one box
I want to be able to run SQLSMS and select an instance to connect to but the instances are on different networks. I remotely log on to our server and from there, I want to be able to access our ...
4
votes
3answers
5k views
How to prevent transaction log getting full during index reorganize?
We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. ...
4
votes
3answers
1k views
Building Dynamic SQL-Server Where Clause
Let us review this dba.exchange Oracle question for SQL-Server.
This is SaUce's code, after a little formatting:
CREATE PROCEDURE GetCustomer
@FirstN nvarchar(20) = NULL,
@LastN ...
3
votes
2answers
171 views
Is it possible to get schema ID in another database without using dynamic sql?
I start with an object ID and a database ID. Inside a user defined function, I want to get the full name and ID of the database, schema, and object. How can I get the schema ID without using dynamic ...
3
votes
0answers
205 views
Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population
When I create a temporary table with a PK defined from the outset like this I get great performance in subsequent joins on that key:
create table #temp (
field1 int not null
field2 int ...
3
votes
2answers
1k views
What are the limitations of Toad for SQL Server freeware? [closed]
I just downloaded the freeware version of Toad for SQL Server. Toad's download site says: "the Freeware edition has certain limitations, and is not intended to be used as a TRIAL for the Commercial ...
3
votes
3answers
2k views
Stored procedure executes too long
We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
3
votes
0answers
658 views
Which workarounds exists to missing relative path feature of SQLCMD :r?
I'm not the first to notice the lack of this feature.
But I wonder if there are some useful workarounds.
I want to deploy a main script and some depended scripts in the same folder.
BTW Is there a ...
2
votes
1answer
75 views
Prevent users from using power pivot excel connections to a database
A couple of weeks ago my boss connected to our production database via excel and was able to query the database. Because of how we're doing security to the database (AD groups) he should have access ...
2
votes
2answers
167 views
Speed up data transfer from db to application
I have build an application that handles data stored in a MS SQL server database. This server is hosted externally of our company and has to be connected to via SSH because of company rules.
On a ...
2
votes
2answers
486 views
IN Clause causes Execution plan to change from Nested Loops to Hash Match
I'm tuning a query and have discovered some behaviour I'm not clear about.
If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.
There only 7 rows returned in the ...
2
votes
1answer
204 views
Is staggering reindexing jobs a good strategy?
I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes.
...
2
votes
1answer
317 views
What is the best practice for backing up a database in Simple mode?
I am trying to create a SQL Job for backing up my database. I want to have:
A weekly full backup
A daily differential backup
Something that will clean up backups that are older that 14 days
I ...
1
vote
1answer
460 views
Doing a bulk insert in SQL Server
I have to do the insert operation in the db as follows,
I create an item
I create 5-10 properties for that item, all these data i get in the form of a collection of objects.
I donot want to ...
0
votes
0answers
80 views
SQL Server connection failed for domain user from Mac OS machine
I have SQL Server 2008 R2 SP1. I am using connection string to connect to SQL Server.
In connection string I am passing the driver path, server name, TCP port number, user name with which I want to ...
0
votes
3answers
344 views
Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?
I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
0
votes
1answer
884 views
Minimum user rights for connecting MS Sql database remotedly
I have followed this tutorial for getting connected to a Microsoft SQL Server. I connect with ping functionality of Glassfish Server and thus I have to give some user rights to be able to even ping ...
-3
votes
1answer
410 views
How database administrators can see my requests to MSSQL server?
I'm a MS SQL 2008 user. I have access to some tabes. I need to request few colums from table as I usually do. But I need to do it onse (for example) in 5 seconds and system administrators shouldn't ...
19
votes
3answers
3k views
Is the SQL Server Express memory and CPU limit per instance?
If I have 8GB of RAM in a server and I run 4 instances of SQL Server Express, will the total memory limit used by SQL Server be 1GB or 4GB?
Would it be advisable to run multiple instances like this ...
13
votes
5answers
2k views
Why are periodic restarts required to keep my instance performing well?
We've got a production DB server on SQL 2005. Everything runs normally for a while, but after a couple weeks we see a notable performance drop. Only restarting SQL Server brings performance back to ...
11
votes
4answers
2k views
Does restarting SQL Server speed it up?
I have noticed that some DBAs restart SQL Server very frequently, sometimes even nightly. I believe they do it to free up some memory, or perhaps to speed up queries too. I know that after a restart ...
11
votes
0answers
1k views
Passing array parameters to a stored procedure [duplicate]
Possible Duplicate:
Technique for sending lots of data into stored proc
I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.
...
9
votes
1answer
2k views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
9
votes
3answers
1k views
SQL Server - Temporary vs. Physical Tables
A movement is afoot in my place of employ to move away from using #temp tables and instead to use permanent physical tables with SPIDs. Whenever one would have previously INSERTed INTO a #temp table, ...
