The notation that consists of sticking the letters "P" for Primary, "K" for Key and "F" for foreign key, in front of attribute names, is crippled, flawed and confusing.

It is crippled because it suggests that "foreign keys" are keys in a sense similar to "primary keys" and/or "candidate keys".  They are not.

It is also crippled because the notation tends to suggest/instill the belief in people's minds that there is somehow an important distinction between "primary" keys and "non-primary" keys.  There is not.

It is flawed because it neglects the notion that any attribute can be part of any number of keys.  The notation typically allows for an attribute to participate in at most "the" "primary" key and only one other "non-primary" key.

It is confusing because it is both crippled and flawed.  Here are the facts about keys :

Foreign keys are not "keys".  The term is a blast from the past, and a gross misnomer.  They denote an inclusion dependency from the table that the "foreign key" is on, to some other table.  There can be any number of "foreign keys" on a table, to any number of other tables, and it is even a fact that the very same set of attributes can be a foreign key twice, or thrice, or any number of times (perhaps to just as many distinct other tables).  Heck, the very same set of attributes can even be a "foreign key" twice or more to the very same table (with a different mapping of from/to attributes).  Anyhow, any attribute can participate in any number of "foreign key"s.

Primary keys are just keys.  There is nothing special about them, in comparison with "non-primary" keys.  All of them denote nothing more than just a rule that for the set of attributes they consist of, the combinations of values that appear for these attributes, in the table, must be unique.  The fact that most SQL engines force you to declare a primary key, is, once again, a sad and sorry blast from the past.  The idea to single out one key and declare that one "more important than others" is merely psychological.  It might suggest that this is the preferred key to use for the table at hand.  Don't make the mistake of thinking that this constitutes an obligation for other database designers to reference only this key in what tables they design.

Brief : there can be any number of "foreign keys", and any attribute can participate in any of them.  There can be any number of keys, and any attribute can participate in any of them.

A notation or a tool that respects these facts, will at the very least allow to identify each key properly (e.g. using labels such as "K1", "K2", ..., "F1", "F2", ...), and allow for each attribute to be accompanied by more than one of these labels (e.g. "K1,K2,F2").

Furthermore, with respect to "foreign keys" specifically, such a tool will also allow to document the exact and complete nature of the "foreign key" : that is, it will also allow to identify the target table of the foreign key and it will also allow to specify/identify what the mapping is like between the attributes of the "source" and the "target" table.


How to sum a value in a grouped set of results? SQL Server

Try something like this:

Select debit_id, transfer_id, sum(amount) as "Amount_Sum"
from Transfer_debits
where transfer_id = 861288
group by  debit_id, transfer_id

For the debit_type, if you want a different sum depending on its value, just add it to the select and the group by.


What is the best way to stage data in SSIS?

I think it is better to pass values as a Table Valued Parameter (TVP).

TVPs can efficiently pass large amounts of data.

This is efficient because the client API internally bulk inserts the rows into tempdb before executing the procedure. And you don't need to load all the data into memory on the client.

You can also take a look at this helpful article to learn more on TVP performance:

Maximizing Performance with Table-Valued Parameters


How to selectively replicate a PostgreSQL database

