Help With MySQL Computed Columns/Auto Updating Field Based On Average I am currently building a social network type application on-top of MySQL and have run into a problem I can't wrap my head around. In my web application each user has a field called "weight" and this weight is calculated by a few different factors.

The number of connections a user has
The number of connections a users friend has

Essentially a users weight is affected by their network and the weight of each of their friends. I need to average out the weight score of a users network and then use that to update a users self weight score. I would like it to be a living thing where the weight can change at any time if the network average changes for a user (but performance wise this might not be a good idea).

I was thinking once or twice per day maybe via a scheduled trigger, a users weighting score could be calculated. My question is, how can I best implement something like this keeping in mind if I have 100,000 users and or millions in future, the performance of updating that many rows. Am I thinking about this all wrong?

Thank you in advance.

How about using a scheduled job to take a snap-shot of your users' weight stats and influencing factors into a different (could be a temp-) table, then using those to calculate the new weight (into a different column of that same table). And then update your user table with the complete set of calculated weight stats.

You'd avoid all kinds of conflicts (from updating with modified data depending on order of execution) and execution time would not be an issue either (though it should not be too bad anway, from what you described), since you could run this as a low priority background process.


db2 expired password but cannot reset Has anybody encountered this problem with a db2 server? I'm running a development database and connecting to the database gives me a password expired error. Trying to reset it will give me an invalid username/password error.

$ db2 connect to mydb user devuser using mypassword
SQL30082N  Security processing failed with reason "1" ("PASSWORD EXPIRED").
SQLSTATE=08001
$ db2 connect to mydb user devuser using mypassword new newpasswd confirm newpasswd
SQL30083N  Attempt to change password for user id "devuser" failed with
security reason "24" ("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001

Everything I've read says that I should reset the password but I can't due to the invalid username/password error.

I don't get this error when connecting using the db2 admin user.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0021804.html

  User authentication is completed by a security facility outside the Db2 database system, through an authentication security plug-in module. A default authentication security plug-in module that relies on operating-system-based authentication is included when you install the Db2 database system

If the reset password feature of the CLP is not working for you, you should reset the password directly on the operating-system at the server (or on the security system (e.g. LDAP) that Db2 has been configured to use)


How to add new columns to MySQL database retaining existing data? In implementing upgrades to an existing Python application, on a development server, one MySQL table has gained an additional column but another has gained 23. What is the best way to add these to to the production servers. There are two servers within the company but a few dozen outside who want the update.

Many thanks...

Answering for main question -

The Best way, use any kinds of database modelling software.
      Including    MySQL Workbench. Where all changes first do on schema
      diagram, than    sync with any number of database. Many of them
      prepare for You clear    SQL scrip which You can run independently. Very good tools - DBSchema.
Other possible way - use any kind of database synchronisation
software, such as:

Next programs (not only) has functional for sync structure of databases

SQLyog and
MySQL Workbench
Navicat


Import/Exporting certain columns of a table across two databases and excluding duplicate emails in mysql I have run into a problem with mysql queries:

Basically, I have two databases that both contain a table called tblclients however db1 has far more records than db2 and there are some duplicate records as well (the users that have email address in both databases are to be excluded from the operation). I intend to append db1 to db2 in a way that only certain columns get appended as long as the email address is not found.

I have done this mysql but it gives me an error. First it complains that no database is selected and even if I type use db1; before the actual statement, it generates another error:

ERROR 1064 (42000) at line 1: 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 'db1.tblclients (db1.tblclients.firstname, db1' at line 1

any though on this?

Someone told me to use mysqldump instead but I believe it could be done in a one line mysql query to be used in a bash script file.

thanks

insert into db1.tblclients (db1.tblclients.firstname,
db1.tblclients.lastname, db1.tblclients.companyname,
db1.tblclients.email,   db1.tblclients.address1,
db1.tblclients.address2,     db1.tblclients.city, db1.tblclients.state,
db1.tblclients.postcode,     db1.tblclients.country,
db1.tblclients.phonenumber, db1.tblclients.password,
db1.tblclients.authmodule, db1.tblclients.authdata,
db1.tblclients.status,
db1.tblclients.pwresetkey, db1.tblclients.pwresetexpiry,
db1.tblclients.emailoutput) values (select db2.tblclients.firstname,
db2.tblclients.lastname, db2.tblclients.companyname,
db2.tblclients.email,
db2.tblclients.address1, db2.tblclients.address2, db2.tblclients.city,
db2.tblclients.state, db2.tblclients.postcode, db2.tblclients.country,
db2.tblclients.phonenumber, db2.tblclients.password,
db2.tblclients.authmodule, db2.tblclients.authdata,
db2.tblclients.status,
db2.tblclients.pwresetkey, db2.tblclients.pwresetexpiry,
db2.tblclients.emailoutput where not exists (select db2.tblclients.email
where db2.tblclients.email=db1.tblclients.email));

Toss the db1.tblclients. in the list of columns; it is totally redundant.  And a syntax error.

A suggestion:  Make the SELECT a LEFT JOIN ... WHERE id IS NULL.

And, again, db2.tblclients. is redundant.  (But not harmful.)

(If those suggestions are not enough, show us what you have; there may be more to fix.)


How to only display a variable in PHP inside a MySQL query when it has a content or when a condition is true? How do I only display $mon or any variable when it has content? whenever i use this syntax and for example, monday must contain the number 1 and tuesday would contain number 2 and the other ones have empty content, it displays error
1064 - 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 '' at line 8
(select my_date from ( select adddate('2015-02-01', numlist.id) as `my_date`,
                 weekday(adddate('2015-02-01', numlist.id)) as day_no,
                 dayname(adddate('2015-02-01', numlist.id)) as `day_name` from
                 (SELECT n1.i + n10.i*10 + n100.i*100 AS id FROM
                  num n1 cross join num as n10 cross join num as n100) as numlist
                 where adddate('2015-02-01', numlist.id) <= '2015-02-28' and
                 weekday(adddate('2015-02-01', numlist.id)) in( $mon, $tue, $wed, $thu, $fri, $sat) ) A)

Legend:
If selected on the php form..(which is not included but i think not important for now, as it is just passing of variable) the following should contain the value.
$mon=1
$tue=2
$wed=3
$thu=4
and so on.. until sunday.
What I need is when the variable doesn't contain any value, it should not appear in the syntax. I don't know how to insert if syntax inside mysql, (or is what am i thinking possible? it does not change its color so I think its not, or am i missing any syntax, i have no luck with google right now.. :D)

You must do some work in PHP.  You have a set of numbers: find the non empty ones, build a string, and put the string into the IN() clause.  Sample code:

$a = array();
foreach (array($mon, $tues, ...) as $val)
{
    if (! empty($val)) $a[] = $val;   // add the useful values to the array
}
$str = implode(',', $a);       // turn the array into a comma-list
$sql = "SELECT ... IN($str)";  // interpolate into the query

What I failed to do in that code snippet is to validate the input -- a hacker could destroy your system via "SQL Injection":

if (! empty($val)) $a[] = "'" . addslashes($val) . "'";


Why doesn't INSERT AFTER Trigger add all results? I'm working on the exercises for db-class.org where we have to write a trigger that makes new students named 'Friendly' automatically like everyone else in their grade. That is, after the trigger runs, we should have ('Friendly', A) in the Likes table for every other Highschooler A in the same grade as 'Friendly'.

This is being done in sqlite on the following dataset. Now my trigger works, but it only adds one person that 'Friendly' likes rather than all of them (those with the same grade).

Here's my trigger:

CREATE Trigger Q1
After Insert on Highschooler
For Each Row
When New.Name = 'Friendly'
Begin
    INSERT Into Likes Values (New.ID, (
    SELECT Highschooler.ID FROM Highschooler
    WHERE Highschooler.Grade = New.Grade));
End;

I suspect there's something wrong with my INSERT query, because in isolation the SELECT returns all the IDs. So how do I make sure it inserts all the IDs with matching grades?

On most platforms your insert would throw an error if your subselect returns more than one row, rather than just insert the 'first' returned row (and 'first' is undefined without an order by clause).

What you are probably after is an insert like this:

INSERT Into Likes
SELECT New.ID, ID
FROM Highschooler
WHERE Grade = New.Grade and ID<>New.ID;


What does the Perfmon counter "Suboptimal plans/sec" actually measure? In SQL Server, what does the Perfmon counter "Workload Group Stats:Suboptimal plans/sec" measure?

It's one of the Resource Governor counters, so it's measured at the workload group (which you have whether you configure RG or not.)

I'm not asking what makes a good plan or a bad plan, but what specifically does that counter measure, and where do you go to find the exact corresponding plans? For example, I can't find anything in Extended Events that would seem to match to a query triggering the "Suboptimal plans/sec" counter.

There are two items this counter tracks:

Timeout
Memory Limit Exceeded

The values can be found in the XML plan as Max has proposed in the comments. I can confirm it has been this way since at least SQL Server 2008.

Kudos to @Max Vernon and @Joe Obbish.

The MSDN BOL Technet Docs description isn't super helpful.


question about using tokuDB or InnoDB for financial data we want to use Percona xtraDBcluster for clustering 4 nodes (Master-Master) and we have a finance project which should have a rather large database. The number of tables is not more than 40, but we have about 2 tables which store financial transaction data and the estimated amount of data in these tables is going to grow at 350,000 records per day, and we should keep them at least 10 years to be able to do various reports.

Most of the operation (98%) in these tables is Insert/read but we can separate updatable fields in a separate table.

My questions are the following:

Should I use a tokuDB storage engine for such large amounts of data or InnoDB?
What is the best solution for such large and sensitive database?

Best Regards

Ali

A few things to note:

XtraDB Cluster doesn't scale writes (horizontally), i.e. you can't increase number of writes by adding more nodes. However, 350,000 records per day is approx. 4 record per second, which is no problem at all for XtraDB Cluster (or any other replication topology), at least as long as the nodes are on the same network.
XtraDB Cluster only supports the XtraDB / InnoDB table storage engine, so TokuDB isn't an option if you want to use that particular cluster technology.
4 nodes isn't a good number. You should be using an odd number (3, 5, ...) of nodes, or an even number of nodes + one node with garbd, the Galera arbitrator.

XtraDB (which is InnoDB with a few patches from Percona) is a tried and tested technology, and XtraDB / InnoDB is the default storage engine in MySQL, Percona and MariaDB. InnoDB is being developed and supported by MySQL (owned by Oracle).

TokuDB is newer, it doesn't have as many years of usage and development. If I understand correctly, TokuDB is developed by Tokutek, now owned by Percona.

With these considerations, in a choice between these two technologies for a system for financial transaction data, I would probably go with XtraDB.


Find rows without matching second field for given constants in first field Consider an example table with two string fields FIRST_NAME and LAST_NAME.

For a given Lastname == Smith, I find to expect a row with another given Lastname == Johnson and identical Firstname.

How can Find rows for which this isn't the case, i.e. Lastname == Smith, Firstname == Julia exists, but Lastname == Johnson, Firstname == Julia does not?.

Sample Data:

FIRST_NAME, LAST_NAME
Jane, Smith
John, Smith
Blake, Smith
Jane, Johnson
Blake, Johnson

Query so far:
SELECT FIRST_NAME FROM (SELECT DISTINCT LAST_NAME, COUNT(*) AS c, FIRST_NAME FROM Table1 GROUP BY FIRST_NAME) WHERE c = 1

What I'd like to get:

John, Smith

I believe that you want the following (see fiddle here):
Create and populate table - using SQL Server 2012 as this will most closely emulate Access. I've used my own SQL style largely modelled on this.
CREATE TABLE source_table
(
  first_name VARCHAR (5) NOT NULL,
  last_name  VARCHAR (7) NOT NULL,
  CONSTRAINT f_l_uq UNIQUE (first_name, last_name)
);

and
INSERT INTO source_table
  (first_name, last_name)
VALUES
  ('Jane', 'Smith'),
  ('John', 'Smith'),
  ('Blake', 'Smith'),
  ('Jane', 'Johnson'),
  ('Blake', 'Johnson');

I've put a UNIQUE constraint on the combination of (first_name, last_name) because it makes sense - if this really is your entire table, you could make it the PRIMARY KEY - if there is/are (a) PK field(s) as part of a table with more fields, then just add this constraint.
If you do have duplicate first and last names combined (dupes), then you can just add a DISTINCT clause to your SELECT and this will give you unique values for your desired result(s) - included in the fiddle, not shown here.
then run the following SQL:
SELECT -- DISTINCT if there are dupes!
  s1.first_name,
  s1.last_name
FROM source_table s1
WHERE s1.last_name = 'Smith'
AND s1.first_name NOT IN
(
  SELECT first_name FROM source_table s2
  WHERE last_name != 'Smith'
);

Result:
first_name  last_name
      John      Smith

Et voilà - the desired result!
p.s. db<>fiddle is a better tool that SQLFiddle - more servers (see dropdown) and more functionality (see the compare link) for example!
p.p.s. welcome to the forum!


One status table for all models? lets say I have a users,posts and categories tables ..

users table would have statuses like ( active, expired .. etc )
posts table would have statuses like ( published, draft, archived .. etc )
categories would also have different statuses

Is it bad if I did my tables like this:

users table:

- id
- name
- email
- password
- status_id

posts table:

- id
- title
- desc
- status_id

categories table:

- id
- title
- status_id

status table:

- id
- name ( active, expired , published, draft, archived .. etc )

I would think so. Especially if you don't want the status published on users.. If you want to add a status to one, you're going to special case code for one of the consumers to exclude?

I would keep it simple -- two separate tables. Or if they're immutable (you won't be deleting and updating) status's and you're only using them on the one table, you can use an ENUM macro/check constraint.


