Multiple differences of opinion about transaction log autogrowth settings We have a two-node SQL Server 2017 AG. There's a job that looks at autogrowth settings for our databases and makes sure they're not set to use a percentage.  For the past day, its been continually saying it's updating the settings for a particular database.  This hasn't happened before.  Upon investigation, I found sys.master_files on node 01 says the transaction log file for the database will grow in increments of 512MB, but node 02's sys.master_files says it will grow in increments of 10%  Server 02 is the primary.  On both replicas, is_percent_growth is cleared in sys.database_files for this database.  Looking at file properties in SSMS, both replicas show 512MB autogrowth, most likely because SSMS looks in sys.database_files (a guess on my part).
There haven't been any failovers on this AG.
Questions

How could there a difference of opinion between sys.master_files and sys.database_files on node 02?
How did the autogrowth settings for the database get out of sync between the two replicas?
How can I clear the is_percent_growth flag on node 02 so I don't have any more false positives?  Ideally, I'd like to do this without taking the AG offline.

I've read https://stackoverflow.com/questions/4174520/sql-server-sys-master-files-vs-sys-database-files which explains the difference between sys.master_files and sys.database_files, but it doesn't explain the circumstances here.

Here's a guess:
When you seed (auto or through a backup) the database, the info from sys.database_files is copied into sys.master_files on the other end (which lives in a table in the master database).
My guess is that a later change for the file properties, which is stored in (what you see though) sys.database_files is not reflected in sys.master_files. Perhaps some changes triggers the state to be copied into sys.master_files and some change don't? And perhaps this has been improved in later versions?
Sorry to be so vague, I don't have the environment here to test this. I would do some changes to the ldf file and see if any of those changes causes sys.master_files at the other end to be updated.
Increase the file size. Shrink it (just 1 MB). Change max size. Etc. One at a time, of course.


Single entry point for mysql cluster I have a mysql cluster setup and working. When connecting to it I specify one of the mysql nodes (mysqld) in my connection string. However, if this node goes down it doesn't automatically fail over to the other mysqld node (obviously). When someone creats an app to connect to the cluster do they simply specify each mysqld node in their connection string? In a MSSQL cluster you have a virtual IP you connect to so that instead of specifying each SQL node.

It's dependent on the connector that you're using. For example, if using Connector/J then you can provide multiple server addresses in your connect-string... http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

Andrew.


need suggestions to improve view performance I am looking to improve the performance of a view in SQL Server 2008.  This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these attributes of a person.

It's a really complicated, long running view.  We have over 19 million people and there is a lot of logic that goes into each column.  For example, there is an indicator as to whether or not a person is deceased and that relies on three CTEs (common table expressions) and a case statement.

Basically, it's a nightmare.

I need to figure out a way to improve the performance.  Changing it to a table is not possible - data must be up-to-the-second accurate.  Changing it to an indexed view is right out - it uses data from multiple databases.  I can't really modify the column structure as it would break a number of existing reports.

Are there any tools in the toolbox that might help?  I'm wondering if stored procedures or functions might help.  Maybe a table with computed columns?  I would be able to pull the person's identifying information on a nightly basis and store that into a table, but the vast majority of columns rely on live data.

Views are typically not implemented for performance. And while you currently cannot implement explicit indexed views (which are just views that SQL Server maintains for you), you can certainly maintain facts manually yourself.

