Best practice guidelines for a application table archive in Oracle 12C Database environment: Oracle 12C Enterprise edition

I have a table  in the application schema and corresponding history table in the history schema.
The application  table has about 10 small varchar2 columns with a maximum row size of 200 bytes.
Once a week ( call it a period) an Oracle scheduler job will run a stored procedure to extract the data out of application table and insert into the corresponding history table.
The history table will have the year and period number columns in addition to the application fields.
A separate asynchronous process purges the application table after the rows are transferred to the history table (outside the scope)
The  history table will contain a year worth of data from the corresponding application table. New history table will be created each year.
The  history table data is queried primarily by the application id column  and other criteria including the year and period

At this time I am not considering a partitioned table design... With the above design goals in mind, I am considering following points in designing my tables and load stored procedures -

setting up compression at the table space level for the history schema
Direct-path  insert with append hint
Make indexes unusable before load and usable after loading

My Question:  Looking for recommendation on the design I am considering.

You can use Flashback Data Archive to keep track of previous versions of a table. With that feature you can query the table as it looked at any point in time (within your retention period). That does sound like a more flexible approach than the one you are building (re-inventing the wheel).

1 and 2 does sound very straightforward good ideas. 3 depends on the amount of data you are loading. unusable indexes will hurt your application during the load.
I would consider putting each load into a separate partition. Then it is very easy to get rid of data after a year - just drop the entire partition. And I assume that you often will look at data at a specific point in time - in which case the optimizer can use partition pruning if you construct the SQL correctly.


Multiple query to columns indexed in MySql I am trying to make a table with server-side page, so it can also apply filters for each of the columns in my table. This is its structure:

+----------------+
|    products    |
|----------------|
| id       int   |
| title    string|
| date     date  |
| category string|
+----------------+

so it occurred to me to create a composite index for the columns that I want to filter:

CREATE INDEX index_name
ON products(c2,c3,c4);

The problem, as the documentation says, is that the query optimizer cannot use the index to perform searches if the columns do not form a prefix to the left of the index. then I could not make a WHERE condition to a specific column if it is not in the correct order of the index, which is a problem for me, since my query could include one or more conditions in any order, according to the user's need for example:

SELECT * FROM products WHERE title = ""
SELECT * FROM products WHERE date = ""
SELECT * FROM products WHERE category = ""
SELECT * FROM products WHERE title = "" AND date = ""
SELECT * FROM products WHERE title = "" AND date = "" AND category = ""

So an index of multiple columns is not the best option for my case, so I thought to add an individual id to each of my columns, is this a good practice or is there another way to address this problem?

This index will take care of 3 of your queries:

INDEX(title, date,  -- in either order
      category)     -- last

Then you will need two more indexes to handle the other two queries.  Assuming you use the order above, then these could be the other two.

INDEX(category)
INDEX(date)

If you have a dozen columns and users can filter on virtually any combination of them, then all you can do is guess at what is best.  Some general rules:

don't make more than a dozen indexes
use about 3 columns in each index
be sure to start some of them with the most commonly used filters

But also, ...

If the filtering is on a range (eg, price range), it should be last in the index.
Don't listen to people who talk about cardinality when arguing for a particular order of columns in a composite index.
If title is too big to be indexed, then leave it out of all indexes.  Do not use "prefix indexing" like title(20).

More discussion:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


Clustered index on random order column I have an existing table named “Orders” in our system. OrderID is the primary key in this table – it is a clustered index. I have a new table designed  as shown below for “OrderCompanyDetails”. It has a 1-to-1  relationship with Orders table. In the new table OrderID is kept as the clustered primary key.

Data gets inserted into the new table only when the Order is approved. So the OrderID getting inserted into the new table may not be in sequence. The OrderID 10 may get inserted before OrderID 5, depending on which order is approved first.

Having the clustered index on OrderID  helps my queries. But the clustered index is on a column which gets data in a random sequence. Is it a bad index design? If yes, should I add a new meaningless identity column with name OrderCompanyDetailID and make that as clustered index?

CREATE TABLE [dbo].[Orders]
(
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderType] [char](3) NOT NULL,
    [StatusCD] [char](10) NOT NULL,
    [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF__Orders__CreatedOn]  DEFAULT (getdate()),
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
    (
        [OrderID] ASC
    )
)

CREATE TABLE [dbo].[OrderCompanyDetails](
    [OrderID] [int] NOT NULL,
    [POCompanyCD] [char](4) NULL,
    [VendorNo] [varchar](9) NULL,
    [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF_OrderCompanyDetails_CreatedOn]  DEFAULT (getdate()),
    CONSTRAINT [PK_OrderCompanyDetails] PRIMARY KEY CLUSTERED
    (
        [OrderID] ASC
    )
)

UPDATE

Recently read article Ever-increasing clustering key – the Clustered Index Debate……….again!. There is a comment in that

  Remember – narrow, static, unique, ever increasing – and usually a surrogate key instead of a natural key.

The recommendation for clustered indexes is that they are ever-increasing or ever-decreasing, but that doesn't mean they have to be. GUIDs aren't increasing or decreasing unless you are using sequential GUIDs. Most people don't use sequential GUIDs.

If you are worried about the performance hit from page splits, then lower your fill factor to accommodate more inserts before a page split is needed. This is what is advised for GUIDs too if the GUID is the clustered index (I'm not saying it should be the clustered index, I'm saying if it is). Be careful how low you lower it though as it will impact read performance, which probably is important to you since you mentioned the clustered index on OrderID helps your queries.


Postgres tablespace size is bigger than database size PostgreSQL tablespace size is bigger than database size. What is the problem about that?

SELECT pg_size_pretty(pg_database_size('ibidev'));
 pg_size_pretty
----------------
 2578 MB
(1 row)

SELECT pg_size_pretty(pg_tablespace_size('tbs_ibi_dat'));
 pg_size_pretty
----------------
 47 GB
(1 row)

Using PostgreSQL 9.1

A tablespace can contain tables from more than one database.

The extra space must be from another database.


Querying system health data becomes slow as event volume increases We get the data from system health XE from SQL server and load into table for later querying.

Part of that statements which gets pretty slow is

INSERT INTO TABLE1
      (
         [object_name]
         ,[col1)
       )

SELECT [Object_name], CAST (event_data as XML) AS eventcol1

FROM sys.fn_xe_file_target_read_file (N'D:\SQL\system_health*.XEL', NULL,NULL,NULL)

Above is real slow when fetching events over 200k

Table structure is

CREATE TABLE [dbo].[Table_1](
    [object_name] [nvarchar](60) NOT NULL,
    [col1] [xml] NULL
) ON [DEMO] TEXTIMAGE_ON [DEMO]
GO

The only index on the table is  clustered indexon object_name [non unique]

I see from msdn that columnevent_data     is nvarchar(max)

Update
I am able to repro this situation for 42K records in system health which runs for 7-8 seconds and same goes over 50 minutes when there are over 0.5 million records to fetch from system health

link - PLan-- This on all versions starting SQL2014 and above where conversion warning is displayed.

Warning does not show up on SQL 2012 therefore i used trace flag 9481 but process is still slow

Plan without warning

Please suggest

You should check out This is how to shred Extended Event XML faster.

The gist is to

Put the XML into a variable.
Shred the XML rows into a temp table.

Also, see below answer by Martin Smith

Optimising plans with XML readers


How to convert the column sum-function-total to dollars in Oracle SQL? I am unable to format the below query intending to display the column-alias total re-formatted to display as dollars.  Though unsuccessful, I suspect a nested solution combined with TO_CHAR, is appropriate but I haven't succeeded with my attempts.

SELECT SUM(TO_CHAR(invoice_total,'$999,999.99')) AS "Total Invoice Amount"
FROM company.invoices;

Can anyone please assist?  Thank you.

This was a simple-syntax issue, which I unable to see due to my growing frustration-thank you.

SELECT TO_CHAR(sum(invoice_total),'$999,999.99') AS "Total Invoice Amount"
FROM company.invoices;


How to insert or update using single query? I have a table test having columns id which primary key and auto incremented and name.
I want to insert a new record if annd only if there are no records.For example

input is id=30122 and name =john

if there are records with id 30122 then I have update the name column to john,if there are no records then I have insert a new record.

I can do using 2 queries like

select * from test where id=30122

if it has some records then I can use update test set name='john' where id=3012

or if it does not have records then I can use

insert into test(name) values('john')

But I wanted to use single query?

Can somebody tell if its possible?

You can try this

IF EXISTS(select * from test where id=30122)
   update test set name='john' where id=3012
ELSE
   insert into test(name) values('john');

Other approach for better performance is

update test set name='john' where id=3012
IF @@ROWCOUNT=0
   insert into test(name) values('john');

and also read this bad habits to kick on schema prefix


How to select rows which have max and min of count? I have the following table in SQL Server 2012:

CREATE TABLE [MyTable] (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
    [MyGroup] INT NOT NULL
);

I want to output a table of the form

    | MyGroup | Count
Max |         |
Min |         |

For example, if I have 30 rows in MyTable where MyGroup = 1, 20 rows where MyGroup = 2, and 10 rows where MyGroup = 3, then

    | MyGroup | Count
Max | 1       | 30
Min | 3       | 10

What sort of query would output this information?

This is an alternative formulation of Travis's answer which avoids the need to sort the COUNT in both directions.

WITH s
     AS (SELECT MyGroup,
                Count(MyGroup)              AS [Count],
                MAX(Count(MyGroup)) OVER () AS [MaxMyGroup],
                MIN(Count(MyGroup)) OVER () AS [MinMyGroup]
         FROM   MyTable
         GROUP  BY MyGroup)
SELECT Agg,
       MyGroup,
       V.[Count]
FROM   s
CROSS APPLY (VALUES ( 'Max', CASE WHEN [Count] = [MaxMyGroup] THEN [Count] END),
                     ('Min', CASE WHEN [Count] = [MinMyGroup] THEN [Count] END))
             V(Agg, [Count])
WHERE V.[Count] IS NOT NULL


Disabling Resource Governor in SQL Server Should we restart SQL Server after disabling Resource Governor? What is the best practice?

Why I am asking this, because I am worried about the existing open sessions in SQL Server. Will their behavior change(in terms of CPU, Memory and IO) to default if we only disable Resource Governor and not restart SQL Services?

For example if a particular session was capped to use 30% of total memory of system because of resource governor. So if we disable Resource governor now alone, will it start using more than 30% instantaneously?

