24
votes
3answers
57k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
24
votes
2answers
12k views

What does a DBA have to know about SSAS?

I've seen a lot of material covering the business aspect of SSAS, but not really much about the important aspects of administration and management. From the point of view of administering an instance ...
24
votes
3answers
45k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
24
votes
4answers
61k views

Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem ...
24
votes
4answers
10k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
24
votes
4answers
10k views

Why is OS authentication considered poor security for Oracle databases?

Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says Be aware that the REMOTE_OS_AUTHENT parameter was deprecated in Oracle Database 11g Release ...
24
votes
1answer
6k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
23
votes
3answers
13k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
23
votes
3answers
3k views

Should every table have a single-field surrogate/artificial primary key?

I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are ...
23
votes
3answers
7k views

Performance difference for COALESCE versus ISNULL?

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
23
votes
2answers
11k views

Check constraint only one of three columns is non-null

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other ...
23
votes
3answers
5k views

Is it possible to have passwords configured per database or per host in .my.cnf

I have the following in my ~/.my.cnf [client] password="somepass" but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different ...
23
votes
4answers
4k views

Does the order of columns in a table's definition matter?

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
23
votes
3answers
16k 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 ...
23
votes
1answer
7k views

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
23
votes
3answers
7k 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 ...
23
votes
4answers
2k views

How do you test for race conditions in a database?

I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to ...
22
votes
10answers
648 views

What is an effective way of labeling columns in a database?

I used to label columns in my databases like this: user_id user_name user_password_hash To avoid conflicts when joining two tables, but then I learnt some more on how to alias tables, and I stopped ...
22
votes
4answers
8k views

SQL Server “empty table” is slow after deleting all (12 million) records?

I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
22
votes
2answers
3k views

Why does query error with empty result set in SQL Server 2012?

When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail ...
22
votes
3answers
2k views

difference in execution plans on UAT and PROD server

I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs). Both UAT and PROD are having exactly data and indexes. ...
22
votes
4answers
13k views

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
22
votes
7answers
8k views

Any Open Source / free ETL out there? [closed]

I was using Pentaho Data Integration even before Pentaho bought it and call it that. I have the last free version. I went on their website recently to see if they had released another version only to ...
22
votes
2answers
21k views

What is the actual behavior of compatibility level 80?

Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected. As far as I understand compatibility modes, it is about the ...
22
votes
2answers
4k views

How to make a transition from SQL Server DBA to Oracle?

As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do? I would assume the concepts are the same and the ...
22
votes
1answer
37k views

What is the best way to reduce the size of ibdata in mysql?

I have some Production servers whose ibdata files increases in size day by day. It has already consumed 290GB of space. The tables in the servers are mostly InnoDB and there are high read and write ...
22
votes
8answers
9k views

How do long columns impact performance and disk usage?

In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on. In reality, how much does it really matter? How ...
22
votes
2answers
2k views

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever ...
22
votes
4answers
55k views

How do I list or search all the column names in my database?

I want to search for a string in the names of the columns present in a database. I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking ...
22
votes
3answers
1k views

Are RANK() and DENSE_RANK() deterministic or non-deterministic?

According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan "... the RANK() and DENSE_RANK() functions are always deterministic". ...
22
votes
1answer
39k views

How to force drop database in SQL Server 2008

I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error cannot create file C:\Program Files.....[databasename].mdf because ...
22
votes
6answers
18k views

SQL Server load balancing

What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programatic access. Is this built in or does a solution have to be purchased? I'm trying to ...
22
votes
5answers
8k views

Handling concurrent access to a key table without deadlocks in SQL Server

I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables. Each row in the table stores the last used ID LastID for the field named in IDName. ...
22
votes
1answer
7k views

Is there an analog of GETDATE() that returns DATETIME2

According to MSDN, Getdate(), GetUtcDate(), and CURRENT_TIMESTAMP all return DATETIME. I ran a short test, which confirms that: CREATE TABLE #t(T DATETIME2(7)); GO DECLARE @i INT ; SET @i=1; WHILE ...
22
votes
6answers
8k views

What problems will I get creating a database per customer?

I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases. We are just ...
22
votes
5answers
20k views

What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?

I'm in a situation where I want to get the minimum value from of 6 columns. I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like ...
22
votes
2answers
28k views

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema ...
22
votes
3answers
2k views

Excessive sort memory grant

Why is this simple query granted so much memory? -- Demo table CREATE TABLE dbo.Test ( TID integer IDENTITY NOT NULL, FilterMe integer NOT NULL, SortMe integer NOT NULL, Unused ...
22
votes
3answers
10k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
22
votes
3answers
3k views

Are there disadvantages to contained databases?

SQL Server 2012 introduced the concept of "contained" databases, where everything (well, mostly everything) the database needs is contained within the database itself. This offers big advantages when ...
22
votes
2answers
13k views

Timeseries: SQL or NoSQL?

I don't care about the general differences between SQL and NoSQL (or their traditional differences). I am currently looking at altering the storage of our internal time series. They all contain ...
21
votes
13answers
715 views

The Value of Certifications [closed]

Questions What certifications do you have? Has your certification (or lack of one) ever worked for or against you? Do you intend to reach out for certification? Do you renew your certifications? ...
21
votes
7answers
39k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
21
votes
4answers
89k views

Restore SQL Server 2012 backup to a SQL Server 2008 database?

Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008? I tried to attach file, it does not work.
21
votes
3answers
9k views

Is the CPU performance relevant for a database server?

This is a purely theoretical question. Let's say I have an application deployed on multiple servers. A load balancer, Multiple/scalable applications servers A (single) database server (for the ...
21
votes
3answers
4k views

Efficency of stored procedures vs raw queries

I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would ...
21
votes
7answers
2k views

What is the most efficient method of performing the FIZZBUZZ test in SQL Server?

This may not be completely on-topic, however it's a slow day here. Is there a more efficient method of obtaining a list of numbers from 1 to 49 with a column containing the words FIZZ when the number ...
21
votes
2answers
3k views

As a DBA, how would I go about transitioning from Oracle to SQL Server?

I'm an Oracle DBA that also has Sybase experience. What are the major architectural and conceptual differences between the two RDBMS platforms? An answer similar to the SQl Server->Oracle question ...
21
votes
9answers
56k views

PostgreSQL is running locally but I cannot connect. Why?

Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
21
votes
2answers
19k views

What is the purpose of the database 'owner'?

Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query ...

15 30 50 per page