Your WHERE clause has two parts that deal with date and time. The date part is OK but the time part isn't doing what you want. You need to extract only the time from the timestamp values, with TIME(date_time):

WHERE
          -- date part
      date_time >= '2014-01-01'
  AND date_time  < '2014-04-01'    -- minor correction
          -- time part
  AND TIME(date_time) >= '08:00:00'
  AND TIME(date_time)  < '20:00:00'

The whole query:

SELECT
    st.id,
    DATE(ip.date_time) AS mydate,
    TIME(ip.date_time) AS mytime,
    ip.price
FROM
    station AS s
    INNER JOIN item_price AS ip
    ON st.id = ip.station
WHERE
          -- date part
      ip.date_time >= '2014-01-01'
  AND ip.date_time  < '2014-04-01'    -- minor correction
          -- time part
  AND TIME(ip.date_time) >= '08:00:00'
  AND TIME(ip.date_time)  < '20:00:00'
ORDER BY
    ip.date_time ;


Tools for looking at transactions inside of transaction log backup file?

If you are interested in yourself, you can check with sys.fn_dump_dblog the whole log file or log backup. Otherwise, there is a tool that I know but haven't tried Apex Log Reader.


Idempotent user and database creation script with MySQL

The task is too complex to safely (and simply) do in a shell script.  Get into some programming language (Perl, etc) and write enough code to check for various end cases, syntax errors, punctuation in user names, etc, etc.  You can also check for the existence of the user and/or database and exit or say "Dup name" or proceed with the delete, or whatever.

And totally disallow 'root' and 'mysql' and 'information_schema' and 'performance_schema'!

The CREATE USER + 3 GRANTs can be simplified to just

GRANT ALL PRIVILEGES ON db.* TO user@localhost IDENTIFIED BY '...' WITH ...


Table Valued UDF vs Stored Proc for passing values to a SELECT statement

The answer partially depends on what you do with the output of the object. Both stored procedures and table-valued functions support inserting into table, but using an inline table-valued function can be significantly more efficient.

Below are a few silly object definitions for the demo. Both of them generate a result set of about 6.4 million rows with a single column.

CREATE PROCEDURE dbo.DUMMY_PROCEDURE (@i BIGINT)
AS
BEGIN
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i <> 0;
END;

GO

CREATE FUNCTION dbo.DUMMY_FUNCTION (@i BIGINT)
returns table
as
return (
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i <> 0
);

GO

Next I'll insert the results of the stored procedure into a temp table. In general, this isn't a good thing to do for result sets that aren't tiny. The results are written to a separate internal structure before being inserted into the table. SQL Server uses a cardinality estimate of 1 row for the insert which can also cause performance issues.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 11514 ms,  elapsed time = 10748 ms
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
EXEC dbo.DUMMY_PROCEDURE 1;

The query took about 11 seconds. I uploaded the actual plan here if you want to take a look. Here's a screenshot of the worst part:

Things are much better with the function. The function definition is inlined and I'm eligible for parallel insert in SQL Server 2016. The insert takes about half a second.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 1562 ms,  elapsed time = 520 ms.
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
SELECT COL
FROM dbo.DUMMY_FUNCTION (1);

The query plan is healthier and much more typical:

There might be other internals differences in addition to that. I couldn't tell you.


MSSQL: How to get the data from 1st - 15th and 16th - Last day of the PREVIOUS MONTH

declare @AA_WC_TEST_PAYRUN table(from_date date, to_date date, is_Complete varchar(10));
insert into @AA_WC_TEST_PAYRUN
values
('2018-10-01', '2018-10-15', 'True'),
('2018-10-16', '2018-10-30', 'True');

if (select is_Complete
    from @AA_WC_TEST_PAYRUN
    where from_date = (select dateadd(month, datediff(month, 0, getdate())-1, 0))) = 'True'
and
    (select is_Complete
    from @AA_WC_TEST_PAYRUN
    where from_date = (select dateadd(day, 15, dateadd(month, datediff(month, 0, getdate())-1, 0)))) = 'True'
begin
   print 'both true';
   -- other statements
end;


SQL Server Reporting Services 2017: Install Cumulative Update

We’ll periodically update the SSRS 2017 download/installer such that it’s always the latest “Cumulative Update.” If you have an earlier release of SSRS 2017 installed and you run the installer, it’ll prompt you to “Upgrade Reporting Services” to install the update.

The Reporting Services installer is separate from the SQL Server installer and SQL Server CU installers. We’re not currently releasing it through WSUS, but we’d love to hear more customer feedback on that topic.

Reference: SQL Server 2017 Reporting Services now generally available


Creating SQL Server logins is a bit tricky and you need practice to understand what's going on.

There is an excellent article about it here.

In essence you need to consider the "server credentials" as well as the "database credentials". They are both different things.

I would normally run SSMS and go down to the bottom of my server instance where it's showing the Security folder (as explained in the above link). In there, expand it and right click to create a new Login. Set up your LoginID and Passwords, and default the login to the database you're referring to. Next go to Securables, and choose the correct database, along with the Schema, etc.

Once that's done you'll notice the same login was created at the database's Security folder. Make sure you've given the new Login, all the right permissions to READ and/or WRITE to the database.

The reason you need to do what I've mentioned above, is so you have a correct and valid Login to the database, which is important. Creating a single Login directly inside the database's Security is not enough.

Then you can connect to the database from external sources (Excel, programs, etc) in many ways, using the right connection strings.


How to audit all DML operations on a SQL Server 2008 R2 database

If you just want to know number of index updates (regardless of how many rows affected, how wide the indexes are, etc.) and as long as you can be sure the service doesn't get restarted or indexes dropped/re-created in that timeframe, then you can look at sys.dm_db_index_usage_stats now and after a couple of days.

As suggested by Kin, have a look at Nacho's FAQ. Basically:

The DMV can reset if:

the server is restarted
an index is dropped and re-created

The DMV can be empty if you start with -x, -m, or trace flag 2330
If you have a filtered index, updates will increment the usage stats even if the update did not affect the rows in the index
If any column in the clustering key is modified, all non-clustered indexes also increment their usage stats
Errors occurring after a DML attempt is made will still increment counters
Index usage tells you number of operations - it does not have any way to report to you number of rows or pages touched (an update to every row in a very wide table looks the same as an update to a single row in a very skinny table) - if you want more details on actual volume touched, see sys.dm_db_index_operational_stats.

And also Tim Ford's series on snapshotting the DMV:

Retaining historical index usage statistics for SQL Server : Part 1 | Part 2 | Part 3


This is pretty much what bluefeet gave you as an example in the comments.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Status])
                    from [EVAULTTEST].[dbo].EvidenceItems
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT [ItemType],' + @cols + ' from
             (SELECT ItemType, [Status]
                from [EVAULTTEST].[dbo].EvidenceItems) x
            pivot
            (COUNT([Status])
                for [Status] in (' + @cols + ')
            ) p '

execute(@query)


I have connection issue and its changing ip

I see 2 attempts where 'root' is expected to have a password, then two where it is not.  Did someone change things meanwhile?

It is not "normal" to allow root to come in any way other than via "localhost".  Did someone GRANT access for root from elsewhere?  If not, then should you really be logging in as "root"??

telnet checks the connection.  However, that is not in question -- "Access denied" came from the server back to the client, hence the connection succeeded.


I've seen this question pop up enough times over the years that I figured it finally warranted a real answer.  Yes, this answer is years after the fact, but facts remain the same now as they did in 2015.

Heap Fragmentation is a red-herring, and frankly I wouldn't pay too much attention to it.  Honestly I say that about fragmentation in general and, at best, it is an indicator of issues to come, with the real detriment to performance being excessive white space per page.  Since that's not your question, I won't digress any further and just point you towards a post from Brent Ozar that will further wet your appetite if you wish to go down that rabbit hole.

What you need to be concerned with for heaps is forwarded records/forward pointers counts.  To quote one of the better posts on the topic by Kalen Delaney:

  when a row in a heap has to move, SQL Server leaves behind a forwarding pointer in place of the row that has moved

and

  SQL Server jumps out of sequence and follows the forwarding pointer for every forwarded record.

This is actual fragmentation that does matter as it's introducing additional reads, so depending on how many forwarded records you have in your heap, this could represent a significant chunk of I/O that you need to perform whenever you read the heap into memory.

How do you find out how many forwarded record pointers exist in your heap?  You need to simply run the following query for the heap in question:

SELECT    index_type_desc
        , page_count
        , avg_page_space_used_in_percent
        , avg_fragmentation_in_percent
        , avg_record_size_in_bytes
        , forwarded_record_count, 1.0*(forwarded_record_count)/(1.0*(page_count)) AS forwarded_record_pct
FROM    sys.dm_db_index_physical_stats(db_id(), object_id('schema.tablename'),null, null, 'detailed')
WHERE   index_type_desc = 'HEAP'
GO

Below are results from some actual tables in a vendor supplied application database, I currently support:

Results Table 1:

Results Table 2:

As you can see, fragmentation in both tables in actually pretty high, but I'm not going to waste my maintenance time rebuilding either table as there are not enough forwarded records to be concerned about it yet.  My reasoning here is that a heap is only ever brought into memory by a table scan operation or a RID lookup (in the event that a nonclustered index defined on the heap is non-covering).  When the table scan is performed, the entire table is loaded into memory, at which point fragmentation doesn't matter nearly as much.  I want to avoid table scans in general if possible, but when they occur, I want them as efficient as possible, so reducing forwarded pointers directly helps with that.  RID lookups are also affected by forward pointers, so even seek operations against a NCI with a RID lookup is subject to extra I/O if it runs into a forwarded record.  When I hit a certain threshold of forwarded pointers within my heap, I will then rebuild the table.  That threshold changes based on the table in question, but my general rule is once the heap has 10% or more forwarded record pages in comparison to total pages, I'm looking to rebuild the heap.  Most of the heaps in this database don't suffer from forwarded records, so I don't have to worry about it too much, but that's not necessarily true for your situation.

Also, there are situations where heaps may not properly deallocate empty pages. Basically, if you find your workloads against a heap table is not primarily insert-driven or you find forwarded records are occurring often, you will want to look into clustering your table with a clustered index.


SQL - Query on table creation, referential integrity

Your scenario makes no sense. You want something like