For example, you mention that you currently calculate "whether someone is dead or not" using three CTEs and a CASE expression (sorry, to be pedantic, it's not a statement).

Instead of referencing this set of CTEs every time the view is accessed, why not put that fact in a table (potentially along with other facts that have to be calculated per user), and calculate that periodically in the background? So maybe every 5 minutes (that is just a SWAG, you'll have to determine what's appropriate), you run a SQL Server Agent job that re-populates the table based on what it currently knows is the truth. Now the view just has to reference the table that is the output of that script, instead of calculating it over and over again while the users wait. So for example:

CREATE TABLE dbo.PersonProperties
(
  PersonID INT PRIMARY KEY REFERENCES dbo.Persons(PersonID),
  IsDead BIT NOT NULL DEFAULT 0
);

Now the job can simply merge that table with the results of the CTE, and then the view can include a reference to that table which simply pulls the BIT column along with a join on the PK. This should be MUCH less expensive at query time that re-evaluating all of that logic every time.

To minimize blocking (e.g. when users are accessing the view at the same time the job is running), you can implement what I call "schema switch-a-roo" and which I blogged about here:

http://www.sqlperformance.com/2012/08/t-sql-queries/t-sql-tuesday-schema-switch-a-roo

So instead of locking resources on the expensive query throughout the operation, the only blocking that happens is when the metadata switch actually takes place.

This works as long as you can afford some brief periods where the data is not accurate. You can tighten up that window to be pretty narrow, but there is always a chance that a person will die in between and for a brief moment a query will return that they are still alive. If you can't afford this, then you make it a part of the process that first introduces that fact to the database to make sure the CTE reflects that immediately and the new table also reflects it immediately.

Still not good enough? The flag a user as "dirty" the second a change for them comes in. The view can union or left join with the stale data for users that are "clean" and go after live data only for the users that are "dirty."


Missing diagram option SSMS I would like to create a relationship between to tables. Normally and in previous versions of sql server and the management studio I would go to database diagrams and draw the relation in the diagram.

I have an 2008 sql server and the ssms 2016 ctp2 client. Am I missing something? tested it too by connecting to an other sql server with other credentials etc.

it is there when I use remote desktop to work on the database server... and connect with a windows account. what am i doing wrong?

He lost me at the 48th second: https://youtu.be/PHjwX-cexIA?t=48s

this seems related to https://stackoverflow.com/a/21324448/169714
but what caused the diagram node to disappear when logged in remotely?

Are you a member of db_owner?

See this:

  To use Database Diagram Designer, it must first be set up by a member of the db_owner role to control access to diagrams (https://msdn.microsoft.com/en-us/library/ms189279%28v=SQL.100%29.aspx)


Is it possible to view LRU-K values in SQL Server? In SQL Server's sys.dm_os_memory_cache_entries, it is possible to view both the original cost of an entry in the cache as well as the current cost of the cache entry (original_cost and current_cost respectively). The DMV sys.dm_os_buffer_descriptors contains a record of the pages that are currently in memory as well as some metadata about the pages. One interesting chunk of info not available in the DVM are the LRU-K values for the data pages.

Is it possible to get the LRU-K values for data pages in buffer pool in SQL Server? If so, how?

There is in fact no useful way to do this as far as I can see.

The other answer mentions DBCC PAGE and leaves it up to the reader to figure out the details. From experimentation I assume they mean bUse1.

This fails to take account that DBCC PAGE is itself a use of the page and the value gets updated before it is shown to us.

A script demonstrating this is below (takes 12 seconds to run).

USE tempdb;

CREATE TABLE T(X INT);

INSERT INTO T VALUES(1);

DECLARE @DBCCPAGE NVARCHAR(100);

SELECT @DBCCPAGE = 'DBCC PAGE(0,' + CAST(file_id AS VARCHAR) + ',' + CAST(page_id AS VARCHAR) + ',0) WITH TABLERESULTS;'
FROM   T CROSS APPLY  sys.fn_PhysLocCracker (%%physloc%%)

DECLARE @DbccResults TABLE
(
      ID INT IDENTITY,
      ParentObject VARCHAR(1000)NULL,
      Object VARCHAR(4000)NULL,
      Field VARCHAR(1000)NULL,
      ObjectValue VARCHAR(MAX)NULL
)
INSERT INTO @DbccResults EXEC(@DBCCPAGE)
WAITFOR DELAY '00:00:07'
INSERT INTO @DbccResults EXEC(@DBCCPAGE)
WAITFOR DELAY '00:00:05'
INSERT INTO @DbccResults EXEC(@DBCCPAGE)

SELECT *
FROM @DbccResults
WHERE Field = 'bUse1'
ORDER BY ID

EXEC(@DBCCPAGE)

DROP TABLE T

Typical results are

+----+--------------+-------------------------+-------+-------------+
| ID | ParentObject |         Object          | Field | ObjectValue |
+----+--------------+-------------------------+-------+-------------+
|  8 | BUFFER:      | BUF @0x00000002FE1F1440 | bUse1 |       54938 |
| 49 | BUFFER:      | BUF @0x00000002FE1F1440 | bUse1 |       54945 |
| 90 | BUFFER:      | BUF @0x00000002FE1F1440 | bUse1 |       54950 |
+----+--------------+-------------------------+-------+-------------+

With the second result being

+---------+-------------------------+--------------+--------------------+
| BUFFER: | BUF @0x00000002FE1F1440 | bpage        | 0x00000002F4968000 |
| BUFFER: | BUF @0x00000002FE1F1440 | bhash        | 0x0000000000000000 |
| BUFFER: | BUF @0x00000002FE1F1440 | bpageno      | (1:120)            |
| BUFFER: | BUF @0x00000002FE1F1440 | bdbid        | 8                  |
| BUFFER: | BUF @0x00000002FE1F1440 | breferences  | 0                  |
| BUFFER: | BUF @0x00000002FE1F1440 | bcputicks    | 0                  |
| BUFFER: | BUF @0x00000002FE1F1440 | bsampleCount | 0                  |
| BUFFER: | BUF @0x00000002FE1F1440 | bUse1        | 54950              |
| BUFFER: | BUF @0x00000002FE1F1440 | bstat        | 0x9                |
| BUFFER: | BUF @0x00000002FE1F1440 | blog         | 0x1c9a             |
| BUFFER: | BUF @0x00000002FE1F1440 | bnext        | 0x0000000000000000 |
+---------+-------------------------+--------------+--------------------+

The output after the 7 second delay is incremented by 7 and after the 5 second delay by 5.

So it seems clear that these LRU values are seconds since some epoch. Restarting the SQL Server service does not alter the epoch but restarting the machine does.

The value rolls over every 65,536 seconds so I presume that it just uses something like system_up_time mod 65536

This does leave one unanswered questions in my mind (any takers?). SQL Server uses LRU-K with K=2 according to the internals book. Shouldn't there be a bUse2? If so where is that?

There is one way of observing the bUse1 value without changing it that I know of though and that is demonstrated by Bob Ward here.

Attach a debugger to the SQL Server process and display referenced memory for the memory address of the buffer structure (shown to be 0x00000002FE1F1440 above).

I did this immediately after running the script above and saw the following.

(From previous experimentation I'd found the highlighted bytes were the only ones that changed between runs so these are definitely the right ones).

One surprising aspect is that SELECT CAST(0xc896 as int) = 51350.

This is exactly 3600 (one hour) less than reported by DBCC PAGE.

I believe this to be some attempt to disfavour pages being kept in cache by calling DBCC PAGE itself. For a "normal" page select this one hour adjustment does not occur. After running

SELECT *
FROM T

SELECT ((ms_ticks) % 65536000) / 1000 AS [Roughly Expected Value]
FROM sys.dm_os_sys_info

The value shown in memory is as expected.

The DBCC command actually updates that value twice. Once at

sqlmin.dll!BPool::Touch()  + 0x3bfe bytes
sqlmin.dll!BPool::Get()  + 0x12e bytes
sqlmin.dll!LatchedBuf::ReadLatch()  + 0x14f bytes
sqlmin.dll!UtilDbccDumpPage()  + 0x364 bytes
sqlmin.dll!DbccPage()  + 0xfa bytes
sqllang.dll!DbccCommand::Execute()  + 0x153 bytes

With the higher value then again at

sqlmin.dll!LatchedBuf::FreeAndUnlatch()  + 0x71 bytes
sqlmin.dll!UtilDbccDumpPage()  + 0x545 bytes
sqlmin.dll!DbccPage()  + 0xfa bytes
sqllang.dll!DbccCommand::Execute()  + 0x153 bytes

With the lower one.

I'm not aware of any way to get buffer addresses for pages without using DBCC BUFFER/ DBCC PAGE any way though and using both of these changes the value we are trying to inspect!


mongodb Query per sec troubleshooting I manage a MongoDB replica set for one of my customer and sometimes he send me a ticket (email) with only one argument :

~ WARNING Date/Time: ~Tue Jul 4 02:39:33 GMT 2017
Summary: ~WARNING - Queries / Sec: 9.674419

How understand this kind of message? I could guess that we have some slow query running. So the first thing I do it's to check the log. What i found in it's that we have some aggregate command that take 500-600ms to execute. Here an example :

2017-07-04T04:08:52.205Z I COMMAND  [conn2104435]
command databaseName.cards_PWCD900004
command: aggregate { aggregate: "cards_PWCD900004",
pipeline: [ { $unwind: "$virtualCards" },
{ $project: { cardProfileId: 1, virtualCards.state: 1,
virtualCards.device: 1,
virtualCards.primaryDpanId: { $ifNull: [ "$virtualCards.primaryDpanId", null ] } } },
{ $group: { _id: { cardProductId: "$cardProfileId",
virtualCardsState: "$virtualCards.state",
deviceId: "$virtualCards.device" }, tokensNumber: { $sum: 1 },
virtualCardsNumber: { $sum: { $cond: { if: { $eq: [ "$virtualCards.primaryDpanId", null ] },
then: 1, else: 0 } } } } }, { $group: { _id: { issuerName: "APLUS",
cardProductId: "$_id.cardProductId",
virtualCardsState: "$_id.virtualCardsState" },
devicesNumber: { $sum: 1 }, virtualCardsNumber: { $sum: "$virtualCardsNumber" },
tokensNumber: { $sum: "$tokensNumber" } } } ] }
keyUpdates:0 writeConflicts:0 numYields:219 reslen:30763
locks:{ Global: { acquireCount: { r: 460 } },
Database: { acquireCount: { r: 230 } },
Collection: { acquireCount: { r: 230 } } } protocol:op_query 523ms

What I would like to know is how troubleshoot such thing? I could launch a mongotop or mongostat but if the issue just finished I will see nothing. I also could use db.serverStatus() but I don't really know which variables is the most significant.

database size : 2GB
SSL : true

About mongo.conf all it's by default. Here more information about hardware:

RAM : 8GB
2 proc : 2,3 GHz each

What are best practices an administrator should have to check if all works on his database ?

If you have any question don't hesitate please.

Lets first look at few of the output terms to understand better.

numYields

The number of times the operation yielded to allow other operations to complete. Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory. This allows other operations that have data in memory to complete while MongoDB reads in data for the yielding operation.
Does a read or write operation ever yield the lock?

reslen

The length in bytes of the operation’s result document. A large responseLength can affect performance. To limit the size of the result document for a query operation, you can use any of the following:

Projections
The limit() method
The batchSize() method

acquireCount

Number of times the operation acquired the lock in the specified mode.

How do you troubleshoot this thing?

I am taking a guess here as I cannot see your query plan. I suggest using explain method and looking at the plan. Especially focusing on index usage, scan/seek.

See this answer about high number of yields.

Monitor your instance for some key matrix which will help you tune your long running queries.  In your case I would especially focus on index misses.

mongotop - shows how much time was spend reading or writing each
collection over the last second
mongostat - brilliant live debug tool, gives a view on all your
connected MongoDB instances


Remove result set doesnt meet criteria This is part of a query result :

I'm going to add a constraint to my query to remove the orders that don't have an invoice_type = final, so the first three rows should be removed and the last three rows should remain in the result.

How should I do that?

I think this query will work for you :

select
  order_item_id,
  order_id,
  invoice_id,
  invoice_type
from your_table
where order_item_id in
(select order_item_id from your_table where invoice_type = 'final' group by order_item_id)


Granting permissions to a DB user - SQL Server I need to grant select and update permissions to a DB user (TestUser) on TestDB database as per below.

When I press ok and re-check the permissions, they are not visible as can be shown below:

From my testing, only permissions at object level (e.g. columns, etc) are displayed here.

Can select permission on the database be visible from somewhere apart by executing T-SQL code? Is this a bug or by design? The same applies to user defined DB role permissions and it is very misleading.

Reason you are not seeing the permission when you open the window by default it is scoped to Securable scope: Schema' and notdatabase`.

More on secureables.

  Can select permission on the database be visible from somewhere apart
  by executing T-SQL code?

Yes it is possible. From your 2nd screen:

Click Search...
Select radio button All objects of the types....
Click Ok
Select Databases

You should see TestDB in the list at top half of the window. In the bottom half you can see Explicit and Effective permission.

In case you want to use tsql:

EXECUTE AS USER = 'testuser';
USE [DbaDatabase];
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;
GO


Run an exe file that exists on a different server from a SQL Server Job We have an .exe file on a server, say Server1 which should be run from a SQL Server job that exists on a different server, e.g.: Server2. How can it be done?

I know if it's a local file, I can use xp_cmdshell in the Job step and run the .exe file, but in our case, this file exists on a different server. If it's doable, what security permissions should be set up to achieve it?

You could still use xp_cmdshell; however, you'll need to make sure that your SQL Server Service account (the AD account used to run SQL Server) has access to the directory containing the exe file and any other directories that that program may require.

xp_cmdshell runs on your SQL Server itself; you can use it just like any other command window.

If, on the other hand, you need the exe to run on the machine that hosts it, then you'll need to look at other options.


SLEEP_TASK Wait Type in SQL Server - What does it indicate? I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them.

I'm not the official DBA, just a SQL Server developer who knows some DBA stuff.  We upgraded our servers last weekend to 10.52.2500.0 - R2SP1 I think.

All the info I could find online indicates that SLEEP_TASK means the server is waiting on some sort of internal process to  complete.  I don't have any blocking or any background processes like checkpoints or ghost cleanup running, so I'm a bit stumped.

Has anyone seen this wait type before, and if so can you give me an idea of what is causing it?

SLEEP_TASK is a general wait, used for all sorts of things where a more specific wait type doesn't exist, but it does crop up with hashing operations (join/aggregate) that spill to tempdb, and occasionally at an exchange (Parallelism operator) where a consumer is waiting on something that isn't a normal CXPACKET or EXECSYNC wait. I would check for Hash spills first.


varchar performance impact This question has been asked before ( Speed impact of using varchar in SQL Server 2008 ), but the answers are not detailed enough to my satisfaction.  Admitedly this is not a current issue and mainly for my education.

However, I will try and make this a concrete example from some current DB schema work I am doing;

Option A

CREATE TABLE [VMware].[TblHostSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    <snip/>
    [PowerStateID] [int] NOT NULL
)

ALTER TABLE [VMware].[TblHostSystem]  WITH
CHECK ADD  CONSTRAINT [FK_TblHostSystem_TblPowerState]
FOREIGN KEY([PowerStateID])
REFERENCES [VMware].[TblPowerState] ([ID])
GO

CREATE TABLE [VMware].[TblPowerState](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PowerState] [varchar](50) NOT NULL
)

Option B

CREATE TABLE [VMware].[TblHostSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    <snip/>
    [PowerState] [varchar](50) NOT NULL
)

If I've chosen option B (as the previous author of the schema has), as the power of a host is something that will frequently change, what sort of churn will this create?

Some statements that I believe are true,

Varchars are stored in the same row/page on MSSQL 2008.
Varchars are not padded?

So my question is, when updating PowerState in the second example from 'poweredOn' (20 bytes) to 'poweredOff' (22 bytes) what does the DB need to do?

Move the row to a new page because there is now room for the extra 2 bytes?
Move everything after the current row 2 bytes forward because there is a cluster index on ID and the order on disk has to be maintained?
None of the above, the database actually allocates 102 bytes (maximum possible size of varchar(50)) and has loads of free space most of the time to accomodate such changes without massive churn?
Breakout the varchar(50) column to another page to avoid the massive churn caused by 1, and 2, and it doesn't pad out as 3, but it means locking potentially multiple pages per row and two reads off the disk (maybe) rather than one.

Would appreciate a detailed of what happens under the covers!

First questions -

Yes they are stored on page normally (for non-(MAX) values)
They are not padded, but there is a 2 byte per row overhead to store the length of the field

1 - IT DEPENDS on if the page is full or not.  Very likely it will fit on the current page.  The header record for the row needs to updated as well to update the length of the field.

2 - Data will only be rearranged at the page level for an insert or update, so nothing else gets shifted.  If it fits on your current page it will, if not there will be a page split.

3 - Never as far as I know for varchar - if you want this behavior you can make it a CHAR instead.

4 - Only if the value exceeds the space on the page.

As to the design itself...

You will definitely get better performance by using the id instead of a varchar.

An int or smallint is orders of magnitude smaller than your strings.
Index lookups are a lot more efficient on integers than on strings
Data stored on the page will be consistent and much smaller
SQL likes keys!  A lot of optimizations can  take place when you use PK/FK relationships because the engine can infer some metadata about duplicates/distinct values, etc.


Two active + one passive node for failover clustering I'd like to know if it is possible to have a single passive node for two SQL Servers in a failover clustering deployment.
For example, both active servers A and B will use server C as their contingent server. If it is apt to do so, will server C run two instances of SQL Server; one for A and one for B?
As a side question; what is the benefit of "dynamic quorum" and "dynamic witness"?

I'd like to know if it is possible to have a single passive node for two SQL Servers in a failover clustering deployment.

Yep that is quite possible. You can have 3 node(let us use node instead of server) cluster with each SQL Server instance on one node A and Node B respectively  and 3rd node is their in case of failover.

You can also have two instances running on same node(suppose Node A) and other node(Node B) acting as passive node. But such configuration is not a best one which you should go with.

  If it is apt to do so, will server C run two instances of SQL Server; one for A and one for B?

In case of failover, and both nodes A and B down, yes node c will act as owner of both instance running on Node A and Node B.

  As a side question; what is the benefit of "dynamic quorum" and "dynamic witness"?

You must understand witness helps in forming quorum. I would suggest you read this blog Understanding Behavior of Dynamic Witness. If you then have a specific question feel free to ask.


How do I change the config path for mysqld? I want to change configuration file path from C:\ to E:\, but I can't find the way to do this.

Alternatively, is there a way to start a new instance with the config file I want? I've done something like mysqld --init-file="E:\db\my.ini" but it didn't work as well.

--EDITED--

I started new instance with the config files, and it worked well, and I faced another problem. Could you give me a help for this one as well?

https://dba.stackexchange.com/questions/100492/cannot-change-datadir-for-mysqld

You need to use the --defaults-file option as follows

mysqld --defaults-file="E:\\db\\my.ini"

or

mysqld --defaults-file="E:/db/my.ini"

If you are running MySQL 5.6 already on the Windows machine and you are trying to run an additional MySQL 5.6 instance, make sure you change the following under the [mysqld] group header in your my.ini

the port number (other than 3306)
datadir (such as E:\db)

perhaps like

[mysqld]
port = 3307
datadir = "E:/db"

Give it a Try !!!


How to grant permission for creating,reading and executing symmetric key in sql server 2008 We have user associated to a schema with limited privileges.During run time we are generating symmetric key using same user log-in.since the user has limited privilege, we are not able to create key now.

How to grant permission for creating,reading and executing symmetric key in sql server 2008 for this user ?

If you create keys without a certificate like:

CREATE SYMMETRIC KEY smTestKey
 WITH ALGORITHM=AES_256
    , IDENTITY_VALUE = 'Key to protect bla'
    , Key_SOURCE = N'Secret pass phrase'
  ENCRYPTION BY PASSWORD = 'secret password';

then the following is enough:

GRANT ALTER ANY SYMMETRIC KEY TO dbuser

If you create symmetric keys that's encrypted by a certificate (that is created by another db user) for example:

CREATE SYMMETRIC KEY smTestKeyCert
 WITH ALGORITHM = AES_256
  ENCRYPTION BY CERTIFICATE testCert;

then you also need VIEW DEFINITION permission on the certificate:

GRANT VIEW DEFINITION ON CERTIFICATE::testcert TO dbuser

However, if you want to open symmetric key by decrypting with the certificate the dbuser opening the key would need CONTROL permission on the certificate:

GRANT CONTROL ON CERTIFICATE::testcert TO dbuser

UPDATE
To summerize:

The user that is creating asymmetric keys needs ALTER ANY ASYMMETRIC KEY permission
The user that is creating symmetric keys needs ALTER ANY SYMMETRIC KEY permission
The user that is creating keyA and encrypting it with keyB needs VIEW DEFINITION permissions on keyB
The user that is OPENING keyA and decryptiong it with keyB needs CONTROL permission on KeyB

your scenario:

--UserA needs to create Asymmetric keys so needs ALTER ANY ASYMMETRIC KEY PERMISSION
GRANT ALTER ANY ASYMMETRIC KEY TO userA

--UserB needs to create Symmetric keys so needs ALTER ANY SYMMETRIC KEY PERMISSION
GRANT ALTER ANY SYMMETRIC KEY TO userB

--UserA creates Asymmetric keys that are used by userB to create Symmetric keys and later open them
--So userA must create the Asymmetric key and Also give CONTROL permission on the Asymmetric key to UserB

--Create Asymmetric key
CREATE ASYMMETRIC KEY asym_CommonKey
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'admin@123';

--Give control permission to UserB
GRANT CONTROL ON ASYMMETRIC KEY::asym_CommonKey to UserB

--UserB creates a symmetric key using the Asymmetric key from userA
--Note, at this stage VIEW DEFINITION permission on the Asymmetric key would have been sufficient
CREATE SYMMETRIC KEY sym_CommonKey
WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY asym_CommonKey

--UserB opens the Symmetric key, decrypting it with the Asymmetric Key
--Note, at this stage User B needs the CONTROL permission on the Asymmetric key.
OPEN SYMMETRIC KEY sym_CommonKey DECRYPTION BY ASYMMETRIC KEY asym_CommonKey with password ='admin@123'

Source:

MSDN

  Requires ALTER ANY SYMMETRIC KEY permission on the database. If
  AUTHORIZATION is specified, requires IMPERSONATE permission on the
  database user or ALTER permission on the application role. If
  encryption is by certificate or asymmetric key, requires VIEW
  DEFINITION permission on the certificate or asymmetric key. Only
  Windows logins, SQL Server logins, and application roles can own
  symmetric keys. Groups and roles cannot own symmetric keys.

MSDN 2

  The caller must have some permission on the key and must not have been
  denied VIEW DEFINITION permission on the key. Additional requirements
  vary, depending on the decryption mechanism:

DECRYPTION BY CERTIFICATE: CONTROL permission on the certificate and knowledge of the password that encrypts its private key.

DECRYPTION BY ASYMMETRIC KEY: CONTROL permission on the asymmetric key and knowledge of the password that encrypts its private key.

DECRYPTION BY PASSWORD: knowledge of one of the passwords that is used to encrypt the symmetric key.


Put backticks around partition like `partition`. It's a reserved word since version 5.6.2. See 9.3 Keywords and Reserved Words.


Is it appropriate to use a timestamp as a DISTKEY in Redshift? I'm having a little trouble understanding how to select a DISTKEY for a table I'm working on.

Consider the following table:

create table test_table (
    country     char(2)       encode zstd,
    record_time bigint        encode zstd not null,
    ip          bigint        encode zstd,
    identifier  varchar(41)   encode zstd not null,
    lat         numeric(10,3) encode zstd,
    long        numeric(10,3) encode zstd,
    PRIMARY KEY (event_time, hash)
)
DISTKEY(event_time)
SORTKEY(country, event_time, hash)

My understanding is that DISTKEYs are only of real importance if a table is to be joined with others.

This table will be the only one on its cluster, and thus, wont be joined with other tables. Since that's the case, am I right in assuming that a DISTKEY is unnecessary/redundant, or does a DISTKEY affect more than meets the eye?

It's pretty corner-case, but there are cases where data location matters slightly.

Let's say you ask the database to do this query:

SELECT COUNT(DISTINCT ip)
FROM test_table
GROUP BY country

If the table is distributed by country, no network activity is needed (I tested this to confirm). For any other distribution style, the hash table will logically need to be re-distributed over the network (I also tested this to confirm).

That said, you probably want to just choose an EVEN distribution style to maximize the scanning speed. For that matter, maybe you want to use Spectrum for this use case.


Database role to allow .NET program to retrieve MSDB table and view rows I'm currently writing a database management application that connects to all of my databases and returns metadata for each database. The user currently has db_datareader role for MSDB but the DataReader .NET object only contains schema and no rows after executing a query on sysjobs or sp_help_jobs stored procedure in the code or in SSMS. I've tried granting execute and select on the database and object levels to no avail. When I give SA to the user it is able to return data, but I do not want to give the user SA.

What is the specific permission needed to retrieve the data from system tables and maintain the highest level of security?

Try making your user account a member of the msdb database's SQLAgentReaderRole.

The rights granted by that role membership are described here:

https://msdn.microsoft.com/en-us/library/ms188283.aspx

Look at the description of rights under SQLAgentReaderRole Permissions.  You will see that it has rights to read the information on the jobs.  This does not require being a member of the db_datareader role.  It does allow the user to enumerate jobs, view properties, and view job history.

The user account can only make changes to a job if that account is the owner of the SQL Agent job.  Otherwise, it is read-only.


How to get my result from Procedure How can i get my out parameters from my procedure.
My  stl_mbc_cdr table has over 100 records and i cant see my results.

declare
   call_charge VARCHAR(10);
   MSG_OUT     VARCHAR(50);
BEGIN
   FOR REC IN (SELECT *FROM dulguunbaatar.stl_mbc_cdr_1062) LOOP
   pk_test_duba.recharge_idd(REC.Called_Number,
                               rec.call_date||rec.call_time,
                                                         rec.duration,
                                                         CALL_CHARGE,
                                                         MSG_OUT);
    END LOOP;
end;

Depends on the client you're using really, but this should work in SQL*Plus, SQL Developer etc.

set serveroutput on;

declare
   call_charge VARCHAR(10);
   MSG_OUT     VARCHAR(50);
BEGIN
   FOR REC IN (SELECT *FROM dulguunbaatar.stl_mbc_cdr_1062) LOOP
   pk_test_duba.recharge_idd(REC.Called_Number,
                               rec.call_date||rec.call_time,
                                                         rec.duration,
                                                         CALL_CHARGE,
                                                         MSG_OUT);
    DBMS_OUTPUT.PUT_LINE('charge: '||CALL_CHARGE||' message: '||MSG_OUT);
    END LOOP;
end;


Permission errors on insert after schema drop/create (Pg 9.0.23) We have a web application that allows users to modify data through an API. In order to allow people to run tests, we provide a sandbox area called 'daily'. This sandbox gets refreshed every day from the 'live' database.

This works by dropping/creating a 'daily' schema then copying over a subset of tables from the 'live' schema. This is applied by running a simple SQL script via crontab every day (see below).

Issue:

when the web application is started after the daily refresh, clients can insert data into the 'daily' schema as expected
when the web application is started before the daily refresh, we encounter permission errors when trying to insert data, eg

permission denied for relation annotation

Note: restarting the web application allows everything to work fine, but I would prefer to avoid the downtime.

Question:

is this behaviour expected (Pg 9.0.23)?
is there anything I can do to avoid this problem?

The script we are using to provide the daily refresh:

BEGIN;

drop schema if exists genome3d_data_daily CASCADE;

create schema genome3d_data_daily AUTHORIZATION genome3ddaily;

-- recreate sequence
create sequence genome3d_data_daily.annotation_id_seq;
select setval('genome3d_data_daily.annotation_id_seq', orig.last_value) from genome3d_data.annotation_id_seq orig;

-- create new tables (including constraints, indexes, etc)
create table genome3d_data_daily.annotation_type    (like genome3d_data.annotation_type including all);
create table genome3d_data_daily.annotation_segment (like genome3d_data.annotation_segment including all);
create table genome3d_data_daily.annotation         (like genome3d_data.annotation including all);

grant select on all tables    in schema genome3d_data_daily to PUBLIC;
grant select on all sequences in schema genome3d_data_daily to PUBLIC;

grant all privileges on all tables    in schema genome3d_data_daily to genome3ddaily;
grant all privileges on all sequences in schema genome3d_data_daily to genome3ddaily;

-- by default this will point to the original sequence
alter table genome3d_data_daily.annotation
alter column id set default nextval('genome3d_data_daily.annotation_id_seq');

-- insert data
insert into genome3d_data_daily.annotation_type     select * from genome3d_data.annotation_type;
insert into genome3d_data_daily.annotation_segment  select * from genome3d_data.annotation_segment;
insert into genome3d_data_daily.annotation          select * from genome3d_data.annotation;

END;

Figured out a workaround that involved just deleting tables (and sequences) rather than dropping the schema. Leaving here in case it helps anyone else.

Changed the first few lines:

BEGIN;
drop schema if exists genome3d_data_daily CASCADE;
create schema genome3d_data_daily AUTHORIZATION genome3ddaily;

...to...

BEGIN;
create schema genome3d_data_daily AUTHORIZATION genome3ddaily;
END;

BEGIN;
drop table if exists genome3d_data_daily.annotation_type;
drop table if exists genome3d_data_daily.annotation_segment;
drop table if exists genome3d_data_daily.annotation;
drop sequence if exists genome3d_data_daily.annotation_id_seq;

This no longer disrupts existing database connections.


What are the benefits of using MySQL Cluster? I have never dipped my hands in MySQL Cluster and want to know how it can benefit me. When is it best to use it, and does it have performance benefits?

When I wanted to get information about MySQL Cluster, MySQL (before Sun bought it) would send out a brief questionnaire, which I wish I still had. One of the questions asked if your tables had integer keys only.

Although MySQL Cluster is ACID-complaint, it does not provide a suitable storage engine for data with compound keys. In some respects, you have to plan its usage like that of a MongoDB or Cassandra in terms of data layout and sharding.

You also have to consider the most annoying part of MySQL Cluster: Network Communication. NDB Cluster tends to be chatty among servers. Even Gig-E can be brought to its knees if doing range queries or full table scans. You must be disciplined enough to use primary keys and unique keys, especially if your application thrives on such keys.

Unless you adhere to these simple observations, MySQL Cluster will do way more harm than good.


Slow queries on billions-rows-table // index used Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with.

My project is about collecting data from devices (up to 1000 or more devices), where every device is sending one data block every second, which makes about 3 million rows per hour.

Currently I've got one big table where I store the incoming data of every device:

CREATE TABLE data_block(
    id bigserial
    timestamp timestamp
    mac bigint
)

Because there are several types of data a data block can (or can not) include, there are other tables which reference the data_block table.

CREATE TABLE dataA(
    data_block_id bigserial
    data

    CONSTRAINT fkey FOREIGN KEY (data_block_id) REFERENCES data_block(id);
);
CREATE TABLE dataB(...);
CREATE TABLE dataC(...);
CREATE INDEX index_dataA_block_id ON dataA (data_block_id DESC);
...

It is possible that in one data_block there is 3x dataA, 1x dataB, but no dataC.

The data will be kept for some weeks, so I'm going to have ~5 billion rows in this table. At the moment, I have ~600 million rows in the table and my queries take a really long time. So I decided to make an index over timestamp and mac, because my select statements always query over time and often also over time+mac.

CREATE INDEX index_ts_mac ON data_block (timestamp DESC, mac);

...but my queries still take ages. For example, I queried data for one day and one mac:

SELECT * FROM data_block
WHERE timestamp>'2014-09-15'
AND timestamp<'2014-09-17'
AND mac=123456789

Index Scan using index_ts_mac on data_block  (cost=0.57..957307.24 rows=315409 width=32) (actual time=39.849..334534.972 rows=285857 loops=1)
  Index Cond: ((timestamp > '2014-09-14 00:00:00'::timestamp without time zone) AND (timestamp < '2014-09-16 00:00:00'::timestamp without time zone) AND (mac = 123456789))
Total runtime: 334642.078 ms

I did a full vacuum before query run. Is there an elegant way to solve such a problem with big tables to do an query <10sec?

I read about partitioning, but this won't work with my dataA, dataB, dataC references to data_block_id right? If it would work somehow, should I make partitions over time or over mac?

I changed my index to the other direction. First MAC, then timestamp, and it gains a lot of performance.

CREATE INDEX index_mac_ts ON data_block (mac, timestamp DESC);

But still, queries take >30sec. Especially when I do a LEFT JOIN with my data tables. Here is an EXPLAIN ANALYZE of the query with the new index:

EXPLAIN ANALYZE SELECT * FROM data_block WHERE mac = 123456789 AND timestamp < '2014-10-05 00:00:00' AND timestamp > '2014-10-04 00:00:00'

Bitmap Heap Scan on data_block  (cost=1514.57..89137.07 rows=58667 width=28) (actual time=2420.842..32353.678 rows=51342 loops=1)
  Recheck Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on index_mac_ts  (cost=0.00..1499.90 rows=58667 width=0) (actual time=2399.291..2399.291 rows=51342 loops=1)
        Index Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
Total runtime: 32360.620 ms

Unfortunately my hardware is strictly limited. I'm using an Intel i3-2100 @3.10Ghz, 4GB RAM. My current settings are as following:

default_statistics_target = 100
maintenance_work_mem = 512MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 4GB
work_mem = 512MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 2GB
max_connections = 20
random_page_cost = 2

This may reflect my MS SQL bias, but I'd try clustering the table by timestamp.  If you're frequently pulling data for a specific time span, this will help because the data will be physically stored contiguously.  The system can seek to the start point, scan to the end of the range, and be done.  If you're querying for a specific hour, that's just 3,600,000 records.

If your query (which is...?) is for a specific machine, Postgres will then need to filter out 99.9% of those 3.6 M records.  If this one-in-a-thousand filter is more selective than a typical date range fitler, you should use the more selective mac field as the first component of your index.  It may still be worth clustering.

If that still doesn't do it, I'd partition by the same field you're indexing, either timestamp or mac.

You didn't give the data types.  Are they appropriate to the data?  Storing dates as text will needlessly bloat your table, for example.


What are the sharding heuristics for MySQL - number of records per table & number of tables per instance? Designing an object store for ~10B objects and using mySQL for storing metadata. We know we need to shard and are looking for general heuristics to scale. Would appreciate pointers and hear about the back-of-the-envelope calculations you all used

Sharding of individual items is not too difficult -- you just need a layer to figure out what machine to go to to get the one item.

Sharding of something that you need to scan or search can be messy.  You would have to hand off the query to each possible machine, let them do the scan/search, get back the results, and stitch together them.  GROUP BY, ORDER BY, and LIMIT all have hiccups.  You end up doing some things both in the sharded machines and in the central aggregator.

You mentioned storing 10B metadata rows.  That sounds like a few TB.  That is easily handled in a single machine these days.  The table could be PARTITIONed, but do not rush into that until you understand what PARTITION does and does not give you.

Meanwhile, your "objects" could be scattered around dozens, even thousands, of machines.  Now you get two more problems -- knowing where to look, and dealing with crashes.

Knowing where to look -- a hash is simple, until you need more shards.  Having a "lookup" adds another column to the metadata, and perhaps an index.  I prefer a compromise between the two.

Crashes -- with a thousand machines, you should plan on one dying every week.  Where's its backup?  Or do you have another copy online?  Is two copies safe enough?  Are all thousand machines in the same location, thereby at risk of a single tsunami, tornado, or whatever?

Oh, back to the back of the envelope -- 10B INSERTs, one row at a time, could take a year to finish.  Please consider LOAD DATA and/or batched INSERTs.

INDEXing the monster metadata table versus caching.  Let's say you have 5 indexes other than AUTO_INCREMENT or CURRENT_TIMESTAMP -- that will be 5 random disk hits to update the indexes.  With terabytes of stuff, there is no way to cache it effectively.  Oops, you are now limited to about 20 rows inserted per second, no matter how you do the INSERTs.  With about 30M seconds/year, that leads a decade to insert your 10B rows.  Watch out for indexing!  Adding RAID striping helps my a small factor.  SSDs ($$$!) helps by maybe a factor of maybe 5.  (And it would be hard to get much past 1TB of SSDs on a single machine.)


How many database servers do I need? This may be a no-brainer to some of you, but I am a newcomer to DBA and this question popped into my head and I couldn't answer it on my own and so I am turning to the experts here.

I have a app that requires two databases –– one production and one for staging. Do I need 2 database servers, or should I use one server and simply keep them in different databases?

On the one hand, you can create a new server using Docker containers so easily. I've been wondering if keeping the data for two environments on the same database is a safe approach.

You need at least two. Don't mess around in production.


Patterns/Designs to help with concurrency/performance I'm working on a SQL Server 2008 R2 DB and I'm experiencing some performance/concurrency issues with probably the main table in the DB. Problems like slow reads, deadlocks, poor performance in general. 60% of users will read from this table while 40% will write to it. The problem I'm faced with is that the writing is actually a collection of big updates to about 50-100 rows at a time, doing stuff with re-parenting, updating FK's, updating chunks of HTML, etc. These writes are a problem when multiple users are doing similar updates while others are trying to read. Even without reading, the updates alone are an issue.

Unfortunately, breaking down the data in these tables hasn't helped. For e.g. 100 rows may represent a single entity to the end user similar to a document per say, so each row represents a section of a document, these updates treat the "Document" as a single entity which may include actions like merging section information, adding new sections, deleting old sections, updating content, moving sections around etc.

Are there any designs/patterns that can help with scenarios like these???

One point would be to not have locks on reads. This is what I do generally - ReadCommited as table hint helps (or on connection level). This is not repeatable (no locks in place). Retrieval may be isolated into non-locking transactions this way.

Also then possibly go partially to stored procedures - no round trips. Merge statement etc. - the usual "keep locks super small" approach.


What will be bytes occupied if we store 2 bytes value in 8 bytes column? There is one column which has the column (Column A) type as bigint(8 bytes), and I am storing the value in smallint(2 bytes). Since I may store bigint value in Column A so defined the data type as bigint.

What will be bytes taken the particular column contains smallint value? 8 bytes or 2 bytes.

Also, there is one column (Column B) which has column type as Varchar(100), and if I trying to store a value with length 50. What will be size occupied by the column? size of varchar(100) or size of varchar(50).

PS: I am using postgres for database.

You can easily get the values you are after:

SELECT column_a, pg_column_size(column_a),
       column_b, pg_column_size(column_b),
       ...
  FROM your_table;

This gives you the byte size of the given values stored in the columns, alongside with the values themselves.

A bigint will always consume 8 bytes, whereas a varchar just the size needed for the given value.  (The latter is tricky, read upon TOAST.)


Mongo Atlas API - Get Databases and Collections in a Cluster So with Mongo Atlas I can easily list clusters in a project by calling the API as follows:

curl -i -u "<user@domain.com>:<my-api-key>" --digest "https://cloud.mongodb.com/api/atlas/v1.0/groups/<groupid>/clusters"

I want to list all database (and if possible all nested collections) for a particular cluster.

Is this possible via the API - I am sure it must be but I am not seeing it in the documentation.

I want to avoid having to connect to each cluster and collecting this information with a query - this is for a service discovery tool I am building for inventorying purposes.

Thanks

The MongoDB Atlas API is for programmatic access to Atlas' management, monitoring, and backup  features. API connections are to the Atlas service, not to the underlying MongoDB clusters.

To query data in your clusters (for example, listing database and collections) you need to connect to each cluster using an authenticated MongoDB driver or client.

  I want to avoid having to connect to each cluster and collecting this information with a query - this is for a service discovery tool I am building for inventorying purposes.

This is the currently the only supported approach.


ORA-01017: invalid username/password; cannot login with other users except sys user, Oracle Database 12c? I have setup  Oracle Database 12c on my local machine. It was working fine initially but now I cannot login to users' account except the sys user. I have altered users' passwords and tried logging in using SQLPlus and SQL DEVELOPER.
ALTER USER XYZ IDENTIFIED BY XYZ;

I have also checked the parameter SEC_CASE_SENSITIVE_LOGON which is set to FALSE.
The users, that I am trying to connect are created in pluggable database i.e orclpdb and they do exist in the pluggable database.
I can only login to sys user of pluggable database and root.
The username and passwords are correct, I don't know why the oracle is behaving weirdly.

Why was SEC_CASE_SENSITIVE_LOGON set to false? That is deprecated in 12c. This is a known issue that I encountered many times.
Case-Insensitive Passwords and ORA-1017 Invalid Username or Password

The Oracle Database 12c release 2 (12.2) default authentication
protocol is 12 (Exclusive Mode). This protocol requires case-sensitive
passwords for authentication. Review your options if you have earlier
release password versions.
Starting with Oracle Database 12c release 2 (12.2), the default value
for the SQLNET.ORA parameter ALLOWED_LOGON_VERSION_SERVER is changed
to 12. This parameter refers to the logon authentication protocol used
for the server, not the Oracle Database release.
By default, Oracle no longer supports case-insensitive password-based
authentication; only the new password versions (11G and 12C) are
allowed. The case-insensitive 10G password version is no longer
generated.
...
If the following conditions are true, then you may have accounts that
are prevented from logging into the database after upgrading to 12.2:
You are upgrading a server that has user accounts created in an
earlier Oracle Database release.
User accounts created in the earlier release use a case-insensitive
password version from an earlier release authentication protocol, such
as the 10G password version.
Earlier release user accounts have not reset passwords.
The server has been configured with SEC_CASE_SENSITIVE_LOGON set to
FALSE, so that it can only authenticate users who have a 10G
case-insensitive password version.
...
Option for Servers with Accounts Using Only 10G Password Version
After you upgrade to Oracle Database 12c release 2 (12.2), complete
the following procedure to enable accounts using the 10G password
version:
Log in as an administrator.
Edit the SQLNET.ORA file to change the
SQLNET.ALLOWED_LOGON_VERSION_SERVER setting from the default, 12, to
11 or lower. For example:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
After you change to a more permissive
SQLNET.ALLOWED_LOGON_VERSION_SERVER setting, expire users’ passwords
to require them to change their passwords. For detailed information,
refer to Oracle Database Security Guide.


Choosing index fillfactor for large, highly inserted table We have a table that will hold records on the 108 order.

╔══════════╦═════╦══════╦══════════════════════════╗
║    id    ║  a  ║  b   ║ ...many other columns... ║
╠══════════╬═════╬══════╬══════════════════════════╣
║        1 ║   1 ║  118 ║                      ... ║
║        2 ║   1 ║ 1022 ║                      ... ║
║        3 ║   5 ║  118 ║                      ... ║
║        4 ║   3 ║  118 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║    10020 ║   5 ║ 1022 ║                      ... ║
║    10021 ║   1 ║  118 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║  5000000 ║   2 ║   30 ║                      ... ║
║  5000001 ║   1 ║   30 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║ 28218321 ║   1 ║  118 ║                      ... ║
║ 28218322 ║   4 ║   57 ║                      ... ║
╚══════════╩═════╩══════╩══════════════════════════╝

Column a always holds one of 5 values.  Column b always holds one of ~5000 values. a and b are unrelated.  So for every value of a there will be at least tens of millions of table rows, and for every value of b there will be at least hundreds of thousands of rows.

A lot of our queries are based on WHERE a=<value> or WHERE b=<value> so we are indexing those columns (two separate, single-column btree indexes).  I am trying to get a sense of what the optimal choice of FILLFACTOR would be for this situation.

This table does not receive many UPDATEs, however it receives large bursts of INSERTs (on the order of 106) in short periods of time.  At the same time it is under moderate, constant read load.

If my understanding of btree index fillfactor is correct it sounds as though we may benefit for a fairly low fillfactor for a and b indexes, allowing lots of empty space for new row references to be added to the leaf pages as new records come in with the same a or b value as previous ones.  Is this correct?

Is this a situation where a low fillfactor would be beneficial and if so what approximate value should we be looking at using?

The fill factor for a B-btree index is only used during initial creation of the index, and when you insert a new highest key.  So any effect of setting this won't last for long on a highly active index which is randomly inserted, as it will quickly settle in to a steady state which is independent of the starting fill factor.  If you change the fill factor and then rebuild the index, you might get a boost in performance.  But unless you can rebuild the index frequently, it wouldn't be a sustainable boost.

The fill factor for indexes, unlike for tables, is not a very generally useful knob.  If it didn't already exist and someone proposed adding it now, they might have a hard time getting it accepted.


Using Structured Filetypes as Databases In having a debate with a coworker it was posed that a flat file, in this case a CSV, could be considered a database. This would include XML and JSON as well. I'm interested in differing views on this.

I would have to say, resoundingly, that your co-worker is correct and you are not. dBASE (.dbf) is a great example of a flat file database--which has been used for 35 years or so. https://en.wikipedia.org/wiki/.dbf

Even RDBMS like SQL use simple 8kb pages for storing data. The tools we use to access the data makes it look more complex, but in reality it's stored in a very simple structure. Using hints, you can even have SSMS display these pages of data right in the output window.

By going by the simple definition of the database, it's a structured collection of data. CSV, XML, JSON, DBF all meet this definition.


Which version of MySQL should I use? For ages I have been using MySQL 5.1.37.

Recently I noticed that this version is no longer supported in repository, so I need to upgrade mysql.

I assume that changing the version should be complicated process with changes in my.cnf, and may be with the changes in the code.

Questions

Which version is the most stable and preferred?
Which version should I use?

You need to get away from MySQL 5.1 ASAP

MySQL 5.0 Active Support ended on December 31, 2009 - Now in Extended Support Phase
Per the MySQL Support Lifecycle policy, active support for MySQL 5.0 ended on December 31, 2009. MySQL 5.0 is now in the Extended support phase.

Versions have changed rapidly since Oracle stepped in

MySQL 5.1.61 released 2012-01-11
MySQL 5.1.60 released 2011-11-18
MySQL 5.1.59 released 2011-09-16
MySQL 5.1.58 released 2011-07-05
MySQL 5.1.37 released 2009-08-02 ( pretty aged by now )

MySQL 5.1 will soon reach its EOL (End of Life) in terms of Active support and go to Extended support (EOL : TBD). Naturally, that support is commerical. It would not hold my breath for that kind of support for the Community versions.

MySQL 5.5.20 was just released 2012-01-11 and 5.5.19 was just a month ago. As with any upgraded product, always read release notes to see what changes and improvements have come about since the previous stable release.


PostgreSQL: how can I create an index for querying variable combinations of booleans I have a table of people, each person has a color and they seek various other colors and require various skills.
So I can query this I've created a table like so:
person
id, color_is_red, color_is_green, color_is_blue, color_is_yellow,
seeks_red, seeks_green, seeks_blue, seeks_yellow, seeks_technical, seeks_creative, seeks_leadership
I can then query the table like so:
-- Searching for green & yellow people that looking for red people with skills creative or technical

select id
from person WHERE

    color_is_red = false
    AND color_is_blue = false
    AND seeks_red = true
    AND
    (
        seeks_creative = true
        OR seeks_technical = true
    )
limit 200

I'm unclear if this is the best way to structure this data. I also don't know how I can index this. I had hoped to use a BLOOM index but bloom doesn't support booleans. I think maybe I need a GIN index as I'll be querying different combinations of the colors depending on whats required however I've tried creating a gin_btree index but the query planner isn't using it. Another option would be a to use a bitset but I'm not sure how I would do this or index it.
Any help would be greatly appreciated!
Thanks very much
update:
I have created a single btree index for each column but the query planner doesn't seem to be choosing to use them. Here is the explain output:
https://explain.depesz.com/s/TZLG
update 2
I've tried to use intarray to index int[] but I've not had much look with basic queries taking over a minute to execute: https://explain.depesz.com/s/RyBn

I've found a solution by casting the bools to ints and indexing using a bloom index. The query now executes in 27ms using a bitmap index scan compared to the previous 100ms of the sequential scan.
The index:
CREATE INDEX bloom_idx ON person USING bloom (
    CAST(color_is_red as int), CAST(color_is_green as int), CAST(color_is_blue as int), CAST(color_is_yellow as int),
    CAST(seeks_red as int), CAST(seeks_green as int), CAST(seeks_blue as int), CAST(seeks_yellow as int)

);

The query:
explain (analyze, buffers, format text)
SELECT id
FROM person
WHERE CAST(color_is_red as int) = 0
  AND CAST(color_is_blue as int) = 0
  AND CAST(seeks_red as int) = 1
  AND
    (
        CAST(seeks_creative as int) = 1
        OR CAST(seeks_technical as int) = 1
    )
limit 200;

The explain output:
https://explain.depesz.com/s/piUf
"Limit  (cost=20348.00..20352.04 rows=1 width=4) (actual time=27.660..27.824 rows=200 loops=1)"
"  Buffers: shared hit=1998"
"  ->  Bitmap Heap Scan on person  (cost=20348.00..20352.04 rows=1 width=4) (actual time=27.658..27.803 rows=200 loops=1)"
"        Recheck Cond: (((color_is_red)::integer = 0) AND ((color_is_blue)::integer = 0) AND ((seeks_red)::integer = 1))"
"        Rows Removed by Index Recheck: 58"
"        Filter: (((seeks_creative)::integer = 1) OR ((seeks_technical)::integer = 1))"
"        Rows Removed by Filter: 72"
"        Heap Blocks: exact=37"
"        Buffers: shared hit=1998"
"        ->  Bitmap Index Scan on bloom_idx_no_skills  (cost=0.00..20348.00 rows=1 width=0) (actual time=24.281..24.281 rows=156588 loops=1)"
"              Index Cond: (((color_is_red)::integer = 0) AND ((color_is_blue)::integer = 0) AND ((seeks_red)::integer = 1))"
"              Buffers: shared hit=1961"
"Planning Time: 0.458 ms"
"Execution Time: 27.905 ms"

(Querying a table of 1 million randomly generated rows)


Window function with order by returns strange results Here is example:

drop table if exists tst;

create table tst (
num integer not null
);

insert into tst values (1), (2), (3);

-- window functions WITH order by clause
select *, max(num) over (partition by true order by num asc), array_agg(num) over (partition by true order by num asc) as test
from tst;

-- window functions WITHOUT order by clause
select *, max(num) over (partition by true), array_agg(num) over (partition by true) as test
from tst;

The result is as follows:

Why order by clause has impact on aggregation function?

That's expected.

An aggregate function used as a window function together with an order by acts as a "rolling" aggregate, i.e. it only considers values up to the "current rows" based on the order by expression.

Quote from the manual

  There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its window frame. Some window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY is omitted the default frame consists of all rows in the partition.

(Emphasis mine)

For a more detailed explanation see this answer


MySQL merge two tables of different database to single database Friends, i have database named as DB1 with a table called TB1A and another database as DB2 with  a table TB2A.
TB1A having 5 columns and TB2A  having 8 columns, All the columns available in TB1A is present in TB2A.
I want to create new table called TB3A which should be a merged result of a TB1A and TB2A. Means TB3A will have 8 columns and contains all the datas of TB1A and TB2A.
CREATE TABLE TB3A AS (

SELECT *
  FROM DB1.TB1A
  JOIN  DB2.TB2A
)

While i trying like above it is showing an error like below

Error Code: 1060
Duplicate column name 'Agreement_Id'
Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

Does any one have any idea about this. I have google it this issue but i am not able to find the solution for this ..

What you've entered is equivalent to:

CREATE TABLE TB3A AS (

SELECT a.AgreementID, a.Column2, a.Column3, a.Column4,
       a.Column5, b.AgreementID, b.Column2, b.Column3,
       b.Column4, b.Column5, b.Column6, b.Column7, b.Column8
  FROM TB1A a JOIN TB2A b
);

You'd have 13 columns, with only 8 distinct names. This is a problem.

What you probably want to do is this:

CREATE TABLE TB3A AS (

SELECT AgreementID, Column2, Column3, Column4,
       Column5, Column6, Column7, Column8
  FROM TB2A
);

INSERT INTO TB3A (AgreementID, Column2, Column3, Column4, Column5)
SELECT * FROM TB1A;

TB3A will now have m + n rows, where m is the number of rows in TB1A, and n is the number of rows in TB2A.

If some rows in TB1A and TB2A are duplicates, this will not remove those. You can change the INSERT ... SELECT statement to filter out rows from TB1A that match rows already in TB3A (thus, originally from TB2A) with matching values in whatever columns you need. For instance, to not bring in rows from TB1A that have the same AgreementId as rows already in TB3A, the INSERT would be:

INSERT INTO TB3A (AgreementID, Column2, Column3, Column4, Column5)
SELECT * FROM TB1A
 WHERE NOT EXISTS (SELECT 1 FROM TB3A
                    WHERE TB1A.AgreementID = TB3A.AgreementId
                  )
;

Additional note on the original statement: JOIN is generally assumed to be an INNER JOIN - one that matches rows form the two tables based on one or more criteria. For example, TB1A JOIN TB2A ON TB1A.AgreementId = TB2A.AgreementId would return the rows from TB1A and TB2A the had the same AgreementID. Rows in TB1A with an AgreementID not found in TB2A would not be returned, and rows in TB2A with an AgreementID not found in TB1A would not be returned.

If there were three rows in TB1A with the same AgreementId, and 2 rows in TB2A with that same ID, those rows would generate 6 rows in the result set:

TB1A      TB2A
  x         m
  y         n
  z

Result set:
    x,m
    x,n
    y,m
    y,n
    z,m
    z,n

In some SQL dialects (MySQL included), if there is no JOIN criteria specified, then the INNER JOIN becomes what's called a CROSS JOIN. Here, since there's nothing to indicate which rows should not be joined together, all rows are joined together. If you have m rows in TB1A, and n rows in TB2A, you don't get m+n rows - you get m*n rows. That's what would have happened, if your original statement had run.


Why does Oracle keep working on MySQL 5.1 2 years ago the first version of Mysql 5.5 was released. What is the reason to continue working on MySQL 5.1 - the current release is Mysql 5.1.62

It looks like there are should be fundamental differences between them. I am confused which version to prefer.

Oracle are obliged to fix security bugs and updates for MySQL 5.1 until the Premium support period has ended - remember that there are paying customers out there using the product in production.

See page 13 of this document for support end dates by MySQL version.

MySQL 5.5 is more than ready for production use & I'd recommend using that for any new projects going forward.


SQL Server - drop user triggered silent revoke of privileges I encountered following scenario:

A user X granted a ton of permission in the SQL Server database. For example it granted SELECT permission on table Y to user Z.

A user X was dropped with simple DROP USER statement.

No errors encountered. User X disappeared and all permissions he granted with him - including user Z select permission o table Y.

Question 1. Shouldn't there be an error preventing me from dropping user with this kind of implications?
Question 2. Is it controlled by any database option etc.
Question 3. The DROP USER in documentation has no remarks indicating this kind of behaviour - can any elaborate what I'm missing?

Thx

For reference it was SQL SErver 2016 server but database was running in 2008 compatibility mode.

The tl;dr answer is that nothing went wrong here and the behaviour is as designed:

A DROP USER statement will remove the user (and by extension their permissions).

More full answer:

SQL Server and T-SQL are powerful tools - there are little to no "are you sure?" checks, such as are common in end user applications, when executing queries - the only things that prevent queries executing are only technological errors (not potential user errors) or any database constraints.

SQL SERVER by default is in autocommit - that is, IMPLICIT_TRANSACTIONS are off, meaning that the query is committed as soon as it is finished executing - no second chances.

Although there are no options as such, this behaviour can be controlled. As mentioned in Akina's comments, one could add this custom behaviour (for example through triggers). The way that this is controlled out-of-the box is by permissions - only user's with the correct permissions can drop other users - only some of the highest privileges can execute such statements. As per BOL article on DROP USER, the statement requires the ALTER ANY USER permission on the database (and of course the db_owner and sysadmins can do this)
The DROP USER documentation does state the required permissions, although you need to go into more detail to find out why this happens. In SQL Server permissions are granted TO a USER (or LOGIN at the Instance level) or the USER added to a ROLE. The USER has to exist for it to have these privileges.

You can find more about where permissions are stored in SQL Server as per This question by exploring the tables sys.database_permissions and sys.database_principals - joined on Princpal_id (and you can see for yourself that without the principal i.e. the USER, their can't be a permissions for it). Nb. Although probably not that risky in most cases, exploring system tables at whim on a production DBs is not advised!

This article explains about permissions in SQL SERVER in detail, and from an authorization point of view This BOL article has more information and is a good start for learning the concepts.

What can you do?

To ensure this doesn't happen again, ensure that only appropriate permissions are given to everyone (Principle of Least Privilege), and those with elevated privileges take care and know what they're doing. Importantly, don't make changes in production with first testing (and testing again)! Also - follow change control procedures appropriate in your company.

To recover, you can either restore from a backup, or you'll have to recreate permissions (ideally you'll have documentation about your system to do this from, if you don't this is the time to make it!).

Finally

SQL Server can take no prisoners - and by default SQL Server COMMITs transactions automatically (and keeping that way is a good thing), be sure of your query before executing and test, test and test again.


Best database for a real time application I am a student doing my Final Year Project where I need to build an android application for on the road navigation. The problem is the application has a feature that alerts the user of nearby incoming vehicles (that have the same system) when the user is near to a junction by updating and getting positions of vehicle in a database in real time. Hypothetically speaking, if there are millions of vehicle or user using the same system, what database can process these amounts of transactions very fast, let's say maximum response time is 1 second per application?

I have literally no knowledge regarding the types of database.

I don't think the database server will be your bottleneck. Many databases can do sub-second responses to queries provided your database is scaled and designed correctly.

I think you need to be asking yourself questions about how the android devices will connect (mobile internet can be slow sometimes), how your middle tier logic will work and how the devices will communicate with the middle tier.

If you are serious about having millions of vehicles on the system you need to be thinking about how often each of them will update their location and how often the other cars will ask for nearby cars.

Let's say you have a million of cars, and each device has to get a sub-second status update about the nearby cars, that would mean that cars need to update their status every half a second and every car requesting statuses every half a second.

That's millions of requests per second from the devices to your web services and millions of queries per second from your middle tier to your database server.
Keeping in mind that your web service will need to calculate proximity, take into account maps and streets and the like, i think your database platform will be the least of your worries.


How to remove a secondary database from an availability group and rejoin it I have an availability group (AG) with multiple databases (DB-A, DB-B, DB-C), and multiple secondaries (SEC-B, SEC-C) and one of the databases will not resume synchronization on just one of the secondaries.

For this example, DB-C is not synchronizing on SEC-C and no amount of restarting SQL Server or resuming HADR will get it to start again.

I don't want to remove the replica (secondary SEC-C) from the AG because I would then have to resync all of the databases (DB-A, DB-B, and DB-C), and that would take more time than is necessary.
I also don't want to completely remove the database (DB-C) from the AG because there are other secondaries (SEC-B) where there is no problem, and I don't want to have to resync it or temporarily lose my HADR on the secondary where it is working.

How can I remove just this one secondary database from the AG, resynchronize it, and add it back to the AG?

This can be done by simply removing the secondary database from the availability group (AG), restoring backups to get it back in sync, and then adding it back to the AG.  SQL Server Management Studio has support for removing a secondary database from an AG and adding it back, but it doesn't  have a wizard (as of this writing) for doing all of the steps required to backup, restore, and rejoin.

Remove the secondary database from the AG by running the following T-SQL on the secondary (SEC-C in the OP example) where the database is not synchronizing:

ALTER DATABASE DB-C SET HADR OFF

See Remove a Secondary Database from an Availability Group.  You can also do this step in SSMS by drilling down to the database under the AG on the secondary, right-clicking on it, and selecting the option to remove it.
Drop the database from the secondary.
Take a full backup and at least one log backup on one of the database replicas that is still in the AG and synchronized, and restore them to the secondary.  If it's a big and busy database and you're restoring to a DR site, this could take more than a day.  Be sure to leave the database in recovery mode (restore with NORECOVERY)!

See Prepare a secondary database for an Always On availability group.
When you've got the secondary database caught up with the latest log backup restored to it, join it to the AG by running the following command on the secondary (SEC-C in the OP example):

ALTER DATABASE DB-C SET HADR AVAILABILITY GROUP = AG_name

See Join a secondary database to an Always On availability group for more information, including how to do this step in SSMS.

This answer is intended to be a high level, succinct guide.  This is all well-documented by Microsoft, but it's spread out over multiple pages, which of course have far more info, and the pages are in different categories.  The page to remove a secondary database is under:

Business continuity -> Always On availability groups -> How-to -> Operation

and the other pages referenced are under

Business continuity -> Always On availability groups -> How-to -> Configure availability group.


When using DBCC INPUTBUFFER to find query associated with spid, is there a way to show variable values of that query? I have been troubleshooting an issue within a database that has some serious blocking issues.

I found the command DBCC INPUTBUFFER a while back that allows me to see the query run for that spid.  I have used this to see what is running and causing the issue.

What I am confused about is that the query that DBCC INPUTBUFFER shows does not include the variables associated with the query.  For example, if the query joined on the @ID of a table, the provided query by DBCC INPUTBUFFER does not show the @ID value.

I am just wondering if there is a way to see what variable is associated with the query.  And/or if maybe this is not the norm and normally DBCC INPUTBUFFER should show you the variable associated with the query.

If it is the latter, then my problem probably rests in the fact that a variable is not being provided and it is just querying a set of rather enormous tables and taking an eternity.

I have been troubleshooting an issue within a database that has some serious blocking issues. I am just wondering if there is a way to see what variable is associated with the query.

I would suggest, you should use either of below for troubleshooting blocking problems :

You can use beta_lockinfo by Erland Sommarskog

beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much information about a blocking situation as possible, so that you can instantly find the culprit and kill the blocking process if the situation is desperate. Then you can sit back and analyse the output from beta_lockinfo to understand how the blocking situation arose and figure out what actions to take to prevent the situation from reoccurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, what command they last submitted and which statement they are executing. You also get the query plans for the current statements. Normally, you run beta_lockinfo to look at the output directly, but there is also an archive mode where the data is saved to table. This is not the least useful, if you want someone to send you the output from beta_lockinfo at a site you don't have access to yourself.

Another method is to use sp_whoIsActive by Adam Machanic with @get_locks = 1 and dump the data into a table for analysis
EXEC sp_WhoIsActive
@filter = '',
@filter_type = 'session',
@not_filter = '',
@not_filter_type = 'session',
@show_own_spid = 0,
@show_system_spids = 0,
@show_sleeping_spids = 0,
@get_full_inner_text = 1, ----------> 1 will get full inner text of query
@get_plans = 1,
@get_outer_command = 1,   ----------> 1 will get outer command of query
@get_transaction_info = 0,
@get_task_info = 1,
@get_locks = 1,   ----------> 1 = ON (get lock info); 0 = OFF
@get_avg_time = 0,
@get_additional_info = 0,
@find_block_leaders = 1,   --> 1 = will find Lead blockers
@delta_interval = 0,
@output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
@sort_order = '[start_time] ASC',
@format_output = 1,
@destination_table = '',
@return_schema = 0,
@schema = NULL,
@help = 0

There are other ways like Blocked Process Report Event Class or if you are using sql server 2008 and up, then Extended events, but above ones will give you the details that you are looking without much effort.


A methodology for tracing a query that sporadically runs for hours instead of seconds Once a few weeks or months, but always on the same weekday, one query from a stored procedure used in a daily batch job may visibly become stuck and run for about 200 minutes until the application kills its connection. This is a very trivial textbook query which joins 5 tables and calculates a sum() on one field to assign to a variable, so there is no point in providing it here. The total number of records to be summed is normally with 1-2 dozens. This query has no parameters, so parameter sniffing is (fortunately) out of question.

I have looked at a number of things:

There are no online users in the DB. It is used purely as a data dump.
There is no job that runs simultaneously. The last activity on the DB is a tranlog backup that completes about 10 min earlier. The next activity starts after about 2 hours, it is a full backup which completes fine after a few min (while this query is still going nuts).
There are following locks while this query is running, all in GRANT status:

OBJECT lock on one of the tables requested in Sch-S mode
HOBT.BULK_OPERATION lock on another table in S mode
OBJECT lock on yet another table requested in IX mode

The query has (NOLOCK) hint on all joined tables. It is in RUNNABLE state and according to sp_whoisactive, which runs every 15 min on the server, the CPU count of the query in question is constantly increasing. The CPU grows to about 1000x of the standalone query execution. The IO of the query from sp_whoisactive is about the same as if I ran it standalone, but the reads count is enormous, 10000x times over standalone execution. It feels like the query is in an endless loop. But when I get into the office in the morning, the job re-runs within a few seconds.

The plan reported by sp_whoisactive is the same as standalone. There is an optimizer hint for creation of an index when I run it, but as the query standalone time is around 200ms, I am not concerned about this hint. Implementing it seems to shave another 10ms off that time.
There are no errors reported by DBCC CHECKTABLE on any of the involved tables or DBCC CHECKDB.

The only point of suspicion I may have about the plan is that it has two parallelism icons. But adding option (maxdop 1) only increases its execution from 200 to 400ms.

There are 5 tables involved, all different sizes, the largest is around 1*10^6 records, not overly large. We refresh statistics and indexes daily at 4am and the job runs at midnight before any large volumes of data would be imported. The job itself only adds 1-10 records.

Forcing the execution plan stored for the hung query leads to the same result: stuck query, huge CPU and IO. Letting the optimizer do its magic, or forcing the plan that I get standalone (they are the same) it completes in a split second.

...

Long story short: I was allowed to run a nightly scheduled trace in production for the duration of the "normal" execution time of this job in order to try to troubleshoot this. We do not know when the next occurrence will be, only hope that it will follow the pattern which we observed - every few weeks.

My million dollar question is what events should I trace for? I am only a SQL developer and do not have the typical DBA skills, so I would not know what innards of SQL server should be exposed through a trace to catch the culprit red-handed.

Based on your thorough outline, I believe you may be experiencing a problem with your table statistics.

Tables only automatically update their statistics when certain thresholds of row updates are passed, in the case of any table over 500 rows it requires 500+20% of the rows to change. For example your million row table requires 200,500 row changes before it will update statistics.

An index REBUILD will update the statistics on a table (REORGANIZE does not).

I think your tables are growing over time and eventually invalidating your statistics, but not enough to trigger an automatic update. However, your reindexing job is not triggering an actual REBUILD for the relevant tables until after your query starts performing poorly due to the last couple changes from earlier in the day. If your changes are inconsistent and small enough increments this would make the performance degradation consistent and sudden. Your reindexing job then fixes the problem after the query has ran poorly and everything looks fine the next morning.

Things to check to confirm:

If you can catch a 'bad' query plan check the pipes' Actual versus Estimated number of rows returned. If this is radically different it's red flag for statistics issues.
Check the criteria on your Reindex Job to confirm that it's not updating the indexes on the query's tables regularly.
If you can use the following query regularly you can track when the statistics are updated:

SELECT name AS index_name
   ,STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('SchemaName.TableName')

If you are running SQL Server 2008 R2 Service Pack 1 or later, you have the option to enable documented trace flag 2371 to dynamically update statistics more frequently:

  The higher the number of rows in a table, the lower the threshold will become to trigger an update of the statistics. For example, if the trace flag is activated, update statistics will be triggered on a table with 1 billion rows when 1 million changes occur. If the trace flag is not activated, then the same table with 1 billion records would need 200 million changes before an update statistics is triggered.

The new behaviour is enabled by default (i.e. without the trace flag) from SQL Server 2016 onward, for databases at compatibility level >= 130.


How Can I Determine Object Distribution in a Multiple-File Filegroup? I am looking to eliminate an extraneous data file in a filegroup in my DB but don't want to use the dreaded DBCC SHRINKFILE method, I am preferring to rebuild indexes into an alternate FG, perform the shrink with EMPTYFILE followed with removal of the file and then rebuild back into the original filegroup. Is there a method to identify which table/index objects are populating the utilized pages/extents in the target database file?

You should assume, for all intents and purposes, that parts of every object in a given filegroup will be distributed across all of the files in that filegroup. You might be able to work up some script that will prove one or two smaller tables don't have any data on file 5 or file 8, but for what purpose? I say it's easier to just write a script that will rebuild all of the tables in that filegroup.


Can I specify a name for the index which SQL Server creates for my unique constraint? In SQL Server I deploy a table with a UNIQUE constraint:

CREATE TABLE [dbo].[Something] (
    [IdentityId]  UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID()
    -- Whatever else...
)

and this makes SQL Server to create a unique index to back the constraint. That index will have name generated automatically and looking something like

UQ__tmp_ms_x__1F778345BD

where UQ means "unique" and all the rest is well, random stuff to me. If I have two unique constraints for the same table they just look like two random strings which is not very convenient.

Is it possible to specify the name for the index which is created under the hood when a unique constraint is created?

Referencing the documentation for Create Unique Constraints, you should be able to use something like this:

CREATE TABLE [dbo].[Something](
    [IdentityId] [uniqueidentifier] NULL,
 CONSTRAINT [MyUniqueConstraintName] UNIQUE NONCLUSTERED
(
    [IdentityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


How does PolyBase collect statistics from external tables? Books online has this to say about query optimisation in PolyBase:

  The query optimizer makes a cost-based decision to push computation to Hadoop when doing so will improve query performance. It uses statistics on external tables to make the cost-based decision. Pushing computation creates MapReduce jobs and leverages Hadoop's distributed computational resources.

My question - how and when does the optimizer collect these statistics?

Inside the SQL Server relational engine statistics can be created implicitly or explicitly, they age as writes occur, and can be refreshed explicitly or during table maintenance. Are there similar strategies employed in PolyBase?

Given the data may be loaded into Hadoop/ Azure blob without SQL Server seeing it on the way in, and data volumes will be large (most likely) run-time ad hoc statistic creation through sampling or similar seems an unlikely strategy to me.

Creation and maintenance of PolyBase statistics is in the hands of the DBA.
The "getting started" guide provides several steps to establish PolyBase, the last of which is CREATE STATISTICS referencing the external table.
The documentation on CREATE STATISTICS states:

Statistics for external tables
When creating external table statistics, SQL Server imports the external table into a temporary SQL Server table, and then creates the statistics. For samples statistics, only the sampled rows are imported. If you have a large external table, it will be much faster to use the default sampling instead of the full scan option.

and further down

Limitations and Restrictions
Updating statistics is not supported on external tables. To update statistics on an external table, drop and re-create the statistics.

From this I conclude it is up to the system administrator to

create appropriate statistics for the intended workload
monitor for stale statistics
implement maintenance procedures for external statistics
ensuring there is sufficient space for the "temporary table" (TempDB?)

One imagines that, as the product matures and gains traction, more features from the current data engine (auto create & auto update statistics) will be ported to PolyBase. On the flip side, do you really want to wait for results while a 5PB store is sampled?


Storing IPv4 and IPv6 hosts and networks I have to store the IPv4/v6 address and networks(CIDR notation) in a MySQL database. Unfortunately, I am constrained to use Mysql.

So, I'm looking for the equivalent of the inet type of  PostgreSql. :

  The inet type holds an IPv4 or IPv6 host address, and optionally its
  subnet, all in one field. The subnet is represented by the number of
  network address bits present in the host address (the "netmask"). If
  the netmask is 32 and the address is IPv4, then the value does not
  indicate a subnet, only a single host. In IPv6, the address length is
  128 bits, so 128 bits specify a unique host address.

  The input format for this type is address/y where address is an IPv4
  or IPv6 address and y is the number of bits in the netmask. If the /y
  portion is missing, the netmask is 32 for IPv4 and 128 for IPv6, so
  the value represents just a single host. On display, the /y portion is
  suppressed if the netmask specifies a single host.

I'm aware of the followings functions of Mysql 5.6 INET_ATON(), INET_NTOA(), INET6_ATON(), INET6_NTOA(). But I use Mysql 5.5... So, forget INET6_xxxx.

I'm also aware of the following question "Storing IP address" but it doesn't deals with network CIDR notation.

How you would implement the equivalent of inet type in Mysql 5.5?

3 columns : Network (2xBIGINT), CIDR (TINYINT) ?
string ? (@Phil)

First: there is no equivalent of the inet type of PostgreSql in MySQL yet.

You should just use the string type to store all kind of IP addresses.
if you need them all in a certain format, convert them before inserting them into the DB.

If you need to do calculations with them, as you mentioned INET_ATON() and INET_NTOA() are the needed functions to be used.


Need help identifying how to make a function selectable using table() clause and a custom data type I would like to be able to run the following code to select records:
Desired Syntax
select * from table(my_new_package.get_data(123))

I have been successful in creating code that compiles, but I am unable to retrieve the data using the above syntax. Could anyone identify how I could update this to ensure the above query will run properly.
Here is the current code:
Package Spec
create or replace package my_new_package is

  -- Types
  -------------
  type my_data_type is record (
      myfield1          VARCHAR2(10)
      ,myfield2         VARCHAR2(10)
    );

   type my_data_type_TABLE is table of my_data_type index by binary_integer;

   -- Functions
   -------------
   FUNCTION get_data(i_id_number varchar2) return my_data_type_table;

end my_new_package;

Package Body
create or replace package body my_new_package is

  function get_data(i_id_number varchar2) return my_data_type_table IS
    current_row my_data_type;
    all_rows my_data_type_table;
    n integer;

    -- Will update the cursor to pull real data
    cursor cur is
      select '1' myfield1, '10' myfield2 from dual
      union all
      select '2' myfield1, '20' myfield2 from dual
    ;
  BEGIN
    n := 0;
    for rec in cur LOOP
      n := n+ 1;
      current_row := rec;
      -- do some things to current row

      -- Add modified current row
      all_rows(n) := current_row;
    END LOOP;

    return all_rows;
  END;

end my_new_package;

The above code compiles, but I can't select from the data using the desired syntax.
Is there anyway to do this where the custom types are embeded at the package level only and not created at the database level?
This will be for Oracle 12 (and Oracle 19).

You need to define the function as pipelined. Below works in 19c.
Package header:
create or replace package my_new_package is

  -- Types
  -------------
  type my_data_type is record (
      myfield1          VARCHAR2(10)
      ,myfield2         VARCHAR2(10)
    );

   type my_data_type_TABLE is table of my_data_type;

   -- Functions
   -------------
   FUNCTION get_data(i_id_number varchar2) return my_data_type_table pipelined;

end my_new_package;
/

Package body:
create or replace package body my_new_package is

  function get_data(i_id_number varchar2) return my_data_type_table pipelined IS
    current_row my_data_type;
    all_rows my_data_type_table;
    n integer;

    -- Will update the cursor to pull real data
    cursor cur is
      select '1' myfield1, '10' myfield2 from dual
      union all
      select '2' myfield1, '20' myfield2 from dual
    ;
  BEGIN
    n := 0;
    for rec in cur LOOP
      n := n+ 1;
      pipe row(rec);
    END LOOP;

  END;

end my_new_package;
/

Test:
select * from table(my_new_package.get_data('A'));

MYFIELD1   MYFIELD2
---------- ----------
1          10
2          20


How to real-time sync local Postgres instance to Cloud Postgres I have a local postgres database (9.2) running on Ubuntu that is constantly being written to by an application capturing sub-second data. (99.9% of the transactions are inserts).  I also have a postgres database with the same data structure sitting in the cloud.  I need a way to get some of the data (not all of the data) up the cloud in near real-time. (Oh, I might have several local postgres databases pushing to the same cloud database.) I would of course need to ensure all the data I want gets delivered if the connection went down. Ideally I would just like to specify what tables/views to push. Anyone have any ideas?  I would prefer not to have to write my own solution but I am not taking any option off the table.

I have looked at postgres streaming replication, however, from what I have read it's a full database replication not just a subset of the data.

Thanks in advance for the ideas.

Londiste, Bucardo or Slony-I can do this.

See Replication, Clustering and Connection Pooling on the PostgreSQL wiki for details.

Londiste would probably be my first choice for this use case, mainly for simplicity.


Time taken to drop/add index is lesser than adding a column. Why is that? I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though.

I'm performing an alter by default it does ONLINE alter.

When I add column it takes at least 50 mins for 50G table in our infrastructure, but when I performed removing and adding a composite index of 3 columns it took 8 mins.

Any idea internals of what it is doing?

Also does the duration changes if I'm adding an PK index/UK index.

Let me say in a simple way. When you create a new  table/index and then you are inserting 10 rows so, index value will be 1 to 10. Now you are deleting 5 and 6 the row in the table so ,holes are formed. Next time while inserting rows from 11 to 20 then index 5 and 6th rows need to be refilled that's why it's taking time. If you drop a column and recreate index then value is auto increment that's why it's faster and there is no hole.


Optimal schema for my JSON data? I am requiring my students to keep a 3 day food log. I want to use that data to display on their personalize web dashboard and will be bringing that info in as a JSON. The primary data I'll display is the date, food they ate, calories from fat, protein, carbs and total calories.

What would be the best way to structure my MYSQL database? Should I have one row in the same table with all the data? In the example below, the student drank chocolate milk. Would I have one row with fields for student ID, name of food, fat, protein, carbs and date?  They may drink chocolate milk more than once per day so that would be a lot of duplicate information in each row but I'm not sure how to better structure this?

Below is a sample of the JSON I'll be inserting into my table(s):

{
    "foods":[
        {
            "isFavorite":true,
            "logDate":"2011-06-29",
            "logId":1820,
            "loggedFood":{
                "accessLevel":"PUBLIC",
                "amount":132.57,
                "brand":"",
                "calories":752,
                "foodId":18828,
                "mealTypeId":4,
                "locale":"en_US",
                "name":"Chocolate, Milk",
                "unit":{
                    "id":147,
                    "name":"gram",
                    "plural":"grams"
                },
                "units":[226,180,147,389]
            },
            "nutritionalValues":{
                "calories":752,
                "carbs":66.5,
                "fat":49,
                "fiber":0.5,
                "protein":12.5,
                "sodium":186
            }
        }
    ],
    "summary":{
        "calories":752,
        "carbs":66.5,
        "fat":49,
        "fiber":0.5,
        "protein":12.5,
        "sodium":186,
        "water":0
    },
    "goals":{
        "calories": 2286
    }
}

Thanks in advance,

Tim

Really what I do typically is just bring in all the data I think I'll ever need into the SQL database. Maximize ease of import and future flexibility.

If you don't want to show "milk" twice --- deal with that downstream. A secondary table or view that has these calculations or additional logic applied.

Typically with JSON-->SQL, the only major problem is that JSON can contain dynamic fields, whereas SQL tables have fixed fields.

It doesn't appear that your JSON contains much dynamic structure anyway (a field like tags, or other stuff like that).

So you really you can just do a 1:1 translation of all the JSON fields you care about into SQL columns, and make one row per entry. I would do this route personally -- you can call it the staging table. Apply calculation or visual logic afterward into a separate table, view, or logic.

If there are dynamic fields, that only pop up in relatively unstructured ways on certain items, create a field or two that simply contains a full brain-dump of these fields and deal with them later in SQL as well. That's been my experience with JSON to SQL.