Why is there a writable instance inside a secondary region for MySQL AWS Aurora Replica?

When you create Amazon RDS instance it creates default instance with 2 endpoints, a writer and a reader endpoint with MultiAZ.
The reader endpoint is always a readonly, and it switches during failover.
Its provided by AWS to achieve the high avialablity.


Predict MySQL errors when turning on STRICT mode(s)

There are few, if any, tools are available.
You really must study the code enough to understand it, before thinking about automated fixes.
You can probably study each problem in depth and fix it before moving on.  For example, changing 0 dates to NULLs is probably an ALTER plus an UPDATE.  But then the code needs changing to test for NULL instead of 0.


Multiple SQL Server instance install question for AlwaysOn

Unless you can point to the direct requirement you are referring to, I do not see any requirement in Skype for Business (SFB?) that states it has to be on a dedicated instance.

I think what you may be referring to is the database needs to be in a dedicated Availability Group. If that is the case you can create another AG on the same instance and cluster to support your database for SFB. A given SQL Server instance can support multiple AGs and each one can have their own listener.


Internals of backup compression with TDE (SQL Server)

That has made us wonder whether the backup process decrypts the data, compresses it, encrypts that result, then backs up that to disk.

This is exactly how space saving is achieved. When backup process run page is decrypted and loaded into memory, then compressed and then encrypted and saved onto disk. The process is mentioned in Backup TSQL Doc

  Starting with SQL Server 2016 (13.x), this enables an optimized compression algorithm for TDE encrypted databases that first decrypts a page, compresses it and then encrypts it again.

I am assuming here that since you are getting good mileage with backup compression on TDE database you must be using SQL Server 2016 patched to latest SP and CU.
I strongly suggest you to read Backup Compression for TDE Enabled Database

Since you are using SQL Server 2017 and your TSQL script uses MAXTRANSFERSIZE> 65536 you will get benefit of backup compression for TDE enabled database. But I strongly suggest you to patch SQL Server 2017 to latest CU


Query not showing in dba_hist_active_sess_history

Afer checking that you are indeed on the correct database and if ASH- and AWR reports for both instances don't show any long running queries it's time to get in touch with your application developers. Try to find out which statement exactly is slown and why.

I had similiar issues with various applications and application environments and issues I encountered where

jdbc:thin behaviour different form jdbc:oci
issues with different ojdbc driver versions
code changes that split a transaction in multiple small connections resulting in the database doing nothing but opening and closing connections
configuration issues with connection pools

So you might have to look in different locations.


How to fetch data from a table which don't have a unique id