Central Management Server - can it manage itself? I'm setting up a SQL Server 2012 lab on Windows 2008 R2 which has 5 SQL Servers. I registered SQL1 as my central management server and created a group under this server that includes all the servers and the CMS itself. I'm a bit confused since the documentation says the CMS cannot be a member of a group it maintains. However, I'm able to include it in the group, run multiple queries and evaluate policies successfully.

Reference: http://msdn.microsoft.com/en-us/library/bb895144.aspx see under "Related tasks"

I've seen other forum posts where users have issues registering the CMS itself but I don't have this problem at all. Has something changed?

I'm running SQL Server 2012 Enterprise SP1 and SSMS 2012.

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Here's the CMS returning queries for the whole group.

I had also previously assumed that this wasn't possible at all, until I started playing around with it.

From what I can tell, the matching is only done by the server network name that's entered.

If you attempt to add a server to the tree with the same network name, it won't allow it; however, if you use a different network name, even though it may point to the same instance, it does work.

For example, I successfully registered my local instance .\SQL2008R2DEV as a CMS, and then successfully registered (local)\SQL2008R2DEV in the tree. (Tested in 2012 Management Studio.)

From a management perspective, this may or may not be a good idea. Furthermore, if Microsoft decides to tighten down the policy, any reliance on this ability will force you to change your process.

Certainly if SQL1 would be a production instance in your case, this is a bad idea because you want to separate management duties from serving user data duties. I strongly recommend using a separate instance of SQL Server just for CMS purposes, particularly in a production environment.


How to design a database for storing phone attribute? I'm trying to capture following attributes :

Name,Company,Price,Size,Weight,Release date,Gms,No of sim,Camera details,SIM details,Screen size,Screen type,Processor and Ram details and all..

Any lead how to proceed?

Always I'm going to fetch data once entered.
Rarely updates are going to be performed.
All attributes to be fetched once only.
Number of attributes exceeds 40 and  all are tinytext.

Depending on how you will access the database, there are different options:
Local with single or multiple users:
Think first, create later and you will have a good design.
STEP 1
 1. who will consume this data? that will give you an idea where you should store your data
 2. how much storage capacity do you need? you need plan for backups and recovery
 3. how many users will access that database simultaneously? server capacity
 4. which RDBMS are you planning to use? which one is your preference

STEP 2
 1. create list of attributes you planning to capture, always think of expansion for the future. maybe you will need to add more attributes.
 2. group them in a way that is meaningful to you.
Ex:
Dimension(Width, Height, Depth, Size, Weight),
Configuration(Processor, Ram, Storage, Screen),
Features(Finger Print, Camera Specs Front and Back, etc.)

STEP3
 1. Create Database, create table(s) and define indexes, create diagrams, create relationships if need it.
 2. Configure security settings.
 3. Load testing data for each attribute.
 4. Start using it and make appropriates changes if need it.

Good luck,


create trigger hangs I have a trigger function that just copies inserted values to another table.
Then, each night I need to drop and recreate this trigger for some export operation.

--trigger function
CREATE OR REPLACE FUNCTION mev_copy() RETURNS trigger AS $mev_copy$
        BEGIN   INSERT INTO measurement_events_copy SELECT NEW.*;
            RETURN NEW;
        END;

The problem is that sometimes the trigger cannot start: CREATE TRRIGGER command never returns, and the DB seems locked-up and puts all queries in "PARSE waiting" status.  Yesterday it was like that for 6 hours until I killed the create trigger.

The problem manifests consistently when table measurement_events is being autovacuumed. At the same time there is a new pending AccessExclusiveLock request for this table.

This is the create trigger, nothing special:

--CREATE TRIGGER that hangs sometimes
CREATE TRIGGER mev_copy AFTER INSERT ON measurement_events FOR EACH ROW EXECUTE PROCEDURE mev_copy();

Postgres version is 8.4.
Any insights?

Cannot be done with PG 8.4.
It's actually on the TODO of postgres to reduce trigger requirements:

https://wiki.postgresql.org/wiki/Todo
http://www.postgresql.org/message-id/1213387611.25121.250.camel@ebony.site


How do I prevent multiple row insert? I set up a playground MySQL database for users and I'd like to prevent multiple row (large) inserts. That is, if there will be over N rows after INSERT operation, then raise an error and do not perform statement. Since INSERT may be "heavy" itself, I want to kill that query before it gets executed.

I hadn't known MySQL supports FOR EACH ROW only, but I thought about BEFORE INSERT FOR EACH STATEMENT trigger that can count rows to be inserted, sum that with row count of the table and then SET SIGNAL if sum exceedes allowed number. The problem is, I don't know how to get number of rows to be inserted, especialy when user starts INSERT INTO ... SELECT ... statement.

What other approach could I choose?

Since batch inserts and LOAD DATA are much more efficient than lots of single-row inserts, your request is backward.

Rethink the real goal, and how to express it.


Nested Dynamic Query with SP_EXECUTE_REMOTE for binding the Parameters I'm having two Azure Databases Lets say DB1(primary) and DB2(secondary).
I've Created two External Data sources for both the DB1 so that they can talk to each other

External Data Source for DB1 is External_DB2
External Data Source for DB2 is External_DB1

I'm having a Sp in my DB2.Lets say the sp is sp_DB2(@param1)
Like that another sp in my DB1.Say  sp_DB1(@param1,@outparam)
(Ps:@outparam is a output parameter)

My sp_DB2 is the Main Sp (which calls all other Sp i.e, sp_DB1).

Inside my sp_DB2 i have the code like

EXEC SP_EXECUTE_REMOTE
N'External_DB1',
N' DECLARE @param1 nvarchar(max),@outparam nvarchar(max)
EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
@param1 ,@outparam OUTPUT
SELECT @param1 AS Paramvalue,@outparam AS outputvalue'

Since sp_Execute_remote doesn't support the output param.I done like the above.But here the thing i want to bind the @param1 value from the Original Sp (sp_DB2).

If i tried like this it works,

EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE @param1 nvarchar(max)=''Testing'',@outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue'

Where i hardcoded the Value of @Param1 as Testing in the Code.

But actually the value of @Param1 should come from the original Sp which is sp_DB2

When i call the Original Sp like sp_DB2(Testing) It should bind with the @Param1 for the other Sp which is sp_DB1

By doing this we can reduce the Hardcoded value of Testing inside the Sp

Pls somebody help me to solve this

Thanks in Advance !
Jay

SP_EXECUTE_REMOTE in Azure works very similar to native sp_executesql. So you should be able to bind the params & values. But I believe there is a limitation that you can't get a output param value from it. Try this and see if it works for your case. Thanks.

EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE @param1 nvarchar(max)= @param1_from_sp_DB2, @outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue',
    N'@param1_from_sp_DB2 nvarchar(max)',
    @param1 --Since this is the input param name from sp_DB2


Does SQL Server use (unused)index stats for optimizer I am wondering about unused index in MS SQL Server. By the Index usage DMV I can identify an index which has not been used for seeks, scans or lookups.

However I know from Oracle that an index might not be used in such a way in a execution plan, however it can still contribute statistics/cardinality information to the (Oracle) optimizer. This contribution is not monitored in the same way.

So I am wondering if in MSSQL a Index can have a similar positive effect even when it is not directly used (in a representative time frame)? And specifically, if it can be better than a column statistic (I.e. dropping the index would be harmful).

I haven’t seen this mentioned in any of the index tuning articles I have come along, so I assume MSSQL (up to 2017) does not have this concept, is that correct?