Postgres built-in replication is based on log shipping, and as far as I know does not support this (you're shipping the Postgres transaction log from the master to the slaves, so everything - including deletes - gets passed along. You may be able to approximate it by keeping all the transaction logs (forever) so you can roll back to a specific point in time, but that's not a particularly efficient technique.

You can probably rig up something like this with trigger-based replication mechanisms like Slony, but a better solution might be a standard mirror plus the table auditing (tablelog) project which basically creates a historical log of what happened to a table (in a shadow schema) so you can see what was done when, and by whom.
This is best combined with 9.x-style master/slave replication, where you can read from the slaves to poke around in the audit history.


PostgreSQL 10.6: Alter parent table will block ALL children?

Adding a column to the inheritance parent will only be completed once all children are modified. So yes, you will have to wait until all 74000 tables have been modified, and there will be an ACCESS EXCLUSIVE lock on all tables at the same time.

I hope you have set max_locks_per_transaction high enough that the locking table can hold all these locks. Having 74000 tables in one database is a problem, and having them all in an inheritance hierarchy is an even bigger problem. But that problem is with the database design.


Easy, with MariaDB.

mysql_install_db.exe --service=service_name --datadir=datadir --port=port

Run this multiple times with  different parameters (service_name, datadir, port should b different for different instances). Start services with "net start " .
This is supported official tool. Documentation is here https://mariadb.com/kb/en/mariadb/mysql_install_dbexe/ . You do not need any mysql_multi, really :)


ALTER COLUMN NOT NULL, not enough space in the log file

you might find it faster to create a new table, truncate the old, and then re-insert:
select t.*
into temp_my_table
from [dbo].[table_name] t;

truncate table [dbo].[table_name];  -- back it up first!

alter table [dbo].[table_name] alter column [Value] [float] NOT NULL

insert into [dbo].[table_name]
select t.*
from temp_my_table t;

You may have to pay attention if you have insert triggers or identity columns.


Tune Performance by putting large tables into the KEEP Pool

You can put the table in KEEP Pool cache and this may have a positive impact on your queries. I have implemented it in my system for a 16 GB partition and we did get good performance gains.

However putting a table in cache does not mean that its data is loaded into memory automatically.
It only means that since we are using a separate buffer pool for these tables, the data blocks that are loaded into memory are not aged out from the buffer as frequently as they would have if you were using the default buffer pool.

So in case your queries are fetching same data blocks repeatedly there will be lesser aging out and better performance gains. However, in case your queries are fetching different data blocks which are not present in the cache then you will be having some disk reads happening and old blocks may get aged out of bufer.

Also, have you evaluated RESULT_CACHE hint for yor query, in case you are not using GTTs you may be able to get good performance gains from that also.


How to alter primary key constraint for 2 columns in same table in SQL Server?

Don't enter this into the check constraint dialogue box. That only allows you to enter expressions for check constraints.

To create your primary key constraint you should just execute your TSQL ALTER TABLE script in a new query window.

To create this using the visual tools you would need to use the indexes/keys dialog or in the table designer view select the two columns and choose "Set Primary Key" from the shortcut menu.

I would avoid this last approach for a composite PK however as AFAIK it gives you no way of controlling the order that the index keys will be defined in.


What does [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'WIN-443PT0LULQU\Administrator', error code 0x534

Okay - after struggling with this for a couple of days (I had tried posting variations on this questions to stackoverflow and to superuser), it seems to be that the SQL Instance had the wrong machine name stored in master - so I had to change that to the correct current machine, and ensure that the correct admin account was enabled as an sa account.

This mostly got me going:
http://www.sqlservercentral.com/articles/SQLServer/135372/

I had to do run these to figure out that the instance had the wrong servername, then to drop the bad one, then to add the correct one:

use master
select SERVERPROPERTY('servername')
select @@SERVERNAME
select SERVERPROPERTY('machinename')
go

use master
exec sp_dropserver "WIN-443PT0LULQU\SOURCEGEARVAULT"
go

use master
exec sp_addserver "BUILD-SERVER","local"
go

I also had to mess around with logins - but I cannot say for sure what I did exactly that ended up working.  I renamed "WIN-443PT0LULQU\Administrator" to "BUILD-SERVER\Administrator" at one point, and after doing the above, I had to restart the instance.

After that, things seem to be working properly, and I am able to execute my maintenance plan successfully.


Centralized master data shared among other several DBs

If your environment is currently heterogeneous or you're moving in that direction already, look into using an enterprise service bus to abstract away the implementations of each database. If it isn't, then I would advise against doing so unless absolutely necessary -- this will complicate your environment a lot, especially as you start adding in HA/DR solutions.

Replication should work for SQL Server. I'm not 100% sure of the exact supported features for heterogeneous replication, nor are your requirements very specific in the question, so I can't comment on that.

Master Data Services is a SQL Server only solution (introduced in 2008 R2, which is higher than the version you've tagged the question with), and beyond that I'm not familiar enough with it to say if it would work for your situation.


Single slave - multiple master MySQL replication

As of October 2013 MySQL (> 5.7.6) supports multi-source replication.

MySQL Multi-Source Replication in MySQL Reference Manual

http://www.mysqlperformanceblog.com/2013/10/02/mysql-5-7-multi-source-replication/

http://on-mysql-replication.blogspot.com/2013/09/feature-preview-mysql-multi-source-replication.html


How to enable windows authentication on server that is not part of domain

Plan answer: No.

Let me explain:

Windows Authentication is just that, authenticating with a Window's login. So if you are dealing with a workgroup computer where you are on a domain it will not work, because that server can only authenticate a local account.

If you are talking about connecting with SSMS, you can try the runas coomand through command prompt and use the "netonly" parameter. I use this for connecting from workgroup to domain.


Backup status report from multiple server in sql server 2005

It is redundant, but i have put this on every server and had it send me an email.  Most third party backup tools put a value in "Description", or you can tell based on
the name or user name.

select
    bs.name backup_name,
    backup_finish_date,
    bs.database_name,
    bs.backup_size ,
    cast(bs.backup_size/1024/1024 as int) 'Backup Size MB',
    cast(bs.backup_size/1024/1024/1024 as int) 'backup size gb',
    bs.compatibility_level,
    bs.Recovery_Model,
    BackupType =
        CASE
            WHEN bs.type = 'L' THEN 'Log'
            WHEN bs.type = 'D' THEN 'Full'
            WHEN bs.type = 'I' THEN 'Differential'
            WHEN bs.type = 'G' THEN 'File Differential'
            WHEN bs.type = 'P' THEN 'Partial'
            WHEN bs.type = 'Q' THEN 'Partial Differential'
        END,
        [user_name],
        [description],
        cast(bs.compressed_backup_size/1024/1024 as int) 'Compressed Backup Size MB',
    cast(bs.compressed_backup_size/1024/1024/1024 as int) 'Compressed Backup Size gb'

from
backupset bs

where bs.backup_finish_date > '12/01/2014'
--and

--bs.database_name in('')
--either put a list of databases you care about, or do a select from
--sys.databases...can change the logic to reflect dbs what haven't been backed up

order by bs.backup_finish_date desc


How to purge old files from pg_xlogs directory

Firstly PG 8.4 is no longer supported (since July 2014), you should move your databases to a supported version as soon as possible.

The WAL configuration settings for the 8.4 version are documented here:

https://www.postgresql.org/docs/8.4/static/runtime-config-wal.html

In particular checkpoint_segments control the number of WAL files.

You must not delete WAL files, either manually or with a cron job or with archive_command. This would definitely corrupt your database.

WAL files may grow in number if archive_command is not working or lagging behind.


After days of debugging, one little setting made all the difference.

If you get this error mentioned in OP, please check the owner of the DB-role used! It wasn't set to dbo in my case. Changing that made it work - see comments for history!


Detect circular relations in a distribution table

Following @ypercube comment to the question, I came up with this solution:

with recursive A as (
    select code1, code2 from B
    where code1 = new.code2
    union all
    select a2.code1, a2.code2 from B a2
    join A on a2.code1 = A.code2
)

select * from A
where code2 = new.code1

If any records are returned, then new.code2 references new.code1 and there would be circular reference if this new record was inserted.

For Firebird specifically, this is what I used:

CREATE EXCEPTION EX1 'circular reference exception';

SET TERM ^ ;

CREATE TRIGGER TRIGGER_B_CIRCULAR FOR B
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
DECLARE CODE char(5);
BEGIN
    FOR with recursive A as (
            SELECT CODE1, CODE2 FROM B
            WHERE CODE1 = new.CODE2
            UNION ALL
            SELECT a2.CODE1, a2.CODE2 FROM B a2
            JOIN A ON a2.CODE1 = A.CODE2
        )

        SELECT CODE2 FROM A WHERE CODE2 = new.CODE1
        INTO :CODE DO
            exception EX1;
END^

SET TERM ; ^


Registered Servers without the ability to connect to Multiple Servers at once

There is no way to avoid that once you share CMS.
Just be aware that it only stores the list of instances of SQL Servers available, but not necessarily everybody has access to connect in every single instance.

Because the connections that are maintained by a central management server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.


How "frequently used data" and cache work in databases

Caching with these DBMS is at the block/page level. Data must be in memory to be used. The high-level process is that the DBMS first looks in cache, and if not present, reads the needed blocks/pages into memory for use. Implementation details vary greatly by DBMS but generally buffers are reused by a LRU algorithm with the intent to keep hot data in cache and evict less frequently used data so that the greatest benefit is realized by limited memory resources.

To address performance, perform query and index tuning so that the minimal amount of data must be retrieved from storage and add as much memory as you can for caching.


Is there any risk associated with stopping mysqldump incomplete?

You should be able to stop mysqldump by pressing control-c... or if it's running in the background, by killing the mysqldump process.

The server will realize almost immediately that the client connection mysqldump was holding has gone away and will release any table locks (held by mysqldump, if any) automatically.

This should be a completely safe operation.


alter primary key and all references in sql server

The SSMS table designer will generate this script for you.  Just make the change in the designer and generate the change script.

There's a a safety config you have to set before the designer will generate scripts that drop and recreate tables.  That should be done on production servers only after testing.


SQL Server 2016: Adding temporal tables to existing application

Is it possible to add temporal tables to 2-3 key tables in the
  application, without changing the PHP code?

You should be able to achieve that goal (assuming the application is written in a way that adding additional columns to the table doesn't cause a problem).  Of course, test, test, test.

Using the example code provided in Greg Larsen's post
Altering an Existing Table to Support Temporal Data, let's work through an example.

Here is our current table.

DROP TABLE IF EXISTS dbo.Product
DROP TABLE IF EXISTS dbo.ProductHistory

CREATE TABLE dbo.Product (
    ID INT Identity
    ,ProductName VARCHAR(50)
    ,ProductPrice DECIMAL(20, 2)
    );

INSERT INTO dbo.Product (ProductName, ProductPrice)
VALUES ('Widget',33.49),('Doo-Hickey',21.76),('Thing-A-Ma-Jig',20.16);

SELECT * FROM dbo.Product

| ID | ProductName    | ProductPrice |
|----|----------------|--------------|
| 1  | Widget         | 33.49        |
| 2  | Doo-Hickey     | 21.76        |
| 3  | Thing-A-Ma-Jig | 20.16        |

We now want to track versions.

  In order to start collecting historical information for my dbo.Product
  table I will need to alter the table so it will support temporal data.
  A SQL Server 2016 temporal table requires a table to have a primary
  key and a couple for date/time columns.  The two date/time columns
  will be used to determine the period of time for when the record is
  valid.   Therefore the first thing I need to do is alter my Product
  table to meet the temporal data table requirements.  To do that I will
  run the following code:

ALTER TABLE dbo.Product
    ADD CONSTRAINT PK_ProductID PRIMARY KEY (ID),
    BeginDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL
        DEFAULT SYSUTCDATETIME()
,
    EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL
        DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
    PERIOD FOR SYSTEM_TIME (BeginDate,EndDate);

There is some very good information in Creating a System-Versioned Temporal Table that addresses Altering Non-Temporal Table to be System-Versioned Temporal Table.

  When converting an existing table, consider using the HIDDEN clause to
  hide the new PERIOD columns to avoid impacting existing applications
  that are not designed to handle new columns.

  The next step in setting up a temporal table is to identify a history
  table that goes along with my dbo.Product table.  To do that I will
  run the following code:

ALTER TABLE dbo.Product
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

Now the Product table looks like this

| ProductName    | ProductPrice | BeginDate                   | EndDate                     |
|----------------|--------------|-----------------------------|-----------------------------|
| Widget         | 33.49        | 2017-09-29 14:28:04.8327739 | 9999-12-31 23:59:59.9999999 |
| Doo-Hickey     | 21.76        | 2017-09-29 14:17:55.7149252 | 9999-12-31 23:59:59.9999999 |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:17:55.7149252 | 9999-12-31 23:59:59.9999999 |

You also find that a new ProductHistory table has been created that looks just like the Product table.  The ProductHistory table is currently empty because we haven't updated or deleted anything from the Product table

Now, let's run an update to generate some history

UPDATE dbo.Product
SET ProductPrice = 34.65
WHERE ProductName = 'Widget';

select * from dbo.Product
select * from dbo.Producthistory

The product table has the current data

| ProductName    | ProductPrice | BeginDate                   | EndDate                     |
|----------------|--------------|-----------------------------|-----------------------------|
| Widget         | 34.65        | 2017-09-29 14:35:49.7943164 | 9999-12-31 23:59:59.9999999 |
| Doo-Hickey     | 21.76        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 |

And now the ProductHistory table has a versioned row

| ProductName | ProductPrice | BeginDate                   | EndDate                     |
|-------------|--------------|-----------------------------|-----------------------------|
| Widget      | 33.49        | 2017-09-29 14:35:41.8079586 | 2017-09-29 14:35:49.7943164 |

You can even add a column to the Product table and the ProductHistory table automatically gets the new column added as well

ALTER TABLE dbo.Product ADD ProductColor VARCHAR(10)

Product

| ProductName    | ProductPrice | BeginDate                   | EndDate                     | ProductColor |
|----------------|--------------|-----------------------------|-----------------------------|--------------|
| Widget         | 34.65        | 2017-09-29 14:35:49.7943164 | 9999-12-31 23:59:59.9999999 | NULL         |
| Doo-Hickey     | 21.76        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 | NULL         |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 | NULL         |

ProductHistory

| ProductName | ProductPrice | BeginDate                   | EndDate                     | ProductColor |
|-------------|--------------|-----------------------------|-----------------------------|--------------|
| Widget      | 33.49        | 2017-09-29 14:35:41.8079586 | 2017-09-29 14:35:49.7943164 | NULL         |

  Are there risks of compatibility or performances?

You would certainly need to test this in a development environment first.  You may have unique situations that have an impact on your decision to use Temporal tables.

Here are a few important links with additional information regarding  Temporal tables

Getting Started with System-Versioned Temporal Tables

Temporal Table Considerations and Limitations

Manage Retention of Historical Data in System-Versioned Temporal Tables

Stopping System-Versioning on a System-Versioned Temporal Table


What does the "Edit top 200 Rows" SSMS option actually do?

The two types of query here use different grids.

You selecting the data (presumably in Query Analyzer) shows a grid that is optimized for display.
Him selecting the data with "edit top xxx rows" displays a grid that is optimized for editing directly in the grid.  This gives it more overhead and will make it slower.


Getting the entire records from Two tables for Count purposes SQL

If there's no relationship between them...

Table A is customers and Table B is employees, and you really want to know how many bodies were involved with a production of Spamalot! I would look at a query like

SELECT
    A.RC + B.RC AS TotalBodies
FROM
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.A A
) A
CROSS JOIN
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.B B
) B;

Read that as Add the RC column from A to the same column in B. A and B are derived in line and will only ever have one value returned as we're computing the scalar count of all the rows.

I'm using a CROSS JOIN because I need everything from both sets. I could have done this as a UNION ALL between the counts. The ALL is important, otherwise UNION eliminates duplicate rows. A variant on B would be to use a COUNT in the outer query and just select all the IDs. Assuming the data types match, it'd work either way.

-- Option B

SELECT
    SUM(D.RC) AS TotalBodies
FROM
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.A A
    UNION ALL
    SELECT COUNT_BIG(*) AS RC FROM dbo.B B
) D(RC);

or just done it inline from the SELECT

-- Option C
SELECT (SELECT COUNT_BIG(*) AS RC FROM dbo.A A) + (SELECT COUNT_BIG(*) AS RC FROM dbo.B B) AS TotalBodies;

Some of that may be database dependent but works fine for SQL Server.

SQLFiddle


Will Entity Framework and/or Linq-To-SQL queries show up as "Adhoc Queries" in SSMS' "Server Dashboard" Report?

Yes. Linq-To-Sql and EF submit dynamic (Ad-Hoc) queries to the database. You can prove this by running a profiler trace and debugging the app.

The good news is that you'll always get the most up-to-date query plan, based on current statistics. The bad news is that the plan will have to be compiled on each execution. This is rarely good, and only when the query commonly returns varying amounts of data.

I see this every day. Developer Bob is more concerned with RAD (kicking the app out the door and looking like a rock star) than app performance. He uses this new, bleeding edge, auto-magical sql generation code tool. Queries are fast from the app because there's only 2500 records in the database and everything fits in memory. 12 months down the road, app volume has went up 100000 %, it's now super dog slow, and the db gets blamed. The solution is to either re-write the app with proper sql or throw hardware at the problem. In my opinion, auto-magical sql insures that your' app will not scale well with significant volume.

"Dynamic code generation is the fastest way to build slow SQL and the slowest way to build fast SQL. " Brent Ozar

Your biggest challenge is not in compiled code speed. It's in your ability to efficiently read and parse datasets which do not fit in memory.


Temporary tables created on disk: is huge

You "can't tune your way out of a performance problem".  So let's look for a villain and fix it.

Change long_query_time down to 1.  Wait a day.  Then run pt-query-digest.

More: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

A query that is running often or long will show up in the slowlog; let's look at the first couple of them.

A likely candidate is postmeta:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

After edit

First query:

IN ( SELECT ... ) is notoriously inefficient.  Change to a JOIN .. ON.

And change the index(es) as indicated in the indexing link above.

Is LIMIT  29999 realistic?  Suggest lowering that significantly.

Second query:

INDEX(post_type, post_date)


SQL aliasing to redirect traffic between databases on the same server

Aliases are only for server/instances of SQL Server, not for databases.

In your case I'd create a database snapshot of the current database (so there's no need for a new read-only copy of your db), redirect all user connections there, do you update, then drop the snapshot. Please take into consideration that a snapshot can take a lot of space if forgotten there, not dropped on time.