You want a pivot table (which isn't a inbuilt function in MySQL, but there are workarounds)

A rough example (I cant test this here):

select ID
   sum(if( sec = 'A', 1, 0)
       * points) as Sec A,
   sum(if( sec = 'O', 1, 0)
       * points) as Sec O,
   sum(if( sec = 'U', 1, 0)
       * points) as Sec U,
   sum(if( sec = 'B', 1, 0)
       * points) as Sec B,
   sum(if( sec = 'S', 1, 0)
       * points) as Sec S,
   sum(if( sec = 'T', 1, 0)
       * points) as Sec T,
   sum(if( sec = 'Y', 1, 0)
       * points) as Sec Y,
   sum(if( sec = 'Z', 1, 0)
       * points) as Sec Z
  from [schema].[table]
 group by id
;

NOTE: I've put section Y and Z as your additional sections as they don't appear in your example with what they are

If that doesn't work directly here's some external links with more detailed examples:

https://www.toadworld.com/platforms/mysql/w/wiki/6349.pivoting
http://modern-sql.com/use-case/pivot


How can you have two auto-incremental columns in one table?

What you are proposing to do can only be done with MySQL cleanly under three(3) conditions

CONDITION #1 : Use the MyISAM storage engine
CONDITION #2 : Make auto_increment column part of a compound primary key
CONDITION #3 : Each auto_increment for a given type must exist in its own row
See the auto_increment documentation for MyISAM

Here is your original table layout

CREATE TABLE `invoices` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoicenumber` mediumint unsigned NOT NULL,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';

Based on the three conditions I just mentioned, here is the new proposed table layout:

CREATE TABLE `invoices` (
  `invoicenumber` mediumint unsigned NOT NULL auto_increment,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';

Here is an example via sample data and SQL:

drop database if exists user1162541;
create database user1162541;
use user1162541
CREATE TABLE `invoices` (
  `invoicenumber` mediumint unsigned NOT NULL auto_increment,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';
INSERT INTO invoices (branch,date,client) VALUES
('A',DATE(NOW()),'John'),
('B',DATE(NOW()),'Jack'),
('A',DATE(NOW()),'Jeff'),
('B',DATE(NOW()),'Joel'),
('A',DATE(NOW()),'Jane'),
('B',DATE(NOW()),'Joan'),
('A',DATE(NOW()),'June');
SELECT * FROM invoices ORDER BY branch,invoicenumber;

Here it is executed:

mysql> drop database if exists user1162541;
Query OK, 1 row affected (0.01 sec)

mysql> create database user1162541;
Query OK, 1 row affected (0.02 sec)

mysql> use user1162541
Database changed
mysql> CREATE TABLE `invoices` (
    ->   `invoicenumber` mediumint unsigned NOT NULL auto_increment,
    ->   `branch` enum('A','B') NOT NULL,
    ->   `date` date NOT NULL,
    ->   `client` varchar(100) NOT NULL,
    ->   PRIMARY KEY (branch,invoicenumber)
    -> ) COMMENT='' ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO invoices (branch,date,client) VALUES
    -> ('A',DATE(NOW()),'John'),
    -> ('B',DATE(NOW()),'Jack'),
    -> ('A',DATE(NOW()),'Jeff'),
    -> ('B',DATE(NOW()),'Joel'),
    -> ('A',DATE(NOW()),'Jane'),
    -> ('B',DATE(NOW()),'Joan'),
    -> ('A',DATE(NOW()),'June');
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM invoices ORDER BY branch,invoicenumber;
+---------------+--------+------------+--------+
| invoicenumber | branch | date       | client |
+---------------+--------+------------+--------+
|             1 | A      | 2012-04-21 | John   |
|             2 | A      | 2012-04-21 | Jeff   |
|             3 | A      | 2012-04-21 | Jane   |
|             4 | A      | 2012-04-21 | June   |
|             1 | B      | 2012-04-21 | Jack   |
|             2 | B      | 2012-04-21 | Joel   |
|             3 | B      | 2012-04-21 | Joan   |
+---------------+--------+------------+--------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : At present, only the MyISAM Storage Engine supports multiple auto_increment values grouped with other columns. This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!


How much disk space do I need to add a primary key to a very large table?

I would also add that not only data pages will be moved when creating a clustered index - also all the non-clustered indexes will have to be rebuilt since each row in non-clustered index contains clustered key value. All these data movements need to be logged and sorting process will heavily use tempDb since you probably don't have 500 GBs of memory available, right? ;)

You'll find plenty of debates and different opinions about using heaps and I'm of course speculating here but most likely there's a reason why such a big table doesn't already have a clustered index defined on top of it. It's not exactly the best practise but sometimes it does make sense to define a table without clustered index (quick, minimally logged inserts) with a few narrow non-clustered indexes that cover frequent queries.

Decision about adding any kind of index (primary, clustered, non-clustered, XML, spatial, ...) depends of course on the problem you're trying to solve. In OLTP system you typically optimise your indexes to improve write performance while in reporting systems like OLAP you usually need to work on read types of workloads.

And few suggestions to wrap this up:
- primary key doesn't have to be clustered
- if the table is not partitioned you should definitely consider partition it
- if your goal is to make certain types of queries faster (and not trying to enforce uniqueness of your bigint column) you should consider creating additional (or modifying existing) non-clustered (possibly filtered) index or indexed view (perfect for aggregate queries)

Hope it helps, good luck!


Performance issues since migrating from SQL Server 2012R2 to 2019

@user1673665 ok, this definitelly looks like cardinality issue, prolly statistics.
You have SQL Server expecting few row, ie for scMat_MaterialText 4, but you get 62000. Thats way off.
Try to run a select on this table, then review estimates vs actual rows read. And try to update statistics. I would say thats the culprint


How to deny viewing definition of server level trigger?

in a test server I create the following server side trigger for an experiment.

this trigger prevents users from dropping logins.

    --set parseonly on
    --set parseonly off

    --=======================================================================================
    -- create a trigger that prevents dropping of logins
    --=======================================================================================

    USE [master]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    create trigger [no_dropped_logins]
    on all server
    for drop_login
    as
      insert into ##LOGIN_WATCH
        select r.*, s.login_name, s.host_name,
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)') AS 'CmdText'
         from sys.dm_exec_requests r
           inner join sys.dm_exec_sessions s
           on r.session_id = s.session_id
           where r.session_id = @@SPID
    GO

    ENABLE TRIGGER [no_dropped_logins] ON ALL SERVER
    GO

second step I create a login in order to use it in the experiment later on.

--==============================================================
-- create a login in order to drop it and call the trigger
--==============================================================

USE [master]
GO

--DROP LOGIN [Radhe]
--GO

CREATE LOGIN [Radhe] WITH PASSWORD=N'Shy4m', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER LOGIN [Radhe] enable
GO

as a third step I connect as Radhe and check what I can see:

as you can see on the picture above - you cannot see the server side trigger.

another test:
running a query as login Radhe I cannot see the server side trigger.

execute as login='radhe'

SELECT
       *
FROM sys.server_triggers

However, when I run the same query as myself:

NOW I grant the VIEW ANY DEFINITION and see what happens:

GRANT VIEW ANY DEFINITION TO [RADHE]
GO

now Radhe can see the server side trigger as per the picture above.

check this out:

running this script:

--==================================================================================
--GRANT VIEW ANY DEFINITION AND RADHE CAN SEE THE SERVER SIDE TRIGGER
--==================================================================================
GRANT VIEW ANY DEFINITION TO [RADHE]
GO
execute as login='radhe'

SELECT * FROM sys.server_triggers
GO
revert
GO
--==================================================================================
--REVOKE VIEW ANY DEFINITION AND RADHE CAN NO LONGER SEE THE SERVER SIDE TRIGGER
--==================================================================================
REVOKE VIEW ANY DEFINITION FROM [RADHE]
GO
execute as login='radhe'

SELECT * FROM sys.server_triggers
GO
revert
GO

and the tigger is working, as you can see when I try to drop the login Radhe:

Conclusion:

Please review your current server side permissions:

SELECT
     spr.name as loginName
    ,spr.type_desc
    ,spr.is_disabled
    ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN
    ,spm.permission_name
    ,spm.state_desc
FROM
    sys.server_principals spr
    inner join
    sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id
WHERE
    spm.permission_name = 'CONTROL SERVER'
    OR
    spm.permission_name = 'VIEW ANY DEFINITION'
    OR
    IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1

Please don't grant high fly permissions to public, or use it with care.


Maximum number of columns in PRIMARY KEY

It's the same for MySQL and MariaDB: an index can consist of 16 columns, at maximum.
Since a PRIMARY KEY constraint automatically adds an index, it can't have more than 16 columns.

See MySQL Reference Manual / ... / The Main Features of MySQL:

  Support for up to 64 indexes per table. Each index may consist of 1 to 16 columns or parts of columns. The maximum index width is 767 bytes (per column) for InnoDB tables, or 1000 for MyISAM. An index may use a prefix of a column for CHAR, VARCHAR, BLOB, or TEXT column types.


PostgreSQL, UPDATE CASE statement uses huge amount of space

Size of the table is 53Gb without indexes. Obviously you have indexes, one of them a GIN index, which is typically multiple times the size of a plain table storing only the indexed column. I expect you get around 180 GB for
SELECT pg_total_relation_size(osmm.topographicarea);

Details on measuring sizes:

Measure the size of a PostgreSQL table row

As has been commented, due to the MVCC model of Postgres, an UPDATE in place effectively writes a new row version of every changed row (and also for affected indexes), which roughly duplicates the size of the table in your operation.
If you don't have to worry about concurrent access, you could go a different route:

Optimizing bulk update performance in PostgreSQL

60 GB (size of table, rounded up) will be too much for a temp table in memory. It would spill to disk, which voids the intended effect. You could just create a new regular table, drop the old one and rename the new - if you don't have concurrent access or depending objects.
To reclaim space you need
VACUUM FULL osmm.topographicarea;
While being at it, make that:
VACUUM FULL ANALYZE osmm.topographicarea;

Or run CLUSTER or use pg_repack or pg_squeeze if you cannot afford an exclusive lock on the big table.
Details:

How to reclaim disk space after delete without rebuilding table?
VACUUM returning disk space to operating system

Optimize UPDATE
Indexes are not helping for this UPDATE, on the contrary. Since you are updating every row and all involved columns are in the same row, indexes are of no use whatsoever here. They still have to be kept up to date at all times, though. It will be much cheaper to delete all indexes and add them back later - especially the comparatively expensive GIN index. And more ... Details:

How to speed up a slow UPDATE query

Also, you are updating every row unconditionally. If some of the rows already have the right value in fcode, it would be cheaper not to touch those at all. Details (last chapter):

How do I (or can I) SELECT DISTINCT on multiple columns?

But it sounds like you are adding a new column. In this case there is nothing to gain here.
Of course, all the usual advice for performance optimization applies as well.


Sql Server 2017 - Cannot shrink transaction log file, blocked by LOG_BACKUP status

The ldf file is divided into Virtual Log Files (VLFs). The last used VLF sets the limit to how small the file can be shrunk. I.e., the file can only be shrunk from the end towards the beginning. So you apparently have a used VLF towards the end of the file. You need to get un-used VLFs at the end.

What you do is you empty the log (BACKUP LOG if not in SIMPLE mode, else the CHECKPOINT command), and then shrink. And then you do that several times. That is the key, several times. At some point in time, you will have as many un-used VLFs at the end so the file will shrink to your liking.

And then set recovery model properly (based on if you do log backups or not).

You can investigate the VLF layout using DBCC LOGINFO.


How to copy database from one server to another with script

Use dbatools powershell module. There are numerous commands to help all sorts of administrations tasks.

Here is a migrate database example.

copy-dbadatabase -Source oldserver -Destination newserver -BackupRestore -SharedPath \\server\backup\ -Database databasename -SetSourceOffline


How do you CREATE TABLE AS SELECT (CTAS) with a composite type?

I don't think you can do it the way you are trying to... if my understanding of "A pseudo-type cannot be used as a column data type" is correct.

However, you can do something similar.

First, you define your composite type (for instance, we have a "point in two dimensions", with fields x and y):

-- Need to define the composite type
CREATE TYPE point_2d AS
(
    x real,
    y real
) ;

Then, you can write 'literals' that need to be cast to point_2d so that PostgreSQL know which exact type they have. So, this works:

CREATE TABLE
    ttt AS
SELECT
    *
FROM
(
    VALUES
    ('(1.5, 2.5)'::point_2d, '(-1.3, -2.2)'::point_2d),
    ('(2.3, 4.4)'::point_2d, '(-2.2, -3.3)'::point_2d)
) AS lines (start_point, end_point) ;

And then, you can perform queries like:

SELECT
    (start_point).x AS x_start,
    (start_point).y AS y_start,
    (end_point).x AS x_end,
    (end_point).y AS y_end
FROM
    ttt ;


Is replication possible on two different domains?

Replication across non-trusted domains or workgroups can be done using Windows Authentication by configuring pass-through authentication.

Create a local Windows account on both the Publisher and Subscriber that has the same username and password.  Use this account for the replication agent process account and have the connections to the publisher, distributor, and/or subscriber impersonate this account.  Ensure the account has the permissions required in Replication Agent Security Model.

This approach is covered in the section Use Windows Authentication to Set Up Replication Between Two Computers Running SQL Server in Non-Trusted Domains in HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet.


Need to find the employees who have switched jobs atleast twice

Your Query should be like:

SELECT * FROM job_history INNER JOIN employees
ON job_history.employee_id = employees.employee_id
WHERE job_history.employee_ID IN
    (SELECT employee_id
         FROM job_history
         GROUP BY employee_id
         HAVING COUNT(employee_id) >= 2
    );

so you missed the alias before the id "WHERE job_history.employee_ID IN..", and the where in change it to what above...


How to extract a SQL database design into Visio in a manageable format?

I'd check out Embarcadero tools Try Data Architect.


Time interval is returning all records or no records


How to simplify a carry over values process?

You should check it, I can't assure this is better in terms or efficiency.

with x as -- all possible combinations
(
    select distinct l, m
    from   test tt
    join   lateral (select m from
                    generate_series((select min(monthly_date) from test where l=tt.l),
                                     '2001-06-01'::date, '1 month'::interval) m) t1 on true
)
select    x.l,
          coalesce(test.v1, 0) as v1,
          coalesce(test.v2, (select v2 -- only executed for missing rows
                             from test
                             where l = x.l and monthly_date < x.m
                             order by l, monthly_date
                             desc limit 1)) as v2,
          m as monthly_date
from      x
left join test
on        test.l = x.l
and       date_trunc('month', monthly_date) = date_trunc('month', m)
order by  x.l, x.m;

l  | v1 | v2  | monthly_date
:- | -: | :-- | :---------------------
a  |  2 | 1.3 | 2001-01-01 00:00:00+00
a  |  1 | 2.2 | 2001-02-01 00:00:00+00
a  |  0 | 2.2 | 2001-03-01 00:00:00+00
a  |  5 | 6.2 | 2001-04-01 00:00:00+01
a  |  0 | 6.2 | 2001-05-01 00:00:00+01
a  |  0 | 6.2 | 2001-06-01 00:00:00+01
b  |  3 | 9   | 2001-03-01 00:00:00+00
b  |  0 | 9   | 2001-04-01 00:00:00+01
b  |  0 | 9   | 2001-05-01 00:00:00+01
b  |  0 | 9   | 2001-06-01 00:00:00+01

dbfiddle here


This always happens because during setup you forgot to adjust your instance to be in both Windows and server authentication. Open SQL Server Management Studio; log in using Windows authentication; right click on your server  then properties, then security tab, and mark on both Windows and server authentication; then click ok.

Close SSMS then here is the trick:

start SQL Server configuration
stop all the services
restart all the services

then you will be able to connect in SQL Server authentication.


If you have multiple databases, writing to independent disks (with the directory per db) option will be your best bet for performance.  Even if you just have one big huge sharded database, you can still get some performance boost with multiple disks by putting your journal and local database (database where replication info goes) on separate disks, but your main database data files would all have to be on a single disk.
Not sure what you're after here.  The recommendation is that your working set fit in memory for best performance and that still applies for sharding, its just split across multiple servers and thus your memory requirements are also split up across those servers.  That will of course also depend on how well you pick your sharding key, hopefully the load is distributed evenly across all the nodes.  If you plan on returning lots of data from your queries then you might need extra memory on your mongo-s servers as they have to do some sorting and aggregation of what you're bringing back.


you can use something like...

SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1

...as a subquery (substitute name of your id field for "id") and wrap it up like this...

SELECT b.* FROM btmt b,
(SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1) As z
WHERE b.id = z.id AND z.cnt > 1
LIMIT 24;

Hard to tell exactly what you're looking for, but that will get you 24 unordered results from the btmt table with fsid value of greater than 1, although it might not be the prettiest way to do so, computationally speaking.

Please clarify if that's not what you're after--hope it helps if so.

EDIT: if you're looking for fsid values 1-25 it would be more like...

SELECT b.*, z.cnt FROM btmt b,
(SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1) As z
WHERE b.id = z.id AND z.cnt BETWEEN 1 AND 25
ORDER BY z.cnt; --through that in just for the fun of it :-)


Small sample database for practicing database administration?

Take a look at the Dell DVD Store database. It's available for multiple databases, SQL Server included.

  The Dell DVD Store is an open source simulation of an online ecommerce
  site with implementations in Microsoft SQL Server, Oracle, MySQL and
  PostgreSQL along with driver programs and web applications


Why does SSAS keep restarting every few seconds?

We never found out what was causing the service to stop, but we were able to prevent it from happening. We put a Deny on the Stop permission for SSAS service for the SQL Server (not SSAS) service account user.

So it seems something acting as the SQL Server's service account user was issuing the Stop command for the SSAS account. This was a while ago, and we've since switched servers, and we stopped digging to figure out what was really happening. So it will remain a mystery for the ages.


How to organize binary tree in database?

I think this question is principally, "how do you incentivize salesmen in a pyramid scheme?" That's cute, so yeah let's do it. Let's assume

We have 7 people in our scheme.
We allocate bonuses based on the tier of pyramid.

You don't have to place them in a visual pyramid to figure this out..

CREATE TABLE my_pyramid_scheme(empid, name, is_active)
AS
  SELECT id::int, (id+64)::int::"char", true
  FROM generate_series(1,7) AS gs(id);

Now you've got

 empid | name | is_active
-------+------+-----------
     1 | A    | t
     2 | B    | t
     3 | C    | t
     4 | D    | t
     5 | E    | t
     6 | F    | t
     7 | G    | t

So first we need to know what tier they're on... For that we use 2n-1. For example, if your number is 1.

2n-1 = 1
2n = 2
n = 1 -- you're on tier one

Now if your number is 6

2n-1 = 6
2n = 7
n = 3.5 -- trunc(3.5) you're on tier three.

So after you know what tier they're on. You need to find how much of a bonus they get.. That's a function of the tier they're on, and the maximum amount of tiers, to find that take the largest number and run it through the same process. With those two things, you typically have some geometric function that determines "incentive."

And, now you can run illicit drug cartel or compete with Herbalife.

Disabling members

If in the above you ever set is_active = false run these super fast operations on the row_number..

SELECT *, row_number() OVER (ORDER BY empid) AS newid
FROM my_pyramid_scheme;


You can call procedures easily:

EXEC LinkedServer.Database.dbo.Procedure;

TVFs will be a different issue:

  Msg 4122, Level 16, State 1
  Remote table-valued function calls are not allowed.

And no, you can't fool SQL Server by creating a synonym so that it doesn't detect a 4-part name; it will just return a different error, e.g.:

  Msg 7357, Level 16, State 2
  Cannot process the object ""db"."dbo"."synonym"". The OLE DB provider "SQLNCLI11" for linked server "LinkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

To work around this, you can either put the call to the TVF inside a remote procedure and call it like above, or do something like this:

EXEC LinkedServer.Database.sys.sp_executesql N'SELECT ... FROM dbo.TVF();';

If you need more direct interaction (e.g. cross applying the TVF against local tables), you might have to perform that on the remote server using a linked server in the other direction for the table data:

-- instead of
SELECT ... FROM dbo.foo AS t CROSS APPLY LinkedServer.db.dbo.tvf(t.id);

-- you would do:
EXEC LinkedServer.db.sys.sp_executesql N'SELECT ...
  FROM ReverseLinkedServer.db.dbo.foo AS t CROSS APPLY dbo.tvf(t.id);';


How to customize Deploy feature in VS2010 for SQL CLR projects?

for the trustworthy setting you should go to your project and:
Right click on the project - Properties - Go to tab Project Settings -> Catalog properties file - Edit -> in the middle there will be option for set trustworthy on/off.

If you don't need to change the database properties at all at deploy time, then go to the Deploy tab - Deploy configuration file -> uncheck the option 'Deploy database properties' - first option in the list. This should not apply the settings from the 'Catalog Properties file' to the database your deploying on.

As for Vardecimal storage option I remember I saw it somewhere in the deploy settings.. but no clue for the moment, sorry. If I find it I'll get back with solution.
Hope this helps, at least a bit.


International Components for Unicode (ICU) Date Formatter for MySQL?

I think the answer is...  Use MySQL to store dates/datetimes/timestamps in the limited formats that it provides, then use app code -- possibly some library in your app language -- to get at fancier formatting.

As a general rule, a database is responsible for storing; the application is responsible for formatting.

See also https://dev.mysql.com/doc/refman/5.7/en/locale-support.html


What are the 'requirements' of your UUID?  Why isn't an AUTO_INCREMENT sufficient?  I believe the use (in C) of mysql_insert_id() does not incur an extra round-trip to the server, but rather provides the value from the previous INSERT.  See http://dev.mysql.com/doc/refman/5.6/en/mysql-insert-id.html .

If that does not suffice, consider

INSERT ... VALUES (..., @uuid = UUID(), ...);
SELECT @uuid; -- or simply use @uuid in whatever needs it next.

A note about UUIDs -- They are very "random".  That is, for tables/indexes too large to be cached, they incur a lot of I/O.  More discussion in my uuid blog.


Just use COUNT(DISTINCT instead of COUNT( - because that’s essentially the difference between DENSE_RANK and RANK.


MySQLSyntaxErrorException on ALTER TABLE ... CONVERT TO ... in MySQL Connector/J

In my previous response I have not taken into account CONVERT TO behavior, so I have tried your test, first using the mysql native client and all works fine, column are automatically converted, and then using a jdbc connector and actually I got the same error... but after some test and some research I have found the solution.

You have to add in your jdbc connection url the parameter jdbcCompliantTruncation and set it to false

jdbc:mysql://localhost/test?jdbcCompliantTruncation=false

  Should the driver throw java.sql.DataTruncation exceptions when data
  is truncated as is required by the JDBC specification when connected
  to a server that supports warnings (MySQL 4.1.0 and newer)? This
  property has no effect if the server sql-mode includes
  STRICT_TRANS_TABLES.

  Default: true

Source: 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

Running my java code using the above connection url the table mytable are correctly converted (I have tried only with an empty table):

mysql> desc mytable;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| value1 | mediumtext    | YES  |     | NULL    |       |
| value2 | mediumtext    | YES  |     | NULL    |       |
| value3 | varchar(1000) | YES  |     | NULL    |       |
| value4 | mediumtext    | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

So, if the server runs without STRICT_TRANS_TABLES in sql_mode, ex:

mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

the jdbcCompliantTruncation=false cause the alignment of the behavior between server and jdbc client.


How do You concatenate a value to an array that was retrieved through a query in sql?

However, if, for example, I attempt to add the string 'there', to the array of strings '{fellas,ladies}' which correspond to the word 'hi', nothing appears to happen:

You are comparing the value of the column word with the string constant 'hithere' - you are concatenating two string constants, not a string to an array column.

 where word = 'hi' || 'there';

is the same as

 where word = 'hithere';

It sounds as if you really want:

SELECT mkvword || 'there'
FROM mkvtable
WHERE word = 'hi';

If you want to change the value that is stored in the table rather than just displaying it, you need an update:

update mkvtable
  set mkvword = mkvword || 'there'
where word = 'hi';


Skipped rows or rows read multiple times with NOLOCK

Gail Shaw posted a demo of how to do so quite a while ago. It works by inducing row movement by increasing the size of the row over time. No inserts or deletes, just updates to existing records.


I am not 100% sure but dbatools.io might have one.


how to identify the default mail configurations

Add a join to msdb.dbo.sysmail_principalprofile and check the is_default column to identify the default profile:

SELECT *
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1
JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id;


Parent and Child Relationship with the Same Foreign Table

You have a Person, who is attending a School? They are studying for a Degree? A Degree will consist of 1 -> many Courses. A Course may be on many Degree programmes (e.g. Foundation Maths)?

The totally general case is that 1 Person can attend 1 or more schools studying for 1 or more Degrees in each school at any one time - is this possible? Some people may be repeating one course but have been allowed to advance, contingent on passing an exam elsewhere?

So, in order to be completely general, you want a Person table, a Degree table and a School table - with Person_Degree being your joining table between Person and Degree and Person and ultimately School. If a Person can only be doing 1 Degree in 1 School at any time, then you can simplify.

You then have a Degree_Course table which will have the joins between Degrees and Courses and a Course table.

Some Degrees can be multi-School - then you'll just have to have a further joining table. However, remember the YAGNI principle - if you don't have a current need, don't include it in your design. This can be difficult to judge - we want our systems to be flexible, but I'm with Einstein on this - make it as simple as necessary but no simpler!

Before a first take on the tables themselves - you could do worse than having a quick look here - you might get some ideas (and I might have even overlooked something! :-) )

Two things to bear in mind - one is that DDL (Data Definition Language) is quite powerful in its own right - right there in the database structure is a lot of "intelligence" - i.e. you can't have a degree without a school or a course without a school! The various other UNIQUE, PRIMARY KEY and FOREIGN KEY constraints enforce other logical rules about the system - rules you would otherwise have to spend a lot of time enforcing in your app!

Finally, a couple of words about table names.

I always use singular names for tables - they are like a class definition and/or a collection - (for me it is a singular concept - you might disagree). As an aside, you have tables in which you might want to have only 1 or even 0 records (think nuclear power plant system with a table called "catastrophic_meltdown"). In any case, choose one convention and stick to it.

Furthermore, my table_names_might_appear_long! :-) It can be greatly helpful when debugging to have meaningful table names - code spends >> 99% of its time in maintenance, so the extra typing is a (very) small price to pay for helpful error messages when things go wrong - and they will, no matter how good a programmer you are!

In PostgreSQL, I would do it like this (should translate reasonably well to other systems - check your documentation for details!) - note that I haven't dealt with semesters/trimesters here - adding a semester field to the degree and course tables might be necessary - again, this depends on your system requirements.

CREATE TABLE person
(
  person_id SERIAL,
  CONSTRAINT person_pk PRIMARY KEY (person_id),

  person_first_name VARCHAR(25) NOT NULL,
  person_last_name  VARCHAR(50) NOT NULL

  --
  -- .. other person stuff... address, phone &c.
  -- .. could make f_name + l_name UNIQUE?
);

CREATE TABLE school
(
  school_id SERIAL,
  CONSTRAINT school_pk PRIMARY KEY (school_id),
  school_name VARCHAR(50) NOT NULL

  -- other school stuff
  -- Tel., address... &c.
  --
);

CREATE TABLE degree
(
  degree_id SERIAL,
  CONSTRAINT degree_pk PRIMARY KEY (degree_id),
  degree_name VARCHAR(50) NOT NULL,
  degree_code VARCHAR(10) NOT NULL,

  degree_school_id INTEGER, -- no "NOT NULL, since it is an FK field - see below

  CONSTRAINT degree_school_fk FOREIGN KEY (degree_school_id) REFERENCES school (school_id)

  -- other degree stuff - faculty, prerequisites (another table?)

);

CREATE TABLE course
(
  course_id SERIAL,
  CONSTRAINT course_pk PRIMARY KEY (course_id),
  course_name VARCHAR(50) NOT NULL,
  course_code VARCHAR(10) NOT NULL,

  course_school_id INTEGER,
  CONSTRAINT course_school_fk FOREIGN KEY (course_school_id) REFERENCES school (school_id)

  -- other stuff - possibly a course_prerequisites table
);

CREATE TABLE degree_course
(
  degree_course_id SERIAL,
  CONSTRAINT degree_course_pk PRIMARY KEY (degree_course_id),
  degree_course_degree_id INTEGER NOT NULL,
  degree_course_course_id INTEGER NOT NULL,

  CONSTRAINT degree_course_degree_course_uq UNIQUE (degree_course_degree_id, degree_course_course_id),  -- prevent same entry twice

  CONSTRAINT degree_course_degree_fk FOREIGN KEY (degree_course_degree_id) REFERENCES degree (degree_id),
  CONSTRAINT degree_course_course_fk FOREIGN KEY (degree_course_course_id) REFERENCES course (course_id)
);

CREATE TABLE prerequisite
(
  prerequisite_id SERIAL,
  CONSTRAINT prerequisite_pk PRIMARY KEY (prerequisite_id),
  main_course_id INTEGER,
  preq_course_id INTEGER,
  CONSTRAINT prerequisite_uq UNIQUE (main_course_id, preq_course_id)  -- prevent same entry twice

  -- other info...
  --
);

CREATE TABLE person_degree
(
  person_degree_id SERIAL,
  CONSTRAINT person_degree_pk PRIMARY KEY (person_degree_id),

  person_id INTEGER NOT NULL,
  degree_id INTEGER NOT NULL

  -- various FOREIGN KEY and UNIQUE constraints to be added here!
);

Similar principles apply to your optionaltrip and degreespecific group tables - if you're having further issues, post back!


Is it possible to specify a different collation for the data vs the entities within a single database?

Yes.  Turn on partial database containment, and you’ll get a case-insensitive catalog collation along with the collation of your choice for the data.

In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS_SC. This collation is the same for all contained databases on all instances of SQL Server and cannot be changed.
The database collation is retained, but is only used as the default collation for user data. By default, the database collation is equal to the model database collation, but can be changed by the user through a CREATE or ALTER DATABASE command as with non-contained databases.

See https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-database-collations?view=sql-server-ver15
And note that Azure SQL Database always uses contained database collations.


As far as I have understood your question, you are trying to create a model for dishes and list their ingredients.

You should never, ever use values as keys. With a relational database, would you create a table which has a column for each imaginable ingredient? Hardly.

Furthermore, while redundancy isn't necessarily a bad thing, it is in keys. In a collection called "dishes" or "food", it is natural that an ingredient does not refer to a chemical compound, right?

So, here is how I'd model a dish:

{
  "_id": new ObjectId(),
  "name": "Katsuo no Tataki",
  "desc": "Lightly Broiled Bonito",
  "tags": ["fish","jp"]
  "ingredients": [
    { "name": "Bonito Filet", qty: "1/2" }
    { "name": "onion", qty: "1/2"},
    { "name": "garlic", qty: "2 cloves"}
  ]
}

When displaying a dish, you simply iterate over the ingredients array, displaying both name and qty.

In order to find all dishes which contain garlic, you simply do

db.dishes.find({"ingredients.name":/garlic/i})

or, to find recipes containing the fish tag:

db.dishes.find({"tag":/fish/i})

Note that I used a case insensitive regex search.


Suggested SQL Server Backup Schedule using Ola Hallengren scripts

[T]he suggestion was to do the database integrity check before the backups so I was think maybe 30 mins before the main backup and then run the index optimize when a known time of little load. Would that make sense as a schedule for SQL Server backups?

A few thoughts:

Index maintenance may not be necessary as often as you think. Usually index fragmentation has a very low impact on performance with modern hardware, especially when you take into account that all logs involved with index rebuilds have to be shipped to your Availability Group secondaries. I would suggest considering a daily (or even more frequent) statistics update, using Ola's maintenance solution, and make sure that you use this example from his site (take note of the NULLs for index fragmentation):

D. Update modified statistics on all user databases
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

If you find that index fragmentation does cause performance problems, or you just want to keep them in decent shape, you can schedule that on a less frequent basis. Depending on the database and index size, maybe once a week or once a month.

This leads me into answering your "guesstimate" of 30 minutes before backups for consistency check. You won't know until you actually get the process running. If your monthly index maintenance takes three hours, you'll want to kick that off earlier than the statistics update. If CHECKDB takes five hours, then schedule it to run on an AG secondary, or restore the database to a stand-alone server and run it there (search for "offloading CHECKDB").

Differential backups every hour is overkill, in my opinion. I generally do these once a day, and possibly twice a day depending on the environment. You have your log backups, and you need to keep in mind that differentials are not incremental. They will continue growing until the next full backup, because they keep track of all changes since the last full backup. In some cases, differential backups can exceed the size of the full backup. In those cases, you'll need to evaluate whether a full backup is better than a differential.

Do you have enough disk space to keep 5-minute transaction log backups? Is that what the business needs (SLA, RTO, RPO)? Can you get away with 6 minutes? Over a three-month period, that extra minute makes a big difference in the number of files you're storing. Often I find most of my clients satisfied with a trade-off of 10- or 15-minute log backups.

Full backups could be done daily, but depending on your retention policy, as well as Recovery Time Objective and Recovery Point Objective, you might be more than happy with a weekly full backup of user databases, a daily full backup of system databases, and the differentials can fill in the gaps where necessary.

Ultimately, this all depends on what the business needs to satisfy service level agreements internally and externally. If a reporting / OLAP style database can be regenerated in two hours from an OLTP source, then you don't need to worry as much about it as the OLTP database.
I hope this helps you come up with an appropriate strategy for your business. That's what should drive these decisions.


First you need to catalog the remote instance.

db2 catalog tcpip node <my alias node name> remote <host or host alias name> server <port>

Issue db2 terminate to make sure the catalog entry remains.

Now you can actually catalog the remote database.

db2 catalog db <remote database name> as <local alias> at node <node alias supplied above>

And once again issue db2 terminate

Here is an example of what this could look like:

db2 catalog tcpip node devinst1 remote devservera server 50100;
db2 terminate;
db2 catalog db crmdb as remotedb at node devinst1;
db2 terminate;

Now you can connect to that local database using either the db name (crmdb) or the alias name (remotedb) depending on your needs.

Note: Sometimes you can run into issues where it will say things about a null package id. This would mean that particular packages need to be bound for the connection to work. Easiest way to solve this is to have your instance owner id (of the remote database) make the first connection and then disconnect or to have your id you are using have BINDADD authority. Then the correct packages will get bound. You may not run into this, especially if you are on the same version and fix pack level of DB. If you ever differ between "client" and "server" is when you for sure have to do this.


Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

You have tradeoffs to consider when deciding on the design.  Which do you want to optimize for:

Insert new item
Revise item
Fetch "current" item
Fetch some old revision
Fetch all revisions

If one of those actions dominates, then design for it, and kludge for the rest.

Also, do revisions need extra columns, such as revisor, revision_date, revision_number, ...?  This may 'force' you to have a separate table for revisions.

If not using a separate table, consider this design:

Have a column rev for revision number:  0 for "current"; 1...N for old versions (1 is oldest).  Then it takes a couple of statements to move 0 to N+1 and insert a new 0.  The Fetches are pretty obvious.  Include an composite index (the PRIMARY KEY?) in this order: (rev, item_id).


Group by age range and gender with aggregation function on some columns

Two options.

a) Use sub query

select
  concat(10*floor(age/10), '-', 10*floor(age/10) + 10) as `range`,
  gender,
  count(*) as count
from (
  select
    *,
    TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age
  from
    transaction left join patient on patient_id = patient.id
) as t group by `range`, gender;

Gives

+-------+--------+-------+
| range | gender | count |
+-------+--------+-------+
| 0-10  | m      |     2 |
| 10-20 | m      |     1 |
| 20-30 | m      |     3 |
| 30-40 | f      |     2 |
| 30-40 | m      |     2 |
| 50-60 | f      |     1 |
| 50-60 | m      |     1 |
| 60-70 | f      |     5 |
| 60-70 | m      |     3 |
| 70-80 | f      |     1 |
| 70-80 | m      |     6 |
+-------+--------+-------+

b) Group by age first and then group in range in application.

When using ORMs it's not easy to do sub queries, so this might be a good idea.

Performance wise: Considering that average humans live for 100 years, there can be at max 200 records (100 male and 100 female) which can be easily looped in an application.

select
  gender,
  TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age,
  count(*)
from
  transaction left join patient on patient_id = patient.id
group by age, gender

Gives

+--------+------+----------+
| gender | age  | count(*) |
+--------+------+----------+
| m      |    4 |        2 |
| m      |   14 |        1 |
| m      |   24 |        3 |
| f      |   34 |        2 |
| m      |   34 |        2 |
| f      |   54 |        1 |
| m      |   54 |        1 |
| f      |   64 |        5 |
| m      |   64 |        3 |
| f      |   74 |        1 |
| m      |   74 |        6 |
+--------+------+----------+


Copy table from one server to another including keys and indexes

Since you want to selectively script out certain tables, you can use dbatools to programatically script out tables, triggers, etc using Export-DbaScript.

Alternatively, you can also try Export-DbaDacPackage with -table parameter.

Since you are using linked server, my answer on Which one is more efficient: select from linked server or insert into linked server? will help as well.


Ok, this basically means:

The National character set of the database you exported from was
AL16UTF16. This is a multi-byte character set and, being the
national character set, would have been used for any NCHAR,
NVARCHAR and NCLOB columns (the N in the name stands for "national").
The "normal" character set of the database you exported from was
WE8ISO8859P1. This is an 8-bit character set (the Western European
ISO variant) and would have been used for any CHAR, VARCHAR and
CLOB columns.

You can see which character sets are being used by a particular database by querying the NLS_DATABASE_PARAMETERS data dictionary view. Your export would have been done from a database with the parameter NLS_CHARACTERSET set to WE8ISO8859P1, and the NLS_NCHAR_CHARACTERSET parameter set to AL16UTF16.

The difference between multi-byte and single-byte character sets is explained in the name. Single-byte characters are stored in a single byte, i.e. 8 bits. Multi-byte characters are stored in one or more (multiple) bytes.

As far as importing multi-byte into single-byte (and vice versa) is concerned, it really depends on the character encodings and whether or not the source character actually exists in the target character set. By way of example, a Japanese multi-byte character is not going to exist in the US7ASCII character set, and you'd end up with a database full of ? signs.

I strongly suggest you read the Oracle Globalization Support Guide, as this goes into the topic of internationalisation in great detail.


In PostgreSQL (as described in documentation, The Information Schema):

SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public';

For MySQL you would need table_schema='dbName' and for MSSQL remove that condition.

Notice that "only those tables and views are shown that the current user has access to". Also, if you have access to many databases and want to limit the result to a certain database, you can achieve that by adding condition AND table_catalog='yourDatabase' (in PostgreSQL).

If you'd also like to get rid of the header showing row names and footer showing row count, you could either start the psql with command line option -t (short for --tuples-only) or you can toggle the setting in psql's command line by \t (short for \pset tuples_only). This could be useful for example when piping output to another command with \g [ |command ].


The compute scalar is occurring because it has to convert the INT values to VARCHAR(12) values before concatenating which it can then compare against the subquery returned values. You are correct that this is not likely to scale well, due to cardinality estimation issues among others.

One solution is to LEFT JOIN the table on itself then use a WHERE clause to exclude the rows that do not have a joined row. This plan is simply two table scans, a nested loop and finally a filter to exclude the NULL valued rows. This should scale reasonably well.

Example:

SELECT a.*
FROM @Foo a
LEFT JOIN @Foo b ON a.id_a = b.id_b AND a.id_b = b.id_a
WHERE b.id_a IS NULL

Returns:

code    id_a    id_b
--------------------
C       3       4
D       4       0
E       5       3


Relational Design - Multiple tables into one foreign key column?

I think you are looking for a subtype/supertype construct.  Meat would be your migrating key and would contain a Type field that indicates which sub-type of meat it relates to.  So:

PurchaseMeat = Meat = {MeatBeef, MeatPork, MeatPoultry}

Where Meat is the type and the key of the subtype.

In Crow's feet notation this is a circle with a line under it.


Postgres table partitioning "no partition of relation "parsel_part" found for row" error?

Your problem is related to this point in the documentation:

  When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound.

(emphasis added)

So in fact in

CREATE TABLE parsel_2014_10 PARTITION OF parsel_part
     FOR VALUES FROM ('2014-10-01') TO ('2014-10-31');

the date 2014-10-31 is not included in this partition, and hence nowhere in all your partition tables (and same for all ending dates)

See the example at: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

The to value of one partition must be the same as the from value of the next one (because the to part is exclusive and the from is inclusive).


Way to run SQLCMD without storing password in text format

If you are using SQL Logins, then basically, no.  You'll want to have sqlcmd use the person's domain login to connect to the SQL Instance (the -E parameter instead of -U and -P).  You can use a login trigger to ensure that they are only able to connect via sqlcmd if you want to prevent them from logging in via SQL Server management studio (it isn't a perfect fix, but it'll stop 99% of people).


Is your client talking utf8?
Run

SET NAMES utf8;

and see if your selects output correctly?


How to use variables in file paths in MySQL statements?

You cannot keep the path in a variable because variables are interpreted by the server, and path names are interpreted by the mysql client.
Related question:

https://stackoverflow.com/questions/14523816/load-data-infile-variable-into-infile-error

However, there are a couple of different work-arounds that will allow you to accomplish what you are trying to do, and they are explained in the article.

I'd like to add one more idea:

If you call the mysql script from a Bash script, copy your file into a temp file, so the script always calls the same file name, like "work_file.csv".

After the mysql script runs, you can delete "work_file.csv".

Your original file is kept intact, and the data from that file is now uploaded.


Mysql Slave - Stop applying changes from a particular user

I think you'll be able to achieve this goal but there isn't really anything native that will give you this functionality out of the box.

You won't be able to use replication filters because they're not granular enough to filter by DML type. So that's off the list early.

You might approach it (optionally using partitions based on your archive routine) dropping the data (partition) outside the bounds of binary logging (SET SESSION LOG_BIN=0) and therefore not sending the DELETE/DROP messages through the replication topology. This could be managed through MySQL's built-in EVENTS scheduler or could be something that runs outside of MySQL in a script controlled through crond.

A quick scan of the documentation of Percona Toolkit's pt-archiver suggests that you could run archival jobs that connect to >1 mysqld instance and can do so in a manner that the session evades the binary logging (see DSN option 'b').

A word of warning, topologies of this design can be pretty delicate especially if you forget that the downstream replicas have more data in certain tables. I have completely forgotten about this when running tools such as pt-online-schema-change where the table is rebuilt on the MASTER and the REPLICA's version of the table ends up the same as the MASTER's.

Good luck!


Postgres not using multicolumn index for queries on leftmost columns?

Your query is returning half of the table.  There is little point in using an index in such a case.  Why do you think it would be faster to read both half the index and the table, rather than reading just the table?


Which oracle should I download for windows 7 x64?

I'm re-reading your post, trying to determine if you're having problems installing 10g, 11g or both.  Whichever, I found an article on how to install Oracle 11g on Windows 7, describing the problem that you're having above.  Basically, the installer should present you with an option to override the prerequisite OS check.  Follow the link, go to step #3 and see if that helps you.


A couple of relatively straight forward ways may be to create a logon trigger (it also talks about event notifications in that article), writing the login events to some audit table, or periodically query the sys.dm_exec_sessions DMV say in a SQL Server Agent job and record the information from that.


Two columns with and without index in where clause - How index works

I want to know how the query execution works here

The general execution model is a pipeline, where each iterator returns a row at a time. Execution starts at the root iterator (on the far left, labelled SELECT in your example).

After initialization, the root iterator requests a row from its immediate child, and so on down the chain until an iterator that can return a row is found. This passes back up the chain to the root where it is queued for despatch to the client. That is a very simplified overview, for more details see:

Showplan Logical and Physical Operators Reference (Product Documentation)
Iterators, Query Plans, and Why They Run Backwards (me)
A Second in the Life of a Query Operator by Brad Schulz

  Is it going to fetch all the rows that matches the 'col2=val2' condition and check for the other condition?

The nonclustered index seek will locate a row that matches col2=val2. It will be able to return col2 and col1 (see its output list) because col1 is present in the index (since the primary key is clustered in this case).

This row is passed up to the nested loops join, which then passes control to the key lookup. The lookup uses the col1 value to seek into the clustered index b-tree to find the value of col3 in this row. The value is tested against the predicate col3=val3 and only returned if it matches.

If there is a match, the row (c1, c2, c3) is passed up the chain and queued for transmission to the client. As control descends the tree again, any new match for col2 in the nonclustered index will result in a repeat of the nested loops join -> lookup -> return row cycle. As soon as the nonclustered index seek runs out of rows, the process completes when control next returns to the root iterator.

  Why these two -Index seek and Key-Lookup, are shown parallel in the execution plan?

That's just the way the graphical plan is laid out. See the links and discussion above for the correct way to understand the execution process in detail.

  Will it always use the available index on 'col2', considering a large dataset and assuming almost all entries in 'col2' are unique?

Most likely yes. The optimizer makes a cost-based choice between the available strategies. With very few matches expected, the optimizer will usually assess a nonclustered index seek with a lookup as being the cheapest option. An exception occurs when the table is very small. In that case, scanning the clustered index and applying both predicates to each row will likely look cheapest.

As a final note, a covering nonclustered index that would avoid the lookup is:

CREATE [UNIQUE] NONCLUSTERED INDEX [dbo.table1 col2,col3 (col1)]
ON dbo.table1 (col2, col3)
INCLUDE (col1);

It should be specified UNIQUE if that is true for (col2, col3).


How to let two sessions read two different records

Yes. You can achieve it using READPAST hint.
But you will need to make sure that your query is reading on row level. You need review your indexes and order by. The order by clause should be on an indexed column.

BEGIN TRANSACTION
SELECT TOP 1 * FROM T1 WITH(READPAST, UPDLOCK) ORDER BY IndexedColumn
-- ROLLBACK TRANSACTION

Here the extract from the documentation:

  READPAST

  Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released.

  For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5.

  READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.
  READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified.

  READPAST cannot be specified for tables in the INTO clause of an INSERT statement. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.
  READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.


It looks like SSMS is using the .NET System.Data.SqlClient API.  As far as I can tell, the statslog_on connection property is not supported using this API.

I ran a simple program* that iterates over the key value pairs allowed by that API and came up with these keys that are permitted in connection strings for SqlClient connections:

Data Source
Failover Partner
AttachDbFilename
Initial Catalog
Integrated Security
Persist Security Info
User ID
Password
Enlist
Pooling
Min Pool Size
Max Pool Size
PoolBlockingPeriod
Asynchronous Processing
Connection Reset
MultipleActiveResultSets
Replication
Connect Timeout
Encrypt
TrustServerCertificate
Load Balance Timeout
Network Library
Packet Size
Type System Version
Authentication
Application Name
Current Language
Workstation ID
User Instance
Context Connection
Transaction Binding
ApplicationIntent
MultiSubnetFailover
TransparentNetworkIPResolution
ConnectRetryCount
ConnectRetryInterval
Column Encryption Setting
Enclave Attestation Url

This list was produced specifically using the .NET Framework 4.7.2.  The list could be different depending on the versions of things that are used by different versions of SSMS.

For instance, I have SSMS 18.4 installed. Attaching a debugger tells me that it's using the .NET Framework 4.8 version of System.Data.dll:

Based on that, I would expect any of these to be allowed in an SSMS connection string (give or take what might have changed between version 4.7 and 4.8 of the framework).  Of course, some of them might fail at runtime, might not be compatible with one another, or might depend on the type of database you're connecting to.

Some allowed aliases for those specific values are allowed, and documented here: SqlConnection.ConnectionString Property - Remarks

*here is the C# program I used to produce those values:

var builder = new SqlConnectionStringBuilder();
foreach (var key in builder.Keys)
{
    Console.WriteLine(key);
}
Console.ReadLine();


Difference between Client Access Name in SQL server Fail over Cluster and Always on availability group

There is a way: use alias on source machine. Anyway, if possible, I suggest you to correct connection strings.


There are a lot of questions and articles about packing time intervals. For example, Packing Intervals by Itzik Ben-Gan.

You can pack your intervals for the given user. Once packed, there will be no overlaps, so you can simply sum up the durations of packed intervals.

If your intervals are dates without times, I'd use a Calendar table. This table simply has a list of dates for several decades. If you do not have a Calendar table, simply create one:

CREATE TABLE [dbo].[Calendar](
    [dt] [date] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
    [dt] ASC
));

There are many ways to populate such a table.

For example, 100K rows (~270 years) from 1900-01-01:

INSERT INTO dbo.Calendar (dt)
SELECT TOP (100000)
    DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '19000101') AS dt
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

See also Why are numbers tables "invaluable"?

Once you have a Calendar table, here is how to use it.

Each original row is joined with the Calendar table to return as many rows as there are dates between StartDate and EndDate.

Then we count distinct dates, which removes overlapping dates.

SELECT COUNT(DISTINCT CA.dt) AS TotalCount
FROM
    #Items AS T
    CROSS APPLY
    (
        SELECT dbo.Calendar.dt
        FROM dbo.Calendar
        WHERE
            dbo.Calendar.dt >= T.StartDate
            AND dbo.Calendar.dt <= T.EndDate
    ) AS CA
WHERE T.CustID = 11205
;

Result

TotalCount
7


Restoring a managed SQL instance in Azure using PowerShell

This command is somehow not clear for PowerShell and there is problem to determine which version of the method is accurate.

However it should work if you specify -FromPointInTimeBackup switch, I have checked  it and got credentials error which is fine as I am not logged in to proper account.

PS C:\WINDOWS\system32> Restore-AzSqlInstanceDatabase `
>>     -Name "SomeDatabase" `
>>     -InstanceName "our-oltp-dev" `
>>     -ResourceGroupName "dev-managedsqlinstances" `
>>     -PointInTime "4/7/2020 12:00:00" `
>>     -TargetInstanceDatabaseName "SomeDatabase_FROM_DEV" `
>>     -TargetInstanceName "our-oltp-sandbox" `
>>     -TargetResourceGroupName "sandbox-managedsqlinstances" `
>>     -FromPointInTimeBackup
Restore-AzSqlInstanceDatabase : Your Azure credentials have not been set up or have expired, please run
Connect-AzAccount to set up your Azure credentials.
At line:1 char:1
+ Restore-AzSqlInstanceDatabase `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [Restore-AzSqlInstanceDatabase], ArgumentException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ManagedDatabase.Cmdlet.RestoreAzureRmSqlManagedDatabase

Additionaly, be sure that you use the latest version of Az.Sql module.

Update-Module -Name Az.Sql -Force


Oracle Create Materialized View Script - Takes 12 minutes in DEV Schema, 5+ days in QA Schema

Start by focusing on the query used by the materialized view.  Examine why the query is running slow and you will solve why the materialized view creation is running slow.  If this is the case it may require another question on the query itself.  In the unlikely event that the queries run in the same amount of time, let us know.


Return proper column based on other column's values

CASE type
WHEN 'varchar'  THEN col_varchar
WHEN 'int'      THEN col_int
WHEN 'datetime' THEN col_datetime
END


Is it better a composite index (item, item_type) or single-column index on item_type_1, item_type_2, ...?

I think, from performance perspective, it will be very close.

But some thoughts:

One column indexes do not store NULL values. So individual indexes will be smaller. Composite index (item_type, item ) size will be bigger than sum of individual indexes. In extreme cases even the height of indexes may differ.
Two of three individual indexes will be unique, so it takes one less consistent read compared to nonunique index.
For item_c column oracle can build precise histogram.
In the case of (item_type, item ), oracle will correctly calculate cardinalities for queries with item types A and B, but for "item_type='C' and item=:X" it can underestimate. This is may be not a problem, but in complex queries with joins can give ineffective plan. So you need to check.
About to compare index (item_a,item_b,item_c) with (item_type, item ) - like others said it will work almost only with queries like "item_a=:X", and will not work when "item_b=:Y". So using index (item_a,item_b,item_c) for first case is just a bad idea when we have equal number of queries on item_b and item_c columns.

    create table hr.tt nologging
    as
    select case when mod(rownum,3) = 0 then rownum end AS item_a,
           case when mod(rownum,3) = 1 then rownum end AS item_b,
           case when mod(rownum,3) = 2 then round(dbms_random.value(1,200)) end AS item_c,
           o.*
     from all_objects o,
    (select * from dual connect by level < 100) d
     where rownum <= 5e6;

    create unique index hr.item_a_idx on hr.tt(item_a) nologging;

    create unique index hr.item_b_idx on hr.tt(item_b) nologging;

    create index hr.item_c_idx on hr.tt(item_c) nologging;

    create table hr.tt2 nologging
    as
    select case when mod(rownum,3) in (0,1) then rownum
                else round(dbms_random.value(1,200))
                end AS item,
           case when mod(rownum,3) = 0 then 'A'
                when mod(rownum,3) = 1 then 'B'
                when mod(rownum,3) = 2 then 'C' end AS item_type,
           o.*
     from all_objects o,
    (select * from dual connect by level < 100) d
     where rownum <= 5e6;

    create index hr.item_item_type_idx on hr.tt2(item_type, item) nologging;

some queries

SYS@mydb> select * from hr.tt where item_a = 300;

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   197 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT         |     1 |   197 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | ITEM_A_IDX |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
...
          4  consistent gets

SYS@mydb> select * from hr.tt2 where item_type = 'A'  and item = 300;

1 row selected.

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   115 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT2                |     1 |   115 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ITEM_ITEM_TYPE_IDX |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          5  consistent gets

Below is an example where optimizer calculations are wrong (oracle 11.2).
Optimizer thinks there will be only one row in the "INDEX RANGE SCAN".
Actually we see 8438 rows. Extended optimizer statistics on these two columns may be helpful, may be not.

SYS@mydb> select * from hr.tt2 where item_type = 'C'  and item = 150;

8438 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4063424880

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   115 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT2                |     1 |   115 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ITEM_ITEM_TYPE_IDX |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_TYPE"='C' AND "ITEM"=150)

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       8618  consistent gets
       . . .
       8438  rows processed

And below is the same case for individual item_c column.
Here optimizer calculations are correct - 8133 rows estimated, 8290 rows selected.

SYS@mydb> select * from hr.tt where item_c = 150;

8290 rows selected.

Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  8133 |   826K|  7737   (1)| 00:01:33 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT         |  8133 |   826K|  7737   (1)| 00:01:33 |
|*  2 |   INDEX RANGE SCAN          | ITEM_C_IDX |  8133 |       |    19   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_C"=150)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8499  consistent gets
       . . .
       8290  rows processed


Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.

Ensure that the NUMA configuration of the host is reported accurately by VMWare, and he out-of-the box configuration of SQL Server will do that.  Be sure to apply Service Pack 2 to get all the latest fixes.  There's some good stuff back-ported from SQL 2017 in there.

  Perform Write and Read as fast as possible using all the hardware resources.

The CPUs and RAM are only half the story when it comes to writing.

Your ability to Write will be gated by the performance of the disk containing your database log, and by your network connection to the AlwaysOn Secondary (assuming it's synchronous).

Consider Memory-Optimized Tables which are designed specifically to scale in write-intensive scenarios on big-memory multi-core machines by 1) minimizing logging, 2) eliminating locking and page latching, and 3) enabling native-compilation of TSQL code.

