Most Popular
1500 questions
26
votes
1 answer
Why does a simple loop result in ASYNC_NETWORK_IO waits?
The following T-SQL takes about 25 seconds on my machine with SSMS v17.9:
DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);
SET NOCOUNT ON;
WHILE @outer_loop < 50000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET…
Joe Obbish
- 32,165
- 4
- 71
- 151
26
votes
1 answer
Why Do My Nonclustered Indexes Use More Space When I Delete Rows?
I have a large table with 7.5 billion rows and 5 indexes.
When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on.
I wrote a query against dm_db_partition_stats to report…
Michael J Swart
- 2,059
- 5
- 22
- 31
26
votes
6 answers
How to prove the lack of implicit order in a database?
Recently I was explaining to colleagues the importance of having a column by which to sort data in a database table if it is necessary to do so, for example for chronologically-ordered data. This proved somewhat difficult because they could simply…
anon
26
votes
2 answers
Get a fixed value on a select
I need to do a SELECT query where I get the value of the field "money". The field doesn't actually exist in the database. I just need the query to return this field with a fixed value; in this case a value in USD.
How do I return a constant value…
fedejp
- 263
- 1
- 3
- 4
26
votes
1 answer
Query for all the Postgres configuration parameters‘ current values?
You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands. These are two avenues for writing the settings, but how about reading?
➥ Is there a way to query for…
Basil Bourque
- 10,806
- 20
- 59
- 92
26
votes
1 answer
How do you view PostgreSQL messages (such as RAISE NOTICE) in DBeaver?
I think this is a DBeaver UI/Configuration question, but I cannot see where the messages are when executing a script (Alt-X).
In PGAdminIII, I would execute a script and see the NOTICE output:
In DBeaver, the same script does not output in…
Jay Cummins
- 615
- 3
- 10
- 17
26
votes
2 answers
SQL server DB status runnable, sleeping, suspended, running and background meanings
I am not able to get the meanings of DB Session status. I searched it for some references, but not getting any help.
Can anyone explain these status (runnable, sleeping, suspended, running and background) what actually means, that would be help me…
Manish Kumar
- 371
- 1
- 3
- 5
26
votes
3 answers
SQL Server splits A <> B into A < B OR A > B, yielding strange results if B is non-deterministic
We have encountered an interesting issue with SQL Server. Consider the following repro example:
CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY);
INSERT INTO #test (s_guid) VALUES ('7E28EFF8-A80A-45E4-BFE0-C13989D69618');
SELECT s_guid FROM…
Heinzi
- 3,145
- 2
- 29
- 42
26
votes
7 answers
Any Open Source / free ETL out there?
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 find out my favorite open source etl is not much…
Nicolas de Fontenay
- 1,873
- 2
- 18
- 36
26
votes
6 answers
PostgreSQL alternative to SQL Server’s `try_cast` function
Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error.
This makes it possible to then use a CASE expression or a coalesce to fall back on.…
Manngo
- 2,819
- 8
- 34
- 56
26
votes
4 answers
Should I explicitly DENY UPDATE to columns that should not be updated?
I am used to working in very secure environments and so I design my permissions to a very fine degree of granularity. One thing that I normally do is to explicitly DENY users the ability to UPDATE columns that should never be updated.
For…
Mr.Brownstone
- 13,102
- 4
- 36
- 54
26
votes
1 answer
Multiple databases in same AWS RDS PostgreSQL instance
I have an existing database in a RDS AWS PostgreSQL instance. I need to create one more database in this instance as spinning up a separate cluster will be expensive. There doesn't seem to be any option to create one more database inside this…
Pravs_thedataguy
- 565
- 1
- 5
- 13
26
votes
6 answers
How to efficiently check EXISTS on multiple columns?
This is an issue I come up against periodically and have not yet found a good solution for.
Supposing the following table structure
CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)
and the requirement is to determine…
Martin Smith
- 84,644
- 15
- 245
- 333
26
votes
5 answers
Best way to store units in database
I have inherited a large (SQLServer) database with hundreds of columns that represent amounts of one thing or another. The units for these values (e.g. "gallons", "inches", etc) are stored in the MS_Description field of Extended Properties. I am…
kmote
- 607
- 1
- 6
- 13
26
votes
5 answers
Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?
Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like:
CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle VARCHAR(200) NOT NULL
, PRIMARY KEY (BookID)
) ;
CREATE…
ypercubeᵀᴹ
- 97,895
- 13
- 214
- 305