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).
2
votes
1answer
129 views
Speeding Up Querying Views
We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries. I can't add indexes to it.
So, I want to cheat.
By ...
3
votes
2answers
161 views
What is the signifigance of a fillfactor of 20?
I have a database for our ticket system in SQL Server express. I was poking around and noticed that a lot of the indexes have a fillfactor set to 20. What is the signifigance of it being set to 20? ...
4
votes
2answers
139 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 ...
8
votes
1answer
295 views
Behavior of data in indexes based on fill factor
Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%?
From my understanding, when the data is inserted there ...
2
votes
1answer
509 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
2answers
376 views
do partitions make sense when dates are not involved?
We are having some discussions about partitioning some application tables in SQL Server 2008 based on their quickly growing size. One is an event table that is typically used for INSERTS (80% of the ...
0
votes
1answer
91 views
Chart creation in BIDS 2008
Due to low reputation point i am not able to include the picture.
I am trying to make u guys understand what i am asking for:
I had created a report. And that Report contains these fields and ...
1
vote
1answer
122 views
SQL Server 2008 Installation pointers for optimizing Performance and Scalability
We are working for a client with an asp.net/SQL server application which has been using a SQL Server 2005 system until now.
The client has decided to upgrade the database to SQL Server 2008 R2 and ...
2
votes
1answer
190 views
Data migration between databases in the same instance
I have two almost identical (structurally) databases:
Documents
DocumentsOld
The original database existed before table partitioning was available, so the solution they found was to create the ...
5
votes
1answer
738 views
Trouble With Database Permissions for sp_send_mail
I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows:
SELECT SUSER_NAME(), ...
2
votes
2answers
131 views
Anyone Know of Some Desktop Software that Searches an SQL server for a String
I have a database on a server that's running Microsoft SQL. I would like to find a tool that logs into that server, allows me to search to a string, and returns the tables and fields that contain that ...
7
votes
0answers
85 views
SQL Server Analysis Studio naive Bayes attribute has too many states
I'm using SQL Server Analysis Studio and I'm trying to train a naive Bayes classifier. I've got an attribute called "item description" that basically represents products you can buy at a store. I've ...
10
votes
3answers
1k views
Are Views optimized when I add a WHERE clause to them?
Does it make a difference if you filter a View inside or outside the View?
For example, is there any difference between these two queries?
SELECT Id
FROM MyTable
WHERE SomeColumn = 1
Or
SELECT Id
...
5
votes
1answer
76 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 ...
2
votes
1answer
146 views
Does the SQL Server optimizer inline function calls in stored procedures?
I'd like to factor out some of the functionality in our SPs into separate functions/stored procedures, but I'm worried about the potential performance penalty. In particular, how efficient will be a ...
7
votes
2answers
351 views
What is an appropriate datatype for an RFID, and is there a standard definition for the format?
I have a database that tracks people based on RFID tags embedded in their shoes. This data arrives in a VARCHAR(MAX) column with other data mixed in, in no particular order. So far, all the RFID ...
6
votes
3answers
549 views
Restoring a database is not working
I have a database called FDB. I have created this database on my PC with SQL Server Express Edition and added one table. I made a backup of this database. The SQL Server version on the server is 2005.
...
2
votes
2answers
266 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 ...
2
votes
1answer
706 views
ALTER TABLE SWITCH statement failed
ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables...
Does anyone knows how to fix that on already existing table full of data?
Well, I've ...
8
votes
1answer
865 views
How can I add a rowversion column to a large table with minimal downtime
Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply
ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL
Then the table is unavailable ...
6
votes
3answers
1k 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
0answers
49 views
Can you recover to a point in time using the LDF? [duplicate]
Possible Duplicate:
How do I get back some deleted records?
A coworker accidentally ran an update statement that needs to be reverted. The last backup is about 12 hours old.
Is there any ...
2
votes
1answer
143 views
IDENTITY_INSERT seems stuck
I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT.
I'm trying to move data between tables, both of which have an identity column (similar schema).
SET ...
2
votes
1answer
57 views
Would appreciate some advice on using external data with SQL Server 2005
I'm a web developer with a bit of DB experience, but this one is beyond my knowledge.
We're trying to set up a new website. The data - products, prices, descriptions etc - are supplied by an external ...
2
votes
2answers
561 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 ...
5
votes
1answer
627 views
Is there a way to grant a user access to only all non system views of database?
In SQL Server, I have a user in a particular database and I've been asked to grant them access to all of the non-system views of the database only. I believe this can be done by editing securables of ...
3
votes
1answer
3k views
SQL Server Activity Monitor - Resource Waits - Logging > 1400 ms/sec, how?
I have an SQL Server 2008 R2 server. I open "Activity Monitor", and inside the "Resource Waits" category, I see that the "Logging" category is taking up more than 1400 ms/sec.
That doesn't seem to be ...
8
votes
1answer
668 views
Monumental difference in execution time between queries when using RECOMPILE query hint
I have two almost identical queries running on the same SQL Server 2005 instance:
The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
5
votes
2answers
2k views
SQL query slow-down from 1 second to 11 minutes - why?
Question: I'm porting the below query (listing tables by foreign key dependencies) to PostGreSql.
WITH Fkeys AS (
SELECT DISTINCT
OnTable = OnTable.name
,AgainstTable = ...
5
votes
2answers
197 views
Running each SQL Server service under different Windows accounts
Besides the Database Engine, SQL Server has some additional Services, namely:
SQL Server Integration Services
SQL Server Analysis Services
SQL Server Reporting Services
SQL Server Agent
Should ...
1
vote
1answer
194 views
Right Tool for Data Mapping in Relation to Business Intelligence
When you are working as a BI developer you also need to create a data mapping between dimensional modeling and source system. What kinda application to visualize a data mapping?
// Fullmetalboy
5
votes
2answers
464 views
Comparing Columns that can contain NULLS - is there a more elegant way?
I know that you can't compare a value to NULL and expect a result without adding something like in the following code...
SELECT
*
FROM
A INNER JOIN
B ON A.ID = B.ID
WHERE
A.STRING ...
1
vote
1answer
188 views
How to propagate a grandparent relationship throughout a tree?
I've got a MySQL table with columns id and parent, wherein each row has another row as a parent, except for some special root rows with parent=0. There are no cycles or broken relations.
In order to ...
2
votes
1answer
196 views
Review Technical ETL process for knowledge management
I'm looking for source code and all related component/material to review technical ETL process between source database and Data Warehouse in order to increase my knowledge in technical Business ...
0
votes
1answer
137 views
Using N-Tier in Business Intelligence
I have read that you also can apply N-Tier for Businesss Intelligence
based on these criteria:
*Presentation
*Functional logic
*Data
What is your experience when you are using N-Tier?
// ...
6
votes
2answers
560 views
Reindex Task Failing Randomly
We use the built-in maintenance plan to reindex (and backup, etc.) our production SQL Server. The problem we are currently having is the reindex task started failing randomly this week after months ...
16
votes
11answers
4k views
Why aren't primary key / foreign key matches used for joins?
As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to ...
0
votes
1answer
149 views
Tips for using DML Statements [closed]
I've recently began learning T-SQL and have found that there are some rules to using single table queries. For instance:
GROUP BY
If aggregate and non-aggregate expressions are used together in a ...
6
votes
4answers
3k views
Can I be automatically notified of prolonged blocking in SQL server?
About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a ...
2
votes
1answer
388 views
Correct SQL Server linked server connection settings to MySQL that will allow inserts
I'm in linked server hell! I've created a linked server to a remote MySQL database. I'm able to query and do updates. However when I attempt an insert I get an error:
"[MySQL][ODBC 5.1 ...
2
votes
4answers
505 views
Remove sub-string from WHERE clause
I have a stored procedure that is using the SUBSTRING function in the WHERE clause.
SELECT DISTINCT
ColumnBB,
ColumnCC
FROM TableAA WITH (NOLOCK)
WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;
...
6
votes
2answers
139 views
How to query the names of encrypted stored procedures?
What query will identify the names of stored procedures which are encrypted? Here's what I got so far -
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
-- need ...
4
votes
1answer
304 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 ...
4
votes
2answers
185 views
Computed column with less size than the “referring column”
I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column ...
3
votes
3answers
3k 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 ...
1
vote
0answers
240 views
Keep original column names in SSRS report designer 3.0?
I have queries returns a lot of (over a hundred) columns which names contain important underscores, >, < and Uppercase/lowercase characters. However, when creating the reports, SSRS designer (3.0) ...
3
votes
1answer
195 views
SSRS set datasource of report model
I created a Report Model which will be consumed by the End users to create ad hoc reports using Report builder tool. The Report Model can be created using the Report Model Project in Business ...
2
votes
1answer
206 views
Oracle Discoverer like app native for SQL Server?
Can someone point me at some web based apps that work like Oracle Discoverer but native for SQL Server. We already the SQL connector running to actually use disco, but was wondering what other ...
1
vote
1answer
275 views
DATETIME2 and IDENTITY as composite key
I am currently developing a new database schema with an opportunity to do things right. The purpose of the development effort is to collect data (events) from various sensors that will be recorded ...
2
votes
1answer
398 views
Can a clustered index with low cardinality cause deadlocks?
I'm looking at a deadlock, which happens when two processes execute an UPDATE statement on the same table. Both transactions are implicit, meaning that they span only the current statement only, which ...
