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

learn more… | top users | synonyms (2)

3
votes
1answer
147 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
9k 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
291 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
14k 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
543 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
996 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
2k 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
97 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
10k 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
281 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
2k 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
640 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
911 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
984 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
941 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
9k 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
663 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
557 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
4k 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
808 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
35k 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
756 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
623 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
62 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
1k 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 ...
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
945 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, ...
0
votes
3answers
1k views

Is it possible to edit sys.messages texts?

Is it possible to add a new language to SQL Server 2008 and edit SQL error messages with message_id less than 50000?
5
votes
1answer
806 views

Are foreign keys better than indexes?

I have a replicated copy of an application database. I am exploring how to change the constraints and indexes to better serve our reporting requirements. Because this is a copy of the application ...
2
votes
2answers
93 views

can I get SQL Server to choose between a table and a view?

Our reports need to be live and quick - don't they all? The overwhelming majority of tables in our application database just have INSERTs - and the tables have incrementing primary keys. Our tables ...
9
votes
2answers
280 views

SQL SERVER Storage of TinyInt

In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask. USE tempdb ; GO CREATE TABLE tbl ...
2
votes
2answers
3k views

Adding Timestamp to Exported Filename in SQL Server Management Studio

I am exporting to a CSV using a job with xp_cmdshell. I need the filename to include a timestamp. The code I currently have is... EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout ...
5
votes
4answers
3k views

Best pratice to store dates group by months-year key par value

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
2
votes
1answer
893 views

Difference between SSAS 2005 and SSAS 2008

I'm asking this question since i'm planning to book a seminar, but the provider told me that they only offer trainings for SSAS 2008 or as individual training which are expensive. So my question: ...
2
votes
2answers
393 views

Configuring SQL Server profiler

Every 15 seconds or so, the application I am trying to profile fires: @@TRANCOUNT > 0 COMMIT TRAN IF @@TRANCOUNT > 0 COMMIT TRAN IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF ...
0
votes
1answer
353 views

Why is SQL Server 2008 R2 Management Studio making me login again?

I login to SQL Server 2008 management studio each morning, it works all day just fine. When I come in the next morning and try to run a query, it asks me to login again. Where is the setting defined ...
0
votes
2answers
1k views

Recommended plugin for SQL server 2008 [closed]

Is there any recommended plugin for SQL server 2008 to use when you are working as database administration, Business Intelligence developer or Database developer?
1
vote
2answers
703 views

Microsoft SQL 2008 Cluster with replication

i am in the process of setting upi a SQl Server 2008 64 bit Failover cluster. A lot of the database queries that we are running during the days are db intensive and lock a lot of the tables while ...
1
vote
2answers
442 views

SQL Server database mirroring on different servers [closed]

I have two different Windows Sever 2008R2 instance located at different places such as db1 and db2. I need to make db2 as mirror server of db1 i.e. all the database on db1 will have a mirror copy on ...
1
vote
3answers
7k views

How should I Schedule SQL Server 2008 queries to run every month?

I need to schedule some queries to run at the beginning of every month (for monthly reports) and I read that creating a JOB in sql server 2008 was the way to go. Unfortunately, I don't have the ...
5
votes
1answer
3k views

Problems opening standard reports

I am using MS Sql Server Mgt Studio. I select a database. I right click and select Report / Standard Reports / Index Usage Statistics I get Error: Incorrect syntax near '(' I check some of the ...