Yes, statistics based on indexes can be used to help with query plan creation even if the underlying index isn't used to access data in the plan. Consider that the query optimizer may consider many different query plans and data access paths while creating a query plan. The compiled query plan may end up not using one of the indexes that was considered. That certainly doesn't mean that any query plan that benefited from the statistics of that index needs to be invalidated, right?

An example might be helpful as well. First I'll throw about 6.5 million rows into a heap:

DROP TABLE IF EXISTS dbo.A_GOOD_HEAP;

CREATE TABLE dbo.A_GOOD_HEAP (
    INDEXED_COLUMN BIGINT NULL,
    OTHER_COLUMN BIGINT NULL
);

INSERT INTO dbo.A_GOOD_HEAP WITH (TABLOCK)
SELECT CASE WHEN t.RN % 10 = 0 THEN 0 ELSE 1 END
, RN
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

Next I'll create an index on one of the columns and look at the histogram for the statistics object that is automatically created.

CREATE INDEX IX ON dbo.A_GOOD_HEAP (INDEXED_COLUMN);

DBCC SHOW_STATISTICS ('A_GOOD_HEAP', 'IX');

Here's the histogram:

╔══════════════╦════════════╦═════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬═════════╬═════════════════════╬════════════════╣
║            0 ║          0 ║  645160 ║                   0 ║              1 ║
║            1 ║          0 ║ 5806440 ║                   0 ║              1 ║
╚══════════════╩════════════╩═════════╩═════════════════════╩════════════════╝

Based on the statistics there are 5806440 rows in the table with a value of 1 for INDEXED_COLUMN. Now consider this query:

SELECT COUNT(DISTINCT OTHER_COLUMN)
FROM dbo.A_GOOD_HEAP
WHERE INDEXED_COLUMN = 1;

The query optimizer has a few different access paths for the data. It also has a few choices for how to calculate the aggregate. One of the considerations for the picking an algorithm for the agggregate is the cardinality estimate of the data. Here's a screenshot of the query plan:

Note that the estimate matches the histogram exactly even though the index isn't used to access data. Newer versions of SQL Server show which statistics were considered during optimization in the query plan. You can see that the statistic associated with the index was used:

However, the sys.dm_db_index_usage_stats dmv doesn't report any end user activity on the index.


Transaction replication from SQL Azure database to on-premise SQL server I have a use case where I need to keep an on-premise SQL Server database in-sync with my Azure SQL database.

Is there a way I can set up replication / sync from the Azure SQL db to the on-prem db?

The following list of article might provide adequate information for you to put together an Azure SQL Data Sync:

Sync data across multiple cloud and on-premises databases with SQL Data Sync (Preview)

  SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

Set up SQL Data Sync (Preview)

(Emphasis mine)

  In this tutorial, you learn how to set up Azure SQL Data Sync by creating a hybrid sync group that contains both Azure SQL Database and SQL Server instances. The new sync group is fully configured and synchronizes on the schedule you set.
  [...]

  Add an Azure SQL Database

  In the Member Database section, optionally add an Azure SQL Database to the sync group by selecting Add an Azure Database. The Configure Azure Database page opens.

  On the Configure Azure Database page, do the following things:

  In the Sync Member Name field, provide a name for the new sync member. This name is distinct from the name of the database itself.
  In the Subscription field, select the associated Azure subscription for billing purposes.
  In the Azure SQL Server field, select the existing SQL database server.
  In the Azure SQL Database field, select the existing SQL database.
  In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.
  In the Username and Password fields, enter the existing credentials for the SQL Database server on which the member database is located. Don't enter new credentials in this section.
  Select OK and wait for the new sync member to be created and deployed.

  Add an on-premises SQL Server database

  In the Member Database section, optionally add an on-premises SQL Server to the sync group by selecting Add an On-Premises Database. The Configure On-Premises page opens.

  On the Configure On-Premises page, do the following things:

  Select Choose the Sync Agent Gateway. The Select Sync Agent page opens.
  On the Choose the Sync Agent Gateway page, choose whether to use an existing agent or create a new agent.

  If you chose Existing agents, select the existing agent from the list.

  If you chose Create a new agent, do the following things:

  a. Download the client sync agent software from the link provided and install it on the computer where the SQL Server is located.

    Important
    You have to open outbound TCP port 1433 in the firewall to let the client agent communicate with the server.

  b. Enter a name for the agent.

  c. Select Create and Generate Key.

  d. Copy the agent key to the clipboard.

  e. Select OK to close the Select Sync Agent page.

  f. On the SQL Server computer, locate and run the Client Sync Agent app.

  g. In the sync agent app, select Submit Agent Key. The Sync Metadata Database Configuration dialog box opens.

  h. In the Sync Metadata Database Configuration dialog box, paste in the agent key copied from the Azure portal. Also provide the existing credentials for the Azure SQL Database server on which the metadata database is located. (If you created a new metadata database, this database is on the same server as the hub database.) Select OK and wait for the configuration to finish.

    Note
    If you get a firewall error at this point, you have to create a firewall rule on Azure to allow incoming traffic from the SQL Server computer. You can create the rule manually in the portal, but you may find it easier to create it in SQL Server Management Studio (SSMS). In SSMS, try to connect to the hub database on Azure. Enter its name as <hub_database_name>.database.windows.net. To configure the Azure firewall rule, follow the steps in the dialog box. Then return to the Client Sync Agent app.

  i. In the Client Sync Agent app, click Register to register a SQL Server database with the agent. The SQL Server Configuration dialog box opens.
  j. In the SQL Server Configuration dialog box, choose whether to connect by using SQL Server authentication or Windows authentication. If you chose SQL Server authentication, enter the existing credentials. Provide the SQL Server name and the name of the database that you want to sync. Select Test connection to test your settings. Then select Save. The registered database appears in the list.

  k. You can now close the Client Sync Agent app.

  l. In the portal, on the Configure On-Premises page, select Select the Database. The Select Database page opens.

  m. On the Select Database page, in the Sync Member Name field, provide a name for the new sync member. This name is distinct from the name of the database itself. Select the database from the list. In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.

  n. Select OK to close the Select Database page. Then select OK to close the Configure On-Premises page and wait for the new sync member to be created and deployed. Finally, click OK to close the Select sync members page.
  To connect to SQL Data Sync and the local agent, add your user name to the role DataSync_Executor. Data Sync creates this role on the SQL Server instance.

  Step 3 - Configure sync group

  After the new sync group members are created and deployed, Step 3, Configure sync group, is highlighted in the New sync group page.

  On the Tables page, select a database from the list of sync group members, and then select Refresh schema.
  From the list of available tables, select the tables that you want to sync.
  By default, all columns in the table are selected. If you don't want to sync all the columns, disable the checkbox for the columns that you don't want to sync. Be sure to leave the primary key column selected.
  Finally, select Save.

Sync data across multiple cloud and on-premises databases with SQL Data Sync (Preview)

  SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

  Data Sync is based around the concept of a Sync Group. A Sync Group is a group of databases that you want to synchronize.


why will compatibility mode change amount of storage iops We recently upgraded from SQL Server 2012 to SQL Server 2016.  We noticed an unusual amount of IOps on our Dev and Integration servers, but not on production.  The only difference is that production has not yet been set to SQL Server 2016 compatibility mode 130.

Running the following script I can turn off/on the extra IOps on our integration server. Why would compatibility mode make such a huge difference in storage?

EXEC sys.sp_MSforeachdb @command1 = N' ALTER DATABASE ?
    SET COMPATIBILITY_LEVEL = xxx'

Where xxx is 110 for higher IOps or 130 for lower IOps.

Presumably something is causing the query planner to produce a less optimal plan for some of your regular statements, resulting in more IO being needed (perhaps index scans where seeks were previously used, or maybe the worse plan requires extra sort operations and/or sorts over larger rowsets so is spilling more into tempdb).

A key difference between 2016 and 2012 is the updated cardinality estimator, added in 2014 and made default in 2016. To tell for sure we would need to see query plan output for the two circumstances. The new estimator produces better plans in many cases, but worse ones in a few specific cases.

For more general detail search for "sql server cardinality estimator" and you'll find many articles on the change and its implications.

For more specific help, we would need you to edit query text and query plan output from both circumstances into your question.

It could be that you need to turn the new estimator off, it could be that you have hints in the queries designed to help the older planner which make the newer one have a harder time, it could be that the queries are odd/complex and need hints to help the new planner/estimator and so on.


Set field values to newly imported rows in PostgreSQL table with existing data I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import.

Question: For the newly imported rows, how do I set the values for a specific column (not found in the CSV)? I cant do UPDATE staging.tablename SET colname = 'somename' the way I could when importing into an empty table. Or is this approach wrong?

I'd recommend using the COPY command from psql. You can set a DEFAULT value for a column and omit that value from the COPY command, eg:

\copy tablename(col1,col2,col3) FROM 'thefile.csv' WITH (FORMAT CSV)

Alternately, you can create a new TEMPORARY table in PgAdmin-III wtih just the columns in the CSV, import the CSV into it, and use SQL like this to merge it into the main table:

INSERT INTO realtable (col1, col2, col3, colwithdefault)
SELECT
    col1, col2, col, 'some default value'
FROM tempcsvtable;

You can use this to calculate columns based on expressions, combine and split columns, omit some rows, etc.


Option 1: You could write one subquery to aggregate the names, one subquery to find the max date, then join the second subquery against the original table on the date to get the rest of the C_ columns. This is ugly.

Option 2: You could use Analytic Functions:

SELECT MAX(date_col) AS max_date_col,
       LISTAGG(name) WITHIN GROUP (ORDER BY name ASC) AS names,
       MAX(c_a) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_a,
       MAX(c_b) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_b,
       MAX(c_c) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_c,
       MAX(c_d) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_d,
       MAX(c_e) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_e,
       MAX(c_f) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_f,
       MAX(c_g) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_g
  FROM /* ... */


Error code 1054: Unknown column when inserting data into a table I have been trying to join data from two separate tables with distinct columns into a third table, but

  Error code 1054: Unknown Column 'Company' in field list

keeps popping up.

My current code is:

INSERT INTO Company (Year, Company, Revenue, Profits)
(select Year, Company, Revenue, Profits from
(select Year, Comp_Name from Comp_Info
union
select Revenue, Profits from Comp_Financials)
AS tempTable);

Comp_Financials table:

Comp_Info table:

Both tables will be combined into the already created table:
Company (ID int auto_increment, Year int, Company varchar (20), Revenue int, Profits int, Primary key (ID))

Any help is greatly appreciated!

