Read committed snapshot:Finding lock on table

It is possible to list the locks using the DMV sys.dm_tran_locks (sp_lock is deprecated).

The DMV query can be hard to code against and the results can be difficult to interpret. Consider using the free sp_WhoIsActive tool from Adam Machanic instead. He explains how to use it to analyse chains of blocking locks in this blog post (which also contains a download link).


How to reduce database size with BLOB fields

3GB is not really a lot of data.  My laptop has 16GB memory, our datawarehouse is 3TB and that would be a fairly small warehouse.  The maximum database size according to here is 524,272 TB!?  So I think performance here is more about the kit you are on, plus index and query design.

You could refactor as Russell has suggested.  However you could also maintain the original one-table design with appropriate indexing, either a non-clustered covering index, or even full-text indexing to support the 'search for record' type queries - this should not include the blob.  Then only get the fingerprint images on an individual basis, when required by the application.  To put this another way, do not include the blob column in SELECTs where it is not explicitly required.  This will have the same effect as splitting the data into two tables without the redesign.  Don't attempt to get all the fingerprints at once!

Please tell us more about the spec of kit you are on, the version of SQL Server you are running, the table DDL (including indexes) plus what type of queries the application normally runs.


Using function is much slower than running query

The extra times goes to the overhead of plpgsql, which is not small.
With RETURN QUERY, it first reads all of the tuples into a tuple store, and then reads them back out again. In this case, I think that that accounts for most of the additional time.  Note that this extra overhead will be proportional to how rows are returned (not how many are inspected but then filtered out).


You cannot; Oracle does not store user passwords, only their hash values, which you can retrieve by select password from dba_users where username = 'WHATEVER'.


What disrupts the inherit logic order of autoincrement that leads to illogical ordering of ids in the absence of an ORDER clause?

I'll just elaborate on @a_horse_with_no_name 's comment. A table (relation) is a set of rows (tuples). By definition, a set is unordered. Asking what the next row is without specifying what you mean by next (ORDER BY), is like asking what colour joy has (well when it comes to power tools everyone knows that it is green and black ;-).

Assume that no rows reside in memory and that another connection reads id=186 into memory. Now your query asks for:

select id from T limit 1;

What would you consider the most sensible thing for the DBMS to do, return 186 from memory, or read a bunch of rows from disk to return the lowest id?

What's the benefit of treat a table as unordered one might ask? The optimizer tries to execute the query as efficient as possible. It can re-arrange the query in a number of ways, as long as it does not violate the semantics of the query. If it would have to take the ordering into consideration, it would severely cripple the freedom to choose the most efficient plan for the query.


Well thanks everyone,
but I read somewhere saying that i should include this in the table i want to sync to(to make the table unique) "alter table "YOURTABLE add unique index(row1, row2);"

then i  add ON DUPLICATE KEY UPDATE name = name and number = number;

Here is a final trigger after making the table unique to the one am synchronize to, in this case it's "database2.cc_phonenumber" and everything worked perfectly.

    delimiter |

CREATE TRIGGER database1 BEFORE INSERT ON pesapi_payment
  FOR EACH ROW
  BEGIN
    UPDATE database2.cc_phonenumber SET amount=amount+new.amount where id=new.amount
    INSERT INTO database2.cc_phonenumber(number, name, amount) values (new.phonenumber, new.name, new.amount) ON DUPLICATE KEY UPDATE name = name and number = number;
       END;
    |

delimiter ;


Error while opening SQL worksheet in SQL Developer

I was also facing the same issue.
While running SQL Developer with Run as Administrator option, I was able to open worksheets.


Strategies/Resources for implementing data lineage in an ETL process?

Lineage is sometimes implemented in the ETL tool, but the Microsoft stack doesn't really. Since you want to offer the insight to your end users it's probably not even a lot of help if your development environment offers it.

One way you could go about it, and something that I've used successfully in the past (even if it violates a few design principles) is to add a field to my fact tables indicating the source of the fact, which can then be exposed via drilldown or possibly even a fact dimension.

For example if you have a fact table which combines forecasts from erp as well as a CRM system you could have a column "factsource" or whatever which contains a concatenated field with sourcesystem|table|primarykey like this:

 sourceid        | customer    | amount
 ----------------------------------------------------
 CRM|forecast|41 | C001        | 1000
 ERP|quotation|6 | C002        | 5000

Then when a user uses the drilldown functionality in the cube client or some other mechanism to see the facts behind the transaction they could immediately see that the 5K comes from quotation number 6 in the ERP system.

Off course you would need to construct that value in your ETL system while loading the fact table.


How to restore SQL Server database to a particular time?

You can do it through T-SQL this way:
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY,
STOPAT = 'Mar 23, 2009 05:31:00 PM'
GO

But don't forget you can use the GUI from SSMS. The graphical solution is easier to understand especially if you are 4 people around the same monitor and the manager is behind you with a stick in his hand:

Right click on the database > Tasks > Restore > Database

General > Timeline and setup the time for database restoration

Check in “Specific date and time” to set up the time for database restoration, press “OK” to save changes, and press “OK” button in the previous window


Exadata: how to backup system before upgrade rpm packages?

Answer is the following:
Create LVM Snapshot-Based Backup for your OS.
Refer below link in steps to perform the same.
https://docs.oracle.com/cd/E50790_01/doc/doc.121/e51951/db_server.htm#DBMMN22020
2 Maintaining Database Servers of Oracle Exadata Database Machine
Section:
Creating a Snapshot-Based Backup of Oracle Linux Database Server


As it turns out, the key word loop needs to be double-quoted inside PL/pgSQL or the parser is confused:

SELECT public.foo_fun(NEW."loop") INTO fRV;

The situation in Postgres 9.3 is unchanged:
SQL fiddle with quotes (works).
SQL fiddle without quotes (doesn't work).

It's generally a good idea to avoid typical key words like loop as identifiers. There are some plpgsql key words in addition to SQL key words (like for procedural elements or exception handling), but I don't know of a comprehensive list of additional plpgsql key words. The source code would be my best idea.

I found a related discussion on pgsql-hackers from 2007. It seems the matter is not fully resolved, yet.
Didn't see any changes here for Postgres 9.4 either ...


Cannot connect to MySQL sever with MySQL Workbench

When you specify localhost as the host to connect to, the mysql commandline client tries to connect using a local socket. To enforce a tcp connection you have to either set the host to 127.0.0.1 or use the --protocol=tcp parameter.

Your server is not configured to accept network connections so you have to switch to change your connection method to Socket/Pipe Path and set the path to the socket, something like /var/run/mysqld/mysqld.sock. You can check yours in the config or by running select @@socket; in the commandline connection. \s used as command in the CLI will tell you that too and some more info about your server.


Product table with possible duplicate part numbers from different manufacturers

A common solution to this problem is composite keys or indices. You can specify any key or index as unique, so combining the PartNumber and ManufacturerID into a composite and then marking it as unique has the effect of enforcing the uniqueness of each combination of PartNumber and ManufacturerID. See this thread on SO for more information.


If you dropped a table with innodb_file_per_table ON it's better not to undelete the .ibd file, but rather scan the disk partition with page_parser from percona data recovery toolkit.

The ibd files after most certainly corrupt and you won't be able to IMPORT TABLESPACE. Another problem is undelete tools usually (every time I've seen actually) misses chunks of data for sake of reconstructing a file.

To quickly check what's inside the undeleted ibd and see how many records lost/recovered I can suggest https://recovery.twindb.com (DISCLAIMER - I'm the author). But, again, I'd parse the partition.


SQL Server Database recovery from corrupt database

I think you're running the restore with the NO RECOVERY option. This will restore your database, but will leave it in the RECOVERY state.

You can easily check this by taking a look at it in the SSMS. It should say something like: yourDatabaseName (Recovering...)

If you haven't the option to take a look in your SSMS, you can simply querying your master database (if your restore doesn't aim at your master database at all. :-D).

SELECT name, state_desc
FROM sys.databases

You can filter it by name for example. Take a look at the column state_desc it should say something like RECOVERING.

If this is the case and your restore is successfully. You can simply run this:

RESTORE DATABASE yourDatabase WITH RECOVERY

This will immediately remove the RECOVERING state from your database and set it back to ONLINE.

Behind the scenes:

RECOVERING is just a transition state from (for example) RESTORING to ONLINE. In most cases a restore using WITH RECOVERY is good enough to set the database back online after the successful restore. But in some cases (e.g. Mirroring) you need to restore a copy of a database on a different machine and leave it in the RECOVERING state to define it as the mirroring partner for your PRINCIPAL database.


Since I can't see your table structure and I don't know the size of your data, here's something that will return the right answer but may or may not need to be tweaked for performance. You can write a query that groups both created and closed tickets together by date.  Although you could just write two queries and do a full outer join, I would guess you want SSRS to accurately represent days with no ticket activity in addition to filtering the data for the date range at the source instead of in the presentation layer, so I would suggest creating a date table/CTE/query. If you already have a date table, use it.  If not, here is a way to create what you need for the last 30 days. You could also make it a stored procedure and parameterize it to choose the number of days.

WITH X AS
(
  SELECT TOP (30) n = ROW_NUMBER() OVER (ORDER BY m1.number)-1
  FROM [master].dbo.spt_values AS m1
  CROSS JOIN [master].dbo.spt_values AS m2
)
,last30days as (
SELECT dt = cast(DATEADD(DAY, (-1*n), getdate()) as date)
  FROM X
)
select dt.dt, tcr.ticketscreated, tcl.ticketsclosed
from last30days dt
  left join (select cast(createddate as date) createdate, count(ticketid) ticketscreated
            from dbo.tickets
            group by cast(createddate as date)) tcr on dt.dt = tcr.createdate
  left join (select cast(closeddate as date) closedate, count(ticketid) ticketsclosed
            from dbo.tickets
            where closeddate is not null
            group by cast(closeddate as date) ) tcl on dt.dt = tcl.closedate

Once you have added that query as a dataset, you can build your chart.

Values should be Sum(ticketscreated) and Sum(ticketsclosed)
Category Groups should be dt


Syntax for using Oracle Linked Server with USE keyword

The USE command changes the database context to the specified database or database snapshot in SQL Server. I believe there should be a query with matching columns after your INSERT statement that would be the source for the INSERT. It may be OPENQUERY and could look like the following:

Insert into LHR_MIGRATION.dbo.Z_MIGRATION_TABLE
    (AMOUNT_TYPE
    ,BUSINESS_UNIT
    ,LEDGER
    ,ACCOUNT
    ,DESCR
    ,DEPTID
    ,DESCR_1
    ,PRODUCT
    ,JOURNAL_DATE
    ,JOURNAL_ID
    ,CURRENCY_CD
    ,MONETARY_AMOUNT
    ,FOREIGN_CURRENCY
    ,AFFILIATE
    ,PROJECT_ID
    ,LINE_DESCR
    ,DESCR254
    ,DOC_TYPE
    ,DOC_SEQ_NBR
    ,JRNL_LINE_SOURCE
    ,JRNL_LN_REF
    ,JOURNAL_LINE
    ,STATISTIC_AMOUNT
    )
SELECT AMOUNT_TYPE
    ,BUSINESS_UNIT
    ,LEDGER
    ,ACCOUNT
    ,DESCR
    ,DEPTID
    ,DESCR_1
    ,PRODUCT
    ,JOURNAL_DATE
    ,JOURNAL_ID
    ,CURRENCY_CD
    ,MONETARY_AMOUNT
    ,FOREIGN_CURRENCY
    ,AFFILIATE
    ,PROJECT_ID
    ,LINE_DESCR
    ,DESCR254
    ,DOC_TYPE
    ,DOC_SEQ_NBR
    ,JRNL_LINE_SOURCE
    ,JRNL_LN_REF
    ,JOURNAL_LINE
    ,STATISTIC_AMOUNT
FROM OPENQUERY(LinkServerName,'SELECT AMOUNT_TYPE
    ,BUSINESS_UNIT
    ,LEDGER
    ,ACCOUNT
    ,DESCR
    ,DEPTID
    ,DESCR_1
    ,PRODUCT
    ,JOURNAL_DATE
    ,JOURNAL_ID
    ,CURRENCY_CD
    ,MONETARY_AMOUNT
    ,FOREIGN_CURRENCY
    ,AFFILIATE
    ,PROJECT_ID
    ,LINE_DESCR
    ,DESCR254
    ,DOC_TYPE
    ,DOC_SEQ_NBR
    ,JRNL_LINE_SOURCE
    ,JRNL_LN_REF
    ,JOURNAL_LINE
    ,STATISTIC_AMOUNT
    FROM SourceTableName');

LinkedServerName would be the real linked server name and SourceTableName would be the name of the table in the linked server.


why MySLQ datadir must be owned by mysql user?

When MySQL is running, it runs as the mysql user account. If the data directory was owned by root, MySQL would not have permission to write to it!


Is it "bad design" to have 2 related tables, each one having a column (named differently) whose value will always be the same in both?

In general, keeping redundant data in a relational database is considered a bad design. There are several problems associated with redundant data:

There is the danger that application errors or manual interventions introduce inconsistencies into the database by modifying only one copy of the redundant information. This is the main problem.
Performance is sapped by having to write more data than necessary, or by constraints or other measure that are to ensure consistency of the copies.
You waste storage space. That is usually a minor concern.

These things are reflected on a theoretical level in the concept of database normalization, which establishes rules that aim at avoiding data redundancy (among other things).

So from a performance point of view, data redundancy makes data modifications slower, but it can speed up queries, because the data can be fetched from several locations, which may make some queries simpler and faster.

Sometimes that is a good enough reason to introduce data redundancy (“denormalization”), but you should only do that if it is necessary for performance reasons, and the advantages outweigh the disadvantages listed above.


SELECT multiple sensor values in one query

First things first, I notice that your 'what I do now' query:

SELECT TOP (1)
    ca.SensorValue,
    ca.Date
FROM sys.partitions AS p
CROSS APPLY
(
    SELECT TOP (1)
        v.Date,
        v.SensorValue
    FROM SensorValues AS v
    WHERE
        $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
        AND v.DeviceId = @fDeviceId
        AND v.SensorId = @fSensorId
        AND v.Date <= @fDate
    ORDER BY
        v.Date DESC
) AS ca
WHERE
    p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
    AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
    AND p.index_id = 1
ORDER BY
    p.[partition_number] DESC,
    ca.Date DESC;

...produces an execution plan like this:

This execution plan has an estimated total cost of 0.02 units.  Over 50% of this estimated cost is the final Sort, running in Top-N mode.  Now estimates are just that, but sorts can be expensive in general, so let's remove it without changing the semantics:

SELECT TOP (1)
    ca.SensorId,
    ca.SensorValue,
    ca.Date
FROM
(
    -- Partition numbers
    SELECT DISTINCT
        partition_number = prv.boundary_id
    FROM
        sys.partition_functions AS pf
    JOIN sys.partition_range_values AS prv ON
        prv.function_id = pf.function_id
    WHERE
        pf.name = N'SensorValues_Date_PF'
        AND prv.boundary_id <= $PARTITION.SensorValues_Date_PF(@fDate)
) AS p
CROSS APPLY
    (
    SELECT TOP (1)
        v.Date,
        v.SensorValue,
        v.SensorId
    FROM dbo.SensorValues AS v
    WHERE
        $PARTITION.SensorValues_Date_PF(v.Date) = p.partition_number
        AND v.DeviceId = @fDeviceId
        AND v.SensorId = @fSensorId
        AND v.Date <= @fDate
    ORDER BY
        v.Date DESC
  ) AS ca
ORDER BY
    p.partition_number DESC,
    ca.Date DESC

Now the execution plan has no blocking operators, and no sorts in particular.  The estimated cost of the new query plan below is 0.01 units and the total cost is distributed evenly over the data access methods:

With the improvement in place, all we need to produce a result for each Sensor ID is to make a list of Sensor IDs and APPLY the previous code to each one:

SELECT
    PerSensor.SensorId,
    PerSensor.SensorValue,
    PerSensor.Date
FROM
(
    -- Sensor ID list
    VALUES
        (@fSensorId1),
        (@FSensorId2),
        (@FSensorId3)
) AS Sensor (Id)
CROSS APPLY
(
    -- Optimized code applied to each sensor
    SELECT TOP (1)
        ca.SensorId,
        ca.SensorValue,
        ca.Date
    FROM
    (
        -- Partition numbers
        SELECT DISTINCT
            partition_number = prv.boundary_id
        FROM
            sys.partition_functions AS pf
        JOIN sys.partition_range_values AS prv ON
            prv.function_id = pf.function_id
        WHERE
            pf.name = N'SensorValues_Date_PF'
            AND prv.boundary_id <= $PARTITION.SensorValues_Date_PF(@fDate)
    ) AS p
    CROSS APPLY
        (
        SELECT TOP (1)
            v.Date,
            v.SensorValue,
            v.SensorId
        FROM dbo.SensorValues AS v
        WHERE
            $PARTITION.SensorValues_Date_PF(v.Date) = p.partition_number
            AND v.DeviceId = @fDeviceId
            AND v.SensorId = Sensor.Id--@fSensorId1
            AND v.Date <= @fDate
        ORDER BY
            v.Date DESC
      ) AS ca
    ORDER BY
        p.partition_number DESC,
        ca.Date DESC
) AS PerSensor;

The query plan is:

Estimated query plan cost for three Sensor IDs is 0.011 - half that of the original single-sensor plan.


SSIS: script task on Visual studio 2013 can not open since Visual Studio 2015 was installed

I have VS 2012, 2013, 2015 installed and faced the same issue.
After looking in the Event viewer it was apparent that it was 2015 VSTA causing the issue.

This solved it for me without re-installing everything....

Uninstalling:

Microsoft Visual Studio Tools For Applications 2015
Microsoft Visual Studio Tools For Applications 2015 Language Support

Repairing:

Microsoft Visual Studio Tools For Applications 2012


Getting Error 10054 after renaming the server

See the Microsoft Docs article Rename a Computer that Hosts a Stand-Alone Instance of SQL Server.

You'll need to restart the SQL Server service, and use sp_dropserver / sp_addserver to update the server name within SQL Server.


Update table using values from another table in SQL Server

There are quite a few ways to achieve your desired results.

Undeterministic methods

(in the event that many rows in table 2 match one in table 1)

UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       JOIN #Table2 T2
         ON T1.gender = T2.gender
            AND T1.birthdate = T2.birthdate

Or a slightly more concise form

UPDATE #Table1
SET    address = #Table2.address,
       phone2 = #Table2.phone
FROM   #Table2
WHERE  #Table2.gender = #Table1.gender
       AND #Table2.birthdate = #Table1.birthdate

Or with a CTE

WITH CTE
     AS (SELECT T1.address AS tgt_address,
                T1.phone2  AS tgt_phone,
                T2.address AS source_address,
                T2.phone   AS source_phone
         FROM   #Table1 T1
                INNER JOIN #Table2 T2
                  ON T1.gender = T2.gender
                     AND T1.birthdate = T2.birthdate)
UPDATE CTE
SET    tgt_address = source_address,
       tgt_phone = source_phone

Deterministic methods

MERGE would throw an error rather than accept non deterministic results

MERGE #Table1 T1
USING #Table2 T2
ON T1.gender = T2.gender
   AND T1.birthdate = T2.birthdate
WHEN MATCHED THEN
  UPDATE SET address = T2.address,
             phone2 = T2.phone;

Or you could pick a specific record if there is more than one match

With APPLY

UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       CROSS APPLY (SELECT TOP 1 *
                    FROM   #Table2 T2
                    WHERE  T1.gender = T2.gender
                           AND T1.birthdate = T2.birthdate
                    ORDER  BY T2.PrimaryKey) T2

.. Or a CTE

WITH T2
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gender, birthdate ORDER BY primarykey) AS RN
         FROM   #Table2)
UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       JOIN T2
         ON T1.gender = T2.gender
            AND T1.birthdate = T2.birthdate
            AND T2.RN = 1;


Each variable-width column in a row will require a single bit to maintain the nullability status (this is so SQL Server can check this bit, rather than skipping down to the row and parsing the contents of that column). Of course you can't add a single bit to a row, you need to add a byte for this data, which means that if you have a single variable-width column, there is a byte of overhead, and that byte can be used to store the nullable status of up to 8 columns (I haven't tested if there are row configurations where multiple but < 8 variable-width columns may still use different nullable bytes).

Note: there is other overhead for variable-width columns, such as length, but I believe in general that that overhead is the same whether the column is nullable or not.


Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI"

Let's see about the possibilities. The first one is that server's collation has changed. This is very unlikely, as it requires rebuilding system databases. Unless you have extremely dynamic environment with multiple admins doing stuff in chaotic manner, I wouldn't consider this further.

