23
votes
7answers
5k views
Why should I use Visual Studio 2010 over SSMS for my database development?
Visual Studio 2010 introduces database projects and a whole array of related features that supposedly facilitate database development. I've used SQL Server Management Studio (SSMS) for many years to ...
23
votes
1answer
1k views
What's the point of WHERE 1=2 for SELECT INTO table query
If we want to create a new table from an existing one in SQL Server we can do the following
SELECT * into Table2
from Table1
WHERE 1=2
Whats the point of where clause? I tried it without the where ...
23
votes
5answers
2k views
When is the right time to use MariaDB instead of MySQL, and Why?
What we earn and what we lost with this migration?
What should I expect as drawbacks after the migration?
Is it really unnecessary to change the applications in any situation?
23
votes
5answers
2k views
Why do sequential GUID keys perform faster than sequential INT keys in my test case?
After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with ...
23
votes
2answers
769 views
Where can I find database tuning exercises for learning?
As a developer, often DBAs take responsibility for resolving performance issues at the database level so we don't get that much experience diagnosing, tuning, refactoring queries etc.
I'm looking ...
22
votes
7answers
4k views
Is it better to store images in a BLOB or just the URL? [duplicate]
Possible Duplicate:
Files - in the database or not?
I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch ...
22
votes
7answers
13k views
How do I completely disable MySQL replication
I am running dual master MySQL replication and now want to move to a single database with no replication. How do I completely disable replication on both databases?
22
votes
5answers
23k views
What is a Key/Value store database?
I've been looking at the wikipedia page for NoSQL and it lists several variations on the Key/Value store database, but I can't find any details on what it means by Key/Value store in this context. ...
22
votes
3answers
1k views
Performance of Non Clustered Indexes on Heaps vs Clustered Indexes
This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap ...
22
votes
3answers
9k views
N prefix before string in Transact-SQL query
Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this
SELECT * FROM ...
22
votes
1answer
458 views
In SQL Server, what is the purpose of grouping stored procedures?
One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by ...
22
votes
4answers
797 views
Is there a “best-practices” type process for developers to follow for database changes?
What is a good way to migrate DB changes from Development to QA to Production environments? Currently we:
Script the change in a sql file and attach that to a TFS work item.
The work is ...
21
votes
9answers
18k views
Passing array parameters to a stored procedure
I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
21
votes
7answers
1k views
What is the difference between a “record” and a “row” in SQL Server?
There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate.
So how do we differentiate between these related terms and how we ...
21
votes
20answers
716 views
How to dive into an ugly database?
I'm sure many of you are/were dealing with a ugly database. You know, that database that isn't normalized at all, that database where you have to do a large painfully query to get the most trivial ...
21
votes
5answers
2k views
What database technologies do big search engines use?
Does anybody knows how Google or Yahoo perform searches for keywords against very very huge amounts of data? What sort of database or technologies do they employ for this?
It takes few milliseconds, ...
21
votes
4answers
1k views
Is NOLOCK always bad?
I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to ...
21
votes
4answers
6k views
When should a primary key be declared non-clustered?
While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED
When would you use a NONCLUSTERED primary key over a ...
21
votes
4answers
1k views
Still wrong to start the name of a user stored procedure with sp_?
One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
21
votes
2answers
2k views
Find Queries that Are Causing Waits
Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server?
In this particular case I am wondering about async_network_io.
21
votes
3answers
22k views
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
...
20
votes
10answers
539 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 ...
20
votes
7answers
2k views
Why should I create an ID column when I can use others as key fields? [duplicate]
Possible Duplicate:
Why use an int as a lookup table's primary key?
So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not ...
20
votes
8answers
3k views
Tool to generate large datasets of test data
Many times when trying to come up with an efficient database design the best course of action is to build two sample databases, fill them with data, and run some queries against them to see which one ...
20
votes
5answers
31k views
How do you mysqldump specific table(s)?
How can I dump a specific table or set of tables without including the rest of the db tables?
20
votes
6answers
1k views
In what data type should I store an email address in database?
I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent. For example: this SQL Server recommendation ...
20
votes
4answers
2k views
What does “index” mean on RDBMSs? [closed]
I use indexes like most developpers do (mostly on... well ! index), but i'm sure there is a lot of subtle way to optimize a database using index. I'm not sure if it is specific to any implementation ...
20
votes
1answer
937 views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
20
votes
1answer
426 views
Reporting Service and Application Role
First poster, long time lurker here.
What is the best way to activate application role in a report ?
I've tried different things and so far the only method that works is to embed the call to the ...
19
votes
13answers
2k views
How to implement a 'default' flag that can only be set on a single row
For example, with a table similar to this:
create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));
It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
19
votes
5answers
4k views
Best practices on common person fields (Name, email, address, gender etc…)
What are the most common best practices on length and data type on common fields like:
First Name
Last Name
Address
Email
Sex
State
City
Country
Phone Number
etc....
19
votes
2answers
1k views
Why are Denali sequences supposed to perform better than identity columns?
In his answer to sql2005 whats better: identity columns or generated uinique id values?
mrdenny mentions, says
When SQL Denali comes out it will
support sequences which will be more
efficient ...
19
votes
7answers
4k views
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
I am writing the schema for a simple bank database. Here are the basic specifications:
The database will store transactions against a user and currency.
Every user has one balance per currency, so ...
19
votes
3answers
2k views
Character vs Integer primary keys
I'm designing a database with multiple lookup tables containing possible attributes of the main entities. I'm thinking of using a 4 or 5-character key to identify these lookup values rather than an ...
19
votes
3answers
3k 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 ...
19
votes
4answers
707 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 ...
18
votes
5answers
3k views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)
After working with ...
18
votes
6answers
4k views
What's the difference between a CTE and a Temp Table?
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?
CTE
WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, ...
18
votes
2answers
3k 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 ...
18
votes
5answers
3k views
What are some ways to implement a many-to-many relationship in a data warehouse?
The dominant topologies of Data Warehouse modelling (Star, Snowflake) are designed with one-to-many relationships in mind. Query readability, performance, and structure degrades severely when faced ...
18
votes
4answers
5k 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 ...
18
votes
5answers
5k views
SQL: SELECT All columns except some
Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.
Something like:
SELECT * ...
17
votes
12answers
385 views
The Value of Certifications
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? ...
17
votes
5answers
679 views
Should Query Tuning be Proactive or Reactive?
As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server). I make the best ...
17
votes
4answers
2k 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 ...
17
votes
3answers
2k 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 ...
17
votes
3answers
2k 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 ...
17
votes
3answers
12k views
What is the difference between sys and system accounts in Oracle databases?
There are two ways to connect to Oracle as a administrator using sqlplus:
sqlplus sys as sysdba
sqlplus system/manager
These accounts should be uses for different purposes, I suppose.
Which tasks ...
17
votes
5answers
6k 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 ...
17
votes
7answers
5k 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 ...