Your ability to Read should be largely unaffected by the disks, unless your frequently-read data is significantly larger than the RAM.


Cursor doesn't print text when I tell it to

Add RAISERROR('', 0, 1) WITH NOWAIT before the delay to flush anything in the buffer to the client (SSMS?) first otherwise SQL Server will wait for more data to fill the packet.

Declare cur cursor for
select * from orders
open cur
fetch next from cur
while(@@fetch_status=0)
begin
    RAISERROR('', 0, 1) WITH NOWAIT
    WAITFOR DELAY '00:00:01'
    fetch next from cur
end
close cur
deallocate cur


What is faster, one big query or many small queries?

What would address your question is the subject JOIN DECOMPOSITION.
According to Page 209 of the Book

You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query:
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';

You might run these queries:
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

Why on earth would you do this ? It looks wasteful at first glance, because you've increased the number of queries without getting anything in return. However, such restructuring can actually give significant performance advantages:

Caching can be more efficient. Many applications cache "objects" that map directly to tables. In this example, if the object with the tag mysql is already cached, the application will skip the first query. If you find posts with an ID of 123, 567, or 908 in the cache, you can remove them from the IN() list. The query cache might also benefit from this strategy. If only one of the tables changes frequently, decomposing a join can reduce the number of cache invalidations.
Executing the queries individually can sometimes reduce lock contention
Doing joins in the application makes it easier to scale the database by placing tables on different servers.
The queries themselves can be more efficient. In this example, using an IN() list instead of a join lets MySQL sort row IDs and retrieve rows more optimally than might be possible with a join.
You can reduce redundant row accesses. Doing a join in the application means retrieving each row only once., whereas a join in the query is essentially a denormalization that might repeatedly access the same data. For the same reason, such restructuring might also reduce the total network traffic and memory usage.
To some extent, you can view this technique as manually implementing a hash join instead of the nested loops algorithm MySQL uses to execute a join. A hash join might be more efficient.