You need to use JOIN by ID instead of the UNION.
Using JOIN there is no need of the sub-query.
Also ( before SELECT is not required after the INSERT INTO.

So the working query is:

INSERT INTO Company (Year, Company, Revenue, Profits)
SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits
FROM Comp_Info AS CI
JOIN Comp_Financials AS CF ON CF.ID = CI.ID;

More explanation:

What you have tried with UNION is the wrong approach. Let me explain with a small example.

I'm creating the Comp_Info and Comp_Financials are the temporary table for the example

CREATE TEMPORARY TABLE Comp_Info (Id INT, Year INT, Comp_Name VARCHAR (200));

INSERT INTO Comp_Info (Id, Year, Comp_Name) VALUES
(1, 2005, 'Wal-Mart'), (2, 2005, 'Exxon Mobil'), (3, 2005, 'General Motors');

CREATE TEMPORARY TABLE Comp_Financials (Id INT, Revenue INT, Profits INT);

INSERT INTO Comp_Financials (Id, Revenue, Profits) VALUES
(1, 288189, 10267), (2, 270772, 25330), (3, 193517, 2805);

Using the sample data the UNION will result as:

SELECT Year, Comp_Name FROM Comp_Info
UNION
SELECT Revenue, Profits FROM Comp_Financials;

|Year      | Comp_Name       |
|----------|---------------- |
|2005      | Wal-Mart        |
|2005      | Exxon Mobil     |
|2005      | General Motors  |
|288189    | 10267           |
|270772    | 25330           |
|193517    | 2805            |

But with the same sample data, the JOIN will result as:

SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits
FROM Comp_Info AS CI
JOIN Comp_Financials AS CF ON CF.ID = CI.ID;

| Year  | Company           | Revenue   | Profits
|------ |------------------ |---------- |---------
| 2005  | Wal-Mart          | 288189    | 10267
| 2005  | Exxon Mobil       | 270772    | 25330
| 2005  | General Motors    | 193517    | 2805


returning set of records in stored procedure I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures:

create or replace function sqlProc () returns setof integer as $$
  select * from foo;
$$ language sql;

create or replace function plpgSqlProc () returns setof integer as $$
declare
  c integer;
begin
  for c in select * from foo loop
    return next c;
end loop;

end;
$$ language plpgsql;

The pure SQL version has 2x better time performance than the pl/pgsql one! Unfortunately I have other logic that cannot be expressed in pure SQL so I am wondering how I should write my stored procedure?

There are a few ways of doing this in PostgreSQL depending on the versions you have to support.  The simplest is:

create or replace function plpgSqlProc () returns setof integer as $$
BEGIN

RETURN QUERY select * from foo;
END;
$$ LANGUAGE PLPGSQL;

This being said, I find where you don't need helper procedural code, plain SQL functions are usually better.  They are usually fast and sometimes they are even planner-transparent.


MYSQL query to return most recent entry as long as it is equal to XXX OK I'll try and explain this as best as I can as I couldnt really fit it into the title.
Let's say I have a database like so:
+----------+--------------+---------------+---------+
| Agent    | Customer ID  |    date       | Answer  |
+----------+--------------+---------------+---------+
| Jon      | 1            | 2017-05-08    |     Yes |
| Jon      | 2            | 2017-05-01    |      No |
| Jon      | 1            | 2017-03-05    |   Maybe |
| Jon      | 4            | 2017-03-10    |   Maybe |
| Jon      | 2            | 2017-03-10    |   Maybe |
| Jon      | 3            | 2017-03-10    |   Maybe |
+----------+--------------+---------------+---------+

I want to create a query that searches all of Jons entries, where he has selected "Maybe" for a customer, and where "maybe" is his most recent entry for said customer. Does this make sense? So out of the above table I only want to return the results for customer ID 3 and 4.
| Jon      | 3            | 2017-03-10    |   Maybe |
| Jon      | 4            | 2017-03-10    |   Maybe |

I dont want records 1 and 2 because there is a more updated entry of "yes" and "no". I simply want it to check all of Jon's entries where the customer is still undecided, and there has been no new record added to state "yes" or "no".
I've tried using MAX(date) and then running WHERE clauses of "agent=xx" and "answer=xx" however Max does not return the entire row for the most recent entry. Just the value. It appears I am in way over my head here and I don't want to resort to grabbing data from the table and sorting through it in PHP. I'm sure there is a way to write a query that does what I am looking for?

with AD as (
select T.*, rank() over (partition by Agent, customer_id order by date desc) R from tableName T
 /* order by agent, customer_id, date desc */
 )
select * from AD
  where R = 1 and answer = 'Maybe';

See db fiddle
For people trying to answers questions like these, tableconvert comes in handy.  Use Import / Markdown.


Backup from Server1 and restore it to Server2? IF we have two Servers installed with SQL Server 2008 instances,
Server1 has three drives C, D and E with 100, 500 and 500 GB respectively
and Server2 has three drives C, D and E with 100, 300 and 300 GB respectively.
Now you have database1 on Server1 with data file 250 GB on D drive and Log file 350GB on E drive, its recovery model is full and replication has been setup
on this database, what is the best way to take backup from Server1 and restore it to Server2?

Problem is server2 has 300GB space but on Server1 has 350GB log file.

It's a little bit strange to have log file bigger than data file.
I guess you didn't take a backup for long time and that is a reason for such a huge log file.

First try with this link: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/633ea08c-942d-4703-bc03-f2befd35f612/log-file-size-keeps-growing-replication?forum=sqlreplication

The simplest solution is log backup, log backup should reduce the size of file.

If that fails you can try to truncate log file (see dbcc command). However, you should use this as a last resort since truncation of log file is "quick and dirty" solution.


Inner Join until null on self-referencing tables I have the following SQL Table:

 OrganizationalUnit
    ID - PK
    ParentOrganizationalUnit - FK
    Title
    Department_Code (optional)
    Division_Code (optional)

 Person
    ID - PK
    OrganizationalUnitID - FK
    FirstName
    LastName

And for the sake of this example, these tables appear as follows:

OrganizationalUnit:
0 | (null) | Information Technology |  101   | (null)
1 | 0      | Database Management    | (null) | 4000
2 | (null) | Human Resources        |  102   | (null)
3 | 2      | Recruitment            | (null) | 4000
4 | (null) | Finance                | (null) | (null)
5 | 1      | SQL                    | (null) | (null)

People
0 | 0 | John    | Doe
1 | 0 | Jane    | Doe
2 | 1 | Josh    | Doe
3 | 1 | Jack    | Doe
4 | 3 | Other   | Doe
5 | 4 | Finance | Doe
6 | 5 | Odd     | Doe

I need to run a query so that I obtain the following output:

Output
PersonID | OrganizationalUnitID | Department_Code | Division_Code | First Name | last Name
0        |         0            |       101       |       (null)  |  John      |  Doe
1        |         0            |       101       |       (null)  |  Jane      |  Doe
2        |         1            |       101       |       4000    |  Josh      |  Doe
3        |         1            |       101       |       4000    |  Josh      |  Doe
4        |         3            |       102       |       4000    |  Other     |  Doe
5        |         4            |      (null)     |       (null)  |  Finance   |  Doe
6        |         5            |      101        |       4000    |   Odd       |  Doe

The department_code and division_code fields are used for association of OrganizationalUnits with another database. This other database is only capable of tracking up to 2 levels of hierarchy (department and division) whereas, I have been asked to track an infinite level of hierarchy, but to map the first level to departments and the second level to divisions. I have no idea where to start to write a query that could do this. I may have to redesign my ERD.

There is a chapter in SQL Server called: Recursive Queries Using Common Table Expressions. It is very well explained and has a very clear and simple example.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186243(v=sql.105)

Based on this documentation, my query solution is as follows (tested OK):

WITH CTE_RecursiveOU (ID, ParentID, DepCode,DivCode)
AS
(
-- Anchor member definition
    SELECT
        u.ID as ID,
        u.ParentOrganizationalUnit as ParentID,
        u.Department_Code as  DepCode,
        u.Division_Code as DivCode
    FROM dbo.OrganizationalUnit AS u
    WHERE u.ParentOrganizationalUnit IS NULL
    UNION ALL
-- Recursive member definition
    SELECT
        u.ID as ID,
        u.ParentOrganizationalUnit as ParentID,
        r.DepCode as  DepCode,
        ISNULL(r.DivCode,u.Division_Code) as DivCode
    FROM dbo.OrganizationalUnit AS u
    INNER JOIN CTE_RecursiveOU AS r
        ON u.ParentOrganizationalUnit = r.ID
)
-- Statement that executes the CTE
SELECT
    p.ID as PersonID,
    c.ID as OrganizationalUnitID,
    c.DepCode as Department_Code,
    c.DivCode as Division_Code,
    p.FirstName,
    p.LastName
FROM CTE_RecursiveOU AS c
INNER JOIN dbo.Person AS p
    ON p.OrganizationalUnitID = c.ID;

GO


What happens to a SQL Server 2019 Standard instance with an Annual Subscription Licence when it expires? We have 3 production SQL Server 2019 standard instances which we have licenced through a third party supplier on an annual subscription basis. In our use case this works out cheaper if you only require the licences for <3 years.
We've just been informed we need to re-up the licence for the 2nd year within the next 5 days, there's a chance the invoice wont be processed and approved our end until next week, I'm just doing my due diligence on what risk this poses to our production environment.
From what I can tell, previously (at least on 2008R2), the instance validated the licence key locally so if this licence type existed back then, even if it did expire, SQL would continue to function as normal. My concern is that Microsoft have implemented some licence protection features since then to support this licence type, which could stop the instance from starting/working, similar to how an expired evaluation licence instance would stop working when the service is restarted.
There's very little information on this licence type online, its mentioned on Microsoft SQL Server's licencing/pricing page but the detailed documents linked there don't cover it at all.
SQL 2019 Licencing & Pricing
Does anyone have any experience with this licencing method/internals of sql licencing and could shed some light on how the instance will behave once it hits its expiration date?

SQL Server has no technical enforcement of licenses (except for Evaluation Edition which is time-bombed).
An installation of SQL Server has a "product key" to activate the installation for a particular SKU, but this is not a license.
The assignment of licenses to servers is handled externally.


duplicate entry issue with large mysql database table I have a very large MySQL database table. The other day, for no apparent reason, I now keep getting:

Duplicate entry '148245' for key 'PRIMARY'

If I remove the entry at with primary of 148245, then I just get the same error but showing a different number. I tried on a backup copy of the table by removing the bad row and trying an insert statement through my ASP.NET webpage using the MySQL connector which has until recently always worked and I get the error but with a different number.

What do I do to remove this error - surely I can't just delete a load of rows until it goes away?

Not an error I have seen before and I am not a massive MySQL expert.

Thanks in advance.

From here - take a dump of the table, drop it and recreate it. Reload your dump - that solved the issue here. If this doesn't solve the issue, then please post SHOW CREATE TABLE my_table\G and also the contents of my.cnf.


Difference installing SQL Server 2014 on stand alone vs. VM Cluster I have installed SQL Server many times on standalone servers with DASD and SAN storage allocated to it.  We have a new Hyper-V Virtual machine Cluster set up for us by a third party IT shop.   I am planning to install SQL Server 2014 on it and would like advice on what I should look out for and what to keep in mind.
What things are different or additional tasks I should prepare for as compared to a simple install on a stand-alone server.

There are 2 nodes to each cluster one for fail over clustering.  Do i just need to install 1 instance of SQL server in 1 of the nodes ?

edited answer based on the comments.

For a basic setup here are a few things that I do before getting to the actual SQL install

Have two additional IPs / Host Names in mind for the Windows Cluster and the SQL Server VIP
Have two Domain service accounts setup for the Engine and the Agent (Something like DOMAIN\sql.eng.svc and DOMAIN\sql.agt.svc)
Enable the following Roles and Features (File Server, .Net Framework 3.5, Failover Clustering, Telnet Client)
In Local Security Policy grant the DOMAIN\sql.eng.svc account "Perform Volume Maintenance Tasks" and "Lock Pages in Memory"

Disks

This is totally dependant on your environment and everyone will have their own preferences but in an ideal world I tend to go for:

One LUN for SQL Data (D:) Formatted with an Allocation Unit of 64K
One LUN for SQL Logs (E:) Formatted with an Allocation Unit of 64K
One LUN for SQL Backups (G:)
One LUN for SQL TempDB (I:) Formatted with an Allocation Unit of 64K
One LUN for Qourum (Q:)

Format the disks and bring them online on Node1.

Windows Cluster

Open Failover Cluster Manager and Validate the cluster with (Node1 and Node2). Once you are happy and there are no errors create the Cluster using the Windows Cluster IP / Host Name you set aside earlier.

As for Installing SQL Server run the installer on Node 1 choosing (New SQL Server failover cluster installation), once complete run the installer on the second node choosing the (Add node to a SQL Server failover cluster) install option.

Here is a decent enough guide on the process from a SQL perspective with screenshots - link.

One thing not covered in that guide is once done I would also enable Kerberos Authentication by running the below Set SPN commands

setspn -A MSSQLSvc/SQLCLUSTERNAME.DOMAINNAME:1433 DOMAIN\sql.eng.svc

setspn -A MSSQLSvc/SQLCLUSTERNAME.DOMAINNAME DOMAIN\sql.eng.svc

More info on SQL and SPN's here link.

It goes without saying that the failover process should be tested, make sure all resources come up on each node. Do this a few times to get the hang of it.

Hopefully this at least gets you started.


Why are column store databases the new thing now? Column store databases have several advantages over traditional databases - most notably compression and faster seek. Column store seem to be the new fad for databases in the past few years, with HANA as the most obvious example, but Microsoft and Oracle (and others) following suite.

However, the idea of column store databases are not new:

  TAXIR was the first application of a column-oriented database storage
  system ... in 1969

(wikipedia)

Why has it taken until the last 8 years for column databases to take off? Surely compression in database was hugely useful in the 1970's when hard drive storage was not as dirt cheap as it is now (and faster seek never hurts). Is there another development in the last decade that enabled column store databases?

By and large, I think the increase in the popularity of data warehousing is prompting the move to Columnstore indexes.

Columnstore indexes are geared toward increasing query performance for workloads that involve large amounts of data, typically found in data warehouse fact tables.

Columnstore indexes store data by column rather than by row. By storing data in columns rather than rows, the database can more precisely access the data it needs to answer a query rather than scanning and discarding unwanted data in rows.

For wide tables, such as those commonly found in data warehouses, Column Store Indexes come in handy as you essentially reduce the amount and size of data needed to be accessed for any given query.

Check out some of these links for more information and examples:

Understanding the SQL Server Columnstore Index

Column-oriented DBMS

Columnstore Indexes in SQL Server 2012


Logical reads time 8KB more than memory on instance I am working on an instance of SQL Server and was using Redgate SQL Monitor. I saw a query in the top 10 list with 1 execution which had logical reads more than the memory for the instance by a factor of around 100 and the duration of the query was only a few minutes. How is that possible? I mean how can 8KB pages times the number of logical reads be more than the memory? What is it that I don't understand?
Thanks

If SQL Server reads 10 pages they don't all have to be in memory at the same time. It can also count reads of the same page multiple times.
To simplify, let's say SQL Server has limited the buffer pool to 8KB (conveniently, the size of a page). Now, you run a query that requires SQL Server to read 3 pages. It can't fit all the pages into memory at once, so:
Action                    Number of reads so far
------------------------  ----------------------
Loads page 1 into memory                       1
Discards/disfavors page 1                      1
Loads page 2 into memory                       2
Discards/disfavors page 2                      2
Loads page 3 into memory                       3

Now, let's say you use a CTE or self-join or something else, if SQL Server isn't able to re-use the same page while it is in memory, it will have to do that all over again. Even if it doesn't, if it has to access the same page twice while it is in memory, that's two logical reads. If an operator in a query plan (like a key lookup) has 10 executions, those are additional logical reads. If a foreign key has to be validated, there are some more. If a trigger runs as part of the transaction, worktables are involved, a sort spills to disk, etc. just keep piling on the reads. Just because all those reads can't fit into memory doesn't mean they don't still have to happen.
There is no direct correlation between read activity and max server memory / buffer pool size. You just need to remember that page reads aren't distinct and they don't all have to happen at the same instant in time, especially if you have a lot of churn in your buffer pool, in which case they have be spread out.


Oracle date not returning the right data I don't know whats going on here, but here is my table (budget) with select * from budget :

|budget_id|as_of_date|quarter_1|quarter_2|quarter_3|quarter_4|
|1        |15-MAY-16 |400      |100      |100      |100

So say if I wanted to calculate sum for all quarters on the given date(15th May of 2016) or before I would use this query :

select as_of_dt from budget where as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd');

This query returns nothing:

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) from budget where as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd');

However if I add one day to this :

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) from budget where as_of_dt <= to_date('2016-05-16', 'yyyy-MM-dd');

I get the right result 700. I want to get all budget records before or equal to a given date string, why is the equal not working?

This might be a hint but I don't get it :

select to_date(as_of_dt, 'yyyy-MM-dd') from budget;
=> 16-MAY-15

select as_of_dt from budget;
=> 15-MAY-16

Somehow the year gets flipped or whatever is happening here, what do I do to get the right output with both equal and less than a given date ?

The to_date function creates a date with a time. If I remember well this is 12:00. If the date in your table has a different time then this is also taken into account. When you want to test dates then you better put the trunc() function around your date like:

select as_of_dt
from   budget
where  trunc(as_of_dt) <= to_date('2016-05-15', 'yyyy-MM-dd');

Now the time part is the same in both dates.


Shortest Query to Combine Count and Unique (Oracle) I have the following query which works for me:

select count(*)
from (
   select UNIQUE col1, col2, col3
   from my_table
   where cond1 = 'A'
   and cond2 = 'B'
   and cond3 = 'C'
)

Is it possible to write this without the nested query?

Not that I recommend it, but:

select count(distinct ora_hash(col1)||'|'||ora_hash(col2)||'|'||ora_hash(col3))
from my_table
where cond1 = 'A'
and cond2 = 'B'
and cond3 = 'C'

A nested query seems like "the right answer" to me.


Postgres Full Text Search With Short Word Matching I'm using Postgres' full text search to return results, but it seems any query with 3 characters or fewer returns no results.  Is there a way to configure Postgres so that I can query with 2 and 3 character long strings?  My data set is extremely small, so performance would not be an issue.

For reference, here's my table definition:

CREATE TABLE doctokens (
    id serial primary key,
    inner_text TEXT NOT NULL,
    tokens tsvector
)

INSERT INTO doctokens (inner_text) VALUES ('Route builders give you a fluent syntax')

After an insert, I create the token tsvector:

UPDATE doctokens SET tokens = setweight(to_tsvector('english', COALESCE(inner_text, '')), 'A')

And here's how I'm querying the table:

SELECT ts_headline('english', inner_text, query, 'StartSel = <em>, StopSel = </em>, ShortWord = 1') as inner_text_highlights
FROM (SELECT inner_text, ts_rank_cd(tokens, query) AS rank, query
    FROM doctokens, plainto_tsquery('english', 'rou') AS query
    WHERE tokens @@ query
    ORDER BY rank DESC
    LIMIT 5) AS query_results;

'route' and 'routing' stem to 'rout'.  Neither 'rou' nor 'routi' do.  So, they don't match.

You can use the prefix match to make 'rou' match, but plainto_tsquery doesn't understand that so you have to make the tsquery yourself:

SELECT ts_headline('english', inner_text, query, 'StartSel = , StopSel = , ShortWord = 1') as inner_text_highlights
FROM (SELECT inner_text, ts_rank_cd(tokens, query) AS rank, query
    FROM doctokens, (select 'rou:*'::tsquery as query ) AS query
    WHERE tokens @@ query
    ORDER BY rank DESC
    LIMIT 5) AS query_results;
              inner_text_highlights
--------------------------------------------------
 Route builders give you a fluent syntax
 Does Routing rhyme with looting?

Prefix matching won't let you match 'routi:*', because the things it would match against get the 'i' stripped off when stemming.


modeling data dimensions as columns - some dimensions are highly variable while others have low variability Please excuse my question if it is a bad one - I am not a DBA...

I would like to model the following data:

Let's say that I have one column that is the row id as generated by a sequence. Let's also say that I have 3 columns: A, B & C that have low data variability. Let's also say that I have 4 more columns I, II, III, IV with high data variability.

As a concrete example, lets say that the values of A, B & C can be any number between 1 - 50, and the values of I, II, III & IV can take any textual data, as another example, any number between 1 - 1B.

The question:
Should I model a single table with columns: row_id, A, B, C, I, II, III, IV. Or should I model two tables the first to contain the low variability data columns: row_id, A, B, C and the second to contain the rest of the columns while referencing the row_id of the first table: row_id, first_table_row_id, I, II, III, IV.

I am using postgresql. What answer best fits that database? What answer is the generic best?

Also, there might not be a clear cut answer, if so, what are the pros and cons to each modeling scheme?

I would suggest creating a separate dimension for your high variability columns. This is a Type 4 mini dimension according to Kimball methodology.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-4-mini-dimension/

Hope this helps.


MS sql query value one table but not another join table where* I have [tbl_AllStudentsEmail] where I get my students distribution list.
I also have [tbl_CourseID] where some students are registered (not all).
Students record are registered here along with the COURSEID.

Conditions: A student can be registered in several courses.