I change Resource Governor settings quite often without restarting SQL Server. I am not aware of any need to restart SQL Server after changing Resource Governor settings except possibly for a classifier function change. A few things that might be helpful to know:

Once a session is assigned to a Resource Governor workload group it stays there, even if the classifier function changes.
I have never seen query plan compilation account for Resource Governor settings.
Some aspects of query execution are decided at the start of query execution, such as MAXDOP and query memory. Note that there are some exceptions where that isn't always true such as certain types of index creation or queries that execute in batch mode.

A precise answer that fully answers when the changes are applied to queries (whether or not it is "instantaneous") is likely outside the scope of an answer here. I suggest figuring out what you care about and doing testing as needed to convince yourself that what SQL Server is doing is okay with your workload. For example, consider the MAXDOP limit available through Resource Governor workload group configuration. For nearly all queries MAXDOP is decided at query execution time, so I wouldn't expect disabling Resource Governor to change the MAXDOP of a currently executing query. However, the next query that starts for the session will no longer be limited in MAXDOP. However, what happens if there's a Scalar UDF executing many times during a query with a parallel plan? I'd expect MAXDOP to possibly change for that query, but I've never tested that and don't care to test it. If I had to guess, I suspect that all of the following will be updated "instantaneously":

MIN_CPU_PERCENT
MAX_CPU_PERCENT
CAP_CPU_PERCENT
MIN_IOPS_PER_VOLUME
MAX_IOPS_PER_VOLUME
IMPORTANCE
REQUEST_MAX_CPU_TIME_SEC
GROUP_MAX_REQUESTS

and all of these to update only after the next query starts executing:

MIN_MEMORY_PERCENT
MAX_MEMORY_PERCENT
AFFINITY
REQUEST_MAX_MEMORY_GRANT_PERCENT
REQUEST_MEMORY_GRANT_TIMEOUT_SEC
MAX_DOP


Understanding SQL Server encryption (by password)? After asking this I still have a question.

A company need to store sensitive data. Please let's not talk about certificate right now.

A DBA does the following :

  CREATE SYMMETRIC KEY SecureSymmetricKey1
  WITH ALGORITHM = DESX
  ENCRYPTION BY PASSWORD = N'blabla';

A programmer wants to encrypt data and does the following :

  -- open key
  OPEN SYMMETRIC KEY SecureSymmetricKey1
  DECRYPTION BY PASSWORD = N'blabla'

  -- actual encrpyt
  DECLARE @encrypted_str VARBINARY(MAX)
  SET @encrypted_str =  EncryptByKey(Key_GUID('SecureSymmetricKey1'),'my data');

Another programmer wants to READ the data so he does :

  DECLARE @decrypted_str VARBINARY(MAX)
  SET @decrypted_str = DecryptByKey(...encrypted_str...)

All fine.

Questions :

When a programmer opens a symmetric key he must the know the password. I don't think that a programmer should know the password. How can this be solved ?
If a GOD hacker got the  entire .BAK file and he restore the backup onto his own machine - he can view the stored procedure source which one of the programmers wrote, and see the password. and then the HACKER can do :

OPEN SYMMETRIC KEY SecureSymmetricKey1
DECRYPTION BY PASSWORD = N'blabla'

What am I missing ?

Thanks for helping.

There are really only two options:

