SQL Server backup very large compared to table size I have a database with 31 tables and my largest table has about 12 columns and 750,000 records. I checked the size of all my tables using this query:
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC, t.Name

and it says that my largest table has 147.45 MB of data:

When I total all of the space used from the tables it is not even 200 MB. However, when I create a backup file it is 4.5 GB.
I am not sure what is going on.

It is interesting that shrinking reduced the backup size. Backup doesn't include unused extents. And such a massive reduction in backup size can't be explained by "lots of half-full pages" alone.
This was apparently some very special situation, in the sense that a shrink did indeed reduce the backup size to that level. One theory I have is that there are heap tables that had lots and lots of unused pages (but still being considered as used from the backup's perspective). I.e., you have some table which is being heavily inserted into and deleted from, and the delete pattern causes the free space to never being deallocated, so to speak.
If you want to aid in the value for this thread for future readers, you can run something like below to see if you have heap tables. We can't say now if potential heap tables has vast amount of free space since the shrink was performed already, but at least we would know if you have heap tables.
SELECT OBJECT_SCHEMA_NAME(OBJECT_ID), OBJECT_NAME(object_id), type_desc
FROM sys.indexes
WHERE type_desc = 'HEAP'
AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0

Perhaps LOB data not being compacted/deallocated could also explain this, but that is just me thinking out loud...


SSIS unpivot returns different results when run via a SQL agent job I hope someone can help me. I have an SSIS package which grabs data from an excel (.xls) file, unpivots it from a rubbish format into a 'proper' format, transfers it to a SQL server 2008 R2 db, and runs some stored procedures. I thought I had it working but testing has thrown up some interesting results. The Excel file has years as separate columns, from 2000 - 2050, plus a whole bunch of other fields.

The really annoying thing is this: when I kick off the SSIS package manually it works as expected. If I run it in BIDS debug mode it works as expected. However if I run it via my SQL agent job it omits all years after 2027. These records are simply not appended. This is causing me some frustration!

Some troubleshooting using the logging functionality has led me to the following information:

OnWarning,EDI-SQLINTDEV01,WOODMAC\ServicePFF,DataFlow Append Temp Data,
{18E3EDB7-DBFE-49E3-8187-7B5CFA4684F6},{48A81BE7-AEDB-4313-A88D-537C5F14693F},
14/08/2014 15:56:24,14/08/2014 15:56:24,-2147192632,0x,
The external columns for component "Excel - Gen Capacity" (2121) are out of
synchronization with the data source columns. The external column "2028" needs to be updated.
The external column "2029" needs to be updated.
The external column "2030" needs to be updated.

(etc. all the way to 2050)

If I run the package manually these entries are not included in the output log.

Can anyone help me?

It's probably relevant to mention that the filepath of the excel file is stored in a user variable, and is different every time, and is retrieved from a SQL query.

EDIT:

I've made a bit more progress with troubleshooting. I know why it starts failing at 2028. It's because there were some empty cells beneath the column headers and 2028 was the first year where there were 8 or more of these. So, bizarrely, if there are 7 or fewer null rows beneath the column header, it will simply ignore the nulls and append all the data for that column. However if there are 8 or more nulls, it seems to ignore the entire column regardless of how much data there is further down. No records are unpivoted where there are 8 or more empty cells below the column heading!!!

How do I get round this? There must be a property setting somewhere.

I think I've fixed it. Thought I'd share the answer in case anyone else comes across this exact same issue. All it took was the addition of IMEX=1 to the Excel connection string.

I stumbled across this post which explains more: Why SSIS always gets Excel data types wrong, and how to fix it!


Executing generated script from output I created a script that generates and outputs the required ALTER SYSTEM KILL SESSION commands that I need to execute.

main script: (without all the conditions, for better use here in SE)

SELECT 'ALTER SYSTEM KILL SESSION '||CHR(39)|| ao.SID ||',' || ao.SERIAL# ||CHR(39) || ' IMMEDIATE;'
FROM v$session ao where (length(trim(ao.username)) = 5
                and upper(substr(trim(ao.username),1,1)) = 'E'

output example:

ALTER SYSTEM KILL SESSION '418,30413' IMMEDIATE;
ALTER SYSTEM KILL SESSION '755,19371' IMMEDIATE;
ALTER SYSTEM KILL SESSION '809,6715' IMMEDIATE;

How can I execute the generated ALTERs ?

I'm using Oracle SQL Developer 4.0.0.13

You'd copy and paste the commands and then run them.  If you want to do this in a single step, you can write a small PL/SQL block

BEGIN
  FOR s IN (SELECT *
              FROM v$session ao
             where (length(trim(ao.username)) = 5
               and upper(substr(trim(ao.username),1,1)) = 'E')
  LOOP
    EXECUTE IMMEDIATE  'ALTER SYSTEM KILL SESSION '||
                         CHR(39)|| s.SID ||',' || s.SERIAL# ||CHR(39) ||
                         ' IMMEDIATE';
  END LOOP;
END;


How to improve this query so it'll run quicker I've got this query:

    SELECT m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType
    FROM     macs m, network_devices n, vlans v, ports p
    WHERE    n.nd_ip_address = m.ipAddr
    AND      m.vlanID        = v.vlan_id
    AND      p.vlanID        = m.vlanID
    GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name, p.interfaceDescription, p.interfacePortType
    ORDER BY m.vlanID

Each of these tables have approximately 50.000 records. The query is taking north of 30min. Can I improve it to make it more efficient?

EDIT: Table definitions:
macs table
ipAddr, vlanID, macAddress, interfaceName

network_devices table
network_device_id, nd_location, nd_name, nd_ip_address, etc

vlans table
vlan_id, vlan_name

ports table
interfaceName, interfacePortType, nodeIP, interfaceDescription

I've limited to mostly what's required from the query.

There's no indexes configured atm nor execution plan as this is not an application per se. This is just a basic script to be run occasionally for internal purposes only and the data will become obsolete within a few days, so we just need to query temporarily to get some answer while we upgrade our network.

Thanks

You can try this:

SELECT m.vlanID,
        v.vlan_name,
        m.interfaceName,
        count(1) as mac,
         n.nd_name,
         p.interfaceDescription,
         p.interfacePortType
    FROM  macs m
    INNER JOIN network_devices n
        ON n.nd_ip_address = m.ipAddr
    INNER JOIN vlans v
        ON m.vlanID = v.vlan_id
    INNER JOIN ports p
        ON p.vlanID = m.vlanID
    GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name, p.interfaceDescription, p.interfacePortType

A few things about the changes:

The cartesian join to inner join switch is purely cosmetic, unless the mysql implementation you're using doesn't support the cartesian inner join syntax, which would be very odd.
Removing the Order By clause should provide a performance bump, but depending on the way you're using the results may not be useful.
If m.macAddress is sometimes null, count(1) != count(m.macAddress)

These small optimizations stated, there is not a whole lot to optimize about this query itself. There is something about the context of the query that is causing the exceptional runtime.

Indexes on n.nd_ip_address, m.ipAddr,m.vlanID, v.vlan_id, and p.vlanID
would certainly help substantially. If you have to run the query multiple times, it would be well worth the time invested in creating the indexes even on temporary data.

It's even likely that re-creating the indexes every time the query runs would cause the overall runtime to be much lower.


MySQL: Join one Row in a table with two Rows in other table In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations):

  `users`                          `user_login`
+--------------+            +-----------------------+
| column_name  |            | column_name           |
+--------------+            +-----------------------+
| user_id      |<---|       | user_login_id         |
| first_name   |    |-------| user_id               |
| email_id     |            | user_name             |<---|
+--------------+            | created_by            |----|
                            +-----------------------+

Table: user_login with recursive association, such that  an employee's username can be created by his boss  in some web-based application . A self relationship of something like (created_by (1)- user_name (∞)).
Table: users contains personal information for each user(*or say for each username in user_login table*)

All fields are varchar(64) in both tables. And user_name is has uniqueness constraint. (the actual database I am working with is enough big with more columns and many rows, but I am just putting only useful informations)

[Query]

Input to my query is user_name.

I need a query If I give user_name value it return me information from user table (fist_name and email_id) for both user and his immediate boss.

Suppose my user table is:

mysql> SELECT `user_id`, `first_name`, `email_id` FROM `users`;
+----------+------------+---------------------------+
| user_id  | first_name | email_id                  |
+----------+------------+---------------------------+
| 1        | Grijesh    | grijesh.mnit@gmail.com    |
| 8        | Sumit      | sumit@cscape.in           |
| b        | OMD        | grijesh.chauhan@cscape.in |
+----------+------------+---------------------------+
3 rows in set (0.00 sec)

And user_login is

mysql> SELECT user_login_id, user_id,  user_name , created_by FROM `user_login`;
+----------------+-----------+-----------+------------+
| user_login_id  | user_id   | user_name | created_by |
+----------------+-----------+-----------+------------+
| 13             | 1         | grijesh   | omdadmin   |
| 89             | 8         | sumit01   | grijesh    |
| bd             | b         | omdadmin  | SuperAdmin |
+----------------+-----------+-----------+------------+
3 rows in set (0.00 sec)

Then for the input user_name = 'grijesh' output details should be from user table about omd and grijesh.

For this I written a query like below (that is working fine):

mysql> SELECT  `user_login`.`user_name`,
               `users`.`first_name`,
               `users`.`email_id`
       FROM    `user_login`, `users`
       WHERE   `user_login`.`user_id` = `users`.`user_id` AND
                (`user_login`.`user_name` = 'grijesh'
                  OR
                 `user_login`.`user_name` IN ( SELECT `created_by`
                                               FROM `user_login`
                                              WHERE `user_login`.`user_name` = 'grijesh' ));

its output is like:

+-----------+------------+---------------------------+
| user_name | first_name | email_id                  |
+-----------+------------+---------------------------+
| grijesh   | Grijesh    | grijesh.mnit@gmail.com    |
| omdadmin  | OMD        | grijesh.chauhan@cscape.in |
+-----------+------------+---------------------------+
2 rows in set (0.06 sec)

[QUESTION]:

Can we have better efficient query equivalent of my above. I tried to think a solution with Joins but its hard to me for this query because for a row in user_login I need to join with two rows in user table.

I tried with join but I couldn't. I need a solution with Join instead nested query like I did.

Any help or suggestion will be great help.

Perhaps I'm misunderstanding your request, but based on the text in your question that sounds simple enough:

SELECT
    U.first_name AS "User Name",
    U.email_id AS "User E-Mail Address",
    BossUser.first_name AS "Authorizing User Name"
    BossUser.email_id AS "Authorizing User E-Mail Address"
FROM
    user_login AS UL
    INNER JOIN users AS U ON UL.user_id = U.user_id
    LEFT JOIN  user_login AS BossLogin ON U.created_by = BossLogin.user_name
    LEFT JOIN  users AS BossUser ON BossLogin.user_id = BossUser.user_id

This allows for users without a created_by; if that field is NOT NULL, you can replace the LEFT JOINs with INNER JOIN.


Always encrypted on Temporal tables Is it now possible to apply Always encrypted to some columns on existing Azure SQL database temporal tables with existing data ? I know it was not possible a few months ago thru SSMS / powershell.

At least it is now possible have encrypted columns on temporal tables:

CREATE TABLE DepartmentHistory
(
     DeptID int NOT NULL
   , DeptName varchar(50) NOT NULL
   , SysStartTime datetime2 NOT NULL
    ,[Customer_credit_card_asym] [varbinary](max) NULL
   , SysEndTime datetime2 NOT NULL
);
GO

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
   ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);
GO
CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED
   , DeptName varchar(50) NOT NULL
   , SysStartTime datetime2  NOT NULL
    ,Customer_credit_card_asym [varbinary](max) NULL
   , SysEndTime datetime2 NOT NULL
) ;

