Support both stored procedures and functions with same TSQL code base? We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends.

Given the following constraints:

Functions may not call stored procedures.
Stored procedures may call functions.

does putting all the TSQL source code into functions and using stored procedures as wrappers around calls to the functions makes sense?

What other options (if any) exist to support both functions and stored procedures with the same TSQL codebase?

For the curious....

Frontend #1: Microsoft Access 2010 SP1 (version 14.0.6024.1000)

Users use Linked Tables > Views > ITVF's
This approach avoids necessity for developing frontend report screens/code
IT staff plan to sunset this environment in 2014-15 or so
Linked tables may access views but not functions or stored procedures
Views, in turn, may access functions but not stored procedures
Would still like to keep functions around for ad hoc queries even after sunsetting this frontend...though this is a would-be-nice not a need

Frontend #2: ASP.NET 4.0 web app

To-be-developed by in-house IT staff
IT staff policy requires .NET web apps to call only stored procedures

Backend: Microsoft SQL Server 2008 SP3 (version 10.0.5500...will likely upgrade to Microsoft SQL Server 2012 sometime in 2013)

UPDATE: to clarify, this represents a reporting app, so really only need read-only access, other than the ability to create incidental temp tables, table variables, and so forth.

Sounds like you've already made the decision to use T-SQL for your back-end.  For OWASP reasons, you are correct in that calling only stored procedures from your data access layer is the safest approach to avoid SQL injection.

Stored procedures and functions are similar in that you can pass arguments for the parameter list.  They also can do similar things.  Code re-use is a good thing and will save you in the end when fixing bugs or making enhancements in the future.  So find the logic that is similar and re-factor that code to functions.  If you want to return tables (single result set), use table based functions.  If you want to return single values, use scalar functions.  Note, you won't be able to perform permanent environment changes with functions (no CRUD operations are allowed... CREATE/INSERT, READ, UPDATE, DELETE).  It's also good practice to wrap your stored procedures with error/exception handling and return those exceptions to the server, to let your logging handle them appropriately.  Also note that it's common to return multiple result sets in a single call to a stored procedure (3 SELECT statements for example).  In your application code, you'd handle each table individually in the response.

Here are some differences:


Estimating distribution of row access in mysql I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can have access to figures like "number of rows accessed per table", but I would actually need those data on a per row basis (e.g. row with id 1 was read 200 times, row with id 2 was read 300 times, where id is the auto increment column)

If you want that kind of granular statistics per id, it sounds like you may want to try something a little convoluted to collect such info. Let's explore this scenario:

If you have a table with the following layout in the wp database:

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and you want to track access to this table, you must force the recording of the table's id using a series of operations combining the use of Triggers, BLACKHOLE tables, MySQL Replication, and "Socially Responsible" Coding.

TRIGGERS

Triggers follow the course of any INSERT, UPDATE, and DELETE. In this scenario, you will need three(3) types of Triggers: 1) After INSERT, 2) After UPDATE, 3) After DELETE.

Each time wp_posts has an  INSERT, UPDATE or DELETE, record the ID in another table. What kind of table ???

BLACKHOLE TABLES

Let's create a table for recording IDs

CREATE TABLE wp_posts_idtracker
(
  `ID` bigint(20) unsigned NOT NULL,
  `DTSTAMP` datetime NOT NULL
) ENGINE=BLACKHOLE;

Now here are triggers to record the ID accesses:

CREATE TRIGGER wp_posts_insertafter AFTER INSERT ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (NEW.ID,NOW());

CREATE TRIGGER wp_posts_updateafter AFTER UPDATE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

CREATE TRIGGER wp_posts_deleteafter AFTER DELETE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

WAIT A MINUTE !!! The table wp_posts_idtracker is a BLACKHOLE table. It stores nothing. So where do the statistics get written ??? Make sure the MySQL Instance with wp_posts has binary logging enabled. Big deal, the stats get written to the binary logs. How can you read those stats ???

MySQL REPLICATION

Using commodity hardware, employee the use of a MySQL Replication Slave. Setup the slave to only accept one table: wp_posts_idtracker. Place this line in /etc/my.cnf in the slave:

[mysqld]
replicate-do-table=wp.wp_posts_idtracker

Since this BLACKHOLE table would be replicated over to the slave, how will it store data ??? Convert it to MyISAM on the slave. Also, index it by ID and DTSTAMP and by DTSTAMP:

use wp
ALTER TABLE wp_posts_idtracker ENGINE=MyISAM;
ALTER TABLE wp_posts_idtracker ADD INDEX (ID,DTSTAMP);
ALTER TABLE wp_posts_idtracker ADD INDEX (DTSTAMP);

Now the Master will simply record every access to a row in wp_posts into the binary logs. MySQL Replicaton takes responsibility to record that over to the Slave. As an alternative to using a separate server for recording this information, you may want to create a second instance of MySQL on port 3307 and have it act as slave of MySQL running on port 3306. You must make sure that the datadir of MySQL on port 3307 is on a separate data volume from that of MySQL on port 3306. Another variation would be to alter the storage engine for wp_posts_idtracker on the slave with the MEMORY storage engine to reduce disk I/O (Caution: If you go with the MEMORY storage engine fro the table wp_posts_idtracker, remember to make the indexes BTREE instead of the default HASH index because running range queries against a HASH-indexed table has horrible performance, even for a MEMORY table). Still another variation would be to place the binary logs in a RAM Disk for even faster replication or placing the relay logs in a RAM Disk as well, along with further reducing disk I/O.

Thus far, IDs involved in INSERTs, UPDATEs and  DELETEs are stored safely in a Replication Slave. Are we forgetting any other types of access to wp_posts ??? Oh yes, SELECT statements. How do we record SELECTs ??? There are no triggers for SELECTs in any known RDBMS. How do we handle SELECTs ???

"SOCIALLY RESPONSIBLE" CODING

Since there are no special mechanisms for SELECTs, the developer must take personal responsibilitity to get the IDs that are encountered in SELECT queries and simply INSERT them into the table wp.wp_posts_idtracker. Suppose the ID you are requesting comes from a bulk gathering of IDs. Send them in bulk into wp.wp_posts_idtracker:

SELECT * FROM wp_posts WHERE ...
INSERT INTO wp.wp_posts_idtracker SELECT IDs,NOW() FROM wp_posts WHERE ...

Don't worry about the INSERT query causing Disk I/O accessing the IDs. Those IDs should be cached following the SELECT. If wp_posts is MyISAM, keys would be cached in the key cache (sized by key_buffer_size). If wp_posts is InnoDB, keys would be cached in the innodb buffer pool (sized by innodb_buffer_pool_size).

SUMMARY

Once you employ this rather unique infrastructure, you can simply connect to the slave and read your statistics.

This was just an example of how to create the type of stats recording you want for a table's ID. You may have other ideas in mind. Do not be afraid to try them. Always make sure you know the consequences of each milestone you are trying achieve when recording stats.

Give it a Try !!!

I TOLD YOU THIS WOULD BE CONVOLUTED !!!


Help understanding database design logic for a secondhand bookstore project in MySQL I am currently working on a small eCommerce based project that allows users to buy/sell second hand books to/from other users. I am having a hard time making design decisions on how to model the database. My requirements are as follows;

Users (should be able to both buy and sell books)
- User can sell a book.
- User can buy a book.

Temp Users
- This is for registering a user through email

Orders
- many orders can be made by one user.

Order_Details (I am yet to create it)
- stores information on each product that has been orderd

Products/Books
- many products belong to one category

Here is my first try at the design;