Automatic key decryption Ie. the key hierarchy includes an encryption by the service master key (usually through the database master key) and the engine is able to decrypt and or encrypt the data when needed. This protects against accidental loss of media, but anyone with access to the running server has access to the encrypted data (subject to access permissions, not subject to cryptographic protection). Transparent Data Encryption TDE is the high-end version of this type of access.
Explicit session provided decryption key The application must ask the user for the key decryption password and open the keys explicitly in each session it uses. The engine itself cannot decrypt the data. This protects against any unauthorized access to the data by cryptographic means (even if one can access the engine and has access rights to read the data, he cannot use the data w/o actually knowing the password). Needless to say having the application prompt the user for password is annoying at best on desktop apps, and down right impossible in web apps (the web apps needs to store the password to use it between requests, and that's a huge problem).

These are the only alternatives. Never ever is there any need to have the password stored in any configuration file or stored procedure. Any scheme that requires to store the access password somewhere is doomed from the get go and you need to rollback to page one of your design and start from scratch.


AlwaysOn Auto failover. Make it faster. I cause a 2016 Server Standard Database to AlwaysOn Auto failover (its a test DB single table), I have to wait up to 60 seconds to connect again via the listenser to the standby.

No data is lost. But 100% up time is not.

What can I tweak to speed it up?

I wrote Windows Console app to run the tests and specify TrustServerCertificate=True in connection. It loops, connects and gets data.

Thank you for the code - it helps! The encrypted connection information shouldn't have any bearing on this, though.

  The latency is always 28 seconds, following failover.

This can happen for a variety of reasons. There are some subtle nuances that make it seem like it isn't actually up. Let's take a look.

string connetionString = "Trusted_Connection=yes;database=MyDataBse;server=fslistener,1433;Encrypt=True;TrustServerCertificate=True";

The connection string is not setting something called Connection Timeout which handles the way a connection is deemed to have timed out or failed. This is in seconds. The default is 15 seconds. Knowing this...

Assume SQL Server failed and we needed to fail over. The fastest this application would connect is the time it takes to failover. This is a combination of a few things:

The cluster detecting an issue
The cluster arbitrating of resources
SQL Server recovering the databases

Let's also assume that the cluster is setup with defaults. This means it'll take a minimum of 5 seconds to notice that something is wrong. It may take longer, depending on how the node fails up to a maximum of the SQL Server healthcheck interval (default 30 seconds).

You can already see that finding the failure can take anywhere from 5-30 seconds, by default.

The next step is initiating the failure and arbitrating for resources. This should take a trivial amount of time, a few seconds at most. This is where the listener is moved and becomes responsive to new login attempts.

Additionally there is the time is takes for the databases to redo and become available - especially if there is a default database specified that is part of the AG (versus one that isn't, say master). This can take anywhere from near instantaneous to many minutes depending on use and hardware subsystems. Let's assume, for this, it only takes 3 seconds.

Adding this all up we have:

5 seconds to find the failure
2 seconds to arbitrate and come online
3 seconds for the database to be available

This totals out to 10 seconds. Now, remember the Time Out I was talking about earlier. If the application attempts to connect and receives a login timeout, that means we waited 15 seconds and didn't get a response. This doesn't mean we constantly tried for 15 seconds, it means we sent a request and patiently sat there, waiting. Adding in a single timeout wait of 15 seconds now brings our total up to 25 seconds. That's assuming a whole bunch of things.

Wrapping up

In the end, the actual failover time will be held in the SQL Server errorlog by finding the start of the failover and the database online messages. Additionally, extended events can be used to find the actual failover times.

The application may (most likely will) see a longer "downtime" due to the way it is coded. In this instance, a default timeout value of 15 seconds is most likely adding and additional overhead. The code also sleeps for 1 second between retries which makes a total of 16 seconds. The connection string has a default database which means it takes us even longer to connect as the database is required to be online for the connection to be established. It takes time to detect and arbitrate the failover, this needs to be taken into account.

More information: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout(v=vs.110).aspx


Checking if a transitive key dependency is in 3rd normal form I'm currently trouble determining if one of my schemas is in third normal form.

I have a table with columns image_id (A), upload_time (B), uploader_id (C), and image_file (D). A is a key (unique identifier for every row in the table), and BC is also a key (every pair of uploader and upload time is unique). Thus, we have A -> BCD, and BC -> A (and transitively BC -> A through A -> BCD).

Is this table in 3rd normal form? I'm not entirely sure, because I know 3NF allows for a partial key transitive dependency (e.g. XY -> Z, Z -> X), but I'm not sure if it allows for a total key transitive dependency like I have above.

A simple check to see if a relation schema is in Third Normal Form is to see if every left hand side of non-trivial functional dependencies is a superkey (and this test can be used also to check if a relation is in Boyce-Codd Normal Form), or, if this is not the case, if every attribute on the right part is a prime attribute (i.e. part of any candidate key).

So in you case both the dependencies A → BCD and BC → A (and also BC → D) have a candidate key as left part, so the schema is in Third Normal Form (and in this case also in the more strict Boyce-Codd Normal Form).


Key lookup partition pruning I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each:

SELECT A.COL1, B.COL2, C.COL3
FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID
WHERE COL20 < 10000  ---- COL20 IS NOT THE PARTITION KEY COLUMN

In the actual query execution plan, for one of the tables, there is a non-clustered index scan with a key lookup.

When I look at the properties for Key lookup in the actual execution plan, it looks like it's getting partition pruned.

I'm confused as to why that would happen, is this like a negative impact on the system. I understand that key lookups themselves are bad, but why does it show that only 7 partitions have been accessed? The properties say:

Key Lookup (Clustered)
----------------------------------
Actual number of rows:      215805
Actual Partition Count:     7
Actual Partitions Accessed: 3..9

My concern is around how the lookup works. In case, when there is no partitioning, when the lookup fetches the data from the data page for a particular key, would it do a seek or a scan to reach to this page? Would a direct key lookup perform better than a key lookup  with partition pruning?

Only rows in 7 partitions qualify the predicate(s) so there is no need to ever lookup rows in the other 3 partitions.

The query plan may choose to scan on B and probe (lookup) on A.

A Key Lookup is always a seek into the clustered index. There is no partition elimination in your original example by the way - the query processor is just reporting that it touched 7 distinct partitions (3..9) while executing the query. None of the lookups accessed other partitions.


Load-balanced MySQL cluster without load balancer I'm looking to create a load-balanced MySQL cluster, but without the actual load-balancer, in order not to add another point of failure or complexity.

What I was thinking was to have the following:

Have a master-master setup for MySQL
On every client, place a simple round-robin proxy which would rotate the requests between servers.

Is this possible? Or there are better ways to achieve this?

Please read my other answer to this question before actually using a MySQL proxy of any kind. If You have 2 master-master servers that a CMS is writing to, and 10 httpd that only read from it, You'll be fine, but (as pointed out in the other answer) that's not always the case. You've been warned.

  MySQL Proxy is a simple program
  that sits between your client and
  MySQL server(s) that can monitor,
  analyze or transform their
  communication. Its flexibility allows
  for unlimited uses; common ones
  include: load balancing; failover;
  query analysis; query filtering and
  modification; and many more.

.

  HAProxy is a free, very fast and
  reliable solution offering high
  availability, load balancing, and
  proxying for TCP and HTTP-based
  applications

If You would run it in TCP mode, it could be even better than Wackamole. If I had to choose between them, I would use HAProxy. Also HAProxy can have a lot of backends, Waclamole can have only 2. Note that HAProxy is "dumb", it connects sockets without any looking on what's inside the stream - dedicated MySQL Proxy might f.e. have an option to point various requests to specified servers.


What should I ask at a MySQL DBA interview? I am a software developer and am helping my team hire a MySQL DBA. The core challenges that we are facing are:

Slower queries and performance due to Hibernate.
Database management (backups, tuning, patches, security).
Scalability due to increase in data from new data sources and accumulation of older data.
We plan to start data mining and data warehousing in the future. Not sure how but that is the direction.

We usually have programming cases where we ask developers to build something for an interview, but it's a bit hard to do a DBA interview in the same fashion.

Can you give suggestions on how the interview should be conducted?

Not a complete list, but rough list of things I would try to cover. It also depends on whether they will be the first "true" DBA or in a DBA team. Whether the DBA is responsible for the machines, too, or only the database on top of that. etc.

what RAID configuration should be used
backup strategies for databases.
MySQL specific things like differences between MyISAM and InnoDB
backup strategies and recovery
Let them do some SQL queries and some query optimization (usage of explain etc. even when using hibernate. Sometimes it's good to bypass hibernate to gain performance)
did I mention backup strategies
For scalability a DBA should know about different replication modes (RBR, SBR, mixed mode replication, replication maintenance such as observing replication lag and binlog maintenance)
InnoDB tuning
What kind of files are being written by the DB (eg, ibdata and log files) and how they can be arranged (eg, one ibdata file per table, move them around on different partitions, InnoDB compression)
Discuss monitoring tools. What are you using, do they have experience with that tool or a similar one?
I'd also look into system tools like iostat/memstat/vmstat/whatever your OS provides.  Give them a system with some load and let them find the cause
And maybe discuss issues in MySQL backup and especially restore ;-)

I'm sure others here can extend this list


weird sql server behavior when inserting and selecting Today after one of the drives on our sql server ran out of space. The drive contained eventracker logs, so I didn't think it was a big deal, but then I noticed when I insert a record into a database, it inserts it above previous records instead of appending it.

When I do select * the records that were inserted show up first. Is there something I can do to fix this?

section_d   section
-------------------
15          SDSD
16          SDSD2
17          SDSD2
18          adf

(4 row(s) affected)

insert into test2 values ('inserted row')

section_d   section
-----------------------
19          inserted row
15          SDSD
16          SDSD2
17          SDSD2
18          adf

(5 row(s) affected)

Databases do not return rows in a given order unless you supply an ORDER BY clause in your query, thus making the INSERT "order" meaningless. The order of a SELECT * FROM MYTABLE; query is undefined.

Apologies for the simple answer!


Is it possible to see the (completed) SQL Statements executed by a specific session? Is it possible to see a list of all past SQL statements executed by a specific session (or number of sessions)?

For example, the following query gives me all currently executing statements from all sessions where the machine is VZWCLIENT8. But I would like to see the past execution.

select s.sid, s.serial#, a.sql_text
from v$session s
join v$sqlarea a on a.hash_value = s.sql_hash_value
where upper(machine) like 'VZWCLIENT8'

yes, read up on FGA (fine grained auditing).

http://www.oracle-base.com/articles/10g/auditing-10gr2.php

this should do what you want to accomplish.


How to insert values in junction table for many to many relationships? I have three tables in database:

 trips(trip_id(pk), trip_name(unique), user_id(fk))

 places(place_id(pk), place_name(unique))

 trips_places_asc(trip_id(fk), place_id(fk))

Since many trips can have many places, I have one junction table as above.

If a user inserts places to the trip, the places will be added to places table and the trip will be associated with the places in trips_places_asc table.

So, if I write  a query like:

INSERT INTO places (place_name)
VALUES ('XYZ')

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

then how to store trip_id and place_id in Junction or Association table trips_places_asc? Will I have to fire two queries?

You want to maintain atomicity of the action of storing a new place and trip, along with the intersection between the two.  In MySQL, you do this with a START TRANSACTION / COMMIT transactional wrapper.

You use the MySQL function LAST_INSERT_ID() (See documentation; similar to SQL Server variable @@IDENTITY) to get the value of the most recent auto increment value assigned.

To add a place and a trip and associate the two all at once, you would write code like this:

START TRANSACTION

DECLARE placeKey int

INSERT INTO places (place_name)
VALUES ('XYZ')

SET placeKey = LAST_INSERT_ID()

DECLARE tripKey int

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

SET tripKey = LAST_INSERT_ID()

INSERT INTO trips_places_asc(trip_id, place_id)
VALUES (tripKey, placeKey)

COMMIT

You should add error handling to the above and if an error occurs part way through, use the ROLLBACK command to undo the partially completed work.

You should also read up on LAST_INSERT_ID() to make sure you know how it works.  It can give you undesirable results in some cases, such as when you insert multiple rows with a single insert statement.


Ignore login mappings during Dacpac compare We're trying to automate the deployment of databases through development, testing and production. Our approach, using SSDT, is to compare DACPACs from the different environment to generate update scripts, and as a final step, map the correct windows logins to the correct users.

/p:IgnoreUserLoginMappings

Looking at the documentation for SqlPackage.exe there is a property IgnoreUserLoginMappings that seems to imply that it would ignore mappings, so that they are not included in the dacpac and hence not updated during the comparison and script generation.
But, when we use this property during our extract the users generated are WITHOUT LOGIN, like this in the dacpac:

<Element Type="SqlUser" Name="[Domain\Account]">
  <Property Name="IsWithoutLogin" Value="True" />
    <Relationship Name="DefaultSchema">
      <Entry>
        <Annotation Type="PersistedResolvableAnnotation" Name="[Domain\Account]">
                <Property Name="TargetTypeStorage" Value="SqlSchema" />
        </Annotation>
         </Entry>
    </Relationship>
</Element>

which is NOT what we want! The subsequent generated update looks something like this:

CREATE USER [Domain\Account] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [Domain\Account];

Are we doing something wrong or is this a bug, I've searched and can't see much info on this property, is anyone using it that can help, or is there another approach?

Related question: here

I found using SqlPackage.exe will not drop security objects.

I was having the same issue as well. Going forward, we are going to deploy database changes using DACPACs. We need a way to exclude users, roles and logins from the update process so they are not dropped from the target SQL Server.

Our database is installed at hundreds of clients sites and many SQL Server installations have had their security setup by the DBAs at that company. They will often add their own domain logins and users and in some cases tighten down roles more than our defaults.

I noticed that when choosing the option to "Upgrade Data-Tier Application" within SQL Server Management Studio 2012 I did not have the option to ignore security objects. The review actually stated it was going to drop users and roles.

But when I used SqlPackage.exe it did not drop the security objects. This is because the parameter "DropObjectsNotInSource" is set to false by default.

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:.\bin\debug\SandBox.dacpac /TargetServerName:sandboxserver /TargetDatabaseName:SandBox

The only downside is that if you want to drop deprecated objects from the database, you will need to add the drops to the post deployment script.

I hope Microsoft will add this feature in the future. These objects are often managed by DBAs and not developers.

Vote here: https://connect.microsoft.com/SQLServer/feedback/details/775839/ssdt-add-publication-setting-to-ignore-database-users


Can I apply production statistics to an empty local database to test PostgreSQL planner hypotheses? I'm toying with the idea of exporting a database's statistics from production and applying them to a test database (same structure, small dataset). The goal is to test software for changes that introduce expensive query plans in a non-production environment before the change goes to production.

I don't see any prior art on this. How might I go about applying statistics from one database instance to another instance, and lock those statistics down to perform this sort of test?

You cannot readily do that.  pg_statistic uses a bit of magic to be able to store the statistics in a column of type anyarray.  There is no ready way to access this magic from user-land, even if that user is the super-user.


Partition trigger and serial column concurrence I'm using the following trigger to auto generate monthly partitions for a given table. It works fine.

create or replace function beat_insert_function()
returns trigger as $$

declare
  partition_date text;
  partition_name text;
  start_of_month text;
  end_of_next_month text;

begin
  partition_date := to_char(new.created_at, 'yyyy_mm');
  partition_name := 'beat_' || partition_date;
  start_of_month := to_char((new.created_at), 'yyyy-mm') || '-01';
  end_of_next_month := to_char((new.created_at + interval '1 month'), 'yyyy-mm') || '-01';

  if not exists (select 1 from information_schema.tables where table_name = partition_name) then
    raise notice 'A partition has been created %', partition_name;
    execute format(E'create table %I (check(date_trunc(''day'', created_at) >= ''%s'' and date_trunc(''day'', created_at) < ''%s'')) inherits (public.beat)', partition_name, start_of_month, end_of_next_month);
  end if;

  --- this generates only par keys
  execute format('insert into %I (something) values ($1)', partition_name) using new.something;

  return null;
end

$$
language plpgsql;

This trigger is configured to run before insert, like so:

create trigger beat_insert_trigger
before insert on beat
for each row execute procedure beat_insert_function();

Because the trigger intercepts each insert and the master table contains a id serial column primary key, I ended with only par keys (2, 4, 6, 8, etc), so I changed the insert to:

  execute format('insert into %I (id_beat, created_at) values (currval(''beat_id_beat_seq''), $1)', partition_name) using new.created_at;

Now, the primary key is correct (1, 2, 3, 4, etc)...

My question is: is it safe to use currval in the given scenario? Can the table receive another insert before the trigger finishes, thus creating an insert error, or will the session prevent this from happening?

Your code is safe, because according to the documentation:

  Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

But why don't you do the natural thing and

EXECUTE format(
           'insert into %I (id_beat, created_at) values ($1, $2)',
           partition_name)
USING NEW.id_beat, NEW.created_at;


Query for returning query plans from sql plan cache that contain 1 or more warnings? Is there a query I can run to return query plans from sql plan cache that contain 1 or more warnings?

RedGate has a great article Checking the Plan Cache Warnings for a SQL Server Database on how to query the plan cache to find plans with warnings.
Ultimately the warnings will be stored in the XML query_plan column of sys.dm_exec_query_plan DMV.


Need some help in Group by StateID - TSQL Query Can anyone help me on below scenario I need small help on group by state ID

Below is my query and I am tried to get all the out put in single line .

But I am getting different lines for each year . please help me to get all the output in single row as the State ID is same.

SELECT  StateID,
SUM(CASE WHEN [Year] = 2016 THEN FileSize END) AS FileSize_2016,
(Select count(Fileid) where year = '2016' ) AS TotalFs2016,
SUM(CASE WHEN [Year] = 2017 THEN FileSize END) AS FileSize_2017,
(Select count(Fileid) where year = '2017' ) AS TotalFs2017,
SUM(CASE WHEN [Year] = 2018 THEN FileSize END) AS FileSize_2018,
(Select count(Fileid) where year = '2018' ) AS TotalFs2018,
SUM(CASE WHEN [Year] = 2019 THEN FileSize END) AS FileSize_2019,
(Select count(Fileid) where year = '2019' ) AS TotalFs2019
--FileState
FROM
(
SELECT StateID,[Year],FileID,FileTotalSizeMBs AS FileSize  --, FileState
FROM #Temp1
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp2
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp3
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp4
)t
where StateID like '570'
GROUP BY
t.StateID , year

Current out put gives 4 rows for different years like 2016 , 2017 , 2018 , 2019 along with the Size of the files.

Where are I want to get only one row as put with all years data as the ID is same.

I tried to GROUP BY but still does not help .

Thank you for the help.

Group by isn't needed since you only select one stateid

SELECT  StateID,
  SUM(CASE WHEN [Year] = 2016 THEN FileSize END) AS FileSize_2016,
  count(case when year = 2016 then Fileid end ) AS TotalFs2016,
  SUM(CASE WHEN [Year] = 2017 THEN FileSize END) AS FileSize_2017,
  count(case when year = 2017 then Fileid end ) AS TotalFs2017,
  SUM(CASE WHEN [Year] = 2018 THEN FileSize END) AS FileSize_2018,
  count(case when year = 2018 then Fileid end ) AS TotalFs2018,
  SUM(CASE WHEN [Year] = 2019 THEN FileSize END) AS FileSize_2019,
  count(case when year = 2018 then Fileid end ) AS TotalFs2019
--FileState
FROM
(
  SELECT StateID,[Year],FileID,FileTotalSizeMBs AS FileSize  --, FileState
  FROM #Temp1
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp2
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp3
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp4
)t
where t.StateID like '570'


Can oscillating number of fragment decrease FTS query performance? I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly.

Reading Guidelines for full-text index maintenance I dig around sys.fulltext_index_fragments and noticed the following:

The number of fragment oscillate between 2 and 20 at high frequency
(multiple times per minutes);
The biggest fragment contains ~105 rows and is 11Mb;
The others contains from 1 to 40 rows.

Can this oscillating number of fragment mess up SQL Server's execution plan selection?

What can I do to clean up this?

Fragmentation can cause query performance issues if there is a significant number of them. I've generally seen it with fragments counts greater than 50 to 100 fragments for the specific full-text index. Not generally noticeable with 20 or less as you describe. It also sounds like master merge is doing its job if you see the number of fragments changing from 2 to 20 and then back down again. This would indicate that as population runs and increases the fragments that the master merge process is then reducing the number of fragments.

Execution plans will not change as a result of fragmentation because Full-Text is implemented via a TVF. So all you ever see in the execution plan is a TVF no matter how many fragments there are. If you are seeing execution plan changes, it is likely due to other reasons than fragmentation. Regrettably, there is currently no way to see what queries actually are in the TVF to resolve your full-text search request. The more complicated your search request, the more work full-text may need to do.

If you are experiencing plan changes and believe that full-text is the reason for the plan changes, it is more likely due to the estimated/actual number of rows the TVF is expected to return. Another thing that could affect performance is what point the TVF is joined to the table and whether or not the plan processed the TVF first or the table first. Search conditions for full-text obviously affect how many rows are returned so the one question I would have is do you get the same performance repeatedly for the same exact search term? Does the performance change as search terms are changed? Is there any wait stats for the query or is the query getting blocked? All these things may help further understand things.

More work is needed to fully understand why you are getting variable performance with full-text.

MORE INFORMATION
Fragments are created during population. This helps with speeding up the population process. Internally, each fragment is a separate table, so think of querying a lot of fragments as UNION of all the fragment tables. As you can imagine, the fewer of them the better the performance.

Many users of full-text confuse the rebuilding of a regular index and think it is also necessary to "rebuild" the full-text index, but actually the opposite is true.

A rebuild of a full-text catalog causes fragmentation of the index(es) because a rebuild is doing a full population and population creates fragments to speed up population. You should only ever rebuild a full-text index if it is corrupted or you change word breakers, ifilters, stop lists, - essentially anything that would change what is stored in the index.

At the end of a full or incremental population, the master merge process (what reduces fragments) automatically runs. However, there is a limit on the number of simultaneous merge processes that can be run at the same time. This is especially important to understand because if you have a lot of full-text indexes on the SQL Instance or you put several full-text indexes into the same full-text catalog and you either rebuild OR reorganize AND the population of several indexes finish near or at the same time it can exceed the threshold. This can result in the fragments for those merge process that exceed the threshold to go into a wait state and the end result is that the fragments for the affected full-text indexes is not reduced. See full-text population slow

Hope this helps.


MySQL 5.6 - Creating an event inside a stored procedure I am trying to create a procedure which create an event in MySQL.

For example, something similar to this:

delimiter //
CREATE PROCEDURE schedule_my_db_job()
  BEGIN

    CREATE EVENT MY_DB_JOB
    ON SCHEDULE EVERY 1 MINUTE DO
    BEGIN
        SELECT * FROM information_schema.events; -- for example
    END;

  END//
delimiter ;

But it gives me following error when I try to create this procedure.

ERROR 1576 (HY000): Recursion of EVENT DDL statements is forbidden when body is present

As I understand MySQL allow stored procedures to have CREATE EVENT sql statement inside. (Quotation from https://dev.mysql.com/doc/refman/5.6/en/create-event.html  "You can create an event as part of a stored routine, but an event cannot be created by another event")

Could you please let me know what is the error here?

Thanks.

It seems there is a conflict in MySQL documentation.
I think CREATE EVENT Syntax section in MySQL documentation is not correctly updated in later versions. Although "CREATE EVENT Syntax" sections says

  You can create an event as part of a stored routine, but an event
  cannot be created by another event.

In Event Scheduler Restrictions section in mysql documentation,
It says

  An event may not be created, altered, or dropped by a stored routine,
  trigger, or another event. An event also may not create, alter, or
  drop stored routines or triggers. (Bug #16409, Bug #18896)


ALTER TABLE ADD CONSTRAINT failing when executed from function So I have 2 functions that I am writing, one to drop all foreign constraints for a schema, and the other will recreate these foreign constraints as they once were.

The drop constraints function works by selecting various "deferrable constraint levels" into respective temp tables (e.g. temp tables for: fully_deferrable, not_deferrable, semi_deferrable) to make sure that information exists for the subsequent recreate constraints function.  I am running into an error though that will not allow me to create the constraint because this constraint already exists.  So the recreate constraints function will error out with something like: " ERROR: constraint "avg_test_k_school_fkey" for relation "avg_test" already exists", even though I have already dropped this foreign constraint via the previous function.

I have checked the tables to make sure all the foreign constraints are dropped, and when I run the ALTER TABLE statement outside of the function, the foreign constraint is added with no problem.  So I am wondering what in the world is going on with executing this from the function.  I am thinking that perhaps I am doing something wrong control-flow wise, or that these operations are not possible to call from functions.  Please let me know if you have any questions!

This function is for the recreation of foreign keys. not_deferrable_constraints is a temp table that has the child table, parent table, whether or not a constraint is deferred, the fields of the primary/foreign keys, and the name of the constraint.

Here is the SQL:

 CREATE OR REPLACE FUNCTION test.recreate_all_foreign_constraints(schema_name name)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
_tbl record;
add_constraint record;
begin

--Recreate not_deferrable_constraints
for _tbl in(select child_table from not_deferrable_constraints) loop
    for add_constraint in(select constraint_name, fk_column, pk_column, parent_table from not_deferrable_constraints where child_table = _tbl.child_table) loop
        execute 'ALTER TABLE ' ||schema_name||'.'|| _tbl.child_table || ' ADD CONSTRAINT ' || add_constraint.constraint_name || ' FOREIGN KEY ('|| add_constraint.fk_column ||') REFERENCES '|| add_constraint.parent_table ||' ('|| add_constraint.pk_column ||')';
    end loop;
end loop;
end;
$function$
;

It would be super cool if this worked! I'm so close I can feel it!! Thanks for your help!

I think you need:

for _tbl in(select DISTINCT child_table from not_deferrable_constraints) loop

If a table has more than one constraints, your code will try to recreate each one of them many times.

Or just use a single for loop, I don't think you need the nesting.

--Recreate not_deferrable_constraints
for add_constraint in (select constraint_name, fk_column, pk_column, parent_table, child_table
                       from not_deferrable_constraints) loop
    execute 'ALTER TABLE ' ||schema_name||'.'|| add_constraint.child_table || ' ADD CONSTRAINT ' || add_constraint.constraint_name || ' FOREIGN KEY ('|| add_constraint.fk_column ||') REFERENCES '|| add_constraint.parent_table ||' ('|| add_constraint.pk_column ||')';
end loop;


All Flash Storage Configuration Our co is getting a new SAN with all flash storage which would be attached to a physical 8 core 48 GB RAM server. I have been studying what is the best way to configure this setup. Our DB has mostly read transactions, write occurs only during midnight when data is interface from core ERP system to this data warehouse DB. There really isn't any performance issues in our old setup right now, except the usual long running queries while crunching millions of financial records every now and then.

So, I have some concern about setting partition offset, file allocation unit size and stripe unit size. I know that file allocation unit size should be 64kb for datafiles volume, stripe unit size provided by vendor is 256kb, but i am not sure about setting the partition offset. But before that, does this 3 parameters even matter when we are using SSDs now? I get that this 3 parameters is important to improve IOPs on traditional mechanical harddrives, but on SSDs, does it matter at all?

Also, since the new setup is all flash, does it matter to split data files, log files and tempDBs into their each volume?

Would appreciate if anyone could provide some insights or advice on the configuration part.

The guidelines for different volumes were more of general best practices when you're trying to squeeze your performance/cost ratio.  Outside of that, putting them in different volumes not only helps segregate workload which could cause performance to be affected by SSD functions such as TRIM or how it deletes data for example, but also helps you with management.

Transaction logs segregation is also very important for ACID compliance.  If you don't have your Tlogs in a different volume and the data volume goes down, you just lost all of your transactions that were not backed up yet.  See Tail of the log backup.  This way you know you can still recover the transactions and restore to a point in time or right before the crash.

Partition alignment is done at the disk level by the OS so you'd still want to ensure you are partition aligned.  Some flash drives might not need that such as the FGPA powered ones which treat SSDs differently a bit but why not double check anyways?  It's easy and fast to.

The 64kb is done more so by SQL Server because that's how big an extent is.  Your vendor could make it 256kb but you should get clear documentation as to why they recommend that.  Do they really read that much data per operation?  If so, great, set it.  If not, stick to what SQL Server as the underlying DB engine needs.

Personally on my all flash storage arrays based on my performance testing and expected growth I tend to put Data/TempDB on the same volume, Transaction Logs on a different one, and backups on another one.  This way if for some reason a backup all of a sudden is huge, or some job for some horrible reason doesn't delete some old data we don't interfere with the data drives.  I test restore and check every backup nightly but I trust my scripts to delete the data which has worked fine.  However, if one day it doesn't, I will know as the backup will fail, there will be no restore, and I'll be alerted along with the dashboard.  These could be mitigating controls you could utilize if you wish.

Edit/update based on question:

Well TempDB will always create as much IO as it needs to depending on your queries, but from what I read you were doing almost all reads and not writes.  TempDB would then be doing the bulk of the writes in your use case.  Thus it would be easy to determine how much tempdb is writing and see if it gets closet to the IOPS limit you have available.  From what I read your old system didn't have any performance issues and the new SSD as opposed to magnetic disk system would be much faster unless you shrunk a massive raid array into 1 disk or something.

When you're factoring in if TempDB contention is going to be an issue or not note the higher IOPS you get with a bigger RAID array as well.  A RAID 5/6 array will still give you all the read performance boost without penalty so in your use case you might get more IOPS by combining your TempDB and data array together, than you would segregating them; again based on usage pattern.

Easy answer?  Check perfmon to see your throughput and if you know the queries are largely going to remain the same (reasonably) and it should show you have to much in IOPS that it doesn't matter which one you go with.  Pick the one you'd be more comfortable managing and scaling with. :)

-Tail Of The Log Backups:

Yes, if your transaction log drive(s) go down you will lose any transactions that have not been hardened to disk somewhere (a dirty page) and have not been yet backed up.  Careful monitoring and proper raid arrays are mitigating controls for that, but many people also replicate/mirror/AG the data elsewhere so there is a copy that is very closely up to date.  Some people like Brent Ozar prefer to run transaction log backup scripts continuously and restore them dynamically from queries if needed.


Enabling LOAD DATA LOCAL INFILE in mysql LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian 7, but not Debian 7 minimal, though both installations come from the same precompiled deb package. Both on OpenVZ VPS.

How to debug why local-infile=1 does not work for an installation, and how to safely activate LOAD DATA LOCAL INFILE?

If the MySQL Debian-7 minimal cannot use local_infile, look around all the make files used for compiling to see if it is disabled by default or if local_infile is enabled for the Debian-7.

Before taking that kind of time, please run the following:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile';

It should echo the following:

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile = 'ON';
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

If it still says 'OFF', then look deep within the compiler settings to enable it.

If it sets to 'ON', you are OK.

Please note I did not say

SET GLOBAL local_infile = 1;

I said use

SET GLOBAL local_infile = 'ON';

The option local_infile is Boolean, not numeric.

If setting this in my.cnf

[mysqld]
local_infile=ON

and restarting mysql does not work either,  you will have to start up mysql with something like this:

echo "SET GLOBAL local_infile = 'ON';" > /var/lib/mysql/init_file.sql
chown mysql:mysql /var/lib/mysql/init_file.sql
service mysql stop
service mysql start --init-file=/var/lib/mysql/init_file.sql
rm -f /var/lib/mysql/init_file.sql

or perhaps adding this to my.cnf

[mysqld]
init-file=/var/lib/mysql/init_file.sql

then restart mysql.

Give it a Try !!!


Most efficient data type for a column storing numeric values between 0 and 100 On my MySQL DB I was thinking of using TINYINT (Unsigned)?

Would you use Byte on SQL Server?

This answer only covers SQL Server. The answer depends on how you define efficient: is it space or CPU? There can be a tradeoff between the two.

Let's start by checking the documentation for data types that store integer data:

╔═══════════╦══════════════════════════════════════════════════════════════════════════╦═════════╗
║ Data type ║                                  Range                                   ║ Storage ║
╠═══════════╬══════════════════════════════════════════════════════════════════════════╬═════════╣
║ bigint    ║ -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) ║ 8 Bytes ║
║ int       ║ -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)                         ║ 4 Bytes ║
║ smallint  ║ -2^15 (-32,768) to 2^15-1 (32,767)                                       ║ 2 Bytes ║
║ tinyint   ║ 0 to 255                                                                 ║ 1 Byte  ║
╚═══════════╩══════════════════════════════════════════════════════════════════════════╩═════════╝

For your data you could use TINYINT because all of your data fits within 0 to 255 and that would use the least amount of space. Let's do a quick test by inserting 10 million rows into tables with values equally distributed between 0 and 100. Note that we use ten columns for all example tables because a rowstore table has a minimum row size of 9 bytes. If we created a table with just one column we would get misleading results. I am testing against SQL Server 2016 SP1:

DROP TABLE IF EXISTS dbo.X_TINYINT;

CREATE TABLE dbo.X_TINYINT (
    NUM1 TINYINT NOT NULL,
    NUM2 TINYINT NOT NULL,
    NUM3 TINYINT NOT NULL,
    NUM4 TINYINT NOT NULL,
    NUM5 TINYINT NOT NULL,
    NUM6 TINYINT NOT NULL,
    NUM7 TINYINT NOT NULL,
    NUM8 TINYINT NOT NULL,
    NUM9 TINYINT NOT NULL,
    NUM10 TINYINT NOT NULL
);

INSERT INTO dbo.X_TINYINT WITH (TABLOCK)
SELECT TOP (10000000)
  n.n, n.n, n.n, n.n, n.n
, n.n, n.n, n.n, n.n, n.n
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
CROSS APPLY
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 101
) n (n);