CREATE ASYMMETRIC KEY dboAsymKey AUTHORIZATION dbo
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD =N'MorilloP@zzwurd';

DECLARE @credit_card1 VARCHAR(100) = '111-1111111-1'

INSERT into Department (DeptId, DeptName, SysStartTime,
Customer_credit_card_asym, SysEndTime)
VALUES
(1, 'Perez' , '2/2/2018 00:00:00.000', EncryptByAsymKey(AsymKey_ID('dboAsymKey'),@credit_card1),
'9999-12-31 23:59:59.9999999')

ALTER TABLE dbo.Department
   ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
ALTER TABLE dbo.Department
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = ON));

A few years back Msg 13579 was received.


Microsoft SQL Server 2005 Encryption Options I'm trying to explore some encryption options on Microsoft SQL Server 2005.

I've recently got around SQL Server 2008 and was able to enable Transparent Data Encryption (TDE). Now I'm trying to figure out encryption options for SQL Server 2005.

Can someone tell me how to achieve TDE for SQL Server 2005? If that's not possible, can someone point me in the right direction? I also understand that cell level encryption must be done on the application side.

PS: The server edition is Standard Edition. I've been trying to find answers for 2005 for a few hours now but most are for SQL Server 2008.

This article on msdn indicates it's a new feature in SQL Server 2008.  To me that means it's not available in 2005.

There are a lot of resources available with step-by-step guides for other data encryption types in 2005, though.  I think it's probably beyond the scope of a question on a Q&A site, however.


SQL Server - granting permissions to an entire schema vs. object? I'm very green when it comes to the world of database permissions management in SQL Sever.

Let's keep an example simple.

Say account 'admin' is the owner of schemas A, B, and C.

There another account 'minion' that you want to have full rights (update/delete/insert/select/alter) on any object (table/view) created under schemas A, B, and C.

Is this possible? Or do you have to execute a grant statement each and every time you add a table/ view under these schemas? (seems a bit silly to me).

You can GRANT schema permissions that are effective for everything existing and everything that will exist in that schema.

Grant Schema Permissions

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: <schema> TO <user>;

Further to that, if you want to then deny permissions on a certain object within that schema, you can do.

Denying Object Permissions

DENY INSERT ON OBJECT::<schema>.<object> TO <user>;


Dual site active-active highly available database I have an application that is being run on two separate sites. Ideally they would share a database, because each application needs to see what the other is writing to the database.

However, the sites are geographically separate and each needs to be able to operate if the other one goes down.

There also needs to be high availability within each site.

Does MS SQL AlwaysOn provide this functionality?
From what I've read it seems to only support a read only secondary site.

I see that a question similar to this was already asked, but it's from before AlwaysOn was released:
Is it possible to have 2 or more databases active, and synchronized between them?

Availability Groups provide you the ability to scale out reads via secondary replicas (end user reads, like reporting workloads, as well as things like backup and DBCC). They do not offer anything new that lets you scale out writes.

Your options there are still things like merge and peer-to-peer replication, or manual sharding or distributed partitioning (where the app knows which database to write to and read from). I don't know to what extent (if at all) any of those solutions will support seamless failover. That might be better achieved with manual partitioning and Availability Groups or even mirroring - but again your app would have to understand that there is more than one database and they are distinct and separate. Availability Groups themselves don't provide anything to distribute the writes automatically.


How to convert timestamp to date & time I am currently working on bigquery(standard sql), where I have a timestamp field which I want to convert to date and time in 2 separate columns. I tried doing this select EXTRACT(DATE FROM timestamp) from table .

I tried casting timestamp to date as well, but it is giving me an error. Any suggestions would be really appreciated

Input

timestamp
1539167364779001
1542242226310005
1544881556726002

Expected output

date         time
10/10/2018   3:59:24 PM
11/15/2018   6:07:06 AM
12/15/2018   7:15:56 PM

On the MySQL side...

mysql> SELECT FROM_UNIXTIME(1539167364779001 * 1e-6);
+----------------------------------------+
| FROM_UNIXTIME(1539167364779001 * 1e-6) |
+----------------------------------------+
| 2018-10-10 03:29:24.779001             |
+----------------------------------------+
1 row in set (0.00 sec)

Notes:

Having a TS in microseconds is rather unusual; where is it coming from?
I multiplied by one-millionth instead of dividing by a million because the arithmetic rounds division to some number of decimal places.
Are there timezone issues?
That value should nicely slip into a column declared TIMESTAMP(6) or DATETIME(6).
To remove the leading zero from the hour and/or strip off the fractional sections, use the function DATE_FORMAT().  (Oh, was there an implied am/pm?)  (Oh, you wanted truncation?)


Creation of a simple field failed in MySql CREATE TABLE bdt.transactions (
`transaction no`     int UNSIGNED NOT NULL  AUTO_INCREMENT,
data                 date    ,
by                   int  NOT NULL  ,
hours                int  NOT NULL  ,
for                  int  NOT NULL  ,
`transaction type`   set('normale','corso')   NOT NULL DEFAULT 'normale' ,
activity             text  NOT NULL  ,
CONSTRAINT pk_transactions PRIMARY KEY ( `transaction no` ) ) engine=InnoDB

Error:

  You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near  'by                   int  NOT NULL  ,  hours                int
  NOT NULL  ,  for                  int  NOT NULL    at line 4

With backquotes is now OK

by is a reserved word, and so is for

