Update all tables containing column name where the field is something specific I know a similar question has been asked before but i was wonder if there is any way that you could run an update across all tables in a database where a column name exists but only for fields with a specific entry.

So for example UPDATE <table> SET <column_name> = '123' where <column_name> = '124'

Thanks in advance for everyones help

This answer will require some dynamic SQL and potentially could make use of a while/do loop.
Basically the idea will be to figure our how to define your list of tables and column names.  If you really are just looking for a list of tables where a column name exists, you will probably want to make use of the sys.syscolumns table to query the column names.

SELECT o.name
FROM sys.syscolumns c
    JOIN sys.sysobjects o ON c.id=o.id
WHERE UPPER(c.name) = 'Description'

From there you can build your dynamic sql statement.

DECLARE @sqltext VARCHAR(MAX)
DECLARE @UpdateValue VARCHAR(30) = 'SetThisValue'

SELECT @sqltext = ISNULL(@sqltext,'') + 'UPDATE '+o.name + CHAR(10) + ' SET     [YourColumnNameHere] = ' + @UpdateValue + CHAR(10)
    + ' WHERE [YourColumnNameHere] = [Somevalue]'
FROM sys.syscolumns c
    JOIN sys.sysobjects o ON c.id=o.id
WHERE UPPER(c.name) = 'Description'

PRINT @sqltext
EXEC sp_executesql @sqltext

I have the print statement in there to help you get the @sqltext exactly the way you want it.  Once you have it as you want, use the EXEC sp_executesql line to actually run the output of the @sqltext.


Select statement take more time in sql Statement 1 : select top 1000 pmch.Columnname1
Statement 2 : select top 1000 pmch.*
Statement 2 executes quicker the  Statement 1.
I know that using "SELECT * " will affect performance so should use column names instead of " * ".
But, here it works differently.
Can anyone suggest under which condition these type of issue occurs?

A large difference in time makes me think there is significantly different IO happening for these two queries.
The second query qualifies for trivial optimization. Likely it is performing a table scan, on the heap or a clustered index.
The first query projects only a single column. If there is an index with that column as a leading key the optimizer is likely to choose to read from that index as doing so will require fewer IOs.
So how do fewer IOs result in longer elapsed times? Let's suppose the base table's pages are sequentially ordered on-disk and can be efficiently scanned, perhaps with pre-fetching. This can be fast. Further, let's suppose the index is badly fragmented on-disk so each new page read requires an on-disk seek. This will be slow.
In summary, statement 1 uses an index scan on a badly fragmented index suffering slow seek times while statement 2 uses efficient sequential disk scan.
To be sure look at the two query plans. If I'm correct the respective objects read will be obvious in each plan. The amount of fragmentation can be found from the documentation. That link also gives the solution. Note that fragmentation need not be a problem, depending on the hardware and workload.
Fragmentation can occur due to rows being added in the middle of the key range. This is a natural part of application usage. It can be mitigated by an appropriate fill factor. It can also happen when database files are shrunk. Don't do that.


BLOBs or references in PostgreSQL I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files will increase over time. I may at times need to extract data from the files for other downstream analyses.

I've done some research regarding the age-old question of storing binary data as BLOBs or references. Both obviously have their pros and cons. Are there any specific issues relating to PostgreSQL that I should be aware of? Is one method or the other preferable if I want to extract data from the files, either via a PostgreSQL function or via an external Python program?

If I were to store the data files directly in the database, would it be better to store them in a separate table with a foreign key referencing the "main" table, rather than in the table containing all the other fields?

I have read the question and answers here; a comment there suggests that storing binary files by reference (in the filesystem) on Linux is better. My questions here relate to PostgreSQL specifically and to extracting data from the files for various analyses.

Update: Similar question.

I think you should store the data in the database as a normal bytea column.  That way you get all the advantages of a database, and you can process the data using database functions (and even PL/Python, if you want that).  Larger data items will automatically be stored out of line, so there would be no reason for you to introduce another reference indirection.

The main reasons for storing large binary objects outside the database would be if they are too large to be able to store and retrieve them in a satisfactory time, if they bloat the database beyond practicality, or if you need to access the files as files from a separate application.  None of that applies there, as far as I can tell.


How to replace "set rowcount" Currently on SQL Server 2008/R2. I just read here that "set rowcount" is being deprecated in some post 2012 release:
http://technet.microsoft.com/en-us/library/ms188774.aspx

We have some legacy code that uses it presumably to delete small chunks of data and to minimize locking in our nightly "Data Purge" job.

Without rowcount, I would have to use "top xxx" instead?

  set rowcount 1000 /* Only delete this number of rows at a time */;
  declare @short_time datetime2 = DATEADD(week,-1,SYSUTCDATETIME());

      SET @TotalRowCount = 0
      while ( select COUNT(1) from FlightAudit with (nolock) ) > @min_row_count
      begin
            delete FlightAudit where CreatedUTC < @short_time;
            SET @SaveRowCount = @@ROWCOUNT
            SET @TotalRowCount = @TotalRowCount + @SaveRowCount
            if @SaveRowCount=0 break;
            print 'delete from FuelerCertificationAudit...' + convert(varchar(10),@SaveRowCount) + ' Time=' + dbo.DateOrNullToChar(getdate())
      end
      print 'Total deleted from FlightAudit...' + convert(varchar(10),@TotalRowCount) + ' Time=' + dbo.DateOrNullToChar(getdate())

Yes. Just remove the set rowcount and use

DELETE TOP (1000) FlightAudit
WHERE  CreatedUTC < @short_time;

The rest of the code can remain unchanged.

This deletes an arbitrary 1000 rows matching the WHERE clause (same semantics as the original query).

If you wanted to define a particular ORDER BY for the TOP then you could use

WITH T
     AS (SELECT TOP (1000) *
         FROM   FlightAudit
         WHERE  CreatedUTC < @short_time
         ORDER  BY CreatedUTC)
DELETE FROM T;


Oracle sys password lost I inherited an Oracle DB on Linux Redhat from someone who left the company and didn't tell anyone what the passwords were to the various accounts on the database itself (not Linux), including what he had the oracle DB sys account set to.

However currently a production app is running on the server, so I don't want to delete the password file to reset it, as that would disrupt the production app.

I have complete (root) acess to the Linux server; the Linux server password is not the issue. I know I've done this once in the past, but I can't recall how I gained control, and Google is pointing to either deleting the file, or changing the password via another user, both of which aren't really option.

Well, if you have full access to the database host you don't need to delete and re-create the password file.

According to Using Operating System Authentication section in Database Administrator's Guide logging in as an OS user which is a member of dba group on *nix or ora_dba group on Windows and connecting to an Oracle Database instance using SQL*Plus with SYSDBA or SYSOPER privileges should do the trick.

$ ssh root@orcldbxe1
[root@orcldbxe1 ~]# su - oracle
[oracle@orcldbxe1 ~]$ groups
oinstall wheel dba
[oracle@orcldbxe1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 22 08:22:35 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>

That's why it's called operating system authentication – you use operating system group. Simple and convenient, and no need to remember passwords. I always use it.

Once you're in, you can reset passwords and do other administrative tasks.

SQL> alter user spongebob identified by s3cr3t;


Storing history of full/partial tables I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables.

I wonder if it's efficient to store a lot of rows with NULLS on those rows that didn't change. For example this is a simplistic representation of my products table;

The products_history table has all it's field (except the FK and non-logical rows) set to NULL, whereas the normal products table has NOT NULL on all, except description.

Now what I had in mind is to push a duplicate of the actual product row and push the change into the products_history table. So let's say I have this row in products:

{600, CURRENT_TIMESTAMP, 2, 'BS001', NULL, 49.95}

and would change the price to 48.50. So I would push which values has changed, in this case the base_price and add a change-row to the history table;

{600, THE_date_created_FROM_PRODUCT, NULL, NULL, 49.95}
{600, CURRENT_TIMESTAMP, NULL, NULL, 48.50}

So here on the first modification of a Product it will get 2 rows, and each modification afterwards only one new with the modified fields). After it will update the current product row with the new base_price.