My goal is that I want to select all the students who are not registered to the course ID.  How do I go about that?

Select
 [StudentID],
 [StudentName],
 [StudentEmail],
 [CourseID]
from [tbl_AllStudentsEmail]
left join tbl_CourseID
on tbl_CourseID.StudentID = tbl_AllStudentsEmail.StudentID
where
 CourseID = 11 and
 tbl_AllStudentsEmail.StudentID is not null

This statement gives me a problem.  It would give me "dupe" students email when the student is registered to course ID = 9 and CourseID = 10.

How can i get the UNIQUE student email out?

Assuming the following table structures and that you want to return students that have NOT registered for CourseID 11...you need to change your LEFT JOIN to include CourseID = 11 and then add CourseID is NULL in the WHERE clause to pull the students that are not registered for that course.

In your query having CourseID = 11 in your WHERE clause limits the results to the students that have registered for that course.

CREATE TABLE #AllStudentsEmail (StudentID INT, StudentName VARCHAR(20), StudentEmail VARCHAR(20))
CREATE TABLE #Course (CourseID INT, StudentID INT)

INSERT INTO #AllStudentsEmail VALUES (1000,'Joe T','jt@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (2000,'Jill B','jb@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (3000,'Tommy S','ts@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (4000,'Sally J','sj@blank.edu')

INSERT INTO #Course VALUES (9,1000)
INSERT INTO #Course VALUES (10,1000)
INSERT INTO #Course VALUES (11,1000)
INSERT INTO #Course VALUES (9,2000)
INSERT INTO #Course VALUES (10,2000)
INSERT INTO #Course VALUES (11,2000)
INSERT INTO #Course VALUES (9,3000)
INSERT INTO #Course VALUES (10,3000)
INSERT INTO #Course VALUES (10,4000)
INSERT INTO #Course VALUES (11,4000)

SELECT  #AllStudentsEmail.[StudentID],
 [StudentName],
 [StudentEmail],
 [CourseID]
FROM #AllStudentsEmail
LEFT JOIN #Course ON #AllStudentsEmail.StudentID = #Course.StudentID
    AND #Course.CourseID = 11
WHERE #Course.CourseID IS NULL

DROP TABLE #AllStudentsEmail
DROP TABLE #Course

--results

StudentID   StudentName StudentEmail    CourseID
3000    Tommy S ts@blank.edu    NULL


How to change the instance name of SQL Server Express 2008? On my development machine I've installed SQL Server Express 2008, but it has the instance name .\SQLEXPRESS. I want it to be the default instance. I'm pretty sure that I selected the "Default Instance" option in the setup, but apparently it didn't work. Is there any way I can change the instance name now?

Added:

It seems that this is a known bug in the installer. It will be fixed when PCU 1 (PubliC Update) comes out, but there's not telling when that's gonna happen. In the mean time there are two workarounds:

When installing, choose the "Named Instance" option and enter MSSQLSERVER.
Install a named instance, then install another instance and this time the "Default Instance" option will work.

Still, the question remains: is there any way to rename an instance WITHOUT reinstalling? Because the setup was a slow pain. XD

There is no way to change the instance name without re-installing, sorry.....

Update: OK, seems there are a few ways of doing this - not sure how well these work, though. I have not ever heard of anyone doing this successfully, though :-( The easiest way to do this is re-installing with the correct name. Especially with the SQL Server Express versions, it's really no big deal to reinstall - done in a breeze, and you can of course backup and restore your data - so no loss there, either....

Also, it seems that method is limited to SQL Server 2000 only - when searching for the appropriate topics for SQL Server 2008 (sp_dropserver, sp_addserver), it seems those are used for renaming a server (computer) to a new name - not changing the SQL Server instance name, in my opinion. There's no more mention of renaming an installed SQL Server instance with that method.


512 Bytes are not being used from SQL Server's 8 KByte data page I have create the following table:

CREATE TABLE dbo.TestStructure
(
    id INT NOT NULL,
    filler1 CHAR(36) NOT NULL,
    filler2 CHAR(216) NOT NULL
);

and then created a clustered index:

CREATE CLUSTERED INDEX idx_cl_id
ON dbo.TestStructure(id);

Next I populated it with 30 rows each size is 256 byte (based on table declaration):

DECLARE @i AS int = 0;

WHILE @i < 30
BEGIN
    SET @i = @i + 1;

    INSERT INTO dbo.TestStructure (id, filler1, filler2)
    VALUES (@i, 'a', 'b');
END;

Now based on information I read in "Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 (Itzik Ben-Gan)" book:

  SQL Server internally organizes data in a data file in pages. A page
  is an 8 KB unit and belongs to a single object; for example, to a
  table or an index. A page is the smallest unit of reading and writing.
  Pages are further organized into extents. An extent consists of eight
  consecutive pages. Pages from an extent can belong to a single object
  or to multiple objects. If the pages belong to multiple objects, then
  the extent is called a mixed extent; if the pages belong to a single
  object, then the extent is called a uniform extent. SQL Server stores
  the first eight pages of an object in mixed extents. When an object
  exceeds eight pages, SQL Server allocates additional uniform extents
  for this object. With this organization, small objects waste less
  space and big objects are less fragmented.

So here I have the first mixed extent 8KB page, populated with 7680 bytes (I have inserted 30 times 256 byte size row, so 30 * 256 = 7680), to check the size I have run size check proc - it returns the following result

index_type_desc: CLUSTERED INDEX
index_depth: 1
index_level: 0
page_count: 1
record_count: 30
avg_page_space_used_in_percent: 98.1961947121324
name : TestStructure
rows : 30
reserved :  16 KB
data : 8 KB
index_size : 8 KB
unused :    0 KB

So 16 KB are reserved for the table, first 8 KB page is for Root IAM page, the second one is for leaf data storage page which is 8KB with occupation of ~ 7.5 KB, now when I insert a new row with 256 Byte:

INSERT INTO dbo.TestStructure (id, filler1, filler2)
VALUES (1, 'a', 'b');

it is not stored in the same page although it have a space of 256 byte (7680 b + 256 = 7936 which is still smaller than 8KB), a new data page is created, but that new row could be fit on the same old page, why does SQL Server create a new page when it could save space and searching time buy inserting it in the existing page?

Note: the same thing is happening in heap index.

While it is true that SQL Server uses 8k (8192 bytes) data pages to store 1 or more rows, each data page has some overhead (96 bytes), and each row has some overhead (at least 9 bytes). The 8192 bytes is not purely data.

For a more detailed examination of how this works, please see my answer to the following DBA.SE question:

SUM of DATALENGTHs not matching table size from sys.allocation_units

Using the information in that linked answer, we can get a clearer picture of the actual row size:

Row Header = 4 bytes
Number of Columns = 2 bytes
NULL Bitmap = 1 byte
Version Info** = 14 bytes (optional, see footnote)
Total Per Row Overhead (excluding Slot Array) = 7 bytes minimum, or 21 bytes if version info is present
Total Actual Row Size = 263 minimum (256 data + 7 overhead), or 277 bytes (256 data + 21 overhead) if version info is present
Adding in the Slot Array, the total space taken per row is actually either 265 bytes (without version info) or 279 bytes (with version info).

Using DBCC PAGE confirms my calculation by showing: Record Size  263 (for tempdb), and Record Size   277 (for a database that is set to ALLOW_SNAPSHOT_ISOLATION ON).

Now, with 30 rows, that is:

WITHOUT version info

30 * 263 would give us 7890 bytes. Then add in the 96 bytes of page header for 7986 bytes used. Finally, add in the 60 bytes (2 per row) of the slot array for a total of 8046 bytes used on the page, and 146 remaining. Using DBCC PAGE confirms my calculation by showing:

m_slotCnt    30  (i.e. number of rows)
m_freeCnt    146   (i.e. number of bytes left on the page)
m_freeData   7986  (i.e. data + page header -- 7890 + 96 -- slot array is not factored into the "used" bytes calculation)

WITH version info

30 * 277 bytes for a total of 8310 bytes. But 8310 is over 8192, and that didn't even account for the 96 byte page header nor the 2 byte per row slot array (30 * 2 = 60 bytes) which should give us only 8036 usable bytes for the rows.

BUT, what about 29 rows? That would give us 8033 bytes of data (29 * 277) + 96 bytes for page header + 58 bytes for slot array (29 * 2) equaling 8187 bytes. And that would leave the page with 5 bytes remaining (8192 - 8187; unusable, of course). Using DBCC PAGE confirms my calculation by showing:

m_slotCnt    29  (i.e. number of rows)
m_freeCnt    5   (i.e. number of bytes left on the page)
m_freeData   8129  (i.e. data + page header -- 8033 + 96 -- slot array is not factored into the "used" bytes calculation)

Regarding Heaps

Heaps fill data pages slightly differently. They maintain a very rough estimate of the amount of space left on the page. When looking at the DBCC output, look at the row for: PAGE HEADER:    Allocation Status   PFS (1:1). You will see the VALUE showing something along the lines of 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL (when I looked at the Clustered table) or 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL when looking at the Heap table. This is evaluated per Transaction, so doing individual inserts such as the test being performed here could show different results between Clustered and Heap tables. Doing a single DML operation for all 30 rows, however, will fill the heap as expected.

However, none of these details regarding Heaps directly affect this particular test since both versions of the table fit 30 rows with only 146 bytes remaining. That isn't enough space for another row, regardless of Clustered or Heap.

Please keep in mind that this test is rather simple. Calculating the actual size of a row can get very complicated depending on various factors, such as: SPARSE, Data Compression, LOB data, etc.

To see the details of the data page, use the following query:

DECLARE @PageID INT,
        @FileID INT,
        @DatabaseID SMALLINT = DB_ID();

SELECT  @FileID = alloc.[allocated_page_file_id],
        @PageID = alloc.[allocated_page_page_id]
FROM    sys.dm_db_database_page_allocations(@DatabaseID,
                            OBJECT_ID(N'dbo.TestStructure'), 1, NULL, 'DETAILED') alloc
WHERE   alloc.[previous_page_page_id] IS NULL -- first data page
AND     alloc.[page_type] = 1; -- DATA_PAGE

DBCC PAGE(@DatabaseID, @FileID, @PageID, 3) WITH TABLERESULTS;

** The 14-byte "version info" value will be present if your database is set to either ALLOW_SNAPSHOT_ISOLATION ON or READ_COMMITTED_SNAPSHOT ON.


Creating Symmetric Key from external provider Knowing the security objects hierarchy, we can see that in order to create a Symmetric Key and encrypt data, we need to create:

Database Master Key which is protected by password
Certificate which is protected by the Database Master key
The Symmetric Key itself, which is protect by the Certificate

In Always Encrypted, we can use EKM module to simplify this hierarchy. For example, we can store the certificates in the Windows Certification Store and these certificates are protecting our encryption master keys.

I am wondering, if I want to use not Always Encrypted built-in encryption functionalities, can I use EKM module to create and manage my symmetric keys (like it is shown on the diagram). In the CREATE SYMMETRIC KEY documentation we have provider option, but not enough information about possible providers and examples.

I am interesting in storing the Certificate which is protecting the Symmetric keys or the Symmetric keys in external storage, because in such way the data is separated from the keys and in the database we are storing only references to the keys (like in always encrypted). Windows Certification Store will be best option for me, but Azure Key Vault or something else will work as well, I guess.

tl;dr - use CREATE CRYPTOGRAPHIC PROVIDER to create a connection inside SQL Server to your EKM/HSM hardware/software, through a vendor-provided .DLL file designed for that purpose.

You can use Azure Key Vault to protect on-premises data.  There are many steps required to enable your Azure account with Azure Key Vault, which are detailed at setup steps for extensible key management using Azure Key Vault.

The steps inside SQL Server consist of the following:

Install the SQL Server Connector for Azure Key Vault from the Microsoft download center.
Run the following code to enable EKM providers:

-- Enable advanced options.
USE master;
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- Enable EKM provider
sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;

Register the EKM module:

CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';
GO

Setup a credential for SQL Server to connect to Azure:

USE master;
CREATE CREDENTIAL sysadmin_ekm_cred
    WITH IDENTITY = 'ContosoDevKeyVault', -- for public Azure
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.azure.cn', -- for Azure China
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany
    SECRET = 'EF5C8E094D2A4A769998D93440D8115DReplace-With-AAD-Client-Secret'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;

-- Add the credential to the SQL Server administrator's domain login
ALTER LOGIN [<domain>\<login>]
ADD CREDENTIAL sysadmin_ekm_cred;

Open the key vault:

CREATE ASYMMETRIC KEY CONTOSO_KEY
FROM PROVIDER [AzureKeyVault_EKM_Prov]
WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0',
CREATION_DISPOSITION = OPEN_EXISTING;

You can then enable TDE by following the steps listed to use SQL Server with SQL Encryption.


How to grant all privileges on all tables in a schema to a user in IBM DB2? At first place, I want to export database from IBM DB2 AIX into IBM DB2 windows. Unfortunately, I can't use BACKUP and RESTORE command because of difference OS issue. So I have to resort to db2move command.

I had exported myschema schema and all of tables from dbemp database in remote IBM DB2 AIX using this command:

db2move dbemp export -sn myschema

When I run that command I was login as user named dbuser1.

It generates bunch of files in the current folder. I grab the files into my local machine (windows) which has IBM DB2 LUW installed.

Now in my local machine's IBM DB2, I dropped the existing schema myschema first by using IBM Data Studio (I also had a local dbemp database too).

Then I imported the files using this command:

db2move dbemp import

It successfully import the schema and tables into local dbemp database.

When I did above actions, I was login as user winuser1.

I have a local user named dbuser1 too in my local machine (windows). In IBM Data Studio, I created a connection profile that connect to local dbemp using dbuser1 user and I can browse myschema schema and the tables, but I can't browse the table's data (I got privilege error). User winuser1 can browse table's data, but for development purpose, I have to connect to the database using user dbuser1.

So I found out that I can grant privilege to a user on the table like this:

GRANT ALL ON myschema.table1 TO USER dbuser1

The problem is I have 100 tables, I don't want to type that lines for each table. And unfortunately, there is no wildcard solution like this too:

GRANT ALL ON myschema.* TO USER dbuser1 -- this doesn't work

So the question is how to grant all privileges on all tables in a schema to a user? Or is there alternative better solution? maybe by copying all privileges from winuser1 to dbuser1?

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess.

db2 grant dataaccess on database to user winuser1

If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be accomplished through scripting.

You could do the following

db2 -tnx "select distinct 'GRANT ALL ON TABLE '||
    '\"'||rtrim(tabschema)||'\".\"'||rtrim(tabname)||'\" TO USER winuser1;'
    from syscat.tables
    where tabschema = 'myschema' "  >> grants.sql

db2 -tvf grants.sql

This makes use of querying the system catalogs to dynamically generate a script to permission things. This is a lot of how we permission for users we don't want to give dataaccess to.

Here is a good page of the authorities for DB2.


Which option is better to search in two tables in varchar columns? I have a performance problem with searching in varchar columns in SQL Server 2016. Considering these tables have plenty of rows, for example 60000 rows each one.

Tables

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
CityID int)

