When defining a foreign key in this Oracle example, what is the significance of the CONSTRAINT keyword?

What exactly is the role of fk_courses_department_code then?

It is the name of the constraint.

This is useful to e.g. drop the constraint later, as that can only be done by supplying the name.


Best way to compare multiple non null/non zero fields in a row in T-SQL?

CREATE TABLE #XX
(ID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY, VAL1  INT, VAL2  INT, VAL3  INT, VAL4  INT, VAL5  INT)

INSERT INTO #XX (VAL1,VAL2,VAL3,VAL4,VAL5) VALUES
(5,10,NULL,17,12),
(12,12,12,NULL,0),
(15,15,15,15,15),
(3,NULL,6,0,NULL),
(10,NULL,15,0,20)

--First Unpivot the values so we can join the resulting values back to itself for comparisons.
--This will also eliminate NULL values from the comparison
;WITH cte AS (
SELECT id, ValueNumber, Value
FROM
(SELECT id, val1,val2,val3,val4,val5 from #XX) v
UNPIVOT
(Value FOR ValueNumber IN(val1,val2,val3,val4,val5))
AS unpvt)

--SELECT only distinct ID's
SELECT DISTINCT v1.id FROM cte v1

--Join the table back to itself based on the ID column
--so we can compare values within each row
INNER JOIN cte v2 ON v1.id = v2.id

--Check the values are different
WHERE v1.value <> v2.value

--Ensure we are only checking between different columns
AND v1.ValueNumber <> v2.ValueNumber

--Check neither value is 0
AND v1.Value <> 0
AND v2.Value <> 0

dbfiddle

The above seems to do what you need - if you don't know the particular columns you'll need to UNPIVOT for then this will need to be reformed into a dynamic UNPIVOT script.


Under the hood, when you see a user with USAGE only, that the user is written in the mysql.user table with all global privileges turned off.

You originally stated the the user had this:

grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password';

You should see a row in mysql.user with the MD5 password and all globals privs set to N. You should also see a row in mysql.db with

user='cptnotsoawesome'
host='localhost'
db='statistics'
all DB levels privs set to 'Y'

You should be able to see them with this query

SELECT * FROM mysql.db
WHERE user='cptnotsoawesome'
AND host='localhost'
AND db='statistics'\G

When you ran the REVOKE command, you simply removed the row from mysql.db. This did not touch the row in mysql.user. Thus, you could still login to mysql and only have the privs to run

SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;

If there is a test database called test or a database whose first 5 characters are test_, (look for it using SELECT * FROM mysql.db WHERE db LIKE 'test%';) then the user with just USAGE can have full rights to the test databases. I wrote about this in ServerFault Sep 2011.

If you want to remove the row from mysql.user you could either run

DROP USER cptnotsoawesome@localhost;

or

DELETE FROM mysql.user WHERE
WHERE user='cptnotsoawesome'
AND host='localhost';
FLUSH PRIVILEGES;


List Top Daily Rentals Per Customer using a CTE

# horror film rental(answer)
select cus_dtl.name, cus_dtl.phone, hf_cus.title, hf_cus.rental_date, hf_cus.name
from (select c.customer_id, concat(c.first_name, ',', c.last_name) as name, a.phone
      from customer as c, address as a
      where c.address_id = a.address_id) as cus_dtl, (select r.customer_id, r.rental_date, hf_inve.title, hf_inve.name
                                                      from rental as r, (select i.inventory_id, hf_title.film_id, hf_title.title, hf_title.name
                                                                         from inventory as i, (select f.title, horror_FID.film_id, horror_FID.name
                                                                                from film as f, (select fc.film_id, horror.name
                                                                                from film_category as fc, (select category_id, name
                                                                                from category
                                                                                where name = 'horror') as horror
                                                                                where fc.category_id = horror.category_id) as horror_FID
                                                                                where f.film_id = horror_FID.film_id) as hf_title
                                                                         where i.film_id = hf_title.film_id) as hf_inve
                                                      where r.inventory_id = hf_inve.inventory_id) as hf_cus
where hf_cus.customer_id = cus_dtl.customer_id
order by cus_dtl.name, hf_cus.title, hf_cus.rental_date desc;

using sub queries..


The main problem are the bad estimates of the join cardinalities that lead PostgreSQL to use a nested loop join when a hash join would perform better.

There is one simple thing you can do to reduce the impact of the outermost nested loop join:

CREATE INDEX ON shipments_address (company_view_id) WHERE is_shipper;

That should cut the execution time roughly in half.

Other than that, I can think of the ugly method of temporarily disabling nested loop joins:

BEGIN;

SET LOCAL enable_nestloop = off;

/* your query */

COMMIT;


Benefits of using Staging Database while designing Data Warehouse

There are a few potential advantages of using an intermediary staging database, which may or may not apply to your situation.  There is no perfect, one-size fits all solution.  Some of the potential advantages include:

If it is appropriate, you can take a snapshot of your production database (you may have a daily backup or hot-site snapshot already) and then do your ETL from the restored backup or snapshot.  This could save load on your production database.
You may need complicated processing for your ETL which requires many intermediate tables which have no use except for the ETL process.  You may not want to clutter your data warehouse with these intermediary tables.
Your raw data may not be available all at once and you need somewhere to accumulate it before starting your ETL process to build your data warehouse.
Your data warehouse may have production window requirements which can't be met by your ETL and so you need to stage your "output" (i.e. new records for the data warehouse) rather than or in addition to your production database.
It may be that your ETL process creates large intermediate tables.  Sometimes space management is easier if you start with an empty model database for your ETL staging area and then "throw it away" each day rather than trying to recover the space in a more surgical way, as you might do with a production or reporting database.

There are possible disadvantages too, which may or may not matter to you.  Chief among these is having to have another database server.  A lot of the advantages could be meaningless if you are using the same server to host the production and/or data warehouse databases.


What would cause a table with 80,000 records to use 145GB of space?

Create a clustered index so that your table is not a heap. When a row is deleted from a heap, the space will not be reused. Even empty pages don’t get cleared up. This problem won’t happen if your table is a clustered index.


My primary use case is that I previously had a composite primary key
  and am replacing it with a UUID for easy joins and to not have to
  propagate the primary key's data to other tables.

A hash index wouldn't work for that: it can't enforce uniqueness, therefore it can't be used to support a primary key. As mentioned in the v11 documentation:

  Currently, only B-tree indexes can be declared unique.


use of signal for insert statement in mysql stored procedure

This is done using a proper UNIQUE index on the table itself. MySQL will automatically take care of it.
This is done using proper data types for the fields in the table itself. MySQL will automatically take care of it. (BTW: your item_value is already smallint(6) so it can only be numeric anyway.)
Seeing the obove two points, you probably don't need to check this anymore.

Meaning you probably not need your stored procedure anymore. At least not for this reason.


How to recover a lost password in MySQL 5.7.11? (for Windows)

If you installed MySQL as a service, you need the following:
STEP 01
Open Windows Command Line as Administrator and run
C:\> net stop mysql

STEP 02
Add this to C:\Program Files (x86)\MySQL\MySQL 5.7\my.ini under the [mysqld] group header
[mysqld]
skip-grant-tables

STEP 03
C:\> net start mysql
C:\> mysql

You should be logged into mysql
STEP 04
Suppose you want root@localhost's password to be hello, run this
UPDATE mysql.user SET password=password('hello') where user='root' and host='localhost';
exit

STEP 05
C:\> net stop mysql

STEP 06
Remove the skip-grant-tables option from the my.ini
STEP 07
C:\> net start mysql

STEP 08
C:\> mysql -uroot -p

This should prompt you for the password. Enter new password and you should be in.
ALTERNATIVE
Instead of creating my.ini, do the following after you have shutdown MySQL
C:\> cd C:\Program Files\MySQL\MySQL Server 5.7\bin
C:\> start mysqld.exe --skip-grant-tables
C:\> mysql

You should now be inside mysql without a password. Next run this
UPDATE mysql.user SET password=password('hello') where user='root' and host='localhost';
exit

Back at the command line, shutdown mysql like this:
C:\> mysqladmin shutdown

Now, you start MySQL57 Service back up


How to convert a MySQL database to PostgreSQL?

have you tried:

mysqldump --compatible=postgresql dbname > export.sql

then do this to escape quotes

sed "s/\\\'/\'\'/g" export.sql > export1.sql

may need some more massaging so look at mysqldump doco more closely.


should I give dbo permissions to NT AUTHORITY\NETWORK SERVICE?

I wouldn't directly say it is insecure to give dbo permissions to that account but it is definitely more secure to have the application run under an account only it can use and apply permissions accordingly.


Can I perform a MAX over a pair of columns?

Does this fits your needs?

SELECT DISTINCT ON (person)
       person, date, priority
  FROM table
 ORDER BY person, date DESC, priority DESC;


How do I find out when a node was added to an availability group?

This is as far as I can see not possible with a single T-SQL statement as individual nodes hold only their own create date.

The sys.availability_replicas page makes reference to a column create_date. This is the datetime value of when the node was added.

To find the create_date for each replica you would need to first connect that replica and query the table for that instance only as below.

SELECT [replica_server_name],
    [create_date]
FROM [master].[sys].[availability_replicas]
WHERE replica_server_name = @@SERVERNAME


Is it possible to join derived tables to actual in Access 2007

i have 2 tables in my access DB, customer and contracts and they are related through the customerid,

this is the script i use normally

SELECT Customer.CustomerName
FROM Customer INNER JOIN Contracts ON Customer.CustID = Contracts.CustomerID;

and it works fine and this is how its represented in the design view

i changed my query to

SELECT Customer.CustomerName
FROM Customer INNER JOIN (select * from Contracts) derivedtable ON Customer.CustID = derivedtable.CustomerID;

similar to yours and it gave me same results and this is how its represented in the design view

i hope it clarifies


Create user with only SELECT and INSERT permissions

In my case, I have only a single schema dbo. So, instead of adding permissions to each table, it's easier to add it to the schema.

GRANT SELECT, INSERT ON SCHEMA::dbo to [my_user]

This way, each time a table is added to that schema the permissions will be applied automatically. For all my projects the schema rarely change (compared to the number of times a new table is added) so it's acceptable to run this one if a new schema is added.

Maybe other answers would provide a solution where the permissions can be applied at the database level?


I think that the best method is the first ...

Simply put, it is.

  ... but it will create a lot of rows ...

Yes, it will.
Don't worry.

  ... and this could make DB slower?

Relational databases are really, really good at running around large numbers of rows in many tables and putting things together.  With proper indexing, this structure will handle large numbers of rows with no problem at all and is the most flexible, so will support many different types of query well.


How do you manually change the 'log_reuse_wait' status in SQL Server?

On the Q & A Transaction Log won't shrink, DB thinks it is replicating I found the answer by Matthias Elflein to be the most correct, though first I had to enable a distributor for it to work (I just enabled the default local distributor and deleted it afterwards).

Just in case that answer disappears for some reason, the code to remove a publication from restored database is:

USE DBname
GO
-- 1.) enable publication for MyDatabase
EXEC sp_replicationdboption
  @dbname = 'DBname',
  @optname = N'publish',
  @value = N'true';