Count the total occurences for each day in given dates

Here's a set-based approach that uses an Common Table Expression (CTE) to generate a quick Date Dimension table and then we use the CTE to group by date

The answer is the same as @nates

Declare @myDates TABLE  (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into @myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')
;

WITH dates ([Date])
AS (
    SELECT convert(DATE, '2017-01-01') AS [Date] -- Put the start date here

    UNION ALL

    SELECT dateadd(day, 1, [Date])
    FROM dates
    WHERE [Date] <= '2020-12-31' -- Put the end date here
    )
SELECT [Date]
    ,count(*)
FROM dates d
JOIN @myDates m ON d.[Date] >= m.startDate
    AND d.[Date] <= m.endDate
GROUP BY [Date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!


sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows

Setting the ORACLE_HOME environment variable incorrectly may cause these errors.

On Linux, you can set ORACLE_HOME as $ export ORACLE_HOME=path\to\oracle\home and on Windows you can set as C:\>set ORACLE_HOME=/path/to/oracle/home/.


User guest does not have permission to run DBCC checkprimaryfile (SMSS)

Have you seen this post on the MSDN forums?

User guest does not have permission to run DBCC checkprimaryfile

One possible solution proposed in that thread is to run SSMS as an administrator.

The other option is to make sure that the user account you're using to the connect to SQL Server:

has a login setup on the instance
has the appropriate permissions for attaching a database (CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE)


Create constraint for stopping mass update to a table

One way to implement this would be with a trigger updating a temporary table in parallel (that is only visible inside the same session).

Create the temp table with ON COMMIT DROP, so that it lives for the duration of a transaction. This way, all updates during the same transaction count against the maximum.

Demo table:

CREATE TABLE foo(id serial, txt text)

Trigger function:

CREATE OR REPLACE FUNCTION trg_foo_maxup()
  RETURNS trigger AS
$func$
DECLARE
  _ct int;
BEGIN

IF EXISTS (
   SELECT 1 FROM pg_catalog.pg_class
   WHERE  relname = 'foo_maxup'
   AND    relnamespace = pg_my_temp_schema()
   AND    relkind = 'r'
   ) THEN

   UPDATE foo_maxup SET ct = ct + 1
   RETURNING ct INTO _ct;

   IF _ct > 5 THEN           -- your maximum here
      RAISE EXCEPTION 'Cannot update more than 5 rows in one transaction!';
   END IF;

ELSE
   CREATE TEMP TABLE foo_maxup ON COMMIT DROP AS
   SELECT 1 AS ct;
END IF;

RETURN NEW;

END
$func$
  LANGUAGE plpgsql VOLATILE SET search_path = pg_temp;

Trigger:

CREATE TRIGGER maxup
BEFORE UPDATE ON foo
FOR EACH ROW
EXECUTE PROCEDURE trg_foo_maxup();

If you want a maximum for the whole session, just omit ON COMMIT DROP.

I added SET search_path = pg_temp to the function definition to prevent mischief with the search_path. This trigger only sees temporary tables in its search_path. That's why pg_catalog.pg_class has to be table-qualified.

-> SQLfiddle demo.


MySQL - Enabling Scheduled Event on Master and Slave Simultaneously

The only way I have found to do this so far is by using:

On the Master B:

SET SQL_LOG_BIN=0;
ALTER DEFINER=user@localhost EVENT e_test ENABLE;
SET SQL_LOG_BIN=1;

This works, but as soon as I alter the Event on Master A, Master B goes back to Slaveside_disabled and I have to repeat this process. So not perfect by any means, as it relies on us remembering to do this for each event, if we Alter them.


Environment:

[oracle@o61 ~]$ ls -l /home/oracle/myscript*
-rwxr-xr-x. 1 oracle oinstall 44 Mar 25 17:38 /home/oracle/myscript.sh
[oracle@o61 ~]$ cat /home/oracle/myscript.sh
#!/bin/bash
touch /home/oracle/myscript.out
[oracle@o61 ~]$

[oracle@o61 ~]$ ls -l $ORACLE_HOME/bin/extjob*
-rwsr-x---. 1 root   oinstall 1254574 Nov  1 21:01 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjob
-rwx------. 1 oracle oinstall 1254574 Nov  1 21:01 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjobo
-rwsr-x---. 1 root   oinstall 1254292 Oct 19 14:54 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjobO
-rw-------. 1 oracle oinstall 1254292 Oct 19 14:54 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjoboO

[oracle@o61 ~]$ grep -v "^#" $ORACLE_HOME/rdbms/admin/externaljob.ora

run_user = oracle
run_group = oinstall

Then create a job that is executed once then dropped:

[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 25 17:42:13 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

begin
  dbms_scheduler.create_job
  (
    job_name => 'RUN_SCRIPT',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/myscript.sh',
enabled => true,
auto_drop => true
  );
end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@o61 ~]$ ls -l /home/oracle/myscript.*
-rw-r--r--. 1 oracle oinstall  0 Mar 25 17:42 /home/oracle/myscript.out
-rwxr-xr-x. 1 oracle oinstall 44 Mar 25 17:38 /home/oracle/myscript.sh
[oracle@o61 ~]$


Query function won't take rows with given default value

If you want to display the Notes that do not have Tags you have to replace the left outer join for right outer join since you want to keep all the registers from the right table of the join.

So if the rest of your query is correct this one should be what you are looking for

select n.id, n.created_at, n.title, n.body,
case count(t.name) when 0 then '{}' else array_agg(t.name) end as noteTags
from notes_tags nt
inner join tags t on t.id = nt.tag_id
right outer join notes n on n.id = nt.note_id
where n.user_id = userId and
  case
    when tagFilter = 'all' or tagFilter = '' then t.name != ''
        else t.name = tagFilter
    end
group by n.created_at, n.title, n.body, n.id
order by n.created_at DESC
limit note_limit;


Generic or better yet custom message of the day in Oracle sql plus

Anything like this?

$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
set serveroutput on feedback off
exec dbms_output.put_line('******************');
exec dbms_output.put_line('MESSAGE OF THE DAY');
exec dbms_output.put_line('******************');
set serveroutput off feedback on

Logging in:

$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 26 00:49:29 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

******************
MESSAGE OF THE DAY
******************
SQL>


Few Insert queries rollbacked due to system crash

MyISAM does not support transactions, commits, or rollbacks. You have to switch to the InnoDB.
InnoDB Architecture

InnoDB has mechanisms, log files, and associative memory structures for rollback, commit, transaction isolation, and MVCC.
MyISAM has no infrastructure like this. Each MyISAM table is its own entity.
Here is a great reason to switch to InnoDB: MyISAM does not cache data. It lets the OS do that. That's a scary thought for MySQL/Linux, and horrific nightmare for MySQL/Windows. I wrote about this difference in the past : What are the main differences between InnoDB and MyISAM?
Thus, in the event of a crash, not only do you lose any data the OS did not flush to disk, but you also may have to run CHECK TABLE/REPAIR TABLE to fix open file counts in the headers of the MyISAM tables affected.
In your case, you are playing Russian Roulette relying on data from a MyISAM tables after a server crash or a mysqld crash.
Please see my past posts on using and tuning InnoDB

When to switch from MyISAM to InnoDB?
Which first: upgrade mysql version or convert storage engine? (Code Provided to Convert to InnoDB)

Give it a Try !!!
UPDATE 2014-01-03 10:29 EST
In your comment, you asked

is it possible that data that was inserted 2-3 minutes before crash also lost in MyISAM?

Absolutely !!! I actually saw this happen three ago. I create a MyISAM table in /dev/shm (Shared Memory). I loaded it with a 3GB .MYD and 2GB .MYI. While loading it, I ran ls -l on the table and it kept saying 0 bytes for .MYD and 1024 bytes for .MYI. After 5 min, I killed the mass INSERT. Suddenly, data and index pages started to pour into the table files. Had mysqld actually crashed, those data and index pages would never have made it to the table. In your case, this would especially be true for all open MyISAM tables in a write-heavy database environment.


PL/SQL: best way to count elements in an array?

I think this is what you're after:

V_COUNT := MY_ARRAY.COUNT;


How do I replicate Mysql DB server to new server?

If you are comfortable with a maintenance window, here is what you can do:

Step 01) You need to add this line to my.cnf on Server1

[mysqld]
log-bin-mysql-bin
expire-logs-days=7

Step 02) Restart mysql so that no one else can login to Server1

service mysql restart --skip-networking

Step 03) Dump everything and gzip it

DUMPFILE=/root/MySQLData.sql.gz
mysqldump --master-data=2 --single-transaction --flush-privileges -uroot -p --routines --triggers --all-databases | gzip > ${DUMPFILE}