EXEC sp_spaceused 'dbo.X_TINYINT'; -- data size is 198216 KB

If I run the same code against a table with 10 SMALLINT columns I get 297416 KB for the data size. Based on the quoted size difference in data types I would expect a difference of about 10000000 * 10 * (2 - 1) / 1024 = 97656 KB so that's pretty close to the expected size increase.

Depending on your version and edition of SQL Server you may be able to reduce space usage further. Row compression is first available in SQL Server 2008 Enterprise and is available in all editions of SQL Server as of 2016 SP1. Based on the description of the algorithm we probably won't get a lot of savings with row compression for TINYINT columns. They already use the minimum one byte, but we should get around a 1% space reduction for the data because 0 is optimized to take no bytes. There also may be some reduction in the metadata overhead for the data types.

After applying DATA_COMPRESSION = ROW to a table I get a data size of 187808 KB.

Page compression is available in the same versions and editions as row compression. The page compression algorithm compresses the data in a few additional ways on top of row compression. With a a lot of repeated values on a page we might see significant storage gains.

After applying DATA_COMPRESSION = PAGE to a table I get a data size of 109024 KB, which is a fairly sizable reduction.

Just for fun we can check the space usage when the data is in columnstore format. Columnstore indexes were introduced in SQL Server 2012 and further improved in 2014 and 2016. These should not be used just for the purposes of saving space. You will want to research and test carefully before using them. There are also some restrictions around using them based on your SQL Server version and edition.