As a result, doings joins in the application can be more efficient when you cache and reuse a lot of data from earlier queries, you distribute data across multiple servers, you replace joins with IN() lists, or a join refers to the same table multiple times.
OBSERVATION
I like the first bulletpoint because InnoDB is a little heavy-handed when it crosschecks the query cache.

Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?
Jun 07, 2014 : Why query_cache_type is disabled by default start from MySQL 5.6?

As for the last bulletpoint, I wrote a post back on Mar 11, 2013 (Is there an execution difference between a JOIN condition and a WHERE condition?) that describes the nested loop algorithm. After reading it, you will see how good join decomposition may be.
As for all other points from the book, the developers really look for performance as the bottom line. Some rely on external means (outside of the application) for performance enhancements such as using a fast disk, get more CPUs/Cores, tuning the storage engine, and tuning the configuration file. Others will buckle down and write better code. Some may resort to coding all the business intelligence in Stored Procedures but still not apply join decomposition (See What are the arguments against or for putting application logic in the database layer? along with the other posts). It's all up to the culture and tolerance of each developer shop.
Some may be satisfied with performance and not touch the code anymore. Other simply don't realize there are great benefits one can reap if they try join composition.
For those developers that are willing ...
GIVE IT A TRY !!!


Database collation different from tempdb collation

