Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
21
votes
1 answer

Seek and you shall Scan... on partitioned tables

I've read these articles in PCMag by Itzik Ben-Gan: Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize Seek and You Shall Scan Part II: Ascending Keys I’m currently having a "Grouped Max" problem with all of our partitioned…
Henrik Staun Poulsen
  • 2,249
  • 2
  • 23
  • 41
21
votes
3 answers

Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

First Words You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at the edit history before 2015-10-06 if you want to…
mpag
  • 978
  • 7
  • 23
21
votes
2 answers

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255), when VARCHAR(30) will probably do. I consistently see agreement that there's a…
Eric J.
  • 478
  • 3
  • 12
21
votes
1 answer

Statistics disappear after incremental update

We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the statistics disappear after the index is rebuilt. Below…
JasonR
  • 313
  • 1
  • 7
21
votes
2 answers

MySql - changing innodb_file_per_table for a live db

I have a large MySql DB (150GB) and only now I've noticed that the innodb_file_per_table is set to off which causes the entire DB to be hosted on one single file (ibdata1). I want to activate innodb_file_per_table and have it retroactively split the…
Ran
  • 1,573
  • 9
  • 21
  • 35
21
votes
4 answers

List primary keys for all tables - Postgresql

Is there a query that will do that? I found some queries that can do this for one table, but I wasn't able to modify it so I can see: tablename | column | type
korda
  • 635
  • 2
  • 7
  • 11
21
votes
3 answers

Is it OK to blindly add missing indexes?

I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster query every time as far as I can tell. Any…
O.O
  • 678
  • 2
  • 7
  • 16
21
votes
4 answers

Determining how a schema change occurred?

Something bad happened yesterday. A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this modification in PRODUCTION database. My Question: Is…
xorpower
  • 577
  • 1
  • 8
  • 18
21
votes
5 answers

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- AC - AllCount DECLARE AC INT DEFAULT 0; …
Mark D
  • 1,140
  • 4
  • 16
  • 28
21
votes
3 answers

How to print to console in SQL?

I'm using PostgreSQL And I want to print a message to the console. If I use plpythonu I use plpy.notice If I use plpgsql I use raise notice but how do I print when the function is pure SQL? using SELECT 'string' isn't helping me as it print the…
aayushdagra
  • 329
  • 1
  • 2
  • 4
21
votes
1 answer

What's the anatomy of a columnstore index?

One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index. I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between the leaf level and b-tree pages, affects of…
JNK
  • 18,064
  • 6
  • 63
  • 98
21
votes
5 answers

Azure SQL Database "Login failed for user" in application, but works fine in SSMS

I wanted to try out the contained database users feature on Azure SQL Database V12, but I'm having a problem authenticating that seems odd to me. I created a database called Classifier. I added my IP to the firewall rules so I could connect to…
Ben Collins
  • 312
  • 1
  • 2
  • 9
21
votes
6 answers

In Sql Server, is there a way to check if a selected group of rows are locked or not?

We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large update/delete activity is being blocked for extended…
ToC
  • 727
  • 1
  • 8
  • 20
21
votes
6 answers

How to prevent transaction log getting full during index reorganize?

We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. The main reason I want to reorganize is to…
Sriram Subramanian
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,254
  • 5
  • 30
  • 38
1 2 3
…
99 100