GO
-- 2.) remove publication from database. Use the PUBLICATION-name (not database name)
sp_removedbreplication 'Publ_DBname','both'

-- 3.) disable publication for MyDatabase
EXEC sp_replicationdboption
  @dbname = 'DBname',
  @optname = N'publish',
  @value = N'false';
GO

I had to create a distribution database on server B before running the code above otherwise the query errored with the following message:

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 76
The Distributor has not been installed correctly. Could not enable database for publishing.
The replication option 'publish' of database 'DBname' has been set to false.
The replication option 'publish' of database 'DBname' has been set to false.

The above error only applied to SQL Server 2012. I found that when restoring these databases to SQL Server 2016 that there was no log wait status (since replication didn't seem to be installed on the machine).


In order to help you with the dynamic SQL, I've put together a small proof-of-concept:

USE Test; /* this could be TempDB */

CREATE TABLE DatabaseNames /* this is the table that exists in your
                                  "separate" database */
(
    DatabaseID INT NOT NULL CONSTRAINT PK_DatabaseNames_DatabaseID
                PRIMARY KEY CLUSTERED
    , ClientName NVARCHAR(255) NULL
    , TargetTableName NVARCHAR(255) NULL
    , SomeValue NVARCHAR(255) NULL
);

INSERT INTO DatabaseNames (DatabaseID, ClientName, TargetTableName, SomeValue)
SELECT d.database_id, d.name, 'TestTable', 'Whatever'
FROM sys.databases d
WHERE d.database_id>4;

DECLARE @cmd NVARCHAR(max);
DECLARE @sep NVARCHAR(max);

SET @cmd = '';
SET @sep = '';

SELECT @cmd = @cmd + @sep + 'SELECT * FROM TestDB.dbo.DatabaseNames DN
         INNER JOIN ' + QUOTENAME(DB_NAME(DatabaseID)) + '.dbo.' +
         QUOTENAME(TargetTableName) + ' CN ON DN.SomeValue = CN.SomeValue;'
        , @sep = CHAR(13) + CHAR(10)
FROM DatabaseNames;

SELECT @cmd; /* this will display the generated dynamic SQL */

/* uncomment this to execute the dynamic SQL
EXEC sp_executesql @cmd;
*/


In-Memory OLTP SQL Server 2016 - insufficient system memory

So my guess is you may not actually have enough Memory for that table to be used as an In-Memory Table.
As mentioned in my comments, per this Brent Ozar article Hekaton (In-Memory OLTP) Tables In Use, it sounds like one of the requirements are you have at least twice as much Memory available as is the size of your In-Memory Tables:

But you need to understand the limitations, and they’ve changed a lot over the years...

Your memory needs to be 2x the size of your data

While you dedicated 56 GB of Memory to your SQL Server instance, a 23 GB table to be moved to an In-Memory Table means you should have at least 46 GB available for just that table, following Brent Ozar's advice. And because SQL Server uses the Memory you allocate to it for caching other things like data pages loaded in Memory, the 10 GB you have left over from 56 GB - 46 GB is not a lot to rely on. I.e. I wouldn't doubt your instance is already consuming more than 10 GB and therefor you don't have 46 GB available to provide to your In-Memory Table.
This article Estimate Memory Requirements for Memory-Optimized Tables in Microsoft's Books Online also is a good guide on how to estimate how much Memory you'll actually need.
I would recommend testing a much smaller table that's only a few GB or so as a proof of concept. Then work your way up. If you get that working, then you can try increasing your Memory a bit further before retrying your original table.


There are couple of ways to do such forensic analysis :

Using Default trace (provided that the default trace is running). The default trace create 5 *.TRC file that are recycled when they arrive to 20 MB size. :

-- create a test procedure .. so that we can drop it
use test_kin
go
create procedure usp_ABOUT_TO_DELETE
as
select name from sysobjects
where type = 'U'
order by name

-- now drop the procedure
drop procedure usp_ABOUT_TO_DELETE

Below is the sql to get details out of the default trace :

         /*
          Object Altered
          Object Created
          Object Deleted
          */

SELECT TE.NAME
    ,v.subclass_name
    ,DB_NAME(t.DatabaseId) AS DBName
    ,T.NTDomainName
    ,t.NTUserName
    ,t.HostName
    ,t.ApplicationName
    ,t.LoginName
    ,t.Duration
    ,t.StartTime
    ,t.ObjectName
    ,CASE t.ObjectType
        WHEN 8259
            THEN 'Check Constraint'
        WHEN 8260
            THEN 'Default (constraint or standalone)'
        WHEN 8262
            THEN 'Foreign-key Constraint'
        WHEN 8272
            THEN 'Stored Procedure'
        WHEN 8274
            THEN 'Rule'
        WHEN 8275
            THEN 'System Table'
        WHEN 8276
            THEN 'Trigger on Server'
        WHEN 8277
            THEN '(User-defined) Table'
        WHEN 8278
            THEN 'View'
        WHEN 8280
            THEN 'Extended Stored Procedure'
        WHEN 16724
            THEN 'CLR Trigger'
        WHEN 16964
            THEN 'Database'
        WHEN 16975
            THEN 'Object'
        WHEN 17222
            THEN 'FullText Catalog'
        WHEN 17232
            THEN 'CLR Stored Procedure'
        WHEN 17235
            THEN 'Schema'
        WHEN 17475
            THEN 'Credential'
        WHEN 17491
            THEN 'DDL Event'
        WHEN 17741
            THEN 'Management Event'
        WHEN 17747
            THEN 'Security Event'
        WHEN 17749
            THEN 'User Event'
        WHEN 17985
            THEN 'CLR Aggregate Function'
        WHEN 17993
            THEN 'Inline Table-valued SQL Function'
        WHEN 18000
            THEN 'Partition Function'
        WHEN 18002
            THEN 'Replication Filter Procedure'
        WHEN 18004
            THEN 'Table-valued SQL Function'
        WHEN 18259
            THEN 'Server Role'
        WHEN 18263
            THEN 'Microsoft Windows Group'
        WHEN 19265
            THEN 'Asymmetric Key'
        WHEN 19277
            THEN 'Master Key'
        WHEN 19280
            THEN 'Primary Key'
        WHEN 19283
            THEN 'ObfusKey'
        WHEN 19521
            THEN 'Asymmetric Key Login'
        WHEN 19523
            THEN 'Certificate Login'
        WHEN 19538
            THEN 'Role'
        WHEN 19539
            THEN 'SQL Login'
        WHEN 19543
            THEN 'Windows Login'
        WHEN 20034
            THEN 'Remote Service Binding'
        WHEN 20036
            THEN 'Event Notification on Database'
        WHEN 20037
            THEN 'Event Notification'
        WHEN 20038
            THEN 'Scalar SQL Function'
        WHEN 20047
            THEN 'Event Notification on Object'
        WHEN 20051
            THEN 'Synonym'
        WHEN 20549
            THEN 'End Point'
        WHEN 20801
            THEN 'Adhoc Queries which may be cached'
        WHEN 20816
            THEN 'Prepared Queries which may be cached'
        WHEN 20819
            THEN 'Service Broker Service Queue'
        WHEN 20821
            THEN 'Unique Constraint'
        WHEN 21057
            THEN 'Application Role'
        WHEN 21059
            THEN 'Certificate'
        WHEN 21075
            THEN 'Server'
        WHEN 21076
            THEN 'Transact-SQL Trigger'
        WHEN 21313
            THEN 'Assembly'
        WHEN 21318
            THEN 'CLR Scalar Function'
        WHEN 21321
            THEN 'Inline scalar SQL Function'
        WHEN 21328
            THEN 'Partition Scheme'
        WHEN 21333
            THEN 'User'
        WHEN 21571
            THEN 'Service Broker Service Contract'
        WHEN 21572
            THEN 'Trigger on Database'
        WHEN 21574
            THEN 'CLR Table-valued Function'
        WHEN 21577
            THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
        WHEN 21581
            THEN 'Service Broker Message Type'
        WHEN 21586
            THEN 'Service Broker Route'
        WHEN 21587
            THEN 'Statistics'
        WHEN 21825
            THEN 'User'
        WHEN 21827
            THEN 'User'
        WHEN 21831
            THEN 'User'
        WHEN 21843
            THEN 'User'
        WHEN 21847
            THEN 'User'
        WHEN 22099
            THEN 'Service Broker Service'
        WHEN 22601
            THEN 'Index'
        WHEN 22604
            THEN 'Certificate Login'
        WHEN 22611
            THEN 'XMLSchema'
        WHEN 22868
            THEN 'Type'
        ELSE 'Hmmm???'
        END AS ObjectType
FROM [fn_trace_gettable](CONVERT(VARCHAR(150), (
                SELECT TOP 1 value
                FROM [fn_trace_getinfo](NULL)
                WHERE [property] = 2
                )), DEFAULT) T
INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
INNER JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
    AND v.subclass_value = t.EventSubClass
WHERE TE.NAME IN (
        'Object:Created'
        ,'Object:Deleted'
        ,'Object:Altered'
        )
    -- filter statistics created by SQL server
    AND t.ObjectType NOT IN (21587)
    -- filter tempdb objects
    AND DatabaseID <> 2
    -- get only events in the past 24 hours
    AND StartTime > DATEADD(HH, - 24, GETDATE())
ORDER BY t.StartTime DESC;

Digging into Transaction log -- More advance ! Note the use of undocumented fn_dblog.

Find out the transaction ID

SELECT [Current LSN]
,Operation
,[AllocUnitName]
,[Lock Information]
,[Transaction ID]
,[Description]
,[Begin Time]
,[Transaction Name]
,[Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] = N'LOP_DELETE_ROWS'
AND [AllocUnitName] = N'sys.sysschobjs.clst'
--- change here for the object name you think got dropped/deleted
--- can be table, stored procedure, etc
AND CHARINDEX(cast(N'ABOUT_TO_DELETE' AS VARBINARY(4000)), [Log Record]) > 0

Now find out the transaction based on the above transaction ID:

           SELECT [Current LSN]
           ,Operation
           ,[AllocUnitName]
           ,[Lock Information]
           ,[Transaction ID]
           ,[Description]
           ,[Begin Time]
           ,[Transaction Name]
           ,SUSER_SNAME([Transaction SID]) as CULPRIT_USER -- WE Got the Cluprit!!
         FROM fn_dblog(NULL, NULL)
         WHERE [Transaction ID] = N'0000:0000026c';

Excellent References :

The default trace in SQL Server - the power of performance and security auditing
How to read and interpret the SQL Server log by Remus Rusanu


Query for fetching records from SQLite database on Blackberry

This might work as an example:

CREATE TABLE Messages
(
    MessageID INT
    , fromUserID INT
    , toUserID INT
    , TimeSent DATETIME
);

INSERT INTO Messages VALUES (1, 10, 11, GetDate());
INSERT INTO Messages VALUES (2, 11, 10, GetDate());
INSERT INTO Messages VALUES (3, 11, 12, GetDate());
INSERT INTO Messages VALUES (4, 11, 10, GetDate());
INSERT INTO Messages VALUES (5, 11, 12, GetDate());
INSERT INTO Messages VALUES (6, 11, 10, GetDate());
INSERT INTO Messages VALUES (7, 11, 12, GetDate());
INSERT INTO Messages VALUES (8, 11, 12, GetDate());
INSERT INTO Messages VALUES (9, 11, 10, GetDate());
INSERT INTO Messages VALUES (10, 11, 12, GetDate());
INSERT INTO Messages VALUES (11, 11, 10, GetDate());
INSERT INTO Messages VALUES (12, 11, 12, GetDate());

DECLARE @t TABLE (MessageID INT);
DECLARE @From INT;
DECLARE @To INT;
DECLARE cur CURSOR FOR
SELECT fromUserID, toUserID
FROM Messages
GROUP BY fromUserID, toUserID;
OPEN cur;
FETCH NEXT FROM cur INTO @From, @To;
WHILE @@FETCH_STATUS=0
BEGIN
    INSERT INTO @t
    SELECT TOP(2) MessageID
    FROM Messages
    WHERE fromUserID = @From AND toUserID = @To
    ORDER BY TimeSent DESC;
    FETCH NEXT FROM cur INTO @From, @To;
END
CLOSE cur;
DEALLOCATE cur;
SELECT *
FROM Messages
    INNER JOIN @t tt ON Messages.MessageID = tt.MessageID

This returns the 2 most recent messages for each conversation between fromUserID and toUserID.  Change the TOP(2) to TOP(20) to get the 20 most recent conversations.

I'm sure this is not the most optimal way to do this; I believe it can be done in a set-based manner as opposed to the cursor-based, procedural manner shown here.


Assuming the format in the 2 columns is hours:mm:ss[.fff] where hours can be even more than 24, I think this would work:

SELECT smf.*
FROM [SMF_DATA].[dbo].[System_Management_Facility] AS smf
  CROSS APPLY
    ( SELECT CHARINDEX(':', CPUTM)    AS i1,
             CHARINDEX(':', CPUZIPTM) AS i2,
             LEN(CPUTM)    AS len1,
             LEN(CPUZIPTM) AS len2
    ) AS a
  CROSS APPLY
    ( SELECT CONVERT(int, LEFT(CPUTM, i1-1))
             + CONVERT(int, LEFT(CPUZIPTM, i2-1)) AS number_of_hours,
             CONVERT(datetime, '00:' + RIGHT(CPUTM,    len1-i1)) AS d1,  -- implicit
             CONVERT(datetime, '00:' + RIGHT(CPUZIPTM, len2-i2)) AS d2   -- dates
                              -- both d1 and d2 get implicit date of '1900-01-01'
    ) AS b
WHERE
    number_of_hours + DATEDIFF(hour, '19000101', d1 + d2) >= 2 ;

The CROSS APPLY are not really needed, they are there so the logic is split in parts and so you don't have a huge complicated final condition.

If you wanted a different check (say 10 minutes instead of 2 hours), it would need only this change:

WHERE
    number_of_hours = 0
  AND
    DATEDIFF(minute, '19000101', d1 + d2) >= 10 ;


SQL Server 2005 on Windows Server 2012 R2 Install Question

That is an unsupported configuration.  Please see this KB article explaining this.

  SQL Server 2005 (the release version and service packs) and earlier versions of SQL Server are not supported on Windows Server 2012 R2, Windows Server 2012, Windows 8.1, or Windows 8. You will receive a warning in the Action Center if Windows 8.1 or Windows 8 detects an instance of SQL Server 2005.


One way to do this is do a select back to the table and sum the rows before the row, and then use that as an inner query to be able to use the sum in the where clause, something like this:

select *
from (
  select
    P1.ID,
    P1.CUSTOMER_ID,
    P1.DATEPAID,
    (select sum(P2.AMOUNT)
      from PAYMENT P2
      where P1.CUSTOMER_ID = P2.CUSTOMER_ID and
      P2.DATEPAID <= P1.DATEPAID and
      P2.DATEPAID >= dateadd(month, -12, P1.DATEPAID)) as PAID12M
  from
    PAYMENT P1
) X
where PAID12M > 10000

Example in SQL Fiddle.

This will return all the rows where the criteria matches, if you need only customer_id or first row or something like that, you'll have to make the outer query a little bit more complex.


Mariadb with more than one node each node will have recent 1 year data

You may want to look at the MariaDB Spider storage engine which is a sharding engine that allows you to partition the data e.g. by range so that data in a certain range will be stored on a certain backend server.

Let's say you have a table with year column. You can then say that values in a particular year should go to a particular backend server. See some example use cases here.

This will give you node 1 for the first year, node 2 for the second year and so on, and no shifting of data, but that is probably better anyway. You can always rename the servers, if that helps.

If you're storing a new row every second, that means 31,536,000 rows per year, which is probably absolutely fine, though it's always a good idea to make sure the table(s) are optimally designed especially when you're storing a lot of data like this.


contradiction of 3rd normal form on address table

You should note that not every country has the same rules for addresses. I would believe that address_id (as an integer) is just a surrogate key for an entry in the Address table.  It is not a reference to the country.

If you want to look into the variations in addresses you might find the following interesting.

FRANK'S COMPULSIVE GUIDE TO POSTAL ADDRESSES

Regarding City/State/Country you will not always have all of these values available to you.  What about countries that do not have states?

If you have a comprehensive data source for every city, state (including NULL cities and states), and country that is maintained, then you could avoid the extra column.

However, you will need to determine your workaround when some elements are missing. (As you can find examples in Frank's web page.)

My personal opinion (not shared by everyone) is that a state and country column are often better than maintaining a large data set.

If you need is extensive enough you could subscribe to a service to maintain you address information. Just do a web search to find a service that fits your budget.


Credentials for mysql fail for mysqldump in bash script

Apologies, I made a really amateurish error. Apparently, a few lines of code were at some point copied and pasted from a windows machine, so had CR/LF as the end-of-line character.
Changed this to LF and it worked fine.

I would still be interested in why it decided to use root@localhost (and what password it was using), so feel free to comment here with an explanation.


First of all export is not backup. You will not recover a physical copy of a database from exp-dump, only an unconsistent copy of its data (you can make them consistent but the option is rarely used). I'm really not sure you can apply the contents of logMiner on such a copy.

Your best bet would be to apply undo statements to your current copy of the DB to return it to its original state, such as described in the documentation:

  If you fully understand and take into account existing dependencies, it may be possible to perform a table-specific undo operation to return the table to its original state. This is achieved by applying table-specific reconstructed SQL statements that LogMiner provides in the reverse order from which they were originally issued. See Scenario 1: Using LogMiner to Track Changes Made by a Specific User for an example.

Unfortunately it would seem that your customer is running its DB in NOARCHIVELOG mode since the view V$ARCHIVED_LOG is empty. V$LOG_HISTORY only records the switchs of the online redo logs which all DBs have. Only databases that are setup in ARCHIVELOG mode will archive redo logs.

I'm afraid your customer is out of luck.

At this point you should probably consider contacting Oracle support.


Getting the count and sum of distinct record from Postgres jsonb attribute

You can just use:

SELECT
    data->>'sector' AS "Sector",
    count(data->>'sector') AS "Count",
    sum((data->'personnel'->'headcount'->>'total')::integer)  AS "Total",
    sum((data->'personnel'->'headcount'->>'male')::integer)   AS "Male",
    sum((data->'personnel'->'headcount'->>'female')::integer) AS "Female"
FROM
    research
GROUP BY
    data->>'sector'
ORDER BY
    data->>'sector' ;

You use two PostgreSQL JSONB operators:

jsonb -> field  => gets the field out of the json(b), returning a json(b) object
jsonb ->> field => gets the field out of the json(b), returning it as text

And you will just get:

Sector     | Count | Total | Male | Female
:--------- | ----: | ----: | ---: | -----:
business   |     2 |   600 |  510 |     90
government |     2 |   446 |  131 |    315
non-profit |     1 |     5 |    4 |      1

However, for this kind of scenario, where your data is perfectly structured, if would make far more sense to use normalized SQL. This would be the structure of your table (without redundant data):

 CREATE TABLE normalized_research
 (
     id integer PRIMARY KEY,
     name text,
     sector text,
     male_headcount integer,
     female_headcount integer
 ) ;

This is how you would fill it in:

 INSERT INTO
     normalized_research
     (id, name, sector, male_headcount, female_headcount)
 SELECT
     id,
     data->>'name',
     data->>'sector',
     (data->'personnel'->'headcount'->>'male')::integer,
     (data->'personnel'->'headcount'->>'female')::integer
 FROM
     research ;

And this is the (much nicer, faster, safer) query you would make:

SELECT
     sector AS "Sector",
     count(sector) AS "Count",
     sum(male_headcount)+sum(female_headcount) AS "Total",
     sum(male_headcount) AS "Male",
     sum(female_headcount) AS "Female"
 FROM
     normalized_research
 GROUP BY
     sector
 ORDER BY
     sector ;

... that would be giving you exactly the same result.

You can find all the logic at this DBFiddle

Side notes

JSON was mostly invented to transfer and exchange data (through Ajax) between web services and web consumers (normally written in JavaScript).

Quoting JSON.org:

  JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999.

(emphasis mine)

It is perfectly well suited for its original purpose. But it has not been designed as a way to store data in a database. That was, somehow, an afterthought, because you can very easIly do it. You can store JSON as text, and you can already use any database, even if it is not aware of JSON.

In some occasions (normally linked to very variable data structures, and data structureS not known in advance) it is a good choice. But when your data is perfectly well structured, your schema (=structure) is well known in advance and doesn't need lots of flexibility: plain old normalized SQL is a better choice: you have data-type safety, consistency, easier indexing, referential integrity, faster access, ...


When setting up security for linked servers, what does the Impersonate checkbox do?

Impersonate
Pass the username and password from the local login to the
linked server. For SQL Server Authentication, a login with the exact
same name and password must exist on the remote server. For Windows
logins, the login must be a valid login on the linked server.
To use impersonation, the configuration must meet the requirement for
delegation.

Create Linked Servers (SQL Server Database Engine)
In other words, this option corresponds to code
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'remote_server', @locallogin = N'domain\account', @useself = N'True'


The table hint in the query takes precedence, as I would have expected.

Here's my test script:

CREATE TABLE t1 (a int);

INSERT INTO t1(a) SELECT TOP 1000 message_id FROM sys.messages;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

    SELECT * FROM t1 WITH(NOLOCK); /* Try it with and without the hint */

    EXEC sp_lock;

ROLLBACK;

With the NOLOCK hint included, no locks are taken by the SELECT statement.

In other words, setting the isolation level on the transaction makes that isolation level the default within the transaction, but that default can still be overridden by table hints, as I demonstrated.


What is the performance impact of queries against DM objects using DMVs in SQL Server 2008?

There are other DMVs that can help you quantify this.  sys.dm_exec_sessions has columns cpu_time, memory_usage and total_scheduled_time amongst others.  While you're running your plan-getting query in one session you can interrogate this DMV in a second session to find how expensive the first is.

Pleasingly, you can also quantify how expensive your second session is at the same time!


How to catch error from sql server on powershell?

Powershell supports try/catch/finally. This allows you detect and handle errors within the scope of try block.

If you simply want to stop the PS script if any errors occur, including non-SQL errors, wrap the main code in a try block:

try {
    runScriptsFromFolder -pathToFolder "C:\SqlScripts"
}
catch {
    throw
}


You need dynamic SQL for this, since you need to change the database context (and USE does not accept variables). Try this:

declare @dbname sysname,
  @exec nvarchar(4000),
  @sql nvarchar(max);

    SET @sql = N'SELECT db_name() as Database_name,
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
   AS indexstats
INNER JOIN sys.tables dbtables
  on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
  on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
  ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc;';

declare dbcursor cursor LOCAL FAST_FORWARD for
select [name] from sys.databases
-- WHERE state = 0 /* online */
-- and database_id > 4 /* primary system dbs */

open dbcursor
fetch next from dbcursor into @dbname

while @@fetch_status = 0
begin
  set @exec = QUOTENAME(@dbname) + N'.sys.sp_executesql';
  exec @exec @sql;
  fetch next from dbcursor into @dbname
end

close dbcursor; deallocate dbcursor

You can also do this without a cursor:

declare   @cmd nvarchar(max),
      @sql nvarchar(max) = N'';

SET @cmd = N'SELECT db_name() as  Database_name,
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
  AS indexstats
INNER JOIN sys.tables dbtables
  on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
  on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
  ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc;';

SELECT @sql += N'EXEC ' + QUOTENAME(name)
  + N'.sys.sp_executesql @cmd;'
  FROM sys.databases
  -- WHERE state = 0 /* online */
  -- and database_id > 4 /* primary system dbs */

EXEC sys.sp_executesql @sql, N'@cmd nvarchar(max)', @cmd;

You could also look at my sp_msForEachDB replacement.


PostgreSQL pg_dump custom file input not dumping data

I usually do this and I do not have that problem.

Backup

pg_dump -p 5432 -U postgres -Fc -d postgres -f /tmp/postgres.custom

Restoration

pg_restore -d postgres -p 5432 -U postgres -C /tmp/postgres.custom


Establishing a 1 to 1 relationship between tables

I guess that you mean: "every Nurse is an Employee".

This can be done with (keeping the Employee table as it is):

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,        -- why not name this EmployeeId ?
    [Name] [nvarchar](100) NOT NULL,
    [StaffNumber] [nvarchar](50) NOT NULL,
    [Salary] [money] NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [GenderId] [int] NOT NULL,
    [ContactNumber] [nvarchar](20) NOT NULL,
  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
    ( [Id] )
 ) ;

and changing the Nurse table so the primary key is not an IDENTITY and it has a foreign key that references the Employee (Id):

CREATE TABLE [dbo].[Nurse](
    [EmployeeId] [int] NOT NULL,                 -- not IDENTITY !
    [WardId] [int] NOT NULL,
  CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED
    ( EmployeeId ),
  CONSTRAINT [FK_Employee_Nurse] FOREIGN KEY
    ( EmployeeId )
    REFERENCES dbo.Employee ( Id )
) ;


Upgraded from SQL Server Express to standard but CPU usage is maximum 50% on dual core

If you want to see pain points of a query capture the actual execution plan via SSMS when you run it. Viewing this within SQLSentry Plan Explorer (free!!) is a bit more easy on the eyes than what SSMS will show. There are some good blog post and articles scattered around the Internet on how to read an execution plan if you need it.

Capture Actual Execution Plan:


Scripting Microsoft Data Warehouse Wizard

This is not my area of expertise, but you might look at Dennes Torres Simple Talk post from January 2015 for any takeaways that help you.

https://www.simple-talk.com/sql/database-administration/centralize-your-database-monitoring-process/

Two subheadings of particular interest may be:

Configure Data Collector through CMS
Configure Collection Sets through CMS


System Memory (RAM) for Windows Server 2012 Standard with SQL Server 2012

All of this assumes you're running 64 bit Windows and 64 bit SQL Server...

SQL Server is greedy when it comes to memory usage, and that is by design. It keeps as much data as it can in RAM because it is faster than having to access disk to fulfill a query or other request.

As a result, you may want to set the minimum memory allocation for a SQL Server instance so it doesn't suffer from what I call memory starvation. If this happens, the server's drives will thrash because database usage will require disk accesses.

You'll also want to set the maximum memory allocation as already mentioned. In general, I give 10% to the operating system and the rest is for SQL Server (I have dedicated SQL servers each with 32GB RAM so it's fairly straightforward).

This article from Brent Ozar's website has more details: https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

I noticed you said sometimes the performance suffers. Do you have other applications collocated on this server? Are there SQL Agent tasks scheduled to run at certain times? Backups can take a lot of resources and other applications sharing resources could end up fighting over them with SQL Server. In that case, you may need to add more memory and carefully tune how it's used, or move the application to another server.


Are you just looking for a complete copy of the data to completely replace what already lives on the destination instance?  In that case, a full backup of the source database and a restore into the destination instance would be the cleanest and easiest route.

If you are looking at select data copying (i.e. you don't want all data, or you don't want to replace the destination data) then you'll need to have a logic layer between the two data sources.  Some ideas behind this would be SSIS, PowerShell, or any other tool to do some brain work between both SQL Server databases.  The tool to use would depend on the specifics of your need as well as your comfort level with all the options.


Serializability schedules: can someone help me understand it?

I just invented a lame example that might help.

Have you ever driven by a gas station while they are changing the prices?  (And the prices are big numerals that need to be taken down and new numbers put up.)

There can't be two guys changing the same price at the same time -- exclusive lock.

Everyone riding by can read the numbers -- shared lock (sort of).  But, if you want to get the price you saw, and don't want it to change before you pump your gas, it gets more complex.  You want the shared lock to prevent changing the price until you are finished with your "transaction" (filling your tank at the price you saw).

In my lame example, you can't really take out a shared lock, except with a "dirty read".  "Dirty read" is a flavor of "transaction isolation".
 The price can change while after you see the price and before you pump your gas.  With a stronger isolation, you will get the price you saw.


Correct way to write this view? Total sales/purchases/profits by day

I ended up doing it this way:

SELECT temp1.hday,
temp1.user_id,
temp1.total_buy,
temp1.total_sell,
coalesce(temp2.total_profit,0) as total_profit,
coalesce(temp3.margin,0) as margin
from

(
select
calendar.days as hday,
users.iduser as user_id,
coalesce(sum(CASE WHEN transaction.transaction_type = 'Buy' THEN transaction.price_total END),0) as total_buy,
coalesce(sum(CASE WHEN transaction.transaction_type = 'Sell' THEN transaction.price_total END),0) as total_sell
-- (total_profit/total_buy) * 100 AS margin
from
calendar
JOIN transaction ON date(transaction.time) = calendar.days
JOIN users on transaction.user_iduser = users.iduser
GROUP BY calendar.days, users.iduser
order by calendar.days ASC
) temp1

left JOIN

(
select calendar.days as hday,
users.iduser as user_id,
coalesce(sum(profit.profit_unit*profit.quantity_profit),0) as total_profit
from calendar
JOIN profit on date(profit.timestamp_sell) = calendar.days
JOIN users on  profit.users_idusers_OUT = users.iduser
GROUP BY calendar.days, users.iduser
order by calendar.days ASC
) temp2

ON temp1.hday = temp2.hday

left join

(
select (sum(profit.profit_unit*profit.quantity_profit)/sum(t1.price_unit*profit.quantity_profit))*100 as margin, calendar.days as hday
                from profit
                join transaction t1 on profit.transaction_idbuy_buy = t1.idbuy
                join transaction t2 on profit.transaction_idbuy_sell = t2.idbuy
                join users on t2.user_iduser = users.iduser
                join calendar on calendar.days = date(profit.timestamp_sell)

                GROUP BY calendar.days, users.iduser

) temp3

on temp2.hday = temp3.hday

I had to create 2 temporary tables. The first gathers information for purchases and sales with conditional aggregates while the second one contains the profit information. profit margin comes from merging again with a 3rd table.

There's probably easier ways of doing this, though


The Log Reader Agent will only read as far as the last transaction committed to all other Availability Group replicas. This means that if one AG replica is offline, Replication will stop reading transactions until that AG replica is back online. This is done to be ultra-safe and ensure that only fully committed transactions are replicated.

In some cases, this may be too conservative of a rule for your setup. In that case, you can add Trace Flag 1448 to your startup parameters, which will disable this behavior for async replicas. If a synchronous replica is offline, it will still prevent the log reader agent from reading unsent transactions for Replication.

Why does Microsoft impose this restriction on synchronous replicas? If the LSN has not been hardened on the synchronous replica, then the transaction isn't fully committed. Replication is very conservative in what it considers a fully-committed transaction that is safe for replication. Say for example, that the AG isn't in sync due to a network issue, or some other problem (ie, it is online, but the Primary node just doesn't know that). If you force a failover of the AG, and allow data loss, then your replication secondary would have data that was lost during failover-- potentially breaking replication.

This TF will affect the server that is currently hosting the primary database replica, and thus must be set on every server that might host the primary replica for the AG.

From the Microsoft documentation on TF 1448:

  Enables the replication log reader to move forward even if the async
  secondaries have not acknowledged the reception of a change. Even with
  this trace flag enabled the log reader always waits for the sync
  secondaries. The log reader will not go beyond the min ack of the sync
  secondaries. This trace flag applies to the instance of SQL Server,
  not just an availability group, an availability database, or a log
  reader instance. Takes effect immediately without a restart. This
  trace flag can be activated ahead of time or when an async secondary
  fails.


Try_Convert Datetime showing '1/1/1900', want to show space

You can use NULLIF() to force a NULL when the value is an empty space.

SELECT TRY_CONVERT(datetime, NULLIF('', ''));

Assuming this is actually coming from a column or parameter/variable:

SELECT TRY_CONVERT(datetime, NULLIF(@param, ''));

SELECT TRY_CONVERT(datetime, NULLIF(column, '')) FROM ...;


Failed To Insert Data Into Tables Using Postgres-XL and 4 Datanode

Finally, I found it as my mistake because I am still affected by ordinary PostgreSQL (9.4) behaviour. After I added ::INTEGER and LIMIT 1000000 in the DML below, the data insertion is a success.

...
temp2 AS (
  SELECT
    *, round(random()*1000)::INTEGER AS receipt_amount,
  random()*1000 AS non_promo, random()*1000 AS promo_rep, random()*1000 AS promo_avp
  FROM temp
),
...

  ORDER BY 1,2,4
)
INSERT INTO sales_small_cat
SELECT * FROM finale LIMIT 1000000;

Frankly, I am still confused. Why does Postgres-XL need the LIMIT clause?


I don't think Rolando thought about what this variable does before answering. Don't set the thread_stack to 32M. It's is allocated per connection and 100 connections is going use 3.2GB.

thread_stack = 192K

Setting it to the fairly standard 192k should be fine.


How to query a database for empty tables

Leverage sys.tables and sys.partitions:

select
    t.name table_name,
    s.name schema_name,
    sum(p.rows) total_rows
from
    sys.tables t
    join sys.schemas s on (t.schema_id = s.schema_id)
    join sys.partitions p on (t.object_id = p.object_id)
where p.index_id in (0,1)
group by t.name,s.name
having sum(p.rows) = 0;

Use a sum of rows just to make sure you don't have confusion with partitioned tables.  Index_ID of 0 or 1 means you're only looking at the row counts for your heaps or clustered indexes.


According to PSS that wait type is the time it takes for the primary to receive a notification that the log block has been written on the destination (though not replayed), and after this the wait type changes to WRITELOG and the log block gets written locally.

Logically then any disk waits to write the block remotely must be included in that HADR_SYNC_COMMIT time.

Perhaps if you run PAL on your secondary you will see that it has lower disk latency and so can complete the writes faster. It would be interesting for you to look and see.

Incidentally the MSSQL Tiger Team have a video and demo XE script and Power BI sample that gathers AG sync information and displays it in graphs; if you're interested in exploring that you could give it a try.


Let check the the table size after you migration to new db(in new db server).
a reason is

First server (10 minutes)

your table might not up to day of statistic use ANALYZE TABLE for Innodb.
you mention you table is 66GB but it might not the real table size it may include the dead-tuple(caused by your update,delete operation).

when you migration it to other place this table or whole database will be ANALYZE to the real time statistic.


Here is an example of an INSTEAD OF TRIGGER.

--demo setup
USE [Test]
GO

drop table if exists Tracked;
drop table if exists TrackOmit;
Create table Tracked (id int, email_address varchar(100), ip_address varchar(50), trackedPage varchar(100))
create table TrackOmit (id int, trackOmit_email varchar(100))

insert into Tracked(id, email_address, ip_address, trackedPage) values
(1,'test@gmail.com','10.100.100.100', ' ')

insert into TrackOmit(id,trackOmit_email) values(1,'hotmail.com'),(2,'gmail.co.uk')
-----------------
--create instead of trigger
DROP TRIGGER IF EXISTS [dbo].[TrackedInsteadOfUpdate];
GO
CREATE TRIGGER [dbo].[TrackedInsteadOfUpdate] ON [dbo].[Tracked]
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON

    --only update rows where the updated email_address
    --is not on the TrackOmit table
    UPDATE t
    SET t.email_address = i.email_address
    FROM dbo.tracked t
    JOIN inserted i
        ON i.id = t.id
            AND NOT EXISTS (
                SELECT *
                FROM TrackOmit o
                WHERE i.Email_Address LIKE '%' + o.trackOmit_email + '%'
                )
END
GO

ALTER TABLE [dbo].[Tracked] ENABLE TRIGGER [TrackedInsteadOfUpdate]
GO

--original values in Tracked
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test@gmail.com | 10.100.100.100 |             |

--Attempt update for hotmail.com - should not update
update Tracked set email_address = 'test@hotmail.com'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test@gmail.com | 10.100.100.100 |             |

--Attempt update for gmail.com - should update
update Tracked set email_address = 'test1@gmail.com'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test1@gmail.com | 10.100.100.100 |             |

--Attempt update for gmail.co.uk - should not update
update Tracked set email_address = 'test2@gmail.co.uk'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test1@gmail.com | 10.100.100.100 |             |


Try this:

SELECT column1, column2, column10
FROM USER
INTO OUTFILE "c:/user.csv"
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY "\n";

For more option read the documentation page.


How to use max function in such condition?

Try it this way:

select userid from (
(select userid from guestuser order by userid DESC LIMIT 1)
union all
(select userid from olduser ORDER BY userid DESC LIMIT 1)
) sq
order by userid DESC LIMIT 1;


How to find the session id of running jobs

I used CONVERT to convert the msdb.dbo.sysjob.job_id to BINARY(16) and then used CONVERT (and a style) to convert that to a varchar(max) which could be compared to what comes out of your cte.  You can read more about CONVERT and styles by reviewing the documentation for CONVERT. This worked in my testing.

Try this:

;
WITH JobDetails
AS (
    SELECT DISTINCT Job_Id = left(intr1, charindex(':', intr1) - 1)
        ,Step = substring(intr1, charindex(':', intr1) + 1, charindex(')', intr1) - charindex(':', intr1) - 1)
        ,SessionId = spid
    FROM master.dbo.sysprocesses x
    CROSS APPLY (
        SELECT replace(x.program_name, 'SQLAgent - TSQL JobStep (Job ', '')
        ) cs(intr1)
    WHERE spid > 50
        AND x.program_name LIKE 'SQLAgent - TSQL JobStep (Job %'
    )
SELECT *
FROM msdb.dbo.sysjobs j
INNER JOIN JobDetails jd ON jd.Job_Id = convert(VARCHAR(max), convert(BINARY (16), j.job_id), 1)


Custom sort before ROW_NUMBER() for paging

instead of ORDER BY c.CoinId i just did ORDER BY c.Rank and that worked

SELECT c.*, ROW_NUMBER() OVER (ORDER BY c.Rank) AS RowNumber FROM coins c


MongoDB replica set: members with different versions of mongod

For minor versions its not an issue, although i would suggest to keep the same minor version across all nodes to avoid different behavior (due to different bugs and optimization patches between versions) after a stepdown. For major versions is fine when the primary is one major version behind the secondaries. Its not a good idea to have a primary on different major version from a secondary.


sql delete records based on combination of multiple fields in other table

DELETE t1
FROM Table t
    JOIN Table t1 ON t1.c1 = t.c1
        AND t1.c2 = t.c2
WHERE t.c3 IS null


PostgreSQL Replication for small, low-usage database

For reference, one of these may be suitable:

Bucardo
Londiste
RubyRep
Slony

A general discussion of replication and clustering in PostgreSQL is here.

There are also many questions on StackOverflow and this useful one on ServerFault.


How to update price where value of rows exist in value of column in sql?


Auto-commit UPDATE Transaction On Linked Server

Given that a Linked Server is an external / separate connection, I would expect that it can be handled / managed separately, just like making an external / regular connection to any SQL Server (even back to the calling instance -- i.e. loop back) via SqlClient in SQLCLR. Being a separate connection, it is optional whether or not to attempt joining the current transaction (this is handled via the Enlist= {true | false} connection string keyword). I am guessing that the Linked Server property of is_remote_proc_transaction_promotion_enabled / "Enable Promotion of Distributed Transactions for RPC" is equivalent to the Enlist connection string keyword.

That being said, there is something being overlooked (including being overlooked in my original answer): the name of that option is "Enable Promotion of Distributed Transactions for RPC" (emphasis mine). The highlighted part is rather important here. This option affects stored procedure calls over the linked server, not regular ad hoc query batches. So, that option should not be relevant to the behavior you are describing in the question.

My (educated) guess is that for ad hoc queries, the option to "Enlist" in the current transaction is defaulted to "false" for auto-commit transactions. But, for explicit transactions, you are telling SQL Server that all statements within the BEGIN TRAN ... COMMIT TRAN should be considered an atomic operation, so the remote connection then tries to enlist in the current Transaction.

FYI: this behavior should have nothing to do with executing the statements in SSMS. That is just a client that submits the statements to SQL Server for execution. It should be the same behavior even if executing via SQLCMD.EXE or SqlCient in a .NET app, etc.

UPDATE

With regards to testing this, I suggested to the O.P. using SQL Server Profiler or Extended Events to capture the transaction enlistment / promotion to distributed transaction. The O.P. replied with:

  I followed your suggestion and used Profiler, and there is indeed a row that has its EventClass value with "DTCTransaction" and EventSubClass value as "Enlisting in a DTC transaction" when I use an explicit transaction. No such rows exist when I rely on the auto-commit setting.


You can also take a look at this whitepaper written by the SQL CAT team. Take note of who all reviewed that document too, it is very well written.

The whitepaper will explain that since we are talking about data compression there is some data that will compress better than other. I believe the section on Application Workload has information for some of your questions, is goes over the performance implications found when using data compression. My advice is going to be to test it. That is really the only way you will find out for sure if it will benefit or hurt your application/system.

A snippet from the whitepaper points out some data that does not benefit from data compression:

Columns with numeric or fixed-length character data types where most
values require all the bytes allocated for the specific data type
Not much repeating data
Repeating data with non-repeating prefixes
Data stored out of the row
FILESTREAM data


Use MySQL Workbench. You can right-click a table and select "Send to SQL Editor" --> "Create Statement". This way no table "properties" will be forgotten to add (including CHARSET or COLLATE).
With this huge amount of data I'd recommend cleaning up either the table or the data structure you use (a good DBA comes handy). If not possible:

rename the table (ALTER) and create a new one with the CREATE script you get from Workbench. You can also extend that query with the new field you need
BULK LOAD the data from the old table to the new one:
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
   SELECT fieldA, fieldB, fieldC, ..., fieldN
   FROM old_table
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

This way you avoid indexing/etc to run record by record. The "update" to the table still will be slow (as the amount of data is huge) but this is the fastest way I can think of.
EDIT: Read this article to get details about the commands used in the above sample query ;)


What does select * from table where column * 'value'; do?

The SQL

WHERE column * value

is a numeric operation of multiplication, and when the value of the expression (column * value) isn't 0, that is equivalent to TRUE so will return the row.


MySQL Slave has unmanagbly higher IO load than master

Munin's formula for "device utilization" is (milliseconds spent doing I/O)/second, which assumes you can't do any I/O in parallel, so I'm not sure that this is a meaningful metric. However, you do clearly have a genuine performance issue here since replication can't stay caught up.

Is there a significant difference in the I/O subsystems between the servers?

The way I would approach this is to compare the I/O subsystems and then if there isn't a glaring difference in architecture, run some I/O benchmarking at a quiet time with a tool like bonnie++ or fio to narrow down the difference in performance.

Note that the slave likely has an equal write workload to the master because it has replay all the writes, plus it may have a considerable read workload (for reading any parts of the database that have to be updated) and might be less efficient for reads than the master because it has less RAM for caching. In this situation I would not necessarily expect the slave to be able to catch up because it's entirely possible for the master to generate work at a rate faster than the slave can consume it.


How to add spatial indexes through rockmongo

From what I can see, RockMongo does not support the creation of geo indexes directly.  Because creating an index is really just an insert into the system.indexes collection for the database (ensureIndex is just a shell helper), it is possible. So, you can still do it via RockMongo, but it is arguably more complicated than using the CLI.

Here is how I successfully added a Geo index via RockMongo:

Go to the system.indexes collection of the parent database (foo in my case)
Insert the following document:

{
   "v": 1,
   "key": {
     "loc": "2d"
  },
   "ns": "foo.geotest",
   "name": "loc_"
}

Substitute the name of the field ("loc" above), the index name ("loc_" above) and the namespace ("foo.geotest" above) as appropriate.

In order to add a field to specify the index type, you are going to have to make a feature request in the project I would think - there is nothing obviously similar currently open:

http://code.google.com/p/rock-php/issues/list


The log in this backup set is too recent, but it's from before the last full backup!

You are trying to apply a Log file that finish with LSN 421814812000000025600001 into a DB restored with LSN 421787067000000013800001 , so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file would not possible to apply , being a change in sequence.

So possible cause here being that you are not restoring transaction log backups in order :

Also, you can use the below script to determine for restoring the available backups in order:as mentioned here

    USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'Customer'
SET @backupPath = 'D:\SQLBackups\'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
   AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
   PRINT @cmd
   SET @lastFullBackup = @lastDiffBackup
END

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.TRN'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
       + @backupPath + @backupFile + ''' WITH NORECOVERY'
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd


What is the safest way to install the sys schema on a production server?

Option 4: Download it from github and install it manually.

Installation would work like this:

# mysql -uroot -ppassword < sys_56.sql

(I'm aware you're using MySQL 5.5. Please read on...)

When you examine what this does, you see that a separate schema is created and in this schema some procedures, functions, views and tables are created. Nothing too fancy. I can't imagine, that this would have any dramatic influence on the server.
Regarding compatibility with 5.5, I must admit that I'm not familiar with 5.5, but given that it's just some views and so on in a separate schema, I'd say that it either works or it doesn't. I personally wouldn't mind at all. Try it on a VM or docker container or whatever, if you like.


What are the best practices for ETL of data from one OLTP system to another, Part 2?

Relational database guys always like to think in "sets" but some tasks just don't work that way.  With ETL, since there is no way around the physical disk having to actually write data you just want to reduce the overhead related to each individual query as much as possible.

Speaking for MSSQL.  If you use "parameterized" queries.  Looping in code and updating in that fashion should be fine.  The reason for this is that by using a parameterized query, the SQL engine can cache a query plan to use for each iteration of your loop and avoid the overhead of starting from scratch each time.

Google parameterized query if you are unfamiliar with that term.  Speaking in Java terms you can also search for Prepared Statements.

I say "should be fine" in my previous post.... assuming that the query you are going to run repeatedly is as well written and efficient as possible.

If you are able to analyze a sample query that is going to be run repeatedly and find out what it's resulting query plan is.  You may find there are indexes that aren't necessary that can be turned off in order to improve the performance of the update.

Or, on the flip side, if your update includes logic that causes a table scan on a related table you may find the addition of an index would greatly improve the performance of your update.


That's an odd question, but you could go with something like this:

USE YourDatabaseName
GO

CREATE TABLE #TempColumns (
    id int,
    tname varchar(50),
    cname varchar(50))

INSERT INTO #TempColumns (
    id,
    tname,
    cname)
SELECT
    ROW_NUMBER() OVER (ORDER BY t.name) AS ColId,
    t.name,
    c.name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

DECLARE @ColumnCount INT =
(
    SELECT COUNT(*) FROM #TempColumns
),
@i INT = 1,
@TableName VARCHAR(MAX),
@ColumnName VARCHAR(MAX),
@DataType VARCHAR(20),
@AllowNull INT;

WHILE (@i <= @ColumnCount)
BEGIN
    SET @TableName = (
        SELECT t.tname
        FROM #TempColumns t
        WHERE t.id = @i);
    SET @ColumnName = (
        SELECT t.cname
        FROM #TempColumns t
        WHERE t.id = @i);
    SET @DataType = (
        SELECT t.name
        FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
        WHERE OBJECT_NAME(c.OBJECT_ID) = @TableName AND c.name = @ColumnName);
    SET @AllowNull = (
        SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName, 'AllowsNull'));

    IF (@DataType = 'varchar')
    BEGIN
        IF (@AllowNull = 1)
        BEGIN
            EXEC('UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = NULL WHERE ' + @ColumnName + '= ''''')
        END
    END
    SET @i = @i + 1;
END

This will create a temporary table to store all the table names and their respective columns, it will then perform an update (in this case) per every column on every table only when datatype is VARCHAR.


Redshift throws Value too long for character type character varying(100) even when values are within the range

Redshift can store multi byte strings into varchar field. But if you define your field as varchar(100) it does not mean 100 characters. Instead it means 100 bytes. So if all the characters in the string are two byte characters, then the field can store at max 50 characters.

From the documentation,

  Use a VARCHAR or CHARACTER VARYING column to store variable-length strings with a fixed limit. These strings are not padded with blanks, so a VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters.

The problem is LENGTH function only returns the number of characters and not number of bytes excluding the trailing blanks. So getting the length on a multi byte character only returns 1. This is documented here.

The alternative OCTET_LENGTH can return the number of bytes instead of number of characters.

Running OCTET_LENGTH revealed trouble maker and it is fixed now.


Using IF clause or while clause in OUTER APPLY

I don't quite understand your table structure or your query, but a much more sane way to generate a small date range (< 100 days) between a start and end date - given that you don't have a calendar table (even though you should, and shouldn't easily be able to argue against one) - is as follows:

DECLARE @StartDate date = '20190401',
        @EndDate   date = '20190430';

;WITH days(d) AS
(
  SELECT @StartDate
  UNION ALL
  SELECT DATEADD(DAY,1,d) FROM days WHERE d < @EndDate
)
SELECT d FROM days;

If your range can be more than 100 days, you can use OPTION (MAXRECURSION n).

Now you can use that CTE to anchor / join / apply etc. to the rest of your query. I can help do that but you'll need to show table structure, sample data, and desired results. Basically, you need to provide an MCVE if you want quality help - this takes out guesswork, minimizes back-and-forth, and helps identify edge cases you may not have considered yet.


Help with recursing self-referencing table

Start with the recursive CTE to get the tree. Keep track of the parent too (faking the bottom level), to identify when it's null.

WITH Tree AS (
    SELECT Id AS VariableId, DataSourceId, DataSourceId AS Parent
    FROM Variable
    UNION ALL
    SELECT t.VariableId, t.DataSourceId, d.ParentId
    FROM Tree t
    JOIN DataSource d
    ON d.Id = t.Parent
)
--Now get the top levels
SELECT VariableId, DataSourceId
FROM Tree
WHERE Parent IS NULL;


how to get count(*) from table which either has column or not?

This will get all posts and their comment counts

SELECT p.*,IFNULL(c.comcount,0) comment_count
FROM post p LEFT JOIN
(SELECT p_id,COUNT(1) comcount FROM comment GROUP BY p_id) c
USING (p_id);

This will get post for p_id=8  and its comment count

SELECT p.*,IFNULL(c.comcount,0) comment_count
FROM (SELECT * FROM post WHERE p_id=8) p LEFT JOIN
(SELECT p_id,COUNT(1) comcount FROM comment WHERE p_id=8 GROUP BY p_id) c
USING (p_id);


Insufficient Privileges When Connect to SYS and SYSTEM

As andba77 suggested, it sounds like a problem with remote password file.  First you need to determine if you have a password file.  It should be on the database server at $ORACLE_HOME/dbs/orapw.  Second is the setting of the init parm 'remote_login_passwordfile'.  If it is set to 'none', no remote logins as sysdba will be allowed.  You can read more about that parameter in the docs at http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams212.htm#REFRN10184

It would help if we had more detail on the remote connection.  Best and most reliable would be to reproduce the problem with sqlplus at a command prompt, then copy and paste the entire command and the entire response.  Only then can we know exactly what you are doing, and in this particular question "exactly" is important.


Making sense of sys.objects, sys.system_objects, and sys.sysobjects?

As noted in my previous post sys.sysobjects is deprecated:

  Note taken from sys.sysobjects (Transact-SQL)

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You would now have to combine sys.system_objects and sys.objects to retrieve all items that are stored in the deprecated sys.sysobjects system table.

SELECT * FROM sys.system_objects
UNION ALL
SELECT * FROM sys.objects AS o

Result:

(2171 row(s) affected)

sys.system_objects

  Contains one row for all schema-scoped system objects that are included with Microsoft SQL Server. All system objects are contained in the schemas named sys or INFORMATION_SCHEMA.

sys.objects

  Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function

For example sp_MScleanupmergepublisher is ìs_ms_shipped but not in the sysschema (it is in dbo) so it's in sys.objects and not sys.system_objects. This is possibly because  it is a shell for sys.sp_MScleanupmergepublisher_internal and is probably defined on instance creation. If you have SSMS right-click on the dbo.sp_MScleanupmergepublisher system stored procedure and then select Modify:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_MScleanupmergepublisher]    Script Date: 19.12.2017 12:12:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[sp_MScleanupmergepublisher]
as
    exec sys.sp_MScleanupmergepublisher_internal


John Huang (MVP) outlines an approach in his blog post:

http://www.sqlnotes.info/2013/05/07/attach-database-with-missing-ndf-file/

The basic approach is outlined by John Huang as:

Create a empty database with the same file layout, including names, filegroups, location, etc, everything but data
Take the missing files offline
Take the database offline
Use the data files you have saved to overwrite the empty data files
Bring the database online

This allows you the chance to cobble things back together, but it can still have issues.

So, please, make sure you maintain good and complete backups.


Unexpected Row of Data when Joining two tables

You need to use LEFT (outer) join. Try this:

SELECT
  a.Article,
  COALESCE(b.ID, 0) AS ID,
  a.Misc,
  b.Misc2
FROM
  [table] a
    LEFT JOIN
        [table] b
    ON
        b.article =  a.article ;

You may want to use COALESCE(b.ID, a.ID) instead of COALESCE(b.ID, 0), depending on what you want to show (a.ID or always 0) for articles that appear in a but not b.

Tested in: dbfiddle.uk
(test kindly provided by @McNets)


Stopping SQL Server Log Reader Agent In 2012

SQL won't clear transactions from the log that the log reader hasn't marked as done (Committed to Distribution).  So your log may grow while the Distribution server is down, but once it comes back, the log reader should be able to catch up over time.
From BOL:

The Log Reader Agent runs at the Distributor; it typically runs continuously, but can also run according to a schedule you establish. When executing, the Log Reader Agent first reads the publication transaction log (the same database log used for transaction tracking and recovery during regular SQL Server Database Engine operations) and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data in transactions that have been marked for replication. Next, the agent copies those transactions in batches to the distribution database at the Distributor. The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.
After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed. Finally, the agent marks the rows in the transaction log that are ready to be purged. Rows still waiting to be replicated are not purged.


Forcing view to use certain execution plan

You can't force an execution plan on a view. Depending on how the view is accessed, SQL Server will generate a different plan.

Querying a view without a where clause may result in table scans, but querying the same view with a predicate on AccountID could result in seeks.

It sounds like you might want to research plan guides, which will allow you to supply query hints (including the USE PLAN hint) without modifying the incoming query. If you're using SQL Server 2016 or newer, you can also use Query Store to force a plan.


How do i merge multiple records into a single record

You can use PIVOT and dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N'';
DECLARE @category NVARCHAR(20) = N'IT Queue';

SELECT @cols = @cols + STUFF((  SELECT ',' + QUOTENAME([Status])
                               FROM dbo.[Status]
                               GROUP BY [Status]
                               FOR XML PATH('')), 1, 1, '');

SET @sql = N' SELECT *
              FROM (SELECT  ''' + @category + ''' Category,
                            s.[Status],
                            q.StatusId
                    FROM dbo.[Status] s
                    INNER JOIN dbo.CactusQueue_Task q
                        ON s.Id = q.StatusId) AS d
  PIVOT (COUNT(StatusId) FOR [Status] IN (' + @cols + ')) AS p;';

EXEC sp_executesql @sql;


Besides attaching the debugger, is there any other differences between "(local)\Instance" and "MyLocalName\Instance"?

(local) will work with any programs that understand to change (local) or . or localhost into the machine's network location. I would imagine that is the issue with the debugger. But as long as the program you are using (i.e. SSMS) understands that translation, there is no behavior difference to worry about.

I would imagine for most production servers, most connections will not be happening from a local machine as you would have a separate database server for security, availability and performance. So for that reason, it may be better to refer to the server as you will from other machines, but most of the time when working locally I just connect to . or .\instanceName since I hate typing ;-)


Does cmdexec have additional process isolation beyond cmd.exe?

Go to the advanced tab for the step.  Use one of the options to capture more output such as "Include step output in history" or the "Output File" setting. This should give you more information about the step and what is happening.


How to go about testing your network for an AlwaysOn implementation

The easiest way to test AG network throughput is to rebuild one of your largest indexes.

While the index rebuild is running (and afterwards), measure the latency for each replica using any of these scripts:

Measuring Availability Group synchronization lag - Derik Hammer
Monitoring Availability Group Replica Synchronization - Jonathan Kehayias

This will give you a rough idea of how far behind each replica gets. If performance doesn't meet your goals, then you can drill into more granular measurements.

Keep in mind that network latency & bandwidth isn't the only issue that can affect replica latency. There's also log file write latency and redo thread throughput on the replicas. Starting at the high level of just checking synchronization lag will tell you whether or not there's a problem at all first, and then you might be able to just avoid drilling down into details altogether - especially since you wrote that this is a DR-only setup, not for HA.


Can I move rows between partitions by updating the partition key?

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:

USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT]
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID])
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT]
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID])
ORDER BY Partition ;

You should see one record in each partition before the update, and both records in the first partition afterwards.


Ah nevermind, found the answer

SELECT DISTINCT LEFT(data->>'day_sess',1)::INT * (INTERVAL '1 day')
FROM x ORDER BY 1;


Do I need to add a new single column index to a table if a multi-column index on that field already exists?

No, not at all !!! The MySQL Query Optimizer will do the right thing if the main column(s) needed is leftmost in the index. If you did make such an index, the MySQL Query Optimizer may opt never to use that index if you always perform GROUP BY job_id,keyword_id. MySQL Query Optimizer may or may not use the index if you gather records by job_id only, but then you have a redundant index wasting space anyway.

If the table is MyISAM, making such an index would just bloat the MYI file.

If the table is InnoDB and innodb_file_per_table is 0, making such an index would just bloat ibdata1.

If the table is InnoDB and innodb_file_per_table is 1, making such an index would just bloat the .ibd file of the table.

In summary, you do not need to make that additional index !!!


Best way to shrink a DB after nulling data from varbinary(max)?

It looks to me like just updating the columns to NULL will release pages for reuse. Here's a Very Scottish® demo, to celebrate it being almost 5PM, EST.

USE tempdb;

DROP TABLE IF EXISTS dbo.RobertBurns;

CREATE TABLE dbo.RobertBurns
(
    Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    Scotch VARCHAR(50),
    HaggisAddress VARBINARY(MAX)
);

DECLARE @AddressToAVarbinaryHaggis VARBINARY(MAX);
DECLARE @AddressToAHaggis NVARCHAR(MAX) = N'
Good luck to you and your honest, plump face,
Great chieftain of the pudding race!
Above them all you take your place,
        gut, stomach-lining, or intestine,
You''re well worth a grace
        as long as my arm.

The overloaded serving tray there you fill,
Your buttocks shaped like a distant hilltop,
Your wooden skewer could be used to fix a mill
         if need be,
While through your pores your juices drip
         like liquid gold.

His knife see the serving-man clean,
And then cut you up with great skill,
Making a trench in your bright, gushing guts
        To form a ditch,
And then, 0h! What a glorious sight!
        Warm, steaming, and rich!

Then, spoonful after spoonful, they eagerly eat,
The devil will get the last bit, on they go,
Until all their well-stretched stomachs, by-and-by,
        are bent like drums,
Then the head of the family, about to burst,
        murmurs “Thank the Lord".

Is there a pretentious soul who, over his French ragout,
Or Italian cuisine that would make a pig sick,
Or French stew that would make that same pig ill
        with complete and utter disgust,
Looks down with a sneering, scornful attitude,
        on such a meal? (as Haggis)

Poor devil! See him over his trash!
As feeble as a withered bullrush,
His skinny leg no thicker than a thin rope,
        His fist the size of a nut,
Through a river or field to travel,
        Completely unfit!

But look at the healthy, Haggis-fed person!
The trembling earth respects him as a man!
Put a knife in his fist,
        He''ll make it work!
And legs, and arms, and heads will come off,
        Like the tops of thistle.

You Powers who look after mankind,
And dish out his bill of fare,
Old Scotland wants no watery, wimpy stuff
        That splashes about in little wooden bowls!
But, if You will grant her a grateful prayer,
        Give her a Haggis!';

INSERT dbo.RobertBurns (Scotch, HaggisAddress )
SELECT TOP 1000
CASE WHEN x.c % 15 = 0 THEN 'Laphroaig'
     WHEN x.c % 5 = 0 THEN 'Lagavulin'
     WHEN x.c % 3 = 0 THEN 'Port Ellen'
     ELSE 'Ardbeg'
END AS Scotch,
CONVERT(VARBINARY(MAX), REPLICATE(@AddressToAHaggis, x.c % 20 + 1))
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS c
FROM sys.messages AS m
) AS x;

CREATE INDEX ix_novarbinary ON  dbo.RobertBurns (Scotch, Id);
CREATE INDEX ix_yesvarbinary ON dbo.RobertBurns (Scotch, Id) INCLUDE (HaggisAddress);

With rows inserted, let's check on our index pages.

SELECT   OBJECT_NAME(i.object_id) AS table_name,
         i.name AS index_name,
         MAX(a.used_pages) AS leaf_me_alone
FROM     sys.indexes AS i
JOIN     sys.partitions AS p
ON p.object_id = i.object_id
   AND p.index_id = i.index_id
JOIN     sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = 'RobertBurns'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY OBJECT_NAME(i.object_id), i.index_id;

After the insert, I get this. Actual pages may vary for you.

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5587
RobertBurns ix_novarbinary                  10
RobertBurns ix_yesvarbinary                 5581

Let's NULL out some rows!

UPDATE rb
    SET rb.HaggisAddress = NULL
FROM dbo.RobertBurns AS rb
WHERE rb.Id % 15 = 0;

And check back in on our pages:

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5300
RobertBurns ix_novarbinary                  10
RobertBurns ix_yesvarbinary                 5273

So page count was reduced. Huzzah! For the two indexes that touch our VARBINARY data, they lost a buncha pages. That means they're back in circulation for other objects to use. Since I'm in tempdb, they probably get gobbled up pretty quickly by all the garbage things that go on here.

Now let's put some data back in:

INSERT dbo.RobertBurns (Scotch, HaggisAddress )
SELECT TOP 10 rb.Scotch, rb.HaggisAddress
FROM dbo.RobertBurns AS rb;

And checking back in:

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5330
RobertBurns ix_novarbinary                  11
RobertBurns ix_yesvarbinary                 5305

Page counts inched up a bit.

So, looks like you don't have to do anything too crazy, or even shrink your database to get space reuse. I think you were conflating the behavior of dropping columns and needing to run DBCC CLEANTABLE with what you're actually doing.

Hope this helps!


sqlserver 2008r2 service pack incompletely applied on shared features

After running an instance repair, the correct version number is now being displayed.

The shared features are still incompletely installed but at least querying the @@version view will now return the correct patch level.


How to split a row based on a delimiter into multiple rows and retain the ID and ordering by using a single SQL statement for MySQL

SQL is a real (but **DECLARATIVE**, not imperative) programming language! This has its pros and cons, see here.

But without the sarky comment from @RickJames :-), I was going to say the same thing. You'll always get problems like this when you don't properly normalise (you have what are called repeating groups - a breach of Codd's Rule no. 2 - scalar values).

As @RickJames says, you'll have to programme your way out of this one using an imperative language, but NOT SQL.


since you confirmed the character set in the database is AL32UTF8, you need to make sure the fonts is installed on the Linux machine, and install the message libraries. that will make ORA_NLS33 has been set appear when you execute alter session set nls_language=


Is there an equivalent to MySQL SHOW DATABASES statement?

There is no such thing. You can query listeners on a machine (lsnrctl status) to see what services are registered there, but that doesn't map one-to-one to database (and there could be multiple listeners on the same machine). Otherwise, the tools commonly used connect to one database instance, and an instance belongs to a single database.

If you're talking about Oracle RAC clusters, then each instance knows of its peers (other instances that service the same database) and you can find the other instances currently started for that database using the gv$instance view.
You can also use the crsctl utility to list the services (including databases) that are registered in the cluster, and their status.
If you're talking about another vendor's clustering software, I'm pretty sure they all have these types of resource management utilities to query.

If you're talking about just a bunch of machines, then no, there's no 100% reliable way of enumerating all databases on a network.

To find active (i.e. started) databases, look for *_pmon_* processes on Unix (there's one per database instance), and Oracle services on Windows.

To locate installations of Oracle database software, look at /etc/oratab on Unix. This should contain all the ORACLE_HOMEs installed. You can look inside each of those in $ORACLE_HOME/dbs for spfile<SID>.ora and/or init<SID>.ora files - there will be one for each database.

(I believe you can find the equivalent of the information in oratab in the Windows registry keys below HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, but I don't know its structure.)

Now, of course, if you've registered all your database with an OEM (Enterprise Manager) server when you installed them, you can find the complete list there - but I guess if you're asking that's not the case.


fundamental difference between Primary/Clustered and Secondary/Non-Clustered index

And it looks like curiosity didn't actually kill the cat :-). So I found this book Database System Concepts (Sixth Edition) online and it's freely available to be read. I've scratched it a bit and it turns out that it has the needed definition inside:

Primary index: in a sequentially ordered file, the index whose search
key specifies the sequential order of the file. Also called
clustering index. The search key of a primary index is usually but
not necessarily the  primary key.
Secondary index: an index whose search key specifies an order  different from the sequential order of the file. Also called  non-clustering index.

So there you have it. Now I suppose you were reading another book (by same author(s)) which was with tests inside (Q&A) and not this one with theory. I'd say you should use them both at the same time.
Now, I'm not sure that a general book would be the best way to start. Personally I'd start with any freely available kit (SQL Server Express, MySQL Lamp, Oracle XE, SQLite...whatever sounds good for your ear) and play with concepts and also their physical implementation. I'd say going also with examples will work better than just pure theory.


I have my ORDER
Every ORDER has ITEMS
Every ITEM has his VARIANTS
Every VARIANT has his QUANTITY

In general:
CREATE TABLE t_order (order_id INT AUTO_INCREMENT PRIMARY KEY,
                      {other columns});

CREATE TABLE t_items (item_id INT AUTO_INCREMENT PRIMARY KEY,
                      order_id INT REFERENCES t_order (order_id),
                      {other columns});

CREATE TABLE t_variants (variant_id INT AUTO_INCREMENT PRIMARY KEY,
                         item_id INT REFERENCES t_items (item_id),
                         quantity DECIMAL(10, 2),
                         {other columns});


I've figured out how to execute custom queries against MariaDB in Excel.

Create your DSN using the "ODBC Data Sources (32-bit)" desktop app and leave all options as the default, except the connection page settings and "Return matched rows instead of affected".

In Excel, on the sheet you want the query to show up on, on the data tab, select "From Other Sources" -> "From Data Connection Wizard".

In the dialog that pops up, select "ODBC DSN" and click "Next".
Find the DSN you created earlier and click "Next".
Now you need to select a table, just one. (We will change this later)
Click "Next", then "Finish", then "OK". The data from your table should populate the sheet, starting in what ever cell you specified before hitting that last "OK".

Okay, That's great! But how do I run a custom query with joins and a where clause? Read on my friend.

Select any cell in the table that resulted from the above steps.
In the design tab (should have opened when you clicked into the table) select the "Properties" button.

Click the "Connection Properties" button beside the "Name" field.

Now, on the "Definition" tab, in the "Command text:" field, you can paste in your query.

I've tested this with basic joins and where statements. Everything seemed to work fine, so this should be enough to get you started. I have not tested sub-queries or any other advanced features yet.


OK, so odds are it isn't link that Up_One posted.

SQL Server does all it's work via schedulers.  Each scheduler is attached to a CPU core on the server.  Usually when I've seen that error it's because the CPUs are very busy and can't properly respond to the request in the time needed.

What's the CPU workload on the SQL Server look like when the problem happens?  How many users are running queries at that time?  How many CPU cores does the server have?

The fix is to reduce the CPU workload on the server.  Typically this is done by doing index tuning on the system which will reduce CPU workload.


Create a table with a select * from a large table and where 1=0. Gather stats, save them using dbms_stats and then apply those stats to the large table. You will essentially tell Oracle that the large table has no data, which would favor full table scans. You can also try switching the optimizer mode to first_rows_1 or invalidating some indexes.


How to update ID=null values in table to incremental counter values?

You can do this.

WITH T
     AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) +
                    ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID,
                ID
         FROM   StagingTable
         WHERE  ID IS NULL)
UPDATE T
SET    ID = New_ID

So the windowed function is used in the SELECT list but you can still use the result of it to UPDATE the column.

You should probably have a filtered index unique constraint on ID WHERE ID IS NOT NULL to prevent duplicates too. Or run this at serializable isolation level to block concurrent inserts.


Possible to add KEEPFIXED PLAN option to reduce the recompile in SQL Server 2005 in Production Env?

Personally, I wouldn't use it.

There are better ways on managing this with asynchronous statistics or plan guides nowadays. I've only ever seen this hint used on some old trading database that was geared towards Sybase/very old SQL Server.

Also, you now have statement level recompilation that may fit your use case better

But really, you'll have to try it and find out in your situation...


Transaction Log Files Larger Than Data Files

Jeff, Brent here. We put a lot of documentation in that URL - make sure you copy/paste the URL into your web browser to read the full documentation.

If you're in simple recovery model, and you've got log files larger than the database, there are lots of possible causes:

Someone started a transaction, locked their workstation, and went home for the weekend
Someone ran a legitimately large transaction (like a huge update across the entire database, multiple times)
The log is growing due to something other than a backup (to find out what it is, check SELECT * FROM sys.databases, and look at the log_reuse_wait_desc column)

My bigger concern would be the fact that your databases are in simple recovery model. That means you can't do a point-in-time recovery. If something goes wrong, you're restoring all the way back to your last good full backup. Businesses are often uncomfortable with that much data loss.


Auto Update Statistics threshold SQL Server 2016

Until our maintenance job will update statistics again with fullscan
next time, the auto statistics update runs and resets the ‘fullscan’
results

This is the side effect of updating statistics with fullscan on a weekly basis, it becomes more of a guessing game when your statistics are going to be automatically updated.
As mentioned in the comments and since you hopefully are on a patch level of SQL Server 2016 SP1 CU4 or above, setting the persisted sample rate higher can work for the tables that you are updating WITH FULLSCAN on a weekly basis.
You could try working with a lower sample rate than fullscan but still higher than the default sample rate for these tables to reduce the time it takes to update.
Another setting that can help due to your auto update statistics taking a longer time when a higher sample rate is used is to enable ASYNC_STAT_UPDATES. Enabling asynchronous stat updates makes your queries not wait for your statistic updates when enough changes have been made, causing them to use the old statistics which have a high sample rate.
The caveat is that the lowest level possible to enable asynchronous statistic updates is for your entire database. It would be wise to test this setting in development.
UPDATE STATISTICS dbo.tablename WITH SAMPLE 1 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS dbo.tablename WITH SAMPLE 5 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS dbo.tablename WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

To enable asynchronous statistic updates:
ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;

Remember that when you truncate the table the sample rate is no longer persisted and when new indexes and their corresponding statistics are created that they will have the default sample rate.
Another idea would be to update your statistics more often for the tables that have more changes if your server has the resources to do so.