Does this database need to be on this Instance? If not, then as @Max Vernon suggested in a comment on the question, you could install this on an Instance that is created with a default Collation of SQL_Latin1_General_CP1_CI_AS. Perhaps you could even get away with using SQL Server Express Edition, if the database is < 10 GB and doesn't require any Enterprise Edition features.

But if that is not an option, then what you are already doing,

  adding COLLATE SQL_Latin1_General_CP1_CI_AS to the CREATE TABLE #TEMPTABLE statement

is pretty much the only reliable thing you can do.

Since you do not want to adversely affect the operation of the app that was delivered by your client, you cannot change the Collation of the new database. And in fact, changing the DB's default Collation wouldn't fix your current problem as it wouldn't update the Collation of the column in the tables in that DB. It would only affect the Collation of string literals and variables, and you would only notice a difference when those two things are the only things being compared (since the Collation of a column would override the implied Collation of string literals and variables).

Since you don't want to adversely affect your existing DBs on your SQL Server, it wouldn't make sense going through the trouble of changing the current Instance's Collation to SQL_Latin1_General_CP1_CI_AS.

Considering that the new DB and related code were developed (and hopefully tested!) on an Instance that had SQL_Latin1_General_CP1_CI_AS as the default Collation, you really need to make it so that the queries in the new DB behave as they did in the environment in which they were created. You can only do this by forcing the Collation of string columns in temporary tables by adding the COLLATE clause (as you doing). Well, technically speaking you can change the temporary tables into table variables since their default Collation will be taken from the current database, but that is a more intrusive change as the Query Optimizer doesn't handle table variables in the same way as temporary tables (and there are other notable differences).

