Best way to linking different entities to a single table

Good question, you have provided a quite engaging context and analysis.

According to my understanding of your scenario description, some important aspects can be handled by means of (Super)Type-Subtype relationships. In this regard, this post provides relevant information.

Assumed business rules

As per your current specifications, the following assertions appear valid:

A Party[1] is either a Person or an Organization.
A Party grants zero-one-or-many Donations.
A Person can be a Voter and a NonVoter.

Preliminary logical data model

Therefore, I have derived the IDEF1X[2] logical data model presented in Figure 1.

Party and Donation

As you can see, the incorporation of the Party entity as an exclusive supertype provides the possibility for a Person or an Organization to be able to grant Donations, since the PartyId attribute migrates[3] from Party to Donation, but I have assigned a role name[4] (i.e., DonorId) to such attribute in order to make it more meaningful in the context of the Donation entity.

Person and Organization

In the same way, PartyId migrates to Person as PersonId, and to Organization as OrganizationId, and these two entities, in turn, can hold their specific attributes.

Voter and NonVoter

I consider that, in your business context, a given Person can be a Voter and a NonVoter over time, depending on the particular District in which said Person is located, thus I have temporarily depicted this circumstances in a nonexclusive supertype cluster, but I consider that we need to clarify some points in this respect.

Aspects that require clarification

The goal of this data modeling excersise is to capture the things of interest with respect to your business context requirements, and one can serve that purpose by determining the entities involved along with their respective attributes (including, with especial attention, the ones that uniquely identify each entity ocurrence) and the way in which such entities are interconected. In this manner, the semantic value that said aspects hold for you, and your data users, is paramount. The better these factors are comprehended, the better they can be depicted in a data model.

Having said that, I have created three more models (also preliminary) in order to have a vehicule to exemplify some ideas about your scenario. They are contained in this PDF, which you can download from Dropbox.

Then, here are the concrete aspects that need clarification:

How do you plan to inscribe people in your system? (a) Do you have access to the electoral register (or electoral roll)? (b) Will you enter this data once you have door knocked their house?
Which attributes do you want to store about a given DoorKnock?
It seems reasonable to state that a Person may receive the status of Voter or NonVoter in the context of —as mentioned above— (a) the District in which such Person resides and (b) a determined Election instance. What do you think about it?

Maybe, a person must live within a certain period in a given district in order to be able to vote in a determined election.

What are the attributes of interest in relation to a Person?
Which are the particular attributes that you want to retain regarding a Voter?
What are the specific attributes that you wish to store about a NonVoter?

Perhaps, they are personal attributes that belong in the Person entity.

It also appears fair to say that a Donation can be granted only in the context of an Election occurrence, is this correct?

This possibility is expressed in all the three models.

With respect to Donations granted by Organizations, (a) are organizations that reside outside your election district allowed to make donations, or (b) is this only permitted for organizations located in your specific district?

If this is allowed only to local Organizations, it would be necessary to move the District PK from Person to Party. I am thinking that, actually, that could be the right place for this attribute, but let me know your ideas about it. I have illustrated this option in Model C.

How much Donations are allowed by each Party? (a) One, (b)  a fixed number, or (c) an undefined quantity?

Option (c) is also currently presented in the three models.

Are there restrictions related to (a) a MinimumAmount and (b) a MaximumAmount per individual Donation?
Do you have restrictions associated to (a) a MinimumAmount and (b) a MaximumAmount per the total set of Donations granted by a certain Party?
How do you identify an Election instance?

I think that a given election can be uniquely identified by means of the date in which it is celebrated (as depicted in Model A) and, perhaps, by a combination of the date and the district where it is held (as depicted in Model B).

What attribute (or group or attributes) uniquely identify a District occurrence?

As you know, I have used DistrictNumber for this purpose in all the models, but it may well be a different attribute.

Response to your comments

  I have never heard of type-subtype relationships in a database. This is very interesting.

Yes —as far as I know— Type-Subtype relationships were introduced, as such, in the relational paradigm back in 1979[5] and, unfortunately, they are not widely used in common implementations. But now that we know them, we can take advantage of this interesting and powerful structure in your scenario.

  I am not all that practiced with databases so not sure how to begin implementing the columns needed. All of my INSERTS are straightforward single table inserts and it looks like this approach will require some fancier queries. I'm definitely willing to learn, though.

Do not worry, it is completely understandable that you are familiar with other computer science branches but not that much with relational databases, and I consider that this is a good oportunity for you to know more about this fascinating discipline, we all learn new things every day.

Yes, INSERTing a row (or set of rows) in a database table implies different considerations than, say, creating a record or file in a programming language.

We need first to focus in obtaining a stable logical data model, then we will easily derive a physical model (DDL statements or “CREATE Table code”). Once we have a clear target (all the aforementioned) defined, I will be glad to offer some ideas and guidance in relation to your INSERTs implementation and other pertaining elements.

Notes

1. Party is a term used in legal contexts to refer to a single person or group of persons that compose an individual entity.

2. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST).

3. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity (or supertype) in its child or category entity (or subtype) as a foreign key”.

4. The use of role names is recommended since 1970 by Dr. E. F. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates role naming.

5. By Dr. Codd (as one would expect) in his work entitled “Extending the Database Relational Model to Capture More Meaning”.


How to escape '%' inside varchar @params for RAISERROR funcion in SQL Server 2012

Use %%:

raiserror(N'This is a message with %%', 0, 1);


idera - why it restores databases faster

From Idera's website, it appears SQL Safe inserts a "shim" (a piece of code, be it CLR or native code) into SQL Server to support what they refer to as "Instant Restore":

  Instant Restore

  Patented Instant Restore technology brings your database online immediately—virtually eliminating application downtime. This can save you huge amounts of time when getting back online in a disaster or searching old backups for a piece of lost data. Once online, your database acts as a fully functional database, supporting all read and write operations. SQL Safe streams data from the backup file “on demand” to support applications and user requests while completing the restore operation in the background.

This indicates, to me, that the database is not actually fully restored prior to the database becoming accessible to client queries.

I'd be just a little concerned about this style of recovery for production since anything that has the possibility of reducing ACID compliance is a risk.  Using this restore capability for development or support seems like a great idea since you no longer need to wait for recovery to complete.


How to find the most common strings between rows

Please store the ingredients in a table not a list.  So let's call that ItemIngredient with two columns ItemId and Ingredient and leave it at that.  The primary key will be (ItemId, Ingredient).  Because searches by Ingredient will probably happen a lot, let's put a non-unique index on Ingredient (which is necessary for below to work the most efficiently).
SELECT
  ItemId
 ,MatchItemId
 ,IngredientMatchCount
 ,MatchRank
FROM
  (
    SELECT
      Base.ItemId
     ,Match.ItemId AS MatchItemId
     ,COUNT(*) AS IngredientMatchCount
     ,RANK() OVER (PARTITION BY Base.ItemId ORDER BY COUNT(*) DESC, Match.ItemId) AS MatchRank
    FROM
      ItemIngredient Base
    INNER JOIN
      ItemIngredient Match
        ON Match.Ingredient = Base.Ingredient
             AND Match.ItemId <> Base.ItemId
    -- Put your WHERE clause here if you want to only fine matches for certain Ids
    GROUP BY
      Base.ItemId
     ,Match.ItemId
  ) matched
WHERE
  MatchRank <= 1 --Or whatever
ORDER BY
  ItemId

You can experiment with RANK vs DENSE_RANK at some point to see what provides the best results for you.  If you need details about the Item you can join back in the outer query.


SQL Database Design Relationship Choices

I would personally design it such that there was one more table called ShiftCategoryLocations (or named something a little more user friendly) that had every logical combination of ShiftId, CategoryId, and LocationId. It would also have its own primary key ShiftCategoryLocationId (or whatever name made more sense) so that your WorkHourLog table could reference the 3 dimensions easily by only having a foreign key relationship on ShiftCategoryLocationId. This is known as a dimensions combinations table.
While for small data you could definitely recreate the relations as needed in the application side, but the most efficiency will be if it existed in the database. It also is more reusable and readable in a sense by being refactored into one place, the database.


Unable to alter partition table to set logged

I was recently affected by this bug. I was able to work around by detaching the partitions, re-defining the parent table to enable logging, and then re-attaching the partitions. Continuing with the OP example:

postgres=> SELECT COUNT(1) FROM customers;
 count
-------
     2
(1 row)

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 u              | customer_ind
 u              | customer_jap
 u              | customers
 u              | customers_def
(4 rows)

Enable LOGGED:

ALTER TABLE customer_ind SET LOGGED;
ALTER TABLE customer_jap SET LOGGED;
ALTER TABLE customers_def SET LOGGED;
ALTER TABLE customers SET LOGGED;

Notice that the parent table remains UNLOGGED:

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 p              | customer_ind
 p              | customer_jap
 u              | customers
 p              | customers_def
(4 rows)

Manually, detach/attach partitions:

ALTER TABLE customers DETACH PARTITION customer_ind;
ALTER TABLE customers DETACH PARTITION customer_jap;
ALTER TABLE customers DETACH PARTITION customers_def;

DROP TABLE customers;
CREATE TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text) PARTITION BY LIST(cust_country);

ALTER TABLE customers ATTACH PARTITION customer_ind FOR VALUES IN ('ind');
ALTER TABLE customers ATTACH PARTITION customer_jap FOR VALUES IN ('jap');
ALTER TABLE customers ATTACH PARTITION customers_def DEFAULT;

Verify that change worked:

postgres=> SELECT COUNT(1) FROM customers;
 count
-------
     2
(1 row)

postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%';
 relpersistence |    relname
----------------+---------------
 p              | customer_ind
 p              | customer_jap
 p              | customers
 p              | customers_def
(4 rows)


Should I add relation id on parent or child table for one to one relation

When the table is splitted vertically with 1:1 relation where slave sub-table record is optional, the common structure is

CREATE TABLE main ( ... ,
                    slave_id INT NULL,
                    UNIQUE INDEX uidx_slave_id (slave_id),
                    FOREIGN KEY (slave_id) REFERENCES slave (id),
                    ... );

CREATE TABLE slave ( id INT NOT NULL PRIMARY KEY,
                     ... );

This structure gives more simple query when you need main records which have no slave record. In all another cases (when you need data from both tables) this and backward structures are close to equivalent.


SQL Server Service Account login or password is not valid error