CREATE TABLE Cities(
CityID int,
CityName varchar(255))

Code

I have a problem like this:

SELECT p.PersonID, p.FirstName , c.CityID,c.CityName  FROM PERSONS p
JOIN CITIES c ON c.CityID = p.CityID
WHERE
     p.FirstName like '%'+@search+'%'
  OR p.LastName  like '%'+@search+'%'
  OR c.CityName  like '%'+@search+'%'

Possible Solutions in my mind:

Creating a full text index for each table.
Creating Columnstore indexes.
Replicating the columns to another table and searching there.

I've searched in books like: "SQL Server 2012 Query Peformance Tunning" and "Expert Peformance Indexing for SQL Server 2012". And of course, a lot of sites and blogs on the internet.I would appreciate another suggestions to solve this issue.

Paul White wrote this article about using a trigram wildcard string search in place of the LIKE operator. It's quite an in-depth article, but it's probably the best you'll find on the subject (like a lot of what Paul White writes). I've implemented it in a test environment and it really is lightning fast compared to the LIKE operator.


DB disk space requirements comparision between MySQL versions I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used.

In one lab, MySQL version 5.0.77 is installed.
The DB directory for each DB is around 230 KB, on average.

    select engine from   information_schema.tables where  table_schema = 'test_1389772840'   and table_name = 'test';
    +--------+
    | engine |
    +--------+
    | InnoDB |
    +--------+
    1 row in set **(0.82 sec)**

In the other lab, MySQL version 5.6.11 is installed.
The DB directory for each DB is around 500 MB, on average.

    select engine from   information_schema.tables where  table_schema = 'test_1389586231'   and table_name = 'test';
    +--------+
    | engine |
    +--------+
    | InnoDB |
    +--------+
    1 row in set **(0.00 sec)**

So in summary, both labs have similar DataBases and tables, and use same engine, but older version is slow, and disk space requirement is less, while newer version is fast, and disk space is requirement huge.

Is this expected ? Why the slowness ? Why the huge space requirement ? How can query speed and disk usage be brought to good levels ?

I checked MySQL documentation, but it did not help. It suggested optimize table ( here http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html ) but there was no change in disk usage.