DROP TABLE IF EXISTS dbo.X_TINYINT_CCI;

CREATE TABLE dbo.X_TINYINT_CCI (
    NUM1 TINYINT NOT NULL,
    NUM2 TINYINT NOT NULL,
    NUM3 TINYINT NOT NULL,
    NUM4 TINYINT NOT NULL,
    NUM5 TINYINT NOT NULL,
    NUM6 TINYINT NOT NULL,
    NUM7 TINYINT NOT NULL,
    NUM8 TINYINT NOT NULL,
    NUM9 TINYINT NOT NULL,
    NUM10 TINYINT NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_X_TINYINT_CCI ON dbo.X_TINYINT_CCI;

INSERT INTO dbo.X_TINYINT_CCI WITH (TABLOCK)
SELECT TOP (10000000)
  n.n, n.n, n.n, n.n, n.n
, n.n, n.n, n.n, n.n, n.n
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
CROSS APPLY
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 101
) n (n)
OPTION (MAXDOP 1);

EXEC sp_spaceused 'dbo.X_TINYINT_CCI'; -- data size is 160 KB

I can also compress the CCI using COLUMNSTORE_ARCHIVE, which I think is designed for historical data which won't change or be read often. With that compression option applied to the whole table the data size is further reduced to 88 KB.

The rowstore compression options add CPU overhead to queries. The amount of overhead will depend on your workload and data but we can use a simple query to illustrate the basic concept:

SELECT MAX(NUM1), MIN(NUM1)
FROM dbo.X_TINYINT
OPTION (MAXDOP 1);

After one test I got CPU time measurements of 1469 ms for uncompressed data, 1687 ms for data with row compression, and 2000 ms for data with page compression. I didn't do tests for the columnstore data just because they work so differently. MIN and MAX queries can be locally aggregated or even satisfied by metadata operations in some cases.

Here is a summary of the results for the test table and query:

╔═══════════════════╦══════════════════╦══════════════════════╗
║ Table Compression ║ Data Space in KB ║ Query CPU Time in ms ║
╠═══════════════════╬══════════════════╬══════════════════════╣
║ NONE              ║           198216 ║ 1469                 ║
║ ROW               ║           187808 ║ 1687                 ║
║ PAGE              ║           109024 ║ 2000                 ║
║ CCI               ║              160 ║ N/A                  ║
║ CCI ARCHIVE       ║               88 ║ N/A                  ║
╚═══════════════════╩══════════════════╩══════════════════════╝

The exact results that you will see depend on your table structure, data, and workload.


Difference between DATE(time_field) AND time_field in a WHERE clause I have 3 tables:

users
movements
unique_ids

I want to get the records gotten from march until now of the users registered in march, counting the unique id of their movements with a distinct and the total of it at the end:

