Database Design: Storing Map Locations When working on a game, I developed a database that used X/Y coordinates, roughly as:

CREATE TABLE Map
(
    MapId INT IDENTITY PRIMARY KEY,
    X INT NOT NULL,
    Y INT NOT NULL,
    North INT FOREIGN KEY REFERENCES Map(MapId),
    South INT FOREIGN KEY REFERENCES Map(MapId),
    East INT FOREIGN KEY REFERENCES Map(MapId),
    West INT FOREIGN KEY REFERENCES Map(MapId)
);

(I'm not at my editor, so that may have syntax errors.)

I then implemented the path searching algorithm (A*, specifically) inside a stored procedure.

Is there a better way to implement this coordinates system?
I had implemented it in SQL Server, since that is what I had on hand and what I was familiar with.  Is there anything in another database system that would've simplified this problem?

It may make a difference to know that this, being a game, is strictly a grid system. That may simplify (or complicate) the problem.

Edit:

Some sample data:

map_id      x           y           north       east        south       west
----------- ----------- ----------- ----------- ----------- ----------- -----------
20          502         501         26          33          21          18
21          502         502         20          22          87          19
22          503         502         33          23          88          21

If the game area is strictly static, then my preference would be to use the coordinates as the primary key.

On postgres, I'd probably do that using a composite type. I don't think there is a direct equivalent in SQL Server - hopefully someone will correct me if I'm wrong.

Whether this will simplify the problem or not depends on how exactly you have implemented your searching algorithm - can you post more details on that?


Create a user with privileges to create users For our software we need a MySQL user who is able to create other MySQL users. This is possible with the MySQL root user but we do not want to use the root user in our software application. Is there a way to create a MySQL user which can create other users?

I read multiple topics and pages on the internet which say it is possible with the GRANT OPTION. But when I check the MySQL permission description, this permission is "The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess." So provides not the option to create users, only give them permissions that you have.

Maybe I understand something wrong but I'm afraid to do something wrong on our server. That's also the reason for my question.

I hope someone can explain to me if it's possible, and how, to create a user with the privileges we need.

To be able to create mysql users, you need to be able to insert into the users table in the mysql database. So create your master user and give him write access to the mysql database.

The documentation states ( http://dev.mysql.com/doc/refman/5.7/en/create-user.html )

  To use CREATE USER, you must have the global CREATE USER privilege or
  the INSERT privilege for the mysql database. When the read_only system
  variable is enabled, CREATE USER additionally requires the SUPER
  privilege.

So the process would be something like this:

(root)> create user masteruser ...
(root)> grant CREATE_USER to masteruser ...
(masteruser)> create user foo1 ...
(masteruser)> create user foo2 ...

Also, it is deemed good practice to use a test system to test such behaviour; so that when you mess up, there is no harm done to a actual real database.


Removing replication I want to remove replication as we are not going to use it further. I want to know what steps should we follow to remove the replication like:

Which jobs to stop
Delete subscription and publisher

...and any other things to consider. I am doing it for the first time, so I need help regarding this.

If I understand correctly you want to totally remove replication from the server.

There are a number of tools you can use, as outlined here for limited dismantling of replication.

Removing Replication

If you just want to strip all replication from the server, you can apparently use sp_removedreplication on each database that needs to have replication dropped.

sp_removedbreplication (Transact-SQL) on the appropriate database

The code is simple.  Here is the MSDN example code:

-- Remove replication objects from the subscription database on MYSUB.
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorks2008R2Replica'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO


Why changing the order of network protocols does not make any sense? All I want to do is to connect to SQL Server though Named Pipes without using an explicit way like NP:ServerName because mentioned approach works.  I want to connect through Named Pipes using just ServerName and give a SQL Server a chance to choose the right network protocol. So I have 3 VMs:

What I did step by step:

In Server Configurations of SQL Server A I enabled all network protocols.

I am also familiar with the Order column, however, changing it appears to have no affect. I swapped the order of TCP/IP and Named Pipes:

I tried to connect from SQL Server B into SQL Server A using Computer Name(WIN-VKHOKLJ3IJG) and I hoped that it will use Named Pipes protocol but unfortunately it chose TCP/IP as a connection protocol:

Could someone explain why TCP/IP was chosen instead of Named Pipes? According the order, SQL Server should choose Named Pipes but it rather preferred to use TCP/IP.

I believe the issue has to do with what client is used when establishing connectivity to Server B from Server A rather than how you order your protocol order on the server itself.

The first hint comes from Choosing a Network Protocol: Enabling the Protocol (highlighting mine):

  The protocol must be enabled on both the client and server to work.
  The server can listen for requests on all enabled protocols at the
  same time. Client computers can pick one, or try the protocols in
  the order listed in SQL Server Configuration Manager.

I suspect most clients determine the desired order of the protocols being used rather than delegate that to the server.

Further digging through MS documentation yields this interesting note in the Client Protocols Properties (Order Tab) article:

  Note

  These settings (e.g. Order) are not used by Microsoft .NET SqlClient. The protocol
  order for .NET SqlClient is first TCP, and then named pipes, which
  cannot be changed.

As we can see for the .NET SqlClient, if TCP/IP is an enabled protocol on the server and the .NET SqlClient is being used, it doesn't matter what you do, you're getting a TCP/IP connection.  So with this client in particular the protocol specified on the server most definitely doesn't matter.

With these examples, I think it's pretty clear that the client you're using to connect to a server has more bearing on the protocol being used rather than the connection protocol ordering on the server itself.

In your specific case, if you really want to use Named Pipes instead of TCP/IP, you may be able to force it via a Client Alias:

You would need to create this alias on Server A for Server B.  Just note that aliases come with their own headaches (e.g. you've now got another thing to manage whenever you deal with connectivity changes on Server B), but as a workaround, it may do the trick.

Also of note, Named Pipes can become problematic on slow connections where the same issues are not as impacting via TCP/IP connections, which is likely why most clients gravitate toward the TCP/IP protocol by default.


Is there a way to find my.cnf in shared hosting? I'm curious is there a way to find my.cnf in shared hosting or is this file can be configured just by DBAs and no one can set his/her own settings?

In a shared hosting environment, you cannot access my.cnf

You can see the variables see other ways:

SHOW VARIABLES

SHOW VARIABLES; will show all variables that are set. Some can be dynamically changed within you session with the SET command. Some can be set in the server so that new DB Connections going forward can use the newly set value.

SHOW VARIABLES LIKE 'innodb%' shows all innodb settings
SHOW VARIABLES LIKE '%timeout' shows all timeout setting
SHOW VARIABLES LIKE '%buffer%' shows all buffers

INFORMATION_SCHEMA

You can also access the variables by means of the INFORMATION_SCHEMA database. The tables to access those values are

INFORMATION_SCHEMA.GLOBAL_VARIABLES
INFORMATION_SCHEMA.SESSION_VARIABLES

The same requests using these tables are

SHOW VARIABLES; becomes SELECT * FROM information_schema.global_variables;
SHOW VARIABLES LIKE 'innodb%' becomes SELECT * FROM information_schema .global_variables WHERE VARIABLE_NAME LIKE 'innodb%';
SHOW VARIABLES LIKE '%timeout' becomes SELECT * FROM information_schema .global_variables WHERE VARIABLE_NAME LIKE '%timeout';
SHOW VARIABLES LIKE '%buffer%' becomes SELECT * FROM information_schema .global_variables WHERE VARIABLE_NAME LIKE '%buffer%';

The reason INFORMATION_SCHEMA.SESSION_VARIABLES exists is to see the setting your session has, in the event you change a variable during the session to something different form the global setting.

Examples

SET GLOBAL max_connections = 1000; will now allow 1000 DB Connections
SET sort_buffer_size = 1024 * 1024 * 8; sets your session sort_buffer_size to 8M
SET GLOBAL sort_buffer_size = 1024 * 1024 * 8; sets session sort_buffer_size to 8M for all incoming DB Connections going forward

CAVEAT

Please keep in mind that not all settings can be dynamically changed. Some values, such as sort_buffer_size (session or global) and max_connections (global only [session is N/A]), such as innodb_buffer_pool_size and lower_case_table_names, can only be changed by setting it in my.cnf and restarting mysqld.

Click here for the List of Variables, their defaults, and their session chacateristics.


RMAN error RMAN-06207,RMAN-06208,RMAN-06210,RMAN-06211,RMAN-06212,RMAN-06213,RMAN-06214 I have this issue with around 7 servers, we have an RMAN job that takes backup and  validate the backup ...

here is RMAN settings:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name A103775 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 21 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=C:\RMAN\tdpo_A103775.opt)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFA103775.ORA'; # default

here is the backup job:

connect target backupuser/backuppassword
connect catalog rman/rmanpassword@rcat
run
{
allocate channel tdpo type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=C:\RMAN\tdpo_ADATABASE.opt)';
backup database plus archivelog delete all input TAG 'Full Database Backup';
delete noprompt obsolete;
restore database validate;
release channel tdpo;
}

and I get this error message!

RMAN-06207: WARNING: 2 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    papcfat0_1_1
RMAN-06214: Backup Piece    c-317588623-20140703-03

I tried crosscheck and delete obsolete, delete expired ... and it did not help!

Use crosscheck copy before deleting:

RMAN>crosscheck copy
RMAN>delete noprompt obsolete;

Crosscheck on it's own (without copy) doesn't do anything.  You could also have done:

crosscheck backuppiece papcfat0_1_1


PostgreSQL: Generate a series of dates for each group in a table I have a balances table in PostgreSQL 9.3 that looks like this:

CREATE TABLE balances (
  user_id INT
, balance INT
, as_of_date DATE
);

INSERT INTO balances (user_id, balance, as_of_date) VALUES
  (1, 100, '2016-01-03')
, (1,  50, '2016-01-02')
, (1,  10, '2016-01-01')
, (2, 200, '2016-01-01')
, (3,  30, '2016-01-03');

It only contains balances for dates that a user has made a transaction. I need it to contain a row for each user with their balance on each date in a given date range.

If the user does not have a row for a given date in the range, I need to use their balance from the day before.
If the user created their account after a given date in the range, I need to avoid creating a row for that user/date combination.

I can reference an accounts table to get users' create_date:

CREATE TABLE accounts (
  user_id INT
, create_date DATE
);

INSERT INTO accounts (user_id, create_date) VALUES
  (1, '2015-12-01')
, (2, '2015-12-31')
, (3, '2016-01-03');

My desired result looks like this:

+---------+---------+--------------------------+
| user_id | balance |        as_of_date        |
+---------+---------+--------------------------+
|       1 |     100 | 2016-01-03T00:00:00.000Z |
|       1 |      50 | 2016-01-02T00:00:00.000Z |
|       1 |      10 | 2016-01-01T00:00:00.000Z |
|       2 |     200 | 2016-01-03T00:00:00.000Z |
|       2 |     200 | 2016-01-02T00:00:00.000Z |
|       2 |     200 | 2016-01-01T00:00:00.000Z |
|       3 |      30 | 2016-01-03T00:00:00.000Z |
+---------+---------+--------------------------+

Note that rows have been added for user 2 for 2016-01-02 and 2016-01-03, carrying over the previous balance from 2016-01-01; and that no rows have been added for user 3, who was created on 2016-01-03.

To generate a series of dates in a date range, I know I can use:

SELECT d.date FROM GENERATE_SERIES('2016-01-01', '2016-01-03', '1 day'::INTERVAL) d

...but I am struggling with LEFT JOINing that series with each set of rows grouped by user_id.

1. CROSS JOIN, LEFT JOIN LATERAL to subquery
SELECT a.user_id, COALESCE(b.balance, 0) AS balance, d.as_of_date
FROM   (
   SELECT d::date AS as_of_date  -- cast to date right away
   FROM   generate_series(timestamp '2016-01-01', '2016-01-03', interval '1 day') d
   ) d
JOIN   accounts a ON a.create_date <= d.as_of_date
LEFT   JOIN LATERAL (
   SELECT balance
   FROM   balances
   WHERE  user_id = a.user_id
   AND    as_of_date <= d.as_of_date
   ORDER  BY as_of_date DESC
   LIMIT  1
   ) b ON true
ORDER  BY a.user_id, d.as_of_date;

Returns your desired result - except that as_of_date is an actual date, not a timestamp like in your example. That should be more appropriate.
Users that are created already, but don't have any transactions, yet, are listed with a balance of 0. You did not define how to deal with the corner case.
Rather use timestamp input for generate_series():

Generating time series between two dates in PostgreSQL

It's crucial for performance that you back this up with a multicolumn index:
CREATE INDEX balances_multi_idx ON balances (user_id, as_of_date DESC, balance);

We have had a very similar case on SO just this week:

Aggregating the most recent joined records per week

Find more explanation there.
2. CROSS JOIN, LEFT JOIN , window functions
SELECT user_id
     , COALESCE(max(balance) OVER (PARTITION BY user_id, grp
                                   ORDER BY as_of_date), 0) AS balance
     , as_of_date
FROM  (
   SELECT a.user_id, b.balance, d.as_of_date
        , count(b.user_id) OVER (PARTITION BY user_id ORDER BY as_of_date) AS grp
   FROM   (
      SELECT d::date AS as_of_date  -- cast to date right away
      FROM   generate_series(timestamp '2016-01-01', '2016-01-03', interval '1 day') d
      ) d
   JOIN   accounts a ON a.create_date <= d.as_of_date
   LEFT   JOIN balances b USING (user_id, as_of_date)
   ) sub
ORDER  BY user_id, as_of_date;

Same result. If you have the multicolumn index mentioned above and can get index-only scans out of it, the first solution is most probably faster.
The main feature is the running count of values to form groups. since count() does not count NULL values, all dates without balance fall into the same group (grp) as the most recent balance. Then use a simple max() over the same window frame extended by grp to copy the last balance for dangling gaps.
Related:

Select longest continuous sequence
How to get max sequence of rows?


Selecting one row from each group based on a Varchar field Given a table Avenger whose data looks like this:

id  Avenger         Type    Power
1   Captain America foo      2
2   Captain America bar      3
3   Me              foo      5
4   You             foo      1
5   You             bar      7
6   Iron Man        foo      4

I want to return a distinct result set of the Avengers, although all other data in the table is relevant to my processing interests.  For business reasons I want to return Type "bar" type records instead of "foo" where applicable.

I can do this in two steps, certainly:

declare @avenger table
(
    id int,
    Avenger varchar(50),
    [Power] varchar(50),
    [Type] varchar(50)
)

insert into @avenger
select * from Avenger where Type = 'bar'

insert into @avenger
select t.ID, t.Avenger, t.Type, t.Power from Avenger as t
where t.Avenger not in (select Avenger from @avenger)

And then just return my result set from @avenger. Because I need the other fields from Avenger (e.g. Power) a UNION seems out of the question.  Type is varchar data and while there are 2 now there might be more in the future, so I'm uncertain about trying to sort it based on alpha or anything like that.  Is there some better/preferred way to do this?

You can do something like this:

select * from (
  select *, row_number() over (partition by Avenger order by Type) as RN
) X
where RN = 1

This will number the rows for each avenger, and for your actual business case you'll most likely need to use a case statement in the order by if there's more than 2 different values, to define their precedence:

....order by case Type when 'bar' then 1 when 'foo' then 2 else 3 end


Query External LDAP Server from SQL Server I have done extensive research on querying LDAP Server but so far has not been able to work around. Therefore, asking experts help

Background: I have a client whose LDAP Server is OnPremise (though facing publicly) and I have a SQL Server(2012 SE) inside the firewall. The communication between the External LDAP and Internal SQL Server are fine as I can ping each other. I also created a remote domain account and used this account when created Linked Server on SQL Server. I also have our internal Domain controller that has Domain Trust Relationship. Now, I have created 2 Linked Servers using Domain account but I have not been able to query LDAP Server on both internally and externally. I hope this is making sense. Anyways, Here are the definitions of my Linked Server and errors I am receiving

[Solved] Linked Server to an Internal LDAP Server: This was resolved by using ADuser@domain.com instead of domain/ADuser

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active
Directory Service Interfaces', @provider=N'ADSDSOObject',
@datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Domain/ADUser',@rmtpassword='########'

Error:

  Msg 7321, Level 16, State 2, Line 1
  An error occurred while preparing the query "SELECT *
  FROM  'LDAP://Domain.com/OU=Accounts,DC=Domain,DC=com'
  --WHERE objectClass =  'User'
  " for execution against OLE DB provider "ADSDSOObject" for linked server
  "ADSI".

Linked Server to an External LDAP Server

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ADSIE', @srvproduct=N'Active
Directory Service Interfaces', @provider=N'ADSDSOObject',
@datasrc=N'CustomerIPAdress'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Domain/ADUser',@rmtpassword='########'

Error:

  Msg 7399, Level 16, State 1, Line 1
  The OLE DB provider "ADsDSOObject" for linked server "ADSIE" reported an >error. The provider indicates that the user did not have the permission to >perform the operation.
  Msg 7321, Level 16, State 2, Line 1
  An error occurred while preparing the query "SELECT *
    FROM 'LDAP://Customer IP Adress'
    WHERE objectClass = 'User'
    " for execution against OLE DB provider "ADsDSOObject" for linked server >"ADSIE".

Any help would be appreciated.Thanks in advance.

What stands out the most in your second error message is the message below:

  The provider indicates that the user did not have the permission to perform the operation

This tells me that the user in your linked server configuraiton doesn't have access to the AD / LDAP server to perform that query. Here is my ADSI configurations along with an example query. You may need to engage your SA team to ensure the account configured in your linked server has the correct access to query AD / LDAP.

    USE [master]
GO

/****** Object:  LinkedServer [ADSI]    Script Date: 11/10/2017 3:46:31 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\accountname',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Here is an example of using the the ADSI linked server using openquery, but you need to understand the OU structure and the domain structure of AD before building the LDAP connection string.

SELECT sn, displayName, givenName, mail, telephoneNumber, ipPhone, sAMAccountName
FROM OPENQUERY(ADSI,
'SELECT sAMAccountName, displayName, telephoneNumber, ipPhone, mail, givenName, sn
FROM ''LDAP://DC=test,DC=intranet,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')
AS derivedtbl_1

NOTE: I changed the DC name in the LDAP string for security reasons, but you should be able to get the idea.


Possible to have a statement in stored procedure outside of current transaction? During a series of statements in my stored procedure I wish to log an entry by inserting a record. That record needs to survive even if the overall SP ends up rolling back.

Is this possible?

Declare the log table to be ENGINE=MyISAM.


Replace the server name in email sender address when sending email from SQL Server 2008 I'm sending emails directly to Microsoft Outlook accounts from SQL Server 2008. Everything is fine, email was sent. Only problem is that in the header of the email the SQL Server name is embedded.

I've tried specifying the @from_address when executing the mail sending in DB but the sql server name is still there in the email's header. I don't want my recipients to see the sql server name in the header.

Is there a way to block this?

Thanks much!

Try creating a new database mail profile and database mail account. Put the email address you desire as the name of the new mail account. Then use the profile when calling the stored procedure to send email:
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = "DB Mail Profile"

Cheers!


Problem with order by in MySQL subquery I'm running a query that runs in about 2 seconds when the subquery does not include the order by date_sold clause. However, when I add the order by clause, the query runs forever.

I'm confused, since it seems that I'm using a index for both queries, and as I've understood, the subquery should be able to use the index for the subquery. Any ideas where I went wrong?

EXPLAIN Select buyer_id as bid,
              (SELECT seller_id
               from sale USE INDEX(tester)
               where buyer_id = bid
               order by date_sold LIMIT 1)
         from sale where seller_id = 35514335;
+----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+
| id | select_type        | table | type  | possible_keys                     | key    | key_len | ref   | rows  | Extra                    |
+----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+
|  1 | PRIMARY            | sale  | ref   | seller_id,seller_id_index_asc,sub | sub    | 8       | const | 14933 | Using index              |
|  2 | DEPENDENT SUBQUERY | sale  | index | NULL                              | tester | 24      | NULL  |     1 | Using where; Using index |
enter code here

The table structure

| id               | int(11)    | NO   | PRI | NULL    | auto_increment |
| seller_id        | bigint(20) | NO   | MUL | NULL    |                |
| buyer_id         | bigint(20) | NO   | MUL | NULL    |                |
| date_acquired    | bigint(20) | NO   |     | NULL    |                |
| date_sold        | bigint(20) | NO   | MUL | NULL    |                |
| brand            | text       | NO   | MUL | NULL    |                |
| muncipality_code | bigint(20) | NO   | MUL | NULL    |                |
| new              | int(11)    | YES  |     | NULL    |                |
| car_regnumber    | varchar(6) | YES  |     | NULL    |                |
| gender           | varchar(1) | YES  |     | NULL    |                |

Tester index

| sale  |          1 | tester              |            1 | date_sold        | A         |       14840 |     NULL | NULL   |      | BTREE      |         |
| sale  |          1 | tester              |            2 | buyer_id         | A         |    11768564 |     NULL | NULL   |      | BTREE      |         |
| sale  |          1 | tester              |            3 | seller_id        | A         |    11768564 |     NULL | NULL   |      | BTREE      |         |

SHOW CREATE TABLE SALE

| sale  | CREATE TABLE `sale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) NOT NULL,
  `buyer_id` bigint(20) NOT NULL,
  `date_acquired` bigint(20) NOT NULL,
  `date_sold` bigint(20) NOT NULL,
  `brand` text NOT NULL,
  `muncipality_code` bigint(20) NOT NULL,
  `new` int(11) DEFAULT NULL,
  `car_regnumber` varchar(6) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `muncipality_code` (`muncipality_code`),
  KEY `brand` (`brand`(10)),
  KEY `seller_id` (`seller_id`),
  KEY `seller_id_index_asc` (`seller_id`),
  KEY `date_sold` (`date_sold`),
  KEY `tester` (`date_sold`,`buyer_id`,`seller_id`)
) ENGINE=MyISAM AUTO_INCREMENT=66390336 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED |

SUGGESTION #1 : Use a Different Index
ALTER TABLE sale ADD INDEX tester2 (seller_id,buyer_id,date_sold);

I suggest this order because seller_id in the query is a constant (35514335). That would make looking up all buyer_id values.
SUGGESTION #2 : Redesign the Query
From the looks of the query, you are retrieving first date_sold for buyer_id for seller_id 35514335.
SELECT buyer_id,MIN(date_sold) date_sold
FROM sale WHERE seller_id = 35514335
GROUP BY buyer_id;

If seller_id 35514335 has buyers who purchased from other sellers as well, then do this:
SET @given_seller_id = 35514335;
SELECT DISTINCT A.buyer_id,B.seller_id FROM
(
    SELECT buyer_id,MIN(date_sold) date_sold
    FROM sale WHERE seller_id = @given_seller_id
    GROUP BY buyer_id
) A LEFT JOIN sale B USING (buyer_id);

If you do not want to include seller_id 35514335 in the output, then do this:
SET @given_seller_id = 35514335;
SELECT DISTINCT A.buyer_id,B.seller_id FROM
(
    SELECT buyer_id,MIN(date_sold) date_sold
    FROM sale WHERE seller_id = @given_seller_id
    GROUP BY buyer_id
) A LEFT JOIN sale B USING (buyer_id)
WHERE B.seller_id <> @given_seller_id;

Give it a Try !!!


SQL Server Stolen Server Memory - what it consists of? Below what my server's performance counters show:

Questions:

1) What "Stolen Server Memory" consists of ?
I can assume "Plan Cache", "Lock Memory", "SQL Cache", "Connection", "Optimizer" are included into "Stolen Server Memory", but what else is included ?

2) How can I query rest of things that are included into "Stolen Server Memory" ?

What "Stolen Server Memory" consists of ?

I will quote from my answer given on This Thread.

  Stolen memory describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.

Plan cache is not part of stolen memory.

  How can I query rest of things that are included into "Stolen Server Memory" ?

AFAIK their is no query to tell what "actually" stolen memory consists and how much. You can run DBCC MEMORYSTATUS() command to see the value of stolen memory.


Aggregating data in .net SQL I'm converting a database originally written in MS Access VBA to .net and many of the SQL statements that worked in Access don't work in .net.  My data set is standard stock market daily information with columns for Date, Open, High, Low, Close, and AdjustedClose.  I want to aggregate the daily data by week (WeekEnding).  In Access I was able to add to a Weekly Table, calculate the WeekEnding date and aggregate the data in a single query:

SELECT (WeekEnding Calc) as WeekEnding, Max(High), Min(Low), First(Close)

Calculating the Week Ending date within a SQL statement seems to be a non-starter in Visual Studio so I added a column for the Week Ending date and calculated that separately.  The First statement worked since the daily data was sorted in descending order by date but is not an option now.  What I'm looking for is something like:

SELECT WeekEnding, Max(High), Min(Low), ????(Close)
FROM DailyTable
GROUP BY WeekEnding

I haven't been able to figure out how to get the value of (Close) based on the maximum DailyDate within each group.  I'm sure this has been solved for many times in the past and would appreciate any help.

For Close, you can use the LAST_VALUE window function to get the last value from a data set (assuming this is the Close value from the last day of the week). You can then put this in an inner select statement and do a simple aggregation on the outer statement to return your intended results.

LAST_VALUE

Note, the below example assumes a last day of the week as Saturday. If this is a different day, look at using SET DATEFIRST to set your preferred day of the week as the start.

Setup:

CREATE TABLE #DailyTable (DateLogged DATETIME,
    High DECIMAL(10,2),
    Low DECIMAL(10,2),
    [Close] DECIMAL(10,2)
)

INSERT INTO #DailyTable (DateLogged, High, Low, [Close])
VALUES ('2019-05-31 00:00:00.000', 2.12, 1.06, 1.59)
,('2019-06-01 00:00:00.000', 2.44, 1.22, 1.83)
,('2019-06-02 00:00:00.000', 0.84, 0.42, 0.63)
,('2019-06-03 00:00:00.000', 0.84, 0.42, 0.63)
,('2019-06-04 00:00:00.000', 1.24, 0.62, 0.93)
,('2019-06-05 00:00:00.000', 1.12, 0.56, 0.84)
,('2019-06-06 00:00:00.000', 0.16, 0.08, 0.12)
,('2019-06-07 00:00:00.000', 3.00, 1.50, 2.25)
,('2019-06-08 00:00:00.000', 3.52, 1.76, 2.64)
,('2019-06-09 00:00:00.000', 3.40, 1.70, 2.55)
,('2019-06-10 00:00:00.000', 3.36, 1.68, 2.52)
,('2019-06-11 00:00:00.000', 2.04, 1.02, 1.53)
,('2019-06-12 00:00:00.000', 3.32, 1.66, 2.49)
,('2019-06-13 00:00:00.000', 3.16, 1.58, 2.37)
,('2019-06-14 00:00:00.000', 0.12, 0.06, 0.09)

Query:

SELECT
    MAX(WeekEnd) AS WeekEnding,
    MAX(High) AS High,
    MIN(Low) AS Low,
    MAX([Close]) AS [Close]
FROM
(
SELECT DATEPART(WEEK, DateLogged) YearWeek,
    LAST_VALUE(DateLogged) OVER (PARTITION BY DATEPART(WEEK, DateLogged) ORDER BY DATEPART(WEEK, DateLogged)) AS WeekEnd,
    High,
    Low,
    LAST_VALUE([Close]) OVER (PARTITION BY DATEPART(WEEK, DateLogged) ORDER BY DATEPART(WEEK, DateLogged)) AS [Close]
FROM #DailyTable dt
) src
GROUP BY YearWeek

Results:

WeekEnding                  High    Low     Close
-------------------------------------------------
2019-06-01 00:00:00.000     2.44    1.06    1.83
2019-06-08 00:00:00.000     3.52    0.08    2.64
2019-06-14 00:00:00.000     3.40    0.06    0.09


SELECT from tableA if not in tableC I have a view, which selects data from TableA (TableA will be decommissioned once we migrated it all). We are creating a new table, called TABLEC, where we'll be migrating all the data from TableA to TableC in the future.

The reason we are doing this, it's because tableA is very old and bad designed.
At the moment, tableA is being used only for old records. All new data that would be inserted into tableA is already going to tableC via application.
I cannot change any table's schema.
The view can be changed to use CTE, IF, it is at least as fast as it's now.
path is not a real path, but it's a regular expression. It will be something like: /file/account/[0-9]+ <-- after this there are some possibilites, it can be several different words (such as: attach/assigned/etc).

While we don't migrate all the data from tableA to tableC, we need the view to be compatible with both tables, as there is already new data going to tableC.

View:

CREATE OR REPLACE VIEW path_view AS
  SELECT split_part(n1.path::text, '/'::text, 18)::integer AS id,
         split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
         lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
         '00000000-1000-1000-3000-600000000000'::uuid AS guid,
         n1.bytes AS byte_count,
         n1.last_modified AS last_modified
  FROM   tablea n1
  JOIN   tableb s2
  ON     s2.path = n1.path;

-- tablea and tableb are pretty much the same; as said before, it was bad designed.

I need the view to do:

Look if the requested data is in the new table tableC
IF NOT, then go to tableA and get the data

QUESTION:

How can I do that? I tried with UNION ALL but it's very slow.

\d tableC:

Table "public.tablec"
--------------------+-----------------------------+-------------------------------------------------------------------
id            | integer                     | not null default nextval('tablec_id_seq'::regclass)
e_type        | integer                     | not null
e_id          | bigint                      |
e_variation   | character varying(16)       | not null
path          | character varying(255)      | not null
name          | character varying(255)      | not null
size          | bigint                      | not null
md5           | md5_hash                    | not null
modified_date | timestamp without time zone | default statement_timestamp()
created_date  | timestamp without time zone | default statement_timestamp()
clientid      | bigint                      | not null
f_id          | bigint                      |

IMHO I think that the fast way on this case is looking first at TableC, and if not exists on TableC, search the record on TableA/B.

I've set up a small example:

create table tableA (id int, path varchar(30), other_things int);
create table tableB (id int, path varchar(30), other_things int);
create table tableC (id int, path varchar(30), other_things int);
insert into tableA values
(1, '/file1/account1/0001', 1),(2, '/file1/account2/0001', 1),
(3, '/file1/account1/0002', 1),(4, '/file2/account1/0002', 1),
(5, '/file1/account2/0003', 1),(6, '/file2/account1/0003', 1);
insert into tableB values
(1, '/file1/account1/0001', 1),(2, '/file1/account2/0001', 1),
(3, '/file1/account1/0002', 1),(4, '/file1/account2/0002', 1),
(5, '/file2/account1/0003', 1),(6, '/file1/account1/0003', 1);
insert into tableC values
(1, '/file3/account3/0004', 1),(2, '/file3/account4/0004', 1),
(3, '/file4/account3/0005', 1),(4, '/file3/account4/0005', 1),
(5, '/file3/account3/0006', 1),(6, '/file4/account3/0006', 1);

As far as you are using a regular expression /file/account/[0-9]+ to filter records, you could use the operator ~. (POSIX Regular Expressions)

But have a look at this answer of Erwin Brandstetter about using regular expressions, just in case you could add some extra index to your table.

I've used a function because it allows me to use IF EXISTS (). It only search the record on TableA if it doesn't exists on TableC. Obviously you can change the search order.

CREATE OR REPLACE FUNCTION getABC (MyPath VARCHAR)
 RETURNS TABLE (
 ID text,
 client_id text,
 other int
)
AS $$
BEGIN

   IF EXISTS(SELECT 1 FROM TableC WHERE path ~ $1) THEN
        RETURN QUERY SELECT split_part(t1.path::text, '/'::text, 2)::text AS id,
                            split_part(t1.path::text, '/'::text, 3)::text AS clientid,
                            t1.other_things
                     FROM   TableC t1
                     WHERE  t1.path ~ $1;
    ELSE
        RETURN QUERY SELECT split_part(t1.path::text, '/'::text, 2)::text AS id,
                            split_part(t1.path::text, '/'::text, 3)::text AS clientid,
                            t1.other_things
                     FROM   TableA t1
                     JOIN   TableB t2
                     ON     t1.path = t2.path
                     WHERE  t1.path ~ $1;
    END IF;

    RETURN;
END; $$
LANGUAGE 'plpgsql';

Now you can search for a specific path on this way:

  This records are fetched from TableC

select * from getABC('/file3/account4/[0-9]+');

id    | client_id | other
:---- | :-------- | ----:
file3 | account4  |     1
file3 | account4  |     1

  This records are fetched from TableA

select * from getABC('/file1/account1/[0-9]+');

id    | client_id | other
:---- | :-------- | ----:
file1 | account1  |     1
file1 | account1  |     1

dbfiddle here


Execution plan with multiple missing indexes If you run a query with 'Include Actual execution plan' the plan will also suggest indexes which are missing. The index details are inside MissingIndexes tag in the XML. Is there a situation when the plan includes multiple index suggestions? I tried different sql queries but couldn't come up with any query which generates two or more missing indexes.

The Query Optimizer in SQL Server can make multiple missing index suggestions for individual queries.  However the part of SQL Server Management Studio (SSMS) which displays execution plans visually only displays a single missing index suggestion; it looks like a bug.  However these multiple index suggestions are visible in SSMS, eg in the properties for the first operator (eg SELECT), press F4.

As you've also noted, multiple suggestions are visible in the plan XML, or third-party tools like SQL Sentry Plan Explorer.  Multiple suggestions will also be visible in the missing index DMVs (eg sys.dm_db_missing_index_details )

This simple query resulted in multiple suggestions for me:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.t1') IS NOT NULL DROP TABLE dbo.t1
CREATE TABLE dbo.t1
(
    rowId INT IDENTITY
)
GO

IF OBJECT_ID('dbo.t2') IS NOT NULL DROP TABLE dbo.t2
CREATE TABLE dbo.t2
(
    rowId INT IDENTITY
)
GO

INSERT INTO dbo.t1 DEFAULT VALUES
GO 100000

INSERT INTO dbo.t2 DEFAULT VALUES
GO 100000

SELECT *
FROM dbo.t1 t1
    INNER JOIN dbo.t2 t2 ON t1.rowId = t2.rowId
WHERE t2.rowId = 999;

HTH


Does MERGE use tempdb? Consider the following query:

MERGE [Parameter] with (rowlock) AS target
    USING (SELECT @AreaId, @ParameterTypeId, @Value)
            AS source (AreaId, ParameterTypeId, Value)
    ON (target.AreaId = source.AreaId AND
        target.ParameterTypeId = source.ParameterTypeId)
    WHEN MATCHED THEN
        UPDATE SET target.Value = source.Value, @UpdatedId = target.Id
    WHEN NOT MATCHED THEN
        INSERT ([AreaId], [ParameterTypeId], [Value])
        VALUES (source.AreaId, source.ParameterTypeId, source.Value);

Statistics I/O gives the following output:

  Table 'ParameterType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Area'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Parameter'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Worktable appears in the messages tab which makes me think that tempdb is being used by MERGE.

I am not seeing anything in the Execution plan that would indicate a need for tempdb

Does MERGE always use tempdb?

Is there anything in BOL that explains this behavior?

Would using INSERT & UPDATE be faster in this situation ?

Left

Right

Here is the table structure

(Expanding on my comment on the question.)

Without a unique constraint on the combination of AreaId and ParameterTypeId, the given code is broken because @UpdatedId = target.Id will only ever record a single row Id.

Unless you tell it so, SQL Server can't implicitly know the possible states of the data. Either the constraint should be enforced, or if multiple rows are valid, the code will need to be changed to use a different mechanism to output the Id values.

Because of the possibility that the scan operator will come across multiple matching rows, the query must eager spool all the matches for Halloween protection. As indicated in the comments, the constraint is valid, so adding it will not only change the plan from a scan to a seek, but also eliminate the need for the table spool, as SQL Server will know there is going to be either 0 or 1 rows returned from the seek operator.


Prompt user for input using substitution variable DECLARE
 localvar char;
BEGIN
 localvar := &ENUMBER;
 IF localvar ='A' then
  dbms_output.put_line('Please proceed to Level1');
 ELSIF localvar='B' or localvar='C' then
  dbms_output.put_line('Please proceed to Level2');
 ELSIF localvar='D' then
  dbms_output.put_line('Please proceed to Level3');
 ELSE
  dbms_output.put_line('Invalid Entry');
 END IF;
END;

I am trying to prompt the user for input using a substitution variable in Oracle, but I am having a problem with this. Any help is appreciated.

When the user types A, the code will look like:

DECLARE
 localvar char;
BEGIN
 localvar := A;
 IF localvar ='A' then
  dbms_output.put_line('Please proceed to Level1');
 ELSIF localvar='B' or localvar='C' then
  dbms_output.put_line('Please proceed to Level2');
 ELSIF localvar='D' then
  dbms_output.put_line('Please proceed to Level3');
 ELSE
  dbms_output.put_line('Invalid Entry');
 END IF;
END;
/

Notice the line localvar := A;. This is obviously incorrect, as A is an unknown identifier.

Either type 'A', or put apostrophes in the code:

DECLARE
 localvar char;
BEGIN
 localvar := '&ENUMBER';
 IF localvar ='A' then
  dbms_output.put_line('Please proceed to Level1');
 ELSIF localvar='B' or localvar='C' then
  dbms_output.put_line('Please proceed to Level2');
 ELSIF localvar='D' then
  dbms_output.put_line('Please proceed to Level3');
 ELSE
  dbms_output.put_line('Invalid Entry');
 END IF;
END;


Functions causing Serial plan.What to use instead? I have few stored procedures in my SQL Server database.I see that these stored procedures are running in serial mode from their execution plan.
For example :

CREATE PROCEDURE [dbo].[DemoProc]
    @Arg1 VARCHAR(20),
    @Arg2 varchar(9) = NULL
AS
SELECT
    r.Plant_Id AS [Plant_Id],
    @LNumber AS [Incident Number],
    COALESCE(idps.AlternateNumber, '') AS [tblFormNumber],
    CASE l.[UserName]
        WHEN NULL THEN 'OPEN'
        WHEN '' THEN 'UNLOCKED'
        ELSE l.UserName
        END AS UserName,
    l.UserId,
    l.Lock LockCode,
    l.LockTime
FROM
    dbo.tblDetailPages idp WITH (NOLOCK)
        INNER JOIN tblForm f WITH (NOLOCK)
            ON idp.tblFormId = f.Id
        INNER JOIN tblReport r WITH (NOLOCK)
            ON f.tblReportId = r.Id
        LEFT OUTER JOIN dbo.tblDetailsPage_Alternate idps WITH (NOLOCK)
            ON idp.Id = idps.PageId
        LEFT OUTER JOIN [dbo].[Monitor] l  WITH (NOLOCK)
            ON l.LNumber = SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)
            AND l.Plant_Id = r.Plant_Id
WHERE
    SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber AND
    r.Plant_Id LIKE CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber)) END
ORDER BY [tblFormNumber]
OPTION (MAXDOP 6)
GO

In this stored procedure,i have a function in the where condition

 WHERE ---
AND r.Plant_Id LIKE CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber))

dbo.GetPlant_Id(@LNumber) is a function

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPlant_Id]
(
    @LNumber varchar(50)
)
RETURNS VARCHAR(25)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM tblReport r WHERE (SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)) = @LNumber HAVING COUNT(r.LNumber) > 1)
    BEGIN
        RETURN cast('Duplicate Incident number found :' + @LNumber as int)
    END

    RETURN
        (SELECT TOP 1 AgencyOri FROM tblReport r WHERE SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber)
END

GO

How do i avoid using functions.?I tried to convert to inline function,but since it is a big function,i cannot put it as an inline function.
To make my question clear,

I was asking how to avoid using function like i showed in the question or is there any way i can change it to improve the performance.?

A scalar UDF only forces a serial plan for the query that calls it. You can have a scalar UDF call in a stored procedure that also has queries eligible for parallelism as long as the queries don't call the function. Your function in this example is a constant so you can just store it in a local variable. I had to make a guess about the data type but something like the following should meet your needs:

CREATE PROCEDURE [dbo].[DemoProc]
    @Arg1 VARCHAR(20),
    @Arg2 varchar(9) = NULL
AS
DECLARE @Plant_Id_Filter VARCHAR(100);

SET @Plant_Id_Filter = CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber)) END;

SELECT
    r.Plant_Id AS [Plant_Id],
    @LNumber AS [Incident Number],
    COALESCE(idps.AlternateNumber, '') AS [tblFormNumber],
    CASE l.[UserName]
        WHEN NULL THEN 'OPEN'
        WHEN '' THEN 'UNLOCKED'
        ELSE l.UserName
        END AS UserName,
    l.UserId,
    l.Lock LockCode,
    l.LockTime
FROM
    dbo.tblDetailPages idp WITH (NOLOCK)
        INNER JOIN tblForm f WITH (NOLOCK)
            ON idp.tblFormId = f.Id
        INNER JOIN tblReport r WITH (NOLOCK)
            ON f.tblReportId = r.Id
        LEFT OUTER JOIN dbo.tblDetailsPage_Alternate idps WITH (NOLOCK)
            ON idp.Id = idps.PageId
        LEFT OUTER JOIN [dbo].[Monitor] l  WITH (NOLOCK)
            ON l.LNumber = SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)
            AND l.Plant_Id = r.Plant_Id
WHERE
    SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber AND
    r.Plant_Id LIKE @Plant_Id_Filter
ORDER BY [tblFormNumber]
OPTION (MAXDOP 6)
GO


TRIM() Function With Linked Server I have a linked server connection for a postgresql into sql server.  I can run this in Sql Server  Select * from OpenQuery(LinkedServer,'Select * from alphadawg') and this returns all results, but the second I try to add in any functions I start getting errors.  For example, if I wanted to TRIM() the trailing spaces off of a field

Select * from OPENQUERY(LinkedServer,
              'Select TRIM(trailing ' ' from ad.userid) As "User ID"
              FROM alphadawg ad')

I get an error of

  Msg 102, Level 15, State 1, Line 5
  Incorrect syntax near ' from ad.userid) As "User ID"

Okay, so maybe the issue is I am trying to execute postgresql in Sql Server, so I altered the syntax to

    Select * from OPENQUERY(LinkedServer,
              'Select RTRIM(ad.userid) As "User ID"
              FROM alphadawg ad')

But that then threw an error of:

  OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "Requested conversion is not supported.".

How can you use the TRIM() function with a linked server connection between postgresql and SQL Server?

Quotes must be escaped in the OPENQUERY argument.

The return type for the TRIM function is text, so you should cast it back to varchar.

Select * from OPENQUERY(LinkedServer,
    'Select CAST(TRIM(trailing '' '' from ad.userid) AS varchar(8)) As "User ID"
    FROM alphadawg ad')


CTE: Get all parents and all children in one statement I have this working CTE example.
I can select all grand-parents and all children.
But how can I select all grand-parents and all children in one statement?
In this example I want Grandfather, Father, Son as output if I give "Father" as input.
I use PostgreSQL. But I think this question should be standard SQL.
Please correct me if I use PostgreSQL specific syntax.
DROP table if exists tree;

CREATE TABLE tree (
 id SERIAL PRIMARY KEY,
 name character varying(64) NOT NULL,
 parent_id integer REFERENCES tree NULL
);

insert into tree values (1, 'Grandfather', NULL);
insert into tree values (2, 'Father', 1);
insert into tree values (3, 'Son', 2);

-- -------------------------------------
-- Getting all children works

WITH RECURSIVE rec (id) as
(
  SELECT tree.id, tree.name from tree where name='Father'

  UNION ALL

  SELECT tree.id, tree.name from rec, tree where tree.parent_id = rec.id

  )
SELECT *
FROM rec;

-- Result:
--  id |  name
-- ----+--------
--   2 | Father
--   3 | Son

-- -------------------------------------
-- Getting all parents works

WITH RECURSIVE rec (id) as
(
  SELECT tree.id, tree.name, tree.parent_id from tree where name='Father'

  UNION ALL

  SELECT tree.id, tree.name, tree.parent_id from rec, tree where tree.id = rec.parent_id
  )
SELECT id, name
FROM rec;

-- Result
-- id |    name
-- ----+-------------
--  2 | Father
--  1 | Grandfather

Above is a simplified working example. The tree can be up to 100 level deep. There can be several level of ancestors above "Father" and several level of descendants below. I want all ancestors and all descendants.

If you want all ancestors and all descendants, you can combine the two queries in one. Use the two CTEs and then a simple UNION:

WITH RECURSIVE
    -- descendants
    rec_d (id, name) AS
    (
      SELECT tree.id, tree.name FROM tree WHERE name = 'Father'
      UNION ALL
      SELECT tree.id, tree.name FROM rec_d, tree where tree.parent_id = rec_d.id
    ),
    --  ancestors
    rec_a (id, name, parent_id) AS
    (
      SELECT tree.id, tree.name, tree.parent_id FROM tree WHERE name = 'Father'
      UNION ALL
      SELECT tree.id, tree.name, tree.parent_id FROM rec_a, tree WHERE tree.id = rec_a.parent_id
    )
SELECT id, name FROM rec_a
UNION
SELECT id, name FROM rec_d ;

If we haven't got any errors above, we can them improve it:

change the final UNION to UNION ALL by putting the starting node(s) in only one of the CTEs.
use JOIN .. ON instead of the implicit joins.
fix mismatches between SELECT and CTE column lists.

The query becomes:

WITH RECURSIVE
    -- starting node(s)
    starting (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t
      WHERE t.name = 'Father'          -- this can be arbitrary
    ),
    descendants (id, name, parent_id) AS
    (
      SELECT s.id, s.name, s.parent_id
      FROM starting AS s
      UNION ALL
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t JOIN descendants AS d ON t.parent_id = d.id
    ),
    ancestors (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t
      WHERE t.id IN (SELECT parent_id FROM starting)
      UNION ALL
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t JOIN ancestors AS a ON t.id = a.parent_id
    )
TABLE ancestors
UNION ALL
TABLE descendants ;


Efficiently remove 90% of a table's data I am working on a process to remove 90% of a table's data because only 10% is needed for testing.

The best way I have found includes storing 10% of the table's rows into a temporary table.

Current method

SELECT TOP 10 PERCENT *
INTO #temp_some_table
FROM some_table (nolock)
ORDER BY some_column DESC

TRUNCATE TABLE some_table

INSERT INTO some_table
SELECT *
FROM #temp_some_table

DROP TABLE #temp_some_table

This method is filling up tempdb and causing the disk to fill up as well.

Questions

Is there a more efficient way to delete 90% of a table's data
ex (DELETE TOP 90 PERCENT FROM sometable)

or

Is there a way to insert 10% of some_table's data into a temporary table using batches? Something like this:

DECLARE @r INT;

WHILE @r > 0
BEGIN

BEGIN TRANSACTION;

INSERT INTO [dbo].[##temp_cds_Basket]
SELECT TOP 10 PERCENT *
FROM [dbo].[cds_basket] s

SET @r = @@ROWCOUNT;
print @r

COMMIT TRANSACTION

END

Possible solution

How about this?

SET NOCOUNT ON;

DECLARE @r INT;
DECLARE @TenPercentDate datetime
with cte (some_column) as (

    select top 10 percent some_column from some_table (nolock) order by some_column desc
)
select @TenPercentDate = min(some_column)
from cte

select @TenPercentDate

 SET @r = 1;

WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

 DELETE TOP (10000) from
  some_table
  WHERE some_column < @TenPercentDate

  SET @r = @@ROWCOUNT;
  print @r

  COMMIT TRANSACTION;

  --CHECKPOINT;    -- if simple

END

--rollback

I have three different ideas that I mentioned in a comment above. Here is a little elaboration on at least one of them (which you are stuck on due to self-diagnosed tunnel vision).

  Well, you could calculate the number of rows that make up 10% beforehand, and then compare that in your batch.

I was thinking about this but we don't really need to do the compare - we can just figure out what datetime value is at the edge of what we want to keep, and delete chunks of older rows until there are none left. For example:

SET NOCOUNT ON;

DECLARE
  @rc INT = 1,
  @cutoff DATETIME,
  @batchsize INT = 10000;

;WITH x(dt) AS
(
 SELECT TOP (10) PERCENT datetime_column
  FROM dbo.mytable
  ORDER BY datetime_column DESC
)
SELECT TOP (1) @cutoff = dt -- earliest row we want to keep
 FROM x
 ORDER BY dt;

WHILE @rc > 0
BEGIN
  DELETE TOP (@batchsize) dbo.mytable
    WHERE datetime_column < @cutoff
  SET @rc = @@ROWCOUNT;
END

You can see this post for some additional ways to enhance this.

  You could consider drop / select into instead of truncate / insert, and also think about using bulk-logged recovery during this operation.

I think this would be a valid option and should cause less pain than the above.

  You could also just perform one data movement operation:

SELECT TOP 10 PERCENT cols
  INTO dbo.newtable
  FROM dbo.oldtable
  ORDER BY datetime_column;

DROP TABLE dbo.oldtable;

EXEC sys.sp_rename N'dbo.newtable', N'dbo.oldtable', N'OBJECT';

This one is also less painful, but feel free to inject some debugging in there to be sure that you got the data you wanted before you perform the drop.

Note that in both of these latter cases there may be constraints, schema-bound views, etc. that prevent you from just dropping the table (obviously an inbound foreign key is not an issue because you are able to truncate, but outbound may still need to be dealt with). There will also be ramifications to existing plans (well deleting 90% of the data should also cause stats to update and invalidate plans, so really no different), and as @Kenneth points out below, you will need to re-establish permissions on the new table (so you might want to be sure you can script those in advance).


Restore InnoDB to different machine I currently have backups that were taken with Percona XtraBackup of all my MySQL databases.

My specific scenario is that I want to restore a month old InnoDB database snapshot to my local machine for testing but I can't seem to find instructions for doing that in the Percona documentation.

Reading around on Google I come to the conclusion that I have to extract the tar.gz file on my machine and "replay" the transaction log.

So my question is what specific steps are needed and what are the caveats for restoring the DB snapshot to my machine.

Finally it seems that using XtraBackup by itself doesn't guarantee that you will be able to restore your data if something goes wrong. For example if the server was to die the backups I have would be useless without the transaction log. So a general phrasing of my question might be what steps need to be taken to ensure that I can restore my data on any machine I wish.

Please keep in mind that my main area of expertise is not database management!

Following instructions that were given to me in an answer, I extracted the backup archive and "prepared" the backup with innobackupex --apply-log . which completed fine. I then stopped mysql, copied ALL the files from the backup dir to the mysql data dir and restarted mysql.

Once I log in though (after resetting the root password), I can see the DB's and their tables (using adminer) but once I select one I'm hit with a table does not exist error. So I'm still at my original question of firstly how to restore to a different machine and secondly how to make sure my backups are usable whenever/wherever I need them.

On a side note, from what I'm reading there should be an .idb file for each table but I don't see anything like that.

It seems that I'm doing everything correctly. My backup script includes

#!/bin/bash
BDIR="/disk2/backup/mysql"

/usr/bin/innobackupex --user=backup --password=xxx --ibbackup=xtrabackup --stream=tar /tmp | gzip -c -9 > $BDIR/`date -u +%Y-%m-%dT%H:%M:%SZ`.tar.gz

which also rsyncs the files off-site.

I download the file, extract (tar -ixvzf), apply the log as mentioned above, stop mysql and copy the files to the datadir (cp), apply permissions, start up and log-in. Browsing to my backup db gives me the error

  140730 16:28:49 [ERROR] Cannot find or open table lead@002dmanager@002dbackup/crm_word_ban from
  the internal data dictionary of InnoDB though the .frm file for the
  table exists. Maybe you have deleted and recreated InnoDB data
  files but have forgotten to delete the corresponding .frm files
  of InnoDB tables, or you have moved .frm files to another database?
  or, the table contains indexes that this version of the engine
  doesn't support.

for all the db tables.

MySQL version on server is 5.1, local version is 5.5

Please refer to the official documentation on how to create, store and restore a full backup with Percona XtraBackup. Percona XtraBackup does guarantee having everything for no data loss:

It can be summarized as:

Perform the backup (innobackupex). You can optionally send it remotely, compress it, encrypt it, etc. at the same time the backup is running. This is the only point where you need access to the original database server.
If you did not send it remotely on the first step, do it now. A local backup, while useful, is not a real backup strategy. For the backup to be complete, you may want to copy also your config file (/etc/my.cnf and your binary executables, if you cannot get them from other source -your distro, for example).
At any time between the backup and the recovery process, you need to do the prepare phase (innobackupex --apply-log). This only requires the backup files, not the original server. This will create the transaction logs for you
After you have applied it, just copy back the files to the original directory while the server is down. You can do that with innobackupex --copy-back if you have the files locally, or just do (s)cp/rsync. Remember to change the file permissions to the appropriate user (usually the mysql user).
Start the server. You are done.

This will restore a full backup. To assure you this is a secure method, Galera cluster can use it to clone nodes automatically (without human intervention) and it is the safest and fastest method available.

If you do not see individual .ibd files, it could be for several reasons- either you are using MyISAM or another engine that do not use those, or you are using InnoDB with innodb_file_per_table = 0, or you lost some of the files on the copy process (or they have wrong permissions). Please provide the full error message and the error log since the last start, and a recursive listing of your datadir.

As the writer of the training for Percona XtraBackup, I am quite familiar with all the typical silly mistakes we do when working with it.

Thank you for the extra information, here are my suggestions as a follow up:

The latest versions of XtraBackup are not compatible with 5.1 without the innodb plugin. Are you using MySQL 5.1 with stock InnoDB (not the plugin, or Percona/MariaDB)? Also, recovering to a newer version of MySQL in binary form may require additional upgrade steps/precautions- it can be done in binary form. Suggestion: try using an older version of Xtrabackup or upgrading the server. 5.1 without the plugin is a 6-year-old release with very little support everywhere. I can also provide alternative backup tools if you want to stick to <5.1 (but very few are so fast).
I do not like the database/table name- just to double check, can you confirm that db: lead@002dmanager@002dbackup table: crm_word_ban is the right name for one of your tables, and that it is the exact name of a .frm file? I want to discard filesystem issues.


Optimal way to save Social media posts from multiple platforms I am working on a platform, that gathers post form multiple sources(Facebook, Twitter etc) and saves them to the database. The plan was to create one table called posts with all the necessary fields for all platforms, even the platform specific ones. So a saved Facebook post might contain an empty column called retweets(Twitter specific).

Another solution is to create a Supertype post which will contain all the general information plus the type(facebook, twitter) and have a relationship with all the platforms specific tables.

A third option is a general and platform specific table with no relationship with each other, Posts contains all the general data and the platforms specific(facebook_posts) table contains the general and the platform specific data they have no relationship with each other only with the user.

What do you think is the optimal option for performance and future proofing? We might want to add more platforms and maybe remove\changes exciting ones. The database might contain 10s of thousands of posts at some point in the future.

Option 1: All information in one table

Table name: Posts
id
title
facebook_id
retweets

Option 2: Supertype with multiple relationships

Table name: SuperPosts
id
type
title

Table name: facebook_posts
id
facebook_id
shares

Table name: twitter_posts
id
twitter_id
retweets

Option 3:  General and platform specific no relationship with each other

Table name: Posts
id
platform_id
text

Table name: facebook_posts
id
facebook_id
facebook_text
shares

Table name: twitter_posts
id
twitter_id
twitter_text
retweets

You are asking how to implement inheritance on a relational database, and the 3 methods you mention are basically the 3 most common/basic ways to do that. See https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database for some discussion about them.

There is no "best" way to do it- it will all depend on how you intend to use your schema so it facilites your queries afterwards.


Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered index so we can lookup based on the Uri of the page. There are a lot more columns in that table as well, related to that page.

If we join with other tables and we join on the UriKey, the query will use the non clustered index only if we only require the UriKey itself. Once we add another column, it does a clustered index scan, wich can take up to days to complete.

This uses the correct index and takes less than a second to complete
https://www.brentozar.com/pastetheplan/?id=rkk1vIb07

select kps.Page_ID, p.PageID, r.*, p2.UriKey
from LB_Keywords_Pages_Static kps
    inner join spidir2.dbo.se_pages p
    on kps.page_id=p.pageid
    inner join Temp_KeyboostRedirects r
    on p.UriKey like '%/'+ r.source +'/%'
    inner join Spidir2.dbo.SE_Pages p2
    on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063

This selects one column extra (p2.Domain_ID) and takes more than a day to complete https://www.brentozar.com/pastetheplan/?id=ByOpUIb0m

select kps.Page_ID, p.PageID, r.*, p2.UriKey, p2.Domain_ID
from LB_Keywords_Pages_Static kps
    inner join spidir2.dbo.se_pages p
    on kps.page_id=p.pageid
    inner join Temp_KeyboostRedirects r
    on p.UriKey like '%/'+ r.source +'/%'
    inner join Spidir2.dbo.SE_Pages p2
    on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063

When we specify the index to be used, it works like a charm https://www.brentozar.com/pastetheplan/?id=SkvkKIbAm

select kps.Page_ID, p.PageID, r.*, p2.UriKey, p2.Domain_ID
from LB_Keywords_Pages_Static kps
    inner join spidir2.dbo.se_pages p
    on kps.page_id=p.pageid
    inner join Temp_KeyboostRedirects r
    on p.UriKey like '%/'+ r.source +'/%'
    inner join Spidir2.dbo.SE_Pages p2 with (index(SE_Pages_UriKey))
    on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063

I noticed this strange behavior also with other keys and queries. I didn't notice this behavior before when we were using SQL 2016. This is causing our database to become very slow.

If we really have to specify every time the index to be used, wouldn't it be better to move to a nosql database?

Sometimes the person writing T-SQL has more accurate information than the query optimizer. In all of your query plans, SQL Server has a final cardinality estimate of 5360890000 rows, which is how many rows are in the dbo.SE_Pages table. Only a single row is ever returned though, and in the comments you said:

  most of the time it['s result set] is only a selection of a few thousand

Something is going very wrong with cardinality estimation. From the optimizer's point of view, it's giving you the best plan that it can. It thinks that all plan options are expensive because billions of rows need to be returned. When returning all rows, in general, a clustered index scan will be costed much cheaper than using an NCI + key lookups to return every single row. The bad cardinality estimate results in a query plan that does the scan which takes days. The key to getting better performance without index hints is to fix the cardinality estimate.

You said that this problem seemed to start happening after you upgraded from SQL Server 2016 to SQL Server 2017. Did you gather statistics after the upgrade? The two most likely causes are that there's something wrong with the statistics for dbo.SE_Pages or Microsoft made a change in SQL Server 2017 that's harmful to your workload.

You can test if statistics are the cause by updating them for that table. You can test if SQL Server 2017 is the problem by changing the compatibility level of the relevant database to 130, although this could have other effects on your application. If you need another temporary workaround that doesn't involve an index try FAST 1. That hint encourages the optimizer to prefer plans which return the first row as quickly as possible. In your case, it's a way to disfavor plans that do a scan. If you need additional help you'll need to add more information to your question. The histogram for UriKey and the table structure (along with an explanation as to why you're partitioning by UriKey) are likely to be helpful.


Confusion normalizing data to 3NF and transitive dependencies I am currently in a Database class and have some confusion with normalizing to 3NF. I have one specific issue that was not addressed in class but appears in the book problems.

For example I have

R (ABCDEFGH)

AB-->D
B-->C
B-->E
A-->H
H-->G

I have two issues with the problem. First, when decomposing the transitive dependencies of B, should they be split into two tables of BC and BE or a single table BCE.

Second is how do I decompose H-->G. When I remove the partial dependency of A-->H into a separate table AH, the dependency H-->G no longer exists because I already removed H to put it into a separate table.

This is my solution to breaking this down.

R (ABCDEFGH)
R1 (ABCEFGH)  R1(ABD)
R2 (ABEFGH) R2(BC)
R3 (ABDFGH) R3(BE)
R4 (ABDFG) R4(AH)

I have no idea whether I am loosing the H-->G FD by breaking it down like this. However following the steps I had to break apart all the partial FDs before breaking down the transitive one.

Any help would be appreciated on my two issues.

You have:

R (ABCDEFGH)

AB-->D
B-->C
B-->E
A-->H
H-->G

I'm not sure how you resulted in the decompositions but the first thing to do usually is to find the candidate keys if the relations. Here there is only one, the ABF.

Then you identify that the relation is not in 2NF, as there are dependencies on parts of candidate keys and not the whole of them (this part seems to have been done it quite well.) The partial dependencies (on parts of the candidate keys) are these and you only missed the last one, that can be derived from the others:

AB-->D
B-->C
B-->E
A-->H
A-->G             -- missed this one

So (leaving the first question for the end), the decompositions to:

R (ABCDEFGH)
                          -- splitting because of the AB -> D, fine
R*(ABCEFGH)  R1 (ABD)
                          -- splitting because of the B -> C, fine
R**(ABEFGH)  R2 (BC)
                          -- splitting because of the B -> E, fine
R***(ABFGH)  R3 (BE)

are rather fine, except some minor details like the D reappearing in the R relation.

The next part which is your second question regarding the A->H and H-G was problematic due to your missing of the implied A->G dependency. It should be:

                          -- splitting because of the A -> HG
R****(ABF)   R4 (AHG)

We have now resulted in 5 relations, all in 2NF:

R**** (ABF)
R1 (ABD)      -- AB-> D
R2 (BC)       -- B -> C
R3 (BE)       -- B -> E
R4 (AHG)      -- A -> HG ,  H -> G

In fact all are in 3NF except for R4. So, we have to split that into two relations:

R4* (AH)      -- A -> H
R5 (HG)       -- H -> G

We now have 6 relations (R****, R1, R2, R3, R4*, R5) which are all in 3NF and BCNF as well. In fact they are all in 4, 5 and 6NF.

Regarding your first question, you could have used one split into (BCE) instead of the two (BC) and (BE) and it would have been perfectly fine. Your final result would be five relations (instead of six), all in 3NF - and all the way up to BCNF, 4NF, 5NF, 6NF (except for that (BCE) which is in 5NF.)


Why does SQL Server use this index? I have two indexes on a table with 786602 rows:

CREATE NONCLUSTERED INDEX [IX1]
ON [dbo].[O] ([A] ASC, [B] ASC)
INCLUDE ([c1], [c2], [c3])
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
           SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX2]
ON [dbo].[O]([A] ASC, [B] ASC)
INCLUDE ([c1], [c2], [c3], [c4], [c5], [c6], [c7], [c8], [c9], [c10])
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
           SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The following queries both use IX2:

select [c1], [c2], [c3]
from [O]
where A = 9 AND B = 10

select [c1], [c2], [c3], [c4], [c5], [c6], [c7], [c8], [c9], [c10]
from [O]
where A = 9 AND B = 10

Disabling IX2 shows that query 1 then uses IX1 with the same performance.

Why does the first query not use IX1 as it seems to PERFECTLY match the requirements?

Is SQL Server really that smart and does not use it to show me that it can be removed?  :-)

Is it save to just DROP X1? (that was my original intention so I started testing the usage and just wondered why X1 is never used)

While I cannot confirm this with any official documentation, the reason must be based on the total size of included columns and the cost.

With the test i conducted, the indexes with 10 columns and 3 columns had exactly the same cost and logical/physical reads, which I assumed to be the same with the original poster's environment.  I gradually increased the size of the 4th column, and at certain point, the opimizer started choosing the 1st index for the 1st statement.  The table used had about 55k rows.

This only shows an anecdotal evidence and a theory on certain behavior of the optimizer.  I hope  someone who really knows the meanism of it will explain us all why, if this theory isn't correct.


Multiple versions of mysql / mariadb I am running 10.3.18-MariaDB-log MariaDB Server but for one application it require I run mysql 5.7, so I am installed that to run under another port which works fine.

However for the software that requires mysql 5.7 I cannot seem to pass this as my mysql client version still shows up as mariadb's 5.5 mysql client.

This is my.cnf for the mysql 5.7 version

[mysql]

# CLIENT #
port                           = 3307
socket                         = /var/lib/mysql2/mysql2.sock

[mysqld]
port = 3307
socket                         = /var/lib/mysql2/mysql2.sock
datadir = /var/lib/mysql2
pid-file=/var/lib/mysql2/mysqld2.pid

is there someway to specify what mysql client binary I can run ? I am not sure what I am doing wrong?

/usr/local/mysql2/bin/mysql -V
/usr/local/mysql2/bin/mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

/usr/local/mysql2/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 99552
Server version: 5.5.5-10.3.18-MariaDB-log MariaDB Server

When you run /usr/local/mysql2/bin/mysql in your last code snippet, that is obviously your MySQL 5.7 mysql command-line client, but the output shows it's connecting to your MariaDB server:

Server version: 5.5.5-10.3.18-MariaDB-log MariaDB Server

If you want it to connect to your MySQL 5.7 server, then you need to specify the non-default port or socket and maybe other credentials like the database user and password (if you have created that), e.g. one of these commands:

/usr/local/mysql2/bin/mysql -P3307
/usr/local/mysql2/bin/mysql -uroot -p -P3307
/usr/local/mysql2/bin/mysql -S/var/lib/mysql2/mysql2.sock

For reference, see also my answer to "how to install a second server of mariadb".


Select all data from IBM DB2 member table Old as400 system has this thing called 'Members'. Basically you can have multiple members on a table and then work with only selected one.
More Info

When you select from a table that has members you have a few options. Just doing a select will retrieve data from the first member. Or call a stored procedure to set the member for the table and retrieve it. Or create an alias for the table with members to access it. But there is no way to just get a

Is there a way to select ALL data from the table from ALL the members?

The best solution, is to install the DB2 Multisystem product and recreate the table as a local "partitioned table".

Behind the scenes, DB2 for i creates the table as a multi-member PF basically just like you have now.

However, SQL statements referencing the table will auto-magically see all the data.  Old RPG applications that use OVRDBF to override to a specific member will continue to work as before.

Unfortunately,  DB2 multisystem is not cheap.  If you don't already have it, i doubt you'd want to get it.

You can create a DDS logical file with DTAMBRS(*ALL) or use OVRDBF MBR(*ALL) to create a single "view" of the data.  But unfortunately, only RPG programs using record level access (RLA - aka "native RPG I/O op-codes) can use them.  SQL access results in a CPF4268 - Object *ALL in *N type *MEM not found.

The only SQL statement in DB2 for i that knows anything about members is the CREATE ALIAS statement.  So you could issue a CREATE ALIAS as needed to access the member you need.  Or you could pre-create alias' for every member.

You can then use the alias' in a select statement like so

select * from myalias1
UNION ALL
select * from myalias2

It'd be nice if you could create a view from the statement above, unfortunately you can't.  Trying to do so results in an SQL7030 - Alias MYALIAS1  for table MYTABLE in MYLIBRARY not valid for statement.

The only way to encapsulate the statement I'm aware of would be to define it as a stored procedure or user defined table function (UDTF).


How can one select a static value as a column based when using UNPIVOT? I'm trying to unpivot a few columns in a table. This is for a reporting tool. The source data is already normalized. I am able to get my desired results with a series of UNION ALL statements. I can get close to what I want with UNPIVOT, but I'm missing one part. I'd like to get the source column name in my UNPIVOT results.

Table structure: |  itemNumber  |  type  |  code  |  description  |
                 |  BN3466774   |  AUD1  |  444   |  desc--text   |
                 |  1238udd74   |  AUD1  |  331   |  fdsafdsafdsa |
                 |  AA34fg774   |  MAN3  |  874   |  asdfasdfasdf |

Desired Output:  | itemNumber   | SourceName | SourceValue |
                 | BN3466774    | type       | AUD1        |
                 | BN3466774    | code       | 444         |
                 | BN3466774    | description| desc--text  |
                 | 1238udd74    | type       | AUD1        |
                 | 1238udd74    | code       | 331         |
                 | 1238udd74    | description| fdsafdsafdsa|
                 ... you get the idea

As mentioned before, I can get this result using a series of UNION ALL statements

SELECT *
FROM
(
SELECT itemNumber, 'type' as SourceName, type as SourceValue FROM myTable
UNION ALL
SELECT itemNumber, 'code  ' as SourceName, code as SourceValue FROM myTable
UNION ALL
SELECT itemNumber, 'description  ' as SourceName, description  as SourceValue FROM myTable
) as myValues

My query using UNPIVOT looks like this:

SELECT itemNumber, Value
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT
(
  Value FOR attributes IN ( type, code, description)
) AS myValues

This query results in the following data:

Desired Output:  | itemNumber   | SourceValue |
                 | BN3466774    | AUD1        |
                 | BN3466774    | 444         |
                 | BN3466774    | desc--text  |
                 | 1238udd74    | AUD1        |
                 | 1238udd74    | 331         |
                 | 1238udd74    | fdsafdsafdsa|

The reason I'd like to go with the UNPIVOT is because it tends to execute about 50% faster than the UNION ALL approach, and in one query I've got about 16m rows being returned.

I'd like to get the source column into my UNPIVOT results, but I've been unable to generate a query that does this.

You are missing a new column generated by unpivoting attributes

SELECT itemNumber, attributes AS [SourceName], Value As [SourceValue]
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT
(
  Value FOR attributes IN ( type, code, description)
) AS myValues


Implementing application locks within SQL Server (Distributed Locking Pattern) In my application I have to perform a distributed locking pattern. Because we already have an instance of SQL Server to use, we decided that it would be easiest to implement the locking at SQL layer of our web application.

A lock can be obtained based on numerous conditions including:

The type of lock requested
An arbitrary application identifier

For all intents and purposes, treat the above two conditions as int data types.

In this pattern, we wish to treat all of our locks as FIFO, which I believe the SERIALIZABLE isolation level will give us.

Here is how we propose to perform the "lock":

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

IF EXISTS (SELECT 1 FROM locks WHERE LockType = @LockType AND ApplicationIdentifier = @ApplicationIdentifier)
    BEGIN
        -- Awesome, the lock will be acquired
        INSERT INTO locks OUTPUT INSERTED.LockId VALUES (2,3)
    END
ELSE
    BEGIN
        -- Someone already has the lock
        SELECT -1
    END

SET TRANSACTION ISOLATION LEVEL READ COMITTED

And the "unlock":

DELETE FROM locks WHERE LockId = @LockId

So my question is two-fold:

Do I need to make the "unlock" SERIALIZABLE as well?
Are there any other approaches that I could use/anything that I have forgotten?

SQL Server can either be 2008/2012

Do I need to make the "unlock" SERIALIZABLE as well?

Despite the name, the serializable isolation level does not guarantee transactions will be executed sequentially, or in the order received. Rather, serializable guarantees transactions will have the same persistent effects on the database as if they had executed sequentially, in some undefined order (see the link for more details).

  Are there any other approaches that I could use/anything that I have forgotten?

Yes. SQL Server already provides support for arbitrary application locks via:

sp_getapplock
sp_releaseapplock
APPLOCK_MODE
APPLOCK_TEST

These built-in applock features provide a broad range of options, and include automatic deadlock detection (though any transaction rollback required is the programmer's responsibility). For example, you can choose from transaction- and session-scoped locking, in a variety of modes. Using these features to implement the behaviour you need should be quite straightforward, and certainly simpler than building your own resilient and reliable lock manager from scratch.


Getting Data Back after Wrong Data Restore in SQL Server I would like to inquire if there is an possible way to return lost data that stored in a Sql server Database after making a wrong database restore?

I know it is extremely hard to get a positive solution but any kind of help will be appreciated.

The database has log and data files that are stored on the file system. If the sys admin has a process of regularly backing up the location where the database files are stored then you might be able to get some of the data back. Assuming that you can get these files from before you overwrote them - then create a new database and attach the db and log file to it. You will most likely still have some problems but you might get some data back.


How do I safely drop a list of tables, when i have the table names listed in a column? Assuming I have a database column containing table names, how do I safely delete all of the tables it lists?

The tables themselves are fairly simple - they may have indeces, but definitely don't contain other complex items (and don't have dependencies on other tables - ie, foreign keys into/out of them, etc...)

Something along the lines of the following should work.

CREATE TABLE dbo.TablesToDrop
(
    TableName SYSNAME
);
INSERT INTO dbo.TablesToDrop VALUES ('Table1');
INSERT INTO dbo.TablesToDrop VALUES ('Table2');
INSERT INTO dbo.TablesToDrop VALUES ('Table3');
DECLARE @Cmd NVARCHAR(MAX);
SET @Cmd = '';
SELECT @Cmd = @Cmd + 'DROP TABLE ' + QUOTENAME(t.TableName) + ';'
FROM dbo.TablesToDrop T;
RAISERROR (@cmd, 0, 1) WITH NOWAIT;
--EXEC sp_executesql @Cmd;

Uncomment the last line to actually execute the DROP TABLE statements.


Protecting the data from internal staff In our database (DB for brevity) we store some very sensitive government data, and would like to secure the data as much as possible.

There are two way to think about security here:

defense from external intruders (essentially making sure PCI-DSS compliance is in place, for instance),
security from internal intruders.

My question is focused on the latter.

The sensitivity of information is considered so high that we're facing a very likely possibility of internal staff getting their hands on the data.

In other words, we're afraid that internal staff can mass-steal the data and trade it (the data is only interesting when stolen altogether, as it's a huge DB of people's private info).

The DB is accessed by our application, on a record-by-record manner. This is considered fine, as using record-by-record manner of accessing data  staff is not expected to mass-steal the information.

An obvious solution would be: give "keys to DB" to a single, most trusted man. However our concern is that even in this case someone can put a gun to his head and ask to make the copy of the DB.

Is there any way we could secure the DB, such that even if the DBA is put a gun to his head, he would be physically unable to steal the data?

Note: The database management system we are using is PostgreSQL, but we may easily switch to another one provided it offers the safety guarantees we're seeking.

Gun-against-the-head

Require more heads. This solution scales linearly. Every gun you need to protect against requires one more disinterested head. Consider methods of discrete head positioning. The more work you spend positioning the heads, the more work others have to spend positioning guns. Hide heads for extra obfuscation.

Security is O(n) with heads: MySQL is equally secure with a rock, which is equally as secure as PostgreSQL. That's the best position in a security analogy MySQL has ever been in.

Software Stack Security

Network Encryption, use SSL with an AES cipher. Consider layering DB authentication with network authentication using openswan/strongswan.
Disk Encryption, use eCryptfs
Authentication, use GSSAPI

Database Security

PostgreSQL supports row-level security. I definitely suggest reviewing the DDL security docs. This protects the database against unbridled app-level access. Perhaps all of your data doesn't need to be read by every app? You can even REVOKE or GRANT access to specific columns on the table, see the docs for more info (search for column).

Special notes

You can store encrypted data in the database too. This gets the DBA admin out of the problem entirely. Then the gun-against-the-head switches points of failure to the end-user. This may or may not be a good thing. Certainly, I prefer them to risk the gun-against-the-head scenarios. I just run it.


ssis delete database on error I'm writing an ssis package that imports a number of access databases nightly.  Each database has the potential to have a slightly different schema. Since I'm creating the tables than doing a direct table insert using execute sql tasks inside of a for each loop container the schema differences cause errors.  I've upped the max error count so that the package can take errors and continue running.  This leaves me with some databases that have incomplete data which I would prefer to drop.

I attempted to make an event handler that would drop the database; however, at the point that the event handler is called the database is still in use.  Is there another way that I could drop the database if there is an error?

Not 100% sure what you are attempting here, but you could record the names of "partial" databases in a table in SQL and then run a separate process after the fact to drop them.


SQL Server Demo Environment on Docker Desktop (Win 10): DB storage und upgrade paths I would like to run SQL Server 2019 on my work as well as my private machine (both running Windows 10 Pro). Thus I thought Docker Desktop would be a nice option.

However I would like to keep the docker image itself minimal (just SQL Server and system databases) and store my demo databases elsewhere. I have tried to accomplish this using a data volume mounted from the host using the following command line switch.

-v C:\SQL:/sql

CREATE DATABASE does work using FILENAME "C:\sql..."

    CREATE DATABASE [test] CONTAINMENT = NONE ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\sql\test.mdf' ,...

ATTACH does work  using Database File Location "/sql/..."

USE [master]
GO
CREATE DATABASE [CustomerOrders] ON
--( FILENAME = N'/sql/mssql/data/CustomerOrders.mdf' ), --generated by ssms -> wrong
(FILENAME = N'C:\SQL\mssql\data\CustomerOrders.mdf'),
( FILENAME = N'C:\SQL\mssql\data\CustomerOrders_log.ldf' )
 FOR ATTACH
GO

However RESTORE does only work with destination /var/opt/mssql and NOT with
destination /sql/ or C:\sql

Try 1

      USE [master]
    RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak'
WITH  FILE = 1,  MOVE N'CreditData' TO N'C:\SQL\CreditData.mdf',
MOVE N'CreditLog' TO N'C:\SQL\CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

Try 2

USE [master]
RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak'
WITH  FILE = 1,  MOVE N'CreditData' TO N'/sql/CreditData.mdf',
MOVE N'CreditLog' TO N'/sql/CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

GO

Error Messages

    Msg 3634, Level 16, State 1, Line 2 The operating system returned the error '2(The system cannot find the file specified.)' while
    attempting 'RestoreContainer::ValidateTargetForCreation' on
    '/sql/CreditData.mdf'. Msg 3156, Level 16, State 5, Line 2 File
    'CreditData' cannot be restored to '/sql/CreditData.mdf'. Use WITH
    MOVE to identify a valid location for the file. Msg 3634, Level 16,
    State 1, Line 2 The operating system returned the error '2(The system
    cannot find the file specified.)' while attempting
    'RestoreContainer::ValidateTargetForCreation' on '/sql/CreditLog.ldf'.
    Msg 3156, Level 16, State 5, Line 2 File 'CreditLog' cannot be
    restored to '/sql/CreditLog.ldf'. Use WITH MOVE to identify a valid
    location for the file. Msg 3119, Level 16, State 1, Line 2 Problems
    were identified while planning for the RESTORE statement. Previous
    messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE
    DATABASE is terminating abnormally.

This one works (but is not what I want, as it is stored inside the docker image then):

USE [master]
RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak'
WITH  FILE = 1,  MOVE N'CreditData' TO N'/var/opt/mssql/CreditData.mdf',
MOVE N'CreditLog' TO N'/var/opt/mssql/CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

Another big difference is file growth on database files stored within the container and on files on the volume (resp. the host).
I created a test database with a 8 MB mdf file  in /var/opt/mssql/data and in /sql (my data volume). Afterwards I issued the following command

ALTER DATABASE [test2] MODIFY FILE ( NAME = N'test2', SIZE = 1048576KB )

This grows the file to 1 GB. In the location /var/opt/mssql/data it finished instantely while the resize operation took 8 seconds on the data volume /sql.

I tested for Instant File Initialization with traceflags 3004 and 3605 but didn't see any output regarding nulling the mdf file out on the data volum /sql.

Thanks in advance for your help

Martin

Performance of SQL Server using shares to host data files is almost always poor, regardless of the underlying storage subsystem. You should be mounting a host directory as a volume in your docker container to get better performance. See this link for more information.


Error from master error log while doing a replication to slave I have setup a master and slave MySQL running 5.7.17 in Linux machines. Both servers have a unique UUID as specified in the auto.cnf file. Besides, the master server has server-id=10 while slave has server-id=20.

However I keep getting the following error coming in the master's error log. I could not figure out why as the replication is running fine. The error starts to come out when the master's SQL thread is stopped.

2016-12-28T09:00:37.953467Z 27931 [Note]
 Start binlog_dump to master_thread_id(27931) slave_server(20),
 pos(hostnameA-bin.000117, 818913948)
2016-12-28T09:01:38.027487Z 27978 [Note]
 While initializing dump thread for slave with UUID <xxxxxxxx-xxx-xxxx-8bbc-xxxxxxxxxxxx>,
 found a zombie dump thread with the same UUID.
 Master is killing the zombie dump thread(27931).
2016-12-28T09:01:38.027652Z 27978 [Note]
 Start binlog_dump to master_thread_id(27978) slave_server(20),
 pos(hostnameA-bin.000117, 818913948)
2016-12-28T09:02:38.104298Z 28025 [Note]
 While initializing dump thread for slave with UUID <xxxxxxxx-xxx-xxxx-8bbc-xxxxxxxxxxxx>,
 found a zombie dump thread with the same UUID.
 Master is killing the zombie dump thread(27978).

Try to raise the slave_net_timeout. Prior to version 5.7.7, it was set to 3600, now to 60. Therefore, if you have any trouble on the network, the slave will think it has lost connection the server but when a Slave tries to [re]connect, it creates a dump thread on Master. Before Master creates a dump thread, it checks if there is already a dump thread running for that Slave (using the UUID value). If yes, it considers that old dump thread as "Zombie" dump thread and kills it. Creates a new one and prints this "Note" in the error log file. (This is cited from the official MySQL site )

Practically speaking: Enter MySQL on the slave machine and

mysql> set global slave_net_timeout=3600;

Restart your MySQL server and it should solve the problem.

Once this is OK, you can add this parameter in the configuration file my.conf to make the changes definitive.


Whether it means the end time of the SYBASE LICENSE? I want to know the meaning of "2013.03010/permanent/0C94 6187 B194 DF07", Whether it means the end time of the SYBASE LICENSE?

sybase log

  00:00000:00016:2011/01/03 14:15:37.02 kernel  SySAM: Checked in
  license for 1 ASE_CORE (2013.03010/permanent/0C94 6187 B194 DF07).
  00:00000:00000:2011/01/03 14:16:53.08 kernel  SySAM: Using licenses
  from: /home/sybase/SYSAM-2_0/licenses
  00:00000:00000:2011/01/03 14:16:53.10 kernel  SySAM: Checked out license for 1 ASE_CORE
  (2013.03010/permanent/0C94 6187 B194 DF07).
  00:00000:00000:2011/01/03 14:16:53.10 kernel  This product is licensed
  to: BEV CORPORATION, an OEM license from Sybase, Inc.
  00:00000:00000:2011/01/03 14:16:53.10 kernel  Checked out license
  ASE_CORE    00:00000:00000:2011/01/03 14:16:53.10 kernel  Adaptive
  Server Enterprise (Enterprise Edition)

This message does not mean that the license is expired or something. It only states that sybase found a valid license and checked it out. More information about the sybase license manager you can find here: http://www.sybase.com/detail?id=1009350


Update image using dynamic sql I have been battling with this statement:

ALTER PROCEDURE [dbo].[transact_image_update]
-- Add the parameters for the stored procedure here
@transact_recordID_int int,
@image1_bin image,
@image2_bin image,
@transact_referenceNo_str nvarchar(25),
@userID_last uniqueidentifier,
@tableName nvarchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

DECLARE @sqlUpdt01 nvarchar(4000)
SET @sqlUpdt01  = '
        Update [dbo].[' + @tableName + '] SET [image1_bin] = '+ CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), @image1_bin), 2)
        + ', [image2_bin] = '+ CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), @image2_bin), 2)
        +', [userID_last] = '''+ convert(nvarchar(4000),@userID_last)
        + ''' WHERE (transact_recordID_int = '+convert(varchar,@transact_recordID_int) +')
            AND ([transact_referenceNo_str] = ''' +convert(varchar, @transact_referenceNo_str)
             +''' )
             AND (locked_bol = 0)
             '
exec sp_executesql @sqlUpdt01

Basically, I have many DB tables with similar schema but different names (for types of transactions) and would like this ONE procedure to make the update given the table name as argument.
This script compiles successfully but execution cannot update the image field.
Is there a conversion I'm missing?

Please help.

I am not sure why the procedure uses image parameters instead of varbinary(max), or why you aren't passing the parameter values to sp_executesql directly. How about:

ALTER PROCEDURE [dbo].[transact_image_update]
  @transact_recordID_int int,
  @image1_bin varbinary(max),
  @image2_bin varbinary(max),
  @transact_referenceNo_str nvarchar(25),
  @userID_last uniqueidentifier,
  @tableName nvarchar(50)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max);

  SET @sql = N'UPDATE dbo.'
    + QUOTENAME(@tableName) + N'
    SET [image1_bin] = @i1,
            [image2_bin] = @i2,
            [userID_last] = @u
    WHERE transact_recordID_int = @t
    AND [transact_referenceNo_str] = @s
    AND locked_bol = 0;';

  EXEC sys.sp_executesql @sql,
    N'@i1 varbinary(max), @i2 varbinary(max), @u uniqueidentifier,
    @t int, @s nvarchar(25)',
    @image1_bin, @image2_bin, @userID_last,
     @transact_recordID_int, @transact_referenceNo_str;
END