In the COLLATE clause, you could use either DATABASE_DEFAULT or continue using SQL_Latin1_General_CP1_CI_AS. Both are equivalent here since it is not likely that the Collation of the new DB will ever change (and if it does, that means you will have a new DB from the client that does not include your changes anyway ;-).

FYI: There are not many differences between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. They both use the same Locale -- 1033 -- and same Code Page -- 1252. The main differences are:

The SQL_ Collations have been deprecated for a long time and should be avoided if possible
They treat apostrophes and dashes within words differently
The non-SQL_ Collations allow for some expansions / normalization offered by Unicode that will equate more characters than the SQL_ Collations.


I resolved the very same error by changing:

NLS_LANG=AMERICAN_AMERICA.UTF8

to

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

After this i got another subsequent error:

WARNING: '...' column truncated (buffer=..., got=...); consider revising sql_column_buffers

I've fixed it as described in Sphinx Documentation


Is there a way to implement a cross-database task on SQL Server 2012 with the Availability Groups feature?

First, you can technically do it with DTC or linked server transactions - it's just not supported, and it's a bad idea for performance anyway. Let's talk about a better method.

The way the question is written, it sounds like you're doing archival - moving data from the "current" tables on Server1.Table1 to a set of "archive" tables on Server2.Table2. In that case, it's okay if the data is in both places for a short period of time, so I'd break it up into two separate tasks:

Phase 1: The Copy - Insert old data into server2.table2 from server1.table1. Assuming this is an archival process, you could have this as a scheduled job that runs nightly at midnight (or whatever day/times work for you.)

Phase 2: The Delete - Reading from the read-only replica of server2.table2, delete data that still exists in server1.table1. Do this as a scheduled job that runs X minutes after phase 1, every X minutes, for an hour (or however often you want to retry.) This way, if the read-only replica is running several minutes behind, your delete will still happen eventually.

It's important to read from the read-only replica rather than server2 directly - after all, your goal is to make sure the data is actually protected. (This is why the cross-database stuff isn't supported.)

To design this for performance, I wouldn't actually join between server2.table2 and server1.table1, either - that's a recipe for a blocking mess. Instead, grab the max ID in server2.table2, and that will tell you the data to delete on server1. If record ID# 500 has made it to server2.table2, then you can delete all IDs <= 500 on server1.table1. You can use the same trick with dates. Bonus points if you use the fast ordered delete technique.

If you're archiving individual records rather than going by an ID or date field, you'll still have to join directly between the tables - just don't join on server2 directly, but instead its read-only replica.

Yes, this is more work than a single delete statement wrapped in a transaction, but as you start to push performance and availability boundaries (like scaling data across multiple servers with AlwaysOn AGs), you have to do more of this stuff yourself rather than hitting the easy button, heh.


SQL Find Code for Each Separate Column in View

IT will not get you all the way, but you could try string splitting on the new line character (char10):

SELECT
    value
FROM
    INFORMATION_SCHEMA.VIEWS v
    CROSS APPLY string_split(v.VIEW_DEFINITION,CHAR(10))

WHERE
   v.TABLE_SCHEMA = 'dbo' and v.TABLE_NAME = 'CustomerVw';

Result

View definition

create view [dbo].[CustomerVw]
as
 SELECT  FirstName + ' ' +LastName as FullName,
   datediff(yy,BirthDate,GETDATE()) as Age,
   'New York' as CityReason
from dbo.Customer;
GO

some adaptions to get closer to what you want (Not very clean)

SELECT
value
FROM
    INFORMATION_SCHEMA.VIEWS v
    CROSS APPLY string_split(LEFT(SUBSTRING(v.VIEW_DEFINITION,(charindex('SELECT',v.VIEW_DEFINITION) + 6),len(v.VIEW_DEFINITION))
                            ,(CHARINDEX('FROM',SUBSTRING(v.VIEW_DEFINITION,(charindex('SELECT',v.VIEW_DEFINITION) + 6),len(v.VIEW_DEFINITION)))) - 2),CHAR(10))
WHERE
   v.TABLE_SCHEMA = 'dbo' and v.TABLE_NAME = 'CustomerVw';

Result

  FirstName + ' ' +LastName as FullName,
   datediff(yy,BirthDate,GETDATE()) as Age,
   'New York' as CityReason


There is no one answer for all with fill factor.  It largely depends on how your data modifications take place database-wide or in particular indexes.  The default, out-of-the-box is 0 (100) which leaves virtually no space in a page.  What this means is an insert into the middle of a page will cause a page split.

Monitor your Page Splits/sec counter and if you are seeing a high rate, then you may want to reconsider your fill factor.  But don't just blindly set that low, as you will be using more disk space with a lower fill factor.  And naturally, it will cause higher IO reads, as there is more pages to house the data.

I recommend you look at my answer here regarding fill factor.  It's a different type question, but it should give you a better idea about fill factor and how it affects you.


Is it possible to install Developer Edition of SQL Server as the primary development server?

Indeed this setup is possible. When the developers open Sql Server Management Studio, instead of connecting to a locally installed instance (not necessary given your requested setup, but I would still install it) then they would connect to the shared instance on the development server.

That is how our shop is setup.


The Hadoop Support section of the Cassandra Wiki says:

  Cassandra's Hadoop support implements the same interface as HDFS to
  achieve input data locality (see cluster configuration for details on
  data locality and how to split your analytic and realtime read loads).

So the answer appears to be, "yes".


How to deal with fragmentation in PostgreSQL?

CLUSTER widget USING widget_account_id_idx;

That will rewrite the table in index order, and your query will become faster.
There are down sides:

during CLUSTER, the table will be inaccessible

the order is not maintained, so you have to run CLUSTER everynow and then


PostgreSQL query with subquery doesn't working as expected

You are including literal single quote marks into the LIKE query (as shown in the output of your first query).  These literal quote marks are not present in the data, so they do not match.

