Most Popular
1500 questions
18
votes
1 answer
Why does this derived table improve performance?
I have a query which takes a json string as a parameter. The json is an array of latitude,longitude pairs.
An example input might be the following.
declare @json nvarchar(max)=…
Michael B
- 519
- 2
- 12
18
votes
2 answers
Sql Server - Best Practices for Growing Database Files
I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 GB.
The DB files auto growth is set to 5MB and I…
Ross Bush
- 405
- 1
- 4
- 14
18
votes
1 answer
Single-row INSERT...SELECT much slower than separate SELECT
Given the following heap table with 400 rows numbered from 1 to 400:
DROP TABLE IF EXISTS dbo.N;
GO
SELECT
SV.number
INTO dbo.N
FROM master.dbo.spt_values AS SV
WHERE
SV.[type] = N'P'
AND SV.number BETWEEN 1 AND 400;
and the…
Paul White
- 67,511
- 25
- 368
- 572
18
votes
2 answers
Disk space full during insert, what happens?
Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb which grows till the disk is full. This time it was a…
HoneyBadger
- 357
- 4
- 10
18
votes
3 answers
Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?
I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code inside each one.
Is there a best way to do this…
Alex Erwin
- 633
- 1
- 5
- 12
18
votes
4 answers
Set a column's default value to the concatenation of two other columns' values?
I have a Postgresql 8.1 database. In one table, there are three columns: first_name, last_name, display_name.
Is it possible to set the default value of display_name to be first_name + " " + last_name?
MattSayar
- 283
- 1
- 2
- 6
18
votes
4 answers
Enable TLS 1.2 for SQL Server 2016 database mail
I have been puzzled by this issue for almost 1 week. Hopefully someone in our community has experienced the same issue and already found a solution.
So here is my problem:
As per our company policy, we want database mail to be able to send emails…
jyao
- 2,895
- 1
- 9
- 26
18
votes
8 answers
Must-read Books for DBA
This question is almost mandatory in any SE site for basic reference.
Please post only books with permanently value for DBAs in general.
The post should be a must read book, the classical ones, the foundation books, not books for specific products…
Maniero
- 2,478
- 6
- 26
- 29
18
votes
3 answers
How to store 'n' days of web server logs in Sql Server?
For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time.
We have a daemon that listens for request/response events from our…
Jarrod Dixon
- 283
- 3
- 11
18
votes
4 answers
Unquoting JSON strings; print JSON strings without quotes
SELECT json_array_elements('["one", "two"]'::json)
gives result
| json_array_elements |
| :------------------ |
| "one" |
| "two" |
I would like to have the same but without the quotes:
one
two
Looks like I can't use…
Maxim Yefremov
- 305
- 1
- 2
- 6
18
votes
1 answer
Multiple on conflict targets
I have two unique indexes on columns a and b. I need something like this:
insert into my_table (a, b) values (1, 2), (1, 2)
on conflict (a) do update set c = 'a_violation'
on conflict (b) do update set c = 'b_violation'
So generally I want to make…
user606521
- 1,225
- 5
- 17
- 26
18
votes
1 answer
PostgreSQL two different ways to generate a UUID: gen_random_uuid vs uuid_generate_v4?
What is the difference between the following two functions
gen_random_uuid() provided by pgcrypto extension
uuid_generate_v4() provided by uuid-ossp extension
Are they both the same behind the scenes? Any performance impacts on using one?
user148246
18
votes
2 answers
FIFO queue table for multiple workers in SQL Server
I was attempting to answer the following stackoverflow question:
What SQL Server 2005/2008 locking approach should I use to process individual table rows in multiple server application instances?
After posting a somewhat naive answer, I figured…
ErikE
- 4,045
- 4
- 25
- 39
18
votes
1 answer
What is the quickest way to purge data?
Scenario:
We have two tables Tbl1 & Tbl2 on the Subscriber Server. The Tbl1 is being replicated from Publisher Server A and it has two triggers - insert and update. The triggers are inserting and updating the data into Tbl2.
Now, we have to purge…
Dharmedra Keshari
- 253
- 1
- 3
- 6
18
votes
3 answers
Dynamically Define a Range in a Dimension
I have an issue that I face every time I decide to build a cube, and I haven't found a way to overcome it yet.
The issue is how to allow the user to define a range of things automatically without having the need to hardcode them in the dimension. I…
AmmarR
- 2,756
- 3
- 22
- 32