SELECT MONTH(mo.ctime) AS month, COUNT(DISTINCT(unique_id))
FROM users us, movements mo,unique_ids id
WHERE us.time_field > '2014-03-01' AND us.time_field < '2014-03-31 23:59:59'
AND mo.time_field > '2014-03-01'
AND us.room_name LIKE 'name'
AND us.room_name=mo.room_name AND us.user=mo.user AND id.id_log=mo.id_log
GROUP BY month WITH ROLLUP

The thing is that few month ago in other query I realise that adding DATE() to the time_field into WHERE clause makes faster the query, but adding this into the query shown, gives me different results than the initial one.

What's the difference between time_field > 'x' AND DATE(time_field) > 'x' inside WHERE clause?

Just addressing this question

  What's the difference between time_field > 'x' AND DATE(time_field) > 'x' inside WHERE clause?

The difference is how the Query Optimizer treats them.

If the WHERE clause has time_field > 'x', this signals the Query Optimizer to try looking for any index so as to take advantage of it doing a range scan.
If the WHERE clause has DATE(time_field) > 'x', this signals the Query Optimizer to throw all indexes under the bus because the DATE function has to be called across the whole table (in the form of a full table scan) or the join result.

Depending on the key distribution of time_field, DATE(time_field) > 'x' triggering a full table scan just happens to be better than a range scan on an index if the values make up a significant percentage of the index. This is even more true using InnoDB because a full table scan passes through the clustered index (where PRIMARY KEY and row data coexist), while a secondary index on time_field would cause a lookup of the secondary index  in addition to the clustered index. If such a secondary index had a lopsided key distribution, such an index would be ignored in favor of a full table scan anyway.

This conjuecture is only indicative of your current dataset. Someone else's dataset may have a better (evenly distributed, more balanced) key distribution, resulting in time_field > 'x' working better than DATE(time_field) > 'x'.

To see such differences, run the EXPLAIN on both SELECT queries. The results may be different.


GRANT REFERENCE on all user defined types The original problem is: a user, who has the permission to create/alter view granted is getting this message when trying to perform either operation:

  Cannot find the type 'nvarchar', because it does not exist or you do
  not have permission.

Through some internet digging I found it may be caused because he does not have GRANT REFERENCE permission on types. However,

select * from sys.types order by name

Returns over 2000 records, and GRANT REFERENCE command accepts a single type as parameter. How can I grant this user the reference permission for ALL types?

If you need to do something like this, just use dynamic SQL to generate the command.

SELECT t.name,
       'GRANT REFERENCES ON TYPE::'
       + SCHEMA_NAME(t.schema_id)
       + '.'
       + t.name
       + ' TO public;' AS command_to_run
FROM   sys.types AS t;


Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example:

A:

ID | Name | Age
1  | Bob  | 25
2  | Dan  | 30
3  | Tina | 20
4  | Val  | 36
5  | Joe  | 51
6  | Mel  | 22

B:

AID  |  Color
1     |  Blue
1     |  Green
1     |  Black
3     |  Green
4     |  Blue
2     |  Red
5     |  Green
3     |  Red

Now Suppose I want to get the rows of A amended and ordered by the count of references each row has in B. In this example I would want

ID | Name | Age | Count
1  | Bob  | 25  | 3
3  | Tina | 20  | 2
2  | Dan  | 30  | 1
4  | Val  | 36  | 1
5  | Joe  | 51  | 1
6  | Mel  | 22  | 0

One way I've basically managed to do this is the following query:

Select Count(*), A.ID, A.Name, A.Age, B.Color From
  B Left Join A
  On B.AID=A.ID
  Group By A.ID
  Order By Count(*) Desc, B.Color Desc

That will return:

Count | A.ID | A.Name | A.Age | B.Color
3     | 1    | Bob    | 25    | Blue(not actually sure what color would go here)
2     | 3    | Tina   | 20    | Green
1     | 2    | Dan    | 30    | Red
1     | 4    | Val    | 36    | Blue
1     | 5    | Joe    | 51    | Green
1     | 6    | Mel    | 22    | NULL

This works because I can guarantee that the null values will show up after the rows that do have a non-null color due to the order by. However, the counts here are not exactly what I'm looking for, I'd prefer to have Mel's row display a 0 for the count.

I'm sure there is a better way to perform this type of query, does anyone have any suggestions on how I could make this cleaner?

Thanks!

You can either use the suggestion suggested by @Thomas Cleberg. Count([attribute]) will only count the rows where [attribute] is not null, whereas count(*) will count all rows

Another option is to do the count in a sub select:

select id, name, age, cnt
from (
    select A.ID,
           A.Name,
           A.Age,
           ( select Count(*) from B where B.AID=A.ID ) AS Cnt
    FROM A
) as T
Order By cnt Desc

I've added a nested level for convenience with the order by


make random numbers for each row and column I will edit some fake data in my database. But if I will make random numbers for each row and column, it is not so random as I want it. The result can you see on image below.

Here is my code I use:

WITH x AS
(
    SELECT mintwee, mineen, nul, pluseen, plustwee
    FROM Topic
)
SELECT mintwee = CAST(RAND()*100 AS INT),
       mineen = CAST(RAND()*100 AS INT),
       nul = CAST(RAND()*100 AS INT),
       pluseen = CAST(RAND()*100 AS INT),
       plustwee = CAST(RAND()*100 AS INT)
FROM x

and this:

WITH x AS
(
    SELECT
        mintwee = CAST(RAND()*100 AS INT),
        mineen = CAST(RAND()*100 AS INT),
        nul = CAST(RAND()*100 AS INT),
        pluseen = CAST(RAND()*100 AS INT),
        plustwee = CAST(RAND()*100 AS INT)
    FROM Topic
)
SELECT mintwee, mineen, nul, pluseen, plustwee
FROM x ;

But both has the same result. I know, I must use an UPDATE statement, but this is only for try if the result is good. After it, I am going to use the UPDATE statement.

I use SQL server with an express 2014 version.

Since there is no correlation between the table rows and the RAND function, the expression is evaluated only once in the query.

One method to generated random values is with the NEWID() function.  The example below generates random integer values between 0-99 range.  The modulo value can be adjusted for a different range and you can use this basic technique to generate random values for dates, character, etc.

WITH x AS
(
  SELECT mintwee, mineen, nul, pluseen, plustwee
  FROM Topic
)
SELECT mintwee = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       mineen = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       nul = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       pluseen = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       plustwee= CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100
FROM x;

EDIT:

Per Aaron's comment, the expression can be simplified if you need only 32-bit integers.  Additional examples:

--postive int
SELECT ABS(CHECKSUM(NEWID()));
--postive and negative int
SELECT CHECKSUM(NEWID());
--postive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 7) AS bigint);
--negative and postive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 8) AS bigint);
--dates between now and 1 year ago
SELECT DATEADD(day, -ABS(CHECKSUM(NEWID())) % 365, GETDATE());
--random uppercase character
SELECT CAST(CAST(65 + ABS(CHECKSUM(NEWID()) % 26) AS binary(1)) AS char(1));


SET QUOTED_IDENTIFIER issue I have created a Database Project in Visual Studio 2010 for a database which is in 2008.
I am having issues in deploying the database as I don't know why, but at the time of deployment in some procedures

QUOTED_IDENTIFIER is set to OFF

But I intended to switch in ON at the time of deployment.

Is there is any setting from which I can control QUOTED_IDENTIFIER settings in my database project?.
Is there any place within database project from where i can switch it on by default at the time of creation or altering..?

QUOTED_IDENTIFIER is a connect-specific setting.  You can see what sessions have it set through the sys.dm_exec_sessions DMV.

But if you are looking to set the default of the instance, you can configure this through sp_configure:

exec sys.sp_configure 'user options', 256
go
reconfigure with override
go

See this BOL reference on user options config.

To set this on the database level, you would do:

alter database YourDB
set quoted_identifier on
go

BOL reference

Within Visual Studio 2010, the option can be found under Properties -> Database.sqlsettings.  Below is a screenshot showing the option:


Can't get my SQL query to filter by dates like I want I have a query where I want to exclude rows if a date column has any one of a set of dates.

I am having trouble figuring how to return the correct result sets without filtering one month at a time.

My initial attempts assumed the operator '!=' would be able to specify multiple variables, but I might be mistaken.

How do I filter the results in such a way that removes all the specified dates I do not want?

SELECT * INTO IPS_TransactionDataAll2
FROM
[IPS_TransactionDataAll1]
where Format([Start Date], 'MM/yyyy') != ('07/2016''08/2016''09/2016''10/2016''11/2016''10/2016''01/2017''02/2017''03/2017''04/2017''05/2017''06/2017''07/2017')

Pseudo-code:

Select into TableB
from TableA
Where dates not in the months 7-11/2016 and 1-7/2017

!= doesn't support a list of items like that.

Try one of these (note the commas between items):

WHERE Format([Start Date], 'MM/yyyy') NOT IN ('07/2016','08/2016','09/2016',...)

or even better (and faster), without converting to text (if your date range is truly consecutive like your example):

WHERE [START Date] NOT BETWEEN '2016-07-01' AND '2017-07-31 23:59:59'

Since between is inclusive of the endpoints, that is equivalent to:

WHERE [START Date] >= '2016-07-01'
AND   [START Date] <  '2017-08-01'


Tree structure hierarchical query sorting I have the following structure of table.

CREATE TABLE table1
(
  serial_num integer NOT NULL,
  parent_num integer,
  parent_key text,
  key_ary_num integer[],
  level integer,
  rank integer,
  tree_no text,
  subject1 text,
  subject2 text,
  ...
);

I need write a query for hierarchical sorting.

In the following sample, "tree_no" is drawn for expected sorting:
The same kind of sorting for all other columns also need to be sorted like parent child separate sorting. I've been trying with recursive.  Any other dynamic solution ?

Here I created SQL Fiddle link: http://sqlfiddle.com/#!12/b93f9/1

Thank you !

