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)

3
votes
1answer
657 views

Optimization around static cursor use

I'm currently in-house support for a vendor-supplied application which uses static cursors to crawl through a list of data. I've leaned on the vendor as much as I can to fix their cursor use with no ...
2
votes
1answer
815 views

Disabling Indexes and Auditing for large data import

Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are: 1. Is it ...
10
votes
2answers
499 views

Is there a set based way to load/read a tree branch with HierarchyId

I am playing around with HierarchyId, and I have not figured out a set based way to do the following: insert all subtree at once retrieve all subtree at once This question is related to my ...
0
votes
1answer
1k views

Strange Sybase / SQL Server linked server issue

I have a sybase table which has a column note varchar(1000) null. The following T-SQL will get the following error if there is a not-null value in note. (sybase is the linked server to the Sybase ...
3
votes
1answer
719 views

SQL Server to Oracle Export - Data conversion error

I have a rather weird problem with exporting a bunch of tables from SQL Server 2005 to Oracle database. I have SQL Agent job that deletes Oracle tables, recreates them and then exports the data using ...
5
votes
1answer
3k views

SQL Server memory usage

I'm trying to understand more about the memory usage for SQL Server. I can't seem to figure out what's exactly stored in the memory buffer. My setup is a Windows Server 2008 x64, 8 GB RAM, ...
6
votes
2answers
1k views

Error handling in container procedures

I'm aware of and use the techniques in this question. In my environment we have a lot of container procedures that call subprocedures, that may call other procedures, ad infinitum. For me ...
1
vote
1answer
893 views

SQL Database to XML?

I would like to export SQL data to an XML file so it can be read in COBOL. I am working with SQL Express 2008 r2.
1
vote
1answer
559 views

JDBC Read uncommitted in SQL Server

If I set the jdbc isolation level to read uncommitted in SQL Server, will SQL Server ever lock any tables, pages, rows etc? Thanks.
-5
votes
2answers
1k views

Performance Improvement in SSRS' Reports [closed]

There are reports build in SSRS 2008 by some user. I need to improve the performance of the already-build reports. The reports fetches a lot of data and run very very slow. I googled and found ...
1
vote
0answers
61 views

SQL Server Database Size much higher than the backup size after restore [duplicate]

Possible Duplicate: Why is a .bak so much smaller than the database it's a backup of? The database backup size is around 120 MB but when I run Restore FileListOnly, I see that the data ...
1
vote
1answer
3k views

Find last (max) value according to TimeStamp using update method

I have task to find values of integer according to last (max) of timestamp (datetime) value. Because it is so complicated query I am consindering this way to find last value something like this ...
0
votes
1answer
562 views

Stored Procedure is not returning complete result

I am using the query as mentioned in the answer accepted in the question located at Stored Procedure doesn't show result in SQL Server Now when I run this SP with the values provided in the ...
1
vote
2answers
4k views

How to update SQL Server 2012 from Evalutation to Developer edition?

During the installation of a new development infrastructure, I had to install many SQL Server instances on different machines. Some were of course dedicated for production and some other for ...
0
votes
0answers
46 views

Database security through stored procedures [duplicate]

Possible Duplicate: Do stored procedures prevent SQL injection? I'm curious about some database security techniques I've been learning in class recently. Namely, I have implemented a SQL ...
3
votes
4answers
19k views

Cause of High Disk Queue Length in SQL Server

I have a fairly high-throughput application that occasionally decides to collapse on me. It's not very often - about once every ~3 weeks or so. When it does, if I check out perfmon, I see 100% "Avg. ...
3
votes
2answers
244 views

Setting up users and admins with schema privileges

I currently have a database that is split into two schemas: data and ref. I have two database roles named Users and Admins. Users need to be able to INSERT, SELECT, UPDATE, and DELETE from the data ...
2
votes
1answer
116 views

Associate a piece of data with a session

I think I want to associate a piece of data with each session. The association must have the following properties: The data being associated is provided either as a part of connection string (if ...
2
votes
1answer
1k views

Is Index causing timeouts?

Recently, while fine tuning the db for better perf, I changed a couple of indexes on tables which were hit very often. I am using the following query to keep tab on index stats SELECT DISTINCT ...
2
votes
1answer
813 views

Stored Procedure doesn't show result in SQL Server

I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP ...
2
votes
1answer
938 views

How to design database for tree view with an infinite depth

I am trying to combine C# and SQL2005 for an application which need to have tree view control. One of user requests is to that tree view can user friendly grow into infinite dept of child nodes. That ...
6
votes
1answer
328 views

Few Questions about SSAS Cubes

I am newbie to SSAS 2012 and here are my questions: Can we have measures of complex types (table)? Is it possible to order the results by a specific measure or attribute? My cube can be modeled as ...
8
votes
5answers
9k views

Does SQL profiler affect server performance?

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that ...
3
votes
2answers
1k views

removing group by clause with SUM

So lets say I have a table, MyBigTable that has student scores of an examination. So lets say the structure looks something like id name school board exam_partA_score exam_partB_score Then I need ...
2
votes
1answer
77 views

How to consolidate script duplication?

I just want to know how you deal with long scripts and redundant information. Here is an example: Suppose you have three tables: table1, table2, and sometable. And you have SELECT id = 1 org ...
3
votes
1answer
131 views

Relationship with a default value

Firstly, I know my title is somewhat misleading, but I couldn't think of a better title. Here is my current relationship CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT NULL, Name ...
7
votes
4answers
14k views

Converting RTF in a text column to plain text in bulk

I have a legacy system with about 10 million rows in a table. In that table there is a column of type text, most of them are standard text but about 500 thousand rows have RTF markup in them. I need ...
2
votes
3answers
1k views

Is it OK to create hundreds of databases in SQL Azure versus one big one and run the risk of deadlocking

I need to create highly scalable solution - where field devices in thousands of sites are delivering data in real time to a back end system, and SQL Azure seems to fit the bill nicely in terms of ...
2
votes
1answer
153 views

After dropping the clustered index, it is still attempting to do a clustered insert [closed]

I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows. I am running the following script to do it: USE ...
3
votes
1answer
5k views

Memory utilization per database - SQL Server

I am trying to find the memory utilization per database(using dmvs) in a sql server instance, ie (Buffer Cache + Procedure Cache) over a period of time. I have the query to get the buffer cache ...
24
votes
3answers
8k views

Which PowerShell technique should I use to talk to SQL Server?

I'd ultimately like to use PowerShell to replace the old KornShell scripts we use for SQL instance monitors. I'm having a hard time, though, getting my brain around all the different ways that ...
2
votes
2answers
128 views

Hosting ASP.Net session database on same same instance as the user database?

We have a web farm using SQL Server sessions. We are seeing occasional timeouts connecting to the session DB even though overall utilization is low. The ASPState session database is on the same ...
5
votes
2answers
3k views

Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users

I'm designing a system for multiple users to queue their requests to a web service which takes requests such as creating new entries, changing entries, requesting data. Many of the web service calls ...
-2
votes
1answer
75 views

Who is the original author/creator that took the time to fill/create Northwind SQL server sample database? [closed]

I've used it before, but I never really seen any documentation in SQL server pointing to who actually created it. Is it a person, a group of people? Did Microsoft create it? Who??
7
votes
2answers
1k views

Can Availability Groups replace transactional replication?

I've been looking at the new features of SQL Server 2012, and how it can help my current situation. Currently we're on SQL Server 2008 R2, and replicate two OLTP servers' databases to a single ...
5
votes
1answer
4k views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN ...
2
votes
2answers
649 views

Is it possible to do a ALL SERVER DML Trigger?

My department works on converting a client's database when a client switches from another company to us. We have a RDP session they can connect to and see our software with their data. Once they ...
1
vote
1answer
5k views

Clarify - Need to add Multiple foreign key constraints

I am not sure if I am thinking about this process the wrong way. Just to Surmise if I create a table(TeamList) whose sole purpose is to capture the coaches team list of players and positions for a ...
3
votes
3answers
3k views

View foreign key constraints so I can delete a table

I have copied a database I was working on so I could change some key elements of its design for my new version. I want to delete totally some tables from the database however I cannot due to some ...
9
votes
0answers
4k views

Fast SQL query with * but extremely slow with specific columns? [closed]

We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this: SELECT * FROM MyView a (nolock) INNER ...
0
votes
1answer
283 views

Write Access Client App for SQL Server 2005 Backend [closed]

I'm writing an inventory/payroll system, and I'm storing all the records on a remote server running SQL Server 2005 on Windows Server 2008 R2. I was wondering how I could start writing forms(very ...
6
votes
1answer
750 views

Including clustered index key in non-clustered index

We're running SQL Server 2008. In looking over the results of a query I ran on sys.dm_db_missing_index_* views, I see a curious suggestion for an included column. I have the following two tables: ...
15
votes
4answers
4k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
6
votes
4answers
3k views

Do I really need to keep .LDF files?

Each month we make an end of month snapshot of our production databse. These month end snapshots are strictly for reporting purposes, there are no inserts, updates or deletes ever done on them. Each ...
3
votes
1answer
2k views

SQL Backups - Full vs Differential vs Log Shipping

I'm looking to refine our SQL backups utilizing Full, Differential, and Log Shipping. Currently, I am only performing full backups once at night. This process is taking so long that it is running ...
27
votes
5answers
32k views

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
1
vote
1answer
2k views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
4
votes
1answer
248 views

Optimizing queries against a set of tables that contain partitioned data

I have a third party program that creates and populates a series of tables to hold some historical data: data_33 -- contains data for 12/01/11 - 12/05/11 data_34 -- contains data for 12/05/11 - ...
3
votes
1answer
7k views

SQL Server 2008 R2 on Windows 7 x64 Professional - what are the downsides?

I need to minimize the cost of the platform, that will run SQL Server 2008 R2 Standard Edition. I have a special licensed version of this SQL server to work with like 20 users (it's an 1C special ...
3
votes
3answers
476 views

How do you ensure accuracy of your query

I am have a table with about 100,000 rows. Lets suppose the primary key is some sequence of numbers like a barcode for example. I am doing checks like SELECT count(*) FROM TABLE WHERE ...