Step 04) Restart mysql on Server1 to allow normal connectivity

service mysql restart

Step 05) Move the ZIP file to the new server (Server2) in /root

Step 06) Unzip the file on Server2

gzip -d /root/MySQLData.sql

Step 07) Login into mysql on Server2

mysql -uroot -p

Step 08) Load the data from the mysql prompt on Server2

mysql> source /root/MySQLData.sql

That's it. That makes all the data as of that dump available on Server2

If you wanted to establish MySQL Replication so that everything written on the Master immediately gets done on the Slave, the steps are a little different. For this example, you will need the Private IP address of Server1. You can get that with ip addr show. Let's suppose the Private IP address of Server1 is 10.1.2.20.

Step 01) You need to add this line to my.cnf on Server1

[mysqld]
log-bin-mysql-bin
expire-logs-days=7

Step 02) Restart mysql so that no one else can login to Server1

service mysql restart --skip-networking

Step 03) Create a Replication Username in mysql client:

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repluser@'10.1.2.20' IDENTIFIED BY 'replpass';

Step 04) Dump everything and gzip it

DUMPFILE=/root/MySQLData.sql.gz
mysqldump --master-data=1 --single-transaction --flush-privileges -uroot -p --routines --triggers --all-databases | gzip > ${DUMPFILE}

Step 05) Restart mysql on Server1 to allow normal connectivity

service mysql restart

Step 06) Move the ZIP file to the new server (Server2) in /root

Step 07) Unzip the file on Server2

gzip -d /root/MySQLData.sql

Step 08) Login into mysql on Server2

mysql -uroot -p

Step 09) Setup replication in mysql client:

CHANGE MASTER TO
MASTER_HOST='10.1.2.20',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000000',
MASTER_LOG_POS=2;

Don't worry about log file and position. They are bogus. The mysqldump has the correct log file and position at line 22. In order to see run this:

head -22 source /root/MySQLData.sql | tail -1

Step 10) Load the data from the mysql prompt on Server2

mysql> source /root/MySQLData.sql

Step 11) Start replication

START SLAVE; SELECT SLEEP(3); SHOW SLAVE STATUS\G

If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, you are done.


How does Apache Cassandra handle dynamic column set conflicts?

The best way to think of a cassandra database is not as a set of databases on different nodes, but as one single database. Adding the column to the first node adds it to all other nodes. The actual number of times your data is written is determined by your replication strategy and replication factor, but each copy of the data will be the same.

Thus, if you tell the first node about a new column, the second node will automatically understand it and be able to access that data. If you tell the second node about different data in the same column, it will either overwrite the old data or add new data, depending on whether the row you're writing into already has data in it.

If you're adding a new column to the second node, then all the data in that column and the data in the first column will simultaneously exist and can be queried through either/any node.


How to turn JSON array into Postgres array?

Postgres 9.4 or newer

Obviously inspired by this post, Postgres 9.4 added the missing function(s):
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!

json_array_elements_text(json)
jsonb_array_elements_text(jsonb)

To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() to build a text string.

Aggregate unnested elements per row in a LATERAL or correlated subquery. Then original order is preserved and we don't need ORDER BY, GROUP BY or even a unique key in the outer query. See:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Replace 'json' with 'jsonb' for jsonb in all following SQL code.

SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;

Short syntax:

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?

ARRAY constructor in correlated subquery:

SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

Related:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Subtle difference: null elements are preserved in actual arrays. This is not possible in the above queries producing a text string, which cannot contain null values. The true representation is an array.

Function wrapper

For repeated use, to make this even simpler, encapsulate the logic in a function:

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow it in index expressions.
Make it PARALLEL SAFE (in Postgres 9.6 or later!) to not stand in the way of parallelism. See:

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

Call:

SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM   tbl;

db<>fiddle here

Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Retrieve a single row from tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings form correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

ARRAY constructor:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

Original (outdated) SQL Fiddle.
db<>fiddle here.

Related:

Need to select a JSON array element dynamically from a postgresql table

Notes (outdated since pg 9.4)

We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar JSON value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...


How do long columns impact performance and disk usage?

The specific answer to your question (at least for Oracle and probably other databases) is that the length of the field doesn’t matter, only the length of the data.  However, this shouldn’t be used as a determining factor concerning whether to set the field to its maximum allowable length or not.  Here are some other issues you should consider before maxing out field sizes.

Formatting
Any client tool that formats the data based on the size of the fields will require special formatting considerations.  Oracle’s SQL*Plus for example by default displays the maximum size of Varchar2 columns even if the data is only one character long.  Compare…

create table f1 (a varchar2(4000), b varchar2(4000));
create table f2 (a varchar2(5), b varchar2(5));
insert into f1 values ('a','b');
insert into f2 values ('a','b');
select * from f1;
select * from f2;

Bad Data
Field length provides an additional mechanism to catch/prevent bad data.  An interface shouldn’t attempt to insert 3000 characters into a 100 character field, but if that field is defined to be 4000 characters, it just might.  The error woudn’t be caught at the data entry stage, but the system may have trouble further down when another application tries to process the data and chokes.  As an example, if you later decide to index the field in Oracle you would exceed the maximum key length (depending on block size and concatenation).  See…

create index i1 on f1(a);

Memory
If the client application allocates memory using the maximum size, the application would allocate significantly more memory than is necessary.  Special considerations would have to be done to avoid this.

Documentation
The size of the field provides another data point of documentation about the data.  We could call all tables t1, t2, t3, etc. and all fields f1, f2, f3, etc., but by specifying meaningful names we better understand the data.  For example, if an address table for a company with customers in the U.S. has a field called State that is two characters we expect the two character state abbreviation to go in it.  On the other hand if the field is one hundred characters we might expect the full state name to go in the field.

All that being said, it does seem prudent to be prepared for change.  Just because all your product names today fit in 20 characters doesn’t mean they always will.  Don’t go overboard and make it 1000, but do leave room for plausible expansion.


Postgres randomly fails silently on huge CSV import

This ended up being a bug with either Postgres or pgAdmin. The problem is I was getting a success message when the query ended instead of a failure message. That threw me off. After checking the logs, I found the error, and it was easy to solve.


Where do I find the tables that a role has access to in Vertica?

SELECT * FROM grants ORDER BY grantor, grantee;

The privileges_description column shows the privilege types, such as (in your case) SELECT.  (If you only want the SELECTs, you can refine that query.)

Source: Viewing Privileges Granted on Objects in the Admin Guide.  (That's from the documentation for version 7.1.x.)


Can I use the "import" tool on PHPMyAdmin multiple times?

Yes this is possible, although you need to watch out for the following:

a) Export using extended inserts so that the extended SQL syntax is used which will reduce the size of your SQL files (If you are exporting using PHPMyAdmin select both complete and extended insert options, as it creates complete inserts with extended insert syntax for large tables)

b) Try using the GZip format which will compress the SQL files and make them smaller (depending on the hosting provider setup this may be an issue so you need to test it out with a small table)

c) Export the tables in such an order that tables with foreign keys are imported last


How can I relate ‘address’ with other two entity types?

You should not use the same table for entities of the different nature.

In the RDBMS paradygm table represents the type or class. For each specific type/class you have, you should use the separate table. Sure you can mangle the table by bells and whistles like entity_type_id. Sure you can cheat and define your entities in common as "something that have an address(es)".

But the direct and inevitable result of such approach is the complexity and non-obviousness of the queries on that tables. Each time you will be forced to check what exact "subtype" of the entities you deal with. More conditions in the WHEREs, more complex JOINs and their ON's and so forth.

Indeed, there are lot of cases when we are forced to denormalize our databases or to break the ACIDity for sake of performance. But if your primary goal is the clear and comprehensible design - you have to avoid violations of the basic principles. Pretty good explanation you can find in the foundational book "An Introduction to Database Systems" by C.J.Date, part II chapter 5.

As mentioned by Gypsy Spellweaver, you need an auxilliary "pure relationship tables":

+----+-------+-----+    +----+----------+-----+    +----+---------+-----+
| id | Hotel | ... |    | id | Merchant | ... |    | id | Address | ... |
+----+-------+-----+    +----+----------+-----+    +----+---------+-----+

      +----------+------------+    +-------------+------------+
      | Hotel_id | Address_id |    | Merchant_id | Address_id |
      +----------+------------+    +-------------+------------+

Here two bottom tables contains nothing but IDs of corresponding hotels, merchants and addresses.

When you want to know what adresses are associated with some hotel your query is:

SELECT w.*, z.*
  FROM Hotel    AS w
  JOIN H_A      AS q ON q.hotel_id = w.id
  JOIN Address  AS z ON z.id = q.address_id
 WHERE w.Hotel = "Holiday Inn"
;

Query that returns all the hotels and merchants having the same address:

SELECT w.*, m.*, z.*
  FROM M_A       AS s
  JOIN H_A       AS q ON q.address_id = s.address_id
  JOIN Hotel     AS w ON q.hotel_id = w.id
  JOIN Merchants AS m ON s.merchant_id = m.id
  JOIN Address   AS z ON z.id = q.address_id
;


This is a full database import which also includes tablespace definitions. Find out from the original Solaris database what are all the target tablespace names and sizes. Pre-create the tablespaces manually using the datafile paths of your choice (C:\data\) before performing the import. You will see IMP-00015: following statement failed because the object already exists: your tablespace which you can ignore.


Is there any difference between a predicate using a literal value or a parameter value when that value will always be the same?

Alright assuming that you are talking about a local variable running from a query in SSMS since it hasn't been specified otherwise. Even if you use the same value for the
AND UserStatus = @userStatus that you would use in the literal AND UserStatus = 1 you will see a difference in your execution plan due to how the cardinality estimate is generated.

When you use a literal value SQL Server will go out to the histogram for that table and see where that value of fits within the range key. The estimate gathered on that will result in one of two scenarios.