The issue isn't with the account you are attempting to use, but your account. If you look in the setup log file, which is created during the installation of the SQL Server application, you will see the details of the issue, with technical jargon that will indicate the issue is probably a permission issue with your account having permission to perform a check on the service account credentials:

Slp: Sco: Attempting to check if container ‘WinNT://DOMAIN’ of user account exists
Slp: UserSecurity.ValidateCredentials — Exception caught and ignored, exception is Access is denied.
Slp: UserSecurity.ValidateCredentials — user validation failed

Rebooting usually fixes this issue, as does changing the service account value after installation is complete. A minor issue that sometimes appears.


Fastest way to migrate database to AlwaysOn cluster

The fastest way would be to create a cluster an join your current stand alone server to the cluster as a new node. Set the database to full recovery model, take a full backup and a transaction log backup and restore them to the instance on the other node with norecovery.

If you have a good full backup (most recent), it would be possible to set the recovery model to full, take a differential and then take a log backup using the last full from the simple recovery model as your base. The differential should bridge the lsn gap.

To answer your questions:

I'm not sure why you're switching the database into single_user except that you want no changes to be made. If you add the server as a node as I've set forth, this won't be an issue. Otherwise, yes, you'll need downtime to migrate the database. Your other option would be to set the recovery model to full, get it in full, setup mirroring to the new instance on the cluster and then cut over during a downtime which would be much less time.
That's entirely possible to do, but in your initial question you said you didn't want to do this.
You must restore at least one log or differential to bring the lsn gap as close as possible.
I would not use the wizard, but the join only means you've staged a database that is currently in norecovery and it only needs to join the AOAG. The wizard will want to pre-stage the databases for you and ask you for shared locations, etc. Like I said it's best to pre-stage and not use the wizard for the best flexibility.


DECODE

  In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.


Short answer is NO. There is no such need to restart.


Why Sqlcode 100 does not come with exception

I will explain the sequence of actions.

The select returns no rows and throws an exception NO_DATA_FOUND
The WHEN OTHERS exception handler handles the exception.
the Raise_Application_Error should raise the new exception.

Problems:

The use of the sqlcode variable is not wise, you should remove it as it is not within an error handler.
The EXIT statements after the RAISE_APPLICATION_ERROR's are dead code.
I cannot quite see how you could see the ora-00100 error with this code and still be correct in what you wrote.


considerations when using triggers on a replicated (target) database

This sounds like a great situation to use indexed views. These are pretty much what they sound like: a view that physically stores its results in the database, and is updated automatically when the base tables are updated.

A few cautions:

Having a lot of these in your subscription database could slow down replication (but so could lots of triggers).
There are some strict SET options you need to change in order to create them, and you have to use WITH SCHEMABINDING when creating the view.
There are additional strict requirements about what can be in your view. For example, you can't use outer joins, aggregates, or cross/outer apply.
If you're using Standard Edition, you need to query the view by name using the WITH (NOEXPAND) hint. SQL Server won't automatically use the view if you attempt to query its base tables.

There's (a lot) more detail in this article, as well as a nice example using the AdventureWorks database:
http://msdn.microsoft.com/en-us/library/ms191432%28v=sql.100%29.aspx

If it's a relatively simple query that's only performing poorly because of a lot of data and (inner) joins, this could be an easy way to improve it.


locking question around INSERT during UPDATE query

If there in an index on color then only the red colored rows will be locked. The rest of this answer assumes there is this index.

Another query looking to update the rows will either deadlock or lock wait, or success (with your query deadlocking or waiting). Depending on what locks are in place an in which order. Either way concurrent updates of the same row are prevented.

A new inserted red row will be delayed or deadlock under the concept of the isolation level being repeatable read (the default) and mysql having a gap lock covering red rows. A lower isolation level of read committed will allow an insert.

ref: isolation levels


Advice needed for Querying DateTimeOffset data by end users

We solved this in Oracle using function based indexes. Although, it's not quite as flexible, you can accomplish similar in SQL Server using computed columns.

If all of the information you need is in the table, and you meet all of the ownership requirements (outlined in the second article) then I would recommend making the date/time field a computed column on the table, and then add an index to the computed column.

Computed Columns:

http://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

Indexing Computed Columns:

http://msdn.microsoft.com/en-us/library/ms189292


The file size limit for a FAT32 filesystem is 4 Gigabytes. You will be hitting this limit.


How to move database from Db2/Linux/System-z to Db2/Linux/Intel

Check IBM Data Movement Tool. Link https://www.ibm.com/developerworks/data/library/techarticle/dm-0906datamovement/


Microsoft SQL Server 2008 encryption over multiple DB versions

Requirement 2 requires the data be stored "normally": that is, you can't use encryption in the client for all data. Encrypting data in the client also contradicts requirement 1

Requirement 3 requires the media or the actual backups are encrypted. Encrypting the HDDs isn't reliable because once someone has the actual media then it can normally be unencrypted by a sysadmin.

So, I'd suggest using a 3rd party tool like Red Gate SQL Backup Pro or LiteSpeed by Quest to secure your backups. Points 1 and 2 are satisfied because the on-line database is unchanged


How is MySQL using an index when doing a case sensitive comparison?

Note the link you gave on the BINARY operator where is it says the following

  Posted by Yann Neuhaus on July 20 2005 12:34pm

  Per default the search operation in not case sensitive, example,
  looking for 'HYPE BEAU' returns 'Hype Beau' in the table product :

  mysql> select prod_name, prod_id from products where prod_name = 'HYPE
  BEAU';

+-----------+---------+
| prod_name | prod_id |
+-----------+---------+
| Hype Beau |    9600 |
| Hype Beau |   25860 |
| Hype Beau |   42120 |
| Hype Beau |   46185 |
+-----------+---------+

4 rows in set (0.01 sec)

  This is because the default collation is case insentitive. With the
  binary clause the binary collation is used and the search becomes case
  sensitive :

If the character set's collation is case insensitive, then using the
BINARY operator is not necessary. It would only appear to work fine in that instance.

For more clarification on collations being case insensitive, please read the MySQL Documentation section "The _bin and binary Collations"


Based on the collation names I assume that you are using Microsoft SQL Server.

COLLATE can be used at the database level or the column level.   Since you are trying to UNION two tables, using the column collation on the needed columns will resolve your query.

Here is a sample bit of code to help you:

use testdb
GO
CREATE TABLE dbo.Serbian  (Name VARCHAR(20) COLLATE Serbian_Latin_100_CI_AS);
CREATE TABLE dbo.Croatian (Name VARCHAR(20) COLLATE  Croatian_CI_AS);
GO
INSERT INTO dbo.Serbian VALUES ('serbian');
INSERT INTO dbo.Croatian VALUES ('croation');
GO

-- Collate to a particular named collation
SELECT Name COLLATE Serbian_Latin_100_CI_AS as CollatedNameSerbian from dbo.Serbian
UNION ALL
SELECT Name COLLATE Serbian_Latin_100_CI_AS from dbo.Croatian
GO
-- Collate to the database default collation
SELECT Name  COLLATE database_default as CollatedNameDBDefault from dbo.Serbian
UNION ALL
SELECT Name COLLATE database_default from dbo.Croatian
GO

DROP TABLE dbo.Serbian;
DROP TABLE dbo.Croatian;
GO

Of course, if you have several columns with conflicting collations you will need to define their collations as well.


Is it more efficient to create check constraints on PostgreSQL table before or after bulk load?

You can benchmark it, but it shouldn't make a difference. The check expression will be evaluated for each row, and it only uses the data from that row. It should make no difference if that takes place while you load the data or afterwards.


Unexpected results with random numbers and join types

This might give some insight until one of the smarter folks on the site chimes in.

I put the random results into a temporary table and I consistently get 4 result regardless of the join type.

/* Works as expected -- always four rows */

DECLARE @Rando table
(
    RandomNumber int
);

INSERT INTO
    @Rando
(
    RandomNumber
)
-- This generates 4 random numbers from 1 to 4, endpoints inclusive
SELECT
    1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
FROM
    sys.databases
WHERE
    database_id <= 4;

SELECT
    *
FROM
    @Rando AS R;

SELECT
    rando.RandomNumber
,   d.database_id
FROM
    @Rando AS rando
    LEFT JOIN
        sys.databases d
        ON rando.RandomNumber = d.database_id
ORDER BY 1,2;

/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM
    @Rando AS rando
    INNER JOIN
        sys.databases d
        ON rando.RandomNumber = d.database_id
ORDER BY 1,2;

/* Also returns a varying number of rows */

WITH rando AS
(
    SELECT * FROM @Rando AS rando
)
SELECT r.RandomNumber, d.database_id
FROM
    rando AS r
    INNER JOIN
        sys.databases d
        ON r.RandomNumber = d.database_id
ORDER BY 1,2;

If I compare query plans between your second query and the variation with a table variable, I can see there's a definite difference between the two. The red X is No Join Predicate so that seems really odd to my caveman developer brain

If I eliminate the random bit of the query to a constant  1 % (4), my plan looks better but the Compute Scalar was eliminated so that led me to look closer

It's computing the expression for the random number after the join. Whether that's expected, I still leave to the internal wizards on the site but at least that's why you're getting variable results in your join.

2014

For those playing along at home, the above query plans were generated from a 2008 R2 instance. The 2014 plans look different but the Compute Scalar operation remains after the join.

This is the query plan for a 2014 using the constant expression

This is the query plan for a 2014 instance using the newid expression.

This apparently is by design, Connect issue here. Thanks to @paulWhite for knowing that existed.


Organize repeating timestamped values in schema

I'd go with the 2nd approach.
Don't be afraid of Join(t)s.
Here are some reasons

Fast enough. Chances are extremly low that you'd rely on the additional speed you gain from a flat structure, if there is any. It's not worth such an ugly hacky design
More flexible. You can add a 5th timeframe per value later and don't end up with empty columns
Less redundancy. (see following paragraph)

You should consider taking the Value names and addition value
   properties (unit, measurement device, measurement method, etc.) into
   a 3rd table and link both it with main model over the timeframe and
   the value.
This is because the value name is a relatively huge string that is stored for every timeframe. Storing only a 4 byte integer (FK) is more efficient and flexible.


How do I convert a BIGINT to a DATETIME in Informix 12.1

One way to do it is with this procedure:

{
#   "@(#)$Id: frunixtime.spl,v 1.2 2002/09/25 18:10:48 jleffler Exp $"
#
# Stored procedure FROM_UNIX_TIME written by Jonathan Leffler
# (jleffler@us.ibm.com) as counterpart to TO_UNIX_TIME.
#
# If you run this procedure with no arguments (use the default), you
# need to worry about the time zone the database server is using because
# the value of CURRENT is determined by that, and you need to compensate
# for it if you are using a different time zone.
#
# Note that this version works for dates after 2001-09-09 when the
# interval between 1970-01-01 00:00:00+00:00 and current exceeds the
# range of INTERVAL SECOND(9) TO SECOND.  Accepting DECIMAL(18,5) allows
# it to work for all valid datetime values including fractional seconds.
# In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is
# 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is
# -62135596800 (11 digits).  The integer part of both these values is
# unrepresentable in a 32-bit integer, of course, so most Unix systems
# won't handle this range, and the so-called 'Proleptic Gregorian
# Calendar' used to calculate the dates ignores locale-dependent details
# such as the loss of days that occurred during the switch between the
# Julian and Gregorian calendar, but those are minutiae that most people
# can ignore most of the time.
}

CREATE PROCEDURE from_unix_time(v DECIMAL(18,5) DEFAULT 0)
            RETURNING DATETIME YEAR TO FRACTION(5);
    DEFINE n DATETIME YEAR TO FRACTION(5);
    DEFINE i1 INTEGER;
    DEFINE i2 DECIMAL(11,5);
    LET i1 = v / (24 * 60 * 60);
    LET i2 = v - (i1 * 24 * 60 * 60);
    LET n  = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5);
    LET n  = n + i1 UNITS DAY;
    LET n  = n + i2 UNITS FRACTION(5);
    RETURN n;
END PROCEDURE;

You'd use:

SELECT sdatetime, from_unix_time(sdatetime / 1000)
  FROM CallDetail;

Example output:

1572509662678    2019-10-31 08:14:22.67800
1572518550704    2019-10-31 10:42:30.70400
1572519033540    2019-10-31 10:50:33.54000

I suspect there may now be a built-in method to achieve an equivalent result, but I've forgotten (or never learned) what it is.


Other than restarting SQL Server, is there any way to force the SQLCLR AppDomain to be reset?

There is a more elegant solution that won't affect all other assemblies: just change the PERMISSION_SET of one of the assemblies in the app domain (app domains are per user).

ALTER ASSEMBLY [AssemblyName] WITH PERMISSION_SET = {1 of the 2 levels that
                                                      this assembly is not current at}

Just remember that you will need to set the PERMISSION_SET back to what it was. Also, you need to access a method in the assembly before changing the PERMISSION_SET will unload it; changing an assembly that is not currently loaded into an app domain that is active, but with another assembly, has no effect on the app domain (App Domains are per-DB, per-User, not per-Assembly).

UPDATE
The method described above is the most fine-grained approach where it will only unload that one App Domain. But, it does require that the assembly can be set to one of the other two levels. For assemblies marked as SAFE it will only be possible if either

the database is set to TRUSTWORTHY ON, or
the assembly is signed and a Login, based on an asymmetric key that is itself based on the same signature as the assembly, exists and has been granted either the EXTERNAL ACCESS ASSEMBLY or the UNSAFE ASSEMBLY permission

In this case you can simply turn the TRUSTWORTHY setting ON and then immediately back OFF again and that will unload all App Domains in that particular database:

ALTER DATABASE CURRENT SET TRUSTWORTHY ON;
ALTER DATABASE CURRENT SET TRUSTWORTHY OFF;

If you only have one App Domain in the database anyway (and I suspect this is the case 95%, or more, of the time), then both of the methods described here have the same net effect. And in that situation, the ALTER DATABASE method seems simpler as it doesn't require specifying a particular object name nor does it require knowing what the original PERMISSION_SET was.

ALSO, if you only have a single App Domain then the ALTER DATABASE method is simpler even in the case where the database is either already set to TRUSTWORTHY ON or you have set up the key-base login with the appropriate permission. If you are using a key-based login then you can set TRUSTWORTHY to ON and then OFF again as mentioned above. But if you already have TRUSTWORTHY set to ON, then just reverse it and set it to OFF and then immediately back to ON:

ALTER DATABASE CURRENT SET TRUSTWORTHY OFF;
ALTER DATABASE CURRENT SET TRUSTWORTHY ON;


It is used only for selecting data.

  ...

  The results I am getting in SQL
  Server profiler are rather dissatisfying.

That is because you did not read what issues memory-optimized tables do address.

They were not invented to "just keep your data in memory", if your table is relatively small and frequently used, it will be in memory after the first reading from disk without any "optimizing".

Memory-optimized tables are lock free, it means in highly concurrent environment instead of beeing blocking on modifying when someone reads the data, new versions of rows are inserted, so there is no blocking.

Your table is readonly, as you said, so what did you want to achieve? There is no blocking at all on this your table, so what did you want to resolve?

If you just did something without good understanding of what you are doing, I can suppose that your bucket count was also chosen wrong, and wrong bucket number slows down your SELECT.


PostgreSQL - Execution plan - Actual time meaning

Your assumption 1) is wrong.

The first time is the time it takes to initialize the step. The second time is the total time the step took (including child steps).

So the total time for the sort step is 729ms - 111ms of that were spent retrieving the data through a Seq Scan.


Calling this a way to "setup the relationship" isn't quite technically accurate, but you can do this with an AFTER INSERT trigger on the user table.  If all of the other columns in user_preferences have appropriate default values defined, and creating a "matching" row is all you need, then:

mysql> CREATE TRIGGER user_ai AFTER INSERT ON `user` FOR EACH ROW INSERT INTO user_preferences (user_id) VALUES (NEW.user_id);

http://dev.mysql.com/doc/refman/5.5/en/triggers.html

Triggers that execute more than one statement require you to temporarily change the DELIMITER from the normal semicolon to something else, and use an outer BEGIN and END block.  This also works with a single-statement trigger, but isn't strictly necessary, so the format above will work in this case.


how to select the not null value of a column when it has both null and not null value in teradata?

What I did to solve this was:
CREATE TABLE x
(
  id SMALLINT NOT NULL,
  hcc SMALLINT NULL,
  d_cd SMALLINT NOT NULL
);

and populate it:
INSERT INTO x VALUES
(1,    null,   1),
(1,    5   ,  1),
(2,    null,  2),
(2,    10,     2),
(2,    20,     3),
(3,    null,   3);

And then run this query:
WITH y AS
(
  SELECT id, hcc
  FROM x
  WHERE hcc IS NOT NULL
),
z AS
(
  SELECT id, hcc
  FROM x
  WHERE hcc IS NULL AND id NOT IN (SELECT id FROM y)
)
SELECT id, hcc FROM y
UNION
SELECT id, hcc FROM z
ORDER BY id;

Result:
ID   hcc
1     5
2     10
2     20
3     null

As desired!
In future, could you please provide a fiddle with your table DDL and DML! This should work with Teradata - I double-checked and it does support Common Table Expressions!


I'm going to make a leap of faith and assume you connect to SQL Server using Windows Authentication. In such a case you have a Kerberos  double hop: one hop is your client application connecting to the SQL Server, a second hop is the SQL Server connecting to the remote \\COMPUTER01. Such a double hop falls under the restrictions of Constrained Delegation and you end up accessing the share as Anonymous Login and hence the Access Denied.

To fix the problem you need to enable constrained delegation for the SQL Server service account. This blog How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied) has an example of how to do it, and I really do hope that the step on how to 'enable unconstrained delegation' is just a typo as unconstrained delegation is just plain evil.

In the case I jumped the gun and you are logging into SQL Server using SQL Authentication you will need to create a credential for your SQL login and use that to access network resources. See CREATE CREDENTIAL.


Сardinality estimation of partially covering range predicates

SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step boundary. It is also an interval with two ends rather than one. Writing BETWEEN is the same as writing two separate predicates with >= and <=.

Interval with two boundaries, within a single step

The formula is modified to perform linear interpolation within the step for the number of distinct values expected, and reflect that two range endpoints are now specified (and assumed to exist within the histogram step) rather than one.

Using the histogram steps given in the question:

For the query with BETWEEN '20140615' AND '20140616', the calculation is:

DECLARE
    @Q1 float = CONVERT(float, CONVERT(datetime, '2014-06-15')),
    @Q2 float = CONVERT(float, CONVERT(datetime, '2014-06-16')),
    @K1 float = CONVERT(float, CONVERT(datetime, '2014-06-14')),
    @K2 float = CONVERT(float, CONVERT(datetime, '2014-06-18')),
    @RANGE_ROWS float = 301,
    @DISTINCT_RANGE_ROWS float = 3;

DECLARE
    @S1 float = (@Q1 - @K1) / (@K2 - @K1),
    @S2 float = (@Q2 - @K1) / (@K2 - @K1);

DECLARE
    @F float = @S2 - @S1;

DECLARE
    @AVG_RANGE_ROWS float = @RANGE_ROWS / @DISTINCT_RANGE_ROWS;

SELECT
    @AVG_RANGE_ROWS * ((@F * (@DISTINCT_RANGE_ROWS - 2)) + 2);

...giving 225.75. Changing @Q2 from '20140616' to '20140617' gives a result of 250.833.

Both results match those given in the question.


What are the valid formats of a PostgreSQL schema name?

Per the fine documentation, I think this might be what you're looking for.

  SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable...


Append Records to a fixed number of columns using dynamic data

[], pivot are hints it's MS SQL. Then

select [Cost Center]
  , [Node1] = max(case rn when 1 then [Function] end)
  , [Node2] = max(case rn when 2 then [Function] end)
  , [Node3] = max(case rn when 3 then [Function] end)
  , [Node4] = max(case rn when 4 then [Function] end)
  , [Node5] = max(case rn when 5 then [Function] end)
  , [Node6] = max(case rn when 6 then [Function] end)
from (
    select [Cost Center],[Function]
     , rn = row_number() over(partition by [Cost Center] order by [Function])
     --  or may be ..order by [id]) depending on column sequence you need.
    from youTable) t
group by [Cost Center];


Since you are migrating to a new server, you don't need to bring SQL Server 2008 into the mix.

As stated by SQL Server expert Paul Randal, You can upgrade from any version 2005+ to any other version.  Paul states:

  There’s a persistent myth that you cannot upgrade a database to a
  version more that is more then two versions newer.

  It’s not true.

  You can backup+restore (safest way) or detach+attach (not a safe way)
  from SQL Server 2005 (or higher) to any other newer version. You
  just can’t do an in-place upgrade to the latest version from SQL
  Server 2005.

As for changing the compatibility level, I'd recommend not changing that for several weeks and let the dust settle from the upgrade.  After you're confident that everything is functioning properly from the upgrade, why not bump the compatibility level up to the maximum and monitor performance.  You might even see an improvement in performance.  However, compatibility levels from 120 (SQL Server 2014) and above bring the new cardinality estimator into play, which might present some performance problems.  If you want, you can be more selective in your testing of the new cardinality estimator by using OPTION (QUERYTRACEON 2312) on selected queries.  You can always reduce the level should problems arise.  Do some internet searches on sql server 2014 cardinality estimator issues to familiarize yourself with possible issues.

At some point in the future when you upgrade to a later release of SQL Server, you WILL have to eventually increase the compatibility level.

Finally, check out Upgrade SQL Server for a checklist of items to consider for upgrading SQL Server.

Referencing ALTER DATABASE (Transact-SQL) Compatibility Level:

  From an application perspective, the goal should still be to upgrade
  to the latest compatibility level at some point in time, in order to
  inherit some of the new features, as well as performance improvements
  done in the query optimizer space, but to do so in a controlled way.
  Use the lower compatibility level as a safer migration aid to work
  around version differences, in the behaviors that are controlled by
  the relevant compatibility level setting. For more details, including
  the recommended workflow for upgrading database compatibility level,
  see the Best Practices for upgrading Database Compatibility Level.


SQL statement timing from MySQL 5.7 client when using a script

You can use -vvv with mysql command.

mysql -f -vvv --table -u myuser -pmypassword -h myhost mydb < test.sql

> mysql -vvv < mysql.sql
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-10 04:08:33 |
+---------------------+
1 row in set (0.00 sec)

Bye

Hope it will fulfill your requirement :)


Grant non Sysadmin the ability to change max memory setting SQL Server

You should use signed procedure in this case.

The greate article Signing Procedures with Certificates by Erland Sommarskog explains it well, and here are the steps to perform:

Create a certificate in the master database.
Create a login for that certificate.
Grant that login ALTER SETTINGS.
Export the certificate to file.
Switch to the application database.
Import the certificate from the file.
Delete the file from disk.
Create a user for the certificate.
Sign the stored procedure with the certificate, each time you have
changed the procedure.


Is there a way in PostgreSQL to set an default owner for all future create table statements in a session

Be warned: I'm relatively new to PostgreSQL and so I'm not sure if this is a good practice or not, but...

One way I can think this can be addressed is by executing a REASSIGN OWNED statement at the end of the process that has created all your objects in the database, like this:

REASSIGN OWNED BY CURRENT_USER TO ink;

This way, even if the tables are momentarily owned by the postgres user, you'll see them owned by ink once the creation process ends.


MySQL - Creating an index in table DDL statement or separately

In InnoDB, the PRIMARY KEY is, by definition, clusered and BTree-organized.  If you populate a table before specifying the desired PK, a hidden PK is generated.  This hidden PK must be removed and replaced by the desired PK and the table must be re-sorted according to the new PK.  No "harm" is done, but extra time is taken and the BTree may become more fragmented.

Adding secondary keys (any key other than the PK) separately from the CREATE TABLE may or may not be inefficient.  But, again, no "harm" is done.  The inefficiency depends on the version, the nature of the index, etc.

FOREIGN KEYs are another matter -- they must be applied in the "right order".  This can be achieved by having the CREATE TABLEs in the "right order" or by separately adding the FKs in the "right order".

OK, the documentation in this area is wimpy.  You could report it at bugs.mysql.com .

I prefer to have (and see) everything inside the CREATE TABLE DDL.


Insert Values Into Table, Putting Duplicates In Another Table

Here is a possible solution that seems to work and doesn't require triggers - you'd have to test it against your real data.

--Demo setup
Declare @Parts table (part_number varchar(30), description varchar(30), information varchar(30))
Declare @PartsTemp table (part_number varchar(30), description varchar(30), information varchar(30))
Declare @PartsDuplicates table (part_number varchar(30), description varchar(30), information varchar(30))

insert into @Parts(part_number,description,information) values
('331335A10', 'Desc1', 'Info1')     --Row already exists on the @Parts table

insert into @PartsTemp(part_number,description,information) values
('331335A00', 'Desc1', 'Info1'),    --No row on the @Parts table and no duplicate
('331335A10', 'Desc1', 'Info1'),    --Row already exists on the @Parts table
('331335A11', 'Desc1', 'Info1'),    --No row on the @Parts table
('331335A11', 'Desc2', 'Info1')     --Duplicate row on the @PartsTemp table

--The solution

--Common table expression to add row number to each PartsTemp row
;WITH PartsTempAndRowNumber
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY part_number ORDER BY description
            ) AS rn
    FROM @PartsTemp
    )

--Insert into @PartsDuplicates where either:
--The rn<>1 - meaning duplicates on the @PartsTemp table
--OR
--The part number already exists on the @Parts table
INSERT INTO @PartsDuplicates (
    part_number
    ,description
    ,information
    )
SELECT part_number
    ,description
    ,information
FROM PartsTempAndRowNumber ptarn
WHERE rn <> 1

UNION ALL

SELECT ptarn.part_number
    ,ptarn.description
    ,ptarn.information
FROM PartsTempAndRowNumber ptarn
JOIN @Parts pt
    ON pt.part_number = ptarn.part_number
        AND ptarn.rn = 1

--Insert rows to @Parts selecting from @PartsTemp where the part_number can't be found
--on the @PartsDuplicates table
INSERT INTO @Parts (
    part_number
    ,description
    ,information
    )
SELECT part_number
    ,description
    ,information
FROM @PartsTemp pt
WHERE NOT EXISTS (
        SELECT *
        FROM @PartsDuplicates
        WHERE part_number = pt.part_number
        )

--Verify @Parts rows
SELECT *
FROM @Parts
ORDER BY part_number

--Verify @PartsDuplicates rows
SELECT *
FROM @PartsDuplicates
ORDER BY part_number

After execution @Parts

| part_number | description | information |
|-------------|-------------|-------------|
| 331335A00   | Desc1       | Info1       |
| 331335A10   | Desc1       | Info1       |

After execution @PartsDuplicates

| part_number | description | information |
|-------------|-------------|-------------|
| 331335A10   | Desc1       | Info1       |
| 331335A11   | Desc2       | Info1       |


Memory threshold is attempted on SQL Server

Sadly, sometimes we get less than helpful information to deal with throughout our busy days.

Q. What do Quantified Alerts tell us?

That service A is doing or enduring something.
That information is happening on a system.

Q. What will it take to Qualify my alerts?

Understand the difference between causation , correlation , unrelated events that a Quantified events have with a Qualified event. Merely having an alert about memory is the same as telling a Sports Athlete that he is using all his muscles during a sprint. It means nothing by itself.
Causation are events that directly contribute to impact of measurable scope. An example would be shutting down a service has direct effects on the users.
Correlation are events that relate to the impact as passive, precursors, or active consequences from the event. An example could be delays on the app server from consistently high Memory and CPU usage combined with Long IOs recorded on the Error logs, which one is the actual issue? The High usage may prevent the new connection, but the limits for current service may be related to the IOS. The passive result may be that some users experience downtime while others still have access (though slow).
Unrelated Events are issues that may or may seem like correlation or causation, but in reality are outliers that have no relevance. An example might be a user that has forgot his email but reports the service is down at the same time other users actually have impact to their services.

Conclusion

Think about the trends of events. Does High Memory usage occur during high load or when off hours occur? Are there bad queries being run at the same time or are many, or large queries happening? The first is an issue and the second and third are expected events on a large server.

At the end of the day, having high Memory usage itself is not necessarily a bad thing.  Your server is being used. You need to determine measurable negative events in order to decide if the alert is good or bad.


What causes "lock request time out period exceeded" when droping a column?

Drop the table using T-SQL as then the query will not timeout but instead get blocked. Note down the spid of this query window (it should be visible in the bottom of SSMS, right next to username). Once this goes into "executing" stage, open another query window and do this,

use master
go
select * from sysprocesses where spid = <spid of the query which is dropping the table\column>

Look at the blocked column and note down the spid number. Next query to see who the owner of the spid is (this will tell you who owns the conflicting lock on this table).

select * from sysprocesses where spid = <spid # from above blocked column>

Once you identify this, you can use dbcc inputbuffer(spid) to find out what query/command it is doing that has taken a lock on this table. If you deem that query inconsequential, then you can kill it using kill <spidnumber>. After this your query for the drop table/Column should complete.

Updated: Try to drop column  ALTER TABLE table_name DROP COLUMN column_name;

Check this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/77fc7e09-ed14-481f-89d5-d5e143dab7bd/trying-to-delete-a-table-in-sql-server-management-studio-and-getting-a-strange-error?forum=sqldatabaseengine


Not able to update one of the key value by using JSON_MODIFY function in SQL Server

Your 'counter' properties are in an object within an array, so your JSON path in JSON_MODIFY isn't correct. You need to reference the index of the item in the array to correctly update the value:

UPDATE test01
set json_data = JSON_MODIFY(json_data,'$[0].counter2',2000)
where test01_id =10

If you had additional objects in the array with different counter 1/2/3 values, then you would need to reference them with their correct index, i.e. [1], [2] and so on. You can see a working example of this here.


How to repair corrupted PostgreSQL pg_toast error

I'm sorry you ran into that kind of problem, due to device failure. I suppose you don't have any backup (either physical or logical) from before the corruption.

You may find interesting things in Christophe Petus's conf talk "Corruption war stories". You'll find the slides here and the youtube video there.

What you could do is trying to copy all your uncorrupted data in another table (created with create table like for example to preserve constraints, comments and indexes), drop the other table and rename the new one...


How can I re-structure my activities table?

I would prefer to maintain the activity_image table to support more than a single image per activity. Assume, you are storing the actual images in a separate file server and not storing as BLOB in the table itself.

I don't see what performance issues you are facing? Maybe, a bit of elaboration would be better for the sake of clarity.

I would prefer to store the image path along with other image related details in activity_image table itself, mostly the image path value would be constant unless you decide to move away the images to other folder location.