I found some compression parameters ( here https://blogs.oracle.com/mysqlinnodb/entry/innodb_compression_improvements_in_mysql ) which I tried configuring:

On 5.0.77:

    show variables like '%compr%' ;
    +---------------------------+-------+
    | Variable_name             | Value |
    +---------------------------+-------+
    | have_compress             | YES   |
    | slave_compressed_protocol | OFF   |
    +---------------------------+-------+

BEFORE change of values on 5.6.11:

    show variables like '%compr%' ;
    +------------------------------------------+-------+
    | Variable_name                            | Value |
    +------------------------------------------+-------+
    | have_compress                            | YES   |
    | innodb_compression_failure_threshold_pct | 5     |
    | innodb_compression_level                 | 6     |
    | innodb_compression_pad_pct_max           | 50    |
    | innodb_log_compressed_pages              | ON    |
    | slave_compressed_protocol                | OFF   |
    +------------------------------------------+-------+

AFTER change of values on 5.6.11:

    show variables like '%compr%' ;
    +------------------------------------------+-------+
    | Variable_name                            | Value |
    +------------------------------------------+-------+
    | have_compress                            | YES   |
    | innodb_compression_failure_threshold_pct | 0     |
    | innodb_compression_level                 | 9     |
    | innodb_compression_pad_pct_max           | 0     |
    | innodb_log_compressed_pages              | OFF   |
    | slave_compressed_protocol                | OFF   |
    +------------------------------------------+-------+

I will be monitoring the average size over next few days.

One thing must be noted that you probably never realized: The setting of the option innodb_file_per_table will determine how much space is inside each database folder.

First, take a look at the InnoDB Architecture

Note the system tablespace (which was know is named ibdata1 in the OS).

In MySQL 5.0, the default value for innodb_file_per_table is 0 or OFF
In MySQL 5.6, the default value for innodb_file_per_table is 1 or ON
With innodb_file_per_table disabled (default in 5.0), the data and index pages for all InnoDB tables are stored inside ibdata1. When you create the table mydb.mytable, you get the following:

/var/lib/mysql/mydb/mytables.frm
Data and index pages for mydb.mytable are inside ibdata1

With innodb_file_per_table enabled (default in 5.6), the data and index pages for all InnoDB tables are outside ibdata1.  When you create the table mydb.mytable, you get the following:

/var/lib/mysql/mydb/mytables.frm
Data and index pages for mydb.mytable are stored in /var/lib/mysql/mydb/mytables.ibd

SUMMARY

Without setting innodb_file_per_table and just storing data

Databases under MySQL 5.0 will have nothing but .frm files for its InnoDB tables. Thus, you only have kilobytes of files in each database.
Databases under MySQL 5.6 will have .frm and .ibd files for its InnoDB tables. Thus, you will have megabytes or gigabytes of files in each database.


Can the current search_path be used in a query? I'd like to do something like this:

SELECT *
FROM information_schema.tables
WHERE table_schema IN (search_path)

I couldn't find anything in the docs. Is it possible? How?

pg_settings is a system view containing all the configuration options and their values available in the current context:

  The view pg_settings provides access to run-time parameters of the server. It is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW, such as minimum and maximum values.

This view you can query just like any other, so you can get the value of search_path like this:

SELECT setting FROM pg_settings WHERE name = 'search_path';

The returned value is a text, with the delimiter being ', '.  Some tinkering is needed so that you can compare table_schema to this:

SELECT *
  FROM information_schema.tables
 WHERE table_schema = ANY (SELECT unnest(string_to_array(setting, ', '))
                             FROM pg_settings
                            WHERE name = 'search_path');

This is still only half perfect, as "$user" can be in the search_path.  One way to solve this is to replace it with the current user like

... replace(setting, '"$user"', CURRENT_USER) ...

See all this at work on DBFiddle.


How to get connection details for another connection open to database How to get connection details as e.g. collation_connection used for another connection open to database?

To give an example. Let's say I have this activity on my Database

mysql> show processlist;
+------+------+--------------------------------+------+---------+------+-------+------------------+
| Id   | User | Host                           | db   | Command | Time | State | Info             |
+------+------+--------------------------------+------+---------+------+-------+------------------+
| 6649 | xxxx | server1.example.com:56300      | mydb | Sleep   | 4123 |       | NULL             |
| 6651 | xxxx | server1.example.com:56301      | mydb | Sleep   |   28 |       | NULL

Now I need to know the collation of connection with ID 6649 from processlist.

What you are asking for is only available in MySQL 5.7. It's a new thing I have not played with yet.

It is in the performance_schema database (a.k.a. Performance Schema System Variable Tables)

session_variables : System variables for the current session. An application that wants all system variable values for its own session should use this table. It includes the session variables for its session, as well as the values of global variables that have no session counterpart.
variables_by_thread : Session system variables for each active session. An application that wants to know the session variable values for specific sessions should use this table. It includes session variables only, identified by thread ID.

It looks like this

mysql> desc performance_schema.variables_by_thread;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| THREAD_ID      | bigint(20) unsigned | NO   |     | NULL    |       |
| VARIABLE_NAME  | varchar(64)         | NO   |     | NULL    |       |
| VARIABLE_VALUE | varchar(1024)       | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

To address the question, you would need to run somthing like

SELECT * FROM performance_schema.variables_by_thread WHERE thread_id = 6649;

If you want that kind of access to other connections, you must upgrade to MySQL 5.7.

Sorry, there is nothing else available in MySQL 5.6 and prior.


Recommendations on creating user-objects on system database I want to setup a bunch of Event Notifications on SQL Server using Service Broker for our production database.

I've setup a new database on my testing machine, and everything works well.
The problem is that I'd rather not have to deploy a whole database for this notification purposes and I can't use one of the user databases already in place (that would be a mess, anyway).

If possible, I'd preferably deploy the notification objects on master, but first I'd like to be sure this is actually a good idea (if it actually is).

From Books Online:

  Do not create user objects in master. If you do, master must be backed up more frequently.

Books Online recommends I do not create user objects in master, but in this case, I don't think it's that much of a problem since I don't really care if I eventually lose some notification data. Besides, we already do backup our master database frequently.

Should I use the master database or something else in this case?

Is there something I should know about the master database, Service Broker or Event Notifications before setting this up?

I can think of a few questions about this implementation that I would want to know before continuing:

What happens if, somehow, microsoft creates an object with the exact same name in a new version?
If databases are migrated to a new server, how will anyone know that there's something in master to migrate?
What happens if your objects corrupt the master database?
What adverse affects would having extra data in master have on the instance?
What would happen if an in-place upgrade was performed on this instance? (ie, upgrading 2008 to 2008 R2)
How would restoring the entire instance from backups be affected?

Most of these I don't know the answer to, but they're questions that come up when I contemplate putting objects in master.


Extract a number from nvarchar column Hoping you can help me. I have a column in a table called indicator. (Data type: NVarchar(80))

Within this column I have some numbers the length of this number is 6 characters in length. The total the Length of indicator column can vary from 0 to 56 Characters long.

Below is small Subset of the data. What I would like to do is produce another column which extracts the numbers from indicator column.

Row 1 : GB-JP--123456
Row 2 : 401015 - GB-NA
Row 3 :
Row 4 : 999999 - LX - UK

I tried the following query but I get this error

select Substring (Indicator, PATINDEX('%[0-9]%', Indicator), LEN(Indicator)) from Table1

However I am presented with the following error

  "SUBSTRING": invalid identifier"

However I am presented with the following error

  "SUBSTRING": invalid identifier"

The provided query should be able to run, here is a test on SQL Server 2017

Unless your tag is wrong and you are using oracle?

Then you would get an error like this:

  ORA-00904: "SUBSTRING": invalid identifier

DB<>Fiddle

If that is the case, then you could use regexp_replace instead.

select regexp_replace(indicator, '[^0-9]', '') from table1

Resulting in

123456
401015

999999

DB<>Fiddle

Solution for SQL Server

  Below is small Subset of the data. What I would like to do is produce
  another column which extracts the numbers from indicator column.

If you want to get the numbers from the column and they are always one series of continuous numbers, you could do this

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING (Indicator, PATINDEX('%[0-9]%', Indicator), LEN(Indicator) )), PATINDEX('%[0-9]%', REVERSE(Indicator)), LEN(Indicator) ))
FROM table;

Table & Data

create table #temp
(
indicator nvarchar(80)
);

INSERT INTO #temp
VALUES
('GB-JP--123456'),
('401015 - GB-NA'),
('1'),
('999999 - LX - UK');

Result

(No column name)
123456
401015
1
999999

DB<>Fiddle

Remember that due to using the LEN() function, trailing spaces are a potential issue.

INSERT INTO #temp
('1   ')

Returns an empty string when using above query.

DATALENGTH() can resolve this (divided by 2 due to the nvarchar datatype, not that it really matters when using SUBSTRING like this)

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING (Indicator, PATINDEX('%[0-9]%', Indicator),( DATALENGTH(Indicator)/2))), PATINDEX('%[0-9]%', REVERSE(Indicator)),( DATALENGTH(Indicator)/2) ))
from #temp;

Comparison DB<>Fiddle


left join query running too long I have two tables, test1 and test2, and these tables have around 600M(70GB) and 10k(2MB) records, respectively. when I run a query with left join on site_id, it is taking too long, even though site_id has an index in both tables.table1.rank also indexed.

Added tables structure here db<>fiddle

EXPLAIN Query:

explain SELECT count(*) FROM test1 t1
   left join test2 t2
   on t1.site_id =t2.site_id where rank=1;

Execution plan:

"  ->  Gather  (cost=273235.26..273235.77 rows=5 width=8)"
"        Workers Planned: 5"
"        ->  Partial Aggregate  (cost=272235.26..272235.27 rows=1 width=8)"
"              ->  Parallel Seq Scan on test t1  (cost=0.00..267015.64 rows=2087846 width=0)"
"                    Filter: (rank = 1)"

Execution plan with EXPLAIN (ANALYZE, BUFFERS):

"Finalize Aggregate  (cost=273235.78..273235.79 rows=1 width=8) (actual time=625.486..625.486 rows=1 loops=1)"
"  Buffers: shared hit=233204"
"  ->  Gather  (cost=273235.26..273235.77 rows=5 width=8) (actual time=624.638..700.069 rows=6 loops=1)"
"        Workers Planned: 5"
"        Workers Launched: 5"
"        Buffers: shared hit=233204"
"        ->  Partial Aggregate  (cost=272235.26..272235.27 rows=1 width=8) (actual time=436.761..436.762 rows=1 loops=6)"
"              Buffers: shared hit=233204"
"              ->  Parallel Seq Scan on test1 t1  (cost=0.00..267015.64 rows=2087846 width=0) (actual time=0.014..336.926 rows=1739383 loops=6)"
"                    Filter: (rank = 1)"
"                    Rows Removed by Filter: 514726"
"                    Buffers: shared hit=233204"
"Planning time: 0.120 ms"
"Execution time: 700.117 ms"

Execution plan with a partition on test1, EXPLAIN (ANALYZE, BUFFERS):

"Finalize Aggregate  (cost=233119.25..233119.26 rows=1 width=8) (actual time=999.316..999.316 rows=1 loops=1)"
"  Buffers: shared hit=179937"
"  ->  Gather  (cost=233118.73..233119.24 rows=5 width=8) (actual time=998.124..1054.495 rows=6 loops=1)"
"        Workers Planned: 5"
"        Workers Launched: 5"
"        Buffers: shared hit=179937"
"        ->  Partial Aggregate  (cost=232118.73..232118.74 rows=1 width=8) (actual time=796.767..796.768 rows=1 loops=6)"
"              Buffers: shared hit=179937"
"              ->  Result  (cost=0.00..226900.55 rows=2087269 width=0) (actual time=0.014..649.061 rows=1739383 loops=6)"
"                    Buffers: shared hit=179937"
"                    ->  Append  (cost=0.00..206027.86 rows=2087269 width=6) (actual time=0.014..515.933 rows=1739383 loops=6)"
"                          Buffers: shared hit=179937"
"                          ->  Parallel Seq Scan on test1_p1 t1  (cost=0.00..206027.86 rows=2087269 width=6) (actual time=0.012..415.959 rows=1739383 loops=6)"
"                                Filter: (rank = 1)"
"                                Buffers: shared hit=179937"
"Planning time: 0.254 ms"
"Execution time: 1054.547 ms"

PostgreSQL optimizes the query so that the left join is completely ignored, so that doesn't factor in the question at all.

You didn't provide EXPLAIN (ANALYZE, BUFFERS) results, but either you don't have an index on test1.rank, or PostgreSQL considers the condition not selective enough.

One thing you can try is to create an index on test1.rank and VACUUM test1, then perhaps you can get a faster index-only scan.


query to find closest lesser date I have a table with rates.  each rate has an effective starting date.  (the ending date of any rate is implied by the existence of a record with a newer effective starting date.)
I also have a table with activities.  Each activity occurs on a date.

What is the best way to match an activity to the rate that was effective on that date?

(unfortunately I don't have the ability to modify the data structure, so adding an explicit ending date for a rate is out.)

rates:
StartDate Rate
9/1/2010    17.00
10/1/2010   18.70
11/1/2010   20.00

Activities:
WorkCenter  ActionDate Hours
WC1         9/30/2010   10
WC1         10/1/2010   5
WC2         10/30/2010  8
WC2         11/3/2010   9

Desired result:
Workcenter ActionDate   Hours   Rate    Cost(=rate*hours)
WC1         9/30/2010   10      17.00   170.00
WC1         10/1/2010   5       18.70    93.50
WC2         10/30/2010  8       18.70   149.60
WC2         11/3/2010   9       20.00   180.00

SELECT
    a.WorkCenter
  , a.ActionDate
  , a.Hours
  , r.Rate
  , r.Rate * a.Hours  AS Cost
FROM
    Activities AS a
  JOIN
    Rates AS r
      ON  r.StartDate =
          ( SELECT MAX(StartDate)
             FROM Rates
             WHERE StartDate <= a.ActionDate
          ) ;

or a GROUP BY solution:

SELECT
    a.WorkCenter
  , a.ActionDate
  , a.Hours
  , r.Rate
  , r.Rate * a.Hours  AS Cost
FROM
    Activities AS a
  JOIN
    ( SELECT
          a.ActionDate
        , MAX(r.StartDate) AS StartDate
      FROM
          Activities AS a
        JOIN
          Rates AS r
            ON r.StartDate <= a.ActionDate
      GROUP BY a.ActionDate
    ) AS grp
      ON  grp.ActionDate = a.ActionDate
 JOIN
    Rates AS r
      ON  r.StartDate = grp.StartDate ;