Most Popular
1500 questions
21
votes
1 answer
How to get the MAX row
In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability.
Sample Table:
DECLARE @Test TABLE (ID INT…
Andrew Bickerton
- 3,234
- 5
- 29
- 38
20
votes
2 answers
Why adding SQL Server user to "Perform volume maintenance tasks" improves the speed of database resizing so much?
If I want to create 5GB database with
CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME =…
Nikolay Kostov
- 321
- 1
- 3
- 10
20
votes
2 answers
Query to List Encryption Certificate for Databases
What certificate is being used to encrypt each of the databases on the instances.
I can get the data using the following but how do I write the queries
USE master
GO
-- this provides the list of certificates
SELECT * FROM sys.certificates
-- this…
Barry MSIH
- 303
- 1
- 2
- 6
20
votes
5 answers
Infrastructure for Highly Concurrent, High Write DB
My requirements are:
3000 Connections
70-85% Write vs Read
Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of concurrent connections as the memory is fine. The write…
Justin
- 301
- 2
- 3
20
votes
2 answers
Scaling PostgreSQL TRIGGER(s)
How Postgres triggers mechanism scales ?
We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s).
Basically we would like to create a TRIGGER for each table we want to be…
Ugo Matrangolo
- 301
- 1
- 2
- 3
20
votes
2 answers
How to measure or find cost of creating a query plan?
I have a typical case where parameter sniffing causes a "bad" execution plan to land in the plan cache, causing subsequent executions of my stored procedure to be very slow. I can "solve" this problem with local variables, OPTIMIZE FOR ... UNKNOWN,…
Jeroen
- 879
- 2
- 14
- 33
20
votes
1 answer
What are the optimal mysqldump settings?
After some search I ended up with the following settings:
mysqldump\
--host=localhost\
--port=3306\
--databases ****\
--user=****\
--password=****\
--default-character-set=utf8\
--add-drop-database\
…
user706838
- 323
- 1
- 3
- 7
20
votes
4 answers
Why are NULLs sorted first?
Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first?
select 1 as test
union all
select 2
union all
select NULL
union all
select 3
union all
select 4
order by test
results…
Richard
- 6,393
- 8
- 42
- 62
20
votes
5 answers
How to recursively find gaps where 90 days passed, between rows
This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this.
SELECT SomeId, MyDate,
dbo.udfLastHitRecursive(param1,…
Independent
- 213
- 2
- 10
20
votes
2 answers
What are database statistics, and how can I benefit from them?
I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?
goric
- 1,626
- 3
- 18
- 25
20
votes
3 answers
Does "WHERE 1=1" usually have an impact on query performance?
I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).
Generally speaking, does this addition to a SQL statment…
transistor1
- 333
- 1
- 2
- 8
20
votes
2 answers
Detecting the locked table or row in SQL Server
I'm trying to understand/learn how to track down the details of a blocked session.
So I created the following setup:
create table foo (id integer not null primary key, some_data varchar(20));
insert into foo values (1, 'foo');
commit;
Now I…
user1822
20
votes
2 answers
Write a slow query to test slow query logging?
Is there a simple query that would take > 2 sec so that I can test the slow query logger?
I am looking for something like a generic recursive or iterative statement.
David LeBauer
- 3,142
- 8
- 30
- 34
20
votes
2 answers
How to backup large MongoDB database
What is the recommended way to back up large data sets in MongoDB? Let's say we have a data size in the order of 10TB - how would you back that up?
We're considering a hidden, possibly delayed, replica set node. The delay would protect us from…
Malakim
- 303
- 1
- 2
- 6
20
votes
1 answer
Multiple User Types - DB Design Advice
I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. Some of the fields that each user has will be the…
tptcat
- 303
- 2
- 3
- 6