This approach works for me and is quite effective (as the history table will only be filled with product data if a product get's actual edited for the first time), but I am wondering if it this is efficient by storing all those NULL-values. Would it affect my performance after a while or would the impact not be that great?

Otherwise; I'm curious what would be good approaches to do this in MySQL, or even Django ORM-specific ways.

One way of doing this is to have a temporal table for business time (sometimes called valid time) as history. Basically what you have is a copy of your products table with additional attributes for begin_time and end_time:

CREATE TABLE PRODUCT_HISTORY
( ...
, BEGIN_TIME TIMESTAMP NOT NULL
, END_TIME TIMESTAMP
,    PRIMARY KEY (<key>, BEGIN_TIME) );

When you add a product you add a row to the history with BEGIN=now and END=null.

When a product changes you update the row where END=null to END=now, and add a new row as for insert.

When a product is removed update the row where END=null to END=now

All these manipulations of the history table are easily implemented via triggers.


Find "Mystery processes" on a MS SQL Sever environment? (SSIS, databases, etc) I inherited a collection of Instances/ MS SQL Databases/ SSIS scripts on a server. I am now the interim "accidental DBA."

I thought I was familiar with everything there, but apparently not. It's all completely undocumented.

I myself am not even currently the 'master admin' of the -- well ... I'm not even sure how that works. Can you group all instances on a server together to have the same master admin account? It's possible one of IT guys who knows nothing of the contents of these dbs has root access of some sort.

Let's just say -- I'm familiar with probably 80% of all processes that go on in this server. It's a collection of databases and SSIS scripts.

HOWEVER, I have noticed that some tables --- well, WHATEVER is writing to them, I have no idea. I don't have visibility to the (presumably SSIS since everything else is SSIS) scripts that are writing to them.

The few SSIS scripts I do have visibility to --- well there's a different "collection" for every Instance on this server.

Well - anyway -- there may be an IT contact that has root access/ admin access to something. The old DBA is gone.

Assuming this IT contact has top-level access to every Instance/ Database on this server, should I just access him for a high-level mapping of every instance-database and potentially access? (I'm literally the only one working with these databases). A lot of stuff I do not have access to, I'm either unaware of its existence, or its hidden.
What happens if this IT contact does not have admin access to some "pieces" or databases here? Are we screwed?
Should I request remote access to the actual Server (probably Windows Server 2012) that this database is hosted on? I'm wondering if that would help me investigate/ find where these Mystery SSIS scripts are.
How do I find out what robot-script is writing to a bunch of tables? Should I set up SQL Server Profiler? I guess it's called Extended Events now? What access/ permissions are required for this (I assume I just need trace permissions on the database where the tables are being updated)? Again, I'm well aware of the database where the updated tables are located --- there are SSIS scripts in that Instance, but none that write to these tables. Some "Mystery Process/ Script" is doing so daily.

Assuming this IT contact has top-level access to every Instance/
  Database on this server, should I just access him for a high-level
  mapping of every instance-database and potentially access? (I'm
  literally the only one working with these databases). A lot of stuff
  I do not have access to, I'm either unaware of its existence, or its
  hidden.

If you're now the defacto DBA you should, in accordance with your IT/Security policies, arrange for administrative access to each instance. This will likely be sysadmin access to each instance. There is no collective 'master admin' across all instances on a server. Instances are isolated from a security perspective and admins on one do not necessarily get/need access to another instance.

  What happens if this IT contact does not have admin access to some "pieces" or databases here? Are we screwed?

It is possible for local administrators of the actual server to regain access to a server when all sysadmins are lost. See this link for more information. Your IT contact will need local admin access to the server to use this fix.

  Should I request remote access to the actual Server (probably Windows Server 2012) that this database is hosted on? I'm wondering
  if that would help me investigate/ find where these Mystery SSIS
  scripts are.

Potentially yes. If there are SSIS packages stored on the file system then you may need remote access to view and manage those packages.

  How do I find out what robot-script is writing to a bunch of tables? Should I set up SQL Server Profiler? I guess it's called
  Extended Events now? What access/ permissions are required for this
  (I assume I just need trace permissions on the database where the
  tables are being updated)? Again, I'm well aware of the database
  where the updated tables are located --- there are SSIS scripts in
  that Instance, but none that write to these tables. Some "Mystery
  Process/ Script" is doing so daily.

Profiler and Extended Events are different tools, but both can be used for this purpose.

Check out this article series on SQLServerCentral.com for information on Extended Events. This will help you put together a session to capture the information you're after.

One additional note, don't be like the DBA who just left your company. As you start to piece together the packages, scripts, jobs and other bits of information about this server, document it. It will not only help anyone who comes in to provide SQL Server support, but you'll find it an invaluable tool yourself.


Always On/SQL Server Replication for Dummies I'm a database developer, not a DBA, but the task has fallen to me to write a brief white paper on our options for configuring a database mirror for web service APIs to use as its source, in order to take load off our primary, transactional database.

I listen to the Brent Ozar podcasts, and I hear how "always on availability groups" are pitched as being the "right" answer for this. I also know that the older SQL Server "database mirroring" technology is officially deprecated.

So I suggested AGs, and our DBA seemed to be on-board, but then he came back and said that implementing this would require the creation of failover clusters and would require lots of extra $$$s and licenses.

I have tried to read the literature about how availability group licensing works, and I get lost very quickly.

Can anyone point me to a "SQL Server Replication for Dummies" type post that compares options and costs for building a second database replica/mirror that can be actively queried for reporting purposes, specifically:

Nightly automatic backup/restore
Log shipping (will that work here?)
Database mirroring
Always On Availability Groups

If so, I'd be forever grateful.

If it matters, our production DB version is SQL Server 2014 Enterprise Edition, but we will be updating to SQL Server 2017 Enterprise Edition this fall.

Thanks!

First on AGs.  To have a readable AG replica, both the primary and secondary SQL instance must be Enterprise Edition.  For SQL 2016 and before you also must have a Windows Server Failover Cluster (although it doesn't need any shared storage).  So there's no extra hardware required; the Windows Servers just need to be able to talk to each other, and be in the same Domain.

For SQL 2017 and after you can create an AG replica without a Failover Cluster (and therefore without automatic failover).  See Read-scale Availability Groups.

If your secondary SQL instance is not Enterprise Edition, you can use Backup and Restore (eg Log Shipping), or Transactional Replication to maintain a second, readable copy of your database.  With any backup-based solution the replica will not be near-real-time, and you will have to briefly disconnect the readers to restore the latest backup.

With SQL 2016 and later, there are additional features to enable you to run reporting workloads directly on your production database.  This uses features including, READ COMMITTED SNAPSHOT isolation, Resource Governor, Updatable Columnstore Indexes with Compression Delay, and optionally In-Memory OLTP, called collectively Real-Time Operational Analytics.


FK constraint fails while inserting into child on Disjoint tables I'm trying to test the concept of disjoint tables in MySQL, so I created some tables:

CREATE TABLE item (
  id int NOT NULL AUTO_INCREMENT,
  type ENUM('label', 'checkbox') NOT NULL DEFAULT 'label',
  PRIMARY KEY (id, type)
  ) ENGINE = InnoDB ;

CREATE TABLE label (
  item_id INT NOT NULL,
  item_type ENUM('label') NOT NULL DEFAULT 'label',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_1` FOREIGN KEY (item_id, item_type)
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  ) ENGINE = InnoDB ;

CREATE TABLE checkbox (
  item_id INT NOT NULL,
  item_type ENUM('checkbox') NOT NULL DEFAULT 'checkbox',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_2` FOREIGN KEY (item_id, item_type)
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  ) ENGINE = InnoDB ;

I had no problems inserting into the parent table:

INSERT INTO item VALUES(1,'checkbox');

But if I then try to insert into the child table:

INSERT INTO checkbox VALUES(1,'checkbox',1);

I get a "constraint failed" error:

  #1452 - Cannot add or update a child row: a foreign key constraint fails (checkbox, CONSTRAINT fk_2 FOREIGN KEY (item_id, item_type) REFERENCES item (id, type) ON DELETE NO ACTION ON UPDATE NO ACTION)

I've re-read my DDL several times and I can't find what is it that I'm doing wrong.
What am I missing?

I tried this in sqlfiddle.com, which means MySQL 5.6.

Also, since I have a box with 5.0.45 and phpMyAdmin at hand, I tried inserting with the GUI (in case I was messing up something else) but got the same results.

Equal both type ENUM values.

CREATE TABLE `item` (
  id int NOT NULL AUTO_INCREMENT,
  type ENUM('label', 'checkbox') NOT NULL DEFAULT 'label',
  PRIMARY KEY (id, type)
  );

✓

CREATE TABLE checkbox (
  item_id INT NOT NULL,
  item_type ENUM('label', 'checkbox') NOT NULL DEFAULT 'checkbox',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_2` FOREIGN KEY (item_id, item_type)
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  );

✓

INSERT INTO item VALUES(1,'checkbox');

✓

INSERT INTO checkbox VALUES(1,'checkbox', 1);

✓

dbfiddle here


mongodump and restore in single command How to perform mongo DB dump and restore in a single command, by providing source and sink DB and collection name separately?

I understand we can do mongodump and then mongorestore but if I need to this on an intermediate machine, this will mean I will need to wait for mongodump to complete before I can start mongorestore manually again. Is there a way to do it in a single command?

You mean like this:
mongodump --archive --db=test --port=27017 | mongorestore --archive --port=27018


Copy mysql schemas to a new instance of a new version I have a few schemas, Which are included in mysql 4 instance, And i want to move them to other mysql instance which running under a newer version - mysql 5.6. This instance is already including some schemas.
I want to move these schemas from the old version to the new version, And so to get rid of the old mysql instance.

What is the best practice to do so? Do i need to upgrade each version (4-->5.0, 5.0-->5.1, etc), And only after the schemas will be updated to version 5.6 i will be able to move them to the new instance?

I don't have any experience with mysql upgrading, So any tip would be great.

You should be able to just mysqldump the individual databases and load them straight into a MySQL instance running on the latest version. I have recommended this before:

May 26, 2015 : mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?
Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
Apr 18, 2013 : Steps to upgrade MySQL from version 5.0 to version 5.5
Apr 11, 2013 : MySQL upgrade 5.0.88 to latest

When doing this, please do not move the grants by dumping the mysql schema. The number of columns have changes between versions :

Oct 10, 2014 : MySQL service stops after trying to grant privileges to a user
Jun 13, 2012 : Fastest way to move a database from one server to another

Try to extracts the grants with pt-show-grants or emulate it as suggested


Linked Server alternative in Sql Azure database We are migrating database from Rackspace to Azure. Our databases were deployed on Virtual Machines in rackspace. In our queries we are using Linked server to other databases. Basically cross database query.
problem is that after migrating databases, we are unable to use linked server queries as Sql Azure db does not supports Linked Servers.

Kindly help.

UPDATE:

Not all databases are in azure. Some databases are with their respective owners in remote locations. We need to query  database that are running outside Azure.
There's no VM for Sql Server in Azure. We are using Database as a service in Azure.

See the image below for more details

You can use cross database queries as explained here. For example, to make reference to a remote table on a query you need to create a data source:
CREATE EXTERNAL DATA SOURCE MyExtSrc
WITH
(
 TYPE=SHARD_MAP_MANAGER,
 LOCATION='myserver.database.windows.net',
 DATABASE_NAME='ShardMapDatabase',
 CREDENTIAL= SMMUser,
 SHARD_MAP_NAME='ShardMap'
);

Define an external table using that data source.
CREATE EXTERNAL TABLE [dbo].[all_dm_exec_requests](
 [session_id] smallint NOT NULL,
 [request_id] int NOT NULL,
 [start_time] datetime NOT NULL,
 [status] nvarchar(30) NOT NULL,
 [command] nvarchar(32) NOT NULL,
 [sql_handle] varbinary(64),
 [statement_start_offset] int,
 [statement_end_offset] int,
 [cpu_time] int NOT NULL
)
WITH
(
 DATA_SOURCE = MyExtSrc,
 SCHEMA_NAME = 'sys',
 OBJECT_NAME = 'dm_exec_requests',
 DISTRIBUTION=ROUND_ROBIN
);

Finally, use the external table on SELECT statements.
SELECT TOP 10
 [request_id],
 [start_time]
 [status],
 [command]
FROM all_dm_exec_requests
ORDER BY [cpu_time] DESC


Dilemma with Partition Key choice One of our tables is over 200gb and the old records are hardly ever queried, and this makes me think partitioning is a good solution, however I am having trouble figuring out which column to use as a Partition Key for a table.
We have 2 tables as follows (names and structures are altered for confidentiality):

transactions table - with a transaction ID,
checks table - With the schema (id, transcation_id, created_at, metadata, last_updated_at) there can be multiple different checks with for a given transaction_id

As part of data aggregation we write a summarized version of the checks into a single column in the transactions table. The way we do this aggregation is by running an UPDATE on transactions table with joins on the checks table filtering by last_updated_at to only check for checks that have been updated recently.
Users however query using created_at in the checks table, and also last_updated_at could potentially change for a given row (although not likely).
Which one should I use as partition key? created_at or last_updated_at?

Most queries become slower with partitioning.
The exceptions I know are:

a query that needs a sequential scan, but contains the partitioning key in the WHERE clause
a query that joins two partitioned tables, and the join condition matches the partition key for both
a query that groups and aggregates by the partitioning key

So you should rather consider how data expire. Partition your tables so that you can get rid of old, obsolete data by dropping partitions.


PostgreSQL PREPARE query with IN () parameters I'm trying to prepare a query from PHP like:

pg_prepare($con, "prep", "select * from test where tid in ($1)");

and then execute it with:

$strpar = "3,4,6,8,10";
pg_execute($con, "prep", array($strpars));

The problem is that I cannot pass a series of values built as prepare expects a fixed number of parameters. Is there any way to make the parameters dynamic?

Use an array to represent the series of values:

pg_prepare($con, "prep", "select * from test where tid=ANY($1::int[])");

$strpar = "{3,4,6,8,10}";
pg_execute($con, "prep", array($strpars));

The cast to int[] in the query might even be superfluous if the planner is able to infer the type by itself.


What storage engine do I need - Mysql? I have designed a database and came to the point of actually deciding which storage engine is better for my case.

I estimate that during the first few years I would have around 100,000 - 500,000 users. Some of the users can also have multiple posts, comments, etc. The number of users can go up to maybe a max of 10,000,000. Well I am just estimating.

I am planning to use a BIGINT for my user_id or in a certain situation I may have to go for a different route where I would store a user_id as a string like '000123456789'. I am not really decided about that.

My CRUD operation estimates are like this:
Create 15%
Read 60%
Update 20%
Delete 5%

So my focus is on Read and the Update.

If you want a proper database then of the two you should go for innodb - it supports ACID transactions, referential integrity, and smaller granularity locking.

myISAM is faster in a number of read-only use cases but:

The lack of support for transaction safety can lead to corruption in multi-user situations where several updates happen at the same time unless you are very careful in your application design
The lack of foreign key support removes a valuable data integrity protection from your armoury
The fact the writes result in a full table lock can cause massive performance issues for use cases that involve much by way of insert and update operations.

innodb is currently the default selected if you do not specify a storage engine, unless you are running an old or badly configured instance of mysql (myisam used to be the default).

Update: as mentioned by akuzminsky in the comments, it may no longer be the case that myISAM is a better performer for any real world workload. A quick search for "innodb myisam benchmarks" seems to suggest this is the case.


Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to float I created some select statements which run without any problem in SQL Server. Then I joined them together with UNION and got this error message>

  Msg 8114, Level 16, State 5, Line 1 Error converting data type
  nvarchar to float. Warning: Null value is eliminated by an aggregate
  or other SET operation.

Does anyone have an idea why? (I googled, and found that it could because I have some NULL values, which can-t be summed up or something, but as I said, the two codes run without error seperately.)

SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100)
       AND (YEAR(BuchDatum) = 2019)
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100)
       AND (YEAR(BuchDatum) = 2019)
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Restzeit_T)) AS Zukünftliche_Bearbeitungszeiten, 3 AS filtering
FROM   [Test].[dbo].[ZNEJO_DETAIL]
WHERE  (ArbPlatz = 103100)
       AND (YEAR(Endtermin) = 2019)
       AND (DATEPART(week, Endtermin) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100)
       AND (YEAR(BuchDatum) = 2019)
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(Angebot) AS Angebot, 5 AS filtering
FROM   [Test].[dbo].[CM01_DETAIL]
WHERE  (Woche = '21.2019')
       AND (Arbeitsplatz = '103100')
UNION
-- This is where I seperate the code.
SELECT FORMAT((
          SELECT W21
          FROM [Test].[dbo].[Übersicht_Woche_RAP_103100]
          WHERE id = 2) /
          (SELECT W21
          FROM [Test].[dbo].[Übersicht_Woche_RAP_103100]
          WHERE id = 5),'P0') AS Calculation, 6 AS filtering

The two screenshots are the two results from the code run in two parts. I want to join them together with UNION.

Thanks for all the help

Tha sollution is (Thanks @Akina) To use the CAST() functino and convert all the select statements to datatype CHAR.

Sample code:

SELECT CAST(SUM(try_convert(numeric(38, 2), Rüsten_Ist)) AS CHAR) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = DATEPART(week,GETDATE())-8)


Shared memory connection experiencing network wait type I have a poorly written application running a SELECT *, resulting in a 2MB result set every few seconds. I cannot correct this. I am confused though,by the connection encountering ASYNC_NETWORK_IO waits. The application is local to the database server.

I have verified the session_id in dm_exec_connections is 'Shared memory'. The client_net_address is 'local machine'.

Anyone ever experienced this before? Or able to explain to me why this is happening?

Thanks!

ASYNC_NETWORK_IO also happens when the app is NOT reading the data fast enough from SQL Server. Note that this is NOT SQL Server issue but its the app issue. Make sure the app is reading the data as a set instead of row by row.


Unable to delete a referenced row due to a trigger on a referencing table? I ran into some problems in using triggers to ensure only deletions from cascading deletes:
CREATE TYPE status_type AS enum ('A');

CREATE TABLE parent(
    name VARCHAR(255) PRIMARY KEY
);

CREATE TABLE child(
    name VARCHAR(255) PRIMARY KEY
);

CREATE TABLE relation(
    childname VARCHAR(255) NOT NULL REFERENCES child (name) ON DELETE CASCADE,
    parentname VARCHAR(255) NOT NULL REFERENCES parent (name) ON DELETE CASCADE,
    status status_type NOT NULL,
    PRIMARY KEY (childname, parentname)
);

CREATE OR REPLACE FUNCTION prevent_deletes_when_status_A() RETURNS trigger AS $$
    BEGIN
        IF OLD.status='A' AND EXISTS (SELECT 1 FROM parent WHERE parent.name=OLD.parentname) THEN
            RAISE EXCEPTION 'Invalid DELETE';
        ELSE
            RETURN OLD;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_onlycascading_fromParent
    BEFORE DELETE ON relation
    FOR EACH ROW EXECUTE PROCEDURE prevent_deletes_when_status_A();

INSERT into parent VALUES ('john');
INSERT into child VALUES ('doe');
INSERT into relation VALUES ('doe', 'john', 'A');
DELETE FROM child WHERE name='doe';

Why does this not work? I understand that it raises the exception message. However, the row in child should be deleted. Am I missing something here?
Shouldn't the row in child be deleted first before the delete cascades to the relation table, where only then would the trigger take effect?

All the cascading deletes and your trigger function are running inside the same database transaction, so if the transaction is aborted by an error you raise in the trigger function, all actions that took place in the same transaction (including the triggering delete) are rolled back.


What else does sp_addmergefilter do I am running the following stored procedure to add a merge replication join filter,

sp_addmergefilter

What else does that stored procedure do behind the scenes?

I am calling that stored procedure and in one case it is timing out after 10 minutes. The join filter is this for the two tables DispatchLink and Dispatch,

Dispatch.DispatchId = DispatchLink.DispatchLink1 and Dispatch.ContractId = DispatchLink.ContractLink1
or Dispatch.DispatchId = DispatchLink.DispatchLink2 and Dispatch.ContractId = DispatchLink.ContractLink2

The structure for those two tables is explained here,

How to Optimise Query

If I remove all the stuff after the 'or' it runs fast.

It's really interesting what I found. I start SSMS as an administrator, and chose the option to debug the stored procedure. I found it was doing this,

declare @test int
select @test=1 from [dbo].[DispatchLink] , [dbo].[Dispatch] [Dispatch]
where (DispatchLink.ContractLink1 = Dispatch.ContractId and DispatchLink.DispatchLink2 = Dispatch.DispatchId)
or (DispatchLink.ContractLink2 = Dispatch.ContractId and DispatchLink.DispatchLink2 = Dispatch.DispatchId)

And it seems that the reason it was doing that was to test whether the join clause was feasible, i.e. whether it had syntax errors etc.

The resulting query plan is the problem, and can be see here,

Analysing A Query Plan

Adding WITH (FORCESEEK) makes the query instant, but of course I cannot do that because that is just what sp_addmergefilter is doing internally. The query optimiser is making an incorrect choice for some reason.


DB2 syscat.routines vs. sysibm.routines I need to select all routines names from a DB2 luw.
I saw the following catalog tables:

sysibm.routines
syscat.routines

If I run the following queries:

select count(*) from sysibm.routines;
select count(*) from syscat.routines;

There is a big Difference between the results.
Can anyone tell what is the difference between these views and which one should be used for the task of selecting all routines names?

Both SYSIBM.ROUTINES and SYSCAT.ROUTINES are actually views over the "real" catalog table, SYSIBM.SYSROUTINES. You can grab the SQL definition for each view like so:

SELECT TEXT
FROM SYSCAT.VIEWS
WHERE VIEWSCHEMA = 'SYSIBM' AND VIEWNAME = 'ROUTINES';

SELECT TEXT
FROM SYSCAT.VIEWS
WHERE VIEWSCHEMA = 'SYSCAT' AND VIEWNAME = 'ROUTINES';

I won't post the results here as I've no idea if that's considered proprietary by IBM (it is technically source from DB2 itself after all). Anyway, if you look through the view source (you'll want to re-format it first with some SQL lint-like tool!) you'll notice several things:

SYSIBM.ROUTINES excludes functions with type "T" and "R" (i.e. table and row functions, so it only includes scalar functions, aggregates, and procedures). This probably explains a lot of the difference.
SYSIBM.ROUTINES excludes routines in the schema SYSFUN. This probably explains the rest of the difference as there's 100+ routines in SYSFUN.
SYSCAT.ROUTINES only excludes routines from the SYSIBMINTERNAL schema (there's only a few of these - not sure if all/some/none show up in SYSIBM.ROUTINES given the restrictions above).
There's a load of joins in each view but I'm pretty sure they're all 1-to-1 and aren't affecting the cardinality of the result which is dictated by SYSIBM.SYSROUTINES (most of those joins are for pulling in things like the result type or collation details).

Some background:

The tables starting with SYS in the SYSIBM schema are the real catalog tables that everything is based upon. Generally I wouldn't bother with these as they're undocumented, and a bit of a pain to work with (however in things like DB2 for z/OS which lacked SYSCAT last time I worked with it, they're the only tables you've got).
The views not starting with SYS in the SYSIBM schema have a structure awfully similar to the standard INFORMATION_SCHEMA. Something probably uses them as such (maybe some JDBC methods, or the catalog functions in various drivers?) but as they're otherwise undocumented it's difficult to know exactly what they include/exclude without looking at their view definitions (using the trick above).
The views in the SYSCAT schema are the really useful ones. They're well documented and generally quite easy to work with (sadly they're also DB2 for LUW specific).

In other words, I'd generally treat the SYSCAT results as "gospel" but if you want to understand exactly what they're showing then you need to dive into their source.


Is it safe to copy DB files directly from an LVM snapshot? I saw two different approaches for backing up database with LVM snapshot. This article suggests spawning another MySQL instance to do an SQL dump. MYSQL Performance Blog suggests that it is safe to copy directly the database files:

  6) Copy data to backup. Normally you can skip slow query logs and
  error log while taking backup. You also can skip most of binary logs –
  however if some of your slaves are far behind you might want to keep
  some of last binary logs just in case, or you can assume in case of
  recovery from the backup you will need to restore slaves as well and
  skip binary logs in your backup process.

For database in InnoDB format, is it ok that I just backup the /var/lib/mysql/ folder?

Yes, it is safe. There is a tool http://www.lenzg.net/mylvmbackup/ that uses LVM snapshots to take backups of MySQL.

From InnoDB's standpoint a backup copy taken with LVM snapshots looks like as InnoDB has suddenly crashed. When you start MySQL from the backup copy it will start crash recovery. To save some time it's better to do the crash recovery immediately after the backup is taken.


how to dump via mysqldump hugh table into chunks i have very hugh table , around 100 M records and 100 GB in a dump file , when i try to restore it it to a different DB  i get sql query lost connection , i want to try and dump this table into chunks (something like 10 chinks of 10 GB) where each chink will be in seperate table.
what i managed to optimized so far is this :

mysqldump --skip-triggers --compact --no-create-info --single-transaction --quick --max_allowed_packet 1G  -h {host} -u {user} -P 3306 -p{pwasword} {my_schema} {}> /mnt/datadir/{table_name}.sql

and now the output is that i have 1 file {table_name}.sql i na size of 100 GB i want to get 10 files in sizes of 10 GB each

You can use primary key to specify to dump only id in those range

mysqldump -u root -p db_name table_name --where='id  < 10000' > test1.sql
mysqldump -u root -p db_name table_name --where='id between 10000 and 20000' > test2.sql
.....
 mysqldump -u root -p db_name table_name --where='id between 900000 and 1000000' > test11.sql


Always ON Uninstallation I have a request to uninstall one of the test instance which is part of Always ON. I googled around for a clean decommission but did not find steps for Always ON Instance uninstallation. Would be great if Steps can be shared here or any link if you are aware of. As per me I would :

Take database backup
Take note of Availability group listener and database involved.(For future if they want it back)
Remove the Databases from the AG.
Remove the AGs
Uninstall the instance as a normal standalone instance.(For all nodes).

Please add or suggest if this needs any changes.

Do you have any replications setup?
Maybe you also want to include a step to remove those incoming or outgoing replications  after your step 2?

It should be fine even if you omit this step, but it might generate unnecessary alerts (if you have set monitoring for replication) to the oncall.


pg_upgrade from Postgres 9.5.5 to Postgres 12.3 errors out I am trying to upgrade the database cluster from 9.5.5 to 12.3 using pg_upgrade.
I ran it with the --check option, and the result came back "clusters compatible", but when I run it with the --clone option, I get the error below. The log does not tell me where it’s failing.
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
executing: SELECT pg_catalog.set_config(‘search_path’, ‘’, false);
Checking for presence of required libraries         ok
could not clone file between old and new data directories: Operation not supported

The documentation says:

File cloning is only supported on some operating systems and file systems. If it is selected but not supported, the pg_upgrade run will error. At present, it is supported on Linux (kernel 4.5 or later) with Btrfs and XFS (on file systems created with reflink support), and on macOS with APFS.

Obviously you are using different software.
You could use the --link option for a fast upgrade, but that will make the old cluster unusable after the upgrade.
Don't upgrade to 12.3. Use the latest supported minor release, currently 12.5.


Mongodb database design I'm planning to implement this schema in Mongodb, I have been doing some readings about schema design, and the notion was whenever you structure your data like a relational database you must be doing something wrong.

My questions:

what should I do when collection size gets larger than 16MB limit?
app_log in server_log collections gets might in some cases grow larger than 16MB depending how busy the server is.

I'm aware of the cap feature that I could use, but the requirement is store all logs for 90 days.
Do you see any potential issues with my design?
Is it a good practice to have the application check for collection size and create new collection by day / hour ..etc to accommodate log size growth?

Thanks

16 MB is the maximum size of single document, e.g. one user.

The size of the collection is limit by maximum database only.

https://docs.mongodb.com/manual/reference/limits/

So logging to the database should be no problem. In my opinion using sensible indizes including _id is superior to a heap of collections.

It is not clear to me what you actually want to store in your database so I cannot really comment on the structure. Generally speaking there are three ways to handle relations: Using arrays

storing subdocuments or
ids of documents (You would have to retrieve those via a separate database request)
invert the relation by giving all referenced documents a field containing the parent document id

If you give more details, chances are somebody can give you a more detailed answer.


SQL Server CDC (Change Data Capture) Existing Data Seed I have an SQL Server Database with 100's of tables with as many rows in each and I'm turning on CDC for some reporting. Since data was created/inserted BEFORE turning on CDC, the CDC tables are current empty and I would like to get the data into the CDC tables to start reporting.

Is there a way to 'refresh' or seed the existing data into the CDC tables?

I've tried a simple update with existing data, however this doesn't actually cause a CDC entry. Presumably it isn't writing to the transaction log and triggering an write.

UPDATE table1 SET column1 = column1;

In my case, most of my tables currently have an UPDATEDON column which I could just update to a now timestamp, which will cause an update and subsequent entries in the CDC tables, but I'd prefer to retain the existing update time.

UPDATE table1 set UPDATEDON = getDateUtc();

Alternatively I could go through and move all the data into temporary tables, delete the data and re-insert it to cause updates, but surely there is a better way? How have others tackled this?

In case others end up here, a solution that works for me was to

store data in a temp table
delete source table data
delete cdc table data
insert temp table back into source table

I had to build up a list of column names in order to insert the IDENTITY which accounts for most of the script. Below is similar to what I used, however I was running inside a loop of all of my tables.

-- 1. Store data
SELECT * INTO #refreshTable FROM dbo.myTable;

-- 2. delete source table data
DELETE FROM dbo.myTable;

-- 3. delete cdc table data
DELETE FROM cdc.dbo_myTable_CT;

-- Build list of columns for insert
DECLARE @MyCursor CURSOR;
DECLARE @MyField nvarchar(max);
DECLARE @columns nvarchar(max) = '';
BEGIN
    SET @MyCursor = CURSOR FOR
        select [name] from tempdb.sys.columns where object_id = object_id('tempdb..#refreshTable');

    OPEN @MyCursor
    FETCH NEXT FROM @MyCursor
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @columns = CONCAT(@columns, ', ', @MyField)
        FETCH NEXT FROM @MyCursor
        INTO @MyField
    END;

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;
SET @columns = SUBSTRING(@columns, 2, 10000); -- Not expecting long column names

-- 4. Insert data back into source table
SET IDENTITY_INSERT dbo.myTable ON;
DECLARE @sql nvarchar(MAX) = 'INSERT INTO dbo.myTable (' + @columns + ') SELECT * FROM #refreshTable';
EXEC (@sql);
SET IDENTITY_INSERT dbo.myTable OFF;


mongodb restore multiple collections with single command How do I restore multiple collections at once in mongodb.

I have tried

mongorestore -c Role -c UserAccount -c Permission -d movie-app dump/

and I got an error file dump is a directory, not a bson file

I can restore single collection at a time I have to  specify the bson file like

  mongorestore -c UserAccount -d movie-app dump/movie-app/UserAccount.bson

I need to know how do I restore multiple collection with one command.

Try these parameter to Include or exclude collections

--nsInclude
--nsExclude

During restoration, you can ignore the unwanted collections.

EG:

Dump all the collections

mongodump --db bhuvi --out /tmp

drwxr-xr-x 2 root root 4096 Dec 20 18:09 ./
drwxrwxrwt 9 root root 4096 Dec 20 18:25 ../
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll1.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll1.metadata.json
-rw-r--r-- 1 root root   83 Dec 20 18:09 coll2.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll2.metadata.json
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll3.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll3.metadata.json

My dump folder has 3 collections.

coll1
coll2
coll3

Restoring coll2 and coll3 collections

I want to restore coll2 and coll3 in the test db.

mongorestore --db test --nsExclude 'test.*1' /tmp/bhuvi/

2017-12-20T18:25:50.299+0000    the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead
2017-12-20T18:25:50.299+0000    building a list of collections to restore from /tmp/bhuvi dir
2017-12-20T18:25:50.299+0000    reading metadata for test.coll3 from /tmp/bhuvi/coll3.metadata.json
2017-12-20T18:25:50.312+0000    restoring test.coll3 from /tmp/bhuvi/coll3.bson
2017-12-20T18:25:50.313+0000    reading metadata for test.coll2 from /tmp/bhuvi/coll2.metadata.json
2017-12-20T18:25:50.326+0000    restoring test.coll2 from /tmp/bhuvi/coll2.bson
2017-12-20T18:25:50.327+0000    no indexes to restore
2017-12-20T18:25:50.327+0000    finished restoring test.coll3 (3 documents)
2017-12-20T18:25:50.327+0000    no indexes to restore
2017-12-20T18:25:50.328+0000    finished restoring test.coll2 (2 documents)
2017-12-20T18:25:50.328+0000    done

Check the collections

> use test
switched to db test
> show collections
coll2
coll3

For restore collections with similar names

mongorestore --db test --nsInclude 'test.coll*' /tmp/bhuvi/

Restore collections with different pattern

There is no straight forward method for this, but we can create a loop to restore this.

create a file with list of collections that we need to restore

vi collections
#add the collections names and save.
coll1
coll2

shell script for restore in loop

#!/bin/bash
input="collcetions"
backup_path="/tmp/mydb"
while IFS= read -r col_name
do
  mongorestore --db sqladmin --collection $col_name $backup_path/$col_name.bson
done < "$input"

root@lin_sql1:/home/ubuntu# ./restore

2017-12-21T04:56:36.596+0000    checking for collection data in /tmp/mydb/coll1.bson
2017-12-21T04:56:36.596+0000    reading metadata for sqladmin.coll1 from /tmp/mydb/coll1.metadata.json
2017-12-21T04:56:36.616+0000    restoring sqladmin.coll1 from /tmp/mydb/coll1.bson
2017-12-21T04:56:36.677+0000    no indexes to restore
2017-12-21T04:56:36.677+0000    finished restoring sqladmin.coll1 (3 documents)
2017-12-21T04:56:36.677+0000    done
2017-12-21T04:56:36.686+0000    checking for collection data in /tmp/mydb/coll2.bson
2017-12-21T04:56:36.686+0000    reading metadata for sqladmin.coll2 from /tmp/mydb/coll2.metadata.json
2017-12-21T04:56:36.699+0000    restoring sqladmin.coll2 from /tmp/mydb/coll2.bson
2017-12-21T04:56:36.760+0000    no indexes to restore
2017-12-21T04:56:36.760+0000    finished restoring sqladmin.coll2 (2 documents)
2017-12-21T04:56:36.760+0000    done

Check the collections

> use sqladmin
switched to db sqladmin
> show collections
coll1
coll2


MySQL Server Performance I have a few questions regarding MySQL server running under Windows Server 2008 R2:

How do I check the number of allowed connections?
How do I turn on query logging to see performance of queries executed?
As I understand MySQL is multithreaded, will increasing the number of allowed connections increase performance on a Quad Core system?
Will having a cluster setup increase performance?
How can I setup load shedding with multiple MySQL servers in a cluster?

Thanks

QUESTION 1 : How do I check the number of allowed connections?

ANSWER

You can set the number of connections in my.ini. For example, to set it to 500 do this:

[mysqld]
max_connections = 500

However, keep in mind the mysqld wil dictate the variable open_files_limit

Run this query : SHOW VARIABLES LIKE 'open_files_limit';

You can raise this, BUT AT YOUR OWN RISK !!! Also, keep in mind that the number of max_connections will multiply the sum of per-connection parameters (sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size)

QUESTION 2 : How do I turn on query logging to see performance of queries executed?

ANSWER

To turn on the slow log and set the minimum slow query time to 2 seconds (default 10)

[mysqld]
slow-query-log
slow-query-log-file = slow-queries.log
long-query_time=2

This should place the slow-queries.log file in the same location as datadir. You can see where datadir is set by running SHOW VARIABLES LIKE 'datadir';

QUESTION 3 : As I understand MySQL is multithreaded, will increasing the number of allowed connections increase performance on a Quad Core system?

ANSWER

This answer depends on the storage engine of all your tables.

If you are using MyISAM, there is nothing to configure because the MyISAM storage engine does not utilize multiple CPUs.

If you are using InnoDB

MySQL 5.5 can be configured to utilize multiple CPUs
MySQL 5.1 InnoDb Plugin (MySQL 5.1.38+) can be configured to utilize multiple CPUs
MySQL 5.1.37 and prior does not utilize multiple CPUs

In order to configure InnoDB for multicore engagement, please read my past posts on this subject:

About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?
Multi cores and MySQL Performance
MySQL Config for Large MyISAM tables
MariaDB XtraDB Performance Tuning
Insert-heavy InnoDB table won't use all my CPU

QUESTION 4 : Will having a cluster setup increase performance?

QUESTION 5 : How can I setup load shedding with multiple MySQL servers in a cluster?

ANSWERS

It can, but you must do two things

Configure a Replication Topology to have one Write Master and Multiple Read Slaves
Do the leg work of coding your app to access the Cluster as follows

Do all INSERTs, UPDATEs, DELETEs on the Write Master
Do all SELECTs on Read Slaves using some Network-Based Load Balancing


stress-testing MySQL slave with master's queries A MySQL host in a slave configuration is in the testing phase before being put into production as a primary server for a specific workload. To make an estimate about how it is going to perform, the idea was born to simply use the current productive master's queries and replay them on the slave. The imminent issues that arise are the following:

the slave already is replaying the master's binlogs, so only SELECT statements should be considered for execution
it needs to happen multi-threaded and in near-realtime (or at least in the same pacing as at the master) to get meaningful figures out of the whole process

Although I know about the general log, I do not see an easy way to meet both requirements. The first would require a more-or-less sophisticated parser to prevent execution of edgy things changing data like a SELECT [...] INTO. As for the second, I know about Percona Playback which is capable of parallel execution and query-pacing, but it only would take the slow query log format (at least for the pacing feature), which is even harder to parse. It also won't process in real time (e.g. the mysql-slow log piped from cat or nc).

As this does not sound too far-fetched as a requirement, I wonder how the other DBAs are managing this.

I would collect queries from Master via slow log and then reply them on the slave using http://www.percona.com/doc/percona-toolkit/2.1/pt-log-player.html to do this you will need:

Enable full slow query logging. You need to be careful with the IO load if your server is really busy. You may want to do it during low time (say at night)
Collect good amount of queries to play with
Replay them using pt-log-player on slave using filter (you don't want to replay INSERTs/UPDATEs/DELETEs as it will ruin data consistency on slave and you'll have to recreate it.


PostgreSQL: How to list the comments attached to a database How do I display the comments associated with a database, not a table? I've tried \dd and \list , but can't seem to find this.
Thanks,

I looks like this has been already answered on SE network. Check out this

In particular, note that the question mentions \l+ in psql.


Error: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table I'm trying to run this query in mysql  Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1 but got this error all the time:

CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
    FOR EACH ROW SET NEW.createdAt = NOW();
[Err] 1235 - This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'

What this means? How I can avoid and/or fix it?

This is the queries I am trying to execute:

-- brands
ALTER TABLE `brands`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_brands_table_insert BEFORE INSERT ON `brands`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- companies
ALTER TABLE `companies`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- emails
ALTER TABLE `emails`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_emails_table_insert BEFORE INSERT ON `emails`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- events_tracking
ALTER TABLE `events_tracking`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_events_tracking_table_insert BEFORE INSERT ON `events_tracking`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- media
ALTER TABLE `media`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_media_table_insert BEFORE INSERT ON `media`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- messages
ALTER TABLE `messages`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_messages_table_insert BEFORE INSERT ON `messages`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- reps
ALTER TABLE `reps`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_reps_table_insert BEFORE INSERT ON `reps`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- targets
ALTER TABLE `targets`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_targets_table_insert BEFORE INSERT ON `targets`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- territories
ALTER TABLE `territories`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_territories_table_insert BEFORE INSERT ON `territories`
    FOR EACH ROW SET NEW.createdAt = NOW();

This error means you already have an BEFORE INSERT trigger on companies table.

If it is the same trigger (meaning trig_companies_table_insert) that you created earlier and now you want to replace it then you need to drop it first:

DROP TRIGGER trig_companies_table_insert;
DELIMITER $$
CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
FOR EACH ROW SET NEW.createdAt = NOW();
END$$
DELIMITER ;

Now if you have some other trigger you have to merge code from both triggers into one, then drop existing trigger, and then create a new one.

To show the list of existing triggers use SHOW TRIGGERS.

SHOW TRIGGERS WHERE `table` = 'companies';


For PostgreSQL database, restrict remote connections to password I have recently set-up a PostgreSQL database on client's CentOs machine. Until now the remote access was restricted.

Now I have tried to open the remote connections for us, with following entry in the pg_hba.conf.

host    all             all             x.x.x.x/x      trust

The connection is fine and we are able to connect the db just with providing the user name. Password in not provided while connecting.

Now we need to restrict the access to valid credentials only.

For this I have changed the entry to

host    all             all             x.x.x.x/x      md5

And the postgresql service is restarted (I have restarted multiple times). Still if we try to connect without a password or with a wrong password, the database is connected.

I think I am doing something wrong with the pg_hba.conf.
Any pointers to what is going wrong?

Following is the content of the file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
host    all             all             0.0.0.0/0               trust
# IPv4 remote connections:
host    all             all             x.x.x.x/x      md5
host    all             all             x.x.x.x/x          md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

The line

host    all             all             0.0.0.0/0               trust

Lets anyone in without a password (other than connections over the unix file socket, connections over the loopback device, and replication users).  Since it occurs earlier in the file, it takes priority over the x.x.x.x/x line.


Tokudb ROW_FORMAT is not being accepted I am trying to create some tokudb tables to experiment with the different row format options to compare the compression available.

https://www.percona.com/doc/percona-server/5.7/tokudb/using_tokudb.html

I have tried all the following
TOKUDB_SNAPPY
TOKUDB_ZLIB
TOKUDB_DEFAULT

With no effect.

If I just ignore it the tables are created with row_fromat = fixed.

MariaDB [eventlog]> show VARIABLES like "%row_format%";
+--------------------------------+-------------+
| Variable_name                  | Value       |
+--------------------------------+-------------+
| tokudb_hide_default_row_format | ON          |
| tokudb_row_format              | tokudb_zlib |
+--------------------------------+-------------+

MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB, ROW_FORMAT=TOKUDB_DEFAULT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_DEFAULT' at line 1
MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ROW_FORMAT=tokudb_default;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tokudb_default' at line 1

MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB;
Query OK, 0 rows affected (0.09 sec)

MariaDB [eventlog]> show table status from eventlog\G;
*************************** 1. row ***************************
           Name: stable1
         Engine: TokuDB
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 18446744073709551615
 Auto_increment: NULL
    Create_time: 2017-02-20 12:26:18
    Update_time: 2017-02-20 12:26:18
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

 MariaDB [eventlog]> ALTER TABLE stable1 ROW_FORMAT=TOKUDB_SNAPPY;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_SNAPPY' at line 1

 version | 10.1.21-MariaDB
 tokudb_version | 5.6.34-79.1

When using MariaDB with tokudb plugin, syntax is

COMPRESSION=TOKUDB_LZMA and not ROW_FORMAT=TOKUDB_LZMA

From Mariadb website,

The DDL syntax is different. While binaries from Tokutek have the patched SQL parser, TokuDB in MariaDB uses the special Storage Engine API extension. Thus in Tokutek binaries you write CLUSTERED KEY (columns) and, for example, ROW_FORMAT=TOKUDB_LZMA. And in MariaDB you write KEY (columns) CLUSTERING=YES and COMPRESSION=TOKUDB_LZMA.

That page also provides more info on actually checking the compression formats.

mysql -sNe 'SELECT dictionary_name, internal_file_name FROM information_schema.tokudb_file_map WHERE table_dictionary_name = "main" OR table_dictionary_name LIKE "key-%"' |perl -F'\t' -ane '@out=qx(tokuftdump --nodata --header --rootnode /var/lib/mysql/$F[1]); foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/) { print $F[0]."\t".$1."\n"; last } }'

Different version of the above with tables names to make it easier to get check specific table names.

mysql -sNe 'SELECT dictionary_name, internal_file_name, table_name, " " FROM information_schema.tokudb_file_map WHERE table_dictionary_name = "main" OR table_dictionary_name LIKE "key-%"'  | perl -F'\t' -ane '@out=qx(tokuftdump --nodata --header --rootnode /srv/mysql/data/$F[1]);  foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/) { print $F[2]."\t".$F[0]."\t".$1."\n"; last } }' > /var/tmp/tables.compressiontype.lst

Will produce out put like

head /var/tmp/tables.compressiontype.lst
TableName   ./database/TableName-key-entity_index   11
TableName   ./database/TableName-main   11

The meaning of the various integers can be found in the github repo

    // compression methods
printf("typedef enum toku_compression_method {\n");
printf("    TOKU_NO_COMPRESSION = 0,\n");  // "identity" compression
printf("    TOKU_SNAPPY_METHOD  = 7,\n");  // google snappy
printf("    TOKU_ZLIB_METHOD    = 8,\n");  // RFC 1950 says use 8 for zlib.  It reserves 15 to allow more bytes.
printf("    TOKU_QUICKLZ_METHOD = 9,\n");  // We use 9 for QUICKLZ (the QLZ compression level is stored int he high-order nibble).  I couldn't find any standard for any other numbers, so I just use 9. -Bradley
printf("    TOKU_LZMA_METHOD    = 10,\n");  // We use 10 for LZMA.  (Note the compression level is stored in the high-order nibble).
printf("    TOKU_ZLIB_WITHOUT_CHECKSUM_METHOD = 11,\n"); // We wrap a zlib without checksumming compression technique in our own checksummed metadata.
printf("    TOKU_DEFAULT_COMPRESSION_METHOD = 1,\n");  // default is actually quicklz
printf("    TOKU_FAST_COMPRESSION_METHOD = 2,\n");  // friendlier names
printf("    TOKU_SMALL_COMPRESSION_METHOD = 3,\n");
printf("} TOKU_COMPRESSION_METHOD;\n");


Detect one-sided matches I have a SQL Server 2014 database table containing a code and two identifiers:

DECLARE @Foo TABLE (code CHAR, id_a INT, id_b INT)
INSERT INTO @Foo (code, id_a, id_b) VALUES
('A', 1, 2),
('B', 2, 1),
('C', 3, 4),
('D', 4, 0),
('E', 5, 3)

The first two rows of the table form a two-way match, as the first row links IDs 1 and 2, and the second row links them in reverse.

I want to find all rows that don't take part in a two-way match, and to that end I've written the following query:

SELECT code FROM @Foo WHERE CONCAT(id_a, ':', id_b) NOT IN (
    SELECT CONCAT(id_b, ':', id_a) FROM @Foo
)

This is working (it returns codes C, D and E for my sample data), but I'm concerned about the performance of those concatenations once the table has millions of rows in it.

I haven't tested the performance with a large dataset (that's tomorrow's job), but in the meanwhile I was wondering if there's a more elegant and performant way I can structure this query?

P.S.
The query plan indicates that I'm doing table scans inside a scalar compute inside a nested loop, which doesn't sound like it'll scale too well:

  |--Nested Loops(Left Anti Semi Join, WHERE:([Expr1007] IS NULL OR [Expr1008] IS NULL OR [Expr1007]=[Expr1008]))
       |--Compute Scalar(DEFINE:([Expr1007]=concat(CONVERT_IMPLICIT(varchar(12),[id_a],0),':',CONVERT_IMPLICIT(varchar(12),[id_b],0))))
       |    |--Table Scan(OBJECT:(@Foo))
       |--Compute Scalar(DEFINE:([Expr1008]=concat(CONVERT_IMPLICIT(varchar(12),[id_b],0),':',CONVERT_IMPLICIT(varchar(12),[id_a],0))))
            |--Table Scan(OBJECT:(@Foo))

The compute scalar is occurring because it has to convert the INT values to VARCHAR(12) values before concatenating which it can then compare against the subquery returned values. You are correct that this is not likely to scale well, due to cardinality estimation issues among others.

One solution is to LEFT JOIN the table on itself then use a WHERE clause to exclude the rows that do not have a joined row. This plan is simply two table scans, a nested loop and finally a filter to exclude the NULL valued rows. This should scale reasonably well.

Example:

SELECT a.*
FROM @Foo a
LEFT JOIN @Foo b ON a.id_a = b.id_b AND a.id_b = b.id_a
WHERE b.id_a IS NULL

Returns:

code    id_a    id_b
--------------------
C       3       4
D       4       0
E       5       3


Effective SQL Server permissions when user is in several AD groups Using Window Authentication via Active Directory Groups, if an Active Directory user exists in two different Active Directory Groups with different  permissions to a database, which Active Directory group does it use to set the permissions?

The most restrictive
The least restrictive
Cumulative of the two
Random

Setting this up in a test environment is proving difficult, because I am not a domain administrator, and we have no test domain. Plus we have seen results both ways. Just wondering if anybody knows the answer.

Rights applicable to the login are summed then applied from least restrictive to most restrictive on a per object basis.  DENY'd rights take precedence over GRANT'd rights.

Taken from the MSDN SQL Server page on Database Engine Permissions:

  Checking permissions can be complex. The permission check algorithm includes overlapping group memberships and ownership chaining, both explicit and implicit permission, and can be affected by the permissions on securable classes that contain the securable entity. The general process of the algorithm is to collect all the relevant permissions. If no blocking DENY is found, the algorithm searches for a GRANT that provides sufficient access. The algorithm contains three essential elements, the security context, the permission space, and the required permission.

  General Steps of the Algorithm

  When the algorithm is determining whether to allow access to a securable, the precise steps that it uses can vary, depending on the principals and the securables that are involved. However, the algorithm performs the following general steps:

  Bypass the permission check if the login is a member of the sysadmin fixed server role or if the user is the dbo user in the current database.
  Allow access if ownership chaining is applicable and the access check on the object earlier in the chain passed the security check.
  Aggregate the server-level, database-level, and signed-module identities that are associated with the caller to create the security context.
  For that security context, collect all the permissions that are granted or denied for the permission space. The permission can be explicitly stated as a GRANT, GRANT WITH GRANT, or DENY; or the permissions can be an implied or covering permission GRANT or DENY. For example, CONTROL permission on a schema implies CONTROL on a table. And CONTROL on a table implies SELECT. Therefore, if CONTROL on the schema was granted, SELECT on the table is granted. If CONTROL was denied on the table, SELECT on the table is denied.
  A GRANT of a column-level permission overrides a DENY at the object level.
  Identify the required permission.
  Fail the permission check if the required permission is directly or implicitly denied to any of the identities in the security context for the objects in the permission space.
  Pass the permission check if the required permission was not denied and the required permission contains a GRANT or a GRANT WITH GRANT permission either directly or implicitly to any of the identities in the security context for any object in the permission space.


Are there any disadvantages to partitioning on financial year? Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.

The majority of queries are within the financial year/one partition. Either select * from sales where date time between '2013-05-01' and '2014-04-30 23:59:59' or some period within that year.

My plan is to have a range partition on an InnoDB table. e.g.

PARTITION BY RANGE COLUMNS(datetime)
(PARTITION p0 VALUES LESS THAN ('1999-05-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2000-05-01') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('2001-05-01') ENGINE = InnoDB,
...

This means that the PK has to become PRIMARY KEY (index,datetime).

Are there any significant disadvantages to partitioning compared to having an unpartitioned table? I know that means the PK is now length 12 and all further indexes will have that prepended to it. Does that make a difference? The table needs to work faster on reads than writes, and there are a fair few indexes on it.

Is there any functional difference between partitioning on RANGE COLUMNS(datetime) and RANGE TO_DAYS(datetime) using MySQL 5.5+?
Is partitioning on datetime OK? One alternative is adding in a column for financial year and indexing/partitioning on that? (And then adding another index on datetime for other queries).

We do sometimes need to query the time across all time or over "the last X months", but this is pretty rare. The main advantages of moving to a single table is to eliminate the logic in the application working out which table to insert/update/select and not needing to calculate unions in those situations where we need more than one table.

Answering your questions above:

In RANGE COLUMNS (datetime) key word COLUMNS is unnecessary. It is used to specify a list of columns, where as you have only 1 column. The difference exists: for RANGE (datetime_column) you can specify partition boundaries in datetime format, whereas for RANGE TO_DAYS(datetime_column) partition boundaries should be effectively in date format.
Partitioning on datetime is OK and it is very often.

Please refer to MySQL Manual, Chapter 18. Partitioning.

If the only reason for you using partitioning is structuring data, then may I suggest you looking at MERGE Storage Engine. It allows to create a table consisting of other tables. When queries to this overarching table have data only in some tables, then only those tables are used. It is easier to set up and maintain, than partitioning.


How to create SOAP endpoint in SQL Server 2016? When I'm trying to create a SOAP endpoint in SQL Server 2016:

CREATE ENDPOINT Weather_CurConditions
STATE = Started
AS HTTP
(
    PATH = '/CurrentConditions',
    AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR), CLEAR_PORT = 8080,
    SITE = '*'
)
FOR SOAP
(
    WEBMETHOD 'GetWeather'(NAME = 'Weather.dbo.usp_GetWeather'),
    WSDL = DEFAULT,
    DATABASE = 'Weather',
    NAMESPACE = DEFAULT
);

I get the following error:

  Msg 7878, Level 16, State 1, Line 1
  This "CREATE ENDPOINT" statement is not supported on this edition of SQL Server.

Here is the output of SELECT @@VERSION:

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation   Enterprise Edition (64-bit) on Windows 8.1 Enterprise 6.3  (Build 9600: )

What can I do ?

You can no longer use SOAP/HTTP endpoints. This isn't a SQL Server 2016 problem, since this would not have worked in 2014 either. From the documentation on CREATE ENDPOINT:

  NOTE: Native XML Web Services (SOAP/HTTP endpoints) was removed in SQL Server 2012.

It has been deprecated for at least 8 years (this is the oldest version of the current document I could find):

Deprecated Database Engine Features in SQL Server 2008

If you look at that document, you will see that "The CREATE ENDPOINT or ALTER ENDPOINT statement with the FOR SOAP option" was deprecated at that time. Their suggestion for a replacement is to "Use Windows Communications Foundation (WCF) or ASP.NET instead."

Arguably, there could be a better error message, like the following (emphasis mine):

  Msg 7877, Level 16, State 1
  SOAP/HTTP endpoints are no longer supported in this version of SQL
  Server.

But your workarounds remain the same:

Continue using an older version of SQL Server.
Find another source for this information that doesn't require this type of information.
As the docs suggest, don't try to make SQL Server perform this work (it's not a web browser) - e.g. create an application or service that stores the data in SQL Server.


How does SQL Server store the information in Job Schedule Properties? I know there is SELECT * FROM msdb..sysjobschedules, but that just shows the next time a job is scheduled to run.

I want to know how this is all determined and stored.  So where does it store the value for Recurs every Monday check box?

Every 20 minutes (this seems to NOT be configurable), the 'next run' information in sysjobschedules is updated by looking at the information you can see in sysschedules.

You know how when you define a job you have to go through a slightly convoluted process of rather than just saying 'run this job every monday', instead defining a schedule that says that, and associating the job with the schedule? This is why - they have independent existences.


"truncate log on checkpoint" option in SQL Server Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB option named truncate log on checkpoint. Their claim is that it prevents the scripts from functioning and performing backups on the instance which is SQL 2012.

I feel that's complete BS since I cannot find any such option via sp_configure and backups are working everywhere but one instance. However I would like to remove a land mine, if that's what it is, and remove other obsolete elements. I do have them checking with the vendor but I don't have a high degree of confidence in anything that they say.

The research I did returned little more than it perhaps being an option for SQL 2000 or a Sybase option. Another claim was that it's called/used implicitly on later versions (2008 and up) when the recovery model is SIMPLE and there's no explicit option to turn it on or off.

Since the TRUNCATE LOG command is obsolete due to how transaction logs work these days, I think it's not an option that can even be queried at this point.

As I don't have any SQL Server 2000 instances about, I was hoping someone might recall this or could check it on one they have laying around. I've told them that it's nothing I can recommend. I was also hoping that someone can confirm this is obsolete.

You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well

In SQL Server 2012 and up, you will get a nice error

  Msg 2812, Level 16, State 62, Line 1
  Could not find stored procedure 'sp_dboption'.

Its there lying for backward compatibility ONLY. In SQL Server 2000 and up, SQL Server has a concept of recovery model that defines how the log truncation is honored e.g. In simple recovery - only a checkpoint will truncate the log where as in full recovery mode - only a log backup will truncate the log.

If you want to understand more about Transaction Log Management then refer to Stairway to Transaction Log Management

As s side note, SybaseASE even in newer version Sybase 15.7 has this option (As a matter of fact it has duplicate trunc log on chkpt and trunc. log on chkpt. the later is the one that SQL Server got in SQL 7.0 through SQL Server 2008R2 - seen in sp_dboption):


How to delete the primary index of a table in Progress, release 11.6? I have made a table, containing an integer field, this field being used in the primary index of that table. Now I would like to replace this integer field by a character one. Therefore I need to remove all indexes of that table, including the primary one. Therefore I'm using the data dictionary, but I get following error message:
You cannot delete the primary index of a table.

How can I proceed?
Thanks

You need to first designate a different index as "primary" before you can drop the existing index. The data admin tool has a menu option for doing so:
Database Schema Admin DataServer Utilities PRO/SQL Tools
┌──────────────────────────────────────────────────────────────────────────────┐
│Name: CustNum                    PRIMARY UNIQUE     ACTIVE
│      ┌──────────────────────────────────┐
│Area: │  Cust_Index                      │
│      └──────────────────────────────────┘
┌─────────────────────────────┐┌───────────────────────────────────────────────┐
│ Comments                    ││Seq Field Name            Type        Asc  Abbr│
│ CountryPost                 ││─── ───────────────────── ─────────── ──── ────│
│ CustNum                     ││  1 CustNum               integer     Asc  No  │
│ Name                        ││                                               │
│ SalesRep                    ││                                               │

│                             ││                                               │
│                             ││                                               │
└─────────────────────────────┘└───────────────────────────────────────────────┘
│     Next  Prev  First  Last  Rename  Add  Delete  ChangePrimary  Uniqueness
└──── MakeInactive  Browse  SwitchTable  GoField  ROWID  Undo  ChangeLocal  Exit
 Index Editor                                                  Data Dictionary
Database: s2k (PROGRESS)               Table: Customer

Change the Primary Index of this table.

(Your screen may look different depending on what version of Progress you are running and whether or not it is Windows. But the functionality is the same...)
If you do not have another index you will first need to create one.


Count number of rows and sum up a field of all tables in all relevant schemas with a readonly connection We have a rather complex database setup which involves 500 schemas. Most of these schemas contain a certain table. This table contains a certain number of rows and those rows contain a size field.

I need to iterate the schemas, detect those schemas that contain the relevant table and count the number of rows and sum up the size field.

The correct SQL statement for one schema looks like this:

SELECT
    TRUNC(SUM((ceil(SMART_REPOSITORY_LOGICAL_FILE.SRLF_SIZE / 4096)*4096)) / (1024*1024),0) AS
    "Volume in MB",
    COUNT(*) AS "number of files"
FROM
    SCHEMA_XYZ.SMART_REPOSITORY_LOGICAL_FILE
WHERE
    SMART_REPOSITORY_LOGICAL_FILE.SRLF_IS_DIRECTORY = 'N'

How could I run this over all schemas that contain the table SMART_REPOSITORY_LOGICAL_FILE ?

I already tried answers available here that involve some XML magic but obviously I don't have the permission to use those calls because I am rejected. I also can not use approaches that involve writing to the schemas.

Did you miss Tony Andrew's solution @ answer ?

Here's a dbfiddle example based on Tony's solution: find count and sum from same table w/ different schemas

For the sake of this example I'll setup a second AUDIT_ACTIONS table:

create table AUDIT_ACTIONS as (select * from SYS.AUDIT_ACTIONS where ACTION <= 50)

Create a table to store our results in:

create table results(owner varchar(100), table_name varchar(100), row_count integer, audit_sum integer);

Some minor tweaks to Tony's solution:

declare
    v_count integer;
    v_sum   integer;
begin
    for t in (select table_name, owner from all_tables
              where table_name = 'AUDIT_ACTIONS')
    loop
          EXECUTE IMMEDIATE 'select count(*), sum(ACTION) from ' || t.owner || '.' || t.table_name
              into v_count, v_sum;
          insert into results (owner, table_name, row_count, audit_sum)
          values (t.owner, t.table_name, v_count, v_sum) ;
    end loop;
end ;
/

And the results:

select * from results;

OWNER                       | TABLE_NAME    | ROW_COUNT | AUDIT_SUM
:-------------------------- | :------------ | --------: | --------:
FIDDLE_QPLKSTJBEESOQLZCONZA | AUDIT_ACTIONS |        51 |      1275
SYS                         | AUDIT_ACTIONS |       180 |     18451

NOTE: The non-SYS OWNER(SCHEMA) will change with each dbfiddle run.


Which is the uniqueness level of object_id? Which is the uniqueness level of object_id in SQL Server? I mean, is this value unique per database, per instance, per server,... Also, what happens when I restore the database in another server or instance? And what happens with the object_id if I create the database from a script instead of a backup?

The object_id column is unique per database. Two objects in separate databases can have the same object_id, however separate objects in the same database have always different object_id values.

Every time you drop and create an object, a new object_id value is assigned automatically and there is no way to influence which value is chosen. However, if you alter an object using the ALTER statement, the object_id stays the same.

Because there is no way to influence the value chosen for object_id, there is also no way to script out a database in an object_id preserving fashion, so if you re-create a database using a t-sql script there is no correlation between the previous object_id values and the new ones.

A database backup on the other hand copies the actual data pages one to one, so object_id values will stay unchanged.

In general it is a good idea to stay away from the object_id value in a production system. You cannot use it to detect a change nor a no-change situation. The only thing it should be used for is to filter or join system tables and DMVs. You can also use it to conditionally drop an object like this: IF(OBJECT_ID('dbo.objectname') IS NOT NULL) DROP... For anything else you should consider that value as off limits.


Limit crosstab query based on join from outer query In my database I have two tables, assessments, and responses, in a 1:N relationship.

I have a query that looks a little something like this

  WITH "cross_responses" AS (select * from crosstab(
       $$select
         assessments.id as id,
         i.name as name,
         r.value as response
       from assessments
         left join users u on u.id = assessments.user_id
         inner join responses r on r.assessment_id = assessments.id
         inner join items i on i.id = r.item_id
       where i.name = ANY ('{E-mail Address, First Name, Last Name}')
       order by 1,2$$,
       $$VALUES('E-mail Address'), ('First Name'), ('Last Name')$$
     ) as ct(id int, email text, first_name text, last_name text)
) SELECT  ...blah blah...
ORDER BY "assessments"."id" desc, assessments.end_time DESC NULLS LAST,
assessments.start_time DESC NULLS LAST LIMIT 1000 OFFSET 4000

So basically, I'm creating a CTE made up a crosstabbed results for key responses from the assessment, then joining it two the assessments table, and selecting to get a nice flat representation mixing meta-data from the assessment, with the answers to individuals questions.

As you can see at the bottom, this query gets run in batches of 1000 when we are streaming a CSV file of all the results over HTTP.

I believe this results in a potential performance issue, because for each batch, I'm assuming crosstab will run against the entire assessment and responses tables, not against just the batch of defined by the outer query. Running explain analyze seemed to confirm this for as the crosstab line in the explain output, says it runs over 5080 rows, which is all the assessments in my development database (production has a lot more)

What I'm asking 1) Is my understanding about how crosstab behaves correct? 2) Is there a way to make the query passed into cross tab "aware" of limitations or conditions applied to the outer query? 3) Is there another approach I should be taking entirely to performantly run this query in a batched fashion to stream the data out to CSV.

EDIT: Adding the results of explain analyze

 Limit  (cost=1813.08..1813.08 rows=1 width=124) (actual time=71.913..72.036 rows=1000 loops=1)
  CTE cross_responses
    ->  Function Scan on crosstab ct  (cost=0.00..10.00 rows=1000 width=100) (actual time=63.593..64.074 rows=5080 loops=1)
  ->  Sort  (cost=1800.58..1803.08 rows=1000 width=124) (actual time=71.568..71.817 rows=5000 loops=1)
        Sort Key: assessments.id, assessments.end_time, assessments.start_time
        Sort Method: quicksort  Memory: 861kB
        ->  Hash Join  (cost=1712.00..1750.75 rows=1000 width=124) (actual time=66.358..69.842 rows=5080 loops=1)
              Hash Cond: (cross_responses.id = assessments.id)
              ->  CTE Scan on cross_responses  (cost=0.00..20.00 rows=1000 width=100) (actual time=63.595..65.204 rows=5080 loops=1)
              ->  Hash  (cost=1645.89..1645.89 rows=5289 width=28) (actual time=2.751..2.751 rows=5289 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 289kB
                    ->  Seq Scan on assessments  (cost=0.00..1645.89 rows=5289 width=28) (actual time=0.008..1.879 rows=5289 loops=1)
Total runtime: 72.384 ms

If all the export can happen through a single database connection, or even better, through a single transaction, it would be simpler and more efficient to just use a server-side CURSOR instead of a pagination based on successive queries with LIMIT/OFFSET.

For instance:

BEGIN;

DECLARE cursor_name FOR
WITH "cross_responses" AS (select * from crosstab(
       $$select...
        ...
) SELECT  ...blah blah...
ORDER BY "assessments"."id" desc, assessments.end_time DESC NULLS LAST,
assessments.start_time DESC NULLS LAST

Then call the SQL statement FETCH 1000, stream the results to the web client, until FETCH returns no row. Then close the transaction, and the cursor will disappear with it.

It's also possible to not use a transaction if the cursor is declared WITH HOLD.

If multiple passes on the query must be run by independant connections (such as if there are multiple invocations by a web server), another possibility is to not use the crosstab() function, and do the pivot operation in the main query itself. This is not difficult because there are only 3 static columns.

For instance:

 select
  assessments.id as id,
  max(case when i.name='Last Name' then r.value end) as "Last Name",
  max(case when i.name='First Name' then r.value end) as "First Name",
  max(case when i.name='E-mail Address' then r.value end) as "Email"
 from assessments
     left join users u on u.id = assessments.user_id
     inner join responses r on r.assessment_id = assessments.id
     inner join items i on i.id = r.item_id
 where i.name = ANY ('{E-mail Address, First Name, Last Name}')
 group by assessments.id
 order by 1 LIMIT 1000 OFFSET 4000;


Create a DELETE statement that deletes Manufacturers with less than 10 sales Orders:
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)

Customers:
ID (Primary Key)
Name
Phone Number
Street (ie. 505 Ramapo Valley Road)
City (ie. Mahwah)
State
Zip

Machines:
Serial Number (Primary Key)
Model Number (Primary Key for Models table)

Model:
ID (Primary key)
Speed
RAM
HD
Price
Manufacturer ID (Primary key for Manufacturer table)

Manufacturer:
ID (Primary Key)
Name
Phone Number
Email Address

Above are tables with different attributes.
I have a lab due soon and one of the tasks is to create an SQLite statement to DELETE Manufacturers with less than ten sales. I have tried to write a sub query, but am not able to formulate a statement that works. The following is what I have so far:
(I know I am off by a lot)
DELETE FROM Manufacturer
HAVING COUNT(*) < 10
WHERE ID IN
(SELECT ManufacturerID FROM Model WHERE Manufacturer
    WHERE Manufacturer.ID = Model.ManufacturerID

SELECT ModelNo FROM Machines
    WHERE Machines.ModelNo = Model.ID

SELECT SerialNo FROM Orders
    ON Orders.SerialNo = Machines.SerialNo

So you're not too far off, you're just missing a couple key things, the first being the JOIN keyword. JOIN is how you can combine two related datasets. There are different types of joins, one being an INNER JOIN which is how you can combine two datasets and only return the rows that match the join condition. For example, if you wanted to get only Customers who have an Order and you want to know the Name of that Customer and the SerialNumber of their Order you could do so with an INNER JOIN like so:
SELECT Customers.Name, Orders.SerialNumber
FROM Customers
INNER JOIN Orders
    ON Customers.ID = Orders.CustomerID

You can join as many datasets as you like in a single query. So if we also wanted to know the ModelNumber for the above Customers' Orders, we could modify the above query like so:
SELECT Customers.Name, Orders.SerialNumber, Machines.ModelNumber
FROM Customers
INNER JOIN Orders
    ON Customers.ID = Orders.CustomerID
INNER JOIN Machines
    ON Orders.SerialNumber = Machines.SerialNumber

Deriving from the above example we can do the same to get a list of all Manufacturers' Orders, by chaining together a bunch of INNER JOIN clauses. Lets only return the ID of the Manufacturer as well, since that's all we care about right now. Note since this is a list of all Manufacturers' Orders, there will be one row for every Order, so you should see the same ID of the Manufacturer repeated in cases where they have more than one order:
SELECT Manufacturer.ID
FROM Manufacturer
INNER JOIN Model
    ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
    ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
    ON Machines.SerialNumber = Orders.SerialNumber

Now you need a way to count how many Orders (how many rows) there are for each Manufacturer.ID from the previous query. To do that we can use the built in COUNT() function. Because we want to count the number of Orders for a certain criteria (by Manufacturer), we'll also need to use the GROUP BY clause. The GROUP BY clause allows us to roll-up (flatten) all the rows by the criteria we specify into a single unique row per grouping based on the columns we specify. In this case we just want a single row per Manufacturer.ID so that when we use the COUNT() function, it counts the number of Orders within that grouping, like so:
SELECT Manufacturer.ID, COUNT(*) AS OrderCount
FROM Manufacturer
INNER JOIN Model
    ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
    ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
    ON Machines.SerialNumber = Orders.SerialNumber
GROUP BY Manufacturer.ID

So the above query now gives us the list of Manufacturer IDs and the count of how many Orders each one has. Let's filter it down to only the Manufacturers who have less than 10 Orders. We will use the HAVING clause to accomplish this, which is the equivalent of using a WHERE clause to filter data but can only be used with aggregate functions like COUNT() on grouped data:
SELECT Manufacturer.ID, COUNT(*) AS OrderCount
FROM Manufacturer
INNER JOIN Model
    ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
    ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
    ON Machines.SerialNumber = Orders.SerialNumber
GROUP BY Manufacturer.ID
HAVING COUNT(*) < 10 -- Filter out everything except Manufacturers with less than 10 Orders

Finally, we want to use a subquery as you mentioned, so we can DELETE the results in our above query from the Manufacturer table like so:
DELETE FROM Manufacturer
WHERE ID IN
(
    SELECT Manufacturer.ID
    FROM Manufacturer
    INNER JOIN Model
        ON Manufacturer.ID = Model.ManufacturerID
    INNER JOIN Machines
        ON Model.ID = Machines.ModelNumber
    INNER JOIN Orders
        ON Machines.SerialNumber = Orders.SerialNumber
    GROUP BY Manufacturer.ID
    HAVING COUNT(*) < 10 -- Filter out everything except Manufacturers with less than 10 Orders
)

Note that when you use a subquery inside of the IN keyword, you can only return one column, and for your case you only care about the ID of the Manufacturers for who needs to be deleted. So I removed the OrderCount column from the subquery above.
There are definitely alternative ways to accomplish the above as well, but to keep the concepts you need to understand to a minimal, this is a pretty good solution. The concepts I hope you took away from this are:

Joins, specifically the INNER JOIN clause, and you should research Outer Joins as well.
Aggregate Functions such as COUNT(), there are many others too such as MIN(), MAX(), and SUM(), etc.
The GROUP BY clause and how grouping works in general.
The HAVING clause and how and when to use it.