Efficiently storing, processing and reporting on hierarchical data of mixed/unknown depth in SQL is quite a complex area: whole books (such as Joe Celko's "Trees and Hierarchies in SQL for Smarties") have been written on the subject.

What you have there is often called a "naive tree" (only the parent relationship is noted) which does not allow for easily performing such a sort, or for queries where you want all data from under a particular node. It is common to de-normalise the structure in these cases by adding a path element to each row, so you can perform the sort you are looking for by sorting by theis field (and you can perform "everything under no matter how deep" queries by using filters such as tree_path LIKE '/path/to/target/node/%' (or tree_path LIKE '/path/to/target/node%' if you want to include the node itself). This avoids any need for recursion or an arbitrarily long collection joins, though of course you have the extra work to do maintaining this path when the tree changes. The book I usually recommend to all DB people (devs and admins alike), "SQL Antipatterns", has a chapter on this which covers the basics of this and other alternatives quite clearly (so you don't need to try consume Calko's tome unless you have some more complex requirements!).

If you can't alter the structure then you might be able to produce the path as an output using a recursive CTE (Common Table Expression) - I'll not go into detail here as there are already many good examples out there (in the documentation and StackExchange questions such as https://stackoverflow.com/questions/3307480/postgresql-recursive-with).

If your tree has a fixed depth (all leaf nodes are the same number of levels below root) then a fixed set of joins will do the trick:

SELECT {stuff}
FROM   source_table t1
JOIN   source_table t2 ON t2.id=t1.parent
JOIN   source_table t3 ON t3.id=t2.parent
JOIN   source_table t4 ON t4.id=t3.parent
ORDER BY t1.name, t2.name, t3.name, t4.name

for three levels below your root(s). If your depth is not fixed then this would not work (well, it could be forced to up to an arbitrary depth but the joins and the ordering clause would be pretty hairy to design and inefficient to run) to you need to use the CTE approach or use a modified data structure.


How to model a user/group relationship where users are assigned an unique number within the group? I am working on a web-server applications that deals with 2 entities:

users
groups

A user can belong to one, several or no group at all. Fairly usual, I assume.

However, I have additional constraints: each user should be able to receive a unique number (unique within the same group) in a "stable" and "deterministic" way, that is:

If a user is added, removed or updated, the existing users must keep their actual assigned numbers in every group.
The number should not be automatically assigned (not like an id. The number should be set explicitly by the web-server administrator when managing groups membership).

I haven't done a lot of Data Modelling and maybe this is dumb simple, by I can't decide which way to go and how to design my tables.

Do you guys have a suggestion ?

After your comments, I think you need a simple many-to-many table with an additional UNIQUE constraint:

TABLE UserGroups
  GroupID
  UserID
  UserNumber
  PRIMARY KEY (GroupID, UserID)
  UNIQUE (GroupID, UserNumber)
  FOREIGN KEY (GroupID)
    REFERENCES Groups (GroupID)
  FOREIGN KEY (UserID)
    REFERENCES Users (UserID)


Clustered Index / Index Oriented Table performance in join, worse than nonclustered? Can a clustered index (or IOT in Oracle) be detrimental, when to be used on a very "broad" table, but only few columns are used? In this case, the "Product" table is used only like a junction table between "ProductCategory" and "Sales".

If there was a nonclustered index on pr.ID and pr.CategoryID, the DBMS would do an Index-only-check, which has a very good performance. But, if I am right, a clustered index actually IS the entire table, ordered by the index columns. So, even if the clustered index had pr.ID and pr.CategoryID as it's index columns, the database would still have to load the entire table with all the heavy nvarchar(4000/max) stuff, only for two small columns.

-- get total sales amounts for product categories
SELECT pc.ID, pc.Name, SUM(sl.Amount) AS TotalSalesAmount
FROM ProductCategory pc
INNER JOIN Product pr ON pc.ID=pr.CategoryID
INNER JOIN Sales sl ON pr.ID=sl.SoldProductID
GROUP BY pc.ID, pc.Name

with Product being a heavy table like this:

CREATE TABLE Product
(
    ID int not null PRIMARY KEY, -- clustered index 1st column
    CategoryID int FOREIGN KEY REFERENCES ProductCategory(ID), -- clustered idx 2nd.
    Name                 nvarchar(200)
    RecommendedPrice     decimal(11,2),
    Creator              nvarchar(200),
    SafetyReport         nvarchar(4000),
    FutureDevelopmentsProposal nvarchar(4000),
    ExpectedSalesSurvey  nvarchar(4000),
    CrystalBallVision    nvarchar(4000), -- nonsense to represent a bloated table
    TarotCardsResult     nvarchar(4000),
    Horoscope            nvarchar(4000),
    FortuneTellerReport  nvarchar(max)
)

One remarkable thing I found out with a similar query on SQL Server 2008 R2:
The query plan contained an index scan on a completely unrelated, nonclustered index, like one on the pr.RecommendedPrice column only.

My idea is that the unrelated, nonclustered index contains references to the clustered index rows (pr.ID, pr.CategoryID), and it's cheaper to get these from a nonclustered index scan, rather than from the actual clustered index.

Am I right in my assumptions?

You are correct Erik.
In case a large portion of the leaf level of the clustered index needs to be read, the size of the other columns affects the amount of data that needs to be read, since the leaf level of a clustered index contains the table pages.

Nonclustered indexes contain the clustered index values for the ability to perform a lookup when a column that is not in the nonclustered index needs to be fetched. The optimizer can leverage this in order to fetch the clustered index values from there when it decided it's cheaper to do that.


Is it possible to copy a dev. database schema over a prod. one, but keep the prod. database' data? I'm using MySQL 5.6.  I have a production database and a development database.

As I'm developing the application, I sometimes need to add new columns/tables to the database.  Is it possible, when I'm ready to update the production database, to take the dev. schema and "lay it over" the production schema, but let the production server keep its data?

Currently, I'm just using mysqldump and dumping out the whole database, and then copying it over the top of the other.  It's working at the moment because the app isn't entirely in production mode, yet, but it's not gonna work forever.

To do this in any automated manner, you need to change your development model. Instead of just keeping the dev database as the current state and using a dump of its structure to build new databases, you should instead create scripts that transform one database version to the next - adding/dropping/altering objects as needed, and transforming data as required by those changes (new columns may need initial values, moving data to a new structure before dropping and old one, etc.). These scripts should be kept in good source control of course.

This way you can take your production database (at, say, version 2.5) and to get it up to the current development release (say, 2.8) apply the upgrade scripts for 2.5->2.6, 2.6->2.7, 2.7->2.8. Of course you can test this process in a test environment first as it should be repeatable (reset test environment to match current production, upgrade, test the application, fix upgrade scripts if something fails, repeat until everything is OK).

At some point this process will become inefficient for creating brand new databases for other instances of the application (i.e. building from the very first version onwards may become unwieldy after time) so you'll want to refactor the process slightly, picking a new starting point and building from there each time.

There are tools to help with the more manual process that you describe, but you will be much better off in the long run not arranging your release process around less automatable actions for managing schema changes. Setting up the process may seem like hassle, but it is a lot easier to do at this stage than after you have real production environments to worry about.


Can someone help me with this MySQL code? I want to retrieve some date from 2 different tables action and staff.

This is my code:

SELECT ActionID, FeedbackID, StaffID, ActionDate, ActionDetails,
       (SELECT StaffName, Department
        FROM staff
        WHERE staff.StaffID=action.StaffID)
FROM action;

I am getting this error:

  #1241 - Operand should contain 1 column(s)

Seems like you want to inner join the two tables on their Primary Keys.  Does this run for you:

CODE:

SELECT
    A.ActionID, A.FeedbackID, A.StaffID, A.ActionDate, A.ActionDetails,
    S.StaffName, S.Department

FROM Action AS A
    INNER JOIN Staff AS S ON Action.ActionID = Staff.ActionID


Jailer exclude tables to be exported As seen in this answer I can use jailer in order to export a subset of data into an SQL script.

But in my case I also want to exclude some tables completely from being extracted at all especially on tables relating on the table that I want to extract.

So how I can exclude the unwanted tables?

Disable all associations to these unwanted tables.
(Alternatively, but not recommended: remove the unwanted tables from the data model)


A way to merge multiple queues Consider the following table:
CREATE TABLE `multiqueue` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `CustomerID` BIGINT(20) NOT NULL,
    `Volume` INT(11) NOT NULL,
    `Content` MEDIUMTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `PublishedTS` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

This table serves as a multi-queue, meaning that it aggregates the queues of requests coming from multiple customers (denoted by CustomerID), each request having a certain Volume of work.
How to write a query that will select top N rows from the table, interleaving the rows from different customers?
If customer 1 sends 100 requests, each of volume 1000, then customer 2 sends 20 requests, each of volume 300, I would like that the query doesn't force any of the customers to starve on responses while my program is busy handling the requests of another customer. It should take 1 request of customer 1 and 3-4 requests of customer 2 in the first fetch, process them, then take 1 more request of customer 1 and 3-4 requests of customer 2, and so on.
What I have tried so far:
SET @runtot := 0;
SELECT q1.id1, q1.customerId1, q1.volume1, q1.content1, (@runtot := @runtot + q1.volume1) AS rt
FROM (
  SELECT ID AS id1, CustomerID AS customerId1, Content AS content1
  FROM multiqueue
  ORDER BY id1
) AS q1
WHERE @runtot < 2000

As described here, the code above limits the number of items selected with a running total of some field in the rows selected. In the scenario above, customer 2 would starve when the above query is in use.
The database in use is MariaDB (version 10.4.13, but I can upgrade to the latest if needed), though a solution for MySQL should also work.

For recent versions, you can use window functions:
SELECT ID, CustomerID, Volume, Content, runtot
FROM (
    SELECT ID
         , CustomerID
         , Content
         , sum(Volume) over (
                partition by CustomerID
                order by ID
           ) as runtot
    FROM multiqueue
) AS q1
WHERE runtot < 2000;

EDIT:
If you want at least 1 row for each customer, you can add another window function ROW_NUMBER() that enumerates the result, and use that in your select:
SELECT ID, CustomerID, Volume, Content, runtot
FROM (
    SELECT ID
         , CustomerID
         , Content
         , sum(Volume) over (
                partition by CustomerID
                order by ID
           ) as runtot
         , row_number() over (
                partition by CustomerID
                order by ID
           ) as rn

    FROM multiqueue
) AS q1
WHERE runtot < 2000
   OR rn = 1;


What is the purpose of the database 'owner'? Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query notifications were failing.

Supposedly the best practice for dealing with this is to make 'sa' the database owner. We changed it and that cleared out the queue.

My (very elementary) question: what is the database owner and what is its purpose?

There is some confusion out there between the database concepts of the 'dbo' (a user) and 'db_owner' (a fixed role) on one side and the instance concept of 'database owner' on the other side. The 'dbo' and 'db_owner' are often called 'database owner'. In what you're asking you are talking about the database owner as the server principal that owns the database.

The theory goes like this: anything that can be granted permissions on is a 'securable'. All securables have an owner. The owner of a securable has absolute control over the securable and cannot be denied any priviledge. Instance level securables are owned by server principals (logins). Database level securables are owned by database principals (users). Principal come in two flavor: primary (identity) and secondary (membership). Server level securables are by default owned by the currently logged primary server principal. Database level securables are owned by default by the current database principal, except for schema bound objects that by default are owned by the schema owner. All securables support the AUTHORIZATION clause at create time to enforce a different owner. ALTER AUTHORIZATION can be later used to change the owner of any securable.

Since the database is a server level securable it follows that it will be, by default, owned by the primary principal that issued the CREATE DATABASE statement. Ie. the NT login of the departed employee.

So your question is really "Why do securables need an owner?". Because the owner is the root of trust. It is the owner that grants, denies and revokes permission on the object. Can a security system be designed without owners of securables? Probably yes, but there would have to be some mechanism in place to replace the role owners play in the current model. For instance consider that dad securables have no owner (eg. instead of owning a securable, the original creator is just granted CONTROL over it) it would be possible create a securable and revoke access on it to everybody, including himself. The requirement of an owner circumvents this problem since an owner cannot lock himself out.

The little known side effect of CREATE DATABASE of creating a securable (the database) owned by original NT login has burned many before. The rules are the same for every securable, but some factors aggravate the DATABASE owner issues:

the other server level securables (endpoint, server role, login) are far seldom used, moved around etc.
database level securables usually end up by being owned by dbo (the database principal), or some other database principal, and thus the owner is contained with the database
Having the database ownership default to the NT primary principal creates a containment issue (the owner is an NT SID managed by AD and does not travel with the database files, the NT account can be thumbstoned etc etc etc)
the most important thing: the database owner has important side effects, specifically the EXECUTE AS context. This later problem is what burns most users. Since Service Broker makes extensive use of EXECUTE AS (the message delivery has an implicit EXECUTE AS context, as well as queue activation that has an explicit one) is usually  Service Broker users that discover this problem first.

BTW, Kudos for investigating and fixing your original problem :)


How can I implement full-text search on a single value in a text[] column? I am using Postgresql 9.3 on an Ubuntu 12.4 machine.

I've only recently started studying database and database design, and I am to create a database that includes text that will later be searched through. I've read up on tsvectors and stuff better than WHERE column LIKE 'query', but I don't have much experience with it. I am thinking of building a table where one column is of type text[], and another one will be the tsvector build from the text. I will use and array of text because some of the text will be displayed to the user with different formatting etc. I have never worked with array columns before. My questions are:

Is using a text array a good idea at all in this case?
Will the text array work well with the tsvectors?
Will using text[] compromise my performance?
Are there any pitfalls I should stay alert for?

Thanks for the attention, and this is my first post ever here, so if I messed up on this question do tell me. P.S.: I've searched this topic here beforehand, I hope I am not creating a redundant question. P.P.S.: Not a native speaker, sorry for the weird language.

EDIT: Thanks for the answers! The texts in the array won't be of fixed size. They will range from 6 ~ 20 lines, but they need to be editable, and some of the texts inside an array will be formatted in a different manner when displayed to the user. I'm not using multiple text columns because some of the entries in the table will have a single, two, or more elements inside the array. I know how each element will be formatted because every row will also have a "type" column, which will define it's formatting.

There are a lot of problems with using text[] like this, but fundamentally I agree @jjanes when he says,

  How will you know which member of the array gets what kind of formatting? If that logic is hard-coded into the position of the member in the array, then why not just have multiple columns and key the formatting to the column names?

You can keep this more direct and simpler by following a golden rule,

If the elements in the array can not be shuffled, then the order matters and the elements are ordinal
If the order matters, you should not be using an ARRAY type, regardless of Full Text Search.

Moreover, the to_tsvector call is a pretty bad idea, this design is problematic too. ARRAY is not for schema-less design, and the position in the array should not itself be information need to render the data in the app.

As a side note, in a normalized display you can do weighing/ranking with FTS. In order to that with your schema, you'd have to move from the schemaless abuse of text[] to an actual schema'd design anyway.

Abomination

If you decide to go down this route, this should be substantially faster and cleaner. But, this is a horrible idea.

CREATE AGGREGATE tsvector_agg (tsvector) (
  SFUNC = tsvector_concat,
  STYPE = tsvector
);

CREATE OR REPLACE FUNCTION text_array_to_tsvector( mytext text[], out tsv tsvector )
AS $func$
  BEGIN
    SELECT INTO tsv
      tsvector_agg(to_tsvector(t))
    FROM unnest(mytext) AS t;
    RETURN;
  END;
$func$ LANGUAGE plpgsql
IMMUTABLE;

Compare the two,

EXPLAIN ANALYZE SELECT to_tsvector(array_to_string(ARRAY['foo', 'bar', 'baz'],' '::text));
EXPLAIN ANALYZE SELECT text_array_to_tsvector(ARRAY['foo', 'bar', 'baz']);

See also

How can I implement full-text search on a value in a JSONB column?


Index / Check DB Script question I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.

Logging info to different tables is not possible without rewriting parts of Ola's solution and creating new objects.

By default, you can log the output to the dbo.CommandLog table by using @LogToTable = 'Y'

You can then query the recent outputs for the integrity checks with this command:

SELECT TOP (100) [ID]
      ,[DatabaseName]
      ,[SchemaName]
      ,[ObjectName]
      ,[ObjectType]
      ,[IndexName]
      ,[IndexType]
      ,[StatisticsName]
      ,[PartitionNumber]
      ,[ExtendedInfo]
      ,[Command]
      ,[CommandType]
      ,[StartTime]
      ,[EndTime]
      ,[ErrorNumber]
      ,[ErrorMessage]
  FROM [MNGDB].[dbo].[CommandLog]
  WHERE CommandType = 'DBCC_CHECKDB'
  ORDER BY EndTime desc;

You can then change the filter to check the index rebuilds / reorgs by changing the filter to WHERE CommandType = 'ALTER_INDEX'.

If you really want to change the tables, you could create a copy of the dbo.CommandLog table and create dbo.CommandLogIndex for example.

CREATE TABLE [dbo].[CommandLogIndex](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseName] [sysname] NULL,
    [SchemaName] [sysname] NULL,
    [ObjectName] [sysname] NULL,
    [ObjectType] [char](2) NULL,
    [IndexName] [sysname] NULL,
    [IndexType] [tinyint] NULL,
    [StatisticsName] [sysname] NULL,
    [PartitionNumber] [int] NULL,
    [ExtendedInfo] [xml] NULL,
    [Command] [nvarchar](max) NOT NULL,
    [CommandType] [nvarchar](60) NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NULL,
    [ErrorNumber] [int] NULL,
    [ErrorMessage] [nvarchar](max) NULL,
 CONSTRAINT [PK_CommandLog2] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Afterwards you have to create a new procedure, a copy of the dbo.CommandExecute procedure to use the dbo.CommandLogIndex table. I use Replace All (ALT + A) in SSMS to change the procedure calls.

As a last step, you could create a copy of the procedure dbo.IndexOptimize with the name  [dbo].[IndexOptimizeLogToTable] and change the dbo.CommandExecute procedure calls to dbo.CommandExecuteIndex.

Afterwards you run the procedure with @logtotable='Y'

  EXEC [dbo].[IndexOptimizeLogToTable] @databases = 'Test',@logtotable='Y'

And you should see the results in the new dbo.CommandLogIndex table.


Database Configuration In DB2 and Oracle What is the equivalent in Oracle for these Db2 configuration parameters?

Interval for checking deadlock (ms)         (DLCHKTIME)
Lock timeout (sec)                        (LOCKTIMEOUT)

In general, there are no equivalent settings.  Checking for deadlocks is something that happens automatically, there is no setting that configures when that checking is done.  Oracle generally has no system-level timeout for locks.  You can set DISTRIBUTED_LOCK_TIMEOUT but that only affects how long a distributed transaction would wait for a lock to be acquired, it has no impact on non-distributed transactions.  Beyond that, a session will wait for a lock indefinitely unless the session requesting the lock specifies a timeout (i.e. SELECT ... FOR UPDATE WAIT n).


Using to_base64 function and regexp_replace with backreferences in MariaDB I am using MariaDB 10.3. I want to replace a string using REGEX and encode part of the result into base64.

For example, I want to first invert the "a" and "b" strings in this example:

MariaDB [(none)]> SELECT regexp_replace('abc', '(a)(b)', '\\2\\1');
+-------------------------------------------+
| regexp_replace('abc', '(a)(b)', '\\2\\1') |
+-------------------------------------------+
| bac                                       |
+-------------------------------------------+
1 row in set (0.000 sec)

"abc" is now "bac, that's ok.

Now I want to base64-encode the result, so ba should become YmE=:

MariaDB [(none)]> SELECT to_base64('ba');
+-----------------+
| to_base64('ba') |
+-----------------+
| YmE=            |
+-----------------+
1 row in set (0.000 sec)

But when I apply the to_base64 function within regexp_replace, it no longer interprets my backreferences and encodes \2\1 instead of the the ba string:

MariaDB [(none)]> SELECT regexp_replace('abc', '(a)(b)', to_base64('\\2\\1'));
+------------------------------------------------------+
| regexp_replace('abc', '(a)(b)', to_base64('\\2\\1')) |
+------------------------------------------------------+
| XDJcMQ==c                                            |
+------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> select from_base64('XDJcMQ==');
+-------------------------+
| from_base64('XDJcMQ==') |
+-------------------------+
| \2\1                    |
+-------------------------+
1 row in set (0.000 sec)

How can we make the backreferences be interpreted BEFORE applying the to_base64 function?

The algorythm as I see it.

We don't know how many replaces we need. So we must use recursive CTE or iterative/recursive code.

In it we must:

Find first pattern occurency offset in source data starting from some offset (initially 1)
Split source by head, pattern and tail
Perform pattern replace and encode
Concat head and encoded replace, calculate its length (it will be used as search starting offset on the next iteration)
Concat tail
Iterate next if current starting offset (last "first pattern occurency offset" - see step 1) > 0

In main query we select only those records where "first pattern occurency offset" = 0.

This algo can be realized:

using local variables @variable performing all chained calculations in excess fields which will be skipped in main query
in user-defined function which processes one literal value
in stored procedure

The process can be simplified if there is a guarantee that only one pattern match exists - but in that case the result for values which contains more occurences (randomly) will be unpredictable.