CREATE TABLE B(x INT REFERENCES A(w) ON DELETE (SET NULL | CASCADE | NO ACTION)

What you have is a 1-1 relationship between your tables, so x should really be in table A - you are trying to implement a parent-child relationship with only one possible child. Furthermore, you are trying to SET a primary key field to NULL, which is not possible. It is possible to have a UNIQUE KEY with multiple fields with NULL values for some of those fields. To answer your question directly, it will violate the PRIMARY KEY constraint and will throw an error at some point, either at table declaration or during testing/production.


ST_Distance_Sphere has first argument (SELECT coordinates FROM houses WHERE id = 7) then you close ), so (SELECT wkb_geometry... is second argument for round, which I suppose is not what you want.

So I believe you just got lost in parentheses - try rewriting code "in ladder" so every parenthesis has closing one in same indentation...

based on your working query - are you trying to do this?

SELECT round(
  ST_Distance_Sphere(
    ((SELECT coordinates FROM houses WHERE id = 7 LIMIT 1)
  ,
  (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <-> (SELECT coordinates FROM houses WHERE id = 7) LIMIT 1))
)) AS DISTANCE;


Correlated sub-query on the same table with only one alias

No, this is wrong. The standard behaviour is that in WHERE id = ... in the subquery the id refers to the closest table in scope. And that is t2. It would refer to my_table in the external query only if t2 had no id column.

You are lucky though because the WHERE id = t2.id - which is translated as WHERE t2.id = t2.id is not actually needed because you used WHERE id IN (subquery). If you had an EXISTS  version, it wouldn't work as you expected.

So, the proper ways to write the query would be either prefix all column references:

SELECT t1.*
FROM my_table AS t1
WHERE ... AND t1.id IN
              (SELECT t2.id
               FROM my_table t2
               WHERE --- t1.id = t2.id AND  -- remove, we don't need this
               ... );

Or be very careful and prefix only when needed:

SELECT *
FROM my_table AS t1
WHERE ... AND id IN
              (SELECT id
               FROM my_table t2
               WHERE --- id = t2.id AND  -- remove, we still don't need this
                 ... );

I would still prefer the EXISTS version for such queries:

SELECT t1.*
FROM my_table AS t1
WHERE ... AND EXISTS
              (SELECT *
               FROM my_table t2
               WHERE t1.id = t2.id AND  -- we do need "t1". here
               ... );

or (if you are always very careful):

SELECT *
FROM my_table AS t1
WHERE ... AND EXISTS
              (SELECT *
               FROM my_table t2
               WHERE t1.id = id AND    -- we need only the "t1". here
                                       -- the second ref, = id is translated
                                       -- to:   = t2.id
               ... );


Database design for a table with both bulk insert and single row insert

1a) If the two insert methods create the same data then making them one table seems to make the most sense. After a bulk load I would recomend running maintenance scripts to make sure that all indexing is up to date.

1b) The only way to know if the bulk insert and the single insert would effect each other in this specific case would be to test it. With sufficient hardware this would not be an issue.

2) Both SQL Server and Oracle are mature products that can handle what you are describing very well. It sounds like you have a preference for Oracle which should be able to handle what you are attempting.

3) SSIS would be a great solution for what you are describing concerning loading millions of rows into SQL Server.


The error is happening because the error being thrown part of a recompile error due to deferred name resolution.  Looking at SQL BOL those aren't trapped when they happen at the same level as the try...catch.  However, if it's happening at a different level, either as dynamic SQL or a SP call, then it will get caught and rolled back.

Using Profiler you can see that the "alter table foo add x dog" statement recompiles before executing and then errors and bypasses the catch block.

If you wrap the statements in dynamic SQL the error isn't returned to Profiler and the transaction is rolled back


SQL get difference of row data in another row

fiddle

WITH cte (day_of_month, stock) -- add pseudo-records
AS
( SELECT day_of_month, stock FROM test
  UNION ALL
  SELECT 1,0
  UNION ALL
  SELECT 31,1 -- June have 30 days
),
cte2 (leadday, day_of_month, stock) -- calculate lead
AS
( SELECT LEAD(day_of_month) OVER (ORDER BY day_of_month, stock), day_of_month, stock
  FROM cte
)
SELECT SUM(leadday - day_of_month) -- obtain data we need
FROM cte2
WHERE stock = 0;

To make sure the result is correct both when there exist the record for the first day of month and when the last record have stock = 0 uncomment appropriate values for test table data.


Complex Delete Cascade Including Self-Referencing Table

I think I have captured what you need in this basic design:
db<>fiddle
ElementBase
Self-fk for the hierarchy:
CREATE TABLE dbo.ElementBase
(
    id integer NOT NULL,
    parent_id integer NOT NULL,
    element_type char(1) NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.ElementBase id]
        PRIMARY KEY CLUSTERED (id),

    -- fk target
    CONSTRAINT [UQ dbo.ElementBase id, element_type]
        UNIQUE NONCLUSTERED (id, element_type),

    -- self fk
    CONSTRAINT [FK dbo.ElementBase parent_id id]
        FOREIGN KEY (parent_id)
        REFERENCES dbo.ElementBase (id),

    -- valid element types
    CONSTRAINT [CK dbo.ElementBase element_type]
        CHECK (element_type IN ('a', 'b', 'c')),

    -- for maintenance
    INDEX [IX dbo.ElementBase parent_id]
        NONCLUSTERED (parent_id)
);

BData
Cascade delete from ElementBase:
CREATE TABLE dbo.BData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'b') PERSISTED,

    -- id key
    CONSTRAINT [PK dbo.BData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Bdata ElementBase id, element_type]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE CASCADE
);

AData
No cascading delete from ElementBase; SET NULL cascading delete from BData:
CREATE TABLE dbo.AData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'a') PERSISTED,
    b_element integer NULL,

    -- id key
    CONSTRAINT [PK dbo.AData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Adata ElementBase id, element_type]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE NO ACTION,

    -- fk to BData
    CONSTRAINT [FK dbo.AData dbo.BData id b_element]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE SET NULL,

    -- fk lookup
    INDEX [IDX dbo.AData b_element]
        NONCLUSTERED (b_element),
);

CData
No cascading delete from ElementBase; SET NULL cascading delete from BData:
CREATE TABLE dbo.CData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'c') PERSISTED,
    b_element integer NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.CData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Cdata ElementBase]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE NO ACTION,

    -- fk to BData
    CONSTRAINT [FK dbo.CData dbo.BData b_element id]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE CASCADE,

    -- fk lookup
    INDEX [IDX dbo.CData b_element]
        NONCLUSTERED (b_element),
);

S
Cascading delete from BData:
CREATE TABLE dbo.S
(
    s_id integer NOT NULL,
    b_element integer NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.S s_id]
        PRIMARY KEY CLUSTERED (s_id),

    -- fk to BData
    CONSTRAINT [FK dbo.S dbo.BData b_element id]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE CASCADE,

    -- fk lookup
    INDEX [IDX dbo.S b_element]
        NONCLUSTERED (b_element),
);

ElementBase Instead of Delete Trigger
This handles deleting related items within ElementBase, then cascading deletes to AData and CData. Cascade deletes to BData and S are handled by RI:
CREATE OR ALTER TRIGGER [dbo.ElementBase IOD Cascade]
ON dbo.ElementBase
INSTEAD OF DELETE AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    -- Exit if no work to do
    IF NOT EXISTS (SELECT * FROM Deleted) RETURN;

    -- Holds ElementBase rows identified for deletion
    CREATE TABLE #ToDelete
    (
        id integer PRIMARY KEY,
        element_type char(1) NOT NULL
    );

    -- Find all related ElementBase records
    WITH R AS
    (
        -- Anchor: parent ElementBase rows
        SELECT D.id, D.element_type
        FROM Deleted AS D

        UNION ALL

        -- Recursive: children
        SELECT EB.id, EB.element_type
        FROM R
        JOIN dbo.ElementBase AS EB
            ON EB.parent_id = R.id
            AND EB.id <> R.id
    )
    INSERT #ToDelete
        (id, element_type)
    SELECT DISTINCT
        R.id,
        R.element_type
    FROM R
    OPTION (MAXRECURSION 0);

    -- Delete related CData records (manual cascade)
    DELETE CD
    FROM #ToDelete AS TD
    JOIN dbo.CData AS CD
        ON CD.id = TD.id
    WHERE
        TD.element_type = 'c';

    -- Delete related AData records (manual cascade)
    DELETE AD
    FROM #ToDelete AS TD
    JOIN dbo.AData AS AD
        ON AD.id = TD.id
    WHERE
        TD.element_type = 'a';

    -- Delete ElementBase (BData, S records via cascade)
    DELETE EB
    FROM #ToDelete AS TD
    JOIN dbo.ElementBase AS EB
        ON EB.id = TD.id;
END;


MySQL corruption even after full dump and re-import

Paolo, can you please provide the mysql error log?  If one isn't available, you can add one by adding this line to your my.cnf file

[mysqld]

log-error=/var/log/mysql/mysql.err

One possible idea to narrow down your research.

You already know how to remove the ibdata and iblog files to create new ones.   If you just want to test your mysql installation.

Remove the Innodb_force_recovery or comment it out
Backup your databases : mysqldump --all-databases -u root -p | gzip -9 > all_databases.sql.gz
Move your MySQL data directory somewhere else: mv /var/lib/mysql{,.bak}
Create a new MySQL data directory: mkdir /var/lib/mysql
Give rights to the MySQL user: chown -R mysql:mysql /var/lib/mysql
Start MySQL: /etc/init.d/mysql start
Run a tail -f on the mysql log you added to my.cnf: tail -f /var/log/mysql/mysql.err

If MySQL isn't running then something else on the system level isn't right.  As Gopinath's link mentionned.  Is there enough disk space: df -h and inodes: df -i? Is you file system healthy? A system reboot or using fsck on the disk partition where MySQL is installed, would detect and possibly fix file system problems.

If MySQL comes back, then you can restore your databases:

zcat all_databases.sql.gz | mysql -u root -p

Send us the logs after completing the steps I sent, and we'll take it from there.


How different are the top DB vendor's SQL (DML-) dialects from each other?

ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything.  Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL.  Of course as each company seeks to differentiate their products, they will develop additional functionality beyond the standards.
From w3schools:

Although SQL is an ANSI (American
National Standards Institute)
standard, there are many different
versions of the SQL language.
However,
to be compliant with the ANSI
standard, they all support at least
the major commands (such as SELECT,
UPDATE, DELETE, INSERT, WHERE) in a
similar manner.
Note: Most of the SQL
database programs also have their own
proprietary extensions in addition to
the SQL standard!

The differences between the SQL dialects is too great in the more specialized areas you listed to simplify the problem down to a “rule of thumb”.  In these situations you probably should convert the entire statement for each database.  By doing this each statement can be optimized for each platform taking advantage of the special features each database can provide.  Writing SQL to lowest common denominator (ANSI) may simplify migration, but at the cost of performance.
SQL Dialects Reference gives some useful comparisons of the SQL variants.  For more information I recommend searching for the specific dialects you are switching between like this:
   differences between mysql and oracle sql