I'm having an issue on how to incorporate buying and selling for one user.I don't want to create a second users table for selling (unless that's the only way to do it.) products only.
Should I create a "sales" table where it holds the users_ID and information that holds the book that is meant to be sold?Would this be a temporary table like the temp_users table?

Any opinions would be helpful.

Regards

You don't sell to users, you sell to parties (companies or individuals). Some might play the role of users.
There are many parties involved in a sales/purchase order:

the vendor (you if you sell)
the buyer (you if you buy)
a party selling something on consignment
sales staff taking commission
the party that placed the order
the party that took the order (a clerk)
the party that is paying for the order
the parties to whom to ship the order items

Do yourself a favour and get a data model pattern book and don't reinvent the wheel :)

Ready-to-Use Database models example


PGP for securing my database? I have considered TDE and cell level encryption mechanism for securing my database, however these two cannot fully satisfy my requirements.

I've found that PGP may help me, but it is mainly used for mail services.

What is the technical feasibility for implementing PGP for my SQL server database, is there any possibilities for that?

TDE does not satisfy my requirements because it only protects data-at-rest.  I need to protect the data from an attacker who has access to the database while it is running on the server containing the encryption certificate, as well as the data contained within backups.

I'd also like to have some users able to see the decrypted values, while some other users cannot ever see the decrypted values.

I have implemented master-slave Replication, and need to enable security without affecting the replication.
Approximately 80% of coding has been completed, so its really hard to change the queries and stored procedures.
I need to enable security for selected tables only (payments, customer details, password, etc.)
I need to secure data from injection.

SQL Server has several built-in possibilities to secure data on a columnar basis.

ENCRYPTBYPASSPHRASE - use this to encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.
ENCRYPTBYKEY - use this to encrypt the data with a key stored in the master database.  Without access to the key, the data cannot be recovered.

Take the following example using ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE:

IF OBJECT_ID(N'dbo.EncryptedData', N'U') IS NOT NULL
DROP TABLE dbo.EncryptedData;
CREATE TABLE dbo.EncryptedData
(
    EncryptedDataID int NOT NULL IDENTITY(1,1)
        CONSTRAINT EncryptedData_PK
        PRIMARY KEY CLUSTERED
    , SomeData varbinary(1000) NOT NULL
    , SomeSalt binary(10) NOT NULL
        CONSTRAINT EncryptedDataSalt
        DEFAULT CRYPT_GEN_RANDOM(10)
);

DECLARE @passphrase nvarchar(100);
SET @passphrase = N'FlashFesterMariaAyin';

INSERT INTO dbo.EncryptedData (SomeData)
VALUES (ENCRYPTBYPASSPHRASE(@passphrase, N'This is a test', 1, N'SomeSalt'))

This shows the encrypted data held in the row:

SELECT ed.EncryptedDataID
    , ed.SomeData
    , ed.SomeSalt
FROM dbo.EncryptedData ed;

The encrypted row:

╔═════════════════╦════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╦════════════════════════╗
║ EncryptedDataID ║ SomeData                                                                                ║ SomeSalt               ║
╠═════════════════╬════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╬════════════════════════╣
║ 1               ║ 0x01000000DD0FF92C67C40F30C130CED841677AB69D34765D6FD57112AC35F59A87395101B32A145E3D42F8889ECF9BDEEC45F970EDD35432178CBDE896AAC27E9363AA20D09782A385048447 ║ 0x9DB6EDA465A5F93C7ADD ║
╚═════════════════╩════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╩════════════════════════╝

This shows how to display decrypted data:

SELECT ed.EncryptedDataID
    , CONVERT(nvarchar(100), DECRYPTBYPASSPHRASE(@passphrase, SomeData, 1, N'SomeSalt'))
    , ed.SomeSalt
FROM dbo.EncryptedData ed;

The decrypted row:

╔═════════════════╦══════════════════╦════════════════════════╗
║ EncryptedDataID ║ (No column name) ║ SomeSalt               ║
╠═════════════════╬══════════════════╬════════════════════════╣
║ 1               ║ This is a test   ║ 0x9DB6EDA465A5F93C7ADD ║
╚═════════════════╩══════════════════╩════════════════════════╝

The SomeSalt column is a pseudo-randomly-generated value used to guard against whole-value-substitution attacks, and provides significant protection against rainbow-table attacks.  Whole-value-substitution attacks consist of copying the encrypted value from one row to another row in order to bypass the need to know the encryption passphrase.


Properly delete data forever and free up the space it was using? I've been having issues finding a real answer to this question as the response is always "Don't do this, it's a bad idea, that space was obviously needed and it will just get used up again in quick order." But that response doesn't apply to me - I have a copy of the production database that I am using solely for developmental purposes - my read/write activites to be limited to a small amount of the database, but I will need to constantly restore the database to overwrite my changes, so I want to reduce the size of the database - I can achieve this by deleting the data in a handful of tables.
Here is what I have done so far:

Made sure database was in simple recovery mode
Ran DELETE FROM TABLE_NAME queries
Tasks > Shrink > Files > File Type: Log, Release Unused Space - this step was successful and reduced the .ldf to its initial minimum size.
Tasks > Shrink > Files > File Type: Data, Release Unused Space - this step did not do anything. It "executed" in half a millisecond and the .mdf is just as big as it was before running the DELETE queries...

Any input appreciated.

The first step is worthless because delete operations are completely logged.
Use truncate instead if you want to empty a table.
About the task 4 you have to reorganize pages because the release pages free only pages ad the end of the mdf file. It will takes a lot.
I suggest you add task 5: rebuild the indexes because shrinking the data file causes the indexes to fragment.


USERSTORE_TOKENPERM growing very fast I'm experiencing a very weird problem with userstore_tokenperm clerk recently.

We have an environment with some databases in contained mode executing in version SQL Server 2017. Anyway i'm facing this problem since the SQL Server 2016.

At a certain moment this cache starts to grow at a rate of 6GB by day. When this ocurr we have to do a failover and restart the principal node of AG.

Run DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') does not clear the cache. We are considering to use the trace flags 4621 as suggested by microsoft to avoid this memory pressure, but i want to know if someone has a tip to help me  to understand this behavior. This seems like a memory leak, because even running DBCC FREESYSTEMCACHE the objects are not released from memory.

We have around to 70 users in contained mode in this server and when the cache starts to grow it grow until starve the server for memory.

I tried to find out what kind of class of token is present and the worse instance have only this entrys.

In sys.dm_os_memory_clerks we already faced more than 30GB of consumption

Someone please can help with some troubleshooting hint or even with an experience about this kind of problem.

Thks in advance

Edit: sorry for not returning with a solution for this case.
The MS support suggested me change the language of user from us_english to NONE. The last response from the support was ... "Probably a memory leak when the user store token, which is 8 kb, needs to be created to store a certain word due to the default language setting. To have more details and confirm what word is causing this high memory usage, these other collections would be necessary. "
As I had no problem after the adjustment, it was not possible to capture any new dump and therefore the solution may have occurred due to another scenario including the applications that are used on this server. Thanks Brent Ozar by the hint


Multiple databases, one backup file Is it possible to backup multiple databases to a single .bak file and then restore individually?

The answer should be an obvious "yes" - this is one of the main options when setting up a Maintenance Plan.  Select multiple databases, then specify a single file.  When I do this, I get one .bak file containing a backup set for each database. The individual backup sets for each database are all there and look fine when I look into the contents of the file.

But when I try and restore any database after the first in the set, the restore wizard still only sees the files associated with the first database.  On the Files page, I only have two entries (data file, log file) always for the first database.  The restore task then throws an error:

  System.Data.SqlClient.SqlError: Logical file [first source db] is not part of database [target db]. Use RESTORE FILELISTONLY to list the logical file names.

And when I use RESTORE FILELISTONLY I only get results for the first database (same as what I see on the Files page in the restore wizard).

What gives?

I could script out the restore task and set the Logical File Names manually but I don't see any easy way to discover them, if they even exist. It looks like the first backup in the media set establishes the Logical File Names for the entire media set, making any other databases effectively inaccessible.

I'm finding almost zero information online about multiple databases in one backup file; most people want to span one database across multiple files.  But in my case, I have a CMS with 4 small related databases that need to be managed as a set. It would be vastly easier to have one consolidated backup file (and one Maintenance Plan, and one agent job) rather than having to do everything 4x.

There's an old SO question question here for SQL Server 2005 describing the exact same problem that says it's a bug that was patched with SP3.  But I'm running a fully-patched SQL Server 2014 (12.0.5207.0). I'm seeing this on two separate systems, one running Web Edition, one running Dev Edition.

Additional information

Going by this old article by Paul Randal, it seems you can simply reference the position number (WITH FILE = xx) if you're restoring in-place (WITH REPLACE) but since I am restoring to a different server (with different file paths) I also need to use WITH MOVE [Logical File Name] TO [new file path]. The same problem would exist if you're restoring a copy with a different name on the same server (like PROD => DEV).

It is well explained in Paul Randal's article as you mentioned in the question.

Let me walk you through an example.

Server 1 I am taking backup of 3 databases into one file.

BACKUP DATABASE [itdba] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT,NAME = N'itdba_backup'
GO
BACKUP DATABASE [itdbatest] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'itdbatest_backup'
GO
BACKUP DATABASE [itdbatest2] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'itdbatest2_backup'
GO

Now if a copy the backup to Server 2 as you mentioned restoring itdba database is like any other restore but you are having trouble restoring other 2 databases.

If you run below tsql

RESTORE HEADERONLY FROM DISK = N'D:\multipleDatabaseBackup.bak'

you will see the position of backups corresponding to databases.  There are many other columns but I am showing few only.

Take a note of itdbatest database with position 2. I can restore this to another server which does not have the same drive/folder structure as Server 1 using MOVE.

itdbatest database file name and location in server 1.

I copied the backup to Server 2 before restoring.

RESTORE DATABASE itdbatest FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\multipleDatabaseBackup.bak'
WITH REPLACE, FILE = 2,
MOVE 'ITDBATest' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ITDBATest.mdf',
MOVE 'ITDBATest_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ITDBATest_log.ldf'
GO


Group By on column from joined table its about the following query.

EXPLAIN SELECT phrase, infinitiv
FROM `dict_verbs_all` `t`
INNER JOIN `dict_verbs_all_language` `verbsGerman_c`
ON (`t`.`id`=`verbsGerman_c`.`verb_id`)
INNER JOIN `dict_verbs_german` `c`
ON (`c`.`id`=`verbsGerman_c`.`verb_language_id`)
WHERE (t.phrase LIKE 'spie%' AND t.language=1)
GROUP BY infinitiv
LIMIT 5

It uses all the time filesort

Using where; Using temporary; Using filesort

Here are my columns

CREATE TABLE `dict_verbs_all` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phrase` varchar(30) NOT NULL,
  `language` smallint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uPhraseLanguage` (`phrase`,`language`)
) ENGINE=MyISAM AUTO_INCREMENT=337740 DEFAULT CHARSET=utf8;