select tablename
from pg_tables
where tablename like ( select '%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%' );

including the select is harmless in this case, but it is not necessary:

select tablename
from pg_tables
where tablename like ( '%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%' );

You don't even need the parentheses around the whole thing, as || binds tighter than LIKE, but I didn't know that until I did the experiment.


SQL Server TempDB files 1-> n and write stalls growing

The information from sys.dm_io_virtual_file_stats is aggreagated since the database came online.

To get usable IO numbers from sys.dm_io_virtual_file_stats, you really need to capture a baseline, wait for a period of time (say 30 minutes) and take another snapshot of sys.dm_io_virtual_file_stats. The difference between the snapshots is the relative latency during that time.

This script take a baseline snapshot of sys.dm_io_virtual_file_stats, waits 30 minutes (by default) and takes another snapshot of sys.dm_io_virtual_file_stats.  The difference between the snapshots is the relative latency during that time.


You can use MySQL Workbench to connect and migrate your schema/data from MS Access to mySQL.
First you need to configure an ODBC Data Source:

Click Start, and then click Control Panel.
In the Control Panel, double-click Administrative Tools.
In the Administrative Tools dialog box, double-click Data Sources (ODBC). The ODBC Data Source Administrator dialog box appears.
Click User DSN, System DSN, or File DSN, depending on the type of data source you want to add. Click Add.
Select the driver that you want to use, and then click Finish or Next.
Follow the instructions and enter the required connection information in any dialog boxes that follow.

Back on MySQL Workbench: Use the menu Database > Migrate and select the ODBC data source you've created to use as source for your job.


It seems to me that you need to set the Timestamp column to CURRENT_TIMESTAMP(3). This would set the column to return the time to the three digit precision you are looking for.


How to check/change MySQL named pipe settings?

If you are using Windows, you need not worry about named pipes at all. Why ?
Years ago, MySQL for Windows had distributed three(3) different executables:

mysqld.exe
mysqld-nt.exe
mysqld-max-nt.exe

The two executeables mysqld-nt.exe and mysqld-max-nt.exe used the named pipes protocol. This is briefly mentioned in Chapter 23 Section 23.2 Page 353 Bulletpoint 3 of the Book

I have a 3-year-old post about this (MySQL-NT is crashing frequently)
Those NT-based mysqld executables are no longer being distributed. Even for mysqld-nt, --enable-named-pipe was not enabled by default. The option is still available today for mysqld.exe.
Do yourself a favor and do not enable it since there is so little documentation on using this protocol. It was originally meant for Windows NT (Not There).
YOUR ACTUAL QUESTION
You can run
SHOW GLOBAL VARIABLES LIKE 'named_pipe';

It will either say ON or OFF.
You can configure it by adding this to my.ini under the [mysqld] group header
[mysqld]
named-pipe

and restarting mysqld.exe by running (as Administrator)
C:\> net stop mysql
C:\> net start mysql


pg_restore "CREATE CAST" not executed if using public or pg_catalog schemas

The reason is that type casts do not live in a schema.

If you specify -n public, you will only get objects from that schema, and that does not include the type cast.


Remember: you do not have a recovery plan until you tested your recovery plan...

The theory is simple: on the disaster recovery machine you have prepared for such cases you restore the most recent full backup, then you apply the most recent diff backup (if any) and then all the hourly log backups taken after the most recent diff (or after the most recent full if no diff was yet taken since the last full). Since you were not able to backup the tail of the log, you will loose up to one hour of transactions. Since your whole recovery schedule was based on a plan to be able to restore within the maximum allowable downtime, your restored server will be up and running (including all jobs, migrated logins etc etc, and the application will be redirected to it...) within the allowed downtime, because you tested this before and is a known procedure, and the operator follows the well know checklist you have set in place for such an event (and had tested it!). And btw, if the maximum allowable downtime is shorter than the physical time required to do a full restore a 4.1Tb DB (which is measured in hours), then some warm or even hot standby (mirror, log shipping) is ready to take on the work...

What happens in the real world is that usually this is the moment when the power of hope (let's hope this never happens...) hits the hard wall of reality...


Your second query will give you all companies that have (one or more) campaign centers with id different than 'Demo'. But those that have a 'Demo' and some other campaign id will still be included.

I guess that you want something different. To find those companies that none of their campaign_id is 'Demo'. If so, you need an anti-semijoin (often called anti-join) which can be accomplished with either NOT IN or a LEFT JOIN / IS NULL or a NOT EXISTS query:

SELECT c.id, c.company_name
FROM center AS c
WHERE NOT EXISTS
      ( SELECT 1
        FROM campaign_center AS cc
        WHERE c.id = cc.center_id
          AND cc.campaign_id = 'Demo'
      ) ;


MySQL table relations, inheritance or not?

I would recommend that you have a Pages table that contains both Static, Blog and any new pages you may have. The Pages table would have the columns page_url, page_content and pagetype (a tiny integer) which will help you differentiate between different pages. Later u may end up with other page types which have content hence leaving page content as a field in the pages table and may also allow you to have custom PHP or markup content in a blog page for customization (thinking aloud)

You can then have a BlogPages table which has the information for blog pages linking to the categories and posts.


No difference. Only the order of the columns in the SELECT list changes.

See related questions:

Is it correct, order of where clause doesn't matter when it is used with join?
join syntax / style performance consideration
Is a join optimized to a where clause at runtime?

Changes like this will almost 100% produce the same execution plan.

Communtiy Wiki answer based on an original comment on the question by @ypercube


MySQL - What is the diffrence between ClearText authentication and PAM authentication plugins?

The Cleartext Authentication Plugin, as you have pointed out, is included in all distributions, but it's not quite correct to that it "is applicable to all MySQL servers."  Yes, it is, but actually, not really... because with Cleartext:

  There is no corresponding server-side plugin. Rather, the client-side plugin can be used by any server-side plugin that needs a clear text password

Okay, Oracle, which server-side plugins would those be?

The answer to that would appear to be "none of them," except, of course, the proprietary PAM plugin.

Of course, if you aren't using a plugin that needs it on the server-side, there's no reason you would use the cleartext plugin on the client side, and the only reason you'd need it on the server side would be because you were going to pass the credentials on to another system... like the PAM plugin does.

If you need the PAM authentication plugin's capabilities, the choices appear to be these:

write your own server-side plugin for PAM authentication or direct authentication against whatever backend system contains your access credentials
use Percona's PAM Authentication Plugin for MySQL which is free and open source and works in MySQL Server as well as in Percona Server
use MariaDB, which supports PAM authentication through its own native client mechanism since 5.2.10 and with some support for the MySQL cleartext client plugin as of MariaDB 5.5.32
pay Oracle's asking price for "Enterprise" MySQL and get the "official" plugin.


Understanding why Replication Distribution Agent stops

As far as starting it...add a schedule that runs the job every minute. If the job is running, no issues.  If not, it will start.

can't help on question 1 with no additional info.


If you wish to mark the line a comment, then prepend the hash mark like so:

# cpu=true

Any line beginning this way will be ignored completely.

Although it is not Windows (which I believe you are running), you can see examples in the Linux config file examples.


Transfer of large database from 1 server to another

Before you bring ZIP into the mix, be advised that Sql Server natively supports backing up a database to multiple files typically resulting in smaller files and faster backup time.

Check out Backup to multiple files for faster and smaller SQL Server files by Greg Robidoux.

Summarizing Greg's post:

Problem

  Have you ever wished you could get your backups to run faster? Well
  there may be a way, by writing to multiple files. Creating SQL Server
  backups is pretty simple to do; you can use the SQL Server management
  tools or you can use T-SQL commands to issue the backup. But sometimes
  with large databases it takes a long time to run and it is difficult
  to copy this very large file across the network or even to your backup
  tapes.

Solution

  Write your database backup to multiple files. In addition to writing
  your database backup to one file you have the ability to write to
  multiple files at the same time and therefore split up the workload.
  The advantage to doing this is that the backup process can run using
  multiple threads and therefore finish faster as well as having much
  smaller files that can be moved across the network or copied to a CD
  or DVD. Another advantage to writing to multiple files is if you have
  multiple disk arrays you can write your backup files to different
  arrays and therefore get better I/O throughput. One thing to note is
  that to maintain the I/O throughput you should keep the writing of
  your backup files and the reading of the database file on different
  disk arrays.

Backup to multiple files using T-SQL

BACKUP DATABASE [Northwind] TO
DISK = 'C:\Northwind_file1.bak',
DISK = 'D:\Northwind_file2.bak',
DISK = 'E:\Northwind_file3.bak',
DISK = 'F:\Northwind_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT,COMPRESSION

On another note:

You should SERIOUSLY consider taking this backup with COPY_ONLY to make sure you don't mess up your production differential base.


Cannot create an additional database in Oracle 18c XE

Licensing Restrictions

  Oracle Database XE restricts itself to only one installation per
  logical environment. The logical environment can either be a virtual
  host such as a VM or container, or a physical host. If more than one
  Oracle Database XE installation is attempted to be started in such a
  logical environment, an "ORA-00442: Oracle Database Express Edition
  (XE) single instance violation error" is raised and the database will
  not start.


Property Size is not available for database

I have resolved this by restarting the SQL Server service, unfortunately/fortunately.

My initial idea was to detach/attach the database. Not that I thought this would actually fix the problem, I just read that in a forum somewhere. This had no effect.

I regrettably did not do what  PJ Mahoney suggested re: running a trace. I hope I see this issue again so I can find out if a trace reveals anything. Thanks for the suggestion.

At least answering my own question means I don't have to see another suggestion about changing the database owner ;P

Thanks


How to achieve this sql server replication scenario?

Yes, this type of topology is known as the Central Subscriber Model.  To make this work you should horizontally partition your published data, utilize static row filters, and set the Action if name is in use article property to Delete data. If article has a row filter, delete only data that matches the filter.

The MSDN article does not detail the implementation very well but I have covered it in Central Subscriber Model Explained.