PAGE compression in SQL Server effect on running queries

Offline ALTER ... REBUILD takes a big fat schema modification lock on the table with absolutely 0 concurrency (not even dirty reads can scan the table).

Online ALTER ... REBUILD, as the name suggests, allows for any concurrent query or DML operation.

The MSDN article How Online Index Operations Work describes the three phases (prepare, build and finalization) and the object locks required in each phase (IS, IS, SCH-M). The builder operates in batches and acquires data locks as it makes progress but it will back off on conflict, and there is some special sauce on handling deadlocks with the builder:

  Deadlocks between the index builder transaction that is holding the
  batch transaction locks and DML statements are possible, but are
  handled internally so that neither the DML operation nor the index
  builder transaction should terminate during the build phase due to a
  deadlock.

More details are in the Online Indexing Operations in SQL Server 2005 whitepaper.

Now that being said, for a 15B rows DW table the best option may be Columnstore Indexes.


What is the Maintenance DB asked by pgAdmin?

It is worth mentioning that you are free to define any existing database in the cluster as "Maintenance DB". You are not limited to the options suggested by the interface, which are just typical defaults.

This is particularly useful for users with limited permissions or read-only access. Access to a single DB is simpler with the same DB as "Maintenance DB". Related:

Do all users need access to the postgres database in pg_hba.conf?

What is the maintenance db? The manual:

  The postgres database is also created when a database cluster is
  initialized. This database is meant as a default database for users
  and applications to connect to. It is simply a copy of template1 and
  can be dropped and recreated if necessary.


Storing email,phone,fax and url in same table

You should do something like the following:

CREATE TABLE client
(
  client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  client_name VARCHAR(100)
  .. other
  .. client
  ., fields
);

and

CREATE TABLE contact
(
  contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT,
  contact_type VARCHAR(3),  -- (e.g. 'URL', 'PH', 'FAX'  &c.)
  contact_value VARCHAR(256),
  FOREIGN KEY (fk_contact_client) REFERENCES client (client_id)
);

A few things to note:

No need for a contact_type table unless there are many (rule of thumb, more than ~7 and/or if your codes are not intuitive).
Of course, you can add a contact_type child table to contact if you wish, in the same manner as contact is a child to client. Whether you decide you want to do this will depend on your use case and the number of contact_types.
It's a pity that MySQL doesn't have CHECK constraints, which would be ideal for a small number of contact types. Whatever you do, don't use an ENUM type, they are evil
If restricting/controlling contact_type is important to you, use a lookup table. This is not "clutter" - many small reference  tables are a "good thing" and MySQL can cope with 1000's of tables. Take a look at the first reference in my answer here - avoid the OTLT (One True Lookup Table) trap. Also, see here and here for further discussion ( Joe Celko is a big hitter in the world of SQL).


What are the possible considerations and drawbacks of storing binary image data directly in a table?

You haven't specified the database platform your considering but at this size/scale, it's unlikely to matter.

5kb per record is trivial. 1 million 5kb records is < 5GB, still trivial. 10 million 5kb records... still not something to lose any sleep about.

If we were to get platform specific, a typically exhaustively researched white paper by Paul Randall on SQL Server filestream storage suggests that it outperforms table storage where files are 1MB or greater in size. Below 1MB file sizes, the positives are primarily around filestream bypassing the buffer pool.

The positives for database storage:

Transacted. The binary data is tied to it's metadata, not exposed by a filesystem, safe from accidental deletion.
Reduced backup overhead. 1 million records in a 5GB file vs 1 million 5kb files.
The natural companion to 2), faster restore.

Edit: The negatives (as suggested by Aaron)

Requires programatic access.
Cost of storage. More of an issue if you have to deal with platform restrictions on size e.g. 50GB on Azure.


Get the maximum updated_at for a given id from each table, combine the results into one row set and then get the maximum from it

SELECT
  MAX(updated_at)
FROM
  (
    SELECT MAX(updated_at) AS updated_at FROM table1 WHERE id = @param_id
    UNION ALL
    SELECT MAX(updated_at) FROM table2 WHERE id = @param_id
    UNION ALL
    SELECT MAX(updated_at) FROM table3 WHERE id = @param_id
    UNION ALL
    ...
  ) AS derived
;

This is much more efficient then combining the tables first and then filtering and aggregating the combined set, even though the code would look simpler that way.

This method can also benefit from an index on (id, udpated_at) in each table, while probably no index would help if you decided to combine whole tables first.


When to break down attributes in tables into sub-tables for normalisation

Let's say you create the table and give it two columns - AssetTypeId and AssetTypeName. Both will be unique by definition so both will be candidate keys. This table is normalized.

Since both columns are a candidate key whichever of these is used in Asset it will still be normalized.

One consideration may be on-disk data size. With only about a dozen asset types a single byte would suffice for AssetTypeId whereas AssetTypeName may require 20 or more. With "tens of thousands" of assets the saving would be small (100kb or so). With lots of columns this may add up, however, so its worth thinking about. If you are using columnar storage or data compression likely this saving is already happening under the covers within the DBMS.

The flip side to having a separate tale is if you need to return the asset type name to the client an additional join will be required. You know your workload best. You must determine where the balance of benefits lies with your application. Neither way is "wrong".


Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql

To improve the speed of that query, you need the following index:

create index on category_member ((p::float8/q));


I was informed this has the potential to fill up the transaction log . . . and it would be better to batch the updates

It would be better if it didn't fill up the transaction log.  But the proposed remedy is the wrong one.

You should size your transaction log to support your transactions, not the other way around.

  Does the second approach yield any benefit in terms of the transaction log on large table updates?

Yes.  The transaction log can be reused between batches, so long as all the batches aren't in a transaction.

  increasing the disk space to support that would be troublesome

Yes, sometimes you do have to code around space issues, but coding isn't free.  Spending money on infrastructure to reduce coding time is usually a wise move.

Also if you are trying to minimize log space, consider loading an empty table with your query, and then performing an ALTER TABLE … SWITCH.  Loading the new table can be minimally logged, and the ALTER TABLE is a metadata operation.

This will also enable you to perform the operation in a transaction, and not commit intermediate results.


Troubleshooting high network traffic between primary and secondary replicas

If you have high outbound network activity on the primary server, and high inbound network activity on the secondary server, it is very likely that there is an increase in logged (write) actions in the availability group databases during this period on Saturdays.  these are getting synchronized to the secondary, resulting in a lot of network activity.

One option for catching this would be to log sp_WhoIsActive to a table during this time period.  You can come back and review these logs to see what queries were running to produce all of this extra AG sync activity.

You mentioned you ruled out scheduled jobs, but this sounds suspiciously like a maintenance activity - rebuilding indexes is something often done on weekends, and it produces tons of traffic in AGs.


How to remove job history for a TIME PERIOD in SQL Server

You could just delete from the sysjobhistory table - as this is essentially what sp_purge_jobhistory does under the covers anyway:

DELETE msdb.dbo.sysjobhistory
 WHERE run_date BETWEEN '2019-01-04' AND '2019-01-15';

Though, as you have pointed out - it isn't officially supported and could have unintended consequences. With that in mind, there is an official supported way to do it using the JobServer class of SQL Server Management Objects:

The below is in C# because that is just what I prefer, but the documentation also has examples of doing the same in PowerShell:

// connect to the local instance
Server server = new Server();

// get the SQL Server Agent associated with the local instance
JobServer jobServer = server.JobServer;

// create a new job filter based on the criteria needed
JobHistoryFilter jobHistoryFilter = new JobHistoryFilter()
{
    StartDate = new DateTime(2019, 1, 4, 0, 0, 0),
    EndDate = new DateTime(2019, 1, 15, 23, 59, 59)
};

// now purge all data returned by the filter
jobServer.PurgeJobHistory(jobHistoryFilter);

And that's it! You could also condense it all into one call chain:

new Server().JobServer.PurgeJobHistory(new JobHistoryFilter()
{
    StartDate = new DateTime(2019, 1, 4, 0, 0, 0),
    EndDate = new DateTime(2019, 1, 15, 23, 59, 59)
});

I haven't put any error handling's in here but you should note that it could fail if you do not have permission to connect to the SQL Server, the SQL Server Agent, or if you do not have permission to query/modify msdb.

Here are some more useful links (and they also have PowerShell examples on them as well):

JobServer.PurgeJobHistory

JobHistoryFilter

Administrative Tasks in SMO

JobServer


SQL Arrays are returned as strings in PHP

Using PHP with SQL Arrays

I would highly suggest never using PHP. However, when you can't avoid it you may find this post useful on reading PostgreSQL arrays as PHP Arrays. People have been complaining about this for 15 years now. PHP doesn't migrate SQL arrays into native PHP array structures.

Getting around the problem with JSON

The new method of working around PHP's long-standing inadequacies is to use JSON to move the array.

SELECT array_to_json(pg_array_result) AS new_name FROM tbl1;
$array = json_decode($returned_field);

Attribution: shamefully stolen from michaelbn's answer on StackExchange..

Getting around the problem with unnest

You have another option to use unnest and return a result set which PHP can handle.

SELECT * FROM unnest(ARRAY[1,2,3]) AS t(x);
 x
---
 1
 2
 3
(3 rows)


Dynamic Count of items +/- 15% per row value

Not sure exactly what you want, but I imagine it's something like this. Here we use a INNER JOIN and check the right table for the range.

-- sample data
WITH t(x) AS (SELECT generate_series(1,10))

SELECT t1.x,
  t1.x*0.85 AS lower, -- just so you can see the lower
  t1.x*1.15 AS upper, -- just so you can see the upper
  count(*)
FROM t AS t1
INNER JOIN t AS t2
  ON t2.x BETWEEN t1.x*0.85 AND t1.x*1.15  -- JOY
GROUP BY t1.x
ORDER BY t1.x;

Output,

 x  | lower | upper | count
----+-------+-------+-------
  1 |  0.85 |  1.15 |     1
  2 |  1.70 |  2.30 |     1
  3 |  2.55 |  3.45 |     1
  4 |  3.40 |  4.60 |     1
  5 |  4.25 |  5.75 |     1
  6 |  5.10 |  6.90 |     1
  7 |  5.95 |  8.05 |     3
  8 |  6.80 |  9.20 |     3
  9 |  7.65 | 10.35 |     3
 10 |  8.50 | 11.50 |     2
(10 rows)


"Logon failed for login 'sa' due to trigger execution." When No SA Login Triggers Defined

"Logon failed for login 'sa' due to trigger execution."

