Most Popular
1500 questions
43
votes
7 answers
How to cast an integer to a boolean in a MySQL SELECT clause?
I'm new here so be kind to me. I have the following scenario:
I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value in this view representing if it is one kind of…
Bruno
- 533
- 1
- 4
- 5
42
votes
5 answers
Why does a UNIQUE constraint allow only one NULL in SQL Server?
In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should allow any number of NULLs?
user87166
- 563
- 1
- 4
- 6
42
votes
1 answer
restoring Postgres database: pg_restore -vs- just using psql
I am dumping my Postgres database using pg_dump (in plain-text format) and then restore it simply by using psql (with the -f option).
Which begs the question: am I missing anything by not using pg_restore which seems like a specialized restore tool…
Marcus Junius Brutus
- 3,309
- 7
- 29
- 44
42
votes
7 answers
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 do my database development without issue.
Why…
Nick Chammas
- 14,670
- 17
- 75
- 121
42
votes
2 answers
NULL or NOT NULL by default?
In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls? Or doesn't it matter?
I know in some DBMSs they say to use Not Null as much as possible because…
BenV
- 4,893
- 7
- 39
- 38
42
votes
3 answers
Using PostgreSQL 8.4, how to convert bytea to text value in postgres?
In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly fine i.e. results in Octet format String. See…
Amit
- 571
- 2
- 5
- 10
42
votes
1 answer
SIMPLE or FULL recovery model for databases?
When should I use the full recovery model and when should I use the simple recovery model for databases?
I always used the full recovery model because it is the default, but today i encountered this error:
Microsoft OLE DB Provider for SQL Server…
MicBehrens
- 769
- 1
- 8
- 19
42
votes
1 answer
Why does CREATE INDEX ... WITH ONLINE=ON block access to the table over a period of minutes?
I have an existing table:
CREATE TABLE dbo.ProofDetails
(
ProofDetailsID int NOT NULL
CONSTRAINT PK_ProofDetails
PRIMARY KEY CLUSTERED IDENTITY(1,1)
, ProofID int NULL
, IDShownToUser int NULL
, UserViewedDetails…
Hannah Vernon
- 70,041
- 22
- 171
- 315
42
votes
3 answers
Difference between SQL batch, statement and RPC?
What is the difference between SQL batch, T-SQL statement and Remote Procedure Call?
How can I tell if part of the T-SQL code is a batch or statement?
FrogEye
- 423
- 1
- 4
- 4
42
votes
2 answers
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about one million rows
Values in column c1 can have about 100…
jap1968
- 721
- 1
- 6
- 12
42
votes
2 answers
postgresql: how to define a JSONB column with default value
I am unable to find in the documentation how to create
a JSONB column in PostgreSQL that has a DEFAULT value of
an empty json document.
How the above can be stated in the CREATE TABLE definition ?
nskalis
- 1,611
- 4
- 13
- 12
42
votes
3 answers
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.
urlreader
42
votes
3 answers
Execution Plan Basics -- Hash Match Confusion
I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join:
select Posts.Title, Users.DisplayName
From Posts JOIN Users on
Posts.OwnerUserId = Users.Id
OPTION (MAXDOP 1)
As…
Kyle Brandt
- 2,345
- 8
- 29
- 37
42
votes
3 answers
postgres: permission denied for schema
I am new to postgresql and I would grateful if
you could please advise on how-to resolve the following error..
I have issued the followed commands:
ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA doc FROM PUBLIC ;
ip_spotlight-# REVOKE…
nskalis
- 1,611
- 4
- 13
- 12
41
votes
8 answers
Is select * still a big no-no on SQL Server 2012?
Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit.
Is this still the case in later versions of SQL Server (I'm using 2012, but I guess the question…
Piers Karsenbarg
- 949
- 2
- 11
- 23