You should surround reserved words with backquotes (`) if you use them as table or column names. Just to be on the safe side, surround all column names with backquotes

CREATE TABLE bdt.transactions (
`transaction no`     int UNSIGNED NOT NULL  AUTO_INCREMENT,
`data`               date    ,
`by`                 int  NOT NULL  ,
`hours`              int  NOT NULL  ,
`for`                int  NOT NULL  ,
`transaction type`   set('normale','corso')   NOT NULL DEFAULT 'normale' ,
`activity`           text  NOT NULL  ,
CONSTRAINT pk_transactions PRIMARY KEY ( `transaction no` ) ) engine=InnoDB

Give it a Try !!!


Oracle 12c Database swapping after set up huge pages I need to setup a Oracle Database 12c with 64GB RAM on Linux RedHet. I decided to use huge pages. Something must have went wrong. Altertlog:

  WARNING: Heavy swapping observed on system in last 5 mins. pct of
  memory swapped in [0.42%] pct of memory swapped out [1.82%]. Please
  make sure there is no memory pressure and the SGA and PGA are
  configured correctly. Look at DBRM trace file for more details.

Supported system pagesize(s):   PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
                               4K          Configured           10                    8192010          NONE
                            2048K          15946             16001                    0                NONE

Seems like the Database is not using the huge pages? What could be the issue?

Update:

 grep Huge /proc/meminfo
AnonHugePages:     40960 kB
HugePages_Total:   16001
HugePages_Free:    16001
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

The steps I did to setup this hugepage configuration on ReDHat 7.3 64GBRAM and the mistake. (Link from oracle base posted above provides a better Manual for reuse)
Check total RAM of OS:

grep MemTotal /proc/meminfo
MemTotal:       65809456 kB

Check Oracle Database – AMM needs to be off:
MEMORY_TARGET und MEMORY_MAX_TARGETneed to be set to zero.

SQL> select value from v$parameter where name = 'memory_target';
VALUE
---------------------------
0

Calculate SGA size, for example 32GB:

select value/1024 from v$parameter where name = 'sga_target';

VALUE/1024
----------
  32768000

Calculate Huge Pages,
for a  x86_64 Red Hat Enterprise Linux Server we can use a default 2MB hugepagesize:

SGA / Hugepagesize=Hugepage +1
  32768000/2048=16001

This need to be set in /etc/sysctl.conf as root:

vm.nr_hugepages =16001

Also we need to set the /etc/security/limits.conf. This value hard and soft memlock should be greater than the the SGA and smaller than the total RAM.

Hugepages * Hugepagesize = minimum Memlock or something like 90% of total RAM.

Setting memlock to a lower value then SGA leads to unpredictable behavior of the Oracle Database. As commented above Oracle Database will allocate memory without using the huge pages. In my case this leaded to fatal errors when using impdp. Datapump did not provide errors which made sense.

grep oracle /etc/security/limits.conf
oracle  soft    nproc   2047
oracle  hard    nproc   16384
oracle  soft    nofile  1024
oracle  hard    nofile  65536
oracle  soft    stack   10240
#oracle  soft    memlock 19857408
#oracle  hard    memlock 19857408
oracle  soft    memlock 57671680
oracle  hard    memlock 57671680

For RedHat we also need to turn off Transparent Hugepages

cat /sys/kernel/mm/redhat_transparent_hugepage/enabled
[always] madvise never

echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag

cat /sys/kernel/mm/redhat_transparent_hugepage/enabled
always madvise [never]

Reboot System and start Oracle Database check:

grep Huge /proc/meminfo
AnonHugePages:     40960 kB
HugePages_Total:   16001
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB


ERwin Data Modeler Support For the sake of not asking 5,000 questions here; are there any reliable sources of information about ERwin?

I have to use it for a course and I have been unable to find any website or documentation that is freely available.

Are there any documentation or resources available that cover basic usage of ERwin and how to apply everyday database admin concepts to it?

The vendor does offer a forum and a knowledge base of sorts for the tool.  Plus, there is also the manual.  I don't really understand what you mean by 'everyday database admin concepts' with respect to a modelling tool, though.  Could you elaborate on that?

However, asking specific questions on data modelling tools is certainly on-topic for DBA.SE.  If you get to 'How do I do xxx in Erwin?' type of questions then it's fairly likely someone here is familiar with Erwin and can answer the questions.


Substitute DISTINCT ON for query transformation (Postgresql to Redshift) I need to transform my Postgresql query to Redshift and I working with the following data, here is an excerpt:

In my final output, I would like only to keep rows with the highest rank (in green). And I was using the following query, which works to get the output, but I have to transform it into Redshift because the visualization tool is only using Redshift and Redshift doesn't recognize DISTINCT ON.
select distinct on customer_id
id_created,
customer_id,
call_start,
status_prior,
status_after,
rank() over (partition by customer_id order by call_start) as rank
order by customer_id, rank desc;

This is my workaround, that works with Redshift:
select * from (select
id_created, customer_id, call_start, status_prior, status_after,
rank() over (partition by customer_id order by call_start desc) as rank
) t
where rank=1;
It seems to work, however, the processing time is longer, because of the subquery.


How can I limit a user to only be able to run procs in a schema, which access another db? In a SQL Server 2012 SP2 Standard Edition instance I have a user in database A that needs to be able to run stored procedures in schema foo within that database. They shouldn't be able to do anything else. For example, they should not be able to SELECT directly from any view or table in either database.

The stored procedures SELECT from tables in databases A and B. I created the procedures WITH EXEC AS OWNER. Database A is owned by sa, which has the  sysadmin role and should therefore have access to both databases. I ran this:

USE [master]
GO
CREATE LOGIN [foo] WITH PASSWORD=N'foo', DEFAULT_DATABASE=[A]
GO
USE [A]
GO
CREATE USER [foo] FOR LOGIN [foo]
ALTER ROLE [db_executor] ADD MEMBER [foo];
GO
GRANT EXECUTE ON SCHEMA :: [Bar] TO [foo];

I then connected as [foo] and executed the procedure, but received the error:

  Msg 916, Level 14, State 1, Procedure GetReport, Line 10 The server
  principal "sa" is not able to access the database "A" under the
  current security context.

Setting TRUSTWORTHY ON seems to open things up much more than is acceptable. Is there another solution?

I am reluctant to post this as an answer, because I don't have a tangible example to post with code, but I pointed Mark to Erland Sommarskog's great article, Giving Permissions through Stored Procedures, which has a section on Signing Procedures with Certificates.

Cert signing seems to be the right approach for forcing data access through a procedure without opening up all kinds of other security holes, which can happen with ownership chaining, using TRUSTWORTHY, elevating the user to a higher role, or granting them direct access to the tables (and thereby giving them a route to bypassing your procedures). I will mock up an example here as time allows (won't be today, unfortunately).


How to select random row with count() Table names:

g_number  name
123       John
123       Sara
123       Michael

I want to get one RANDOM name and quantity of all names with the same g_number in one query:

SELECT name, count(*) cnt
FROM names
WHERE g_number='123'
ORDER BY rand()

But result is always the same

name    cnt
John    3

Looks like count() ignores ORDER BY rand() line.
So that's the question: is it possible to solve this in ONE query?

I think you want to do two different things in one query:

randomly choose one name out of all the ones `WHERE g_number=123
count all the rows WHERE g_number=123.

If that's what you want to do, do it with two subqueries:

SELECT
   (
     SELECT name
     FROM t
     WHERE g_number = 123
     ORDER BY rand()
     LIMIT 1
   ) AS name
   , (
     SELECT count(*)
     FROM t
     WHERE g_number = 123
   ) AS count_of_123

That will give you as a result:

|    name | count_of_123 |
|---------|--------------|
|    Sara |            3 |

or

|    name | count_of_123 |
|---------|--------------|
| Michael |            3 |

or

|    name | count_of_123 |
|---------|--------------|
|    John |            3 |

You can check it at http://sqlfiddle.com/#!9/5342d2/8/0

This is not just MySQL, it is standard SQL (except, maybe, for the name of the rand() function, that might be DBMS-dependent). The query also reflects clearly the fact that you want to do two (related but different) things. You can check also at SQLFiddle the PostgreSQL version, and the SQLite one.

Within the realm of MySQL, you can use one non-standard query that will also work (and which, performance-wise, is probably better):

SELECT
   name, count(*) AS count_of_123
FROM
   (
     SELECT name
     FROM t
     WHERE g_number = 123
     ORDER BY rand()
   ) AS q0 ;

I think this is closer to what the original poster had in mind. Even so, I wouldn't rely on it. This is very far from standard SQL. MySQL might decide to change how this is implementd and behaves in the future. Also, IMHO, it is less clear what the intent of the query is.

Check it at http://sqlfiddle.com/#!9/5342d2/10/0


How do I load them as varbinary(MAX) format into SQL Database? There are 1000 picture that are jpeg format.How do I load them as varbinary(MAX) format into SQL Database?But at the same time I want the bulk insert functionality.

Yes, use OPENROWSET with BULK. You need a format file though.

Assuming you want to attach blobs to existing records, something like:

INSERT SomeTable (id, blob)
select
    X.SomeID, B.Blob
from
    SomeKeyTable X
    JOIN
    OPENROWSET (
             BULK 'c:\myfile.txt',
             FORMATFILE = 'c:\myfileformat.txt'
    ) B ON X.AKey  = B.AKey


Referencing database programmatically via T-SQL I am writing a stored procedure that takes a database name as an argument and returns a table of that database's indexes and their fragmentation level.  This stored procedure will live in our DBA database (the DB that contains tables the DBAs use for monitoring and optimizing things).  The systems in question are all SQL Server 2008 R2 if that makes a difference.

I have the basic query worked out, but am stuck on trying to provide the indexes' actual names.  To the best of my knowledge, that information is contained in each individual's sys.indexes view.  My specific problem is trying to reference that view programmatically from another database's stored procedure.

To illustrate, this is the portion of the query at issue:

FROM sys.dm_db_index_physical_stats(@db_id,NULL,NULL,NULL,NULL) p
INNER JOIN sys.indexes b ON p.[object_id] = b.[object_id]
    AND p.index_id = b.index_id
    AND b.index_id != 0

The query works fine when executed from the database identified by @db_id, because it is using the proper sys.indexes view.  If I try to call this from the DBA database, however, it all comes up null, as the sys.indexes view is for the wrong database.

In more general terms, I need to be able to do something like this:

DECLARE @db_name NVARCHAR(255) = 'my_database';
SELECT * FROM @db_name + '.sys.indexes';

or

USE @db_name;

I have tried switching databases or referencing other databases using combinations of string concatenation and OBJECT_NAME/OBJECT_ID/DB_ID functions and nothing seems to work.  I'd appreciate any ideas the community might have, but suspect I will have to retool this stored procedure to reside in each individual database.

Thanks in advance for any suggestions.

Dynamic SQL comes in handy for these types of administrative tasks.  Here is a snippet from a stored procedure I wrote that not only gets the defragmentation levels, but also generates the code to do the defragmentation:

select @SQL =
'
select getdate(),
       ''' + @@ServerName + ''',
       ''' + @DatabaseName + ''',
       so.Name,
       si.Name,
       db_id(''' + @DatabaseName + '''),
       ips.object_id,
       ips.index_id,
       ips.index_type_desc,
       ips.alloc_unit_type_desc,
       ips.index_depth,
       ips.avg_fragmentation_in_percent,
       ips.fragment_count,
       avg_fragment_size_in_pages,
       ips.page_count,
       ips.record_count,
       case
         when ips.index_id = 0 then ''alter table [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
         else ''alter index '' + si.name + '' on [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
       end
  from sys.dm_db_index_physical_stats(db_id(''' + @DatabaseName + '''),null,null,null, ''' + @SampleMode + ''') ips
  join [' + @DatabaseName + '].sys.objects so  on so.object_id = ips.object_id
  join [' + @DatabaseName + '].sys.schemas ss  on ss.schema_id = so.schema_id
  join [' + @DatabaseName + '].sys.indexes si  on si.object_id = ips.object_id
                      and si.index_id  = ips.index_id
order by so.Name, ips.index_id
'

exec (@SQL)


PostgreSQL Size Quota on Table or Schema How do one limit the size of a PostgreSQL table? (or schema)

Limit in either bytes or rows would be OK.

I'm starting to think that there's no easy and obvious solution.

I have a lot of identical tables in multiple schemas (my take on multi-tenancy) and I would like to restrict each schema to a certain max size.

I have yet to find anything in Postgres that allows me to turn on any kind of quota. So I suppose you need to build this functionality yourself.

The naive 'solution' would be to do something like:

insert only if select count(*) < max quota.

But that does not feel right.

Anyone with better solutions in mind?

You can create a trigger that checks the number of records in your destination table when an INSERT is used.

The trigger function would look like this:

CREATE OR REPLACE FUNCTION check_number_of_row()
RETURNS TRIGGER AS
$body$
BEGIN
    -- replace 100 by the number of rows you want
    IF (SELECT count(*) FROM your_table) > 100
    THEN
        RAISE EXCEPTION 'INSERT statement exceeding maximum number of rows for this table'
    END IF;
END;
$body$
LANGUAGE plpgsql;

And trigger will be like this : -

CREATE TRIGGER tr_check_number_of_row
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE PROCEDURE check_number_of_row();

There is no other way i think to set table size in postgresql but u can restrict row limit on table


Cloning a SQL Server Virtual Machine with TDE When a virtual machine with a SQL Server database that uses TDE is copied or cloned, will the database be readable on the new instance?

Yes, cloning or copying a VM should not make a database using TDE unreadable or unavailable on the new cloned VM.
If you check the Move a TDE Protected Database to Another SQL Server doc, which by the way is not specifically what you're trying to do, but lists the restrictions that could cause a TDE database unable to be decrypted on a different server, you'll see that none of the Limitations and Restrictions rules are broken when copying or cloning a VM.
Every "piece" needed to decrypt the database is going to be copied along with the VM and everything shall be at the same place within the new VM.


Download SQL Server Profiler for SQL Server Management Studio How can I profile a SQL Server 2008 database to see code that's being executed on a particular database?  I remember using the SQL Server profiler, but I don't see it in SQL Server Management Studio after downloading SQL Server 2008 R2 Express.  Where can I download that tool and install it?  Do I need the full version of SQL Server 2008 in order to see this option?

SQL Profiler does not come with SQL Server Express 2008 R2*.  You can get it with the installation media for Datacenter, Enterprise, Standard, Web, Developer, or Workgroup licenses.

If you have the installation media, you can get Profiler that way.  Otherwise, Developer Edition is available for $50.

There are some other tools available, and you could even create your own server-side traces (for example) without Profiler.  Then, use a tool like Qure Workload Analyzer to review the trace files.  With 2008 R2, you could in addition use Extended Events to track activity.  The advantage is that both of these are less of a performance hit than running Profiler would be.

If you're looking at a point-in-time solution and can install procedures, Adam Machanic's sp_whoisactive could be something to look into.

* Profiler is available in SQL Server 2012 Express Edition SP1 onward. You need to select SSMS (Complete) in the installation options.


Insert iterated values from one table to another in OracleDB I'm pretty new in Oracle universe and I need help. I have to iterate through Users Table and insert these 3 specific rows into Task_Group Table. The hard thing to do is to check if these rows even exist (tables are just samples, I need to iterate through a table with a lot of rows), and then insert them only in rows and set Company_Number to: 22.

Please help, guys!

You can check whether the entry for the user already exists in the table and then exclude that users.

Depending on your data, you can do it with an insert, e.g.

insert into task_group (id, user_id, phone,name, company_no)
select rownum, id, phone,name, 22
  from users u
 where not exists(select 1 from task_group t where t.user_id = u.id);

you cann also loop over the data in your cursor and do commit every x Rows, but it is a very slow way to do that


Find continuous records with equal values characters I need your kind support to resolve this issue.

I have a table contains similar to the below data :

|AccountNo|remarks    |
|Q32571   |Actual Read|
|Q32571   |Locked     |
|Q32571   |Actual Read|
|Q32571   |Actual Read|
|Q32571   |Actual Read|
|Q32571   |Locked     |
|Q32571   |Actual Read|

The expected result shall give me the continuous records only with the same remarks

|AccountNo|TotCont|
|Q32571   |    3  |

Can anyone help please
thanks

As mentioned in the comments, you need to have a way to determine the order of rows, so I added an ID column to my example.  I also added another AccountNumber and mixed the data up a bit to verify that my solution would work for other accounts.  You did not specify your RDBMS.  My solution uses SQL Server.

--Set up demo data
DECLARE @T TABLE
    ([ID] int,[AccountNumber] varchar(10), [Remarks] varchar(100))
;

insert into @t(ID,AccountNumber,Remarks) values
(1,'Q32571','Actual Read'),
(2,'Q32571','Locked'),
(3,'Q11111','Locked'),
(4,'Q11111','Locked'),
(5,'Q32571','Actual Read'),
(6,'Q32571','Actual Read'),
(7,'Q32571','Actual Read'),
(8,'Q32571','Locked'),
(9,'Q32571','Actual Read'),
(10,'Q11111','Actual Read'),
(11,'Q11111','Locked')
;

--The actual query
;with _groupings as
(
select AccountNumber, Remarks, count(*) as CountConsecutive
from (select t.*,
             (row_number() over (order by AccountNumber, ID) -
              row_number() over (partition by Remarks order by AccountNumber,ID)
             ) as grp
      from @t t
     ) t
group by AccountNumber, grp, Remarks
)
select AccountNumber, Max(CountConsecutive) as CountConsecutive
from _groupings
group by AccountNumber

| AccountNumber | CountConsecutive |
|---------------|------------------|
| Q11111        | 2                |
| Q32571        | 3                |


Can mongoDB split read and write, with primary on HDD, replica sets on SSD? I have three machines, each machine both has SSH and HDD, I want to save SSD space, and in my env: less write, lots read. So does mongoDB support splitting read and write, with primary on HDD, replica sets on SSD. If so, how do I go about doing this?

You can send rs.stepDown(300) to the replicas on the SSD disks that you want as Secondary. Then they won't get chosen for the next 5 minutes. The replica on the HDD should be Primary when all the others step down.

Also make sure that you can read from your secondaries with rs.slaveOK().

In case that your primary has problems, one of your secondaries will take over. (Unless if you really don't want that, you can use {priority:0} in the member options.) Be aware that your script with these tasks, should only execute when your 'previous primary' is synced correctly again, before you return to your preferred environment. This could be automated as a cronjob or sometimes better manually if you need to figure out what happened with the Primary.

To optimize read/write:

Howto Benchmark and test limits
Split different database files by task : journal/log/data


Is there a logical size limit to enum tables I'm setting up a database to handle a FAQ page for different musical venues. The basic structure is:
id | venue_id | category | question | answer

category links to an enum table so our app can allow users to filter questions.  I am trying to decide if it makes sense to use an enum table for questions since all venues will be able to answer the same (optional) questions.
Our current design shows there could be around 200 total questions.  Obviously this isn't enough to cause any type of functional issue, but I am wondering if it is logical to have an enum table this large.  What are the things that I should consider when making this decision?
Additionally, if questions are tied to a category would it make sense to eliminate the category all together in lieu of the question enum or is better not to bog a enum table down with more columns than neccessary?

I recommend that you keep the questions in a separate table and have a FK in the answer/response table. I like my enum types to represent a well-defined and discrete concept. For example, in a order tracking system, the status of an order would be a good enum. A concept like a question does not fit that requirement. A question can have attributes (e.g is the question mandatory or optional, are the responses constrained or open-ended, etc), which in my opinion makes it something I would want to store in a table.
I can imagine a question fitting multiple categories.  If that is the case, then there should be a category table and a link table between the question table and the category table. If a question can fit only one category, then that would be a good fit for an enum column in the question table.


Viewing how much transaction log is used in Simple Recovery Model We have a DW database in Simple Recovery Model. Every night, we load in data from different sources and run a few procs. I'd like to see how much the transaction log is actually used during this nightly load that takes hours to run.

Currently, the log has ballooned to over 50GB due to a one-time cleanup query we ran recently. The used space on the log is near 0% due to Simple Recovery Model. We'd like to shrink the log file down to a more reasonable size. I suspect that the nightly load only uses 5GB but is there a way to actually find this out? I thought about polling the database every minute or five and capturing the space used and unused in a logging table for a couple nights. Is there a better way?

Seems like an appropriate way to do it.

Create a logging table:

CREATE TABLE dbo.LogSpace
(
  dt DATETIME NOT NULL DEFAULT SYSDATETIME(),
  dbname SYSNAME,
  log_size_mb DECIMAL(22,7),
  space_used_percent DECIMAL(8,5),
  [status] BIT
);

Do this before and after your load:

INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status])
EXEC sp_executesql N'DBCC SQLPERF(LogSpace) WITH NO_INFOMSGS;';

Optionally, remove any rows not related to this specific database:

DELETE dbo.LogSpace WHERE dbname <> N'yourdb';

Then you can compare the before and after size/space used for any given date, or for all dates you have collected.

;WITH x AS
(
  SELECT dbname, dt,
  duration = DATEDIFF(SECOND, LAG(dt) OVER
    (PARTITION BY dbname ORDER BY dt), dt),
  [current] = space_used_percent,
  previous = LAG(space_used_percent) OVER
    (PARTITION BY dbname ORDER BY dt),
  rn = ROW_NUMBER() OVER
    (PARTITION BY dbname ORDER BY dt),
  log_size_mb
 FROM dbo.LogSpace
)
SELECT * FROM x WHERE rn % 2 = 0;

Keep in mind that checkpoints that happen during your process can actually make log space be re-used; I remember doing some performance testing recently and after certain operations the space_used_percent actually went down. So you may want to take the max observed over a few days (and maybe run it more often - in which case you want a slightly different query, that doesn't assume pairs of consecutive rows are related to any specific activity), rather than just relying on how it ended up after the load.

Also make sure that the autogrow settings for the log file are reasonable - you don't want 1MB or 10%, but you don't want 10GB, either. Since an autogrow event for a log file will (a) make all transactions wait and (b) does not benefit from instant file initialization, you want a good balance between how many times the log file has to grow during an abnormal operation like your data cleanup, and how long it takes any individual growth event to happen. If that event was recent enough, you can review these events in the default trace to see how long it took then.


SQL Database, how to set up users saving favorite songs I'm setting up a web page and database for SQL practice where a bunch of songs will be saved in one database table, and there will be a table of 'users' (all just be testing however...) of the site. I wanted to make a feature of allowing users to build a list of songs they like from those in the database. But I'm unsure how best to keep track of the liked songs of each user.

As far as I know there isn't an array like attribute to add to the users table to hold their linked songs list so the only things I can think to do are:

Create a table for each user upon account registration to hold their liked songs.
Create a mass table holding all songs liked by users, with an attribute of the liking user and then to display their liked songs, pull all from that table that have their name.

Are either of these sufficient? Is there some better way to this?

I would pull down MusicBrainz's recording table and simply link it to your users with either a m2m table, or as an array of their favorite recordings (1:n). Depending on whether or not you're querying on the data or just displaying it for the user.


Why can it take up to 30 seconds to create a simple CCI rowgroup? I was working on a demo involving CCIs when I noticed that some of my inserts were taking longer than expected. Table definitions to reproduce:

DROP TABLE IF EXISTS dbo.STG_1048576;
CREATE TABLE dbo.STG_1048576 (ID BIGINT NOT NULL);
INSERT INTO dbo.STG_1048576
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

For the tests I'm inserting all 1048576 rows from the staging table. That's enough to fill exactly one compressed rowgroup as long as it doesn't get trimmed for some reason.

If I insert all of the integers mod 17000 it takes less than a second:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 17000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

  SQL Server Execution Times: CPU time = 359 ms,  elapsed time = 364 ms.

However, if I insert the same integers mod 16000 it sometimes takes over 30 seconds:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 16000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

  SQL Server Execution Times: CPU time = 32062 ms,  elapsed time = 32511 ms.

This is a repeatable test that has been done on multiple machines. There seems to be a clear pattern in elapsed time as the mod value changes:

MOD_NUM TIME_IN_MS
1000    2036
2000    3857
3000    5463
4000    6930
5000    8414
6000    10270
7000    12350
8000    13936
9000    17470
10000   19946
11000   21373
12000   24950
13000   28677
14000   31030
15000   34040
16000   37000
17000   563
18000   583
19000   576
20000   584

If you want to run tests yourself feel free to modify the test code that I wrote here.

I couldn't find anything interesting in sys.dm_os_wait_stats for the mod 16000 insert:

╔════════════════════════════════════╦══════════════╗
║             wait_type              ║ diff_wait_ms ║
╠════════════════════════════════════╬══════════════╣
║ XE_DISPATCHER_WAIT                 ║       164406 ║
║ QDS_PERSIST_TASK_MAIN_LOOP_SLEEP   ║       120002 ║
║ LAZYWRITER_SLEEP                   ║        97718 ║
║ LOGMGR_QUEUE                       ║        97298 ║
║ DIRTY_PAGE_POLL                    ║        97254 ║
║ HADR_FILESTREAM_IOMGR_IOCOMPLETION ║        97111 ║
║ SQLTRACE_INCREMENTAL_FLUSH_SLEEP   ║        96008 ║
║ REQUEST_FOR_DEADLOCK_SEARCH        ║        95001 ║
║ XE_TIMER_EVENT                     ║        94689 ║
║ SLEEP_TASK                         ║        48308 ║
║ BROKER_TO_FLUSH                    ║        48264 ║
║ CHECKPOINT_QUEUE                   ║        35589 ║
║ SOS_SCHEDULER_YIELD                ║           13 ║
╚════════════════════════════════════╩══════════════╝

Why does the insert for ID % 16000 take so much longer than the insert for ID % 17000?

In many respects, this is expected behaviour. Any set of compression routines will have widely ranging performance depending on input data distribution. We expect to trade data loading speed for storage size and runtime querying performance.

There is a definite limit to how detailed an answer you're going to get here, since VertiPaq is a proprietary implementation, and the details are a closely-guarded secret. Even so, we do know that VertiPaq contains routines for:

Value encoding (scaling and/or translating values to fit in a small number of bits)
Dictionary encoding (integer references to unique values)
Run Length Encoding (storing runs of repeated values as [value, count] pairs)
Bit-packing (storing the stream in as few bits as possible)

Typically, data will be value or dictionary encoded, then RLE or bit-packing will be applied (or a hybrid of RLE and bit-packing used on different subsections of the segment data). The process of deciding which techniques to apply can involve generating a histogram to help determine how maximum bit savings can be achieved.

Capturing the slow case with Windows Performance Recorder and analyzing the result with Windows Performance Analyzer, we can see that the vast majority of the execution time is consumed in looking at the clustering of the data, building histograms, and deciding how to partition it for best savings:

The most expensive processing occurs for values that appear at least 64 times in the segment. This is a heuristic to determine when pure RLE is likely to be beneficial. The faster cases result in impure storage e.g. a bit-packed representation, with a larger final storage size. In the hybrid cases, values with 64 or more repetitions are RLE encoded, and the remainder are bit-packed.

The longest duration occurs when the maximum number of distinct values with 64 repetitions appear in the largest possible segment i.e. 1,048,576 rows with 16,384 sets of values with 64 entries each. Inspection of the code reveals a hard-coded time limit for the expensive processing. This can be configured in other VertiPaq implementations e.g. SSAS, but not in SQL Server as far as I can tell.

Some insight into the final storage arrangement can be acquired using the undocumented DBCC CSINDEX command. This shows the RLE header and array entries, any bookmarks into the RLE data, and a brief summary of the bit-pack data (if any).

For more information, see:

The VertiPaq Engine in DAX by Alberto Ferrari and Marco Russo
Microsoft Patent WO2015038442: Processing datasets with a DBMS engine
Microsoft Patent WO2010039898: Efficient large-scale filtering and/or sorting for querying of column based data encoded structures


Does range improve match operation and which runs first, range or match I am using MySQL version 5.6.10

I have a very very large table large_table and a relatively small table small_table

The small_table has only the records of the date 2019-11-20

There is a column I want to update in small_table whose entry is in large_table. I have a unique key using which I would find the row and then get the column from large_table and update it in `small_table.

I have run the following query

update
    small_table, large_table
set
    small_table.start_time = large_table.start_time
where
    small_table.uuid = large_table.uuid
;

The above query is taking very very long. I was wondering if I can improve the query by specifying the range of date in large_table like

update
    small_table, large_table
set
    small_table.start_time = large_table.start_time
where
    small_table.uuid = large_table.uuid and
    large_table.time >= '2019-11-20' and large_table.time < '2019-11-21'
;

Would the above query improve time of execution ?

Basically what I want to know is would it make a difference to specify range and match both or the range does not matter when the match is specified.

Also, as @danblack mentioned in the comment, I would like to add one more question.

I could tell you if I have indexing on small_table or large_table but I do know what impact it would have in a different case.

So, I would also like to know how would result be affected if

large_table had indexing, but small_table didn't
small_table had indexing, but large_table didn't
Both small_table and large_table had indexing
Both small_table and large_table didn't have indexing

As you indicated by the small_table only containing one range, adding the date adds a hint that could be used in some cases. As worst it will just add a quick condition to an already retrieved row.

'had indexing' really doesn't help, it matters what the index begins with. Indexes can have multiple parts and the order matters significantly. An index with a 'prefix of A' could include A as the entire index.

The indexing on the small_table is largely a second consideration as the entire table needs to be scanned.

A uuid as a prefix on an index (or primary) key should be considered the minimium on the large_table. In this case the small table will be scanned and each uuid will be looked up in big table.

If a time is a prefix of a index (or primary key) of the large_table, date criteria in the index be searched and matched against the small_table. It depends here if the optimizer thinks the date range in the table is bigger or small than the small_table. If there is no uuid indexes this is the only optimal solution.

If there are no indexes, much slowness will happen, i.e. the large_table will be entirely scanned for each element in small_table

To check what will be used check:

EXPLAIN
SELECT small_table.uuid, small_table.time, large_table.uuid, large_table.time
FROM small_table, large_table
WHERE
    small_table.uuid = large_table.uuid
    AND large_table.time >= '2019-11-20' and large_table.time < '2019-11-21'


JSON in MySQL : get data from an object within a range of keys What I have:

A mysql table, say table1
table1 contains two columns viz. id and data
id is int but the twist is data is JSON type and its keys are comparable
the table1 contains only one row (for the sake of this question)

table1

id
data

1
{'1': 'content1', '2': 'content2',    '3':'content3',.......,'10000':'content10000' }

What I want to have:
I want a query such that it returns me key-value pairs within a range of keys, say, 100 to 200.
What I'm getting on searching on internet:
Everywhere I got only the answers where one can get rows which have the values within a range, but here the case is I want values of keys within a range.

Possible realization:
WITH RECURSIVE cte AS ( SELECT @from num
                        UNION ALL
                        SELECT num+1 FROM cte WHERE num < @till )
SELECT CONCAT('key', num) `key`,
       JSON_EXTRACT(test.val, CONCAT('$.key', num)) `value`
FROM test
CROSS JOIN cte
HAVING `value` IS NOT NULL;

fiddle


Getting error message when opening report server home page ( found 163 but 162 needed) I just uninstalled SQL Server reporting services native and installed back again from same media which we used 3 years ago. This version is free one (development use only). I configured the report server but am now getting following message.

  The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '163'. The expected version is '162'. (rsInvalidReportServerDatabase)

Here is the current version of SQL Server info.

Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Please help how to resolve this.

Talked through with the Asker in chat but in essence:

Database Engine (SP3) and Reporting Services (RTM) are now installed on the same server but experiencing incompatibility since the "naïve" Reporting Services (installed from a RTM media - overwriting the now-uninstalled updated version) is seeing a more recent (updated) Database Engine ReportServerDB version than it can handle. Database Engine has been upgraded since the original install (via Windows Update or whatever applies to that environment) to SP3 but RS has now been reset to "naïve" RTM version.

Update the installed Reporting Services with SP3 and any CUs to match the installed Database Engine version, restart and re-try. There may be some downtime for users depending on whether it requires a restart so advise/schedule that accordingly.


Writing Conditions in Left join sql I want to know why these queries give different results

    Select *  FROM Table1 T1
    Left Join MyTable MT
    ON MT.studentId = T1.StudentId
    AND T1.class = 7

    Select *  FROM Table1 T1
    Left Join MyTable MT
    ON MT.studentId = T1.StudentId
    WHERE T1.class = 7

The where clause is evaluated after the join. A left join B means all tuples  from A together with the matching tuples from B, if no tuples from B match null is the result. Given:

create table t1 (studentid int not null, class int not null);
create table t2 (studentid int not null);
insert into t1 values (1,1),(2,2);
insert into t2 values (1);

select * from t1 left join t2 on t1.studentid = t2.studentid;

STUDENTID   CLASS       STUDENTID
----------- ----------- -----------
      1           1           1
      2           2           -

i.e. there is no row in t2 that matches studentid 1 so a null is the result. For:

select * from t1
left join t2
    on t1.studentid = t2.studentid
   and t1.class = 1;

Nothing changes. For:

select * from t1
left join t2
    on t1.studentid = t2.studentid
where t1.class = 1;

on the other hand, the where clause is evaluated after the join so t1.studentid  2 is removed from the result.


How to dump a postgreSQL/postgis sample database filtered by a spatial extent? My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent.

My first idea was to select only the features in my spatial extent and then extract these but I'm not sure I can do it without losing some information from the data model.

The second idea is to import only the data that I want in a new db, filtered with spatial extent (quite long).

The third idea is to copy completely the db and then filter the undesired features.

Which idea is the best? Is there a 4th idea that could be easier/quicker? The data model is quite complex : https://github.com/qwat/qwat-data-model

Might not be the most elegant solution, but you can use the pg_dump utility to copy the DB and then overwrite the data in the spatial tables of the new, subset DB using the ogr2ogr utility. Run pg_restore and then ogr2ogr with your clipping method of choice. To overwrite the spatial data so that it is constrained to the extent of a shapefile, for example, run something like:

ogr2ogr -overwrite -update -clipsrc clip_shape.shp PG:"host=subsethost user=subsetloginname dbname=subsetdb password=subsetpassword" PG:"host=originalhost user=originalloginname dbname=originaldb password=originalpassword"

Check pg_dump and ogr2ogr documentation (linked) for details on the optional switches. Combining the two utilities should get you squared away.


Oracle custom aggregate Year    Value
2012     10
2013     20
2014      0

I need 10 * 20 * 0 for multiple rows.
If I use this function  EXP (SUM (LN (Value))), I get an ORA-01428  error:

  argument '0' is out of range.

I have seen How do I create a user-defined aggregate function? but that is for MySQL, not Oracle and my requirement is to include zeroes.

In Oracle you can have your own aggregation function. To write one, we need to implement an interface which looks like this:

create /*or replace*/ type mult_agg_type as object (
    totalAggValue    number,
    static function ODCIAggregateInitialize(sctx IN OUT mult_agg_type) return number,
    member function ODCIAggregateIterate(self IN OUT mult_agg_type, value IN number) return number,
    member function ODCIAggregateTerminate(self IN mult_agg_type, returnValue OUT number, flags IN number) return number,
    member function ODCIAggregateMerge(self IN OUT mult_agg_type, ctx2 IN mult_agg_type) return number
);

The above is just the type definition introducing the type with the required type interface amended by a field definition which stores the value aggregated so far.

This interface can be implemented as follows:

create /*or replace*/ type body mult_agg_type is

    static function ODCIAggregateInitialize(sctx IN OUT mult_agg_type) return number is
    begin
        sctx := mult_agg_type (null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self IN OUT mult_agg_type, value IN number) return number is
    begin
        if self.totalAggValue is null then
            self.totalAggValue := value;
        else
            self.totalAggValue := self.totalAggValue*value;
        end if;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self IN mult_agg_type, returnValue OUT number, flags IN number) return number is
    begin
        returnValue := self.totalAggValue;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT mult_agg_type, ctx2 IN mult_agg_type) return number is
    begin
        self.totalAggValue := self.totalAggValue*ctx2.totalAggValue;
        return ODCIConst.Success;
    end;

end;

Now that the datatype is defined completely, we need to generate a stub function which can be used as an aggregating function in a select-statement:

create /*or replace*/ function agg_product(input number) RETURN number
    PARALLEL_ENABLE AGGREGATE using mult_agg_type;

I chose the name agg_product, but feel free to choose any name you prefer or like best. As an example of how this can be used in your case, try:

select agg_product(value) as prod from your_table_name;


How can I find the Database ID of a deleted database? Where can I view/find the database ID after a database was deleted?

I work in software/hardware environment and we have locking unit (prototype) synced in our S/W with unique DB ID. In order to reset this locking unut we need to know the DB ID. Unfortunately DB was completely deleted and we have no way to continue our work.

Database was deleted about 6-7 days ago.

You could inspect the default trace to see the database id for a deleted database, assuming the database was deleted recently enough for the drop to be captured in the trace.

This T-SQL will show you the trace records:

DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path
FROM sys.traces
WHERE is_default = 1;

IF (SELECT COALESCE(OBJECT_ID('tempdb..#trctemp'), 0)) <> 0
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt

SELECT tt.DatabaseID
    , tt.DatabaseName
    , tt.StartTime
    , tt.HostName
    , tt.LoginName
    , tt.ApplicationName
FROM #trctemp tt
WHERE tt.eventclass = 47
    AND tt.objecttype = 16964
ORDER BY tt.StartTime DESC;


Insert Missing Dates from a query How can i insert missing dates from a query i created.  The result below:

Date          Frequency
2014-05-18    5
2014-05-20    7
2014-05-25    7
2014-05-27    6

I want the result to have missing dates with 0 value as shown below:

Date          Frequency
2014-05-18    5
2014-05-19    0
2014-05-20    7
2014-05-21    0
2014-05-22    0
2014-05-23    0
2014-05-24    0
2014-05-25    7
2014-05-26    0
2014-05-27    6

Please note that I have read only access to the server.

Here is an example using a calendar table (which you really should have). This example just populates 2014 but you can stuff it with as many years as you like...

CREATE TABLE dbo.Calendar(d DATE PRIMARY KEY);

INSERT dbo.Calendar(d) SELECT TOP (365)
 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, '20140101')
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' ORDER BY number;

Now the query is simple:

DECLARE @s DATE = '20140518', @e DATE = '20140527';

SELECT c.d, Frequency = COALESCE(s.Frequency,0)
  FROM dbo.Calendar AS c
  LEFT OUTER JOIN dbo.splunge AS s
  ON c.d = s.[date]
  WHERE c.d >= @s
    AND c.d < DATEADD(DAY, 1, @e);

SQLfiddle example

If you can't create a calendar table (and don't have a numbers table handy either), then you can just put it inline:

DECLARE @s DATE = '20140518', @e DATE = '20140527';

SELECT c.d, Frequency = COALESCE(s.Frequency,0)
  FROM
(
   SELECT TOP (DATEDIFF(DAY, @s, @e)+1)
 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, @s)
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' ORDER BY number
) AS c(d)
  LEFT OUTER JOIN dbo.splunge2 AS s
  ON c.d = s.[date]
  WHERE c.d >= @s
    AND c.d < DATEADD(DAY, 1, @e);

SQLfiddle example

For more on generating sets (of dates, numbers, etc) see this series:

http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3


Creating bacpac failed (DoAllowRowLocks / DoAllowPageLocks) I'm creating a bacpac file from a local database for import to Azure SQL Server, but I keep getting an error which I cannot seem to resolve.

Error SQL71564: Element Primary Key has an unsupported property DoAllowRowLocks set and is not supported
    when used as part of a data package.
Error SQL71564: Element Primary Key has an unsupported property DoAllowPageLocks set and is not supported
    when used as part of a data package.

And this error repeats itself for 50 more keys. I have been focussing on this problem for 2 days fulltime now, and I would really like some help on this issue.

I have fixed the problems using these script:

[Rebuild all indexes]

USE AdventureWorks;
declare @table_name varchar(200)
declare @SQL varchar(300)

DECLARE vendor_cursor cursor
        FOR SELECT sys.tables.name
        FROM sys.tables WHERE sys.tables.name NOT LIKE '%_dss'
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @SQL =  'ALTER INDEX ALL ON '+@table_name+' REBUILD';
 EXEC(@SQL);
 print 'INDEX Rebuild on all indexes in table '+@table_name+' is done.';
 FETCH NEXT FROM vendor_cursor  INTO @table_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
GO

I also needed to change the IsNotForReplication property.
This script turns it YES for all tables that have an identity column.

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

This script turns it NO for all tables that have an identity column.

EXEC sp_msforeachtable @command1 = ‘
declare @int int
set @int =object_id(“?”)
EXEC sys.sp_identitycolumnforreplication @int, 0′


Growing database pains Happy new year everyone.  I'm hoping for some general guidance in the following situation...
I have an application that has been running for about 10 years.  The datastore is in mysql, (now on AWS Aurora).
Some of the tables that are in one-to-many relations are starting to have a larger number of rows:
Records (~1.4million rows)
        |
        V
    (1 to many)
        |
        V
SubRecords (~10million rows)
        |
        V
    (1 to many)
        |
        V
SubSubRecords (~22million rows)

There is not a lot of actual data being stored in these rows (ie subSubRecords is about 5gb in total), and the queries I run are very straightforwards, using indexed keys with no joins.  For example...
SELECT ... FROM Records WHERE id = ?;
SELECT ... FROM SubRecords WHERE recordId = ?;
SELECT ... FROM SubSubRecords WHERE subRecordId = ?;

So far, everything continues to be highly performant.
However, I'm starting to worry about how this design will hold up over time.  While it took 10 years to get to 22 million rows in SubSubRecords, the db is growing a lot faster now.  I wouldn't be surprised to see that table climb to 100million rows over the next 5 years, which feels like a lot.  And I'm not sure at what point it will become a problem.
I realize this is a rather broad question, and is situation-dependent.  But what what types of solutions are generally recommended in these cases?

Setup partitions? (The tables use foreign keys to enforce integrity
and my understanding is that this is incompatible with partitions.)

Convert the data in subRecords and subSubRecords to a json payload
and store that directly in a json column in the main records table?
(Same amount of data, but less rows, if it matters.)

Move to an entirely different db?  (Mongo?  I know nothing about it
but have heard is better at scaling in certain situations.)

Ignore it until it becomes an issue? :D

Any suggestions / pearls of wisdom from those who have wrestled with
similar problems is welcome.  Thanks (in advance) for your help!
Addendum:
As requested, here is the CREATE TABLE syntax for the above tables...
CREATE TABLE records (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    typeId TINYINT(1) UNSIGNED NOT NULL,
    userId INT UNSIGNED NOT NULL,
    updated TIMESTAMP DEFAULT NOW() NOT NULL,
    savename VARCHAR(100) NOT NULL,
    title VARCHAR(100) NOT NULL,
    instructions TEXT NOT NULL,
    FULLTEXT ftRecords(savename, title),
    PRIMARY KEY(id),
    FOREIGN KEY(typeId) REFERENCES recordTypes(id),
    FOREIGN KEY(userId) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET=utf8;

CREATE TABLE subRecords (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    recordId INT UNSIGNED NOT NULL,
    thumbnailId INT UNSIGNED NULL,
    sortOrder SMALLINT NOT NULL,
    enabled TINYINT(1) DEFAULT 0 NOT NULL,
    title VARCHAR(100) NOT NULL,
    instructions TEXT NOT NULL,
    parameters VARCHAR(500) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(recordId) REFERENCES records(id) ON DELETE CASCADE,
    FOREIGN KEY(thumbnailId) REFERENCES thumbnails(id) ON DELETE SET NULL
) ENGINE=InnoDB CHARACTER SET=utf8;

CREATE TABLE subSubRecords (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    subRecordId INT UNSIGNED NOT NULL,
    thumbnailId INT UNSIGNED NULL,
    sortOrder SMALLINT NOT NULL,
    caption VARCHAR(200) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(subRecordId) REFERENCES subRecords(id) ON DELETE CASCADE,
    FOREIGN KEY(thumbnailId) REFERENCES thumbnails(id) ON DELETE SET NULL
) ENGINE=InnoDB CHARACTER SET=utf8;

100M rows is not scary.
Partitioning -- No.  It is unlikely to add any performance benefit.  If, however, you need to purge "old" data, there might be a use for partitioning.
If recordId is an INDEX of SubRecords -- Good.
If recordId is the first column in the PRIMARY KEY of SubRecords -- better.
Show us SHOW CREATE TABLE for further advice.
Your 3 SELECTs would run faster if you JOINed the 3 tables in a single SELECT.
Clustering
A slight improvement would be to improve the "clustering" of the rows that you fetch at the same time.  For SubRecords,
ALTER TABLE SubRecords
    DROP PRIMARY KEY(id),
    ADD PRIMARY KEY(recordId, id),
    ADD INDEX(id);

That way, when you get the several SubRecords for one Record, they will be located next to each other.  This is because the PRIMARY KEY (in InnoDB) is 'clustered' with the data.  The INDEX(id) is to keep AUTO_INCREMENT happy.
This change may not show any noticeable improvement until the dataset is bigger than RAM.
A similar thing can be done with SubSubRecords.


NOT EXISTS with two subquery fields that match 2 fields in main query Background: Two different document types in a document management system.
Both Doc Type A and Doc Type B have a Ticket #, and a Ticket Date.
What we're looking for:
Doc Type A docs that don't have a matching Doc Type B doc (NOT EXISTS) with the same Ticket # and Ticket Date.
There like are Doc Type B docs that have the same Ticket # but NOT the same Ticket Date. We want to ignore those.
Seems simple.... but I am stuck.
So far what I have is something like this:
    SELECT
       DISTINCT ki110.keyvaluechar AS "Ticket #",
       ki101.keyvaluedate AS "Ticket Date"
    FROM
       itemdata
    LEFT OUTER JOIN
       hsi.keyitem110 ki110 on ( itemdata.itemnum = ki110.itemnum )
    LEFT OUTER JOIN
       hsi.keyitem101 ki101 on ( itemdata.itemnum = ki101.itemnum )
    WHERE
       ki101.keyvaluedate BETWEEN '01-01-2021' AND '01-31-2021'
    AND
      (
        itemdata.itemtypenum  = 178
      ) -- this is Doc Type A
    AND NOT EXISTS
       (
        SELECT
           ki110.keyvaluechar,
           ki101.keyvaluedate
        FROM
           itemdata,
           keyitem110 ki110 ,
           keyitem101 ki101
        WHERE  --(itemdata.itemnum = ki110.itemnum) --Ticket #

                -- ** the problem is here  for Date: I need to say Date in Doc Type B doc is not the same as Date in Doc Type A doc using ki101.keyvaluedate)

        AND itemdata.itemtypenum = 183) -- this  is DOC Type B

You are not using EXISTS correctly. In an EXISTS, the selected column makes no difference, it is entirely ignored and does not even need a name. This is why I favour the syntax EXISTS (SELECT 1 all on one line, because effectively it is just extra syntax of the EXISTS not of the subquery.
In your example, you also need to correlate the subquery to the outer.
EDIT I think I know what you want now.
If you follow the logic, you need to exclude rows in your outer where there are matching rows in the inner with the same ticket and date.
Notes:

The LEFT JOIN doesn't make sense here as you are filtering on it's columns, it may as well be an INNER JOIN.
The best format for dates is yyyy-mm-dd as that will not change depending on language and region.
You should use half open intervals for datetime with a time component or you will get wrong results, so dateCol >= start and dateCol < dayAfterEnd. I haven't changed this as you may have a date-only column.
Use more sensible names for the alias
We can extract out the join logic into a CTE
Don't use column names that need quoting

;WITH cte AS (
    SELECT
       ticketNumber.keyvaluechar AS TicketNum,
       ticketDate.keyvaluedate AS TicketDate,
       itemdata.itemtypenum,
       itemdata.itemnum
    FROM
       itemdata
    INNER JOIN
       hsi.keyitem110 ticketNumber on ( itemdata.itemnum = ticketNumber.itemnum )
    INNER JOIN
       hsi.keyitem101 ticketDate on ( itemdata.itemnum = ki101.itemnum )
)
SELECT DISTINCT
   TicketNum, TicketDate
FROM
   cte
WHERE
   ticketDate BETWEEN '01-01-2021' AND '01-31-2021'
AND
  (
    cte.itemtypenum  = 178
  ) -- this is Doc Type A
AND NOT EXISTS (SELECT 1
    FROM
       cte cte2
    WHERE cte2.itemnum = cte.itemnum --Same Ticket #
        AND cte2.TicketDate = cte.TicketDate  -- Same dates
        AND cte2.itemtypenum = 183  -- this  is DOC Type B
)

Note also that according to this, your LEFT JOINs should probably be INNER JOIN

As I see that someone has mentioned NOT IN, I will just say: NOT IN is usually a very bad idea, because on nullable columns it gives incorrect data.
See here, for example, for an explanation why.


MySQL-test suite not included with windows mysql installer? I noticed in my MySQL folder there is no mysql-test. Why is that?

Is it possible to add it?

You must one of the following situations:

Using an old version of MySQL/Windows (Missing from MSI or Zip File)
Using a current version of MySQL/Windows (Missing from MSI or Zip File)

I have MySQL 5.6.14 for Windows installed on my Desktop at Work
C:\>cd MySQL_5.6.14

C:\MySQL_5.6.14>dir
 Volume in drive C has no label.
 Volume Serial Number is A06D-D0B3

 Directory of C:\MySQL_5.6.14

10/01/2013  02:38 PM    <DIR>          .
10/01/2013  02:38 PM    <DIR>          ..
09/30/2013  03:58 PM    <DIR>          bin
12/14/2013  07:35 PM    <DIR>          data
09/30/2013  03:58 PM    <DIR>          docs
09/30/2013  03:58 PM    <DIR>          include
09/30/2013  03:58 PM    <DIR>          lib
09/30/2013  04:02 PM    <DIR>          mysql-test
09/30/2013  04:02 PM    <DIR>          scripts
09/30/2013  04:02 PM    <DIR>          share
09/30/2013  04:02 PM    <DIR>          sql-bench
09/30/2013  04:02 PM    <DIR>          support-files
09/30/2013  04:02 PM            17,987 COPYING
10/08/2013  08:26 AM               237 my.ini
09/30/2013  04:02 PM             1,141 my-default.ini
09/30/2013  04:02 PM             2,496 README
               4 File(s)         21,861 bytes
              12 Dir(s)  1,871,352,606,720 bytes free

C:\MySQL_5.6.14>

I went into the folder mysql-test
C:\MySQL_5.6.14>cd mysql-test

C:\MySQL_5.6.14\mysql-test>dir
 Volume in drive C has no label.
 Volume Serial Number is A06D-D0B3

 Directory of C:\MySQL_5.6.14\mysql-test

09/30/2013  04:02 PM    <DIR>          .
09/30/2013  04:02 PM    <DIR>          ..
09/30/2013  03:58 PM    <DIR>          collections
09/30/2013  03:58 PM    <DIR>          extra
09/30/2013  03:58 PM    <DIR>          include
09/30/2013  03:58 PM    <DIR>          lib
09/30/2013  03:59 PM    <DIR>          r
09/30/2013  03:59 PM    <DIR>          std_data
09/30/2013  04:01 PM    <DIR>          suite
09/30/2013  04:02 PM    <DIR>          t
09/30/2013  03:58 PM            36,862 mysql-stress-test.pl
09/30/2013  03:58 PM           190,060 mysql-test-run.pl
09/30/2013  03:58 PM             1,447 purify.supp
09/30/2013  03:58 PM             2,860 README
09/30/2013  03:58 PM               655 README.gcov
09/30/2013  03:58 PM             4,600 README.stress
09/30/2013  03:58 PM            21,064 valgrind.supp
               7 File(s)        257,548 bytes
              10 Dir(s)  1,871,352,606,720 bytes free

C:\MySQL_5.6.14\mysql-test>

It's there for me. I installed this a couple of month ago using the no-install zip file.
SUGGESTION
Download the latest no-install zipfile for MySQL 5.6.15 and look for the mysql-test folder
UPDATE 2013-12-16 13:00 EST
I just downloaded the no-install zip file. I looked in it and it is in there. Please download it and extract. Of course, make sure you have Perl installed on the Windows machine.


Automatic backups for MySQL Workbench Is there a way to schedule AUTOMATIC backups for specific tables in MySQL Workbench?

MySQL Workbench is a client querying and modelling tool and a very good one at that - it's purpose is not to perform server tasks - that's the job of the server. You can perform manual backups using Workbench, but I'm assuming that's not your long term goal.

There are many ways you can perform backups. Take a look here for 10 of them. Some of the solutions are OS dependent - basically, you have more choices if you're on Linux/*nix than on Windows (which are you?). Also, it depends on whether your tables are InnoDB or MyISAM (hope it's InnoDB!).

Two (other Open Source) backup solutions not mentioned in the post above are Percona's XtraBackup which can be cron'd or Zmanda's (which was mentioned in the High Performance MySQL book by Schwartz et al.). Another solution would be MyDumper - again a Percona backed tool (except this one also copes with MyISAM tables).

If you're familiar with *nix and file systems, then maybe an LVM solution might be for you? Try here.

I would advise you to study the solutions available and do some testing and see which one best suits your requirements.


Is Postgres insert performance the same in the replica We've got a Postgres database with a table that takes heavy select, update and inserts that needs some tuning (it has too many indexes).
We have a read replica, and I'm wondering if moving all of our SELECT queries over to the replica will be a quick win while we sort out the indexes performance. (Our application can be switched over to a replica fairly easily while sorting the indexes is going to take longer).
My understanding is that pointing read operations at the replica probably won't improve performance in this case, but wanted to check my understanding.
My understanding is that:

INSERT and UPDATE operations will return once they're committed to memory (which is then written to the WAL asynchronously), so they'll be not much faster.
The replica will stream the WAL and will have to update it's own copy of the tables and indexes. So SELECT operations on the replica will have to contend with the same IO and CPU demands that those updates impose on the primary, so will gain no performance.

If our app were simply streaming inserts, then I could see a performance improvement for the inserts, but in practice since the clients are performing SELECT/INSERT together, I can't see a real performance gain here in this use case?
Is this correct, or have I missed something?

It could improve performance on your INSERT operations (and thus improve performance where those operations occur in the application) depending on how your read replica is setup and if your INSERT contention is partly due to locking caused by heavy reads against your primary database.
I'm not an expert on PostgreSQL replicas in practice, but the theory is applicable, that if your read replica is asynchronously synchronized from your primary, then any SELECT queries executed on your read replica shouldn't cause locking against your primary database (like they normally would if they were executed on the primary). It sounds like it is synchronized asynchronously if it's streaming the WAL which is being asynchronously written to. If that's the case, and if your database was experiencing heavy read lock contention, it's possible you'll see INSERT performance gains on your primary database by moving your SELECT queries to the read replica.
As far as the performance of the SELECT queries goes, I believe you're likely correct, that you won't necessarily see any performance gains since they'll still be bound by the same CPU, I/O, and write lock contention as the read replica is updated.
If it is easy enough to test, I'd say it's worth trying, if you got nothing to lose.


mysql restore from data directory I recently asked a question regarding the restoration of a pretty huge (around 300 to 400 GB) mysql database and got some nice suggestions as seen here. Now, I have backed up the data directory of mysql as well.

So, for example before reinstalling the machine the data directory is present under /mounts/mysql.

I have backed up that particular folder and I was wondering if I just restore /mounts/mysql folder back in the new machine, will it be ok? Or will there be some problem because the database is not restored using the proper mysql command?

I just use this backup THE WHOLE MYSQL FILES way to migrate a MySQL database, and it worked!

here is my steps

  1, install MySQL server on the 2nd server with exactly same
  configuration like 1st server;

  2, stop 1st and 2nd mysqld server

  3, change the 2nd mysql data folder name, which you can find in mysql
  configuration file (my.cnf):

[root@ntest1 mysql]# cat /etc/my.cnf|grep dir
#dir
datadir=/usr/local/mysql/data
innodb_data_home_dir=/usr/local/mysql/data
innodb_log_group_home_dir=/usr/local/mysql/data
target_dir=/data/backups/

so I change the data/ name to data_bak/

mv data data_bak

because my innodb data(ibdata1 ...), innodb log(ib_logfile0...), binlgo(mysql-bin.000001...) and other database files (mysql/, yourdatabase/, ...) are all in /user/local/mysql/data/, so I just need copy all this files and folders to the object server with the same location

scp -r 10.88.48.111:/usr/local/mysql/data/* /usr/local/mysql/data/

note that I use root to copy, so I need to change the roles

chown -R mysql.mysql /usr/local/mysql/data

and then MySQLd could start up in the next moment


What is the best choose for PK in Partitioned Table? I have one large table that is partitioned (table name: Trans). At the moment this table is to be created on 32 partitions. This table will contain approximately 300 million records and data older than 14 days will be deleted daily. One of the columns in this table is a reference to a table that will contain up to 5 million records (table name: Sens) and I also want it to be partitioned. I would like to ask you about:

Will it be a problem that both tables will use the same partitioning function? So the Sens table would also be distributed over 32 partitions and would be save on the same files as the Trans table. Is this a good approach ?

The Trans table has a PK based on two columns TranID (Identity (1,1)) and ParititionID. At the moment, FK to a smaller table ('Sens') is based on only one column - SenID. The smaller table also has to be partitioned. What will be the difference in the approach / efficiency / speed of operation if the PK in the Sens table will only be on the IDENTITY (1,1) column instead of the IDENTITY (1,1) column and the partition column, i.e.

ALTER TABLE [dbo].[Sen]
ADD CONSTRAINT [PK_SenID]
    PRIMARY KEY CLUSTERED ([SenID] ASC) ON [PRIMARY];

-- or

ALTER TABLE [dbo].[Sen]
ADD CONSTRAINT [PK_SenID]
    PRIMARY KEY CLUSTERED (
                              [SenID] ASC,
                              [PartitionID]
                          ) ON [psTrans]([PartitionID])

Have you ever try to have partition column which is computed ? I am thinking about choose partition according to new column which is computed base on other column in table:

CAST(HASHBYTES('MD5', [othercolumnInTable]) AS tinyint) % 32

For the first question, there's no issue with using the same function for two tables as long as the definition of the partition function doesn't ever need to change. You mentioned deleting daily data and your partition function is planned to contain 32 partitions, so I assume that you're creating one partition per day of the month. If so, I can't see a reason why you would need to merge or split partitions. With that said, given that you just have two tables involved there isn't anything wrong with creating two separate functions as well.
Whether or not the two tables should exist on the same database files depends on how you're defining your files and filegroups. Given that you're only keeping 14 days of data and you purge the rest, I assume that you aren't putting some partitions on different classes of storage. 5 million rows in the Sens table really isn't a large number, so it's likely true that there will be no issue with using the same database files with no tables. This is just a guess based on incomplete information.
For the second question, evaluate whether or not a partition-aligned index is the right fit for the table:

An index that is built on the same partition scheme as its
corresponding table. When a table and its indexes are in alignment,
SQL Server can switch partitions quickly and efficiently while
maintaining the partition structure of both the table and its indexes.
An index does not have to participate in the same named partition
function to be aligned with its base table. However, the partition
function of the index and the base table must be essentially the same,
in that:

The arguments of the partition functions have the same data type.
They define the same number of partitions.
They define the same boundary values for partitions.

My own opinion is that you shouldn't partition a table with 5 million rows unless you have a very good reason. Partitioning is a feature designed for large tables. Why do you need to partition a table with 5 million rows?
For the final question, I have worked with partitioned tables that have computed partition columns. I do not recommend it unless you have no other choice. We consistently ran into odd issues including partition elimination not working as expected.


What's the purpose of /*!*/; in mysql binlogs? The title pretty much sums up the extent of my question.

They seem to appear after every statement.

Sometimes on a new line following. At other times, at the end before the ;.

What's the point?

They are evidently place markers within the binary logs for processing internal statements according to the context of the commands issued.

When you performed mysqlbinlog against a binary log, you see it acting as a delimiter. There are also opportunities for MySQL to process certain directives native to the particular version of MySQL cranking out the binary log.

Example:

I ran the following commands:

reset master;
use test
show binary logs;
create table rolandotable (a int);
show binary logs;
insert into rolandotable values (1);
flush logs;
show binary logs;

I got this:

mysql> reset master;
Query OK, 0 rows affected (0.21 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+
1 row in set (0.01 sec)

mysql> use test
Database changed
mysql> create table rolandotable (a int);
Query OK, 0 rows affected (0.22 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       203 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> insert into rolandotable values (1);
Query OK, 1 row affected (0.05 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.18 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       439 |
| mysql-bin.000002 |       107 |
+------------------+-----------+
2 rows in set (0.00 sec)

Here comes the mysqlbinlog dump of mysql-bin.000001

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111221 14:56:13 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.12-log created 111221 14:56:13 at startup
ROLLBACK/*!*/;
BINLOG '
3TnyTg8BAAAAZwAAAGsAAAAAAAQANS41LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADdOfJOEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#111221 14:56:59 server id 1  end_log_pos 203   Query   thread_id=8     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1324497419/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C cp850 *//*!*/;
SET @@session.character_set_client=4,@@session.collation_connection=4,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table rolandotable (a int)
/*!*/;
# at 203
#111221 14:58:24 server id 1  end_log_pos 271   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1324497504/*!*/;
BEGIN
/*!*/;
# at 271
#111221 14:58:24 server id 1  end_log_pos 369   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1324497504/*!*/;
insert into rolandotable values (1)
/*!*/;
# at 369
#111221 14:58:24 server id 1  end_log_pos 396   Xid = 99
COMMIT/*!*/;
# at 396
#111221 14:58:29 server id 1  end_log_pos 439   Rotate to mysql-bin.000002  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Most lines have /*!*/ as a delimter between entries. Sometimes, a special directive needs to executed between commands, such as this tag: /*!\C cp850 *//*!*/;

That directive, \C, sets a character set with mysql client for processing multibyte charsets within binlogs. I learned that from the help command in mysql client:

mysql> \?

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>

There are still other directives, such as the first two lines:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

The first line works as of MySQL 4.0.19

The second line works as of MySQL 5.0.3

If you run mysqlbinlog and import that output to a machine running MySQL 4.x, the second line is ignored.

I also addressed this type of directive from the output of mysqldump concerning stored procedures. That way, If you import a MySQL 5.0 mysqldump into 4.x, all commands to create stored procedures are completely ignored.

This is the agenda for using /*!*/


How to replicate from Standby mode database I have set up a pipeline which brings transactional logs each hour from a FTP site, and this newly created database is in Standby mode all the time because I need to read data during an office hour.
Initially, I was thinking about leaving the restore mode in NoRecovery because I learned that Standby mode takes more time and for performance reason. But then, I realized that I cannot read data during an office hour, so I decided to set up hourly transaction log update in Standby mode all the time.
So, I will have one database which I am trying to isolate it to just pull transactional logs hourly, and then I would like to have a copy of this database so that it would not interrupt any process (of hourly logging) while I am reading data for reporting purposes.
I am trying to see if there is a way to replicate this database (which is in standby mode) so that at least I have a backup database, and I could pull data from there for reporting purposes.
Or would it be better if the database that update logging files daily from FTP site be NoRecovery mode and there is a way to replicate this database somehow? I am just thinking about a better option to set up stable database architecure.
BTW, this particular SQL Server is in an Azure VM, and  I am trying to bring in only necessary data into Azure SQL Server (Portal) eventually.

Any reason you can't just use the native Replication features of SQL Server, such as Transactional Replication?
You can real-time synchronize between your two servers, and then reporting off the Subscriber server won't affect performance of the Publisher server.
You can also look into Snapshot Replication if you want something less real-time and more scheduled on a recurring basis.