HISTOGRAM DIRECT HIT Essentially this means there is a RANGE_HI_KEY(Upper column value for any step in the histogram) value for that specific literal value in your query and therefore the estimate will match the number of EQ_ROWS(# of rows whose value equals the RANGE_HI_KEY) in the histogram. This means your estimate will be the number of rows that match that value based on the last time you updated statistics.

HISTOGRAM INTRA-STEP HIT This is when the value exists in a range between two RANGE_HI_KEY values. When your literal value is between that range it is calculated by the RANGE_ROWS(number of rows between two histogram steps), DINSTINCT_RANGE(the number of distinct values within that histogram step), and AVG_RANGE_ROWS (RANGE_ROWS/DISTINCT_RANGE_ROWS) and that will give you your estimate.

However when you run with a local variable you will no longer go to the Histogram for these values since the @Variable isn't known at runtime.

For more information on this topic I recommend reading this white paper by Joe Sack.

DENSITY VECTOR When there is no specific value to go with then SQL server instead uses a Density to be able to determine the estimated number of rows returned for that predicate. Density being 1/ the number of distinct values within that column. So then your cardinality estimation will be Density * the number of rows in the table.

So long story short no. Even if you run the same value over and over with a local variable you will not get the same results for reasons further explained in the link Eric Provided from Kendra Little.


Reporting Service 2014 Service Pack 1 Failing to work with error - wrong database version

There are two workarounds:

Delete one record from table ServerUpgradeHistory

USE [name_of_you_SSRS_database]
DELETE FROM ServerUpgradeHistory WHERE ServerVersion = 163

Install Cumulative Update 1 for SQL Server 2014 Service Pack 1. Before updating your SQL Server 2014 SP1 with Cumulative Update 1 - test it hard in your lab.

BE AWARE - Pay attention to backup encryption keys before you upgrade SSRS 2012 to
2014. After you apply CU1 all your encrypted content will be inaccessible.


If your instance is not that big, your best bet would be to recreate your slave from scratch.

You won't be losing data since from what I understand, your master is fine.


Oracle schema migration to new database with zero downtime

This is by no means the only way or necessarily the best way.  You should consider streams and GoldenGate as well, but if at least one application is designed to use packages and access the data only through those packages, consider the following:

Create a new database and setup views of the original database's tables for one schema using a database link.
Copy the package code for the application using that schema to the new database.
Switch that application to use the new database.  It should work exactly the same, except that it will be slower due to the link.
For each table or group of related tables, add package code that for each database call that maintains data in both the local and remote tables.  There are many ways you could do this.  One way would be to have a switch table with a row for each application table.  You could make a replication routine for the table and when the tables are in sync it could update the switch table to let the code know that it doesn't need to maintain both tables.

Yes, this is complex, and yes it would take a lot of time and testing, but given a requirement of zero down time, it should be expected.


In Oracle, you would use the COMMENT command to:

  [...] add to the data dictionary a comment about a table or table
  column, view, materialized view, operator, indextype, mining model, or
  edition.

Most tools (PL/SQL Developer, Toad...) will display these comments in appropriate fields when you browse the database schema.

The comments can be queried directly with the *COMMENTS dictionary views, such as ALL_TAB_COMMENTS.


Rebuild indexes with a FILLFACTOR 100 so pages are packed as tight as possible.
Update statistics on the table WITH FULLSCAN so the query optimizer has the most complete information possible to generate a query plan.


Select TOP @N where @N is a variable from another Query

You need to enclose the variable in parenthesis:

select top (@OtherHDDs) -- your column list here
from YourTable

MSDN Reference on SELECT


What is the SQL Server equivalent of pg_typeof(), how can I get the type of a column returned in a result set?

There are various indirect methods, but nothing quite as simple as pg_typeof.
SQL_VARIANT_PROPERTY is often used for convenience, but has limitations since it cannot contain all other types.
Writing the subject of inquiry to a table then inspecting metadata is one method. Convenience system stored procedures like sys.sp_help can be employed as a somewhat more convenient method than writing a query against the metadata views.
Another, conceptually-related idea is to call:

sys.sp_describe_first_result_set (procedure); or
sys.dm_exec_describe_first_result_set (function)

For example:
EXECUTE sys.sp_describe_first_result_set
    @tsql = N'SELECT 1.0;'

SELECT FRS.*
FROM sys.dm_exec_describe_first_result_set
    (N'SELECT 1.0;', DEFAULT, DEFAULT) AS FRS;

These return a great deal of internal information, including type details. The above example returns:

system_type_name

numeric(2,1)

An alias type example:
CREATE TYPE Banana FROM binary(456) NOT NULL;

EXECUTE sys.sp_describe_first_result_set
    @tsql = N'DECLARE @b Banana; SELECT @b;'

SELECT
    FRS.system_type_id,
    FRS.system_type_name,
    FRS.user_type_id,
    FRS.user_type_schema,
    FRS.user_type_name
FROM sys.dm_exec_describe_first_result_set
    (N'DECLARE @b Banana; SELECT @b;', DEFAULT, DEFAULT) AS FRS;

returns (in part):

system_type_id
system_type_name
user_type_id
user_type_schema
user_type_name

173
binary(456)
260
dbo
Banana


How do we handle timestamp formatting errors on large data imports?

We figured it out.

Some of our timestamp text data was imported in scientific notation (i.e. 2.0160924 E+13. This threw an error, as we didn't include the . in the template format in the to_datetime() function.

Our solution was to pull the datetime from another column that contained more or less duplicate data.

Point is - the to_datetime() function in PostgreSQL is very particular and requires data going in to match the format you define. If you're having a similar issue run a few tests to figure out where your data isn't matching up to what the function expects.

We also saw that Postgres MAY support a conversion from scientific notation. We couldn't find anything in 9.3 that did, but it seems like it is on their list of things to implement.


Test if a string is a valid, unquoted identifier?

Here is what I ended up using based on @a_horse_with_no_name's comment.

CREATE OR REPLACE FUNCTION is_unqualid(_ident text)
 RETURNS bool AS $func$
  SELECT format('%I',lower(_ident)) = lower(_ident);
$func$ LANGUAGE sql;

The idea is that the format function (with %I) will not add any quotes if it is unnecessary (i.e. when the input is already a simple identifier). I also normalized identifier names to lower cases using lower() before the comparison, which may or may not be desirable depending on whether the identifiers are supposed to be case insensitive or sensitive.

Sample output:

=> SELECT is_unqualid('Ab'), is_unqualid('ab'), is_unqualid('a b'), is_unqualid('a. b');
 is_unqualid | is_unqualid | is_unqualid | is_unqualid
-------------+-------------+-------------+-------------
 t           | t           | f           | f
(1 row)


How can I search for a hex string in oracle?


Open the query designer for a new query.
Add the tables you are interested in.
Drag the field number from table1 to table2
Drag the field code from table1 to table2
Right click on each of the lines between the two tables and select join properties
Select option 2

Lastly in the bottom section of the designer select the columns you wish to see in your output


What are the reasons to create a key-less, index-less database?

There are a number of possibilities listed in the comments. Several are not "legitimate" reasons not to have indexes or primary keys: ignorance on the part of the application developer, or accidental deletion of all indexes.

In a system where INSERT operations are prioritized over anything else, technically, indexes do slow things down. It may be that slow response for reporting, etc. was considered acceptable, as long as inserts completed as fast as possible.

Aaron Bertrand makes a critical note - a primary key is a constraint. It puts a restriction on the data, preventing duplicate values from being entered.  The same holds true for a unique index.

With this is mind, there's at least one possible reason to avoid implementing those: the application is designed to manage the relation aspects of the data at the application level.

This is more likely if the application was originally developed a long time ago (say the 1980s or 1990s). I worked with a SQL Server DB for about 10 years that was supporting an application originally developed in FoxBase, and it had no foreign keys; all the relationships were maintained via triggers. They'd started using foreign keys for newer tables, but only started changing existing tables a few years ago.

Depending on how the code's written, it's actually possible that you could break the application by adding primary keys or unique indexes. It's unlikely, but not impossible.

Adding regular indexes would only break things if the application really needs to keep inserts as lean as possible. That may have been a limitation at some point, but it may be that it isn't any longer.

Note that this is just one possible scenario. As Aaron recommended, the best option is to communicate with the vendor. Checking to see how much tuning you can do to optimize your organization's workload isn't a bad idea, and respecting the application's creators (whether they deserver it or not :-) ) is a good way to start a relationship.


sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

You have to use Service Names to connect to Pluggable Databases in 12c.

It's just one of those things that can trip you up, I guess! SQL Plus connecting OK probably threw you.


SQL Server Replication - Unable to start agent job - Username or Password Incorrect

We have literally just solved this issue.

It turns out, the SQL agent was configured to run as my user in the following format:

Myuser@domain.com

I simply changed this to use the following format:

MyDomain\Myuser

It suddenly just started working once I changed that.


Meaning of "update conflicts" in CollStats result

The number records how many times two or more threads are trying to update the same document inside the WiredTiger storage engine. It is not persisted between server restarts.

This statistic is specific to the WiredTiger storage engine (as it is under the WiredTiger section). Under MongoDB this is known as write conflict, and will be transparently retried under valid conditions.

That is, the update is retried by MongoDB (using the WiredTiger's API) until it completes without a conflict. From the client perspective, the write succeeds and is otherwise normal except for increased latency.

Write conflicts indicates that multiple clients are trying to update exactly the same document within the same fraction of a second. This window is the time between the execution of the WiredTiger API's WT_SESSION::begin_transaction() and WT_SESSION::commit_transaction() operations. There is no fixed minimum time for these operations. It could be double or even single-digit microseconds if the amount of data to be written is small and the server has no queues for CPU or memory channels. There is no fixed maximum time either, although it is somewhat dependent on the performance of the server.

These conflicts can arise due to WiredTiger's optimistic concurrency control, which is described in concurrency FAQ for WiredTiger.

Note that comments in WiredTiger's source code might be misleading from MongoDB's point of view, since WiredTiger and MongoDB uses mostly the same term, but attach slightly different meanings to them.

For example, "transaction" in WiredTiger has always been available, but "transaction" in MongoDB means a multi-document ACID transaction feature for MongoDB 4.0 (not yet released as of this writing).


DB2 - What is the difference between online backup and AUTO_DB_BACKUP?

AUTO_DB_BACKUP means DB2 will take a backup based on when it feels it should do so. The things that influence the taking of a backup are:

Never completed a full backup
Time elapsed since the last full backup was a specified number of hours
Transaction log space consumed (with archival logging enabled), since the last full backup is a specified size.

In order to have AUTO_DB_BACKUP working, you also need to have AUTO_MAINT turned ON as well. I would also wager a guess that in order to enable the scheduling part, you may need to have the DBM CFG HEALTH_MON turned to ON as well.

Most DB2 DBAs I talk to turn off HEALTH_MON and AUTO_MAINT because of the performance issues encountered. DB2 may choose to exercise maintenance and backups when it is least convenient for you. So I would recommend against using them, unless you are using BLU under DB2 10.5. Then it sounds like AUTO_MAINT may be more important again.

Anyway, an online backup is just a backup while users are still engaging the database and/or the database is activated. You can take online or offline backups regardless of whether or not you have AUTO_MAINT and AUTO_DB_BACKUP enabled. You probably want online backups enabled if you wish to use AUTO_DB_BACKUP in case DB2 decides to take a backup in the middle of the day. Otherwise it could either fail to backup, or kick users off the system. I'm not sure which as I've never tried. Like I said, I steer clear of HEALTH_MON and AUTO_MAINT.

This way I can either take scripted backups when I want them scheduled via a job scheduler. Or I can take an on-demand online or offline depending on the situation.


Massive MSreplcommands table, 2 billion rows, cleanup job not deleting commands

The process at https://blogs.msdn.microsoft.com/chrissk/2009/12/29/how-to-resolve-when-distribution-database-is-growing-huge-25gig/ can help with this.

To prevent it happening in future you may need to look at the configuration of the Distribution clean up: distribution job.


Easy way to replace an YAML array of IDs with strings from a joined table

Colin 't Hart's comment is 100% correct - your data model fails on the first letter of ACID.  YAML is fine for serialization of objects for application consumption, but since your data layer is a RDBMS, you should stick to the RDBMS paradigms.  Assuming you don't have the permission to migrate to a better structure and probably force a re-code of the application, the good news is that you're halfway there with your original query already:  Instead of going straight to array ( regexp_split_to_array ), split to table ( regexp_split_to_table ) instead, then perform a join on camp_weeks for the names.  You could then group that for the stuffed string listing ( using string_agg if it's available on your version of postgres, like shown below ), or pass the results as-is, letting the application worry about the presentation layer.

SELECT  a.name,
        a.id,
        a.title,
        string_agg( cw.week_name, ', ' )
             AS week_names
FROM (  SELECT  cs.id
                    AS camper_id,
                cs.name,
                c.id,
                c.title,
                regexp_split_to_table( trim(
                    regexp_replace(
                        cs.camp_weeks,
                        E'[\\n\\r''-]+',
                        ' ', 'g' )
                    ), E'\\D+' )
                    AS camp_week_id
        FROM    public.campers cs
        LEFT JOIN public.camps c
            ON  cs.camp_id = c.id ) a
LEFT JOIN public.camp_weeks cw
    ON  CAST( a.camp_week_id AS INTEGER ) = cw.id
GROUP BY a.name, a.id, a.camper_id, a.title
ORDER BY a.id, a.camper_id;


Why does this query take 32 sec to execute?

I'd actually think about adding a status column to the table tblstockingorders and having a trigger on that table that injects the status into the tblstatushistory.

Granted you'd have to do a 1-time update to all the rows in tblstockingorders (something similar to your query above) and set their last status, but this would give you best overall performance as these two tables grow.


How to tell if a backup file contains CDC?

(if someone finds a way - which I dont think exists, I will delete my answer :-))

Is there a way to determine if a backup contains CDC data?

I dont think that there is any way to know from a backup, if CDC is enabled or not.
The most you can know if the database was involved in replication (under the hood uses log reader agent that scans the Transaction log) or not by using DatabaseBackupLSN in the result of RESTORE HEADERONLY DatabaseBackupLSN as is the “begin of checkpoint” that is triggered when the backup starts. This LSN will coincide with FirstLSN if the backup is taken when the database is idle and no replication is configured.
I assume the reason that microsoft does not surface whether CDC is enabled or not, might be a security thing as well. CDC is an enterprise level technology that enables tracking of changes all the way down to the column level. This is required by regulations like HIPAA, FISMA, etc.
From Restoring or Attaching a Database Enabled for Change Data Capture

SQL Server uses the following logic to determine if change data capture remains enabled after a database is restored or attached:

If a database is restored to the same server with the same database name, change data capture remains enabled.
If a database is restored to another server, by default change data capture is disabled and all related metadata is deleted.
To retain change data capture, use the KEEP_CDC option when restoring the database. For more information about this option, see RESTORE.
If a database is detached and attached to the same server or another server, change data capture remains enabled.
If a database is attached or restored with the KEEP_CDC option to any edition other than Enterprise, the operation is blocked because change data capture requires SQL Server Enterprise. Error message 932 is displayed:

SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.


How to display MS SQL results vertically?

The expected result when you use SELECT F1, F2, F2 is to obtain one column for each field in the SELECT sentence.

SELECT '1' + CHAR(13) + '2' + CHAR(13) + '3' + CHAR(13)

------
1
2
3

(1 row(s) affected)


Migrating 300GB database to Azure SQL Server

Have you tried the Azure Data Migration Service located here? You can try the service for free from now until August 31, 2018. For more information, please click here.


How to make SQL Server order fields by 'Id' (which in this case is an Identity column)

Now, when you say:

  I need the id column to be arranged in order of increasing number because I use a data table in C# to go through these values one at a time, therefore jumping from 7 to 9 to 11 back to 8 is very inconvenient.

it shows some confusion. There are two issues here that are orthogonal to each other:

the internal order that rows are stored
the order that the rows are returned in a result set

These two are not to be confused. The internal order is a matter of the database's physical design while the order of a result set can vary from query to query, depending on the ORDER BY clause.

Tables are inherently unordered. Despite that, in many DBMS - and in this case SQL Server - we have the option to add a "clustering index" on a table, so there is a B-tree index (with the chosen order) that leads to the rows.

This is however irrelevant to the order that a result set is presented. The only way to have a guaranteed order in the result set is to specify an ORDER BY clause.

If you don't specify an ORDER BY clause, the order can be arbiratry:

you may have a heap (table without clustered index) and still sometimes see the result ordered by id and sometimes in a random order.
you may have a clustered table and still sometimes see the result ordered by id and sometimes in a random order.

Notice that there is no difference above. The underlying table structure doesn't guarantee an order.

So, if you do want the rows stored in a specific order on disk, make (id) the clustered index. But note that without an ORDER BY id, you will still get the results in arbitrary order.


Migrating BLOB in Oracle into PostgreSQL

From a conceptual point of view the equivalent to Oracle's BLOB datatype is bytea in Postgres.

bytea only allows you to read and write the complete data (you can't just retrieve 100KB from a 1GB value). Which means that this is kept in the memory on the JVM while reading it. This is the same as with Oracle's BLOB type. From a JDBC point of view those two data types behave nearly the same.

The OID (or better: "large objects") isn't a real "data type". It is merely a pointer to the data stored in the file system. The advantage of using large objects is that you can properly stream the content from the server to the client, you don't need to load the entire binary data into the client's memory.

However dealing with large objects is much more complicated than dealing with bytea columns. You also need to manually clean up deleted large objects if you delete the corresponding row from the base table (something you do not need to do with bytea). Reading and writing of large objects is also much more complicated then dealing with bytea values.

As you are currently using a BLOB column, you apparently do have the memory on the client side to process the data, in that case to make transition as smooth as possible, I highly recommend to use bytea in Postgres.


I/O errors backing up to an SMB Share from MSSQL Server 2014

Just following up on this. Backups have been running flawlessly since we made a change to the SMB timeout in the registry:

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\LanmanWorkstation\\Parameters]
"SessTimeout"=dword:00000258

What that works out to is 600 seconds or a 10 minute timeout for SMB sessions from the SQL Server, the default is 60 seconds. it needs to be saved in hex.

I found it in the article: CIFS and SMB Timeouts in Windows

It's the line with a heading of Client Session timeout.

We arrived at this solution after verifying in a packet capture that the session was being ended by the SQL Server sending an RST, ACK to end the session.


Displaying parent attributes alongside child attributes

No, you can't.

As you currently have your Employee table, you can't have a Manager Role attribute for an employee.

To have a Manager Role attribute, you'd have to add a ManagerRole column to your Employee table:

Key  Name   Role  ManagerKey  ManagerRole
---  -----  ----  ----------  -----------
1    John   CAO   1           CAO
2    Adam   Emp   1           CAO
3    Steve  CAO   3           CAO
4    Colin  Con   3           CAO
5    Tony   Con   3           CAO

... so that you could define a Manager Role attribute on your Employee dimension.


If you use Availability Groups you must do log backups. If you use Hallengren's backup solution then you'll need to set it up on both the primary and secondary node(s). Then direct the jobs it creates to point to the same network share.

What will happen next is the script will create one directory for each server to hold system databases and any databases not within an availability group. Then another directory gets created with the availability group name and all the full and log backups go into there.

That's important because your full backups and log backups might be split up between servers (so full backups happen on the primary and log backups happen on the secondary; there are settings so you can tweak this). So you want them all stored together in case you ever need to use them.

AGs have a lot of things you have to know about. Monitoring so you know when they get out of sync, adding job steps for applications to make sure they only run on each server when it's primary (Hallengren's jobs have this built in), and patch order (don't failover with cluster manager ever). Don't put SSISDB into an AG without reading about it first. Don't use it wherever MSDTC is in use.

You'll need to do some solid research but they're not that difficult. You'll learn edge cases as you go along.


mongodb - Replica set creation error: Quorum check failed because not enough voting nodes responded

The answer is that all 3 machines must have the same version 4.0


How can I enable Transparent Data encryption (TDE) on MySQL 5.6

Transparent Data Encryption (TDE) can be used to perform real-time I/O encryption and decryption on instance data files. To increase data security, you can enable TDE to encrypt instance data. Data is encrypted before it is written to disk and decrypted when it is read from disk. TDE does not:

Increase the size of the data file.
Require developers to make any code changes to use TDE.
Alibaba Cloud ApsaraDB for RDS fully supports TDE for MySQL. In this article, we will look at setting up TDE for MySQL on Alibaba Cloud.

Note: TDE is currently only applicable to SQL Server 2008 R2 and MySQL 5.6. To view or modify TDE settings, you need to log in with an Alibaba Cloud account rather than a RAM account.

Setting Up TDE on ApsaraDB for RDS

To enable TDE on Alibaba Cloud:

Go to the RDS Management Console, select the appropriate RDS instance.
Under Security Control, TDE tab you will be able to find the option to enable TDE.

This feature will allow encryption of data while at rest. The encryption of data while in transit is still the responsibility of the user and is outside the purview of this document.

Encrypting Tables

Log in to the database and execute the following command to encrypt the table to be encrypted.

alter table  engine＝innodb block_format=encrypted;

Decrypting Tables

If you want to decrypt the TDE encrypted table, execute the following command.

alter table  engine＝innodb block_format=default;

You can find detailed TDE documentation here


Why does this simple transaction deadlock with MySQL?

I'd say it's an illustration on how gap locks work -http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks  .

Say you have adjacent user id , 1 and 2.  When procedure executed simultaneously from 2 different sessions, each of them put a gap lock on two index-records (with user_id values 1 and 2 - maybe 0 ,4,5 as well ,but let's assume just 2 for simplicity sake), and each of them has to wait for another one to release the lock to perform an insert.

  Gap locks in InnoDB are “purely inhibitive”, which means they only
  stop other transactions from inserting to the gap. They do not prevent
  different transactions from taking gap locks on the same gap. Thus, a
  gap X-lock has the same effect as a gap S-lock *."

To disable locks,

  Gap locking can be disabled explicitly. This occurs if you change the
  transaction isolation level to READ COMMITTED or enable the
  innodb_locks_unsafe_for_binlog system variable (which is now
  deprecated). Under these circumstances, gap locking is disabled for
  searches and index scans and is used only for foreign-key constraint
  checking and duplicate-key checking.

Using READ COMMITTED sounds like a reasonable solution to me, assuming it doesn't break application logic.


Critical Patch Updates:

  Critical Patch Updates are collections of security fixes for Oracle products. They are available to customers with valid support contracts. They are released on the Tuesday closest to the 17th day of January, April, July and October.

So it depends on your security patch policy.

But there are other types of patches: PSU(Patch Set Updates) and BP(Patch Bundle)

Here is the description from Oracle Database - Overview of Database Patch Delivery Methods - 12.1.0.2 and older (Doc ID 1962125.1) from https://support.oracle.com

  Types of Proactive Patch (SPU / PSU / Bundle Patches)

  (...)

  Security Patch Update (SPU)

  a cumulative collection of security fixes released as part of Oracle's Critical Patch Update (CPU) program
  (...)

  Patch Set Update (PSU):

  a cumulative collection of fixes for proven high impact bugs encountered in the field
  includes the security fixes that are released as part of the CPU program
  guaranteed not to contain any changes to the optimizer or fixes which change application behaviour
  (...)

  Bundle Patch (BP)

  a cumulative collection of fixes to address bugs in a given feature, product, or configuration
  a superset of PSU
  (...)

So maybe you want to patch with last PSU or BP to get fixes for bugs and mybe sume enhancements

You can download these patches only if you have a valid CSI (Customer Support Identifier)


Date alignment and pair matching extraction best done with TSQL or C#?

Here's something I was fiddling with based on some work I had laying around.  It doesn't handle the events clustered at a single time well.  It could theoretically be helpful anyhow...:)

;WITH ordered_rows AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY EventTime) AS Row,
        Identifier, Type, EventTime, DiscoveredDate, FileId FROM #EventDataTemp
)
,filtered_rows AS
(
    SELECT Row, Identifier, Type, EventTime, DiscoveredDate, FileId,
        CAST(CASE Type WHEN 'Went Off' THEN 1 ELSE NULL END AS INT)
            AS OffEventRow
    FROM ordered_rows
    WHERE Row = 1
    UNION ALL
    SELECT o.Row, o.Identifier, o.Type, o.EventTime, o.DiscoveredDate, o.FileId,
        CAST(CASE WHEN o.Type = 'Went Off' AND f.Type = 'Went Off'
            OR o.Type = 'Came On' THEN f.OffEventRow ELSE o.Row END AS INT)
    FROM ordered_rows o INNER JOIN filtered_rows f
        ON o.Row = f.Row + 1 AND o.Identifier = f.Identifier
)
,on_events AS
(
   SELECT Identifier, OffEventRow, MAX(Row) AS OnRow
   FROM filtered_rows
   WHERE Type = 'Came On' AND OffEventRow IS NOT NULL
   GROUP BY OffEventRow, Identifier
)
SELECT f.Identifier, f.Type, f.EventTime, f.DiscoveredDate, f.FileId
FROM filtered_rows f LEFT JOIN on_events o
    ON f.Identifier = o.Identifier
    AND f.Row = o.OnRow
WHERE f.Type = 'Went Off' AND f.Row = f.OffEventRow
OR f.Type = 'Came On' AND o.OnRow IS NOT NULL
ORDER BY f.Identifier, f.EventTime


How To Grant View Definition of Object in SQL Azure

Your syntax is incorrect.  It should be

GRANT VIEW DEFINITION ON <object/schema> TO <user/role>

Example

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [bar]

GRANT VIEW DEFINITION ON OBJECT::dbo.MyView TO [barRole]


Remove extra email characters to enforce uniqueness

To answer your question (+1 for an interesting, challenging and relevant question). I did the following:

Created a table:

CREATE TABLE regexp_test (my_email_string VARCHAR(255));

Populated it with sample data (as per the question, the only place a +sign can appear in the email address is at the end of the first (local) part with 1 or more digits between it and the @ sign:

INSERT INTO regexp_test
VALUES
('as.dfWEWd.fs+4@stuff.com'),
('aS.Sdf.34343a.sfs@yahoo.com'),
('adsFFdsf"£"££$£$+15@tester.cn'),   <-- non-standard characters!
('test_dots.asdf.+12345@blah.au.com')

-- multiple dots after the @ sign
-- this is the tricky part!

And then ran this query:

SELECT
  LOWER
  (
    REGEXP_REPLACE
    (
      REPLACE
      (
        SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1)
        , '.', ''
      )
        ||
        '@'
        ||
        SUBSTRING(my_email_string, '[^@]*$'),
      '[^a-zA-Z0-9@.]', '', 'g'
    )
  ) AS "New email"
FROM regexp_test;

Explanation of query below.

Result:

               New Email
    asdfwewdfs@stuff.com
assdf34343asfs@yahoo.com
     adsffdsf@tester.cn
testdotsasdf@blah.au.com

All this is available on a fiddle here.

TL;DR - this will work for an email which contains multiple full stops (dots or periods) either in the first (local) part of the email (these will be removed as per the question) OR in the second (domain) part - this part can legitimately have multiple dots - say blah.au.com. Any symbols which are not alphanumeric will be removed from the local part. Anything between a + and the @ sign in the local part will also be removed.

In particular, emails like john.m.doe+15@blah.au.com will be converted to johnmdoe@blah.au.com.

To explain the query, it is best to go from the inside out (you can see some of the logical progression on the fiddle itself).

The first inner-most part uses SPLIT_PART() twice:

SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1)