CREATE TABLE `dict_verbs_all_language` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `verb_id` int(11) DEFAULT NULL,
  `verb_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `U_verb_conjugation` (`verb_id`,`verb_language_id`),
  KEY `verb_id` (`verb_id`),
  KEY `conjugation_id` (`verb_language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dict_verbs_german` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `infinitiv` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uInfinitiv` (`infinitiv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have an index on all relevant columns but its not beeing used and I don't know why.
I hope you can help me how to eliminate using temporary and using filesort

Probably because the table "dict_verbs_german" is the last table joined.

When I last researched this, "using temporary; using filesort" meant MySQL was writing the the join result into a temporary table, then sorting. (See note below.) In order to avoid "using temporary; using filesort", you'd have to meet all these conditions.

dict_verbs_german is the first non-constant table in the JOIN order, and
dict_verbs_german has an index that marches the ORDER BY column list.

To avoid "using temporary", you'd have to meet all these conditions.

dict_verbs_german is the first table in the JOIN order, and
dict_verbs_german has an index that marches the ORDER BY column list.

Note: I'm talking about ORDER BY, because that's specifically what my research dealt with, and I'm pulling these notes from an email I wrote in 2010. I think the same optimizations applied to GROUP BY, but I could be wrong. Improvements to the optimizer over the last couple of years might have changed things, too.

MySQL documentation

  In some cases, MySQL cannot use indexes to resolve the ORDER BY . . ..
  These cases include . . . joining many tables, and the columns in the
  ORDER BY are not all from the first nonconstant table that is used to
  retrieve rows. (This is the first table in the EXPLAIN output that
  does not have a const join type.)

MySQL documentation for optimizing limit queries

(I don't think this last reference will help you with this problem, but it's good to know where to find it.)

Later . . .

Cutting the WHERE clause seems to eliminate the filesort. (That might not be true for real-world data.) Maybe you can create a view without a WHERE clause and a LIMIT clause, then query the view and still avoid a filesort. I'm sorry that, for personal reasons, I'm not able to test that for you right now.


How to transfer data from Sybase to SQL Server database? url reference How to import Sybase .db file into SQL Server database?

Please tell me how to transfer data from sybase to sql server database.

Method 1: You can use ODBC connections to create an SSIS package which will fetch the data from SYBASE database onto SQL server. On how to use or create such SSIS Package read Extracting data from Sybase SQL Anywhere using SSIS through ODBC.
Then Schedule the above created package via SQL agent job to update data on SQL server as per schedule which meets the requirement.

Method 2: You can create a Linked server which fetches the required data from Sybase to SQL as described here.

And then create a job executing the task that refresh data from SQL Anywhere onto SQL Server using OPENQUERY


I tried to produce the same ordering and having some trouble
but here are two different queries you can start with:

SELECT
    booki_id,bet_id,bettype,
    group_concat(odds order by bet) betodds,line
FROM
    bettable
GROUP BY
    booki_id,bet_id,bettype,line
;

SELECT * FROM
(
    SELECT
        booki_id,bet_id,bettype,
        group_concat(odds order by bet) betodds,line
    FROM
        bettable
    GROUP BY
        booki_id,bet_id,bettype,line
) AA
ORDER BY betodds
;

I also created sample data to test it with, and here it is:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use test
Database changed
MySQL (Current test) :: DROP TABLE IF EXISTS bettable;
Query OK, 0 rows affected (0.03 sec)

MySQL (Current test) :: CREATE TABLE bettable
    -> (
    ->     id       INT NOT NULL AUTO_INCREMENT,
    ->     booki_id INT,
    ->     bet_id   INT,
    ->     bet      INT,
    ->     bettype  VARCHAR(10),
    ->     line     FLOAT,
    ->     odds     FLOAT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

MySQL (Current test) :: INSERT INTO bettable (booki_id,bet_id,bet,bettype,line,odds) VALUES
    -> (123,321,1,'3way',0.0,2.3),
    -> (123,321,2,'3way',0.0,3.4),
    -> (123,322,1,'3way',0.0,1.1),
    -> (123,322,2,'3way',0.0,7.4),
    -> (123,323,1,'3way',0.0,1.3),
    -> (123,323,2,'3way',0.0,9.4);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL (Current test) :: SELECT
    ->     booki_id,bet_id,bettype,
    ->     group_concat(odds order by bet) betodds,line
    -> FROM
    ->     bettable
    -> GROUP BY
    ->     booki_id,bet_id,bettype,line
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.01 sec)

MySQL (Current test) :: SELECT * FROM
    -> (
    ->     SELECT
    ->         booki_id,bet_id,bettype,
    ->         group_concat(odds order by bet) betodds,line
    ->     FROM
    ->         bettable
    ->     GROUP BY
    ->         booki_id,bet_id,bettype,line
    -> ) AA
    -> ORDER BY betodds
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.02 sec)

MySQL (Current test) ::

You will have to play with the order of the whole rows from here.

Give it a Try !!!


Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's reasonably fast. When the where clause includes conditions for fields from both tables using an OR the query plan switches to a table scan and doesn't utilize any of the indexes.
All four fields that are being filtered on are indexed on their respective tables.
Fast query plan where I filter on 3 fields from one table: https://www.brentozar.com/pastetheplan/?id=Hym_4PRSO
Slow query plan where I filter on four fields...three from one table and one from another table: https://www.brentozar.com/pastetheplan/?id=r1dVNDRHO
Ideally I would like to understand why this is happening and how to nudge the query engine to utilize all the indexes.
I considered a union but unfortunately this legacy system is using ntext values which can't be unioned. Also note the join between the two tables is one to one so I really expected the optimizer to utilize the indexes, but maybe it doesn't know that?

It looks to me like it’s doing a Scan because it may well need rows from T553 if the condition in T1011 holds. On the other hand, if any of the conditions on T553 hold, it’ll need rows from T1011.
So indexes would have to be able to handle finding rows in T553 and then pulling in the relevant rows from T1011, and also finding rows in T1011 and pulling in the relevant rows from T553. And the Query Optimizer figures that it’s probably best to get a good join happening and to filter the results.
To make it better, use a UNION of the two ways through the query. This should help it decide to do it the preferred way and you can optimise each side of the Union as you need.
Something like:
SELECT *, ROW_NUMBER....
FROM
(
SELECT *
FROM T1617
WHERE
  C1402001100 LIKE @P0
  OR C200000001 LIKE @P2
  OR C200000020 LIKE @P3
UNION
SELECT *
FROM T1617
WHERE C260100004 LIKE @P1
) t

Use SELECT CAST(ntextCol as nvarchar(max)), ... for something which can be UNIONed.
One thing to keep in mind is the UNION operator will remove dupes, not only between the two datasets, but within them as well. There are PKs in place here though, based on the objects involved in the seeks. If it did apply here, the answer would be to include something that’s unique in the view on each side of the UNION and then leave it out in the outer query.


Fileshare witness for Multisite AvailabilityGroup setup If I am having a multisite AG with 2 nodes in the primary datacenter and 2 nodes in the DR - Where can I place the quorum for its continuous availability? I am assuming a 3rd site - but my management is not in favour of using Windows Server 2016. As of now, the only option I see is Windows Server 2012R2. If this is the case, what are my options?

Best case - third independent site and/or online somewhere that you can reach it from on premises. Otherwise, you need to put it in one of the sites (and it also needs to be highly available). QUorum is a model. You're configuring a witness. General rule of thumb: The core cluster group and the witness should always be in your primary site since that's where you care more.

You may potentially need to adjust votes on the nodes, and both sides should be able to see that FSW. If you need to fail over to Site B, you'll need a new FSW.


sql server configuration manager says it is on a 32 bit machine and it is not. Should we change that? when running the following script for Determining 32/64 bit in Powershell

cls
if ((gwmi win32_operatingsystem | select osarchitecture).osarchitecture -eq "64-bit")
{
    #64 bit logic here
    Write "64-bit OS"
}
else
{
    #32 bit logic here
    Write "32-bit OS"
}

I get a nice reply back:

  64-bit OS

However, when running the sql server configuration manager

I get it is running on 32 bits as you can see on the picture below.

now 2 questions:

where is sql server looking at to say it is running on a 32 bits system?

how do I change that?

ok 3rd question, is it worth changing?

It is misleading and according to some post it is a bug. You cannot change it.

Here is the connect item.

https://connect.microsoft.com/SQLServer/feedback/details/332566/in-configuration-manager-running-under-64-bit-os-proprty-is-set-as-no-where-it-is-installed-on-x64

See the response from Microsoft's Sam Lester.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e83b5ded-8cda-468d-ac36-1b8c31b1073b/running-under-64bit-os?forum=sqlsetupandupgrade

  It turns out that the label is very confusing.  There is a bug filed
  on it in the SQL bug database and it will hopefully get fixed in the
  future to make this clearer.  It turns out that this field shows if
  you are running a 32-bit version of SQL under a 64-bit OS (as in WoW).
  I had an X64 machine here with a 64-bit instance of SQL and a 32-bit
  instance of SQL.  The field shows "No" for the 64-bit version of SQL
  and "Yes" for the 32-bit version of SQL.  A bit misleading, but
  "correct" if you look at it in this way.  :)


Benefits of reindexing large bloated indexes in PostgreSQL I have a 228GB index on a 1.86TB table. My shared_buffers are 225GB on a host with 768GB RAM. The index is bloated by 75%. After reindexing it, I get about 85GB. I'm on PostgreSQL 10.9.

I think my current query times on the old index are good as-is. Postgres docs do say that Btree indexes require no maintenance [reference missing].

Given that I have other large indexes on other large tables, will Postgres work better given that the new index fits into shared buffers better?

Given that I have other large indexes on other large tables, will Postgres work better given that the new index fits into shared buffers better?

There is probably little if any benefit.  If all the other indexes are rarely used and this one is intensively used, then maybe there will be.  The gold standard would be try it and see.  But unless all your scans are index-only scans, the index and the pages of the much larger table the index points at will be fighting over shared_buffers, as they certainly both won't fit.

Just because something will fit in the shared_buffers, doesn't mean it will get loaded into shared_buffers (you could force it with pg_prewarm) and then stay there forever (PostgreSQL doesn't provide a way to "nail" things into shared_buffers).  And the buffer eviction algorithm is not all that clever, so don't count on them staying in shared_buffers just because it seems obvious to you that they should.

  I think my current query times on the old index are good as-is.

So why worry about it then?


Obtaining a MIN() value in a column in a subquery I have the two following tables:

CREATE TABLE PC (
    model ...,
    speed ...,
    ram...,
    ...
);

CREATE TABLE Laptop (
    model ...,
    speed ...,
    ram ...,
    screen...,
    ...
);

And am trying to create a query that gives the model numbers of all laptops with a speed slower than that of any PC.

I came up with:

SELECT model
FROM Laptop, (SELECT MIN(speed) AS speed FROM PC)slowpcs
WHERE Laptop.speed < slowpcs.speed;

However even after double checking that I had added a laptop that meets this criterion and running the code, it's giving me an empty results list.

INSERT INTO Laptop
(model, speed, ram)
VALUES
(2006, 3.00, 2048),
(2007, 1.73, 1024),
(2009, 2.80, 512);

INSERT INTO PC
(model, speed, ram)
VALUES
(1070, 2.00, 1024,
(1071, 2.10, 512),
(1050, 1.90, 512);

From your post:

INSERT INTO PC
(model, speed, ram)
VALUES
(1070, 2.00, 1024, --- Missing closing parenthesis ')'
(1071, 2.10, 512),
(1050, 1.90, 512);

Once that was corrected, your query worked for me.


Is a chain of foreign keys better than duplicating data? I'm new to database designing for real-world applications. I have an idea for an application where each student could leave an opinion about a university at which one was on an exchange (I will use Django + PostgreSQL if that helps). While I was thinking about the database design I came up with this schema:

The reason for such a design was that there will be a lot of information to store. The user, however, should be able to look for an opinion giving just his e.g. destination university. He should also be able to narrow down the scope of the search by providing additional information.
However, when I think of the schema right now I'm unsure whether I should add separate FKs to the Opinion table to avoid such FKs chain like in the image, or is it just fine?

I would keep this style of design. The only issue that this could begin to cause is a performance hit on reports when dealing with a lot of data, but you wouldn't start seeing it until the millions of rows range. This would be because any report would have to basically query a half dozen tables to get the information it needs, but in general this would give you a lot of flexibility to create queries and views to sort and filter the rows.
In the real world, you'll likely find that the University table would contain the fields for both Country and City, with them just as commonly being just text fields, instead of having lookup tables. This would improve query performance a bit, especially since the number of universities would not be nearly as large as the number of faculty or students. The cost for this would be an increase in storage requirements, since every row in the University table would have the full city and country names instead of the FKs.


Can a/some DBMS be installed as an OS Is there any DBMS that can be installed as an OS instead of as an app on a OS?
I wasn't able to find any.

Later edit, just to clarify:
I'm used to installing a DBMS (MySQL, MSSQL) as a server(ice) onto an OS(Windows, Ubuntu, etc.). I was trying to find out if there are some DBMS' that do NOT require an underlying OS and are 'installed' as the OS directly on hardware. I was asking here because googling didn't help.
Thanks for the answers I got, it seems that the answer is close to 'NO'

Yes. IBM i, formerly known as AS/400, is a platform with the operating system that has a relational DBMS as its integral part. Indeed, Db2 for i is in a way an alternative access method to the IBM i file system.


some SQL Server MSX targets show as Normal, Blocked I've centralized my farm's jobs in MSX, covering about 70 instances of SQL Server. Seemingly randomly, at least once a week, several instances will stop accepting instructions. Within the MSX window Manage Target Servers, instead of all instances showing as Normal, they show as Normal, Blocked. I have failed to figure out why this happens. Defecting and re-enlisting usually cures this ill, but that means having to re-add that target in any appropriate jobs, so I would rather resolve the cause than endure the workaround.

Any advice? I would like to keep using SQL Server's tools for multi-server administration, but am open to other avenues.

When using a centralized MSX (Multi-Server SQL Agent jobs), there are some shortcomings in the SSMS GUI for administering those multi-server jobs.

When a target server has a problem downloading instructions for a job, those errors will be listed in msdb.dbo.sysdownloadlist. However, in the SSMS GUI, this only manifests as the job showing as having a "Blocked" status.

You can use EXEC sp_help_targetserver @server_name = 'MyServer'; to see the instructions, including errors & unsent instructions.

Microsoft recommends re-syncing the entire server when you see that it is blocked. This is done by executing the command EXEC sp_resync_targetserver @server_name = 'MyServer';  Doing this will delete & re-create all jobs on the target server. This can be disruptive (you can imagine what might go wrong if you drop & recreate all your jobs at the same time a job should run). Additionally, it will result in losing job history on the target.

Instead, you may choose to manually repair the specific job(s) that are a problem:

DECLARE @server_name sysname = 'MyServer';
DECLARE @job_name    sysname = 'The Job That Broke';

-- Get the Job_ID
DECLARE @job_id uniqueidentifier;
SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = @job_name;

--Delete existing download instructions for this server/job
DELETE dl
FROM msdb.dbo.sysdownloadlist   AS dl
WHERE dl.target_server = @server_name
AND dl.object_id = @job_id;

--Post new instructions to delete & re-add job
EXECUTE msdb.dbo.sp_post_msx_operation
                @operation = 'DELETE',
                @object_type = 'JOB',
                @job_id = @job_id,
                @specific_target_server  = @server_name;
EXECUTE msdb.dbo.sp_post_msx_operation
                @operation = 'INSERT',
                @object_type = 'JOB',
                @job_id = @job_id,
                @specific_target_server  = @server_name;


Instant File Initialisation on Windows Oracle Server I require to add additional datafiles to a SmallFile tablespace. The process took several minutes on my system for 1 datafile.

Is it possible to allow Oracle the same capability as SQL Server using instant file initialisation? my googling was unproductive.

Thanks,

Chris

No, Oracle does not take advantage of that security policy and always zeroes out the files before writing to them.

I have no idea whether ASM behaves in a different way, but in the filesystem files are always initialized.


Contradiction in book related to variable scope in Stored Procedures I was reading the 70-461 TSQL training book and the part about stored procedures said this:

  Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by Proc1

But in the same book was this example code:

CREATE PROCEDURE Sales.ListCustomerByAddress
(@address AS NVARCHAR(60))
AS
DECLARE @SQLString AS NVARCHAR(4000);
SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = @address';

EXECUTE sp_executesql
         @statement = @SQLString,
         @params = N'@address NVARCHAR(60)',
         @address = @address;
RETURN;
GO

My understanding of the quoted statement is that the @SQLString variable and @address parameter cannot be visible to and referenced by the sp_executesql procedure? A variable is an object that holds a value so If it is not there or cannot be seen (for instance in @statement = ) how can the value be used or passed

Can someone help reconcile this contradiction?

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating how variable scope often works):

int OuterVariable = 5;

function int Sub1 (int Param1)
{
  int InnerVariable = 10;

  return InnerVariable + OuterVariable + Param1;
}

int Result = Sub1(3);

Since variables declared in an outer scope are often visible to inner scopes (i.e. global), the code above wouldn't error when calling Sub1 stating that OuterVariable hasn't been declared. Instead, the subroutine would have access to the value of OuterVariable (and in some languages, it might even be able to overwrite that value). Hence, the value of Result at the end would be 18.

So, when the book states:

  "Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by Proc1"

they are saying that T-SQL does not work like that. They are not saying that variables in Proc1 cannot be passed into a sub-Proc call; they are saying that if you want those values in the sub-Proc call, then you must pass them in explicitly via input parameters. Essentially, "visible" means "available without explicitly passing in via input parameters".

Maybe this will help illustrate:

CREATE PROCEDURE dbo.InnerProc(@InnerInputParam INT, @InnerName NVARCHAR(30))
AS
SELECT @InnerInputParam AS [InnerParam], @InnerName AS [InnerName];

-- SELECT @OuterName; -- commented out as this is a parse error
GO

CREATE PROCEDURE dbo.OuterProc(@OuterInputParam INT)
AS
DECLARE @OuterName NVARCHAR(30) = N'Mr. Outer Scope';

EXEC dbo.InnerProc
        @InnerInputParam = @OuterInputParam,
        @InnerName = @OuterName;
GO

Executing EXEC dbo.OuterProc 5; or EXEC dbo.OuterProc @OuterInputParam = 5; (same thing) will return:

InnerParam     InnerName
5              Mr. Outer Scope

Those values are "known" by dbo.InnerProc because they were passed in. And they were passed in because they were not "visible" to dbo.InnerProc, even though they existed in the outer / parent scope of dbo.OuterProc during execution. But neither @OuterInputParam nor @OuterName can be referenced in dbo.InnerProc because inner / child scopes have no knowledge of outer / parent scopes, at least not for variables (including Table Variables) and parameters.

Finally, to relate all of this back to the initial example in the book, this is why I do my best to change variable names when using sp_executesql: to make the code more readable. Changing one variable name might make it quite a bit clearer:

SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = @InnerAddress';

EXECUTE sp_executesql
         @statement = @SQLString,
         @params = N'@address NVARCHAR(60)',
         @InnerAddress = @address;


foreign key relationships and auto-incrementing in Sql Server I have two instances of a database with these two tables:

Foo

foo_id <-- primary key

Bar

bar_id <-- primary key

foo_id <-- foreign key

bar_id and foo_id are automatically incremented, read only primary keys.

What I want to do is this:

On database instance 1, I drop all the rows in foo and bar
Import the rows from database 2 to database 1

How can I keep the relationship between tables Foo and Bar in this setup? That is, whatever the primary key in Foo is, the corresponding row in Bar has the right foreign key value to Foo no matter what value foo_id has?

You can issue the command

SET IDENTITY_INSERT tablename ON

before inserting values into a table and call

SET IDENTITY_INSERT tablename OFF

afterwards.

According to the documentation for SET IDENTITY_INSERT (Transact-SQL):

  At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

and

  If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

To keep the foreign key relationship simply import the main table (Foo) before the details table (Bar).


Synonym being recreated randomly - possible to create some kind of trigger/log? We have a couple of databases with synonyms to link each other, one of the synonyms is randomly being recreate incorrectly with an additional set of square brackets in the database name. I've checked and rechecked every script we have and none seem to be the culprit.

At this point I'm trying to see if I can log any transaction which drops/creates a synonym, is it possible?

You could do this with a trigger, by logging the login + query executed

Create the logging table

CREATE TABLE DBO.SynonymTriggerLogging( ID int Identity(1,1) PRIMARY KEY NOT NULL,
                                 LoginName varchar(200),
                                 QueryExecuted varchar(max));

Create the trigger (If you only want to log login name + the drop statement)

CREATE TRIGGER SynonymTrigger
ON DATABASE
AFTER DROP_SYNONYM
AS
INSERT INTO DBO.SynonymTriggerLogging(LoginName,QueryExecuted)
SELECT ORIGINAL_LOGIN(),EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');

Testing

CREATE SYNONYM TEST
FOR dbo.test;

DROP SYNONYM TEST;

Result

SELECT *
FROM DBO.SynonymTriggerLogging;

ID  LoginName   QueryExecuted
1   rvertongen  DROP SYNONYM TEST

If you also need the hostname, use the following table & trigger

CREATE TABLE DBO.SynonymTriggerLogging( ID int Identity(1,1) PRIMARY KEY NOT NULL,
                                 LoginName varchar(200),
                                 QueryExecuted varchar(max),
                                 HostName varchar(200));
GO
CREATE TRIGGER SynonymTrigger
ON DATABASE
FOR DROP_SYNONYM
AS
INSERT INTO DBO.SynonymTriggerLogging(LoginName,QueryExecuted,HostName)
SELECT ORIGINAL_LOGIN(),EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),HOST_NAME()

Also take a look at example D from this link to prevent SYNONYM's from being dropped.


Is it possible to limit the maximum disk usage for a user Is it possible to set a limit on the amount of physical disk space a user (or group of users) can use within a single database? For example is there some method of associating a schema with a filegroup and then setting a maximum size for the filegroup? The actual database would have other tables on it and would therefore want to have other filegroups which were unlimited.

If your users have create permissions in their own schema, then you can set up a DDL trigger to capture table creation that occurs on a different filegroup than the one you've associated with that user. You can't prevent the table from being created, but you can roll it back and return an error message.

First, let's add a filegroup and a file with a max size to an existing database:

USE [master];
GO

ALTER DATABASE floob ADD FILEGROUP SomeUser;
GO

ALTER DATABASE floob ADD FILE
(
  NAME = SomeUser,
  FILENAME = 'C:\...wherever...\floob_SomeUser.mdf',
  MAXSIZE = 20MB -- your quota, this part is important!
) TO FILEGROUP SomeUser; -- this part is also important!
GO

Now let's create a schema and a login that can control that schema:

CREATE SCHEMA SomeUser;
GO

CREATE LOGIN SomeUser WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO

USE floob;
GO

CREATE USER SomeUser FROM LOGIN [SomeUser] WITH DEFAULT_SCHEMA = SomeUser;
GO

GRANT CONTROL ON SCHEMA::SomeUser TO dbo;
GRANT CREATE TABLE TO SomeUser;
GO

First of all, with these settings, this user can't create tables in dbo or any other schema other than their own, which means by default they will only be able to create tables on their limited file[group]. If you connect to the database floob as this user and try to create a table in dbo:

CREATE TABLE dbo.foo(id INT);

You get this error:

  Msg 2760, Level 16, State 1, Line 1The specified schema name "dbo" either does not exist or you do not have permission to use it.

And because of this user's default_schema, not specifying the SomeUser schema will still end up with a table created in SomeUser (though you really should be pushing for best practices here, especially in this case, and always specifying the schema name when creating or referencing objects).

Now we can create a trigger like this, which will roll back the entire batch in the case where a filegroup is not specified:

CREATE TRIGGER EnsureFilegroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @e XML = EVENTDATA();

  IF @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') = N'SomeUser'
     AND NOT EXISTS (SELECT 1 FROM sys.indexes AS i INNER JOIN sys.data_spaces AS d
       ON i.data_space_id = d.data_space_id
       WHERE d.name = N'SomeUser'
       AND i.[object_id] = OBJECT_ID(
        QUOTENAME(@e.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'))
        + '.' + QUOTENAME(@e.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)')))
       AND i.index_id IN (0,1))

  BEGIN
    RAISERROR('You must place your objects on the SomeUser filegroup.', 11, 1);
    ROLLBACK TRANSACTION;
  END
END
GO

It is particularly handy to keep the user, schema and filegroup names the same, which will make it easier to have this as programmatic. You can first perform a check that the schema being used is not one of your "umlimited" schemas, then proceed with a check that compares the schema name to the filegroup name. If it doesn't match, this user has rights to the wrong schema and is trying to create objects in the wrong place, and an error will be raised:

ALTER TRIGGER EnsureFilegroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @e XML = EVENTDATA();

  IF @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') <> N'dbo'
    -- or NOT IN (N'dbo', N'other unlimited schemas')
  BEGIN

    IF NOT EXISTS (SELECT 1 FROM sys.indexes AS i INNER JOIN sys.data_spaces AS d
       ON i.data_space_id = d.data_space_id
       WHERE d.name = @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)')
       AND i.[object_id] = OBJECT_ID(
        QUOTENAME(@e.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'))
        + '.' + QUOTENAME(@e.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)')))
       AND i.index_id IN (0,1))
    BEGIN
      RAISERROR('You must place your objects on your own filegroup.', 11, 1);
      ROLLBACK TRANSACTION;
    END
  END
END
GO


Should I use two databases or two schemas? We are currently having one Oracle instance on RAC with one database created. A new requirement came to our database team to temporarily (1-2 months) host a new application that is independent of existing one and after that we will migrate it to a new RAC.

Our technical teams discuss two options: the first suggestion is to create a new database (instance) for that application and the other is to have another schema on the same database.

From performance (how much is the overhead when creating a new instance compared to having the database on the same instance), administration (security, troubleshooting, backup, etc..) and migration perspectives, which option is better considering that we want to seamlessly migrate to the new server without complications or long outage.

When using one database with two schemas you also share SGA structures(like library cache) and also possibly TEMP tablespace and also backups. In rare cases one application can have negative impact on other's performance. Also if you are asked to revert one schema it will be much harder to "restore" just one schema. The same also applies to patching, it's harder to request a downtime from two different customers.

If you use two schemas then definitely create separate DATA and TEMP tablespaces. (You can not have separate UNDO).

If you use two databases then you can not share RAM between them - for buffer cache. You can partially bypass this limitation by using OS level buffer cache. Simply disable direct file access.
A separate database can also be easily migrated onto another machine using RMAN or simple pure scp/rsync.


Different Maximum server memory across Availability Replicas I have a scenario in SQL Server 2014 AlwaysOn High Availability where other services are running in one of the Secondary read-only replicas.

These services obviously demand additional memory. Is there any good practice or specific reason why SQL Server should have the same memory configuration across different replicas?

That’s due to fact it (SECONDARY) has to serve same level of work load as PRIMARY. I.e. when PRIMARY is memory intensive for 32GB, and SECONDARY limits to 24GB in the incident when it’s become PRIMARY it could perform same as primary the impact (8GB Memory) could be in various factors

Additional connections might not established
Existing requests might be slower
Log redo could be backlogged
Etc..


Compare ID and if true update another table I have 3 tables. 1 Table (Table A) with patients of a certain disease (heart disease for example), and another table with patients (Table B) with all kinds of diseases. The third table is the results table.
What I want to do is compare heart disease patients from table A (table A is only heart disease patients) to kidney disease Patients in table B. IF the there is a hit (returns results) in table B, update the results table with an 'x' under column kidney disease for that ID.
The results table is currently in this format:

ID|Kidney|CHD|Diabetes ... all the way for another 10 types of diseases

To summarise: We check table A against table B with the specified disease name and if there is a return of result for that patientid, the results table updates with an 'x' under that disease name for that specific patientID. I will then rerun the query changing the disease names (from Kidney to cancer for example) to find out if the heart disease patient has cancer?
Sample Data
Table A

PatientID
1
2
3
4
5
.... all the way to 812 (812 patients of heart disease)

Table B

PatientID|Age|Disease1|Disease2|Disease3...all the way to Disease 10
1         50  kidney   lung
2         35  kidney   heart     diabetes
3         94  cancer   pneumonia CHD

What is meant to happen is, for every PatientID in Table A, we compare to table B for that specific disease (example kidney) from disease 1-10 in table B, if there is a result, we then update the results table with an 'x' under that specific disease column in the results table. So in our example, for patient 1 and 2 should have an 'x' under kidney.
With our sample data, the result table should look like this when we run for kidney:

ID|Kidney|CHD|Diabetes
 1     x
 2     x

Can anyone point me in the direction to writing the query for this? The query checks all patientID (812 of them) from table A for a specific disease in table B and if it returns results it updates for that disease and patient with an 'x' in the results table.
If there is a better way to do this, please advise me on how to go about it!

I propose implementing a different and more versatile database structure, because the addition of a (base) table that retains persons or patients that are affected by only one particular disease introduces update anomalies and is unnecessary.
Business rules
I consider that the following business rules formulations are of prime importance:

A Person is affected by zero-one-or-many Diseases
A Disease affects one-to-many Persons

Therefore, you want to implement a common many-to-many (M:N) relationship.
Illustrative logical model
Then, from such formulations, I have derived an IDEF1X1 logical model that is shown in Figure 1:

As demonstrated, it is very important to define the entity types that are involved in the business domain under consideration, their attributes and how they are interrelated (including the cardinality of the relevant relationships).
In this case  Person and  Disease are independent entity types, each with their own attributes, and they are interrelated by way of an associative entity type, that I called PersonDisease, which holds the attributes that pertain to the relationship that takes place between the two mentioned entity types.
Each PersonDisease occurrence (or row once it is stored in a SQL database) is uniquely identified by PersonId along with DiseaseNumber, so I fixed this combination of attributes as the PRIMARY KEY of this associative entity type.
Derivable data
This model indicates that the Table B included in your answer is derivable data (obtained via DML operations that combine or compute columns from multiple tables), and not an entity type, neither independent nor associative; therefore you should not set it up as a base table.
Expository implementation
Consequently, I created the following DDL structure in terms of the logical model discussed above:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should carry out some testing sessions to define
-- the most convenient INDEXing strategies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Person
(
    PersonId        INT       NOT NULL,
    FirstName       CHAR(30)  NOT NULL,
    LastName        CHAR(30)  NOT NULL,
    GenderCode      CHAR(3)   NOT NULL,
    BirthDate       DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (PersonId),
    CONSTRAINT AK_Person_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    )
);

CREATE TABLE Disease
(
    DiseaseNumber    INT       NOT NULL,
    Name             CHAR(30)  NOT NULL,
    ParticularColumn CHAR(30)  NOT NULL,
    SpecificColumn   CHAR(30)  NOT NULL,
    Etcetera         CHAR(30)  NOT NULL,
    CreatedDateTime  TIMESTAMP NOT NULL,
    CONSTRAINT PK_Disease      PRIMARY KEY (DiseaseNumber),
    CONSTRAINT AK_Disease_Name UNIQUE      (Name)-- ALTERNATE KEY.
);

CREATE TABLE PersonDisease -- Associative table.
(
    PersonId        INT       NOT NULL,
    DiseaseNumber   INT       NOT NULL,
    DiagnosedDate   DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_PersonDisease                 PRIMARY KEY (PersonId, DiseaseNumber), -- Composite PRIMARY KEY.
    CONSTRAINT FK_from_PersonDisease_to_Person  FOREIGN KEY (PersonId)
        REFERENCES Person  (PersonId),
    CONSTRAINT FK_from_PersonDisease_to_Disease FOREIGN KEY (DiseaseNumber)
        REFERENCES Disease (DiseaseNumber)
);

This structure permits

storing n Disease instances (via simple INSERT operations) and
storing the relationship between n Person instances with n Disease instances.
managing Disease data independently of that which pertains to Person.

And avoids

employing ad hoc procedures that have to be carried out due to the update anomalies introduced by an additional disease-specific table.

Sample data
Let us suposse that the relevant database holds the data that follows:
-- 'Person' sample data.
INSERT INTO
    person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES
    (1, 'David', 'Smith', 'M', CURRENT_DATE, '20161031 13:14:02');

INSERT INTO
    Person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES
    (2, 'Nicole', 'Johnson', 'F', CURRENT_DATE, '20161031 13:14:02');

-- 'Disease' sample data.
INSERT INTO
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
   (1, 'Disease A', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
    (2, 'Disease B', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
    (3, 'Disease C', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
    (4, 'Disease D', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

-- 'PersonDisease' sample data.

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
   (1, 1, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
    (1, 2, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
    (2, 1, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
   (2, 2, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
    (2, 3, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
(2, 4, '20161031', '20161031 13:14:02');

So you might like to retrieve all the diseases by which all the persons are affected via, e.g., the statement displayed bellow:
SELECT Person.FirstName            AS "Patient first name",
       Person.LastName             AS "Patient last name",
       Disease.Name                AS "Disease name",
       PersonDisease.DiagnosedDate AS "Diagnosed date"
  FROM PersonDisease
  JOIN Disease
    ON Disease.DiseaseNumber = PersonDisease.DiseaseNumber
  JOIN Person
    ON Person.PersonId       = PersonDisease.PersonId;

As is also exemplified in this SQL Fiddle.
Inspecting all the other diseases suffered by persons that have heart disease
Let us assume that heart disease is primarily identified by disease number 3, so you can construct a SELECT statement like, e.g., the following one to see what other diseases are suffered by people with heart disease:
SELECT PersonDisease.PersonId,
       Person.FirstName,
       Person.LastName,
       PersonDisease.DiseaseNumber,
       Disease.Name
  FROM PersonDisease
  JOIN Person
    ON Person.PersonId              = PersonDisease.PersonId
  JOIN Disease
    ON Disease.DiseaseNumber        = PersonDisease.DiseaseNumber
 WHERE Person.PersonId              IN (SELECT PersonId
                                          FROM PersonDisease
                                         WHERE DiseaseNumber = 3)
   AND PersonDisease.DiseaseNumber <> 3;

You can review a comparable method in action in this new SQL Fiddle.
Of course, this and the query discussed in the preceding section can be defined as VIEWs so that you can SELECT data directly FROM them, if necessary.
There might well be other approaches to solve this requirement like, e.g., comparing the data stored in PersonDisease with a VIEW that includes only the persons who have heart disease, or a CTE as suggested by @Joishi Bodio in their answer. Some of them could be served by faster execution plans than the ones that correspond to the other approaches, so you should naturally make some tests and implement a reliable INDEXing strategy.
Using the CROSSTAB() Function
It appears that you want to construct some kind of PIVOT query, so you may find of help this Stack Overflow answer in which @Erwin Brandsteter details several usages of the CROSSTAB() function that, according to the PostgreSQL documentation, is included in the  tablefunc module.
Answer focus
The objective should be, as discussed above, to avoid appending the superfluous disease-specific base table.

Endnote
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


mysql - query which provides additional column with iterative number by condition I do have a table which looks like this.
Already grouped by user_id AND activity date:

[user_id] [activity_date]
123455     '2019-07-01'
123455     '2019-07-02'
123455     '2019-07-03'
222222     '2019-07-01'
222222     '2019-07-02'

Is it possible to write a query which gives me an additional column like this:

 [user_id] [activity_date]   [additional_counter]
123455     '2019-07-01'        1
123455     '2019-07-02'        2
123455     '2019-07-03'        3
222222     '2019-07-01'        1
222222     '2019-07-02'        2

It should just count up if the user_id has the same predecessor (same user_id).

For MySQL 8+

SELECT user_id, activity_date, COUNT(*) OVER (PARTITION BY user_id ORDER BY activity_date ASC) additional_counter
FROM tablename;

For MySQL 5+

SELECT t1.user_id, t1.activity_date, COUNT(t2.activity_date) additional_counter
FROM tablename t1, tablename t2
WHERE t1.activity_date >= t2.activity_date
  AND t1.user_id = t2.user_id
GROUP BY t1.user_id, t1.activity_date;

fiddle


What are the minimal MySQL grants for the debian-sys-maint user? What are the minimal privileges that debian-sys-maint@localhost needs for the mysql service to work correctly?

This answer on ServerFault suggests granting RELOAD only, but this is incorrect, as it prevents the mysql service from stopping or starting properly.

It depends on what you want to protect you MySQL server from.

To be able to automatically manage the MySQL db server, Debian has added the account debian-sys-maint@localhost.  That account is used to start, stop and do some other maintenance on the database without any user is needed to type in an administrator password.  That might look frighting at first, but actually are not, as long as you have the server protected from unauthorized use.

The debian-sys-maint@localhost user are only accessible from the local machine through localhost, so you can't connect to MySQL as that user unless you already are logged in on the server machine.
As the password for that MySQL account is only readable by the administrator account root, the attacker need to be root to be able to read and use it to access this Debian acccount.

$ ls -l /etc/mysql/debian.cnf
-rw------- 1 root root 666 sep 14  2010 /etc/mysql/debian.cnf

So, when the privileges for debian-sys-maint@localhost is of any real concern, you have a much worse problems with an unauthorized user with root access on your server. The access to the MySQL db is your least concern then.

Also notice that if an unauthorized user has access to the computer as root, there are nothing you can do to stop them from restarting MySQL with all authorization code in MySQL deactivated. With that, they have access to everything, like adding a new Administration account and then restart the MySQL server with authorization activated again. And you wouldn't noticed a thing, at first...

So no, this isn't as a large security problem as it might look at first sight.  So no, one doesn't need to change this from the access already given by Debian.


How can I write a trigger that can subtract values between two tables I have a table called transaction that has these fields

CREATE TABLE transaction (
    transaction_id INT AUTO_INCREMENT,
    status varchar(255),
    myPIN varchar(255),
    amount_paid varchar(255),
    PRIMARY KEY (transaction_id)
)  ENGINE=INNODB;

Another table called defaulters that has the following

CREATE TABLE defaulters (
    defaulters_id INT AUTO_INCREMENT,
    myPIN varchar(255),
    debt_amount varchar(255),
    PRIMARY KEY (defaulters_id)
)  ENGINE=INNODB;

Now I want the value to subtract if the value of status=1 in transaction table where myPIN is equal to the amount_paid then it subtract debt_amount-amount_paid to get debt_amount remain
 and it reflect the remaining amount in defaulters table in debt_amount field.

fiddle

Look for

CREATE TRIGGER trigger_name
AFTER INSERT
ON transaction
FOR EACH ROW
UPDATE defaulters
SET debt_amount = debt_amount - NEW.amount_paid
WHERE myPIN = NEW.myPIN
  AND NEW.status = 1;

fiddle


Check for records that break a foreign constraint (created with foreign_key_checks = 0)? Is there an easy way to check for any records in a table that breaks any constraint?

The constraint was created on a populated table using "foreign_key_checks=0".


Query To Show All Users Who Have Multiple Managers I have a database that holds user information.  I need a way to find out what users have multiple managers.  Below is a subset of data to illustrate the data structure.
Using MS SQL Server 2016 what would be a query to show me all userid and managerid where for each user the count of managerid > 1?
Declare @@Test Table
(
    userid varchar(100),
    mngrid varchar(100)
)

Insert Into @@Test Values ('abc-413', '619232'), ('abc-413', '39021'), ('xyz-999', '39021')

Select * FROM @@Test

You just need to use the GROUP BY and the HAVING clause to filter the Table down to only users with multiple managers like so:
SELECT userid
FROM Test
GROUP BY userid
HAVING COUNT(mngrid) > 1

Also I don't think @@Test is valid syntax (though I'm unable to test right now) so my example just uses a regular Table, but you can replace it with your actual Table name.


Query works manually, not from stored procedure - Safe update error I have a relatively simple update query that works fine when I manually execute it from a Mysql Workbench query window, but will not work when I call it as a stored procedure.  The stored procedure in question:
CREATE PROCEDURE `sp_UpdateGoal`(
    IN UpdateGoalUserName varchar(45),
    IN nGoalDate DATE,
    IN nAmount DECIMAL(9,2),
    IN nActiveGoal tinyint,
    IN nOriginalAmount DECIMAL(9,2),
    IN nChangeLog VarChar(100)
)
BEGIN
    UPDATE
        salesgoals
    SET
        Amount=nAmount,
        OriginalAmount=nOriginalAmount,
        `active`=nActiveGoal,
        ChangeLog=concat(ChangeLog,"\r\n",now()," ",nChangeLog)
    WHERE
        salesgoals.username=UpdateGoalUserName
        AND salesgoals.goalDate=nGoalDate;
END

The call to the stored procedure I'm using:
CALL sp_UpdateGoal( 'lauren', "2020-01-01",150000,1,150000,"Testing sp_UpdateGoal");

which returns
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
I would prefer not to disable safemode.
The manual query that works:
   UPDATE
        salesgoals
    SET
        Amount=150000,
        OriginalAmount=150000,
        `active`=1,
        ChangeLog=concat(ChangeLog,"\r\n",now(),'Testing manual query')
    WHERE
        username='lauren'
        AND goalDate='2020-01-01';

SalesGoals is a simple table:
CREATE TABLE `salesgoals` (
  `username` varchar(45) NOT NULL,
  `GoalDate` date NOT NULL,
  `Amount` decimal(9,2) NOT NULL,
  `ChangeLog` varchar(500) NOT NULL,
  `Active` tinyint DEFAULT '1',
  `OriginalAmount` decimal(9,2) DEFAULT NULL,
  PRIMARY KEY (`username`,`GoalDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It looks to me like when I call the stored procedure, it's ignoring my WHERE clause. I've been staring at this for a crazy long time and don't see anything wrong. What am I missing?
Thanks!

Right From the MySQL 8.0 Docs

It is possible for UPDATE and DELETE statements to produce an error in safe-updates mode even with a key specified in the WHERE clause, if the optimizer decides not to use the index on the key column:

Range access on the index cannot be used if memory usage exceeds that permitted by the range_optimizer_max_mem_size system variable. The optimizer then falls back to a table scan. See Limiting Memory Use for Range Optimization.

If key comparisons require type conversion, the index may not be used (see Section 8.3.1, “How MySQL Uses Indexes”). Suppose that an indexed string column c1 is compared to a numeric value using WHERE c1 = 2222. For such comparisons, the string value is converted to a number and the operands are compared numerically (see Section 12.3, “Type Conversion in Expression Evaluation”), preventing use of the index. If safe-updates mode is enabled, an error occurs.

As of MySQL 8.0.13, safe-updates mode also includes these behaviors:

EXPLAIN with UPDATE and DELETE statements does not produce safe-updates errors. This enables use of EXPLAIN plus SHOW WARNINGS to see why an index is not used, which can be helpful in cases such as when a range_optimizer_max_mem_size violation or type conversion occurs and the optimizer does not use an index even though a key column was specified in the WHERE clause.

When a safe-updates error occurs, the error message includes the first diagnostic that was produced, to provide information about the reason for failure. For example, the message may indicate that the range_optimizer_max_mem_size value was exceeded or type conversion occurred, either of which can preclude use of an index.

For multiple-table deletes and updates, an error is produced with safe updates enabled only if any target table uses a table scan.

Based on the these things, you need to run this
select @@global.sql_safe_updates,@@session.sql_safe_updates;

If your output shows 1 for @@global.sql_safe_updates,

mysqld process was started up with sql_safe_updates or i-am-a-dummy.
Otherwise, it was not started that way.

If your output shows 0 for @@session.sql_safe_updates, that means your client is setting it

The client will automatically have @@session.sql_safe_updates set to whatever is in @@global.sql_safe_updates
If the client has @@session.sql_safe_updates set to something different from @@global.sql_safe_updates, then the client is personally setting it.

The fact that you can execute it by hand shows that the client program you are using to connect allows you to run the query. You may need to review the setting in MySQL Workbench to see if --safe-updates is being imposed on stored procedure. Other than these things, please check out suggestions from StackOverflow on how to tweek safe-updates off and on in the Workbench.


Deferred Name Resolution for Linked Servers I need to be able to create stored procedures that point to a linked server where the database does not yet exist on the linked server.

The thought process is that the above mentioned stored procedures are a part of a larger Database Creation script. My company wants to run this database creation script on the live servers to create new databases, make sure everything is OK, add all data, and then migrate select (daily snapshots) data to a Report server (the linked server).

I have created Synonyms that will be in the database creation script that point to a linked server called EDI_Report_Server. For example:

IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE object_id =
               OBJECT_ID(N'[dbo].[EDI_SnapshotLog]') )