First and one half: is it possible that the database has been moved to another instance? If you are using client-side aliases, the change can be transparent.

The second one is that database's collation has changed. This is quite straight-forward, all one needs is an alter database command. So, check the database's current collation and expected collation. Do they match?

The third one is that the stored procedure itself has been modified or renamed. Maybe there used to be explicit collation specification, but that was dropped. Do you have sprocs in version control?

To see the last modify dates for stored procedures, query sys.procedures

SELECT create_date, modify_date, name
FROM sys.procedures;

As far as I know, without auditing it's hard to catch database changes after the fact. If you are using some kind of CI system, double-check if the database - or sprocs - were re-deployed recently.

It would be worth a shot to chat with other server admins and developers too.


Go ahead and create a dimension.  It may sound strange at first, but think of the end user who ends up selecting a radio button for "male/female", or "sold/unsold", etc.


Find Stored procedure which are using temp tables

Unfortunately, SQL Server doesn't store any type of metadata or dependency information about temp tables potentially created inside of them, so you'll have to parse. A first pass could be:

SELECT [schema] = s.name,
  [procedure] = p.name
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
ON p.[schema_id] = s.[schema_id]
WHERE OBJECT_DEFINITION(p.[object_id]) LIKE N'%#%';

You'll get some false positives, probably. You can tighten down the search space with various approaches, like:

LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%create%table%#%';
OR LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%select%into%#%';

But those things could still appear in a comment or in multiple completely unrelated statements.


alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));


ALTER TABLE on a large table with an indexed column

If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see. Suppose the table you want to change is called WorkingTable. You could perform the changes in stages like this:

#
#  Script 1
#  Alter table structure of a single column of a large table
#
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

You can perform this on all slaves. What about the master ??? How do you prevent this from replicating to the slaves. Simple: Don't send the SQL into the master's binary logs. Simply shut off binary logging in the session before doing the ALTER TABLE stuff:

#
#  Script 2
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#
SET SQL_LOG_BIN = 0;
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

But wait !!! What about any new data that comes in while processing these commands ??? Renaming the table in the beginning of the operation should do the trick. Let alter this code a little to prevent entering new data in that respect:

#
#  Script 3
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#  and preventing new data from entering into the old table
#
SET SQL_LOG_BIN = 0;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
CREATE TABLE WorkingTableNew LIKE WorkingTableOld;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

Script 1 can be executed on any slave that do not have binary logs
enabled
Script 2 can be executed on any slave that does have binary
logs enabled
Script 3 can be executed on a master or anywhere else

Give it a Try !!!


Why does Recursive CTE estimate just 1 row?

Cardinality estimation for recursive common table expressions is extremely limited.

Under the original cardinality estimation model, the estimate is a simple sum of the cardinality estimates for the anchor and recursive parts. This is equivalent to assuming the recursive part is executed exactly once.

In SQL Server 2014, with the new cardinality estimation model enabled, the logic is slightly changed to assume three executions of the recursive part, with the same number of rows returned by each iteration.

Both of these are uneducated guesses, so it is no surprise that the use of recursive CTes usually results in poor quality estimations. More generally, estimating the result of a recursive process is nigh-on impossible, so the optimizer doesn't even try. This is not changed by using a particularly simple recursive structure, clearly intended to produce sequential numbers - the optimizer has no logic to detect this pattern.

In your particular case, the final estimation is one because the optimizer makes further guesses about the selectivity of predicates like [Recr1007]<(100) (in the Filter at Node ID 3) and ([Recr1003]*(100)+[Recr1007])<=(10000) (the residual predicate on the Nested Loops Join at Node ID 2). Again, these are guesses, and the results are unfortunate, though not surprising.

  Is there anyway to improve the estimate while keeping the CTE approach?

Not that I am aware of.

  Has there ever been a request for a "with (AssumeMinRows=N)" hint?

Not directly in those terms. There have been plenty of requests for materializing a CTE, which would help if such materialization came with automatic statistics generation. I won't list the others because you seem to have commented on most of those suggestions already :)

There have also been suggestions for selectivity hints, but nothing like this has made its way into the product yet.

As noted in comments on the question, your best bet right now is to use a real numbers table instead of generating one on-the-fly using a recursive CTE. A second option is to use manual materialization - a temporary table - as I am sure you are aware.


Changing number of rows when importing table

The answer is innodb only estimates number of rows by command show table status. Please refer to http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html for further explanation.


Evidently, "root" is not a superuser (which is a bit odd, given the normal implications of the word "root").  The pg_restore command you wish to run must be run as a superuser.  Only a superuser can drop someone else's database, and then create a new database owned by someone else.

Although it looks like the restored database is going to be owned by "root", not "u".  I guess that is not what you are expecting, but it is what was encoded in the pg_dump output.


It isn't like there is a rule for the optimizer choosing the index with the very same columns that the query predicates have.

You should always use your bind variables with the proper type. No, in this specific case, those types do not prevent the optimizer from using your index.

Below is just an artificially constructed example. I do not know your table/index structure and data distribution.

drop table t1 purge;
create table t1(c1 nchar(32), c2 number, c3 nchar(32), c4 number);
insert into t1 with g as (select * from dual connect by level <= 1000) select N'HELLO', mod(rownum, 1000), N'WORLD' || rownum, rownum from g,g;
alter table t1 add constraint t1_pk primary key (c1, c2, c3) using index;
create index t1_i1 on t1(c1);
create index t1_i2 on t1(c2);
create index t1_i3 on t1(c3);
exec dbms_stats.gather_table_stats(user, 'T1');

With the above sample data, we have 1 million rows, with C1 having the same value everywhere, C2 having 1000 uniformly distributed different values and C3 being unique (with no constraint on it).

alter session set statistics_level=all;
variable b1 varchar2(32)
variable b2 varchar2(32)
variable b3 varchar2(32)
exec :b1 := 'HELLO';
exec :b2 := '500';
exec :b3 := 'WORLD500';