Which gets (chomps) the email string up to the first @ sign (and only @ signe in a valid email address). Then the second SPLIT_STRING chomps the string still further and removes anything from a + sign to the @ symbol inclusive.

Then the outer

REPLACE
(
  SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1)
  , '.', ''
)

removes (replaces '.' with '' - nothing) all the dots in the first (local) part of the string as per the question, but not in the second (domain) part (addresses such as @blah.com.au are valid).

Now,  I get the email domain with the SUBSTRING() function using a regular expression as explained here:

SUBSTRING(my_email_string, '[^@]*$'),

this gives us the domain from after the @ sign to the end of the string.

I then use the SQL string concatenation operator (|| - double pipe) to join the two strings and also reinsert the removed @ sign.

So, my data now looks like this (result_1):

asdfWEWdfs@stuff.com
aSSdf34343asfs@yahoo.com
adsFFdsf"£"££$£$@tester.cn
test_dotsasdf@blah.au.com

So, the only dots and at signs remaining in the email string are valid, but we still have to get rid of the + signs (from the question) and other symbols (for checking) from the string. For this we use REGEXP_REPLACE.

So, the query now looks like this (result_1 substituted in for clarity).

REGEXP_REPLACE(result_1, '[^a-zA-Z0-9@.]', '', 'g')