BEGIN
       SET @SynonymCreateSQL = N'CREATE SYNONYM [dbo].[EDI_SnapshotLog]
                               FOR [EDI_Report_Server].' + DB_NAME() +
                               '.[Snapshot].[SnapshotLog]'
       EXEC dbo.sp_executesql @SynonymCreateSQL
END

And in this database creation script I will create a stored procedure that has code similar to this:

INSERT INTO EDI_SnapshotLog (
         [SnapshotLoadStartTime]
        ,[SnapshotLoadEndTime]
        ,[SnapshotLoadResult]
        ,[SnapshotValidationErrors]
        )
...
...

Trying to run it gives the error:

  The OLE DB provider "SQLNCLI10" for linked server "EDI_Report_Server" does not contain the table """."Snapshot"."SnapshotLog"".

Must I create the database and tables on the linked server first, or is there a way to defer the name resolution on linked servers?

Thank you for your time

This is most likely not possible. According to the MSDN page for Deferred Name Resolution and Compilation:

  Note
  Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. ...

And in fact, over a Linked Server it does not seem that Deferred Name Resolution works at all. I tried (in both SQL Server 2008 R2 RTM and 2012 SP3) with both a Synonym and a direct Linked Server reference to a non-existent Table, and both failed. So it seems that Linked Servers do not support Deferred Name Resolution at all.

However, one thing you might be able to do is to:

Create a single "template" database on the remote server (the DB should have the tables and any other referenced objects created as well)
Create all of the synonyms to point to the template DB
Create the Stored Procedures
DROP and reCREATE the synonyms when the "real" database has been created


What impact do queries have on a server's resources? This is a really ignorant question, but I'm wondering what impact queries have on a server's resources in general and can't seem to find a straight answer through searching.

For example, suppose a query takes 0.1s to execute. Does that mean a CPU/core is tied up at 100% for that amount of time?

As an extreme example, what would happen if a query took a full minute to execute? Would the server be completely bogged down during that time, or do database management systems internally limit how much CPU, memory, etc., that they allow themselves to use so as to prevent locking up a system?

A query takes some combination of CPU and I/O.  It might be 100% and 0% if everything it needs is cached in RAM, or it might be 1% and 99% if nothing is cached.  Or anywhere in between.

Only one "core" of the CPU will be used for one connection in MySQL.  Multiple connections could use all the cores; but I rarely see that.

Another issue is "locks" or "mutexes".  This is where one connection is busy doing something, but has a lock on something; and another connection wants that something.  Now the second connection has to wait, not using CPU or I/O.

So, 0.1 seconds (or a full minute) does not really say anything other than that the user had to wait that long for the answer.  It will be some combination of CPU (one core), I/O, and being blocked.

The Operating system is responsible for parceling out resources such that the system never appears to be "locked up", and everything gets done eventually.

I have seen a query take 57 hours; it was mostly I/O.  I have seen queries that I could estimate would take months.  And I have seen queries that take far less than 0.1 second.

And I have seen systems that are running over a 1000 queries per second even though every query took more than 1/1000th second.  This is the magic of Operating systems.


postgres fetch record from partitions without the check constraints I am using postgres 9.4,
I have a messages table that has 100M records, messages belongs to one feed_id, so I decided to partition the tables by posted_at.

 Table "public.messages"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
 id_str                       | character varying(255)      | not null
 feed_id                      | integer                     |
 replied_parent_id            | character varying(255)      |
 shared_parent_id             | character varying(255)      |
 message                      | character varying(255)      |
 posted_at                    | timestamp without time zone |
Triggers:
    messages_insert_trigger BEFORE INSERT ON messages FOR EACH ROW EXECUTE                              PROCEDURE create_messages_partition_and_insert()
Number of child tables: 231 (Use \d+ to list them.)
Child tables: messages_y2008_w45,
          messages_y2015_w01,
          messages_y2015_w04,
          messages_y2015_w03,
          messages_y2015_w02,
          messages_y2009_w23,
          ....

\d messages_y2008_w45
Table "public.messages_y2008_w45"
-- SAME AS messages
Indexes:
    "messages_y2008_w45_pkey" PRIMARY KEY, btree (id_str)
    "index_messages_y2008_w45_on_posted_at" btree (posted_at DESC)
Check constraints:
    "messages_y2008_w45_posted_at_check" CHECK (posted_at >= '2008-11-03 00:00:00'::timestamp without time zone AND posted_at < '2008-11-10 00:00:00'::timestamp without time zone)

Inherits: messages

There is a problem when I try to query parent messages. A parent message can have multiple child messages that posted at different date.
I fetch the child messages using the date range, and select the shared_parent_id from the child messages. Then I try to fetch the parents messages, but I don't have the date range for the parent messages, the parent messages can be outside the child messages' date range. What I can do is add a parent_posted_at in the messages table. But updating all the existing messages will take very long.
Also it's not ideal to add parent info in the child messages.

Is there any better way?

My question maybe more related to the design of the DB, should I use partition for this case or maybe partition differently?

EDIT:

Here's some example rows: I want to all 2015 May messages. So I selected [3,4,5,6]. But [5,6] are referring to [1,2].

To select the May messages, I have the date range, so it fits the check contraints, but after that I have to find the [1,2], the problem is I do not know the parent's posted_at, I only have the ids. So it will scan through all the partition tables. Maybe I should not partition by posted_at, instead I should partition by feed_id?

 id_str | feed_id | replied_parent_id | shared_parent_id |  message |      posted_at
--------+---------+-------------------+------------------+----------+---------------------
      1 |      26 |                   |                  | message1 | 2015-03-03 15:55:55
      2 |      26 |                   |                  | message2 | 2015-04-03 15:55:40
      3 |      26 |                   |                  | message3 | 2015-05-03 15:54:41
      4 |      26 |                   |                  | message4 | 2015-05-03 15:54:34
      5 |      26 |                   |                1 |          | 2015-05-03 15:54:15
      6 |      26 |                   |                2 |          | 2015-05-03 15:54:00

(6 rows)

A few details are still not clear, but let's see, what we can do now.
First, having about 100M rows and 231 partitions sounds not that good.  The resulting tables will be too small, in turn their number too high - I cannot tell the threshold, but at some point the query planning migt get too expensive.  I think it is quite possible that yearly partitions would be enough.  Alternatively, if you really want to fetch a whole month at a time, create monthly partitions.

Now to the actual problem.

It is not quite clear to me why you have rows in the parent table.  The usual way of partitioning is that the parent is empty, and every row is redirected to one of the children.

At the same time, is you have an index on posted_at of the parent table (as you have it on the children), finding rows in the parent based on the timestamp is easy.

On the other hand, while I'm not sure which column shared_parent_id refers to, you can define an index on it, too - looking rows up based on this will be easy, too.

The only thing still has to be added is tell your query to look for parents in the parent table only.  Let's have a look at a possible query:

WITH child_messages AS (
    SELECT shared_parent_id, {other interesting columns}
      FROM messages
     WHERE posted_at {matches your needs}
)
SELECT *
  FROM child_messages

UNION ALL

SELECT shared_parent_id, {other interesting columns}
  FROM ONLY messages -- this way it does not go to the children
 WHERE {unclear column} IN (SELECT shared_parent_id FROM child_messages);

The WITH query may pick up rows from the parent, too - this you may or may not want, adjust the query accordingly.

Furthermore, the performance might not be ideal, in this case there is room for tweaking the query (eg. a JOIN instead of the IN(), pushing the query in the WITH clause into a (sub)query and so on).

And a final notice: varchar(255) is usually a sign of a value of unknown-before length - if you really want to constrain it, you may want to choose a meaningful limit.  Otherwise, an unlimited varchar (or text) has a slight performance advantage in PostgreSQL over the limited ones.  Furthermore, from your example it seems that shared_parent_id is a number (integer) - use the best fitting type.


CTE Needing ; Or Not I have discovered that when I run a CTE in a query window it must have the leading ; but if I am going to run a CTE in a stored procedure it does not need the ;

To me, it would be needed in both places or neither place.  Why is this punctuation need in one area and not the other?

It's not that a CTE needs a leading ;, but that the statement before must end with a ;.

If the statement with the CTE is the first statement in a batch (or the first statement in your SP), then there's no statement before it to end with a ;

There's a StackOverflow question about it with more detail.


MongoDB cloneCollection between CentOS servers times out at 2 hours I'm trying to clone a MongoDB collection from CentOS server A to CentOS server B. Both are running 2.4.6. The copy works great until exaxctly 2 hours, at which point I get a 9001 socket exception: "errno:10054 An existing connection was forcibly closed by the remote host."

I have tried to set the tcp_keepalive_time to 3600, down from 7200 (2 hours) on both servers with the hope that my keep alive probes will tell routers to keep my connection alive after the 1 hour mark.

No dice. Even with a shorter keepalive time (and 70 second interval for keepalive probes), my connection dies at 2 hours.

Does anyone know how to set up my servers and/or my mongodb configuration to allow my tcp connection to be active past 2 hours?

Thanks!

The connection being killed after 2 hours, even when active (keepalive would only come into play for an idle connection, which is why it had not impact on your issue), suggests that this is a firewall or similar in the path between the two servers that is killing the connection.  You either need an exception to that rule from your network admin or you need to figure out a different way to copy the data, preferably one that can resume following an interruption.

The easiest alternative I can think of is to use mongodump to dump out the collection locally on the first MongoDB instance, SCP/FTP the files (with a client that supports resuming transfers) to the second machine, then use mongorestore to load the collection locally into the second MongoDB instance.


SQL TFS Visual Studio Solution: One Database or Multiple Databases When creating a TFS Visual Studio 2017 solution with SQL Server 2016, should the solution contain

one database project per solution, or
multiple database projects in a single solution?

Our system has databases which are interlinked/cooperate in creating one service.
I believe the correct answer is "multiple databases in one TFS Visual Studio solution"; please validate.

Thanks,

A solution is meant to cover multiple like projects together. So for example, I have a "Data Warehouse Solution" that has all of my SSDTs related to our data warehouse. I then have a different solution for my "RDBMS Project," with more projects inside of that. It is a logic grouping for user ease.

There is nothing stopping you from having one master solution with tons of projects, or one project per solution.

EXCEPT

I found the need to have an SSDT for the database server itself, if you are keeping track of server objects created at the server level. For example, security objects. Then I found that you couldn't create a reference to the database server. You had to create a SSDT project just for it.


Service Discovery for MongoDB What is the best way of implement a Service Discovery like ETCD for use in a mongodb cluster? I wish have, 1 master for HA and two nodes, in my master i have a static ip, but nodes maybe can changes ip. When a node has been recreated, this should connect to master mongo for update the replication cluster.

As renaissancedev blog documentation here In order to build flexible and maintainable cloud infrastructure we need a way to connect services together with minimal effort. For a long time DNS has been the way to do that, but it also poses a different set of problems. How do you keep the DNS entries up to date? How do you make sure that the instances that are registered in DNS are actually healthy and able to serve traffic?

Consul from Hashicorp is a modern answer to these problems that is being widely adopted by teams of all sizes. It is a service discovery layer for your datacenter, as well as being a distributed key-value store and able to perform cross-datacenter replication and discovery. One of the wonderful things about it is the fact that the service discovery layer can be used via a built-in DNS interface. Services that are registered in Consul can have an associated health-check which will cause a failing instance to be removed from the list of DNS entries for that service. All-in-all it’s a pretty great system that is definitely worth considering if you haven’t already started using it yourself.

For Example the Solution of this problem

Once I fully understood what the failure was, the solution became obvious. Since you can register arbitrary service names in Consul and associate a variety of different health checks, all I had to do was set one up that would only point to the node that was currently the master. In order to make that work I wrote a scripted health check that would run the following script every 10 seconds:

#!/bin/bash
ISMASTER=$(/usr/bin/mongo --quiet --eval 'db.isMaster().ismaster')
if [ "$ISMASTER" = "true" ]
then
    exit 0
else
    exit 2
fi

I then configured the Consul agent on the MongoDB instances with this service definition:

{
  "services": [
    {
      "check": {
        "interval": "10s",
        "script": "/consul/scripts/mongo_is_master.sh"
      },
      "name": "mongodb-master",
      "port": 27017,
      "tags": [
        "mongodb",
        "master"
      ]
    }
  ]
}

Whichever instance is passing that check will be registered as the mongodb-master.service.consul DNS record in Consul. That means that the clients just need to be configured to connect to mongodb://mongodb-master.service.consul:27017/database?replicaSet=rs0&readPreference=primaryPreferred and everyone will be happy.

For your further info here and here


SSAS (Analysis Services) 2014 - Cumulative Update 1 for SP1 I just recently applied CU1 for SQL Server 2014 SP1 (http://support.microsoft.com/en-us/kb/3067839) to my development workstation and one PROD server. Analysis Services continues to report itself as build 12.0.4100.1 (which is SP1) and not build 12.0.4416.0. Given that there are quite a few SSAS fixes in this update, this doesn't seem to be right.

When I look at the file information for CU 8 (http://support.microsoft.com/en-us/kb/3067836), I see a whole host of SSAS file updates that I do not see in CU 1 for Service Pack 1.  From my outside-of-Microsoft perspective, it seems that the SSAS updates were not included in this update as advertised.

Can anyone corroborate?

Thanks!

Late yesterday (7/8/2015), the Microsoft SQL Server Engineering Team confirmed that the Analysis Services hotfixes were indeed missing from the release of this CU. They intend to fix this in CU2 for SQL Server 2014 SP1.  More information in the update message on the bottom of their blog post: link to SQL Release Services blog entry


what could be preventing my transaction log autogrowth to be set to UNLIMITED? I have been trying to set the autogrowth of one of my databases to unlimited but despite whatever I do it remains the same.

First thing I have checked if something is holding any locks to my database:

select radhe='kill ' + cast(DTL.request_session_id as varchar(3))
      ,DTL.request_session_id
      ,DTL.resource_type
      ,DTL.request_type
      ,DTL.request_status
      ,DEST.TEXT
      , SP.spid
      , SP.blocked
      , SP.status
      , SP.loginame
from sys.dm_tran_locks DTL
INNER JOIN sys.sysprocesses SP
       ON DTL.request_session_id = SP.spid
   --INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id]
   CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST
where 1=1
  and resource_type = 'database'
  and resource_database_id = db_id('junofinance')
  and request_type = 'LOCK'
  and request_status = 'GRANT'

but there was nothing holding any database locks.
so I went ahead and ran the following:

use master
ALTER DATABASE [JUNOFinance]
MODIFY FILE ( NAME = N'JUNOFinance_log', MAXSIZE = UNLIMITED)
GO

when I check about the log reuse I get the following:

select log_reuse_wait_desc, * from sys.databases
where name = 'junofinance'

but that is not the problem, as after I few times trying these scripts I got:

then when I check the log maxsize I still get it limited:

exec sp_helpdb 'junofinance'

I have even tried some silly things like disabling the autogrowth and setting it to UNLIMITED

USE [master]
GO

GO
ALTER DATABASE [JUNOFinance] MODIFY FILE ( NAME = N'JUNOFinance_log', MAXSIZE = UNLIMITED, FILEGROWTH = 0)
GO

then it worked fine, go figure!

but then when I re-enabled it, it went back to the limitation:

USE [master]
GO

GO
ALTER DATABASE [JUNOFinance] MODIFY FILE ( NAME = N'JUNOFinance_log', FILEGROWTH = 10240KB )
GO

On this server in particular, I cannot upgrade it, so we are on:

  Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) - 13.0.5337.0 (X64)
  May 16 2019 02:24:21   Copyright (c) Microsoft Corporation  Enterprise
  Edition:  Core-based Licensing (64-bit) on Windows Server 2012 R2
  Datacenter 6.3  (Build 9600: ) (Hypervisor)

If you open BOL article sys.database_files (Transact-SQL)

you'll find that
268435456 = Log file will grow to a maximum size of 2 TB

2 Tb is the max size of SQL Server database log file, if you need it more than 2Tb you add the second log file.

Here you can find Maximum Capacity Specifications for SQL Server that states that 2Tb is the maximum log file size: