SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

3
votes
1answer
307 views

Proper Design for Datamart Invoice

I'm trying to design a set of fact tables for a datamart that will work in an ssas cube for one of our clients. One of the challenges I'm facing is that there appears to be some disperencies between ...
3
votes
1answer
129 views

DTC in SQL server 2008 R2 Clustering

I believe DTC is optional but cannot confirm it (can't find it on MSDN) Why we need DTC (distributed transaction cordinator) when clusting a single instance (active passive scenario)?
1
vote
2answers
673 views

Create multiple files for each file group for table partitioning?

I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO ( [PRIMARY], [fg2], [fg3], [fg4], ...
3
votes
3answers
1k views

SQL Join slower than subquery on View with a Function

My database has a view that calls a function. Joining on that view is surprisingly slow. Is there a way to speed up joining to a view like that? I've included 2 queries which illustrate the issue. ...
3
votes
2answers
2k views

Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights?

I am struggling with maintaining or re-gaining access after restoring an SQL Server database (2008 R2, if that makes any difference). Here is what I am trying: SQLServer login APPUSER is member of ...
2
votes
2answers
5k views

How to deny drop permission for a table?

Is there a way to deny drop permissions for a specific table from a user or role?
1
vote
1answer
279 views

How to create a Job for Merge Replication?

I want to create a step in a Job for Merge Replication. I basically want to know which command should I write so that a particular Subscription starts synchronizing?
1
vote
1answer
309 views

drastically different runtimes in BIDS and the SSRS Web Portal

I have a report in a BIDS project that has been deployed to our SSRS Web Portal. On the web, it renders within seconds. In BIDS, it takes upwards of seven minutes. I can see that the query runs ...
5
votes
6answers
5k views

Any way around unique index 16 column max

According to the CREATE INDEX documentation: Up to 16 columns can be combined into a single composite index key. We've got a table with ~18 columns that need to form a unique combination. This ...
9
votes
2answers
37k views

How to create Sql Server login for a domain account?

All, I have Sql Server 2008 installed on a server (let's say Server1) in a Domain (let's say AD). I also have a domain account called AD\Sql1. This is not an Admin account on the Domain (AD), but I ...
3
votes
1answer
1k views

Sql Server 2008 Replication “Generate Scripts” option not available

All, On Sql Server 2008, I have Transaction Replication going from Server1 to Server3 (with Server2 as distribution). I have an option to "Generate Scripts" on Publication on Server1. But I do not ...
4
votes
2answers
3k views

SQL Server 2012 RC installation failing

I'm trying to test out the new SS 2012 RC, but I'm having problems with the installation. My installation stops a ways in saying that the resource needed cannot be found. I'm using the ISO from MSDN ...
3
votes
1answer
146 views

migrating from SQL server 2005 to 2008

I had SQL server 2005 installed on my windows server 2008 R2. Recently i installed SQL server 2008 and SQL server management studio 2008 r2 (SSMS) on my virtual server and i configured the instance ...
1
vote
2answers
5k views

Why can't I connect to SQL Server 2008?

I initially installed SQL Server 2008 R2 with Windows authentication mode and have successfully changed it to mixed mode (per http://msdn.microsoft.com/en-us/library/ms188670.aspx). After this I am ...
9
votes
3answers
8k views

Storing date as integer (numeric), what are the advantages

Question 1 I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example ...
12
votes
1answer
3k views

Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets?

I want to handle a large dataset (> 1 billion rows) in SQL Server 2008 R2. I heard that if you set up "sharding" or "horizontal partitioning", it makes it quicker to handle large datasets as it breaks ...
2
votes
1answer
290 views

How to recover SA account password without restoring it?

I forgot my SA password and resetting the password is out of the question because internal applications are using it and will will affect them. I also don't have an option to change the SA credentials ...
13
votes
2answers
12k views

In Sql Server's RANK function, how to limit the results to just the first 3 ranks

In Sql Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from Rank 1, 2, ...
1
vote
1answer
518 views

data driven subscriptions in SSRS - pulling a password value from the database

If I create a data driven subscription in SSRS and use the query to return some value for the username and password, how "safe" is this password? Let me explain. I am looking to set up a data source ...
2
votes
1answer
213 views

How do I store more than 15,000 Japanese characters in a column?

I want to store more than 15,000 characters of Japanese text in a SQL Server 2008 database. We have a front end tool that will display this text.
5
votes
2answers
967 views

Transaction independent writes inside a trigger

I am using sql express 2008 r2 express edition. Is it possible to do writes inside a trigger independent from the the transaction of the statement which causes the trigger to fire? I have triggers ...
2
votes
1answer
1k views

Performance Inserting and Updating Millions of rows into a table

We have a CSV the user imports into the Application. After inspecting the CSV, validating and applying some business logic, we got Inserts and Updates operations we should perform into a ...
3
votes
1answer
4k views

Sql FILESTREAM garbage collection issue

Updated 3x I'm using FILESTREAM, but am having an issue with garbage collection. The FILESTREAM files are not being cleaned up when I set the column to null. In the event log, there is an enormous ...
5
votes
1answer
96 views

Total locking time

Consider two approaches to update data: Approach 1: Update Table 1, commit Update Table 2, commit Update Table 3, commit Approach 2: Update Table 1, Table 2, Table 3, commit. Now Approach 1 is not ...
6
votes
3answers
9k views

Why is this Full Outer Join not working?

I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join. I have 2 tables (which ...
2
votes
2answers
1k views

Notification mail not sent on failure

Currently we are having problems with our backups, specifically disk space issues. On investigation it seems the notification mail isn't being sent when the job fails. This is a weird issue as a ...
0
votes
1answer
260 views

Equivalent of include clause while creating non clustered index in sybase?

I searched on creating non clustered index with included columns for sybase database. But I don't think "include" is allowed while creating index. Is there any equivalent in sybase?
2
votes
2answers
1k views

SQL Server: in what situation can snapshot restore be blocked after SET SINGLE_USER?

In my tests, i have the following script: USE master; ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [TestDB] FROM DATABASE_SNAPSHOT = 'TestDBSnapshot' In some ...
1
vote
3answers
613 views

Big size of database logs SQL Server 2008

I have a database which is running under Microsoft SQL Server 2008. Now, I have seen that the log of the database (the .ldf file) is growing to big size. The database file (.mdf) has a size of 630MB ...
4
votes
2answers
841 views

Splitting a column in two and returning a summary of results

Lets take a very simple table Department | Staff Name ---------------------|--------------- Web - Electronics | Matthew Store - Electronics | Mark Web - Home | Luke Store - ...
6
votes
1answer
964 views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
7
votes
3answers
886 views

What happens to non clustered index when included column is updated using update statement?

Question on non clustered index with included columns (DB - MS SQL Server). I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is ...
6
votes
1answer
8k views

Create a new column in a view and assign it a data type

here is my query: CREATE VIEW VIEW2 AS SELECT [Column1], (NULL) AS column2 VARCHAR(10) FROM VIEW1; I get an error in the third line. If I leave the query like this, without specifying the data ...
2
votes
1answer
598 views

How do we create spatial index on views if it is possible?

Is it possible to create spatial index on sql server 2008? If yes how do we create it on table with schemabinding?
0
votes
1answer
188 views

Recommended Data profiling tool for BI developer

What recommended data profiling tool to retrieve a lot of information and enable to visulize the data into graphical statistics? The data is coming from the source database.
5
votes
2answers
520 views

SQL Server - DBO without Backup

Similar to my previous Question : Prevent Non Copy Only Backups, how do I create a user that can do everything to my database take backups at all. I've granted them every role except ...
9
votes
6answers
3k views

Tables with hierarchy: create a constraint to prevent circularity through foreign keys

Suppose we have a table that has a foreign key constraint to itself, like such: CREATE TABLE Foo (FooId BIGINT PRIMARY KEY, ParentFooId BIGINT, FOREIGN KEY([ParentFooId]) REFERENCES ...
5
votes
1answer
756 views

What is the impact of large varchar data types on indexing?

We have some tables in our database that have a data type of varchar(8000) where the longest actual value in that column is 75. When we place an index on the column we get the expected warning that ...
3
votes
1answer
2k views

Install SQL Server 2008 Express on same machine as normal SQL Server 2008 install?

When setting up a server, we installed SQL Server 2008. Now it turns out we don't need it, and we want to use the free Express version instead. Ideally without much interruption to things using the ...
3
votes
3answers
30k views

How do I preserve the leading zeros when I insert a number into this table? [closed]

I have inserted two records into a table. create table num(id int) insert into num values(0023) insert into num values(23) select * from num When I query them, they all display as 23. It means that ...
2
votes
1answer
698 views

How often do you back up the distribution database for transactional replication when sync with backup is set to true for the distribution database?

I'm trying to figure out how often to back up the distribution database when "sync with backup" is set to true. If I understand this correctly, sync with backup ensures the distribution database can ...
4
votes
1answer
560 views

Getting ratio of reads to writes on SQL Server

Sorry I am not a DBA so bear with me. I wanted to get a better understanding of the ratio of reads to writes in our archiecture. The best to get this is at the database. We are using SQL Server ...
5
votes
2answers
3k views

Dropping and recreating indexes for performance reasons?

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's ...
5
votes
1answer
61 views

SQL Server 2008 Index

What would be the best index(es) to add to the tables in order to make this query as fast as possible. The sd.incidents tables is about 2.5M records, problems about 8K, and changes about 120K. select ...
4
votes
1answer
153 views

Why does my heap not fill its pages completely while an identical clustered index can fill to m_freeCnt = 0?

-- Example 1: With Clustered Index CREATE TABLE tbl5 ( i VARCHAR(900) NOT NULL ) ; GO CREATE CLUSTERED INDEX CIX_tbl5 ON tbl5 (i ASC) ; GO INSERT INTO tbl5 ( i ) VALUES ...
5
votes
3answers
326 views

Which is the most efficient way to run a particular select query?

If I run the following code: select PolicyNumber, MAX(decpageid) as decpageid, Risk from StatRiskDecpages where PolicyNumber = 'AR-0000301132-04' group by PolicyNumber, Risk I get the following ...
5
votes
3answers
999 views

Recalculate row size in SQL Server 2008?

It is possible to recalculate row size in SQL Server 2008? Take this example: declare @counter int declare @statement nvarchar(max) set @counter=0 drop table kua2 create table kua2(id int) while ...
0
votes
1answer
323 views

milli seconds to datetime

I have this statement exec [spProject_Update] @ProjectID = 496, @Name = 'Hangman Bollywood iPhone', @Description = '', @EstimateTime = 2674800000 I will get milliseconds in @EstimateTime How to ...
3
votes
2answers
3k views

How to identify fragmentation level of the table data itself not the table indexes, and then defrag

Every where I can find descriptions on how to identify the fragmentation level of the table indexes, but I cant find how to identify the same for the table data iself. Also defragmentation solution is ...
3
votes
2answers
838 views

why does my clustered index scan have ordered = false

When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False. I will mention that we have parallelism involved, ...