How huge the tables are? How the queries are written? Do you have proper indexing strategy? Is the query columns are hitting the index precisely? What datatypes are used for indexed columns? Is paging concept implemented on your application? At one search how large the resultset is? Are you restricting user's search and load more when the user scroll (assume web/mobile app)?


mongodb disable write access (while db migration)

There are two similar options available, based on user permissions.

For the user which your application uses to connect to the DB (let's call it applicationConnUser), change its permissions: for example if it currently has the readWrite role, revoke that and grant the read role instead.
Set up a different user - leave applicationConUser unchanged and create a new account applicationConUserReadOnly to which you grant the read role - and reconfigure your application to connect with this account instead.

Note that your application may not react helpfully when its normal operations cannot write to the database; depending on how it is coded, it may crash out as soon as it gets a db error.


SQL Server 2014 CTP 2014 expiration date

The service will not startup after the date mentioned.  Prior to that date, you will need to obtain the SQL Server 2014 RTM to continue using the product past the end of March.

This is a bit of conjecture on my behalf, but since Microsoft almost never releases a third community technology preview (CTP) edition of any product, they are highly likely to make the RTM version available to MSDN subscribers within the next week or two, and then to everyone shortly thereafter and certainly before the end of March.

Just announced; Microsoft has RTM'd SQL Server 2014 for GA on April 1st.  No joke.


Which is faster to update thousands of table rows?

It's generally faster to just try the UPDATE.

Issuing a SELECT first, just increases the read cost. Plus, more importantly, it's unreliable under concurrent write load to the database anyway. You would have to add FOR UPDATE or similar to lock rows. But don't. Just issue the UPDATE.

One way to increase performance in many cases, though: avoid empty updates:

UPDATE public.table1
SET    column1 = 'newvalue'
     , date_update = ...
WHERE  column1 = 'oldvalue'
AND   (column1   , date_update) IS DISTINCT FROM
      ('newvalue', ...        );  -- to skip empty updates

Related:

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

It can make sense to issue SELECT first if you want to involve LIMIT (or similar), which is not allowed in UPDATE directly. But consider concurrency:

Postgres UPDATE ... LIMIT 1

Resulting performance can vary with lots of parameters. Warm cache, concurrent transactions, load on the sever, etc. Your case may be particularly tricky:

  check a lot of tables with the same condition

Involving lots of tables makes it hard to find the best query plan. If you know that a certain predicate is particularly selective - and Postgres doesn't  - then it might pay to run a cheap, simplified SELECT first. But you need to deal with concurrency properly.

Typically, this would indicate problems with your query or your server configuration concerning cost settings or statistics, and it would be better to fix that instead of working around the problem. But Postgres does have a few blind spots, like no combined statistics for multiple conditions or no statistics for nested values in document types like jsonb, xml etc.

Update: Postges 10 added "multi-column optimizer statistics to compute the correlation ratio and number of distinct values". (Still does not help with values nested in document types.) Details in the manual.

When updating large parts of the whole table, different strategies might pay:

Optimizing bulk update performance in PostgreSQL


Error: FATAL: no pg_hba.conf entry for host - PostgreSQL - pg_hba.conf is empty

Upgrading a database with either dump/restore or pg_upgrade will not copy the configuration files, so you are left with whatever pg_hba.conf was there right after initdb created the new cluster.

You have to edit and adapt postgresql.conf and pg_hba.conf and other configuration files you use manually on the new cluster. With pg_hba.conf copying the file over is usually good enough, but postgresql.conf requires more attention.


Why is SELECT * much faster than selecting all columns (in a different column order) by name?

This was posted to pgsql-hackers mailing list and I tried to answer in brief there. It seems if the target list (specified columns) matches the tuple descriptor of the relation exactly, that is, both in number of columns and order, then the underlying scan can return a tuple that's directly consumable by the enclosing Sort node. On the other hand, if the target list does not match (either in order or  the number of specified columns), the scan returns a form of the tuples that requires Sort's data preparation step to perform extra work (convert from an internal tuple format to the format directly consumable by the sorting code).

By the way, '*' is internally transformed into a list that (intuitively) matches the relation's tuple descriptor.

EDIT:
If you look at your latter EXPLAIN ANALYZE's Seq Scan's actual times, you can see it's more than former's. That happened because the scan performed an additional step of projection (that is, converting heap tuple into an internal values[], nulls[] format). And because that happened, the upper Sort node had to do extra work in its data initialization, that of converting it back to the tuple format that the actual sorting step understands. That's evident from the Sort's startup cost. That doesn't happen in the former case. That is, both the scan returns the tuple as it is and sort's initialization step simply copies it.


MySQL: Is it best practice to check whether some column is NULL by `NOT ISNULL(column_name)`?

How many different values do you need?  It sounds like you need only "true" and "false".  If so, declare the column to be NOT NULL.  There is no need for a NULL check if it is declared NOT NULL.  The NOT NULL performs that CHECK.
If you need yes/no/maybe or yes/no/don't-know-yet, etc, then you could declare it to be NULL (which simply means that it has an extra possible setting, namely NULL).


Is it possible to have a script that double quotes a view or proc

In case this helps anyone else, an alternative approach to the using double single-quotes to represent a single single-quote in a string is CHAR(39).  That sentence is hard to follow, so as an example:

'Don''t'

can be represented using this approach as

'Don' + CHAR(39) + 't'

While this may not look as pretty with quick strings, it may clear up issues with large blocks of DSQL.  To further clean it up, I also suggest you mask special characters with variables such as:

DECLARE @tick CHAR(1) = CHAR(39)
SELECT 'Don' + @tick + 't code with DSQL'

In my scripts that I want to keep clean, I often take this approach with Tabs CHAR(9) and newline (crlf) characters CHAR(13) + CHAR(10)


I would go for option 1 (two tables).

If you only plan to have 1000 content items (and in 3 years, not now), I don't think you will have many performance problems unless each content item is tagged with a dozen tags where most are unique to that item. And even then I don't think you'll see much of a performance problems. It also simplifies your structure if you have 1 tag table for every content type and then you decide to add/remove/change content types later.

If you're really not sure, do you have the time/resources to build two sample DBs, one based on each structure, populate them with what you think is a realistic load (you can write a script/program to generate & insert test data, right? with "boring" tags and content such as "content_1..content_2000 and tag_1... tag_5000) and actually test which performs better?


How to get random datetime of current day in SQL Server

The only way to go 'back-in-time' would be to add a negative number.  Try adding ABS to your query.

DECLARE @RandomDate datetime

DECLARE @fromDate datetime='2018-04-07'

SELECT @RandomDate= (DATEADD(day, ROUND(DATEDIFF(day, @fromDate, @fromDate)
* RAND(CHECKSUM(NEWID())), 5),DATEADD(second, abs(CHECKSUM(NEWID())) % 86400,
@fromDate)))

SELECT  @RandomDate

ABS - A mathematical function that returns the absolute (positive) value of the specified numeric expression. (ABS changes negative values to positive values. ABS has no effect on zero or positive values.)


Does SSD drive make indexes rebuild and update stats useless with SQL Server?

I'm going to disagree that SSDs are the de facto standard, though they certainly are far more common than a short time ago. I know my company doesn't set any standards, but nearly all of our virtual database servers still use an auto-tiering partial-flash SAN, against my better judgement

And unfortunately, I'm going to start my answer with an "it depends."

In general, index maintenance is far less important than it was in prior years and older versions - specifically, the long and deep index rebuilds that used to be offline-only. The days of "reorganize everything and rebuild if > 30% fragmented" are pretty much over. I think most would agree that rebuilds are no longer worth doing, and SSDs do play a part in that.

On the other hand, statistics have less to do with fragmentation and physical distribution of data on disks and more to do with logical distribution of data across/among the table(s), so this can still be important. Whether it's important for a given workload depends on a lot of factors.

If you can test stopping these maintenance routines in a non-Prod system, you can look for poorly-performing queries, then analyze the DB engine's steps in the Execution plan. All of the statistics issues I've seen (and I've been dealing with some recently) have been easily visible by looking at the operators in an execution plan and seeing huge differences between "estimated number of rows" vs "actual number of rows". If the queries perform poorly enough, execute a quick statistics update and try again. It's a bit of work going through all of these steps, but once you have a handle on the system you can tell if statistics maintenance jobs are worthless overhead or important and necessary.

Erin Stellato has several great blog posts about statistics that can help with background information on why fresh statistics can be important, and also when they're most useful and how to detect if they're not updated frequently enough.


AG (SQLServer 2014) - Suspend Data Movement

The short answer is No you cannot do that. First, if you suspend the data movement in one of the availability group database, you no longer able to failover it to your secondary node. Unless you make a forced failover. If you want to suspend the data movement in one of your databases, and also want to make the failover, you have to remove that specific database from the availability group and change the data. However, to add it back to the availability group, first you need to synchronize it back again.


Which is more efficient, filtering the SSRS dataset or filter using a query parameter

TL/DR

Yes you are correct, you can prove it by showing the result of ExecutionLog in the SSRS database.

Longer Answer

I created 2 identical reports based on the Adventureworks database, one with a filter on the dataset for City and one with a parameter in the query for City.

Report 1

The query for this report is:

SELECT  Person.Address.*
FROM    Person.Address

And the filter is added like this:

The result of this report is:

Report 2

The query for this report is

SELECT  Person.Address.*
FROM    Person.Address WHERE city=@city

There are no filters on this dataset

The result of this report is:

Where you can see the same data is shown, but the behaviour is different since the user has to put in a value for the filter (marked in yellow)

If this is undesired this can be overcome by adding a default value to the parameter and setting the visibility to hidden like this:

and this:

Proof of efficiency

The efficiency of both reports can be proven by querying the executionlog table and views like this:

SELECT [ItemPath], [Parameters], [TimeDataRetrieval], [TimeProcessing], [TimeRendering], [RowCount]
FROM ExecutionLog3;

Which for these 2 reports returns:

+--------------------------+--------------+-------------------+----------------+---------------+----------+
|         ItemPath         |  Parameters  | TimeDataRetrieval | TimeProcessing | TimeRendering | RowCount |
+--------------------------+--------------+-------------------+----------------+---------------+----------+
| /Report Project3/Report1 | NULL         |               669 |           1878 |           880 |    19614 |
| /Report Project3/Report2 | city=Bothell |                 8 |             42 |             4 |       26 |
+--------------------------+--------------+-------------------+----------------+---------------+----------+

So the second method not only fetched a lot less rows (26 versus 19614) but also consumed less time processing and rendering the report.


Show all stored procedure definitions (content) for editing

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text.

So first, make sure you change this to 8192:

  Tools > Options > Query Results > SQL Server > Results to Text >
  Maximum number of characters displayed in each column

Now you can make one script for all the procedures that are less than 4K:

SELECT REPLACE(m.definition, N'CREATE PROC', N'ALTER PROC')
  + CHAR(13) + CHAR(10) + N'GO'
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p
ON m.[object_id] = p.[object_id]
WHERE LEN(m.definition) <= 4000
ORDER BY p.name;

(Of course, your replacement is naïve - what if they have create    procedure (more than one space) or you are on a case sensitive collation? You may have to fix some of those manually.)

Now, you only have to worry about manually deriving the script for the larger procedures. First, see if you have any:

SELECT name
  FROM sys.procedures
  WHERE LEN(OBJECT_DEFINITION([object_id])) > 4000;

I talk a little about some workarounds here (in the context of dynamic SQL, but the same would be true if you assigned OBJECT_DEFINITION() to a variable).

Mr. Magoo is right though, you will get the full definition without having to worry about truncation if you use the scripting functionality within Management Studio:

right-click a procedure, modify or script as > alter to >
in Object Explorer Details, do the same as above but for multiple
right-click a database, tasks > generate scripts > (but this does CREATE, not ALTER, so you'll need to perform replacements still)

As an aside, while you are adding semi-colons everywhere, I also suggest ensuring that all of your procedures are created with schema prefixes (e.g. dbo.procedurename, not just procedurename), and also for all object references inside the procedure. Those are likely to cause problems long before a lack of semi-colons ever will. Also a few general things to keep in mind while performing your changes.


Performance of inner join query for certain condition

Run one query, make sure you have good indexes

Select Name2
From Table2 Inner join Table1 on Table1.T1ITable2.T1Id
Where Table2.Type IN ('A', 'B', 'C') and Table1.Code='ABC'

A graphical query plan will show where you need indexes

A million rows isn't much and well within capability of SQL Server


How do I find the day of week of a date-time field in postgres?

The whole issue revolves around CASTing and quirky PostgreSQL syntax.

CASTing strings as DATEs:

It's very simple - your first line of code:

ntest=# SELECT EXTRACT(DOW FROM '2011-07-07');
ERROR:  function pg_catalog.date_part(unknown, unknown) is not unique
LINE 1: SELECT EXTRACT(DOW FROM '2011-07-07');
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
ntest=#

does not work. This is because, as far as PostreSQL  is concerned, '2011-07-07' is just a string literal, not a DATE. This does work:

ntest=# SELECT EXTRACT(DOW FROM DATE '2011-07-07');
 date_part
-----------
         4
(1 row)

Note the extra DATE that CASTs the string as such. You can also CAST it this way:

ntest=# SELECT EXTRACT(DOW FROM '2011-07-07'::DATE);
 date_part
-----------
         4

This double colon (::) is very much a PostreSQL thing. It's really quite "obvious", once you grasp the PostgreSQL syntax (it had me stumped for a couple of minutes!)

A date field:

ntest=# \d orders;
       Table "public.orders"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 ldate       | date    |
 customer_id | integer |
 amount      | integer |

So, we have a date field - with records as below:

ntest=# SELECT * FROM orders;
   ldate    | customer_id | amount
------------+-------------+--------
 2009-10-08 |           3 |   3000
 2009-10-08 |           3 |   1500
 2009-11-20 |           2 |   1560
 2008-05-20 |           4 |   2060
(4 rows)

So, to get the "day of week" from a date (note the two FROMs - doesn't work without the FROM between brackets):

ntest=# SELECT EXTRACT(DOW FROM ldate) FROM orders;
 date_part
-----------
         4
         4
         5
         2
(4 rows)

Now, for a timestamp field:

ntest=# \d timestamptest;
           Table "public.timestamptest"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 x      | timestamp without time zone |

And:

ntest=# SELECT * FROM timestamptest;
             x
----------------------------
 2016-04-18 22:52:30.020979
(1 row)

And DOW (again, note the two FROMs):

ntest=# SELECT EXTRACT(DOW FROM x) FROM timestamptest;
 date_part
-----------
         1

Quirky syntax (the double colon also OK, if you want to be really explicit), but it works fine once you've mastered it.


PostgreSQL undocumented timenow() function?

Yes, this is undocumented, very old, deprecated for a long time ago stuff.
The key difference from the NOW function is the data type of the result. timenow used the type abstime, which was less accurate and prone to the year 2038 problem. There is a small note in the documentation:

The types abstime and reltime are lower precision types which are used internally. You are discouraged from using these types in applications; these internal types might disappear in a future release.

This type was completely removed in postgresql 12 with all related functions. Per source code I can confirm: yes, timenow will return real time by every call, not the time of transaction start.
You could achieve same result by using clock_timestamp() function instead of now()


How can I find the backup that requires the least amount of archivelogs to restore?

I have a weekly script that automates a duplicate from prod backup.  WHen I inherited it, it did a duplicate until time and was highly dependent on jobs completing on schedule so it could assume a good 'until time'.  I modified it to get a good scn and so duplicate 'until scn'.  I get the scn with this snippet of code querying the rman catalog, in a shell script.  If you are on windows, you'll need to adjust scripting technique, but principle is the same:

    # 2. Get the scn from the most recent archlog backup

#

rcvryscn=$(sqlplus -s rman/pwd@rmcat <<EOF

set echo off feedback off verify off trimsp on head off

col maxscn for 9999999999999999

select max(first_change#) maxscn

from rc_backup_archivelog_details a

join rc_database d on a.db_key=d.db_key

where d.dbid=$dwprdid;

EOF

)


Difference between EXEC() and sp_executesql with no parameters?

sp_executesql supports parameterisation, whereas EXEC only accepts a string.

Only performance differences that may arise are due to the parameterisation i.e. a parameterised sp_executesql call is more likely to have a reusable cached plan. An EXEC call is likely to lead to lots of single use space wasters in the plan cache.


Unrecognised system process saturating CPU

This was indeed down to some structure being left over from a SQL2012 AV group as had been doing some testing on these servers during a period when they were not in production as they were the only hardware in different DC's suitable.

During the next window of opportunity I rebooted the servers and the performance of both servers has vastly improved and the processes are no longer appearing. (added to my list of issues to all the ones Brent details on his website with SQL2012 AV group issues)

2012 AlwaysON Setup

A must read for anyone attempting to configure these. As noted in Brent's articles I found a number of pitfalls, more tricky than expected and currently have decided to postpone the rollout due to my own issues with unsuccessful multisubnet failover (i could only get this to work in 1 direction) and above mentioned performance overhead.


Upgrading SQL Server 2008 Standard to Enterprise

Have your client with the ISOs or install files open the DefaultSetup file within either the x64 or x86 directory, cut and paste, and send you the text for the SQL Server edition you require. It should look something like:

;SQLSERVER2008 Configuration File
[SQLSERVER2008]
PID="AAAAA-BBBBB-CCCCC-DDDDD-EEEEE"

No need to copy/download the whole ISO.


Moving large database to another drive, how to measure performance before, and setup some type of replication for fast fail back?

You can use perfmon to measure your disk performance and contention.  I would measure the following counters for the appropriate drives (all under physical disk):

Avg. Disk sec/Transfer - Otherwise known as IOPs, this is the total number of actions (Read and Write) that occur on your disk.
Avg. Disk sec/Read - This is the average amount of time it takes an action to read from your drive.  For DB operations, it's recommended (generally) this be under 20ms.
Avg. Disk sec/Write - Same as for sec/Read, but for writes.  Again, should be (generally) under 20ms.

You want to monitor these values over a period of time to see how they trend.  If you capture them at regular intervals (default is every 15 seconds), put them into Excel and calculate your Min, Max, and Average over a reasonable period of time for your standard work period to establish your current baseline.

Next up, for testing the drive, SQLIO is a good tool.  I'd probably combine it with the perfmon counters to measure how the disk performs in comparison to your current setup.  This will give you a general idea of how the disk will perform.

For replication, you can replicate databases and individual objects, but not files.  So to accomplish your goal, you would need to basically create a full copy of the database to replicate to and handle the different naming/connection strings.  That being said, I believe that's WAY overthinking the issue.  If you do enough baselining and testing up front, you will know how it will perform before you even make the move.


From this post: Obtaining a unique database identifier for SQL Server 2005 and later
 it seems that

  probably SMO reads the database_guid value from
  sys.database_recovery_status:

  Used to relate all the database files of a database together. All
  files must have this GUID in their header page for the database to
  start as expected. Only one database should ever have this GUID, but
  duplicates can be created by copying and attaching databases. RESTORE
  always generates a new GUID when you restore a database that does not
  yet exist.

And if you open the documentation sys.database_recovery_status (Transact-SQL) you'll find that

  NULL= Database is offline, or the database will not start.

I tried to put some my databases offline and to query database_guid from  sys.database_recovery_status and it is NULL while it's not NULL when bring these databases online again.


Temp table lives after being explicitly dropped

Try putting a batch separator (GO) command between the SELECT/DROP/SELECT

GO signals the end of a batch of Transact-SQL statements

SELECT *
INTO #tmp
FROM dbo.SourceTable1
WHERE 1 = 0

GO

DROP TABLE #tmp

GO

SELECT *
INTO #tmp
FROM dbo.SourceTable2
WHERE 1 = 0


check if some tables exist or not in SQL Server database

Well if you turn on certain options in SSMS like "Check for object existence" you will get a script that looks like this when dropping and re-creating an object (e.g. table):

IF NOT EXISTS
    (SELECT * FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[<schema>].[<object_name>]')
        AND type in (N'U')
    )

Is it better? Uncertain.
Is it different? Yes.
Is it what Microsoft would do? Yes.


mysql - best way to provide unique voucher

One way to do it is to update and select in the same statement; and add a condition to the statement so that (if selected, don't select again)

Here is my way:

SET @voucher_id=0;
UPDATE voucher_table SET status=1, v_id = (SELECT @voucher_id := v_id) WHERE status=0 LIMIT 1;
SELECT * FROM voucher_table WHERE v_id=@voucher_id;

It is kind of creating an artificial lock. Notice that any other transaction (or user) won't be able to pick up the same voucher ID due to the Atomicity and Isolation in ACID properties of the database systems.


what do they stand for?

Codes like HYT00 and HY000 are called SQLSTATE. The HY part is the Class.
I can't find any documentation that even hints at what it stands for. There is nothing to indicate that the individual letters have any meaning. Most other classes are numeric (hex), ranging from 00 to 46. And then we have HV, HW, HY, and HZ. That would seem to rule out the theory that HY was chosen as a tribute to Henny Youngman. Rather, it looks like they reserved class codes with a first character of I through Z for implementation-specific classes, and that left HZ as be the highest one defined in the ANSI spec. Then they worked backward through HY, HW, and HV for other conditions generally outside of SQL itself -- datalinks, foreign data wrappers, and call-level interfaces. (They skipped HX -- another mystery.)

why are they similar?

Because it is part of the ANSI SQL standard. Any RDBMS that wants to follow the standard will use the SQLSTATE format for their return codes.
According to this Teradata documentation, ANSI defines HY as a CLI condition.

Call-Level Interface condition

Note: The Call-Level Interface referred to is not the Teradata Database CLIv2, but rather an ANSI-standard CLI that is a dialect of the Microsoft Open Database Connectivity, or ODBC, specification.

IBM says:

'HY', is generated by the CLI or ODBC driver.

MariaDB says that HY

is the class used for builtin conditions which do not have a specific SQLSTATE class.

Firebird SQL uses HY for:

"CLI-specific Condition"

The MariaDB usage is an outlier, but the consensus is that HY is used for errors raised by the CLI. (Note that this does not stand for Command Line Interface, but rather for Call-Level Interface, which is the  API for embedding SQL code in a host program. See https://en.wikipedia.org/wiki/Call_Level_Interface.)


Oracle startup error: "end-of-file on communication channel"

O/S-Error: (OS 665) The requested operation could
  not be completed due to a file system limitation

So the windows error 665 is the reason for the error an the error refers to file C:\ORADATAFILES\ORAPROD03\USERS03.DBF.What is the problem with the file? The following article discusses this error message:  What factors can lead to Win32 error 665 (file system limitation)?. If the reason is file fragmentation a patch is available. I know it is to late but maybe a restore will not be necessary in this situation.


Older versions of MySQL allow declaring the CHANGE MASTER TO parameters in my.cnf. Newer version may not. In fact, according to the MySQL Documentation concerning setting replication options in my.cnf:

  Before MySQL 5.1.17, these options are silently ignored if given
  unless there is no master.info file. If that file exists, the MySQL
  server has already previously been configured for replication, so the
  information in the file is used instead. Because the server gives an
  existing master.info file precedence over the startup options just
  described, you might elect not to use startup options for these values
  at all, and instead to specify the replication parameters associated
  with them by using the CHANGE MASTER TO statement. See Section
  12.5.2.1, “CHANGE MASTER TO Syntax”.

  Beginning with MySQL 5.1.17, these options are deprecated and have no
  effect when mysqld is started. If they are used, an appropriate
  warning is written to the error log. Instead, you must use CHANGE
  MASTER TO to set the values corresponding to the deprecated options.
  These options are removed in MySQL 5.5.

Naturally, running CHANGE MASTER TO generates a master.info file, which includes master_ssl. No harm, no foul. Relying on my.cnf for older versions of mysql results in option being ignored when no master.info exists. They are no longer valid.


Speed of WAL send/receive when replicating

You can use pg_stat_replication along with pg_xlog_location_diff. Checking the differences can tell you how many bytes of xlog have flown from the master to the slave.


Oracle caches (placed in System Global area) can not be turned off. You can only increase or decrease the size of these caches.


How to get default locking hint of a table

You can run you query in a transaction and run sp_lock @@spid after running the query, you will know all the locks held.


Learning COMPUTED BY - what right parenthesis does it need?

Forget COMPUTED BY and just use AS which is the same as GENERATED ALWAYS AS ... VIRTUAL
Syntax summary is in the documentation.
There is no computed by clause and there never has been. OP was looking in the manuals for something called Oracle RDB that runs on OpenVMS and nobody uses.


Find time between status changes for each unit on each incident

OK, try this.  I made some assumptions.

All statuses start as DP
Status changes are only one way as specified above and don't revert the other direction.
If a status change is listed twice, use the first earliest instance
If a status change is skipped, use the previous status time stamp to calculate the change
INCIDENT is not desired as part of the AVG status change report for UNIT
Averages in the final query are based off the actual number of status changes that occurred and is variable by UNIT/INCIDENT

With those assumptions in mind I present the following solution.  LAG() isn't an option in 2008, so you are left with making a bunch of datasets to realize the output you want.  I'm using CTEs to generate the lists of UNITS by INCIDENT and then creating temp tables to work with the timestamps.

I could have done a bunch of nested CTEs but then they'd be executed multiple times; same with doing this with sub queries. Finally, when reading the output for the status changes, if a change is preceeded by a NULL value, it is because the status was skipped.  In the case of E14, based off available sample data, there were no status changes from DP.

Hopefully this is what you need or at least enough to get you going.

GENERATE SAMPLE DATA

create table history
(
      UNIT CHAR(3)
    , DATETIMESTAMP CHAR(16)
    , STATUS CHAR(2)
    , INCIDENT CHAR(10)
)

INSERT history
values ('E04','20140617101703ED','DP','F141000001')
     , ('L24','20140617101703ED','DP','F141000001')
     , ('E04','20140617101845ED','ER','F141000001')
     , ('L24','20140617101848ED','ER','F141000001')
     , ('E07','20140617101955ED','DP','F141000002')
     , ('L17','20140617101955ED','DP','F141000002')
     , ('E04','20140617102029ED','AR','F141000001')
     , ('L24','20140617102038ED','AR','F141000001')
     , ('E07','20140617102235ED','ER','F141000002')
     , ('L17','20140617102238ED','ER','F141000002')
     , ('E14','20140617102501ED','DP','F141000003')
     , ('D03','20140617102626ED','DP','F141000002')
     , ('E07','20140617102712ED','ER','F141000002')
     , ('L17','20140617102717ED','ER','F141000002')
     , ('D03','20140617102740ED','ER','F141000002')
     , ('D03','20140617102744ED','aR','F141000002')

--DROP TABLE history

CODE

--GATHER DP AND AK STATUSES AND COMPARE
with
cte_dp as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'DP'
    group by UNIT, STATUS, INCIDENT
),
cte_ak as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'AK'
    group by UNIT, STATUS, INCIDENT
)
select dp.UNIT, coalesce(ak.DATETIMESTAMP, dp.DATETIMESTAMP) PreviousTimeStamp, DATEDIFF(second,dp.DATETIMESTAMP,ak.DATETIMESTAMP) DiffInSecs, dp.INCIDENT, case when ak.UNIT is null then 0 else 1 end as StatusCount
into #dk
from cte_dp dp
    left join cte_ak ak on ak.UNIT = dp.UNIT
        and ak.INCIDENT = dp.INCIDENT
        and ak.DATETIMESTAMP > dp.DATETIMESTAMP;

--GATHER ER STATUS AND COMPARE TO PREVIOUS STATUS CHANGE
with
cte_er as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'ER'
    group by UNIT, STATUS, INCIDENT
)
select dk.UNIT, coalesce(er.DATETIMESTAMP, dk.PreviousTimeStamp) PreviousTimeStamp, DATEDIFF(second,dk.PreviousTimeStamp,er.DATETIMESTAMP) DiffInSecs, dk.INCIDENT, case when er.UNIT is null then 0 else 1 end as StatusCount
into #kr
from #dk dk
    left join cte_er er on er.UNIT = dk.UNIT
            and er.INCIDENT = dk.INCIDENT
            and er.DATETIMESTAMP > dk.PreviousTimeStamp;

--GATHER AR STATUS AND COMPARE WITH PREVIOUS STATUS CHANGE
with
cte_ar as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'AR'
    group by UNIT, STATUS, INCIDENT
)
select kr.UNIT, DATEDIFF(second,kr.PreviousTimeStamp,ar.DATETIMESTAMP) DiffInSecs, kr.INCIDENT, case when ar.UNIT is null then 0 else 1 end as StatusCount
into #rr
from #kr kr
    left join cte_ar ar on ar.UNIT = kr.UNIT
            and ar.INCIDENT = kr.INCIDENT
            and ar.DATETIMESTAMP > kr.PreviousTimeStamp;

--REPORT THE STATUS CHANGE TIMES IN SECONDS FOR EACH STATUS CHANGE, PER UNIT, PER INCIDENT
select dk.UNIT, dk.DiffInSecs 'DP-AK', kr.DiffInSecs 'AK-ER', rr.DiffInSecs 'ER-AR', dk.INCIDENT
from #dk dk
    left join #kr kr on kr.UNIT = dk.UNIT and kr.INCIDENT = dk.INCIDENT
    left join #rr rr on rr.UNIT = dk.UNIT and rr.INCIDENT = dk.INCIDENT

--REPORT THE AVG TIME THE STATUS CHANGED FOR EACH UNIT
select dk.UNIT, (isnull(dk.DiffInSecs,0)+isnull(kr.DiffInSecs,0)+isnull(rr.DiffInSecs,0))/(case when (dk.StatusCount+kr.StatusCount+rr.StatusCount) = 0 then 1 else (dk.StatusCount+kr.StatusCount+rr.StatusCount) end) AverageSeconds_Between_StatusChange
from #dk dk
    left join #kr kr on kr.UNIT = dk.UNIT
    left join #rr rr on rr.UNIT = dk.UNIT

drop table #dk
drop table #kr
drop table #rr


Always on viability/configuration for very specific purpose

The only possible concern in your setup is the Availability Mode and Failover option. If your virtual machines are in the same datacenter or relatively close geographically with a good connection between them you should look into using Synchronous-commit mode and Automatic Failover for the best recovery scenario with no data loss and minimal downtime, especially if it fails during a time it may be difficult to get someone to manually fail it over.

If they are they significantly geographically apart and connectivity isn't reliably 'good' then your setup is fine unless data loss is considered catastrophic. The Asynchronous route is primarily for use when performance is sporadic or far from guaranteed.

While specifying the connection has some value, it may be overkill in such a setting if for some reason your reporting application loses connectivity to the Secondary Replica. There is no reason it couldn't report off the Primary Replica aside from any possible performance concerns, but that may be a valid enough reason to insure the Primary denies the connection.


What schema does this mean 'mysql - schema's default character set: utf8'?

common_schema is a tool that someone added to your server?  Sounds like it has not been updated to utf8mb4.
CHARACTER SET utf8 (aka utf8mb3) is old; utf8mb4 is new.  The latter can handle Emoji and more of the Chinese character set, plus some less common languages.
If you don't know anything about common_schema, ignore the warning.
In your tables, consider using utf8mb4.  This is the default in 8.0; you would have to take deliberate action not to use it.


Can multiple fact tables have the same structure?

Yes and depending on what you are doing that might be a very good idea. I will use a request table as an example.

Request comes in and goes to a submitted request table. After it gets processed it moves to a pending table. If it gets approved it moves to an approved/completed table. If it gets canceled it gets moved to a canceled table. Having the same table structure allows for the system to easily move data around and for users to easily select data from any of the tables.

While this can also be done with a status column the design of the database and amount of data could make it more desirable to have a separate table for each state.


Before the change your application code, you should try to mongo shell this command on your test environment.
db.test.findOneAndUpdate({},
    {"$push":
        {"post.2.likes":"Yunus"}
    })


SQL 2008 permissions - cumulative or not?

I don't know how much you already know about the user and permission system in SQL Server. It is possible that you need to do some studying in order to fit all pieces together, to your liking.
Restore (which includes log shipping) gives you a binary copy of the database. This includes all users in the database with the assigned database level (and lower) permissions. Nothing you can do about this. Except for running post-restore scripts, but that seems undesirable and also impossible since it means breaking log shipping.
Then you have the instance level. This means logins. The only privilege at the instance level that "trickle down" to the database level is sysadmin (including CONTROL SERVER). This allow somebody to do everything, including SELECT, INSERT ect on all tables in all databases. There are of course other server level roles and server level assignable permissions, but those are more "special things", and as far as I can see won't affect traditional object level privileges.
Permissions are cumulative. You have the sum of all privileges. But that excludes any DENY. DENY overrides all types of GRANT. (There is an exception with column level DENY, but my guess is that it won't affect you.) Well, except for sysadmin, DENY isn't checked for those who are sysadmin.
I've tried to follow your description to be more to the point, but I'm afraid I get a bit lost in the details. Perhaps above can get you started and you can get back with specifications or more precise questions?


How to make PostreSQL functions private (inaccessible to end users)?

Just use REVOKE to control the permissions Postgres assigns to roles.
Documentation:

The REVOKE command revokes previously granted privileges from one or more roles. The key word PUBLIC refers to the implicitly defined group of all roles.
See the description of the GRANT command for the meaning of the privilege types.

CREATE FUNCTION nocando()
RETURNS void AS $$
  SELECT null::void
$$ LANGUAGE sql;

REVOKE ALL
  ON FUNCTION nocando ()
  FROM PUBLIC ;


Does joining to the same table twice have a predictable performance impact

There are numerous restrictions to what can be an indexed view. In a data warehouse setting those restrictions will make it impossible for just about every standard star join view to be an indexed view. Which is really a shame, since despite what the comments say, [materialized] indexed views are magic!

As for whether joining to the same table twice is an issue, it is not. In a data warehouse you'll often find yourself joining to time and date dimensions dozens of times.

Also, I was interested to see that you made the primary key on the date dimension the date itself. In my last data warehouse we ended up needing to use an INT column as the primary key, because we needed to save indeterminate dates (like 2016/1/??) where all of the date parts were not known.

Also, when writing your views, you should always check to verify whether a LEFT JOIN will have better performance than an INNER JOIN, even if they produce the same results. Back when our data warehouse was closer to a snowflake schema, the joins were into the hundreds, and many of the views did better with all LEFT JOINs as opposed to INNER JOINs where INNER JOINs were appropriate.


SQL Server Web transaction replication to Azure SQL

According to Xiaochen Wu (Senior Program Manager at Microsoft), there is an error in the documentation. Web edition does not support transaction replication to Azure SQL.


How to compare performance of two versions of a function?

One thing you could do to compare these two versions is to rename one of them to f_old and execute them both inside the same batch, while including the actual execution plan and IO statistics.  From there, it is a simple matter of comparing the plans and IO stats of each.

One thing you need to be wary of is that in your first statement you had IF @b > 0.  In your second statement you have IF @b < 0.  What happens if @b = 0?  You have not covered that possibility.

Finally, I imagine you will find that your thoughts regarding NOT EXISTS vs. EXISTS is correct.  EXISTS will process until the condition is met.  NOT EXISTS requires a table scan (if not indexed) and an index scan (if indexed).  On a small table, you will not see a huge performance gain, but it is always best to design your logic around EXISTS, as opposed to NOT EXISTS.

Matt


Oracle: duplicate schema to another database

Use expdp with the flashback_scn or flashback_time parameters, along with SCHEMAS= to export the schema at a consistent point in time.

This is the same as using CONSISTENT=Y in the legacy exp utility. Examples  here.

If the data for the schema in question is in a tablespace of its own with no other objects from other schemas, you can use rman to do a tablespace point in time recovery.


Try this

SELECT a.descendant_id
    , CASE a.path_length WHEN 0 THEN null ELSE a.ancestor_id END ancestor_id
    , CASE a.path_length WHEN 0 THEN null ELSE a.path_length END path_length
FROM webineh_prefix_nodes_paths AS a
JOIN (
    SELECT descendant_id, MIN(CASE path_length WHEN 0 THEN 1000 ELSE path_length END) min_path
    FROM (
        SELECT a.*
        FROM (
            SELECT userid
            FROM webineh_user_buys
            GROUP BY userid
            HAVING SUM(amount) >= 1000
            ) AS buys_d
        JOIN
            webineh_prefix_nodes_paths AS a
        ON a.descendant_id = buys_d.userid
        JOIN (
            SELECT userid
            FROM webineh_user_buys
            GROUP BY userid
            HAVING SUM(amount) >= 1000
            ) AS buys_a
        ON a.ancestor_id = buys_a.userid
        ) tmp2
    GROUP BY descendant_id
    ) td
ON a.descendant_id = td.descendant_id
       AND a.path_length = CASE td.min_path WHEN 1000 THEN 0 ELSE td.min_path END ;


This is made very simple by the index usage DMV:  sys.dm_db_index_usage_stats

For example, this query I use very often to identify unused indexes:

SELECT o.name Object_Name,
       i.name Index_name,
       i.Type_Desc
 FROM sys.objects AS o
     JOIN sys.indexes AS i
 ON o.object_id = i.object_id
  LEFT OUTER JOIN
  sys.dm_db_index_usage_stats AS s
 ON i.object_id = s.object_id
  AND i.index_id = s.index_id
 WHERE  o.type = 'u'
-- Clustered and Non-Clustered indexes
  AND i.type IN (1, 2)
-- Indexes without stats
  AND (s.index_id IS NULL) OR
-- Indexes that have been updated by not used
      (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );

That query is taken verbatim from sql-server-performance.com but because their blogging platform puts in em-dashes and smart quotes it renders the code on their site un-copy-pasteable. (Why? Why on a code blog??) So I put it here in a form you can use without having to resolve syntax errors.

All due credit to them for this and many other useful code snippets I rely on.


Multiple named Instances and Availability Groups

Your proposed setup looks okay to me; as long as both nodes are in the same domain, that should work fine.

What you need to resolve is the ability for each server to get to the other on the configured ports. It sounds like 5023 is being blocked by a firewall rule of some kind, or that you haven't used the same port consistently throughout the configuration of an availability group.

I've come across the error you're showing and in my case it was actually an encryption issue. Please check out the following posts in detail:

http://sqlperformance.com/2013/04/system-configuration/troubleshooting-alwayson

http://sqlperformance.com/2012/10/system-configuration/dedicated-network-availability-group


SQL Server 2008: What are the English Word Breakers?

The per language noise list (also referred to as stop list) and thesaurus files are in the MSSQL/FTData/ folder. The noise files are plain text, the thesaurus XML, so both can be inspected or changed easily.

Configuring Full-Text Linguistic Components in BOL has the details.


SQL sort by sum of two different where clauses in a join

i would be tempted to write something like below to pivot sum.

Query

SELECT
   table1.Name
 , SUM(CASE WHEN table2.Values = 1 THEN 1 ELSE 0 END) AS "s1"
 , SUM(CASE WHEN table2.Values = 2 THEN 1 ELSE 0 END) AS "s2"
 , SUM(CASE WHEN table2.Values = 3 THEN 1 ELSE 0 END) AS "s3"
FROM
 table1
INNER JOIN
 table2
ON
 table1.ID = table2.table1ID
GROUP BY
 table1.Name


AlwaysOn High Availability doesn't work , can't activate it

You need Enterprise edition for fully functional Always ON AG and a minimum Standard edition to use the basic Always On AG. Express edition of SQL Server does not supports Availability Groups


The only way I know to do that is to leverage this limitation:

  Tables published for transactional replication must have a primary
  key.

Publish Data and Database Objects

So replace the table's primary key with a unique clustered index called pk_MyTable_PreventReplication or somesuch.

Merge replication requires a special ROWGUIDCOL column on the table, which you can prevent with security or a DDL trigger.


To completely remove FILESTREAM features from a database, you need to perform the following steps.

Delete all FILESTREAM columns from all tables
Disassociate tables from FILESTREAM filegroups
Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them)
Remove all FILESTREAM filegroup (there may be more than one of them)

Walkthrough Click Here and follow his steps

Another Note: if you have Data in your FileStream file you will not able able to delete it. sp_filestream_force_garbage_collection unfortunately this only works >= SQL Server 2012
For that fix you can See the Whole Thing Here

If that doesn't work. try to make a backup for it.
That is how I cleared my FileStream file.

When it is Cleared then you can Delete it FileStream File and FILESTREAM filegroup.

If your database in heavily indexed, you should check you Functions and View, some might go missing. (Have a Back up of that)


Will Temporal Tables be included in the Standard Edition of SQL Server 2016?

Community Wiki answer generated from question comments. Feel free to edit it to improve it.

  Anthony Faull: The Temporal Tables feature is available in all editions of SQL Server 2016 including Standard Edition, Web Edition and Express Edition. It is not limited to Enterprise Edition.