select c4 from t1 where c1 = :b1 and c2 = :b2 and c3 = :b3;
select * from table(dbms_xplan.display_cursor(format=>'allstats last cost +peeked_binds'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  3r9y8nvz3vdtq, child number 0
-------------------------------------
select c4 from t1 where c1 = :b1 and c2 = :b2 and c3 = :b3

Plan hash value: 3324024074

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |        |     4 (100)|      0 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |      1 |     4   (0)|      0 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | T1_I3 |      1 |      1 |     3   (0)|      0 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'HELLO'
   2 - :2 (VARCHAR2(30), CSID=873): '500'
   3 - (VARCHAR2(30), CSID=873): 'WORLD500'

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

   1 - filter(("C2"=TO_NUMBER(:B2) AND "C1"=SYS_OP_C2C(:B1)))
   2 - access("C3"=SYS_OP_C2C(:B3))

Notice in Peeked Binds section the type of the variables (VARCHAR2), and in the Predicate Information section how they were automatically converted with the SYS_OP_C2C and TO_NUMBER functions. This implicit conversion in this case does not prevent index usage, but you will not be so lucky with other implicit conversions. (Also notice how I got 0 row as result, while I should have got 1 row as result with those values - but that is a different topic.)

The optimizer chose the index on column C3 instead of the PK index.
The cost of the query is 4. So the cost of the PK index should be higher. Let's see with a hint:

explain plan set statement_id = 'PK_HINT' for select /*+ INDEX(t1 t1_pk) */ c4 from t1 where c1 = :b1 and c2 = :b2 and c3 = :b3;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 2347959165

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   139 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   139 |     4   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("C1"=SYS_OP_C2C(:B1) AND "C2"=TO_NUMBER(:B2) AND
              "C3"=SYS_OP_C2C(:B3))

The usage of PK index is possible, but still, the optimizer did not choose it. Now most people would say their cost is the same (4), but they would be wrong. Such execution plans do not show all the details. We can check those from our plan table (or V$SQL_PLAN). I have already placed the plan of the PK index version there with the above EXPLAIN PLAN statement, now do it for the original as well:

explain plan set statement_id = 'NO_HINT' for select c4 from t1 where c1 = :b1 and c2 = :b2 and c3 = :b3;

We have both plans in the plan table. The important factor not being displayed is the CPU cost:

break on statement_id
select STATEMENT_ID, OPERATION, OPTIONS, OBJECT_NAME, COST, IO_COST, CPU_COST from plan_table order by STATEMENT_ID, ID;

STATEMENT_ID OPERATION            OPTIONS                OBJECT_NAME COST IO_COST CPU_COST
------------ -------------------- ---------------------- ----------- ---- ------- --------
NO_HINT      SELECT STATEMENT                                           4       4    29046
             TABLE ACCESS         BY INDEX ROWID BATCHED T1             4       4    29046
             INDEX                RANGE SCAN             T1_I3          3       3    21564
PK_HINT      SELECT STATEMENT                                           4       4    29746
             TABLE ACCESS         BY INDEX ROWID         T1             4       4    29746
             INDEX                UNIQUE SCAN            T1_PK          3       3    22414

Notice how the COST equals to IO_COST.  However, the CPU COST of using the PK index is higher than of the single column index. Simply put, even if the IO cost is the same, processing more columns requires more CPU time.

Also it is a logical decision, because in this example C3 alone is already unique, so using a smaller index with fewer columns, even if not significantly, should be faster.

When in doubt, with so simple statements and low query cost, you should inspect the details as well. If you have a similarly very selective column, this could be the reason for the optimizer choosing the index on it.


Portable client tool to connect to SQL Server Express LocalDB and execute a query

I suggest just writing a simple .NET console app to do just that. It can accept parameters for "ConnectionString" and "Query". This will be flexible enough to use in various situations.

using System;
using System.Data;
using System.Data.SqlClient;

namespace SimpleSqlCmd
{
    class Program
    {
        static void Main(string[] args)
        {
            using(SqlConnection _Connection = new SqlConnection(args[0]))
            {
                using(SqlCommand _Command = new SqlCommand(args[1], _Connection))
                {
                   _Connection.Open();

                   _Command.ExecuteNonQuery();
                }
            }

            return;
        }
    }
}

Usage would be:

SimpleSqlCmd "Server=(localdb)\InstanceName;" "Do some SQLs;"

UPDATE:

I have created a project on GitHub to host a more functional / complete version of the code shown above. The code on GitHub will be updated over time whereas the code here will not be updated (since that is really not the purpose of this site). The repository URL for Simple SQL Exec (renamed, to avoid confusion, from "SimpleSqlCmd" as it is shown above):

https://github.com/SqlQuantumLeap/SimpleSqlExec


Since asking the question, I have found these two very nice web-based solutions:
https://dbdiagram.io/home
https://alternativeto.net/software/quickdbd/
and a basic one:
https://dshifflet.com/erd/index.html

Unfortunately due to security concerns, I am not allowed to use 3rd party cloud solutions. It's gotta be a standalone desktop software.

I also discovered ERAlchemy and it looks like I can manually generate ERDiagrams in Typora using markdown.

Any other useful ideas?


Answer to all you questions is YES.
You can just add (rs.add()) new nodes to RS and data will be synced. Faster way to do that is create those new nodes from backup/snapshot of old node and then rs.add().

What comes to upgrade, if you are just upgrading to new version of MongoD, there is no need to prepare new nodes and then add them to RS, but of course you can do that too. Proper procedure is just upgrade software at secondary node (one node at time) and then just "step down" primary and upgrade that node too.

What ever procedure you choose, there is no downtime.


Delete word, its meanings, its meaning's example sentences from DB

Use the cascade deletion.
Does SQLite support ON DELETE CASCADE declaration for FOREIGN KEY?


What's the fastest way to find the first 2 purchases for every customer in Redshift?

Assuming a separate customers table, and a compound index on orders(customer_email, purchase_date), something like this will do it:

select
  customer_email,
  array(
    select order_id
    from orders
    where customer_email = c.customer_email
    order by purchase_date
    limit 2
  ) as order_ids
from customers c;


Why most of Sql Injection need url like this "index.asp?id=123"

When you see URL's that have that kind of syntax id=123, there is a good chance the underlying code will use 123 as input to some kind of dynamic SQL statement.  Unless the underlying code is written to prevent SQL injection, you can gain additional information from the database (or cause harm) by simply adding additional information to the url directly in the browser window.

Have a look at SQL Injection Walkthrough

What is SQL Injection?
It is a trick to inject SQL query/command as an input possibly via web pages. Many web pages take parameters from web user, and make SQL query to the database. Take for instance when a user login, web page that user name and password and make SQL query to the database to check if a user has valid name and password. With SQL Injection, it is possible for us to send crafted user name and/or password field that will change the SQL query and thus grant us something else.

What do you need?
Any web browser.

Try to look especially for URL that takes parameters, like:

http://duck/index.asp?id=10

There are numerous examples of SQL injection in the post I referenced above.


Unable to find Audit specification when I try to create Server level audit

We can't troubleshoot pointing and clicking through a GUI, because we don't know exactly what you did or what you might have missed.

You need to create a server level audit (which is like a container for specific audit definitions) and a server level audit specification (which is the part that dictates the auditing).

I suggest looking at the documentation for examples using DDL (e.g. CREATE SERVER AUDIT and CREATE SERVER AUDIT SPECIFICATION). This wasn't too hard to work out:

USE master;
GO

CREATE SERVER AUDIT ServerAudit
TO FILE
(
    FILEPATH = N'C:\temp\',
    MAXSIZE = 0 MB,
    MAX_ROLLOVER_FILES = 2147483647,
    RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE,
    AUDIT_GUID = '0da5fd7b-8218-486f-8641-c31465cae657'
);
GO

ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION [AuditPrincipals]
FOR SERVER AUDIT [ServerAudit]
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON);
GO

So then I did this:

CREATE LOGIN floob WITH PASSWORD = 'blat', CHECK_POLICY = OFF;

And I saw this in the audit log:


Oracle regular expression replace in string ignoring first and last characters

This returns the answer you want:

SELECT REGEXP_REPLACE
('TLYU TK0T23T', '([^(\A.)])T([^(.\Z)])', '\1Qq\2' ) contain
FROM dual;

Bobby


Oracle client in Windows 7: how to change looking path for sqlnet.ora

Did you try to set the environment variable TNS_ADMIN to the directory in which you have your sqlnet.ora file?


Set user variables from tables being queried in MySQL without locking

Your first function can be even simpler by noting that

SELECT 0 + DATE('2017-01-18 08:31:38'); -- 20170118

But that is not usable for arithmetic.  Note that this time next month, 20170218, would be "100 days" from now:

For the second one, look into DATEDIFF().  (No need to convert from DATETIME to DATE.)

See also the TO_DAYS() and FROM_DAYS() functions.  Today is 736712 and would work with aritmetic.

I suspect that all of this will eliminate the need for dimDate, thereby solving your stated problem??  If not sufficient, please provide SHOW CREATE TABLE for each table involved; we need to see what indexes you currently have.


Increment value in row if it exists, otherwise create row with value?

INSERT INTO mytable (ID,`key`,`value`) VALUES (1106,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

and

INSERT INTO mytable (ID,`key`,`value`) VALUES (1107,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

If your table has value defined as

CREATE TABLE mytable
(
   ...
   value INT DEFAULT 1,
   ...
);

then the INSERTs would be

INSERT INTO mytable (ID,`key`) VALUES (1106,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

and

INSERT INTO mytable (ID,`key`) VALUES (1107,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;


A Galera node's memory usage is very similar to a normal MySQL/MariaDB standalone server. If 128GB is enough for a standalone server to handle your workload, it will be ok for a Galera node too.

Of course you can also have server with different characteristics. In which case, you will also need a load balancer that will take into account the servers "weight". You can use MaxScale, for example.


pg_dump custom archive and create backup date

You can use pg_restore -l <custom_dump_file> and the output should start with something like the following:

;
; Archive created at 2019-02-13 22:59:59 UTC
;     dbname: <database_name>
;     TOC Entries: 2615
;     Compression: -1
;     Dump Version: 1.13-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 10.6 (Ubuntu 10.6-1.pgdg16.04+1)
;     Dumped by pg_dump version: 11.1 (Ubuntu 11.1-3.pgdg16.04+1)
;

As you see, the "Archived created at" line is probably what you're looking for.


mysqldump --no-data and --single-transaction

If you only want a dump of the schema there is really no point in using the --single-transaction option. This option is more useful towards obtaining consistent data from transactional tables. In fact, while using this option, the dump is still vulnerable to being corrupt if any of the following statements are executed: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.

You can read more on the description for this option on the MySQL Documentation for 5.0.


How to increase the maximum number of control files?

It is clearly documented that the parameter can not have more than 8 values.

CONTROL_FILES

  Range of values:  1 to 8 filenames


That is for DCD (Dead Connection Detection).

Dead Connection Detection

SQLNET.EXPIRE_TIME

If a client suddenly "disappears", e.g it's powered off, or loses network, the connection still remains open on the database server, with the corresponding session as well.

When the above parameter is set, the database probes the connections to check if they are still alive. The above parameter controls the frequency of this check (in minutes).

It is a common misconception that setting this parameter to 10 results in any dead session being cleaned up in at most 10 minutes, but that is not how it works, read the above PDF for explanation.


How to have multiple condition on one column

SELECT NAME
FROM STUDENTS s
WHERE EXISTS (SELECT 1 FROM GRADES WHERE ID = s.ID AND GRADE = 2)
    AND EXISTS (SELECT 1 FROM GRADES WHERE ID = s.ID AND GRADE = 6)

or if you don't have multiple records for some grades for particular users:
SELECT NAME
FROM STUDENTS
JOIN GRADES ON STUDENTS.ID = GRADES.ID
WHERE GRADE = 2 OR GRADE = 6
GROUP BY ID, NAME
HAVING COUNT(1) = 2


Dump roles on AWS without pg_dumpall -g access (postgresql 9.6.8)?

It turns out the 10.0 solution was backported (apparently) to 9.6.8, so:

pg_dumpall --host <aws_endpoint> --globals-only --no-role-passwords \
    --username masteruser >roles.sql

Then run roles.sql against the new database.


Postgresql 9.3 GROUP BY on an indexed column in 30m rows table taking a long time

Looking at the number of buffers it took to read the index, you are probably suffering from table and index bloat.

If you can afford to lock the table, run vacuum full analyze gpsdata to clean up old data.

There are several possible reasons why auto-vacuum did not take care of cleaning up properly and re-using deleted space:

You have many sessions accessing that table that are in the state "idle in transaction". Rows that are used by a transaction can't be cleaned up. Make sure you properly commit all transactions - that includes SELECT statement which also start a transaction.  The easiest way to do that is to enable autocommit (default in psql)
You have a high frequency of updates on that table so that the table is constantly changed and auto-vacuum can't keep up (or doesn't get a chance to clean up - see 1.)
you have disabled auto-vacuum

An index on gpsdata (equipmentid,id) might speed up the calculation.


Select distinct value from row if condition exists

How about a little 2 in 1 using CTE?

;with cte as (
    select top 1 loc = 1
    from parts
    where Location = 'A1'
)
select distinct Part --, Type, Location
from parts left join cte on 1=1
where loc is null or Location = 'A1';


Are there better alternatives rather than using a "Full outer join" when it is not possible to use a "left or right join"?

If you need to use a full outer join, use a full outer join.  It exists for a reason.  In general, though, it should be an uncommon tool in the toolbox.

When designing systems, you generally try to minimize the number of outer joins that you need to do.  In this case, it would seem to make more sense to have a general transaction table that both modern_transaction and card_transaction are children of.  Then you'd just need to do a left outer join to both children from the parent.

A full outer join isn't going to produce false results.  It will produce exactly the results you ask for.  As with any code, however, that may not be the results you actually want.  In your case, what is the primary key of each table?  Eeffective_date is a date and dates always store time down to the second.  Are you storing the actual date there?  Or are you truncating the date and storing the time as midnight?

If the primary key is Eeffective_date, customer_num, branch_code and you are storing the time as midnight, that implies that each customer can only do one of each transaction at each branch every day.  It seems unlikely that a customer couldn't do two transactions in one day.
If the primary key is Eeffective_date, customer_num, branch_code and you are storing the actual time, then customers can do multiple transactions every day but it would be likely that some transactions that should match wouldn't match in your join because one table was inserted into with a value just before the change of a second and the other was inserted into with a value just after the change.
If the primary key is something else, then a single row from t1 might match multiple rows from t2 and vice versa.  The full outer join will do exactly what you're asking but it is extremely rare that this would be what you want to happen.


System DSN for SQL Server to connect through ODBC with for a specific port

To specify a non-default port number, add a comma and the port number after the server name. For example, with a port number of 12345:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\SQLtest]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="SQLtest"
"Server"="BLR002,12345"
"Database"="Maint"
"LastUser"="sa"

If the SQL instance is a named instance (named MAINT) like the DSN in your question shows, you should be able to connect using with your original DSN specification instead of port as long as the SQL Server Browser service is running on the server, UDP port 1434 allowed through the firewall on the server, and the instance port allowed as well.


You might have something like this. If the post is a new topic, ParentID and ReplyToID would be NULL. For forums it is often desirable to show when a post is a reply to another post, but still indicate which topic it belongs to, so a second column (ReplyToID) can indicate this. When it is a reply to the original parent this can be NULL or have the same value as ParentID.

CREATE TABLE dbo.Posts
(
    PostID      INT IDENTITY(1,1) PRIMARY KEY,
    UserID      INT NOT NULL FOREIGN KEY REFERENCES dbo.Users(UserID),
    ParentID    INT NULL REFERENCES dbo.Posts(PostID),
    ReplyToID   INT NULL REFERENCES dbo.Posts(PostID),
    Content     NVARCHAR(MAX), -- more accurate than "description"

    -- ... other columns ...

    -- "DateTime" is not a good choice for a column name
    create_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modify_date DATETIME NULL -- or same default as create_date
);


How to connect once for all queries in SQL Server Management Studio?

Click View --> Registered Servers
Right-click Local Server Groups --> New Server Registration

Then you can store the connection info, name it what you want, and just double-click then when you want to use it.


Programmatically determine whether a database was shut down cleanly or whether it required crash recovery

Crash recovery will be used every time SQL Server is restarted. So I am assuming that when you take it offline cleanly means no transactions are rolled back. That information can be found in the error log. We can write code to read the error log based on timestamp and read the logs to see which database transactions were rolled back

EX:

Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2015-01-05 01:15:48.39 Server      Software Usage Metrics is disabled.
2015-01-05 01:15:48.39 spid4s      Starting up database 'master'.
2015-01-05 01:15:48.50 spid4s      140 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2015-01-05 01:15:48.50 Server      CLR version v4.0.30319 loaded.
2015-01-05 01:15:48.60 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2015-01-05 01:15:48.60 spid4s      0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.


Does a full transaction log cause a rollback?

Yes but it will be the rollback which actually causes the log to become physically full.

As a transaction generates log records, it will also reserve space in the log for any records required required to rollback. With 20MB of log space remaining and a hypothetical transaction that generates 10MB of log records, you would receive the log full error with 10MB still free and the rollback would then consume that 10MB.


I've solved 2. using sub-querys

SELECT
  tr1.id AS id,
  dsv.idSite_id AS idSite,
  tr1.idSiteVersion_id AS idSiteVersion,
  tr1.idTest_id AS idTest,
  tr1.result AS result,
  tr1.timeStarted AS timeStarted,
FROM
  dash.testresult AS tr1
  NATURAL JOIN (
    SELECT idTest_id, idSiteVersion_id, MAX(timeEnd) AS timeEnd
    FROM dash.testresult
    WHERE timeEnd IS NOT NULL
    GROUP BY idTest_id, idSiteVersion_id
  ) AS tr2
  JOIN dash.siteversion AS dsv
    ON dsv.id = tr1.idSiteVersion_id
  JOIN dash.test AS dtest
    ON dtest.id = tr1.idTest_id AND dtest.ignored = 0


MySQL Group Replication fails to understand "::ffff:" IP Address

That output is done in the most generic possible way, using IPv4-mapped IPv6 address.

It should work if you simply add:

group_replication_ip_whitelist="10.12.1.2";


verify that mongos server is connected to config servers

Have you tried using the sh.startBalancer() helper instead?

Rather than a straight update, it does takes an timeout argument as how long to wait for balancing to start as well as a sleep interval in terms of how long to sleep between waiting.  Here's the code from the shell by way of explanation:

mongos> sh.startBalancer
function ( timeout, interval ) {
    sh.setBalancerState( true )
    sh.waitForBalancer( true, timeout, interval )
}

So, you could even break it up and use the waitForBalancer helper if you wished.  For reference, here is the equivalent stopBalancer command erroring out when I tried to stop it with a config server down:

mongos> sh.stopBalancer(2000, 100)
Waiting for active hosts...
Waiting for active host adamc-mbp.local:30999 to recognize new settings... (ping : Tue Dec 31 2013 19:51:32 GMT+0000 (GMT))
Waiting for the balancer lock...
Waiting again for active hosts after balancer is off...
Tue Dec 31 19:51:39.243 error: {
    "$err" : "error creating initial database config information :: caused by :: SyncClusterConnection::udpate prepare failed:  localhost:29000:9001 socket exception [FAILED_STATE] server [localhost:29000] ",
    "code" : 8005
} at src/mongo/shell/query.js:128


Can PostgreSQL use multiple partial indexes per query?

Very short version: Yes, sometimes.

PostgreSQL can use bitmap index scans to combine multiple indexes.

A predicate like

WHERE a > 50 AND a < 50000

is a specialisation of the more general form:

wHERE a > 50 and b < 50000

for a = b.

PostgreSQL can use two indexes here, one for each part of the predicate, and then bitmap AND them. It doesn't matter if they happen to be on different ranges of the same column.

This is much less efficient than a single index, and may not be useful for some queries, but it's possible.

The bigger problem is that PostgreSQL's partial index support is not very bright. Irrespective of whether there's one or two indexes it might just not figure out that it can use the index at all.

Demonstration setup:

CREATE TABLE partial (x integer, y integer);
CREATE INDEX xs_above_50 ON partial(x) WHERE (x > 50);
CREATE INDEX xs_below_50000 ON partial(x) WHERE (x < 5000);
INSERT INTO partial(x,y) SELECT a, a FROM generate_series(1,100000) a;

OK, what will Pg prefer for given queries?

regress=> EXPLAIN SELECT y FROM partial WHERE x > 50 AND x < 50000;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using xs_above_50 on partial  (cost=0.29..1788.47 rows=50309 width=4)
   Index Cond: ((x > 50) AND (x < 50000))
(2 rows)

regress=> EXPLAIN SELECT y FROM partial WHERE x > 20 AND x < 50000;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on partial  (cost=0.00..1943.00 rows=50339 width=4)
   Filter: ((x > 20) AND (x < 50000))
(2 rows)

regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 AND x < 50000;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using xs_above_50 on partial  (cost=0.29..1787.45 rows=50258 width=4)
   Index Cond: ((x > 100) AND (x < 50000))
(2 rows)

regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 AND x < 20000;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using xs_above_50 on partial  (cost=0.29..710.71 rows=19921 width=4)
   Index Cond: ((x > 100) AND (x < 20000))
(2 rows)

What if we try to force a bitmap index scan just to find out if Pg can use one, even if it's not worth doing for this particular simple case and small sample?

Try:

regress=> SET enable_seqscan  = off;
SET
regress=> SET enable_indexscan  = off;
SET
regress=> SET enable_indexonlyscan  = off;
SET
regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 AND x < 20000;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on partial  (cost=424.48..1166.30 rows=19921 width=4)
   Recheck Cond: ((x > 100) AND (x < 20000))
   ->  Bitmap Index Scan on xs_above_50  (cost=0.00..419.50 rows=19921 width=0)
         Index Cond: ((x > 100) AND (x < 20000))
(4 rows)

Hm. Nope. Not combining the indexes there. It might be able to but simply not think it's worth scanning a second index, though.

What about a query that ORs two predicates instead?

regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 OR x < 200;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on partial  (cost=1905.29..3848.29 rows=99908 width=4)
   Recheck Cond: ((x > 100) OR (x < 200))
   ->  BitmapOr  (cost=1905.29..1905.29 rows=100000 width=0)
         ->  Bitmap Index Scan on xs_above_50  (cost=0.00..1849.60 rows=99908 width=0)
               Index Cond: (x > 100)
         ->  Bitmap Index Scan on xs_below_50000  (cost=0.00..5.73 rows=193 width=0)
               Index Cond: (x < 200)
(7 rows)

Here PostgreSQL has ORed both indexes to find a match, then done a heap scan and recheck.

So yes, PostgreSQL can combine multiple partial indexes, at least for some queries, where it is useful to do so.

But if I RESET the planner overrides...

regress=> RESET enable_seqscan;
RESET
regress=> RESET enable_indexscan ;
RESET
regress=> RESET enable_indexonlyscan ;
RESET
regress=> EXPLAIN SELECT y FROM partial WHERE x > 100 OR x < 200;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on partial  (cost=0.00..1943.00 rows=99908 width=4)
   Filter: ((x > 100) OR (x < 200))
(2 rows)

... Pg will realise it's faster to just sequentially scan the table.


Reorder data in one column based on data in another column

I'm no pro on Oracle, but yo need something like this wrapped in an UPDATE statement,

SELECT *
FROM ( SELECT id, row_number() OVER (ORDER BY id) FROM dummy )
  AS t1(id, rn)
JOIN ( SELECT seq, row_number() OVER (ORDER BY seq) FROM dummy )
  AS t2(seq, rn)
USING (rn);

We look at the two columns id and seq

For id, we sort by id, and return the row number() and id
For seq, we sort by seq, and return the row_number() and seq

We have the same amount of rows for both, so we'll have the same amount of row_number(). Now we just join on row_number(). Tie-handling is undefined here.

You should probably rename seq to priority.

A priority can be static and global depending on the context. The value can matter, as in a priority queue or job scheduler where some priorities hold significance.
A seq by convention usually has gaps so the values don't matter, just the comparison (sometimes the uniqueness).

Here is one attempt at making this approach work in Oracle (dbfiddle):

UPDATE
  test tgt
SET
  seq =
  (
    SELECT sortedBySeq.seq
    FROM (SELECT test.*, ROW_NUMBER() OVER (ORDER BY Id  ASC) AS rn FROM test) sortedById
    JOIN (SELECT test.*, ROW_NUMBER() OVER (ORDER BY seq ASC) AS rn FROM test) sortedByseq
      ON sortedById.rn = sortedBySeq.rn
    WHERE sortedById.Id = tgt.Id
  )
;

Referencing the same table three times probably won't lend itself well to efficiency, but this is something to start with at least.


Ora2pg - migration Oracle db into postgreSQL without instalation of Oracle

DOCUMENTATION

You can use -i:

-i | --input file : File containing Oracle PL/SQL code to convert with
                    no Oracle database connection initiated.

Or set INPUT_FILE in ora2pg.conf:

  INPUT_FILE

  This directive did not control the Oracle database
  connection or unless it purely disable the use of any Oracle database
  by accepting a file as argument. Set this directive to a file
  containing PL/SQL Oracle Code like function, procedure or full package
  body to prevent Ora2Pg from connecting to an Oracle database and just
  apply his convertion tool to the content of the file. This can be used
  with the most of export types: TABLE, TRIGGER, PROCEDURE, VIEW,
  FUNCTION or PACKAGE, etc.

I did not test this though.


ALTER TABLE t MODIFY COLUMN c ENUM(...) ...

If that is only adding a new option on the end and not going past 255 options, that should be a "schema-only" change that happens instantly.  (This was in 5.5, so has been around for 12 years.)
Any other change to the ENUM is likely to require a "copy" of the table (slow).
ALTER TABLE t ADD INDEX (...)

has been efficient (not instant, but did not require copying the table over) since 5.6 (2013).
For other column alterations -- It depends.
That probably does not apply to ADD UNIQUE, and certainly does not apply to any changes to the PRIMARY KEY.
So, based on that info, I would expect Cloud-SQL with 5.7 to handle things "fast".  (Sorry, but I have no direct knowledge of that port of MySQL.)  I suggest you take it up with the vendor.


Foreign Key Constraint into an different database

Would you replicate the OU table ...?

Yes.  Because local lookups of master/reference data are important for application performance, reliability, and to enforce referential integrity because this:

an FK to an external database

is not possible.


How can I output text until it is ascii in SQL?

A space is not a valid range delimiter so LIKE [ -~] will not work. That becomes a test for those three characters only.
You could check the ASCII code directly, rather than using LIKE:
DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE ASCII(SUBSTRING(@input, @index, 1)) BETWEEN 32 AND 126
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

demo
Side note: It's possible to get unexpected results with LIKE ranges because the comparison is collation-aware.


Lets make sure we understand some of the terms involved.

WSFC=Windows Server failover cluster
FCI=failover clustering instance
AOAG=Always On Availability Groups

I suggest you make you requirement absolutely clear before you start implementing. As @John suggested version of SQL Server and Windows Operating system can detect most of your decision.

You definitely need WSFC, does not matter what configuration you chose.  For multi site (assuming multi subnet) WSFC follow this document.

Then you have 3 options:

FCI
AOAG
FCI+ AOAG

For creating multi-subnet Failover cluster instance follow this document.

Creating AOAG between multi subnet is not an issue as long as WSFC is configured correctly.  Issue mostly arises because listener is not configured correctly. See this for details.

Now you need to decide if you need to combined solution of step 1 and 2 meaning FCI+AOAG. This document will give a good start.

For storage level replication you definitely want to discuss with your vendor. In general you can do that when you are deploying AOAG.  Because AOAG will read transaction log and write to secondaries. Most storage level replication that I am aware off, you will need disks to be offline for implementing block level changes.

Here are few more resources that can help you.

https://www.sqlhammer.com/  has bunch of example of different configuration by Derik Hammer.
https://www.mssqltips.com/sqlservertip/4597/configure-sql-server-alwayson-availability-group-on-a-multisubnet-cluster/


How to completely remove EnterpriseDB Installation of Postgresql on Ubuntu?

In Feb 2009, Scahin_S  wrote in [1] to use the uninstall-postgresql binary.  In the same thread Sachin_S also documented a manual alternative for uninstalling 8.3 is

/opt/PostgreSQL/8.3/installer/server/removeshortcuts.sh /opt/PostgreSQL/8.3 8.3
/etc/init.d postgresql-8.3 stop
rm -rf /opt/PostgreSQL
rm /etc/postgres-reg.ini
rm -rf /etc/init.d/postgresql-8.3
userdel postgres

if /etc/ld.so.conf exists, edit it and remove /opt/PostgreSQL/8.3/lib
if present.

if /etc/ld.so.conf.d exists:

rm /etc/ld.so.conf.d/postgresql-8.3.conf

1 - "Uninstall Postgres" thread
Enterprise DB "PostgreSQL Installers for Windows, Linux and OS X" forum
http://forums.enterprisedb.com/posts/list/1437.page


Determining if a relation is in third normal form 3NF

When looking to see if a relation is in 3NF, two are the questions that you must answer:

Has every functional dependency a left hand side which is a superkey?
If this is not true, are the attributes on the right hand side prime attributes? (i.e. belonging to any key?).

So in your example you can see that both A -> B and A -> C are dependencies in which the left hand side is a key, so the answer to the first question is yes.

In the third dependency, instead, B is not a superkey, (since B+ = BC), so, is C a prime attribute?

To answer to this question, you should know all the keys of the relation, and see if C belongs to one of them. And in this case this is very simple, since A is the unique key of this relation, so you can conclude that the relation is not in 3NF. But note that in general, in more complex cases, it is not so simple to answer to such question, since you can have a very large number of keys, actually a number which is exponential with the number of attributes.


How to convert minutes to hours using MYSQL?

You need DIV and % operators.

DIV is the integer division and % is the modulo function, returning the reminder:

SELECT 12345 DIV 60 AS hrs, 12345 % 60 as min;

UPDATE

You have to change your query such way:

SELECT
   . . . . .
   TIMESTAMPDIFF(MINUTE, Tickets.date, Tickets.solvedate) DIV 60 AS TempoH,
   TIMESTAMPDIFF(MINUTE, Tickets.date, Tickets.solvedate) % 60 AS TempoM
   . . . . .


Select failed from V$LOGMNR_CONTENTS when called from a procedure

V$LOGMNR_CONTENTS

V$LOGMNR_CONTENTS contains log history information. To query this
view, you must have the LOGMINING privilege.

The LOGMINING privilege was granted to DBA role. When running an anonymous block, all your privileges granted through a role are effective. When running a stored procedure defined with the default definers rights option, privileges granted through a role are ignored. The LOGMINING privilege should be granted directly to your user:
grant logmining to c##myadmin;


SSRS output to file subscription not working

Turns out that the report tries to run as the Owner of the subscription, and that credential needs to have a higher level of privs in the AD for the save-to-file-share option.

I don't have those privs but I can tell SSRS that the subscription is owned by someone that does.


ORACLE execute error: ORA-01950: no privileges on tablespace 'PDATA'

You did not grant PEEI_SYS any quota in the tablespace. PEEI_SYS owns the table, and therefore the data blocks that need to be created. PEEI does not need any quota if it is not going to own objects.
ALTER USER PEEI_SYS QUOTA UNLIMITED ON PDATA;


The secondary replica does not know the role / status of that DBN1 replica

When trying to failover to the secondary, the status of the primary is not known:

it finds the current primary replica but not the primary replica status.

When failing over from the primary it does find this information.

When you select the properties of the replica with the question mark on the secondary replica:

Properties

Role: Unknown

Whereas on the primary replica all roles and their sync states are known.

sys.dm_hadr_availability_replica_states

Executing this query returns multiple lines on the primary, but only the one for the secondary on the secondary replica's.

select * from sys.dm_hadr_availability_replica_states;

This behaviour would be reversed after failover.

Extra checks

When looking at the query that gets executed when opening the properties of the unknown replica on the secondary replica, the sys.dm_hadr_availability_replica_states dmv is also used with a left join in a much bigger query.

An interesting part here is the OperationalState column:

ISNULL(arstates.operational_state, 6) AS [OperationalState],

Meaning if the replica is not present in that dmv, such as when executing on the secondary, return 6 as the OperationalState.

This state is not explicitly noted in the documentation on the dmv:

  operational_state tinyint Current operational state of the replica,
  one of:

  0 = Pending failover

  1 = Pending

  2 = Online

  3 = Offline

  4 = Failed

  5 = Failed, no quorum

  NULL = Replica is not local.

  For more information, see Roles and Operational States, later in this
  topic.

The 'Unkown' state is also not noted in the documentation.

In this case 6 is used when the role is not known.

It does not always appear to be 6, when refreshing the availability replica's on the secondary:

the query used sets the unknown replica's role status to 3:

ISNULL(arstates.role, 3) AS [Role],

Which is offline on the dmv's documentation.

And the connected state column will be 2

ISNULL(arstates.connected_state, 2) AS [ConnectionState],

Which is also not noted in the documentation:

  0 : Disconnected. ...

  1 : Connected. ...


SQL server has "default trace" running by default.  You need to search the trace files using something like this:

--Find current trace file name
--Select * from sys.traces

SELECT
     loginname,
     loginsid,
     spid,
     hostname,
     applicationname,
     servername,
     databasename,
     objectName,
     e.category_id,
     cat.name as [CategoryName],
     textdata,
     starttime,
     eventclass,
     eventsubclass,--0=begin,1=commit
     e.name as EventName
FROM ::fn_trace_gettable('H:\MSSQL10_50.ODS01\MSSQL\Log\log_217.trc',0)
     INNER JOIN sys.trace_events e
          ON eventclass = trace_event_id
     INNER JOIN sys.trace_categories AS cat
          ON e.category_id = cat.category_id


Auto-Shrinking All Databases Files To Their Minimum Size In Sql Server

My question is about how i can shrink all database files to their minimum size after truncating related tables

To answer your question directly, this is done using DBCC SHRINKFILE command -

DBCC SHRINKFILE (YourDatabaseLogicalFileName,<desired size in MB>)

Here you can read more information on how to use this commnand
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15

But, as other comments already mentioned, the reason of poor performance lies somewhere else and you need to start a methodical process of collecting and analyzing performance information (Performance Counters, Waits, Top heaviest queries, etc.)

Among other reasons, possible that poor performance in your Dev environment could be simply because system specification of Dev environment (CPU, RAM, Storage, Network etc.) is much weaker/worse than your Production; or Developers could be running some heavy and not tuned queries in Dev environment, that they do not run on Production


How to identify a user (Oracle) that is locked due to unsuccessful login attempts

Select the user information from dba_users and look at the ACCOUNT_STATUS. If the status is LOCKED then the user was deliberately locked. If it is LOCKED(TIMED) then it is locked because of multiple wrong passwords. If you have set the PASSWORD_LOCK_TIME then the user will be unlocked after this time is reached.

select   username, account_status
from     dba_users
where    username='A-USERNAME'
/


I was not able to replicate, so here's my successful YAML config file (Windows 7 64 bit, MongoDB 2.6.1):

replication:
    replSetName: "rs1"
    oplogSizeMB: 512
storage:
    dbPath: "data"
    smallFiles: true
systemLog:
    destination: file
    path: "data/mongod.log"

How I started it:

C:\Users\Adam\mongo\2.6.1\bin>mongod.exe -f mongod-yaml.conf
2014-06-03T23:23:12.528+0100 log file "C:\Users\Adam\mongo\2.6.1\bin\data/mongod
.log" exists; moved to "C:\Users\Adam\mongo\2.6.1\bin\data/mongod.log.2014-06-03
T22-23-12".

And then (after an rs.initiate()):

rs1:PRIMARY> db.serverCmdLineOpts()
{
        "argv" : [
                "mongod.exe",
                "-f",
                "mongod-yaml.conf"
        ],
        "parsed" : {
                "config" : "mongod-yaml.conf",
                "replication" : {
                        "oplogSizeMB" : 512,
                        "replSetName" : "rs1"
                },
                "storage" : {
                        "dbPath" : "data",
                        "smallFiles" : true
                },
                "systemLog" : {
                        "destination" : "file",
                        "path" : "data/mongod.log"
                }
        },
        "ok" : 1
}

Note that I had issues when I used tabs to indent, which were resolved when using spaces.  Case sensitivity was also a problem, but mainly due to muscle memory from the old config options.


How to exclude NULL values inside CONCAT MySQL?

If you want to skip NULL values (but not empty strings), you can use CONCAT_WS() function:
CONCAT_WS( ', ',            -- Separator
           CONCAT_WS(' ', tadd.street_number, tadd.street_name),
           tadd.apt_number,  tadd.city,
           tadd.postal_code, tadd.country
         ) AS Address

From the docs:

CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments.  If the separator is NULL, the result is NULL.
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.


The warnings you're seeing most likely come from the sys.sysdepends view.

If you script it out using

EXEC sys.sp_helptext @objname = N'sys.sysdepends'

The definition has a bunch of converts and other nonsense going on.

CREATE VIEW sys.sysdepends AS
 SELECT
  id = object_id,
  depid = referenced_major_id,
  number = convert(smallint,
   case when objectproperty(object_id, 'isprocedure') = 1 then 1 else column_id end),
  depnumber = convert(smallint, referenced_minor_id),
  status = convert(smallint, is_select_all * 2 + is_updated * 4 + is_selected * 8),
  deptype = class,
  depdbid = convert(smallint, 0),
  depsiteid = convert(smallint, 0),
  selall = is_select_all,
  resultobj = is_updated,
  readobj = is_selected
 FROM sys.sql_dependencies
 WHERE class < 2
 UNION ALL
 SELECT  -- blobtype dependencies
  id = object_id, depid = object_id,
  number = convert(smallint, column_id), depnumber = convert(smallint, type_column_id),
  status = convert(smallint, 0), deptype = sysconv(tinyint, 1),
  depdbid = convert(smallint, 0), depsiteid = convert(smallint, 0),
  selall = sysconv(bit, 0), resultobj = sysconv(bit, 0), readobj = sysconv(bit, 0)
 FROM sys.fulltext_index_columns
 WHERE type_column_id IS NOT NULL

sys.objects, on the other hand, is fairly straightforward.

CREATE VIEW sys.objects AS
 SELECT name,
  object_id,
  principal_id,
  schema_id,
  parent_object_id,
  type,
  type_desc,
  create_date,
  modify_date,
  is_ms_shipped,
  is_published,
  is_schema_published
 FROM sys.objects$

The view definition for sys.sysdepends causes the same warnings when queried on its own.

SELECT *
FROM  sys.sysdepends

In general, if you want to control datatypes and indexes and have some performance tuning ability when referencing system views or tables, your best bet is to dump them into a temp table first.


just typecast id value like this

postgres@localhost:test> create table "Tenants"
 (
     "id" UUID NOT NULL ,
     "domain" VARCHAR(255) NOT NULL UNIQUE,
     "name" VARCHAR(255) NOT NULL,
     "schema" VARCHAR(255) NOT NULL UNIQUE,
     "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
     "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id")
 );
CREATE TABLE
Time: 0.048s

postgres@localhost:test> INSERT INTO "Tenants" ("id","domain","name","schema","createdAt","updatedAt")
 VALUES ('3b6b220b-2690-4279-8343-cbe6167f7596'::UUID, 'test1.stage-domain.io', 'Test1','test1', '2019-02-25 14:29:33.475 +00:00','2019-02-25 14:29:33.475 +00:00') ;
INSERT 0 1
Time: 0.004s

hope this helps


Oracle not available error with Oracle XE 11.2.0-1.0

I have removed the trailing / from ORACLE_HOME and it solved my problem.


Taking a backup of a single table in the SQL Server

Right click the database; Tasks, Generate Scripts & enable Script Data from the options. Select the table and Finish.

This will generate a CREATE TABLE followed by INSERTs for the data.


Practices to keep SQL Server databases easy to port to MySQL?

Yeah, plan on using none of the SQL Server features like Geography data types, SQL CLR, filtered indexes, top, CTEs, etc.

I'd say don't bother trying to program for multiple database platforms.  Pick a platform and that'll be the platform which the app runs on.  If the app needs to be moved to another platform later deal with it then.  Otherwise you'll spend so much time trying to figure out if the code can be ported to both platforms that you'll get no where.

If you are going to plan for a MySQL migration, why not also plan for Oracle, Sybase, NoSQL, Progress, etc?


SQL Server - How are data pages stored when using a clustered index

Data pages are stored contiguously when the index is created and when the index is rebuilt. Otherwise, SQL Server will attempt to keep the pages in physical order. That not being possible, logical order is attempted. You can get gaps in a table because of other writes that are happening in the database. SQL Server uses B+ trees for indexes - the leaf (data) level of the index contains the data but is also a doubly linked list that contains pointers to the next and previous pages. This makes it less important that the table be 100% physically contiguous.


Which are the differences and advantages on single INDEX or compound INDEX?

Here's some explanation of which index you could use and when:

First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a record requires writing not only the data row, it requires changes to any indexes as well. The more indexes a table has, the more changes need to be made, and the greater the average performance degradation. In the section "Loading Data Efficiently," we'll go into more detail about this phenomenon and what you can do about it.

Second, an index takes up disk space, and multiple indexes take up correspondingly more space. This might cause you to reach a table size limit more quickly than if there are no indexes.

Example:

mysql> ALTER TABLE customer ADD INDEX idx_sub
 (subscription)
mysql> EXPLAIN SELECT name FROM customer
WHERE subscription='2009-01-01' ORDER BY name
*************************** 1. row ***************************
 ...
 type: ref
 key: idx_sub
 rows: 4370
 Extra: Using where; Using filesort

The ideal way:

mysql> ALTER TABLE customer ADD INDEX
 idx_sub_name (subscription,name)
mysql> EXPLAIN SELECT name FROM customer
WHERE subscription='2009-01-01' ORDER BY name
*************************** 1. row ***************************
 ...
 type: ref
 key: idx_sub_name
 rows: 4363
 Extra: Using where; Using index

Benchmarks:

Avg number of sec to run the query:

Without index: 3.743
Index on subscription: 0.435
Covering index: 0.012

Covering index:

35x faster than index on subscription
300x faster than full table scan

Related:

Better Indexing for MySQL?
Covering Indexes


Row level security returning no error when blocking all effects of a query

If you only enable RLS for a table, but do not specify rules, then a "deny all" rule is implicitly assumed.

Quote from the manual

  When row security is enabled on a table [...] all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. [...] If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.

(emphasis mine)

So since the policy makes no rows visible, this is why there is no error when UPDATEing and you get 0 rows affected.


Even though only 0.1% of the articles have a duplicate, the cost (in terms of wasted space) of a NULL int value (for a redirect_to column) in the article table is small. (In fact, if using the InnoDB storage engine, you will waste no space at all if the value is NULL. (See e.g. Bill Karwin's SO answer here as well as the MySQL documentation about InnoDB field contents.) However, if the redirect_to column needs to be indexed, then there is a performance penalty.

A separate table (article_redirect) is the more normalized approach. This will add another join to your queries, but again the cost of that (in terms of extra memory and processing time) is small because the table is small, especially if best-practice indexing is used. You should therefore have a compound index (article_id, redirect_to) on the article_redirect table.

For the separate table approach (article_redirect), it's good practice to define an explicit primary key (in every table). Assuming an article can only be a duplicate of one other article (but many articles can duplicate the same article), then the primary key should be article_id.

As an aside, I will mention that it's usually not good practice to have TEXT columns (TEXT is a type of BLOB) together with other "meta data" columns such as title, timestamps and so forth that you might retrieve in queries. Instead, consider moving this to a separate table with a foreign key pointing back to your article table. See the MySQL documentation about optimizing for BLOB types for details.


Cannot open `pg_hba.conf` in pgAdmin III

It's not clear what is the point in doing that, but if you're an admin you can do chmod o+r pg_hba.conf to let a non-admin read it.

There are two ways in pgAdmin3 to access those files:

by reading the file system (if it runs on the same host than the server). This corresponds to the File->Open *.conf commands. With that method, permission-wise, pgAdmin is not special, it's like a text editor or a pager.
through the DB connection, with Tools->Server configuration. This requires the adminpack contrib module installed server-side. In the case of Ubuntu, I'm not sure it would work because the configuration files are not inside the data directory below /var, they're under /etc/... and the server-side functions involved are not supposed to reach anything outside the data directory. But if/when this method does work, it's the postgres process that reads the files, so the permissions shown in the question are good enough.


can I replace nulls in a PIVOT with zeroes?

If you have the following table of data that you want to pivot:

CREATE TABLE yourtable (id int, name varchar2(1), value varchar(10));

INSERT ALL
    INTO yourtable (id, name, value )
         VALUES (1, 'A', '1500')
    INTO yourtable (id, name, value )
         VALUES (1, 'B', '4500')
    INTO yourtable (id, name, value )
         VALUES (2, 'C', '3.5')
    INTO yourtable (id, name, value )
         VALUES (3, 'B', 'test')
    INTO yourtable (id, name, value )
         VALUES (4, 'A', 'blah')
    INTO yourtable (id, name, value )
         VALUES (4, 'C', 'hello')
SELECT * FROM dual;

And your current code is similar to:

select id, OptionA, OptionB, OptionC
from
(
  select id, name, value
  from yourtable
) src
pivot
(
  max(value)
  for name in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv

See SQL Fiddle with Demo.  The sample data will create null values.

When you want to replace the null values you have to do it in the final SELECT list. So your code will need to be:

select id,
  coalesce(OptionA, '0') OptionA,
  coalesce(OptionB, '0') OptionB,
  coalesce(OptionC, '0') OptionC
from
(
  select id, name, value
  from yourtable
)
pivot
(
  max(value)
  for name in ('A' as OptionA, 'B' as OptionB, 'C' as OptionC)
);

See SQL Fiddle with Demo


Get more information from Oracle audit trail

Sure, use one of the below settings:

AUDIT_TRAIL

  db, extended

  Performs all actions of AUDIT_TRAIL=db, and also populates the SQL
  bind and SQL text CLOB-type columns of the SYS.AUD$ table, when
  available. These two columns are populated only when this parameter is
  specified.

  xml, extended

  Performs all actions of AUDIT_TRAIL=xml, and includes SQL text and SQL
  bind information in the audit trail.

alter system set AUDIT_TRAIL=db, extended scope=spfile;
shutdown immediate
startup


Migrating DB, SSIS packages, SSRS Reports from 2008 R2 to 2012

You have a lot of reading and testing to do.  There isn't a magic bullet per say but lots of ways of hitting this issue.  Firstly setup the 2nd environment as if it were prod, but restore the databases in a test environment and restore the SSIS packages.  If reporting services are not dependent on this then focus on this part first.  Give your SSIS package test data to feed and test the validity of the data.  You can even have it write to a test table using your business logic, and have the real process do the same thing so you can ensure you get accurate numbers and figures.

Mirroring is a fine solution assuming you're fully up to date, but there's 2 modes to mirroring and high performance is only available in Enterprise.  Thus your network has to be fast when you setup mirroring since all transactions must commit on the mirrored side first.

Are you following all the 2012 upgrade best practices, best practices analyzer, upgrade wizard for code issues, researched potential codes, have a well written test plan, and acceptance tests?

User Kin answered a very similar question, so please do research this site for info or duplicate questions, you'll often find a lot:

For SSRS depending on the complexity, you might want to just rebuild it.  If you wish to migrate, the SSRS Scripter is a great resource. Kevin Klines "how to" and "how to migrate reports" are also included in the link.


SQL Server 2008 R2 DETA Error: Can not get minimal database information in allotted time

From the comments I see you removed the default time limit like I mentioned but still the analysis didn't happen in enough time or ever complete as I feared.

18GB is definitely the largest trace I have ever heard of attempting to be analyzed by the Database Engine Tuning Advisor. I suggest you break your trc file up into smaller sizes. Either by looking at the data a bit more filtered or for a smaller window of time while still maintaining enough of a workload to capture both data modification and selects.

Alternatively -

I haven't been a big fan of the DETA and sometimes the results, even with a properly sized workload file, fly in the face of reality (most index  suggestions are very wide covering index in a lot of cases, redundant indexes in overlapping column coverage at times, etc.) I would suggest a "Top 10" style tuning exercise may be a better approach. Find the worst performing queries by duration and/or reads and work on tuning them through query tuning and index tuning/design best practices.

Edited: Added a couple more tips based on comment from OP

I would also look to set an end time. DETA isn't supposed to be a "done in 5 minutes" deal. It is trying to be incredibly thorough and look at your database as deeply as it can and analyze the workload as long as it can to come up with the recommendations. Have you tried setting an end time for the analysis?

I'd check out this link on MSDN for some other tips - https://blogs.technet.com/themes/blogs/generic/post.aspx?WeblogApp=sql_server_isv&y=2011&m=04&d=08&WeblogPostName=fundamentals-running-database-engine-tuning-advisor-and-selecting-indexes&GroupKeys=

Bottom line though - This will be a slower operation. The larger the workload file and more complex the database, the slower it will be. If you are running on the same system that other work is going on then you are each competing for resources.


Why does ALTER COLUMN to NOT NULL cause massive log file growth?

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done in a single pass, though, the log has to account for all of the changes so that, if you hit cancel, it knows exactly what to undo.

An example. Simple table:

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
  bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

Now, let's look at the page details. First we need to find out what page and DB_ID we're dealing with. In my case I created a database called foo, and the DB_ID happened to be 5.

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

The output indicated that I was interested in page 159 (the only row in DBCC IND output with PageType = 1).

Now, let's look some select page details as we step through the OP's scenario.

DBCC PAGE(5, 1, 159, 3);

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;
DBCC PAGE(5, 1, 159, 3);

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5, 1, 159, 3);

Now, I don't have all the answers to this, as I am not a deep internals guy. But it's clear that - while both the update operation and the addition of the NOT NULL constraint undeniably write to the page - the latter does so in an entirely different way. It seems to actually change the structure of the record, rather than just fiddle with bits, by swapping out the nullable column for a non-nullable column. Why it has to do that, I'm not quite sure - a good question for the storage engine team, I guess. I do believe that SQL Server 2012 handles some of these scenarios a lot better, FWIW - but I have yet to do any exhaustive testing.


Dimension Help - Deciding Fact or Dimension

Put the indicators on the customer dimension.
This means that any fact table that joins against the customer dimension has access to all of the indicators.  If they are on the dimension then you can trivially make them available to any fact table that links to the customer dimension.
If you just need a fact table with counts of customers that roll up by the dimension attributes, then you can create a 'factless fact table' that just has a single fact - a 'QTY' column with a value of 1.  This allows counts of customers to be grouped by any of the attributes.
If Customer is a slowly changing dimension, consider putting an additional row with -1 in the QTY column into the fact table every time a type 2 change is made.  This should link to the previous version of the dimension, with the current version having an additional row with a 'QTY' of 1.  This allows you to track statistics on changes in customer attributes over time.
A cube can consume this change over time for the counts by implementing a calculated measure that does a running sum in 'QTY' from beginning up to the selected date.  If necessary you could also build a snapshot table.
If the number of attributes and volume of changes becomes unwieldy then you have what Kimball calls a 'Rapidly Changing Monster Dimension' in his first book.  In this case, consider pulling the attributes out into a separate junk dimension that has a row for each distinct combination of values.  You will still want some scaffolding to link this to the actual dimension rows so you can copy the junk dimension key onto any fact tables you have the customer dimension key on.
A cube can consume either structure, so it is less likely to be an issue for the cube so much as an issue for the ETL processing.


Multi-Site clustering on Windows 2012 for SQL Server 2012

The cluster will pickup all the different subnets you have configured on your NICs.

Once a server with NICs that have IP addresses of the 2nd subnet is included in the cluster you will be asked to add an IP for each subnet for each role as you add them to the cluster.


dbcreator role member not able to access database it just restored

After the database is restored, a member of the sysadmin or securityadmin server roles will need to alter the authorization (ownership) of the database to the login:

ALTER AUTHORIZATION ON DATABASE::<name of database> TO [PROD\apdf];

I can verify the restore operation does not seem to correctly grant ownership through the below MCVE.

create a login and make it a member of the dbcreator server role:

CREATE LOGIN backup_restore_test
WITH PASSWORD = 'asdfousd^f89aw09e8r90qawer$udfjoia'
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF;

ALTER SERVER ROLE dbcreator ADD MEMBER backup_restore_test;

Take a backup of the model database, to restore later:

BACKUP DATABASE model
TO DISK = 'C:\temp\model.bak'
WITH COPY_ONLY;

Login to the server using the [backup_restore_test] user, and run the following:

RESTORE DATABASE newmodel
FROM DISK = 'C:\temp\model.bak'
WITH MOVE 'modeldev' TO 'C:\temp\modeldev.mdf'
    , MOVE 'modellog' TO 'C:\temp\modellog.ldf'
    , RECOVERY;

This shows the backup_restore_test login is the owner of the database:

SELECT d.name
    , sp.name
FROM sys.databases d
    INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid
WHERE d.name = 'newmodel';

However, this fails:

SELECT *
FROM newmodel.sys.tables

The error:

  Msg 916, Level 14, State 1, Line 14
  The server principal "backup_restore_test" is not able to access the database "newmodel" under the current security context.

Switch back to a sysadmin login, and run this:

ALTER AUTHORIZATION ON DATABASE::[newmodel] TO [backup_restore_test];

Now, this works, when logged in as the [backup_restore_test] login:

SELECT *
FROM newmodel.sys.tables

Unless I'm overlooking something, the backup_restore_test login should have access to the database as soon as it is restored since it owns the database at that point.  I've verified this behavior on SQL Server 2012, and SQL Server vNext


So, a covering index is an index that has all the columns that are needed for your query.
A key lookup happens when the optimizer uses your index to get data, but is lacking one or more columns, so it fetches those from the clustered index.

As for example you have an index seek on Table_22 with the index table_22_promoId_Index.
Afterwards in the plan you see that the key lookup fetched a whole bunch of columns.

If you add all the columns from your output list of the key lookup, into your Table_22_PromoId_Index as included columns, you'll stop having a key lookup, cause now it'll just fetch everything from that index, without needing extra information from your clustered index
Then you can repeat that for the others

  I don't know how to approach it with a complicated plan like this.

Take it one step at a time, pick one lookup, and try and fix that, then move to the next one.
Also since you're selecting many columns, try and remember that making huge indexes also has downsides on updates/inserts and takes up storage, so be careful not too have too many indexes (All depends on how your specific case though.)

This is also why people advice you to only select columns you actually need instead of everything, since that makes your indexes more narrow and easier to tune.

A useful read about this topic :
Expensive key lookups

  What confuses me is that the Key Lookup contains something called a "Predicate" and a "Seek Predicate". I haven't seen this before and I'm a little confused by the explanation in the article.

So a seek predicate and a predicate in a key lookup is the same as in your Index seek. What happens is that he'll fetch the data from his clustered index (in the case of a key index) using the Seek predicate. Afterwards if he has to filter that data more, he'll use the predicate for extra filters.

There's a good answer from Joe Obbish explaning it really well:
Difference between seek predicate and predicate


SQL Server 2016 Enterprise Edition allows you to set maxdop to any value up to 64.  However, SQL Server 2016 Standard Edition has limits around parallelism, per this docs page:

Partitioned table parallelism is not supported.
Standard Edition is not NUMA Aware and does not support Large Page Memory and Buffer Array Allocations
The degrees of process parallelism (DOP) for an index build is limited to 2 DOP for the Standard Edition
Limited to lesser of 4 sockets or 24 cores