Most Popular
1500 questions
51
votes
5 answers
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 comes to figuring out which other tasks I want…
Josh
- 673
- 1
- 6
- 7
51
votes
5 answers
Data obfuscation in SQL Server
What is the best practice for Data Obfuscation in SQL Server?
We'd like to use masked Production data in our UAT system.
If we want to do it quickly, and with a higher level of obsfucation, what approach should be taken? I'm thinking about…
Sky
- 3,684
- 16
- 49
- 68
51
votes
3 answers
How do you grant execute permission for a single stored procedure?
Normally when I create a stored procedure I use the following as a template of sort
Create procedure
<@param1 , @param2 , etc..>
as begin
end
Is there a way to include granting execute permission on only…
DaneEdw
- 613
- 1
- 5
- 6
51
votes
2 answers
Convert right side of join of many to many into array
When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row.
Example with 3 tables:
CREATE TABLE items (
id serial primary…
Ced
- 724
- 2
- 7
- 10
51
votes
10 answers
restore table from .frm and .ibd file?
I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-server, I believe version 5.1 was installed. After I…
Tong Wang
- 613
- 1
- 6
- 5
51
votes
4 answers
What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?
My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.
Our requirements:
Standard edition (for financial reasons…
usr
- 7,330
- 5
- 32
- 58
51
votes
4 answers
SQL Server commands to clear caches before running a performance comparison
When comparing the execution time of two different queries, it's important to clear the cache to make sure that the execution of the first query does not alter the performance of the second.
In a Google Search, I could find these commands:
DBCC…
andrerpena
- 961
- 2
- 9
- 12
50
votes
6 answers
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined below) but still got these errors:
pg_restore:…
KSletmoe
- 657
- 1
- 6
- 7
50
votes
2 answers
Refresh materalized view incrementally in PostgreSQL
Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?
Consider this table & materialized view:
CREATE TABLE graph (
xaxis integer NOT NULL,
value integer NOT…
user4150760
- 1,099
- 3
- 14
- 20
50
votes
6 answers
Why do we use Group by 1 and Group by 1,2,3 in SQL query?
In SQL queries, we do use Group by clause to apply aggregate functions.
But what is the purpose behind using numeric value instead of column
name with Group by clause? For example: Group by 1.
ursitesion
- 2,071
- 7
- 32
- 45
50
votes
3 answers
How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?
I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
leeand00
- 1,702
- 5
- 20
- 36
50
votes
4 answers
What is the difference between sys and system accounts in Oracle databases?
There are two ways to connect to Oracle as an administrator using SQL Plus:
sqlplus sys as sysdba
sqlplus system/manager
These accounts should be used for different purposes, I suppose.
Which tasks are these two schemas meant for? When should I…
Lazer
- 3,351
- 15
- 42
- 53
50
votes
5 answers
Why would I NOT use the SQL Server option "optimize for ad hoc workloads"?
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why is there even an option to "optimize for ad hoc…
SomeGuy
- 2,003
- 8
- 34
- 46
50
votes
20 answers
Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect
I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?
I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL Workbench (PC) offers to make a connection via TCP…
Dizzley
- 601
- 1
- 5
- 3
50
votes
3 answers
How do I know what indexes to create for a table?
Is there a way I can figure out the best way to know which indexes to create for a table?
Nick Ginanto
- 929
- 3
- 9
- 10