This replaces all ('g' for global) characters not (^) in a-z or A-Z or the @ symbol or a dot with nothing ('').

In fact, this part might be redundant. If there are no symbols other than .s (dots) anywhere in the local part of the string, or a + sign followed by  digits and then an @ sign, then the REGEXP_REPLACEis unnecessary! I threw it in just in case!

The LOWER() just tidies it up - I prefer emails in lower case!

Et voilà!


How to know which website is causing the sql server to stop responding?

1) Computer was restarted (one of causes).
Use Sql Server Configuration Manager to set Start Mode to Automatic

2) Use Event viewer (Windows Logs/System) to get more information about stops/starts of service:


How can I control the order of a query regarding to inner join

The easiest way to influence a query plan and make it run faster is to have good indexes and ensure the statistic on those indexes are up-to-date.  In your case have indexes on the primary and foreign keys in each table.  It would be best if you included b_come and c_type in their respecitive indeces, too.  (Sorry, I'm not strong on Informix; if the syntax allows the include clause or filtered indexes investigate and implement these.)

Wrapping the date() function around a_dt generally precludes any use of an index on that column.  If makes it "non-SARGable" in the jargon.  It may be better to structure it as

WHERE a.a_dt >= '01/01/2012 00:00:00.000'
and   a.a_dt <  '01/02/2012 00:00:00.000' -- or whatever the day after 1/1 is in your locale.


Is the following table for a simple music database in 3NF? If not why?

If Track_Id and (Track_Name, Track_Artist, Track_Album) are candidate keys, with no other partial or transitive dependencies, then the table is in 3NF. Think about this - can Track_Name determine Track_Album or Track_Artist, or can Track_Album determine Track_Artist? If not, it's in 3NF.

If the other two tables have candidate keys, they'll be in 3NF.


Right now we are connecting using the Listener\InstanceName

The listener does not actually interact with the Browser service, and thus I wouldn't use this way with a listener - ever.

  I wanted to configure in such a way that just giving the AG_Listener would be enough to connect without using the port explicitly.

Set the listener for port 1433. The listener uniquely identifies the instance of SQL Server and there can be multiple listeners all using 1433 as each listener has a unique IPv4 and/or IPv6 address. Note that there are other means to which SQL Server can be connected through when there are multiple instances on the same server which may or may be appropriate or correct ways of connecting.


In a standard SQL dbms, you'd enforce that kind of requirement by ordering the id numbers, and using a CHECK constraint.  Application code, a stored procedure, or a user-defined function is responsible for putting the id numbers in the right order.

create table friends (
  user_a integer not null,  -- references users, not shown
  user_b integer not null,  -- references users, not shown
  primary key (user_a, user_b),
  check (user_a < user_b)
);

But MySQL doesn't enforce CHECK constraints. Using MySQL, I'd still use a stored procedure (not application code) to put the id numbers in the right order. But I'd also run a query or a report every now and then to make sure that all the values for user_a were less than the values for user_b.

select *
from friends
where user_a >= user_b;

I probably wouldn't include an UPDATE statement to fix those values at first. I'd want to track down what process, application, or user was going around my stored procedure to insert data directly into the table. (You can revoke direct access to the table, and require all access to go through stored procedures. But this isn't practical in some legacy systems; too much code to rewrite. And there are alternatives.)

Unfortunately, some requirements have to be implemented as administrative procedures, like running reports.


What is the term that describes the use of OFFSET and LIMIT in a query

It's generally called paging.

Different vendors have implemented it in different ways. SQL Server was late in adopting the standard (OFFSET/FETCH in SQL Server 2012); prior to that you had to use wacky tricks with CTEs and ROW_NUMBER(), and I forget what we did in SQL Server 2000. #temp tables? MySQL chose to one-finger salute the standard and use LIMIT.

Lots of discussion about various techniques for SQL Server in this comment thread.


What is the industry term for superset of "metrics" and "dimensions"?

Dimensions and Measures are the two main components of cubes. The dimensions are basically ways to look at the data while the measures are the data.

When writing MDX the measures are a member of the [Measures] dimension but that is mostly a syntax convention.

As for a common name, I would say they both are partly definitions of a cube design. The measures define the values and the dimensions define how you slice or aggregate the measures.

The combined result of a query across dimension members and measures is called a cellset but that term is used for a result rather than a cube definition (which is what measures and dimensions are).

If there even is a common term for the combination of dimensions and measures it would be physical aspects of data aggregation

If I were to design a UI where I list both in an interface for users to add a report I would probably call them 'report items' or 'data components'  or something but I think the clearest for an end user would be to follow  what Excel does and label them 'rows' or 'columns' and 'values' where things you can add to rows/columns are the dimensions and values are the measures.

I don't think it's any clearer to an end user to try and find a common name and from a UI design perspective I would make a distinction between things they should put on an axis and 'values'.


SQL Replication groups in Windows failover clusters RRS feed

I believe, DFS replication is meant for files synchronization not for the databases. consider following options as database solutions/features in SQL Server.

SQL FCI
Since you already have SQL FCI in-place, the databases and data (program files) related to SQL Server are already in shared location which can be accessible to both nodes.

Availability Groups
Unlike FCI, availability groups hosted by stand-alone SQL instances, each instances called as replica, every replica maintains their own copy of databases and every change in the database replicated all database copies across replicas within a group. you can have 8 replicas, required enterprise license. WSFC required for auto-fail-over purpose.

Log Shipping
Log shipping works based on scheduled backups, which periodically backups the changes on primary server, secondary server copies backup files and restores regularly as per defined schedule.

SQL Replication
Above options considered as high availability solutions, but replication is something used for copying and distributing the database's objects


Key column 'r_Course_id' doesn't exist in table Error in my code

You were missing a few things. There was no faculty_id column in the last table and you missed a comma  after course_id.

Try running the statements below (it works for me).

CREATE TABLE Faculty (
Faculty_id INT NOT NULL,
First_Name VARCHAR (20) NOT NULL,
Last_Name VARCHAR (20) NOT NULL,
EMAIL VARCHAR (30) NOT NULL,
Date_of_birth VARCHAR (30) NOT NULL,
Number_of_courses int, PRIMARY KEY (Faculty_id)
);

CREATE TABLE Courses (
Course_id INT NOT NULL,
Discipline VARCHAR (20)NOT NULL,
Course_number INT NOT NULL,
Credit DECIMAL(7,2)NOT NULL,
Year_offerd INT ,
Course_Title VARCHAR (50)NOT NULL,
PRIMARY KEY (Course_id ) );

CREATE TABLE FacultyCourses (
F_C_ID int NOT NULL AUTO_INCREMENT,
Course_id INT NOT NULL,
Faculty_id INT NOT NULL,
FOREIGN KEY (Course_id) REFERENCES Courses (Course_id),
FOREIGN KEY (Faculty_id) REFERENCES Faculty(Faculty_id),
PRIMARY KEY (F_C_ID) );


Since the TVF calls a Web Service, the Assembly had to be marked as WITH PERMISSION_SET = EXTERNAL_ACCESS (hopefully you chose to sign the Assembly, create an Asymmetric Key, and create a Login from that Key, as opposed to setting TRUSTWORTHY ON for the Database). This fact gives you two options:

Use something like File.AppendAllText to log the info into a file and keep it out of the database. This method creates the file if it doesn't exist. NOTE: This option needs to be tested with concurrent actions that would fire the Trigger as there might be some blocking / additional wait-time incurred if the OS does not allow concurrent write activity on the file.
Log to a table by creating an regular / external SqlConnection and issuing an INSERT statement. Be sure to specify enlist=false; in the Connection String so that it doesn't attempt to bind to the current Transaction. This will allow the INSERT (which occurs in a separate connection / Session) to happen whether or not there is an exception raised in the SQLCLR TVF (unless, of course, the error is with the INSERT operation ;-).


oracle sql joins returns multiple row than expected

In this case Oracle works as it should, its your expectation that is incorrect.
Why do you expect 24 rows?
zz_value table has 8 rows with 9863 gg_id. zz_gg table has 8 rows with 9863 gg_id. pos_id and nach_id are the same in all rows in your sample. With your join conditions, this alone return 64 rows for gg_id 9863.
Your select with those columns returns duplicated rows. You can reduce that amount by using select distinct ..., but even that will return more than 24 rows because of the data_id column in zz_gg.


sql transactional replication - slow renitialization / slow bulk insert

actually opening this snapshot that he generates before and doing this bulk insert like thru linked server in that case?

Is this a push subscription or a pull subscription?  For Push the Snapshot Agent runs at the distributor; for Pull it runs at the subscriber.  The Snapshot Agent reads the snapshot files and connects to the subscriber to create the tables and bulk load them.
You can also copy the snapshot folder to an Alternate Snapshot Location and have the Snapshot Agent read from there.  If you're using Push Subscriptions because you have SQL Express on the subscriber and so don't have SQL Agent, you can syncronize your Pull Subscription through PowerShell.

Why it is going so slow ?

Look at the Wait Stats and CPU utilization on the Subscriber.

Can it be the case that the publisher machine is distributor also ?

Yes.  Called Local Distribution.  Most common configuration.


In the result, the value column must have a data type, like always. SQL Server determines the type using the rules for data type precedence (more precisely, the VALUES clause is a UNION, so types are matched there).

In your first example, the precedence rules give a column of type float. In the second example, it is datetime.

Solve the problem by explicitly casting each value in the VALUES clause explicitly to a common type, e.g. varchar or sql_variant:

SELECT
    new_col = X.col + CAST(S.RouteID as varchar(10)),
    X.value
FROM #Shipping AS S
CROSS APPLY
(
    VALUES
        (CONVERT(sql_variant, S.RouteID),   'Id'),
        (CONVERT(sql_variant, S.Latitude),  'Lat'),
        (CONVERT(sql_variant, S.Longitude), 'Lon'),
        (CONVERT(sql_variant, S.[Time]), 'Time')
) AS X (value, col);

or:

SELECT
    new_col = X.col + CAST(S.RouteID as varchar(10)),
    X.value
FROM #Shipping AS S
CROSS APPLY
(
    VALUES
        (CONVERT(varchar(8), S.RouteID),   'Id'),
        (CONVERT(varchar(8), S.Latitude),  'Lat'),
        (CONVERT(varchar(8), S.Longitude), 'Lon'),
        (CONVERT(varchar(8), S.[Time], 112), 'Time')
) AS X (value, col);

Note: Not everything can be cast to sql_variant. From that link:

Everything except the old deprecated image type can be cast to char or varchar. See the conversion matrix in Data Type Conversion (Database Engine):


What's the best way to convert dtsx packages to query a new server?

Two options come to mind:

If your dtsx packages are stored in the file system, you could use POWERSHELL  to read in each file, search and replace the connection string and rewrite the file.  Here is an example:

(Make sure you make a copy of the folder BEFORE running the Powershell script, just in case):

$DtsxFiles = Get-ChildItem C:\Users\uswsh\Documents\*.dtsx -rec
foreach ($file in $DtsxFiles)
{
    (Get-Content $file.PSPath) |
    Foreach-Object { $_ -replace "localhost", "localhost1" } |
    Set-Content $file.PSPath
}

If your packages are not stored in the file system, you could dynamically generate CREATE SYNONYM commands that would point to the new location.

CREATE SYNONYM [dbo].[OriginalTable] FOR [RemoteServer].[RemoteDatabase].[RemoteSchema].[RemoteTable]


Slow join behaviour with 'or' in predicate

OR is slower because you no longer can perform an index seek, but effectively forces the database engine to look through each leaf node in your index tree.
With a single parameter (no OR), the engine can seek down through the index to the relevant leaf nodes, but when you ask it an OR, it scans the entire range of leaf nodes.

If the performance is too slow to tolerate, do as the comments suggest and try a UNION (no duplicate values) or a UNION ALL (allow duplicate values), or even two separate queries on their own and then join the results together in the code layer receiving the results.