Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
110
votes
6 answers

Difference between database vs user vs schema

What is the difference (if any) between a database, a user, and a schema?
Ravi
  • 1,565
  • 4
  • 15
  • 20
109
votes
4 answers

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to connect to that database local_SID = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port=…
Louis Rhys
  • 1,285
  • 4
  • 11
  • 8
108
votes
18 answers

Is adding the ‘tbl’ prefix to table names really a problem?

I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’. On the internet I found some blogs saying it adds nothing to documentation, and also that “it takes longer to read…
Racer SQL
  • 7,546
  • 16
  • 77
  • 140
107
votes
7 answers

Why use both TRUNCATE and DROP?

In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost all of whom are much more experienced than I am)…
user606723
  • 1,526
  • 4
  • 14
  • 16
106
votes
9 answers

Where are some useful SQL puzzles to teach SQL in a workplace?

I'm looking for beginner and intermediate level SQL puzzles, that I can point trainees at for practice. I'm aware of http://sqlzoo.net/ which is a great resource - is there anything else out there that you could suggest?
testerab
  • 477
  • 3
  • 8
  • 13
105
votes
6 answers

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the number of rows to list might vary per group (defined by…
Paul White
  • 94,921
  • 30
  • 437
  • 687
104
votes
8 answers

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: SELECT COALESCE(c.FirstName ,(SELECT…
Curtis
  • 1,265
  • 2
  • 8
  • 9
102
votes
3 answers

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE and ON DELETE CASCADE My Parent Table CREATE…
Smokey
  • 1,131
  • 2
  • 8
  • 8
101
votes
3 answers

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, description…
Stéphane
  • 5,745
  • 5
  • 20
  • 13
100
votes
4 answers

Are views harmful for performance in PostgreSQL?

The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6): The danger with using views is filtering a query against a view, expecting to read a very small portion of a very large table. Any filtering…
ARX
  • 1,509
  • 3
  • 14
  • 15
98
votes
4 answers

SQL server databases stuck in restoring state

I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state! Is it possible to stop the restoring process? and also, How can I make sure the database integrity has not been compromised?…
Pooya Yazdani
  • 1,093
  • 1
  • 8
  • 7
98
votes
5 answers

When to use TINYINT over INT?

In general, I always use Ints. I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data. For example, it's better to use tinyint when you know that the only…
Richard
  • 1
  • 8
  • 42
  • 62
98
votes
4 answers

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column? Edit: Not a dupe of: Would index lookup be noticeably faster with char vs…
Daniel Serodio
  • 1,325
  • 3
  • 12
  • 13
97
votes
8 answers

How to query last restore date in SQL Server?

Is there a T-SQL query that shows the last restore datetime for a certain database?
Kees C. Bakker
  • 1,253
  • 1
  • 9
  • 9
96
votes
5 answers

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to user table Are these equivalent? If not then why?…
codecool
  • 2,023
  • 2
  • 17
  • 22
1 2 3
…
99 100