For security reasons logon triggers don't report errors to the end user, but they are captured in the SQL Log.  From Docs:

  all messages originating inside the trigger that would typically reach
  the user, such as error messages and messages from the PRINT
  statement, are diverted to the SQL Server error log.

Logon Triggers

  Is there another explanation for a logon trigger not allowing a login that it does not check against?

Of course you could always have a bug in one of your logon triggers.  Check the SQL Logs.  And of the Logs don't clearly indicate which trigger failed and why, then add more telemetry (PRINT or RAISERROR) to your triggers so that next time they will.


Linked servers - Upgrading from 2005 to 2012

More than focusing on other servers that link to your current server, I would worry about applications. In complex environments, some applications may be out of control and keep pointing to your old server.

In these cases, this is the plan I am using:

install the new server with the same instance name as the old one
migrate all server objects, including logins and everything else
switch off or rename the old server
add a CNAME in your DNS to point the old server name to the new
server name

This way, you don't have to worry about linked servers, ETL jobs or applications still pointing to the old server.

Obviously, this assumes that you already have tested everything to be compatible with the new version.

UPDATE

Based on your comments, if you still want to discover incoming linked servers, you can register all your servers and run a multi-server query like this:

select name
from sys.servers
where product = 'sql server'
    and data_source = 'your sql server 2005'


It's likely the disk space is the root cause. Watch the space free in the tmpdir location whilst the ALTER is running.


What are the advantages of Always Encrypted 2016 vs Encrypted Columns?

The major difference I know is

Normal Column Encryption

The normal Column encryption which was introduced from SQL Server 2005 uses function Encryptbycert to encrypt the columns. It is not as secured as compared to Always Encrypted in terms that with this method data is submitted as clear text in SQL Server and this data can be seen from traces. A DBA, who has admin access to SQL Server, can see the data so this is actually not fully secured.

Always Encrypted

With always encrypted the encryption is done at clients app by API, like  ADO.net,ODBC. Drivers are installed at clients end to do this encryption. This will not allow SQL Server to see text data hence not revealing it to DBA's and one who have admin access on SQL Server. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine.

Quoting from Docs.microsoft

  Always Encrypted makes encryption transparent to applications. An
  Always Encrypted-enabled driver installed on the client computer
  achieves this by automatically encrypting and decrypting sensitive
  data in the client application. The driver encrypts the data in
  sensitive columns before passing the data to the Database Engine, and
  automatically rewrites queries so that the semantics to the
  application are preserved. Similarly, the driver transparently
  decrypts data, stored in encrypted database columns, contained in
  query results.

I hope I am able to give you some insight into both technologies.


Unexpectively slow data loading into Oracle

There's some information missing that would be useful but my first approach would be to concatenate the files into a smaller number of large input files to cut down the file handling overhead. Look at the load method and check if the data profile supports the use of SQL Loader direct loads. If SQL loader is not appropriate maybe use external tables and add the APPEND hint to the INSERT.

You may be able to think about how the table is partitioned and if the partitioning is reflected in the files, i.e. each file being relevant to one partition. This might give you scope for making the load parallel based on the partition criteria.

The above assumes that you are inserting data but if you have updates in there as well then I would go for external tables with a merge statement.
This has worked for me with 10g in the past. I had a shell script loading each file based on its suitability for each loading method.
We loaded up to 80 million records in around 3 to 4 hours from, in our case, hundreds of files rather than millions. So taking a complete guess at 100 records per file you should be able to load them in less than a day.


Testing an Oracle DB connection before it is made - ERROR?

The error is telling your user login username or password is incorrect. Remember that from 11g onwards, passwords are case sensitive.

The test & connect button tests if the entered details (TNSName, port etc) are correct & then proceeds to connect to the database. In your case, your credentials are wrong & you're getting the error message.


Unable to add primary key to innodb table without one

You can have an auto-Incrementing column, as long as there is an index (key) on it, to fix this issue you need to execute those commands:

ALTER TABLE my_wonky_table ADD COLUMN count SMALLINT NOT NULL;
ALTER TABLE my_wonky_table ADD INDEX (count);
ALTER TABLE my_wonky_table CHANGE count count SMALLINT NOT NULL AUTO_INCREMENT;
ALTER TABLE my_wonky_table ADD PRIMARY KEY (id, count);


Merge Replication From Multiple Express to Central

Is this even possible

Yes.  Once you have the subscriptions created and initialized then you insert your data into the tables created on the subscribers.  The next time the subscriptions synchronize, the data will be sent to the publisher.


Does mysqldump use in memory pages during backup?

By "in memory", I assume you are referring to cached data and index blocks.

mysqldump issues a bunch of SELECTs, which use the caching that is available to any other SELECTs.  Since it does not dump the indexes, most cached index blocks do not help the the dump process.

Xtrabackup has some extra hooks into InnoDB to help achieve a 'consistent' dump in a less intrusive way.  But still the caching is in effect.


Measuring Always On Availability Group potential data loss

I've occassionally created specific tables for this purpose, sometimes called "tracer" or "canary" tables. It has one column which is a datetime. A job runs on the source system inserting the current time. This runs at whatever granularity you need - minutes apart was sufficient for me. Read the max value from the secondary & that's how far behind you are.


If I understand correctly, this should give you what you're looking for.

SELECT R.*, COALESCE(RS.Rank, 0) AS Rank
FROM Runners AS R
  LEFT OUTER JOIN RaceStandards AS RS
    ON R.RaceN = RS.RaceN
    AND (
      (RS.Direction = '+' AND R.FromDist >= RS.FromDist AND R.ToDist <= RS.ToDist)
      OR (RS.Direction = '-' AND R.FromDist <= RS.FromDist AND R.ToDist >= RS.ToDist)
    )


WHERE clause not preventing return of row but changing values for aggregate function (here: count)

This is standard SQL behaviour for pretty much all databases.

There are three ways how rows are returned:

without aggregate functions, the query returns one row per row;
with aggregate functions, but without grouping, the query returns exactly one row;
with aggregate functions and grouping, the query returns one row for each group (i.e., for each unique combination of values in the grouped columns).

In other words, aggregating without grouping behaves as if there is a single group, i.e., as if you had written GROUP BY () (a GROUP BY clause with an empty list results in a single group 'from nowhere', similar to how a query without FROM results in a single row 'from nowhere').

To prevent returning this single-group row, you can

add an explicit GROUP BY, so that you end up in case 3. above (after the WHERE has filtered out all rows, there is no value from which a group could be created):

SELECT
   'something confidential',
   count(CURRENT_DATE)
WHERE FALSE
GROUP BY 1;  -- refers to the first column

move the aggregation into a subquery, so that the permission check affects the output of that:

SELECT *
FROM (SELECT
          'something confidential',
          count(CURRENT_DATE)
     ) AS subquery
WHERE FALSE;

move the permission check into the HAVING clause (if this is possible), so that the output for the single group is thrown away:

SELECT
   'something confidential',
   count(CURRENT_DATE)
HAVING FALSE;


MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

This very much depends on the data present in the table at the time of the UPDATE.

If, for instance, the table has 1,000,000 rows where value1 = 'foo' and only 1 row where value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; will only actually update a single row.  Whereas  UPDATE table1 SET value1='foo' WHERE value2='bar'; would update 1,000,001 rows.

If you are talking about a table with only two rows, the difference is entirely negligible.

If you were using SQL Server and had a filtered index like WHERE value2 = 'bar' AND value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; would most likely use the index instead of scanning the entire table.


How to wait between PostgreSQL DB restore and reindexing?

Note that the REINDEX step only applies if you're updating an 8.3.x cluster in-place from x<5 to x>=5. It does not apply for dump and restore. You do not need to perform this step for what you are doing. You won't even be using 8.3, you'll be going straight from 8.2 to 9.2 (right?).

Also, as per the upgrade documentation, you should use the new PostgreSQL's pg_dump when preparing for an upgrade, not the old version.

That said, this really shouldn't be happening; see comments.


Comparing two tables for a UUID change and fix it

UPDATE tbl_a a
SET    uuid = b.uuid
FROM   tbl_b b
WHERE  b.template_folder_uuid_parent = a.template_folder_uuid_parent
AND    b.heading = a.heading
AND    b.uuid <> a.uuid

The last line is to avoid empty updates, which are often forgotten and can be very expensive with big tables.

I would first verify that this update does what you expect it to do before you apply it:

SELECT *
FROM   tbl_a a
JOIN   tbl_b b ON
       b.template_folder_uuid_parent = a.template_folder_uuid_parent
AND    b.heading = a.heading
AND    b.uuid <> a.uuid;


How to obtain row counts for all tables in Netezza?

How about:

SELECT TABLENAME,
       OBJTYPE,
       OWNER,
       CREATEDATE,
       USED_BYTES,
       USED_BYTES/1073741824 as USED_GB,
       RELTUPLES as "ROWS"
 FROM _V_TABLE_ONLY_STORAGE_STAT
 WHERE OBJCLASS = 4905 OR OBJCLASS = 4911
 ORDER BY TABLENAME;


SQL Server 2012 Installation Error on Windows 2008 Server

Please include the option "UpdateEnabled=False" in the defaultsetup.ini file which can be found in Media**X64\defaultsetup.ini**.
This works like a charm. Please let us know how this goes.


A failed login error will usually look something like this:

Login failed for user 'bob'.
Reason: Could not find a login matching the name provided. [CLIENT: 10.59.68.215]

If you're trying to isolate the issue to a particular server, the client's IP address should be clearly displayed in the error message, so that gives you at least something to work from.

If you're trying to pin it down further, fire up SQL Server Profiler. This was previously not available for some editions of SQL Server, but the SQL Server 2012 Express SP1 Management Studio finally includes it, meaning it's available to everyone!

Once you've logged in to your server with Profiler, use "Events Selection" tab and choose to  show "All events" and "All columns". You'll want to check the box just to the left of Security Audit --> Audit Login Failed, then use the Run button.

Then, simply leave profiler running until you see a hit or three. This will show you additional information including the Application Name, and the Client Process ID.

EventClass:      Audit Login Failed
ApplicationName: Microsoft SQL Server Management Studio
ClientProcessID: 4680

In a busy production environment, the more appropriate way to capture this information is to script out a server-side trace that captures the events to a .trc file on the server, or to use Extended Events.


A General Distribution Release (GDR) is simply a kind of hotfix, or patch that contains specific fixes, and is intended for installation on all SQL instances.  This is in contrast to a Quick Fix Engineering (QFE) hotfix, which is intended to be installed only if you experience a particular issue.

After a hotfix, it will also be included in the next Cumulative Update (CU) or Service Pack (SP).  But you can install the hotfix without taking all the changes included in the next CU/SP.

Going forward GDR hotfixes are only going to contain security fixes.

For SQL Server 2017+ a GDR is part of the Modern Servicing Model (MSM), which is replacing the older Incremental Servicing Model (ISM), which will continue to apply to SQL Server 2016 and before.  It's explained here.


SQL - Update table for a unique ID with multiple entries if one entry has variable in question

There multiple entry in table patient_encounter for a person_id, so you need to get the person_id with top 1

Try this

update patient_encounter
Set user_defined4 = 'DTC'
where person_id = (SELECT top 1 person_id from patient_encounter
                     where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')

EDIT as per comment, if you are looking to update all the record, use IN as below

update patient_encounter
    Set user_defined4 = 'DTC'
    where person_id IN (SELECT person_id from patient_encounter
                         where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')


Modelling an E-R diagram for a Pizza place database, need help representing the payment method for the customer

Credit card info should be in a separate table because normally, you need to have more than just a number. You will also need, at the very least, the card's expiry date. You may also need to store cardholder information (name, address, etc...) in case it is different from the name on the account. All of that extra information is only relevant to customers that pay by credit card. And will you allow multiple cards for one account? How will you handle cards that expire and need to be replaced? Delete the old record, mark as expired, add a new one? Or just update existing data?

And although it's not necessarily going to be reflected in an ER diagram, you have to consider security if you plan to implement this. You might want to start looking here: https://www.pcisecuritystandards.org/


IMO, the balance sheet, income statement and cash flow tables can be the same. Here's my take on it:

-- Companies
CREATE TABLE company (
    [id]      int NOT NULL PRIMARY KEY,
    [name]    varchar(255) NOT NULL
);

-- "Balance sheet", "IFRS Income statement", etc
CREATE TABLE statement (
    [id]      int NOT NULL PRIMARY KEY,
    [name]    varchar(255) NOT NULL
);

--- "Tangible assets", "Outstanding stock", etc
CREATE TABLE statementRow (
    [id]      int NOT NULL PRIMARY KEY,
    statementId int NOT NULL,
    rowOrder  int NOT NULL,
    rowTitle  varchar(255) NOT NULL,
    rowDescription varchar(max) NULL,
    rowProperties varchar(max) NULL,
    FOREIGN KEY (statementId) REFERENCES statement ([id])
);

--- The facts
CREATE TABLE statementFact (
    companyId      int NOT NULL,
    statementRowId int NOT NULL,
    [date]         date NOT NULL,
    amount         numeric NULL,
    PRIMARY KEY ([date], statementRow),
    FOREIGN KEY (companyId) REFERENCES company ([id]),
    FOREIGN KEY (statementRowId) REFERENCES statementRow ([id])
);

Advantages of this model:

You can have different types of balance sheets, income statements, etc, in order to cover future reporting needs
The model defines the ordering of the rows for each statement (don't rely in an identity column for that, because you won't be able to insert rows later on)
Using "date" instead of "year" allows you to publish statements more than once per year, i.e. per quarter or month, or even ad-hoc.
The rowProperties field allows you to add information like if the row should be boldface, italics or other formatting properties.
Optionally, you may want to move "companyId" from statementFact to "statement" if certain reports only apply to certain companies.


Current information
SQL Server Developer Edition is a free download starting with SQL Server 2014.
You can download SQL Server 2017 Developer Edition for Windows, Linux, and Docker from the Microsoft SQL Server downloads page.

SQL Server 2017 Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment.

Original answer
We can't advise on detailed licensing issues, that is something you should read about on the MSDN Subscriptions site, and contact support if necessary. With that said, I can respond to other points in your question:

Next to each download it says: "No product key is required". I assume this means that a 28 day trial is initiated after download?

You have a choice here. During installation, you are presented with a screen like:

If you choose the pre-populated product key, you are installing Developer Edition (not a trial) under the MSDN Subscriber licence terms. No product key is required for this option, because one is provided for you.

If you choose Evaluation, you are installing a time-limited trial of Enterprise Edition, with different licensing terms. This is just the same as if you had download Trial Edition from anywhere.

What is the difference between SQL Server Developer; SQL Server Web Edition and SQL Server Business Intelligence? I assume that SQL Developer can do everything that SQL Server Business Intelligence and SQL Server Web Edition can do?

SQL Server Developer contains all the features of SQL Server Enterprise Edition, just on different licensing terms. You can compare the various editions here:
SQL Server Editions
You cannot download software from MSDN Subscriber Downloads without a Microsoft account associated with a valid subscription. As it says on the Using Subscriber Downloads help page:

You must be signed in to your Microsoft account that is associated with your subscription in order to download products on Subscriber Downloads. If you are not signed in, you'll be prompted to sign in when you attempt to download a product or view product keys. Everyone, including non-subscribers, can view Subscriber Downloads, but only active subscribers can download products and keys.


Way to update many (>3000) records with unique data, using a character limited web form

The SET can be made a bit simpler:

SET info4 = 'BNID: ' +
  CASE guestid
    WHEN 12345 THEN '917546553'
    WHEN 67890 THEN 'B04695465'
      [etc, etc.]
                          --- no ELSE here
  END

WHERE info4 IS NULL       --- and this is needed

Saves 6 characters per case.

If you can create a table (even a temporary one) you could also use:

--- run once
CREATE TABLE #tmp
( guestid INT NOT NULL
, i4 VARCHAR(200)
, PRIMARY KEY (guestid)
) ;

--- as many times as needed
INSERT INTO #tmp VALUES
(12345,'917546553'),
...
(67890,'B04695465');

and then:

UPDATE guest
  SET info4 = 'BNID: ' + i4
FROM guest
  JOIN #tmp
    ON #tmp.guestid = guest.guestid
WHERE guest.info4 IS NULL


Why we need auxiliary instance in TSPITR

TL;DR to be able to restore data+undo without overwriting the entire current undo.

When you perform RMAN online backup, some transactions are in the middle of processing, they did change something but not yet performed their COMMIT. Transactional model of work ("atomicity") requires those transactions to be rolled back (completely undone) on recovery. Oracle engine does this automatically. Oracle engine does not allow onlining tablespace if this cannot be done, to protect user from seeing possibly inconsistent data. So do transactions save somehow the "consistent" data before they change it? Yes, in the undo tablespace (or a "rollback segment"). This is why, recovering a tablespace, you cannot "forget" it had undo, you need to recover undo from the same point in time (same SCN).

To sum up, Oracle engine needs to have undo data synchronized with from the same point in time as the main data.

Undo is, usually, put into a single tablespace despite it is being a mix of pieces of "consistent" information from many different data tablespaces. This is why you TSPITR to an auxiliary instance - to be able to restore data+undo without overwriting the entire current undo.

Once auxiliary is online, the auxiliary's data has been made consistent and auxiliary's undo is no longer needed.


We are running database maintenace with indexing the system, indexing fails due to locking?

If the requirement is to rebuild the indexes while letting users continue to work, you will find this extremely challenging, if not impossible, to meet if you can't do online index rebuilds, which are only available in Enterprise Edition.

There are really only two types of solutions:

Use online index rebuilds.
Do index REORGANIZE instead of REBUILD -- reorgs are always online operations, but may not do as good a job as a rebuild, and may run more slowly in comparison.

Regardless, instead of rolling your own scripts, I would highly recommend using an already-built-and-tested solution such as Ola Hallengren's maintenance scripts, which will intelligently maintain your indexes based on their current state of fragmentation. The indexes may not actually need to be rebuilt all the time.


Install MySQL for Windows from .zip and reset root password

Since you started mysqld with --skip-grant-tables, you cannot execute any standard GRANT, REVOKE, or SET PASSWORD commands. Notwithstanding, you can change the password of root@localhost as follows:
UPDATE mysql.user SET password=PASSWORD(‘passhere') WHERE user='root' and host='localhost';

Then, restart mysqld and you are back in business.
As to you being anonymous, if you login right now and run
SELECT USER() HowYouAttemptedToLogin,CURRENT_USER() HowYouWereAllowedToLogin;

You will note that the HowYouWereAllowedToLogin will have an blank username and some host.
GIVE IT A TRY !!!
UPDATE 2015-11-17 16:24 EST
The column known as password in mysql.user no longer exists in MySQL 5.7.
It was renamed authentication_string.
Proper Approach
What you should have done is run
mysqld --initialize

That would create the data folder for you.
The root@localhost was assigned a temporary password, which is visible in the error file starting with the following datetime and string
2015-11-17T20:56:02.175980Z 1 [Note] A temporary password is generated for root@localhost:

You could log in with it and then run ALTER USER
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

See the MySQL 5.7 Documentation on SET PASSWORD


;WITH x AS
(
  SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID)
  FROM dbo.your_table
)
SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL
FROM x
WHERE VAL < Prev_VAL
ORDER BY ID;

If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is about 4X as expensive:

;WITH x AS
(
  SELECT ID, VAL, Prev_ID = ROW_NUMBER() OVER (ORDER BY ID)
  FROM dbo.your_table
)
SELECT c.ID, c.VAL, p.VAL, Cumulative = c.VAL + p.VAL
FROM x AS c
LEFT OUTER JOIN x AS p
ON c.Prev_ID = p.Prev_ID + 1
WHERE c.VAL < p.VAL
ORDER BY c.ID;


MySQL - grant all privileges on *.* brings back Access denied

You did not upgrade properly try running:

/usr/bin/mysql_upgrade -u root -p


Believe it or not, although the products went EOL December 18, 2009, the last versions of MySQL Query Browser and MySQL Administrator are still available for download for free. Hey, I still use them today.

Click here and get it while Oracle isn't looking ... QUICK !!!


Date variable to filter result for all previous years

There are many ways to get the beginning of this year, I think the most intuitive is DATEFROMPARTS():

DECLARE @BeginningOfThisYear date = DATEFROMPARTS(YEAR(SYSUTCDATETIME()),1,1);

SELECT ... FROM dbo.Customers AS c
WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.Orders
    WHERE CustomerID = c.CustomerID
    AND OrderDate >= @BeginningOfThisYear
);

Less intuitive ways include:

DECLARE @BeginningOfThisYear date = DATEADD(DAY,
  1-DATEPART(DAYOFYEAR, SYSUTCDATETIME()),
  SYSUTCDATETIME()
);

And downright terrible ways include:

DECLARE @BeginningOfThisYear date = CONVERT(date,
  CONCAT(YEAR(SYSUTCDATETIME()),'0101'));


How Should I Configure Memory for Multiple Instances of SQL Server?

SQL Server will use as much memory as you can throw at it (pretty much), and it is always advisable to set a limit on every instance.  The default setting is 2147483647MB.

Depending on your usage and own unique circumstances, your settings may vary, but normally a good starting point is to set the max server memory between 75 and 90 percent, depending on how much RAM you have.  Glenn Berry share a good indication a few years back HERE.

If you have multiple active instances on a server, you might have to share the resources between them, for e.g. if you have 32GB of RAM with 3 instances, you should assign around 9500MB to each instance.  When you are using just one instance at a time, the others will not release the RAM they are using.  You could free up their RAM by stopping the service.

When you have other applications / services running, the amount of memory might have to be adjusted to accommodate them.


Research Database: Access and Ownership Policies

Welcome to office politics.  Things like NDAs regarding the disclosure of data can help with this.  Your adviser and the universities legal department should be able to help you get these papers drawn up.


Does "consolidation" of query statements improve query performance?

Reasonable question.  Let me set you straight on the relative costs of various components of the query.

OR is the de-optimizer.  Either of those formulations will work this way:

Scan the entire table
For each row, check the WHERE clause
Most or all of the OR parts will be evaluated to eliminate a given row, thereby making your suggest a small improvement.

Scanning the table and fetching every row is the most costly part.  Evaluating the WHERE is a minor part.

That is, any savings you get will be insignificant in the big picture.  And, focusing on getting rid of OR may provide more gain.

If you have a million rows in the table, then this formulation will run much faster:

SELECT .. FROM .. WHERE (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
UNION ALL
SELECT .. FROM .. WHERE (StreetNumber = 142 AND StreetName = "Jones" AND StreetSuffix = "St")
UNION ALL
SELECT .. FROM .. WHERE (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
...

but there are issues with such.  You will need an index:

INDEX(StreetNumber, StreetName, StreetSuffix)  -- (in any order)

If there might be duplicates, then use UNION DISTINCT instead of UNION ALL.

For up to a few hundred rows, just write it whatever is simplest for you (eg, OR without optimization).

You will soon find that your model for addresses is incomplete.  What about "Apt. 123" or "c/o ...".  Etc.  There is probably no good reason to split an address into multiple columns -- simply have a single string with all the parts written in a way that facilitates displaying.  Eliminating the AND would be a tiny optimization.


Mysql separate username from client table for performance and use myisam for it

The MyISAM vs InnoDB Myth

  "InnoDB is not good as MyISAM"

That's an old wives' tale.  Erase it from your mind.

InnoDB has improved a lot since that rumor was started.
You now have user information split across two tables; the small overhead of doing such is probably worse than having all the info neatly together in one InnoDB table.

Bottom line:  Use InnoDB for all tables.  There are very few exceptions to this simple rule.  In no particular order:

InnoDB tables usually have a 2x-3x larger disk footprint.  But, so what, disks are huge.
COUNT(*) without WHERE is 'instantaneous' in MyISAM.
2-col AUTO_INCREMENT -- standard in MyISAM; clumsy to simulate in InnoDB.  (Rarely asked for.)
Performance in obscure cases.  (No specifics come to mind at the moment.)
One might quibble that the differences in FULLTEXT constitute an issue.

On the flip side, Oracle has taken the stand that MyISAM will be removed from MySQL.

The CHAR vs VARCHAR Myth

  "CHAR is better than VARCHAR"

Another _old_wives' tale.  Even in MyISAM, that quote is often taken out of context.

Even in context it is rarely valid
If you have variable length data, the savings for I/O is higher than the alleged savings of CHAR over VARCHAR.
In InnoDB, CHAR and VARCHAR are mostly implemented identically.

Bottom line:  Use CHAR only for strings that are truly fixed length.

The need-to-optimize-the-little-things Myth

I'll start with the 'answer' first.

Even before looking at the data, there are other tasks.

Receive the query, possibly across a WAN.  (Up to milliseconds.)
Parse the tokens in the query.
Figure out which table is being used for each column named in the query.
Open the tables.
Invoke the Optimizer to deduce the best way to perform the query.  This will involve locating all the possible indexes, doing probes into the tables to gather statistics, etc.
Run the query.

In the grand scheme of things, locating a record is far more costly than anything that is done with the record.  (This is a generalization, not an absolute.)

Locate the record -- perhaps via an index, perhaps "next after" the last record fetched.
Fetch the block containing the record.  This is probably cached in the buffer pool, but it might need to be fetched from disk.  So, this step might be nanoseconds, or it could be milliseconds.
Dissect the block to find the row in question.  This might include scanning the "history list" if multiple transactions are running and the "isolation mode" needs to be consulted to decide which copy of the row is "visible".
Now that you have the row, the columns need to be picked apart -- even with off-word-boundary issues, byte scans, NULL checks, length checks (eg, for VARchar, etc, we are talking nanoseconds per column.
"Endianness" slips in about here.  MySQL can handle big-endian and little-endian hardware architecture with binary compatibility.  This implies that for some hardware-dependent situations, it must swap bytes to get the column value into the right "endianism".
Do something with the column.  This may be simply copying it intact; it may be applying a function (collation, summation, sqrt, whatever).  Again nanoseconds.

Now, what was your question?  Oh, yeah, you were concerned about some tiny part of the last step.

General

Word boundaries, for various hardware, software, and design reasons are not worth thinking about.
For large tables, I/O is a much bigger factor in performance than fixed versus variable-length things.
Fixed length in MyISAM had very few advantages.  Most vanish when you aren't doing UPDATE or DELETE + INSERT.
All columns in a row needed to be "fixed", else it was "variable".
InnoDB possibly has zero benefits from "fixed".
MyISAM was designed before variable-length charsets (utf8, etc) were added.  Even CHAR is effectively variable length when using utf8.

(And stop reading any MySQL reference that is over a decade old.)


How to provide the key value in a join when a match is not found

Use COALESCE

Purpose
COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

select c.customer, c.version, coalesce(u.name, to_char(c.userid)) as user c.description
from customerlog c
     left join users u on u.userid = c.userid
where c.customer='Orange'


If you want to restrict your votes to "one user can vote for another user once", you simply use the two user id:s as primary key. As for the vote, just use one column and make it an enum:

CREATE TABLE IF NOT EXISTS `user_votes` (
  `voter` int(11) NOT NULL,
  `voted_for` int(11) NOT NULL,
  `vote` enum('up','down') NOT NULL,
  PRIMARY KEY (`voter`,`voted_for`)
)


How to verify data dumps between two MS SQL and MySQL

There can be various reasons why file hashes different:

Line Endings differ;
Spacing might differ (tabs vs spaces);
Different layout for same data
Encoding (of the file that data is saved in)
Quotes (single vs double)
Case sensitivity

What I would do, is compare both files with diff tool. git diff can come in help here as it has ability to diff word by word and if you pass it --no-index option don't even have to place them in git repository. This command can help

$ git diff --no-index --color-words file1.txt file2.txt


when is it necessary to create aliases in the SQL Configuration Manager?

What aliases are used for in the SQL Server Configuration Manager?

Aliases are a way for the client to be redirected to a different location without having to change things such as the connection strings. It's akin to adding in a record to your system's HOSTS file.

I can't see the names (as you've properly redacted them which is a good thing) but if any of those names are the same as the connectivity question you have open then what's happening is that everywhere a client uses that name to connect, it'll be replaced with the information in the alias. This could completely be the reason why your connectivity no longer works after an IP change as the alias could be forcing an old address.

  Does it have anything to do that in this particular case the machines (publisher and subscriber) are on different domains?

It doesn't deal with any of that. It specifically says, "Wherever you see this name in a data source, replace it with this other information instead."


check the following link :

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

It will help you in detail about the Load infile query issue in mysql


You can't, as far as I'm aware.
One might try things like:
OPTION (TABLE HINT (sp, READUNCOMMITTED))

...but this will fail for things that aren't real tables.

Table-valued or OPENROWSET function 'sp' cannot be specified in the TABLE HINT clause.

It would also fail because the desired hint is semantic-affecting.
If the thing you need to access doesn't respond to setting the isolation level, you'll have to report the deficiency and hope for a fix.
There was a Connect item about DMVs and whatnot not respecting isolation level, and as best I recall, the official word was that isolation level was not guaranteed for system functions.
Aaron Bertrand said:

There are some built-ins that simply don't obey isolation level no matter how the hint gets there (e.g. OBJECT_SCHEMA_NAME().

Also, as Martin Smith remarked:

The issue is really how to apply it to a TVF - not a CROSS APPLY
SELECT * FROM sys.dm_db_stats_properties(1,1) WITH (NOLOCK)

also fails. But conversely this succeeds:
SELECT *
FROM sys.objects obj WITH (NOLOCK)
INNER JOIN sys.stats stat WITH (NOLOCK) ON stat.object_id = obj.object_id
CROSS APPLY sys.objects obj2 WITH (NOLOCK)


Merging tables with a 1 to 1 relationship in a database

If they are small reference tables then I would be more inclined to merge them than if they are not. If reference tables, then they are likely separated due to over the top normalisation and merging them makes séance from query performance perspective.

If they were large fact tables I would be less inclined to wade in before checking ramifications, as it could be that they were separated onto separate file groups with files on different spindles for parallel operations. If this is the case however, I would still be inclined to merge them, and consider table partitioning.

Either way, be sure to thoroughly check for Database and client application dependencies before Changing the table structure.


How to replace multiple parts of a string with data from multiple rows?

Here is an example of using a recursive cte to translate the variables

drop table if exists testing;
go
create table testing (id int, display varchar(16));
insert into testing values (1, 'Apple');
insert into testing values (2, 'Banana');
insert into testing values (3, 'Celery');
insert into testing values (4, 'Dragonfruit');
insert into testing values (5, 'Eggplant');

DROP FUNCTION IF EXISTS dbo.TranslateVariables
go
CREATE FUNCTION dbo.TranslateVariables
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (

--Common Table Expression for Translation
WITH TranslationTable
AS (
    SELECT FindValue = '{' + convert(varchar(5),id) + '}' ,ReplaceValue = display,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM testing
    )
--Recursive CTE to loop through the TranslationTable and replace FindValue with ReplaceValue
,RecursiveCte as
(
SELECT @StringValue AS StrValue
    ,(
        SELECT count(*)
        FROM TranslationTable
        ) AS cnt

UNION ALL

SELECT replace(StrValue, tt.FindValue, tt.Replacevalue)
    ,cnt - 1
FROM RecursiveCte
JOIN TranslationTable tt
    ON tt.rn = cnt )

SELECT StrValue
    ,cnt
FROM RecursiveCte where cnt = 0
    )
go

--Verify translation
SELECT *
FROM dbo.TranslateVariables('{1} {3}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

 StrValue     | cnt |
|--------------|-----|
| Apple Celery | 0   |

SELECT *
FROM dbo.TranslateVariables('{3},{4},{5}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

| StrValue                    | cnt |
|-----------------------------|-----|
| Celery,Dragonfruit,Eggplant | 0   |


Oracle 10 xe; backing up oradata folder enough to recreate a duplicate later?

List of files that you should backup:

select member from v$logfile;
select name from v$datafile;
select name from v$controlfile;
select value from v$parameter where name ='spfile';

If the last query returns nothing, backup %ORACLE_HOME%/database/initXE.ora (Windows) or $ORACLE_HOME/dbs/initXE.ora (Linux, Unix).

To make it a simple as possible, you can simply copy these files after the database was shut down.

An online backup requires ARCHIVELOG mode configured and using user-managed backup from SQL*Plus combined with OS commands, or RMAN.


Why are so many MPP solutions based on PostgreSQL instead of MySQL?

It is mostly a license issue.  These developments end up patching the code quite heavily, so if you were to deal with MySQL, you'd either have to open-source your code or be at the mercy of MySQL's corporate owner for the life of your business.  Some offers for MySQL get around that by implementing their work as a storage engine, but that doesn't offer all the flexibility that they need, and they invariably end up patching the MySQL core as well.


As there are only two possible results (one or two characters before the digit) you can do a brute force approach using MySQL's limited regex support:

case when delivery_postcode rlike '^.[0-9]'         -- a digit as 2nd character?
     then substring(delivery_postcode from 1 for 1) -- first char
     else substring(delivery_postcode from 1 for 2) -- first two chars
end


Insert into table is slow in the night but very fast during day time

The data isn't in memory when it runs at night, based upon the PAGEIOLATCH wait that you mentioned, but is in memory during the day. Your Page Life Expectancy is probably taking a nosedive at night due to all of the other maintenance that is going on. Having to get the data from disks and especially slow disks is much slower than if it were in memory already. Seems like you're going to need to add more memory since getting the data pages from disk is so slow due to the I/O issues you are experiencing.


Split a string into multiple rows and get the count for each of them

You could try something like this if you have a recent version of SQL Server! I've used PostgreSQL for this example - but SQL Server has the STRING_SPLIT function which appears to be very similar to PostgreSQL's regexp_split_to_table function.

I can't appear to get SQL Server's  function to work on dbfiddle.uk (Express Edition issue?). My own dbfiddle of the code below can be found here! There's also an SQL Server UNNEST function but the second SQL doesn't appear to be as compatible!

Create and populate your table -

CREATE TABLE reviewer(
Reviewer TEXT
);

data -

INSERT INTO reviewer
        ( Reviewer )
VALUES ('Person1,Person2,Person5')
,('Person3,Person1')
,('Person4');

and then run this SQL (suitably translated for SQL Server) -

SELECT rev, COUNT(rev)
FROM
(
  SELECT regexp_split_to_table(r.reviewer, ',') AS rev -- use STRING_SPLIT here!
  FROM reviewer r
) AS tab
GROUP BY rev
ORDER BY rev

Result -

Rev     count
Person1     2
Person2     1
Person3     1
Person4     1
Person5     1

You should always include your version of SQL Server when asking questions here. p.s. welcome to the forum!

You could also try something like this - but it would appear to be more difficult to translate the SQL - just an FYI - same result!

SELECT rev, COUNT(rev)
FROM
(
  SELECT rev
  FROM reviewer r,
  unnest(string_to_array(r.reviewer, ',')) AS rev
) AS tab
GROUP BY rev
ORDER BY rev


Does updating a jsonb column trigger updates on all expression indexes on that column?

Will I therefore suffer the full performance penalties of updating the indexes on jdoc->b and jdoc->c?

Yes.

  Or is PostgreSQL smart enough to notice that those values did not change, and that the indexes therefore does not need to be updated?

Nope, because the index probably did change, you just don't see that.

When you update the json document PostgreSQL writes a new version of the row that contains it to the table. There's a new row in the table and the old row gets marked as effectively deleted. (It's not that simple, but this is good enough.) The new row has a new address, and the indexes need to be updated to point to the new row address, even though the value in the index might be exactly the same.

PostgreSQL has some optimisations that let it avoid such index updates in a few cases (read about HOT if you want to know the gory details) but they're no help if you are updating an indexed column.

  If I suffer the full performance penalty, is there any way to avoid it?

No, there isn't. You'd have to split the json document into two columns.

This will not change with 9.6's new support for updating json documents. It'll still write the whole updated document to a new TOASTed field as a new tuple, and that'll require index updates. It's more efficient to create the new json document but no faster (or smaller) to update write it to disk and the indexes.


Why does elimination of an if-statement cause notable speed up in my function?

Sample data, tests with the actual functions + possible rewrite can be found below

DECLARE @objectid uniqueidentifier
select top(1) @objectid = GUIDS
from #TEMP;

-- function 1 ,omitted the return 0

IF (SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 ) )
        IS NOT NULL
    BEGIN
      SELECT 1;
    END

-- Function 2 changed the returns

    DECLARE @result BIT
    SET @result = 0
    SELECT TOP(1) @result=1 FROM StateChanges
        WHERE LinkedObjectId=@objectId AND
           (ChangeType = 1 OR ChangeType = 2 )
    SELECT @result;

Function V1:

A Nested loop left outer join is present in the plan, as to facilitate the IF ... IS NOT NULL check

Function V2

No nested loop operator, a more simple, straightforward plan

As seen, a nested loop left join was added because of the IF check, if we execute the query without the IF statement:

DECLARE @objectid uniqueidentifier
select top(1) @objectid = GUIDS
from #TEMP;
SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 );

No more Nested loops and the same plan as V2 is seen

Which should explain the increased worker time, since adding the NL join to check for the IS NULL will increase CPU usage.

To answer your question, yes the IF statement should increase your CPU usage, as a result of the additional operators.

Testing with the previous defined tables, indexes, functions and adding some data

CREATE TABLE #TEMP(GUIDS uniqueidentifier)
DECLARE @GUID uniqueidentifier
declare @i int =1
WHILE @i <= 1000
BEGIN

SET @GUID = NEWID()
INSERT INTO #temp(GUIDS)
VALUES(@GUID)
SET @I += 1
END

SET NOCOUNT OFF;
INSERT INTO [dbo].[StateChanges]([RecordId],[LinkedObjectId], [ChangeType],[ChangeTime], SomeColumnsHere )
SELECT GUIDS, GUIDS , 5, getdate(), 'Blabla'
from   #TEMP

Testing the functions

SELECT [dbo].[ObjectHasInterestingChanges_v1](GUIDS)
FROM #temp;

  SQL Server Execution Times:    CPU time = 125 ms,  elapsed time = 125
  ms.

SELECT [dbo].ufn_ObjectHasInterestingChanges_v2(GUIDS)
FROM #temp ;

  SQL Server Execution Times:    CPU time = 125 ms,  elapsed time = 132
  ms.

they appear to get the same CPU time with a low dataset and no matches (nothing matches because Changetype is not 1 or 2 in my data).

On another note, you could change the function to outer apply

SELECT ISNULL(a.bla,0)
FROM #TEMP
OUTER APPLY
(SELECT TOP(1) 1 as bla  FROM StateChanges
        WHERE
    LinkedObjectId=GUIDS AND
            (ChangeType = 1 OR ChangeType = 2 ) ) as a

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 5 ms.

Test 2, adding some mathing data

DECLARE @GUID uniqueidentifier
declare @i int =1
WHILE @i <= 1000
BEGIN

SET @GUID = NEWID()
INSERT INTO #temp(GUIDS)
VALUES(@GUID)
SET @I += 1
END

SET NOCOUNT OFF;
INSERT INTO [dbo].[StateChanges]([RecordId],[LinkedObjectId], [ChangeType],[ChangeTime], SomeColumnsHere )
SELECT GUIDS, GUIDS ,2, getdate(), 'Blabla'
from   #TEMP;

Testing the functions and the rewrite again

SELECT ISNULL(a.bla,0)
FROM #TEMP
OUTER APPLY
(SELECT TOP(1) 1 as bla  FROM StateChanges
        WHERE
    LinkedObjectId=GUIDS AND
            (ChangeType = 1 OR ChangeType = 2 ) ) as a;

  SQL Server Execution Times:    CPU time = 31 ms,  elapsed time = 21
  ms.

SELECT [dbo].[ObjectHasInterestingChanges_v1](GUIDS)
FROM #temp;

  SQL Server Execution Times:    CPU time = 375 ms,  elapsed time = 378
  ms.

SELECT [dbo].ufn_ObjectHasInterestingChanges_v2(GUIDS)
FROM #temp ;

  SQL Server Execution Times:    CPU time = 344 ms,  elapsed time = 352
  ms.


fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows

This isn't really an answer, more of a discussion of the problem.  If I understand correctly, you would have 1 row with twelve columns turn into 479,001,600 rows.  From the initial row you provided there are no duplicate values within the columns of the same row, so it doesn't appear that you are trying to eliminate duplicates within a row.

You didn't say how many rows are in your initial table, but this should mean that 100 rows in the initial table would equate to 47,900,160,000 rows in the resulting table.  The only way there could be duplicates there is if two rows in the initial table had a row with the same values as another row.

If those values are in the same order, then a simple select distinct * from t1 would eliminate the duplicates.  If instead the values are in a different order, then you actually have to determine the permutations for each row and make sure only one of those is in the initial table.

For smaller values of n, it would probably be easiest just to determine all the permutations of each row and then remove the duplicates.  For larger values of n eliminating from the initial set would certainly be advantageous for lower numbers of rows in the initial table.  As the initial table size grows the benefit of this early elimination is diminished.

Here is a way to get the permutations of one row.  It is likely not the best way.

Here is a way to get rows that have duplicates in a different order in the initial table.


You have to join the tables on a value.  I'm taking a wild guess here, but I think you might be looking for something like this:

SELECT (SELECT CONCAT(ROUND(SUM(server.Tech=7)/COUNT(server.Tech) * 100,2), '%') AS Server,
CONCAT(ROUND(SUM(network.Tech=7)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server

On network.Tech = server.Tech

) AS V,

I don't know what your equals sign is doing in your arithmetic operation.  Are you trying to pull all Tech where it equals 7?  If so, it would be

SELECT (SELECT CONCAT(ROUND(SUM(server.Tech)/COUNT(server.Tech) * 100,2), '%') AS Server,
CONCAT(ROUND(SUM(network.Tech)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server

On network.Tech = server.Tech
WHERE server.Tech = 7 AND network.Tech=7

) AS V,

Of course, if you are trying to sum all Techs that equal 7 over the full rowcount, it will need more work.


Ola Hallengren script not deleting old backup files but simply taking time

You're providing xp_delete_file with a directory, and a file extension to check for. How many files were there that needed to be deleted when this took 9.5 minutes to run?

It would make sense that the process would be trying to delete each file, and would ultimately fail to delete each file. If there's one file to try to delete, it would complete much faster than if there's 100.

To try to confirm this, I created a directory in which I placed (initially) 1 .bak file dated 2017-03-01, set it to "read-only" (so it shouldn't get deleted), and ran the following to try to delete it.

EXECUTE master.dbo.xp_delete_file 0,N'H:\Dummy_Backup',N'bak',N'2017-04-27T14:09:10'

With 1 file, the command completed successfully (but no files were deleted) in less than 1 second.

I made copies of the file, so I had 64 files, and ran again. This time, it took 1 second.

Continuing in the same vein:

256 files - 5 seconds
512 files - 15 seconds
1024 files - 31 seconds

So, it seems likely that there were a large number of files it was trying (and failing) to delete.

And, to be clear - it failed to delete any files in all of the above tests as well. I went in and turned off the "read-only" attribute for the 1024 files after the last test above, and ran it again - all 1024 files were deleted successfully (in 25 seconds).

So, it does seem likely that the 9.5 minute wait was due to a large number of files it was trying to delete.


How to change the replicate_*_table variable without modifying my.cnf?

Unfortunately the replicate-do|ignore-db|table variables cannot be set dynamically. In fact, they're a bit weird, since they're not even presented in SHOW GLOBAL VARIABLES output.

So you have to specify these params in the my.cnf file.

Just as final note, regardless of these particular params, whenever you set a global variables dynamically (e.g. SET GLOBAL sql_mode := 'TRADITIONAL') and then restart the mysqld server -- your changes are lost. Whenever the server starts up, it's what's written in the config file that takes place. No persistence for online changes.


Create Replica Sets on an existing sharded MongoDB

You don't convert collections to a replica set. You convert (stand alone shard) node (where all databases and collections are) to the replica set.

Convert (stand alone) node to the replica set, follow these instructions.


Error 3201 Operating system error 5(Access is denied.) Creating backup on network drive

The SQL Service account should have write access to the folder to be able to write the backup there.

Depending if you also want to create subdirectories, overwrite backups or delete old backups you need appropriate rights for that too.


How does MariaDB handle ENUM types used in FOREIGN KEY constraints?

As demonstrated in the related question, JOINS on enums are "by-value". Constraints, on the other hand, is validated "by-position". This behaviour strikes me as rather counter-intuitive. Using a similar setup as Evan:

CREATE TABLE foo ( a ENUM ('A', 'B') not null primary key );
CREATE TABLE bar ( a ENUM ('X', 'Y', 'Z') not null primary key );
ALTER TABLE bar ADD CONSTRAINT fk_foo_bar
    FOREIGN KEY (a) REFERENCES foo (a);

insert into foo (a) values ('A'),('B');
insert into bar (a) values ('X'); -- OK
insert into bar (a) values ('Z');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test3"."bar", CONSTRAINT "fk_foo_bar" FOREIGN KEY ("a") REFERENCES "foo" ("a"))

So 'X' is valid according to the foreign key constraint. As Evan demonstrated, JOINs behave differently:

SELECT * FROM foo JOIN bar USING(a);
Empty set (0.00 sec)

So despite that, the row in bar is validated by the foreign key, the join between foo and bar is empty.

It may be tempting at first to use ENUM as a substitute for the lack of CHECK CONSTRAINTS in the MySQL family. However, the way FOREIGN KEYS work together with ENUMs can shoot you in the foot. A typical pattern for subtypes look like:

CREATE TABLE P
( p int not null primary key
, sub_type ENUM ('A','B')
,     constraint AK_P unique (p, sub_type));

CREATE TABLE A
( p int not null primary key
, sub_type ENUM ('A')
,     constraint aaa foreign key (p, sub_type)
                     references P (p, sub_type));

CREATE TABLE B
( p int not null primary key
, sub_type ENUM ('B')
,     constraint bbb foreign key (p, sub_type)
                     references P (p, sub_type));

insert into P (p,sub_type) values (1,'A'),(2,'B');
insert into A (p,sub_type) values (1,'A'); -- OK
insert into B (p,sub_type) values (1,'B'); -- OK, but should not be allowed since 1 is sub_type 'A' in parent
insert into B (p,sub_type) values (2,'B'); -- Fails, but should be OK

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test3"."B", CONSTRAINT "bbb" FOREIGN KEY ("p", "sub_type") REFERENCES "P" ("p", "sub_type"))

So the foreign key validates the position in the enum, not the value itself.

PostgreSQL does not allow foreign keys between different enums, so the situation is not possible to reproduce there:

CREATE type e1 as ENUM ('A', 'B');
CREATE type e2 as ENUM ('X', 'Y', 'Z');

CREATE TABLE foo ( a e1 not null primary key );
CREATE TABLE bar ( a e2 not null primary key );

ALTER TABLE bar ADD CONSTRAINT fk_foo_bar
    FOREIGN KEY (a) REFERENCES foo (a);
ERROR:  foreign key constraint "fk_foo_bar" cannot be implemented
DETAIL:  Key columns "a" and "a" are of incompatible types: e2 and e1.


Execute a stored procedure in a SELECT or FROM statement or a SQL function

I would look at these two additional posts at stackoverflow:  https://stackoverflow.com/questions/14506871/how-to-execute-a-stored-procedure-inside-a-select-query https://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure    As aasim.abdullah has said, I would look into Table valued functions more too.  Information about them can be found here:  https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx    I know the Microsoft link refers to SQL Server 2008 R2, the theory is the same though.


Delete is a DML command and stores the data in redo log till the delete operation is committed.
 This means that if data to be removed by delete is slightly large[even though search time is less] it will take longer time as it will move data to redo log.

So may be the instance when your operation took longer large no. of rows were being deleted to many queries might be writing to redo log.


You have a missing comma after the alias "Total" in your first query.
Extract will take single column as input, while you are giving two columns.

You could do it like:

SQL> SELECT COUNT(deptno)          AS "total",
  2    extract(YEAR FROM hiredate) AS "year"
  3  FROM emp
  4  GROUP BY extract(YEAR FROM hiredate);

     total       year
---------- ----------
         2       1982
         1       1983
         1       1980
        10       1981

SQL>

UPDATE

Using the WHERE clause:

SQL> SELECT COUNT(deptno)          AS "total",
  2    extract(YEAR FROM hiredate) AS "year"
  3  FROM emp
  4  WHERE EXTRACT(YEAR FROM hiredate) = 1981
  5  GROUP BY extract(YEAR FROM hiredate);

     total       year
---------- ----------
        10       1981

SQL>


reusing space after deleting lots of rows

SQL Server will reuse available space in the datafile as necessary. If there is no space left it will attempt to auto-grow the datafile(s) for that File Group based on the auto-growth setting. If auto-grow is disabled and there is no room left, then you will get an error trying to do something that would require more space.

With regards to deleting large amounts of records and potentially not seeing that space get reclaimed as "available", you probably need to rebuild the Clustered Index on that table. I have run into situations where I had a rather large (many hundreds of millions of rows) table that would get a new 30 million or so new rows per day and delete the oldest 30 million or so records. We set up a weekly job to do a full rebuild on the clustered index and that is the only time the space was reclaimed. I am not sure of the exact reason that the rebuild was required (maybe the background process that removes the deleted rows couldn't do that task due to high activity on the table?).


What does "unpackaged" mean in PostgreSQL extensions?

If you had, say, a PostgreSQL 9.0 database using hstore, you would've loaded the hstore support by running the contrib's .sql script.

It was a major pain to dump and reload such databases, and especially to upgrade the contrib to a new version.

When extensions were introduced, there was a need to be able to convert the "unpackaged" contrib, i.e one loaded from an sql script, into an extension that could be recognised by the system, versioned, and upgraded using the extension control system.

That's what from unpackaged is for. It registers an already-installed extension that was set up manually from a script.

For more detail, see the documentation.


Data modelling for specific units of measurement

I would go with one unit (seconds).  Then I would have the input/output convert as needed.

Controlling cron from a table??  Turn it around.  Have the job continually running, but sleeping a computed length of time.  If necessary, use a table for communicating the "when to run next".  Be sure to watch out for edge cases -- such as being told that the next run time is yesterday.  It should probably be interpreted as means "run immediately".


How can it be that count(distinct x, y) < # records distinct x, y

You have 5 records with m.sex is null I think.

Any operator applied to NULL (except IS [NOT] NULL and <=>) returns NULL treated as False.


Cassandra cqlsh browser queries possible only in bytes

I used in my example situation Hector Object Mapping, HOM also for creation of the database from scratch. This was not a good idea, because everything was handled as bytes, like they normally are, and no instructions about how to interpret them was thus given.

Solution to this was to use cqlsh client and give there CQL queries to add tables. So, I made queries like:

CREATE COLUMNFAMILY users (KEY varchar PRIMARY KEY, password varchar);

and with these the remarkable thing is the explicit type (varchar for password) that was given to each column. Now, on the very same tool, data was interpreted as I wanted.

See also:

More info in Getting started with CQL page.

Also see CQL Language Reference for more information e.g. about available datatypes other than varchar mentioned before.


ON DUPLICATE KEY UPDATE in INSERT INTO SELECT with GROUP BY

Like all functions you need to make a further select with the data
And please next time provide a minimal reproducible example, so that the answer can be doe quicker and with much less effort

CREATE TABLe t2 (ID INT primary key,P1 INT,P2 INT ,Items INT )

INSERT INTO t2 VALUES (1,1,1,1)

CREATE TABLe t1 (ID INT primary key,P1 INT,P2 INT ,Items INT )

INSERT INTO t1 VALUES (1,1,2,2)

INSERT IGNORE INTO t2 (ID,P1,P2,Items)
    SELECT ID,P1,P2, t1sum
    FROM
    (SELECT ID,P1,P2,SUM(Items) t1sum
    FROM t1 GROUP BY ID,P1,P2) t3
ON DUPLICATE KEY UPDATE t2.Items=t3.t1sum

✓

SELECT * FROM t2

ID | P1 | P2 | Items
-: | -: | -: | ----:
 1 |  1 |  1 |     2

db<>fiddle here


It sounds like PostgreSQL is set to recover from log shipping rather than by connecting as a replication user.  Please double and triple check your recovery.conf and if that doesn't work, then post it here.

The approach you are taking is a valid approach though, and it means that the recovery will just wait for the next segment until it arrives creating the message you are seeing, but it must be transferred using whatever recovery command you have configured in the master's postgresql.conf.


Converting MySQL 5.6.10 - 32 bit to 64 bit version

The data file format is the same for 32-bit and 64-bit editions of MySQL.