Mysql query time massively affected by limit

The bigger the limit, the more of the 500K rows it will have to scan.  Hence the longer the query will take.
Yes, I see that the growth is not "linear"; there are many possible reasons for that, but let's ignore that and go for what you really want -- the query to be faster.
See the following for several ways to speed up the query.  In particular, pick the SPATIAL option.  http://mysql.rjweb.org/doc.php/find_nearest_in_mysql
Your indexes on lat and lng are not useful for this query, and tend to be not useful if you added a "bounding box" constraint in the WHERE clause.  However, INDEX(lat, lng), INDEX(lng, lat) is useful for a bounding box.
But the real problem seems to be that the Spatial test is in HAVING.  The Spatial index can be used if you move that test into the WHERE clause.  Yes, you will have to repeat the expression; too bad.
PS:  distance is not null is redundant; the test < 30 fails for NULL.
Since there is no test on lat or lng, INDEX(parser_code, lat, lng) won't help.  You could add a bounding box test; that might let that composite index be even faster than SPATIAL; that would depend on the cardinality of parser_code.


Query for: view users and permissions (roles) from root security folder (MS SQL)

It sounds like you are looking for a script similar to this one...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]([DBNAME] [SYSNAME] ,
       [USERNAME] [SYSNAME] ,
       [DB_OWNER] VARCHAR ,
       [DB_ACCESSADMIN] VARCHAR ,
       [DB_SECURITYADMIN] VARCHAR ,
       [DB_DDLADMIN] VARCHAR ,
       [DB_DATAREADER] VARCHAR ,
       [DB_DATAWRITER] VARCHAR ,
       [DB_DENYDATAREADER] VARCHAR ,
       [DB_DENYDATAWRITER] VARCHAR ,
       [DT_CREATE] [DATETIME] NOT NULL,
       [DT_UPDATE] [DATETIME] NOT NULL,
       [DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3]  DEFAULT (GETDATE()) ) ON [PRIMARY]
       GO
       INSERT INTO    [TEMPDB].[DBO].[DB_ROLES]
       EXEC SP_MSFOREACHDB 'SELECT''?'' AS DBNAME, USERNAME, MAX(CASE ROLENAME WHEN ''DB_OWNER''THEN ''YES'' ELSE ''NO'' END)
       AS DB_OWNER,        MAX(CASE ROLENAME WHEN ''DB_ACCESSADMIN ''   THEN ''YES'' ELSE ''NO'' END) AS DB_ACCESSADMIN ,
       MAX(CASE ROLENAME WHEN ''DB_SECURITYADMIN''  THEN ''YES'' ELSE ''NO'' END) AS DB_SECURITYADMIN,
       MAX(CASE ROLENAME WHEN ''DB_DDLADMIN''   THEN ''YES'' ELSE ''NO'' END) AS DB_DDLADMIN,
       MAX(CASE ROLENAME WHEN ''DB_DATAREADER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAREADER,
       MAX(CASE ROLENAME WHEN ''DB_DATAWRITER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAWRITER,
       MAX(CASE ROLENAME WHEN ''DB_DENYDATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAREADER,
       MAX(CASE ROLENAME WHEN ''DB_DENYDATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAWRITER,
       CREATEDATE, UPDATEDATE, GETDATE()
            FROM (SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE FROM [?].DBO.SYSMEMBERS A   JOIN [?].DBO.SYSUSERS  B  ON A.MEMBERUID = B.UID
            JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID)S
            GROUP BY USERNAME, CREATEDATE, UPDATEDATE
            ORDER BY USERNAME'
            SELECT  SERVERPROPERTY('SERVERNAME') AS [SERVERNAME], B.NAME AS [LOGINNAME],
            CASE B.SYSADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS SYSADMIN, CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO'
            END AS SECURITYADMIN, CASE B.SETUPADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS SETUPADMIN, CASE B.PROCESSADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS PROCESSADMIN, CASE B.DISKADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS DISKADMIN, CASE B.DBCREATOR  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS DBCREATOR, CASE B.BULKADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS BULKADMIN, B.DBNAME AS [DEFAULT_DBNAME],
            A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME WHERE B.ISNTUSER=1
            SELECT * FROM #LOGINS ORDER BY [LOGINNAME]
            DROP TABLE  [TEMPDB].[DBO].[DB_ROLES]
            DROP TABLE  #LOGINS


How to avoid Forced reboots using unattended upgrades to SQL Server 2014

Hi Craig,

I may have a solution for you.

You should go to the registry and delete the registry key below:
HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

I've created PS scripts for upgrading SQL Server Instances.

Here an example:

# Select the source path and the Instance name.
# Launch the script on a PowerShell console as Administrator.

$SourcePath = "\\MYNAS\Sources\SQL Server 2014"
$InstanceName = "MYINSTANCE1"

Set-Location $SourcePath

$exists = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"  -ErrorAction SilentlyContinue
    If (($exists -ne $null) -and ($exists.Length -ne 0)) {
        Remove-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"
    }

.\setup.exe /ACTION="Upgrade" /Q /INDICATEPROGRESS="True" /INSTANCENAME=$InstanceName /IACCEPTSQLSERVERLICENSETERMS

$InstanceName = "MYINSTANCE2"

Set-Location $SourcePath

$exists = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"  -ErrorAction SilentlyContinue
    If (($exists -ne $null) -and ($exists.Length -ne 0)) {
        Remove-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"
    }

.\setup.exe /ACTION="Upgrade" /Q /INDICATEPROGRESS="True" /INSTANCENAME=$InstanceName /IACCEPTSQLSERVERLICENSETERMS

I hope this will help.

Max.


For the port you need to start mongod with parameter --port 57777 . Alternative place it on the configuration file under

net:
   port: 57777

For the username and password part you need to enable security by start  mongod with parameter--auth . Alternative place it on the configuration file under security.authorization = enable
Then you can create the username:root assign a password and certain privileges.

More can be found here: http://docs.mongodb.org/manual/core/authorization/


Breaking down by minutes in between two dates

You are going to think I am crazy but here it goes

PROPOSED QUERY

select the_time,count(1) visits from
(select the_date + interval m minute the_time from
(select '2014-10-30' the_date) AA,(select hh*60+mm m from
(select 0 hh union select  1 union select  2 union select  3 union select  4 union
select  5 union select  6 union select  7 union select  8 union select  9 union
select 10 union select 11 union select 12 union select 13 union select 14 union
select 15 union select 16 union select 17 union select 18 union select 19 union
select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
(select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
(select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
inner join visit on the_time >= starttime and the_time <= endtime
group by the_time;

What the query does is generate every minute of a day. You inject the date in subquery AA

SAMPLE DATA

use test
drop table if exists visit;
create table visit
(
    visit_id int not null auto_increment primary key,
    starttime datetime not null,endtime datetime not null
);
insert into visit (starttime,endtime) values
('2014-10-30 10:10:00','2014-10-30 10:15:00'),
('2014-10-30 10:15:00','2014-10-30 10:20:00'),
('2014-10-30 10:12:00','2014-10-30 10:18:00');

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> drop table if exists visit;
Query OK, 0 rows affected (0.00 sec)

mysql> create table visit
    -> (
    ->     visit_id int not null auto_increment primary key,
    ->     starttime datetime not null,endtime datetime not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into visit (starttime,endtime) values
    -> ('2014-10-30 10:10:00','2014-10-30 10:15:00'),
    -> ('2014-10-30 10:15:00','2014-10-30 10:20:00'),
    -> ('2014-10-30 10:12:00','2014-10-30 10:18:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY EXECUTED

mysql> select the_time,count(1) visits from
    -> (select the_date + interval m minute the_time from
    -> (select '2014-10-30' the_date) AA,(select hh*60+mm m from
    -> (select 0 hh union select  1 union select  2 union select  3 union select  4 union
    -> select  5 union select  6 union select  7 union select  8 union select  9 union
    -> select 10 union select 11 union select 12 union select 13 union select 14 union
    -> select 15 union select 16 union select 17 union select 18 union select 19 union
    -> select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
    -> (select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
    -> (select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
    -> select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
    -> inner join visit on the_time >= starttime and the_time <= endtime
    -> group by the_time;
+---------------------+--------+
| the_time            | visits |
+---------------------+--------+
| 2014-10-30 10:10:00 |      1 |
| 2014-10-30 10:11:00 |      1 |
| 2014-10-30 10:12:00 |      2 |
| 2014-10-30 10:13:00 |      2 |
| 2014-10-30 10:14:00 |      2 |
| 2014-10-30 10:15:00 |      3 |
| 2014-10-30 10:16:00 |      2 |
| 2014-10-30 10:17:00 |      2 |
| 2014-10-30 10:18:00 |      2 |
| 2014-10-30 10:19:00 |      1 |
| 2014-10-30 10:20:00 |      1 |
+---------------------+--------+
11 rows in set (0.00 sec)

mysql>

PROPOSED QUERY WITH DATE FORMATTED, TOTAL VISITS

select IFNULL(TIME(the_time),'Total Visits') the_time,
count(1) visits from
(select the_date + interval m minute the_time from
(select '2014-10-30' the_date) AA,(select hh*60+mm m from
(select 0 hh union select  1 union select  2 union select  3 union select  4 union
select  5 union select  6 union select  7 union select  8 union select  9 union
select 10 union select 11 union select 12 union select 13 union select 14 union
select 15 union select 16 union select 17 union select 18 union select 19 union
select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
(select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
(select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
inner join visit on the_time >= starttime and the_time <= endtime
group by the_time with rollup;

EXECUTED...

mysql> select IFNULL(TIME(the_time),'Total Visits') the_time,
    -> count(1) visits from
    -> (select the_date + interval m minute the_time from
    -> (select '2014-10-30' the_date) AA,(select hh*60+mm m from
    -> (select 0 hh union select  1 union select  2 union select  3 union select  4 union
    -> select  5 union select  6 union select  7 union select  8 union select  9 union
    -> select 10 union select 11 union select 12 union select 13 union select 14 union
    -> select 15 union select 16 union select 17 union select 18 union select 19 union
    -> select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
    -> (select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
    -> (select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
    -> select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
    -> inner join visit on the_time >= starttime and the_time <= endtime
    -> group by the_time with rollup;
+--------------+--------+
| the_time     | visits |
+--------------+--------+
| 10:11:00     |      1 |
| 10:12:00     |      1 |
| 10:13:00     |      2 |
| 10:14:00     |      2 |
| 10:15:00     |      2 |
| 10:16:00     |      3 |
| 10:17:00     |      2 |
| 10:18:00     |      2 |
| 10:19:00     |      2 |
| 10:20:00     |      1 |
| 10:20:00     |      1 |
| Total Visits |     19 |
+--------------+--------+
12 rows in set, 1 warning (0.01 sec)

mysql>

GIVE IT A TRY !!!


SQL Server Standard Edition CPU + Tempdb Configuration

ASYNC_NETWORK_IO has nothing to do with tempdb.  ASYNC_NETWORK_IO is the wait that occurs when SQL Server is transmitting data to the client.  So the only ways to address this are to upgrade the network to provide more bandwidth by either upgrading the network or, if the network is saturated, address the saturation in whatever way possible, or modify the queries or number of users to reduce the amount of data that has to be transmitted to clients.

For SOS_SCHEDULER_YIELD, giving the full treatment in an answer here is a bit overkill (IMHO), and I would refer you to SOS_SCHEDULER_YIELD at sqlskills.com to get started on your journey there.  However, I might say that if you have one or more queries that are returning massive amounts of data, resulting in the ASYNC_NETWORK_IO waits, it's very likely that the same queries are also the cause of the SOS_SCHEDULER_YIELD waits.

These waits may be inevitable on data warehouse or big data systems where queries that return massive amounts of data are the norm.


Should a table have a foreign key to another table if it already has a foreign key to another table that has that foreign key?

It is not a good practice for many reasons. The most important one, design principles, you should never mix entities just because it is easier for the programmer/dba to do queries. Entities should be as clear as they can and must exclude any other information not related to them.

For your scenario, to make it easier for future querying, you can create a view that does the join and instead query this view.

Regarding any benefit that could be possible while cutting a join, there are other costs maintaining the foreign keys, so there is not really a huge advantage either.

If you are in SQL Server, you can create an index for the view, which materializes the view (creates an underlying table with the data) and you will have performance as querying a single table.


Python script leaves the database in restoring mode

Pyodbc won't progress past info messages automatically, and RESTORE generates a lot of them. So you must process them with cursor.nextset() or else you're actually aborting the RESTORE by running cursor.close() before it actually completes.
Eg
cursor.execute(sql)
while cursor.nextset():
    pass
cursor.close()


what is difference between last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats?

Worker time is the time the task(s) was effectively active, occupying a scheduler and running code (ie. not suspended). Elapsed time is clock time. On a DOP 1 query the worker time will be at most the elapsed time, less if the task was suspended at any moment during execution (thus the clock time would advance, but the worker time not). For DOP > 1 the worker times aggregate so they can exceed elapsed time, while still subject to suspension.

A significant difference between worker time and elapsed time is indicative of blocking, consider the 682616 worker vs. 11509766 elapsed: this a request that blocked for 11 seconds waiting on something (probably a lock).


Grant permissions to run an SQL server job

You could create a stored procedure that runs the job.  You can use with execute as owner to run the stored procedure as the database owner.  That way, the users themselves don't need permissons on sp_start_job.

create procedure dbo.DoYourJob
with execute as owner
as
exec sp_start_job @job_name = 'YourJob'

Grant execute rights on DoYourJob to allow people to start the job.

Of course, you can also put the contents of the job in a procedure, and grant rights to run that.  That would allow more interaction, like displaying a result text.


SQL problem with checking next and previous rows

How about a rewrite of it?

;with eMK_Mileage
AS (SELECT 15755 AS ID, '8E79F' AS FK_CarID, 69646 AS KM , '20140716' AS KM_Date UNION ALL
SELECT 15756,'8E79F' , 70393 , '20140726' UNION ALL
SELECT 15757,'8E79F' , 58258 , '20140822' UNION ALL
SELECT 15758,'8E79F' , 72055 , '20140822')
,FullList
AS (
 SELECT  * FROM eMK_Mileage as Mileage
)
-- FINAL SELECT
SELECT
*
,CASE WHEN FL.KM < ISNULL(P.KM_PREV,FL.KM) THEN 0 ELSE 1 END AS validKMstand
,CASE WHEN FL.KM > ISNULL(N.KM_NEXT,FL.KM) THEN 0 ELSE 1 END AS validKMstand2
FROM
FullList AS FL
OUTER APPLY
--PREVIOUS ROW
(SELECT TOP 1
        KM AS KM_PREV
    FROM FullList AS PREV
    WHERE
        FL.FK_CarID = PREV.FK_CarID
        AND FL.KM_Date>=PREV.KM_Date
        AND FL.ID>PREV.ID
    ORDER BY
        KM_Date DESC, ID DESC
)AS P
--NEXT ROW
OUTER APPLY
(SELECT TOP 1
        KM AS KM_NEXT
    FROM FullList AS NXT
    WHERE
        NXT.FK_CarID = FL.FK_CarID
        AND NXT.KM_Date>=FL.KM_Date
        AND NXT.ID > FL.ID
    ORDER BY
        KM_Date ASC, ID ASC
)AS N
ORDER BY ID,KM_Date

the output, for my sample is this:

ID  FK_CarID    KM  KM_Date KM_PREV KM_NEXT validKMstand    validKMstand2
15755   8E79F   69646   20140716    NULL    70393   1   1
15756   8E79F   70393   20140726    69646   58258   1   0
15757   8E79F   58258   20140822    70393   72055   0   1
15758   8E79F   72055   20140822    58258   NULL    1   1


BI Development is a big subject to learn and will take a lot of time understand all the concepts, I would suggest this book as place to start your journey of discovery: The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset by Joy Mundy
Permalink: http://www.amazon.co.uk/dp/0470640383


How to apply a newly-created Domain to an existing Column?

Incorrect SQL was the problem. The ALTER COLUMN must be part of an ALTER TABLE.

This SQL does indeed alter the data type of the existing column from VARCHAR to the newly-created domain.

ALTER TABLE unit_
ALTER COLUMN fuel_
SET DATA TYPE fuel_domain_;


Is it advisable to add indexes to a data warehouse's staging area?

We have a similar situation as the one you described, except that these copied tables are only used for ETL (by select queries). I'll describe how we approached it, but you have to evaluate if it works for your environment (see Jon Seigel's comment at your question).

The concept is straightforward:

We index these "copy" tables in the staging area to support queries as much as possible. Most of indexes (especially on the large tables which get hit by scans) are columnstore indexes (we're on 2012 as well), some are covering nonclustered indexes. As I said, these tables are (for the time being) only used for select queries in the ETL, so we can make sure every query is supported either by a columnstore index or a covering nonclustered index.
Before the data pump (copy) from source systems into the staging area, we drop the indexes, to make bulk loads as fast as possible and to ensure we're touching source systems for the minimal amount of time. After the data pump is complete, we recreate them - the logic is set up so that the recreation is done "dynamically" (as in, indexes are not hardcoded - every index that gets dropped is then recreated exactly as it was), which allows us to change indexing without having to maintain the drop/create code.

This approach works for us, drop/recreate is faster than loading into indexed tables; well in the case of a columnstore index you don't have many other options anyway apart from partitioning & using partition switch, but we're not doing that for these tables (EDW fact tables are a different story).


Understanding DB Throughput Calculations

If we are talking about throughout and the scenario you describe i would consider that your requirement is 10 x 6kb/sec which would be 60kb/sec.

So yes assuming you had 10 concurrent requests and you tried to move 10 x 100kb it would take 17 seconds.

In theory 1000kb / 60kb per sec = 16.66sec.

In the real world if the app required data from the db, the response time would vary depending on whether the db had to parse the query, create an execution plan and then read from disk. This all adds up to delays (latency) before the data starts being returned to the app.

If this is a commonly executed query (for example in a stored proc) the query has already been parsed, execution plan created and data already held in memory this means the initial delay would be much lower.

I would say it would be rare for an app to consistently consume data with 10 simultaneous connections. I guess a video streaming app could set a constant load for long durations. But more likely as other connections start and stop there would be more or less bandwidth available so transfer times could be lower. I would expect to see fluctuations in transfer rates.

Also: watch out for kilobit vs kilobyte,  And expect there will be some overhead. I.e It takes more than 6kb to move 6kb worth of data.


return field name and number of rows which meet condition in one sentence

you can use conditional aggregation for that:
select count(*),
       count(*) filter (where field1 ilike '%E+%') as field1_matches,
       count(*) filter (where field2 ilike '%E+%') as field2_matches,
       count(*) filter (where field3 ilike '%E+%') as field3_matches
from the_table
where field1 ilike '%E+%'
   OR field2 ilike '%E+%'
   OR field3 ilike '%E+%'


Migrate SSRS Reports to Power BI Report Server

...is there any way that I can put these services in the same
  server/database and running under the Power BI Report Server?

You can have SSRS and PBIRS on the same Server, but they will require their own ReportServer databases in order to run them both.

If you want to migrate your SSRS reports into PBIRS, then you need to follow the steps in the MS article. 1. Restore a copy of the SSRS database to a new server, and then 2. install PBI RS. PBI RS will update the database and retain the reports within it.

I think the article gets a little confusing as most people reading it will be migrating from SSRS to PBIRS on a different SQL Server. There isn't a lot of info about running both side by side. But it can be done.


Problem with SSIS Variable Expression containing getDate()

You could use a script task to set the variable property:

Dts.Variables["uVarDateStamp"].Value = DateTime.Date;


Split values from field and count them according to another field

You can achieve your goal by using a tally (number) table, which you can easily create in the following way

CREATE TABLE tally(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO tally (n)
SELECT NULL
 FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
;

  This will create a tally table and fill it with 100 rows with values
  1 to 100 accordingly.

You should adjust the number of rows in your table to be equal or more (e.g. double the number to be on the safe side) than a maximum number of delimiters (comma) in content table.
You can figure out the maximum number with a simple query

SELECT MAX(CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', ''))) max_tags
  FROM content;

Output:

| MAX_TAGS |
|----------|
|        4 |

Now you can use it to split tags

SELECT category_id, tag, COUNT(*) count
  FROM
(
  SELECT cc.category_id,
         SUBSTRING_INDEX(SUBSTRING_INDEX(c.tags, ',', n.n), ',', -1) tag
    FROM categories_to_content cc JOIN content c
      ON cc.content_id = c.id CROSS JOIN tally n
   WHERE n.n <= 1 + (CHAR_LENGTH(c.tags) - CHAR_LENGTH(REPLACE(c.tags, ',', '')))
) q
 WHERE CHAR_LENGTH(tag) > 0
 GROUP BY category_id, tag

Output:

| CATEGORY_ID | TAG | COUNT |
|-------------|-----|-------|
|           1 | abc |     2 |
|           1 | def |     1 |
|           1 | foo |     1 |
|           1 | ghi |     1 |
|           2 | bar |     1 |
|           2 | foo |     1 |

Here is SQLFiddle demo


computed column making the select query to run slow

"Is this a good plan to copy the indexes." Indexes exist to improve the response time of read operations. Those operations arise from the workload. If the workload differs, the indexes should differ, too.

For example, it is not uncommon to have one server handle the OLTP activity and replicate the data to a second server where analytic queries are processed. The first server's work will be mostly single row lookups. The second's range scans and aggregates. In this case having different indexes on each server makes good sense. If all indexes were on both servers there would be additional, unnecessary work to do during writes and maintenance.


In MySQL, should I add an index even if the query that scans the table is only ran once a month?

An IN clause breaks down to an OR

SELECT type FROM users WHERE type = 1 OR type = 3;

OR is typically (but not always) non-SARGable. That means an index won't be used too seek data but will be scanned. This is the same (roughly) as scanning the table anyway: same number of rows.

So given there is minimal benefit, I wouldn't bother in most cases.

If you have millions of rows and the query is blocking other processes, then you can do this if you have an index. No you have 2 seeks.

SELECT type FROM users WHERE type = 1
UNION ALL
SELECT type FROM users WHERE type = 3;

Summary: not worth it unless you have a big table and blocking issues.


Choose security group for restore-db-instance-to-point-in-time

Not without an additional API call.  You will need to wait for the target database to move to an "available" state and then use modify-db-instance with the target database and security group as parameters.

Here's some pseudocode based off of a similar example I have in Powershell. Selecting information from describe-db-instances will vary by which language you're using the API with, but by default it looks like it comes back as JSON.

restore-db-instance-to-point-in-time --source-db-instance-identifier sourceDatabase --target-db-instance-identifier targetDatabase
while(targetinstance not "available")
{
    targetinstance = describe-db-instances --db-instance-identifier targetDatabase
    sleep for X seconds
}
modify-db-instance --db-instance-identifier targetInstance --db-security-groups mySecurityGroup


In cases like this and the size of the DB (>17 Gb) I suggest based on the official documentation for version 9.2 the following:

do a pgdump_all

pg_dumpall [connection-option...] [option...]

pg_dumpall > databackup.out

Restore with psql. I have found out a better execution and stability

psql -f databackup.out postgres

Take extreme precaution with the file encoding. Specially if you are doing migrations among different OS and specially if you are uploading to a cloud RDBMS provider.


Which version of Microsoft Tape Format is supported by which MS SQL Server

The problem was indeed a corrupted backup, as Kin correctly deduced. Most likely the corruption was introduced during an ftp transfer.

So, when you get the error "The media family on device 'C:\myfile.bak' was created using Microsoft Tape Format version 1.8. SQL Server supports version 1.0." or the error "Specified cast is not valid. (SqlManagerUI)" when using the SQL Management studio Gui to restore a backup, you most likely have a corrupted backup file.

In my case, zipping the file before ftp transfer solved the problem.

You would think that backup files always contain a checksum, and if the file was later modified that sql server would report this incorrect checksum, but you would be wrong.


"Specify SQL Server administrators" - unusual default value

You're right: system administrators can gain sysadmin access, but you will find entries in the default trace for that action. Moreover, it requires restarting the service at least twice, which would hardly go unnoticed.

Not granting sysadmin privileges to BUILTIN\Administrators is a change introduced in SQL Server 2008. In my opinion it's a good idea: it allows the separation of duties between server administrators and SQL Server administrators.

The main undesired effect of having BUILTIN\Administrators in your sysadmin role is the loss of control, which can manifest with one or more of the following annoying things:

New databases appearing on your servers
Unwanted connections from windows admins that end up competing with legitimate connections
Implied permissions to windows admins on databases with sensitive
data
Seemingly innocuous but disrupting "self service" operations,
such as taking full "one-off" backups on databases with differential
backup strategies.

Long story short, windows admins have a different skill set and a different mindset, not to mention different responsibilities: letting them manage your SQL Server instances is not a good idea.

My suggestion is to create a windows group for your DBAs and add that group to the sysadmin role upon each new SQL Server installation.


I'm going to wrap database trigger's source code, command properly run but source not encrypt

Oracle's wrap program doesn't wrap triggers. The way to do this is to move the code into a package or a stand-alone procedure, and make the trigger a one line call to invoke this code.

See the documentation on the limitations of wrapping: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/wrap.htm#LNPLS01602

In your case, something like the following (untested) should work:

CREATE OR REPLACE PROCEDURE block_tools AS
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT upper(program) INTO v_prog
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0
AND  ROWNUM = 1;
IF UPPER(USERENV('TERMINAL')) NOT IN ('MOAZZAM-LT') THEN
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR --OR — Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR --    — SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR --— PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR --  — Business Objects
UPPER(v_prog) LIKE '%EXCEL%'  OR --    — MS-Excel plug-in
UPPER(v_prog) LIKE '%SQLPLUS%' OR --     — SQLPLUS
UPPER(v_prog) LIKE '%FRMBLD%' OR
UPPER(v_prog) LIKE '%IFBLD60%' or upper(v_prog) is null
THEN
RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login in '||v_prog);
END IF;
END IF;
END;
/

CREATE OR REPLACE TRIGGER block_tools_trigger_HR
AFTER LOGON ON HRMS.SCHEMA
CALL block_tools;

All I've done is move all the code into a stored procedure, and redefined the trigger to CALL that procedure.


would it be possible in postgres to get a count of sql hits on a select statement

Yes, you can use this in the select list:

 (case when tags ilike '%-1-%' then 1 else 0 end)
 + (case when tags ilike '%-3-%' then 1 else 0 end)
   as hits

or alternatively (convert the boolean values to integers (TRUE -> 1, FALSE -> 0) and then add:

 (tags ilike '%-1-%')::int + (tags ilike '%-3-%')::int
   as hits

But the query (and many others) would be much easier if you normalized the design and store the item-tag relationship in a separate, junction table.


Incorrect results using WITH RECOMPILE option

This ought not happen, but there have been several bugs that mean it might, depending on your version of SQL Server, and which patches you have applied.

The relevant Microsoft KB articles are listed below:

FIX: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012 or SQL Server 2014

FIX: A query that uses parameters and the RECOMPILE option returns incorrect results when you run the query in multiple connections concurrently in SQL Server 2008

FIX: Different results may be returned when you concurrently run the same query with the RECOMPILE option in SQL Server 2008


MySQL failed connection attempts hitting max_connections

I don't think the failed connections are causing the max connections issue, but can you set log_warnings=2 and after a short time, view the error log to see if there is more information there? The error log would record something like "access denied for user 'user'@'host'..."

The command to set log_warnings=2 is:

set global log_warnings=2;

You can find the error log like this:

show global variables like 'log_error';

What version of mysql are you running?

Can you also show the output of:

show global variables like 'skip_name_resolve';

and:

show global status like 'max_used_connections';


Multiple query to columns indexed in MySql

This index will take care of 3 of your queries:

INDEX(title, date,  -- in either order
      category)     -- last

Then you will need two more indexes to handle the other two queries.  Assuming you use the order above, then these could be the other two.

INDEX(category)
INDEX(date)

If you have a dozen columns and users can filter on virtually any combination of them, then all you can do is guess at what is best.  Some general rules:

don't make more than a dozen indexes
use about 3 columns in each index
be sure to start some of them with the most commonly used filters

But also, ...

If the filtering is on a range (eg, price range), it should be last in the index.
Don't listen to people who talk about cardinality when arguing for a particular order of columns in a composite index.
If title is too big to be indexed, then leave it out of all indexes.  Do not use "prefix indexing" like title(20).

More discussion:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


List foreign keys dependencies that refer to a given table

You are looking for the columns confupdtype and confdeltype in the catalog pg_constraint.

The codes mean:

  Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default


How to use RESTRICT for Foreign Key in mysql?

According to the MySQL Documentation on DELETE RESTRICT

  • RESTRICT: Rejects the delete or update operation for the parent table.
  Specifying RESTRICT (or NO ACTION) is the same as omitting the ON
  DELETE or ON UPDATE clause.

As for NO ACTION

  • NO ACTION: A keyword from standard SQL. In MySQL, equivalent to
  RESTRICT. InnoDB rejects the delete or update operation for the parent
  table if there is a related foreign key value in the referenced table.
  Some database systems have deferred checks, and NO ACTION is a
  deferred check. In MySQL, foreign key constraints are checked
  immediately, so NO ACTION is the same as RESTRICT.

DELETE RESTRICT protects the parent from deletion, not the children.


Indexing strategy when using the between operator SQL Server 2008

This is not a complete answer, I do not have the time now, so let me just share a few thoughts. The complete answer would be huge, and I am not sure you want to know the details.

I have been working with various temporal queries for several years already, and learned a lot in the process. As such, I would rather not have to optimize your query in my production system. I would try very hard to avoid solving it with T-SQL. It is a complex problem. Itzik Ben-Gan has written about "gaps and islands" several times, including a chapter in his latest book on OLAP functions. Your problem is a variation of gaps and islands.

First, I would consider reading all the data to the client and solve it there using loops. I know, it requires sending data over the network, but fast loops in Java/C++/C# work very well for me most of the time. For instance, once I was struggling with a query involving time series and temporal data. When I moved most of the logic to the client, the C# solution was several times shorter and it ran 20,000 times faster. That's not a typo - twenty thousand times faster.

There is another problem with solving such problems in T-SQL - your performance may be unstable. If a query is complex, all of a sudden the optimizer can choose another plan and it will run many times slower, and we have to optimize it again.

Alternatively, I would consider storing data differently. Right now I see two possible approaches.

First, instead of storing intervals we could use this table:

ClientId,
PrescriptionId,
DrugId,
Date

We can use trusted constraints to make sure each PrescriptionId covers a range of dates without gaps and overlaps, so that one interval stores as one unbroken sequence of dates.

Note: I know you are using DISTINCT in your first subquery, so you are assuming that one person can take one drug on one day from more than one prescription. I am not mkaing this assumption, for simplicity. Are you sure it is a correct assumption? If yes, we will have to change the design.

Once we have this table, we can essentially materialize your first subquery as an indexed view:

SELECT ClientId,Date,COUNT_BIG(*) AS DistinctDrugs
GROUP BY ClientId,
Date

THat done, you can use your second subquery to group data points into interval, or just run it on a client, where is can be solved as one trivial loop.

Second approach: instead of intervals I would store sequence of events. there woudl be two kinds of events: interval start and interval end. With every I would store a running total, the number of open events after this event has happened. Essentially this running total is the number of prescriptions active after the event has happened.

As in the previous approach, much of the data you calculate on the fly every time you run your query is pre-calculated in this table. We can use trusted constraints to ensure the integrity of precalculated data.

I can describe it in more detail later if you are interested.


Query execution plan is horrible until Statistics are Updated

You could use a manual plan guide to enforce the desired plan.

Alternatively, you could use Query Store to enforce plan guides via the GUI.

Also, you could run an update statistics job after the big load of messages.  I wrote a post on my blog showing an easy way to do that.


the value of undo_sql in flashback_transaction_query view is always null

As far as I remember, you should have supplemental logging be enabled so that database can collect the data needed to reconstruct the undo statements used for Flashback Transaction Query:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

For more information on enabling supplemental logging, read "Configuring Your Database for Oracle Flashback Transaction Query" section in Advanced Application Developer's Guide and "Supplemental Logging" section in Database Utilities (LogMiner is another utility which depends on supplemental logging).


MySQL cluster Replication and Failover via docker-compose

You may have set up replication, but failover doesn't just magically happen by itself. You need also

Some kind of cluster manager to notice the failure and initiate the failover procedure.
Complete the failover procedure and ensure that the new master is operational. Part of the process is described here.
A floating IP address or some sort of connection proxy to direct incoming connections to the new master.


Teradata 15: Failed [5315 : HY000] An owner referenced by user does not have SELECT WITH GRANT OPTION access to DBC.RoleMembersV.RoleName

Oh, I just figured out the correct grant statement.  All my views are working now.  I can audit my user/database/role permissions using these views.

GRANT SELECT ON DBC TO DBA_TABLES_TEST WITH GRANT OPTION;

Thank you.


do user_scans and no user_seeks mean the index is useless?

Not useless, but maybe sub-optimal.  Scanning a 10 MB index is a hell of a lot faster than scanning a 1 GB table, though a seek would be even better.  Can you tell us more about the indices?  How are the tables being used?


How are these tempdb tables automatically created after each SQL Server restart?

Thanks for your input. Actually, I found the answer:

Those tables were created by a stored procedure that is set to execute on startup of the database engine service, configured via the sp_procoption system stored procedure.


Service mongod fails after adding security.keyFile in config file

Use full path for keyfile. Don't use ~ -character there. Your mongod is started (probably) with user 'mongod' and that user don't have home directory.

Additionally keyfile must be have chmod 0400 and ownership same than user who run mongod process (mongod).


You can do this two ways.

Use the mysql client SOURCE command.

mysql> SOURCE mysql_stuff.txt or mysql> \. mysql_stuff.txt

or, you can use the mysql client from the shell.

$> mysql -u my_user -pmy_password my_schema < mysql_stuff.txt

Note - no space between -p and the actual password. If you don't put in the password, you will be prompted for it, but you must include the -p or else the client will throw an error.

Now, if you have multiple files and you wish to go through them and execute each one, then look here.

You could use sed, awk and grep in inventive ways to create SQL text files with a 1000 lines, each with a different file name - you could start with (in the directory containing your SQL files).

ls --format single-column > mysql_stuff.txt (bash or cygwin)

and then add SOURCE (+ space) to the beginning of each line

sed -i -e 's/^/SOURCE /' mysql_stuff.txt

1) -e not strictly necessary in this case, see here. It's used
in the case where one wishes to run multiple commands at once.
2) -i means perform the edit "inplace" - no new file is created. If you're not sure of your regular expressions (either search and/or replace), then backup your file.
3) the circumflex (^) is part of the regular expression search pattern. It means start the replace at the beginning of the line.

and then run SOURCE mysql_stuff.txt from the mysql client and it will go through the file executing each line. Oh, yes, don't forget to add a semi-colon ; to the end of each line also.

sed -i -e 's/$/;/' mysql_stuff.txt

the $ means perform the substitution at the end of the line.


SSMS suddenly starts generating DEFAULT constraints inlined into the CREATE TABLE statements instead of ALTER TABLE

This 'might' be the problem, although, there does not appear to be a 'solution'.  I was able to reproduce your issue. See this connect item regarding this problem:

https://connect.microsoft.com/SQLServer/Feedback/Details/895113

  The SQL SMO generates for a table with defaults depends on the rowcount of the table. This makes it impossible to programmatically compare the schema.


Background, functionality and performance

&& opperator

&& is bounding-box overlaps. All operators call functions in PostgreSQL: you can see this \doS+ && in this case && literally calls the PostGIS function geometry_overlaps. The only catch here is that && will make use of an index, from the docs

  In general, you will want to use the "intersects operator" (&&) which tests whether the bounding boxes of features intersect. The reason the && operator is useful is because if a spatial index is available to speed up the test, the && operator will make use of this. This can make queries much much faster.

You can see in the definition of geometry_overlaps that it calls an internal C function gserialized_overlaps_2d.  The function gserialized_overlaps_2d uses 4 comparisons to determine whether or not there is an overlap in the bounding box. That's
not usually all that useful except for adding selectivity, so you don't normally want it.

That means this isn't a performance question, && just doesn't do much. However what && does do can make use of a GIST index.

ST_Intersects

ST_Intersects from the docs,

  This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries.

The reason why is simple, only the bounding box uses the index. That means it'll do a && AND someting else. And you can see that with \dfS+ st_intersects

SELECT $1 && $2 AND _ST_Intersects($1,$2);

So the extra bit it does is call either geos_intersects or sfcgal_intersects intersects depending on your chosen back end. in the best case, that you get geos_intersects, you can see what that does here.

In essence, it is telling you if any point intersects without making any assumptions (other than floating point math).

Mixed SRIDs

As a last note, it maybe be worth noting that these two operations handle mixed SRIDs differnetly.

Your questions

  Question 1 is if && is significantly faster than ST_Intersects when using with ST_MakeEnvelope (a rectangular boundary), when finding points inside a rectangular bounding box.

Yes, it's faster -- significantly. It does less. It doesn't find "points" inside a rectangular bounding box unless one side is a simple point. Other than that, it finds bounding-box overlaps which are subject to false positives if all points reside outside of the bounding box.

  Question 2 is Also am I understanding correctly that when checking points inside a rectangular boundary && does exactly same thing as ST_Intersects?

No. It should be clear why now.


SELECT only the rows with a unique value on certain columns

I can't see your problem
Distinct produces exactly what you want
SELECT
DISTINCT
[HOME]
, [CURRENT]
, [EFFECTIVEDATE]
, [NOTTOEXCEEDDATE]
, [FLAG]
, [GRADE]
, [POSITION]
, [LASTPAYPERIODPAID]
FROM Table1

Result
HOME    CURRENT EFFECTIVEDATE   NOTTOEXCEEDDATE     FLAG        GRADE   POSITION    LASTPAYPERIODPAID
GEORGE  610124  630100          11/10/2019          11/6/2021   Y       14          ACCOUNTANT
GEORGE  610422  610422          (null)              (null)      N       15          SUPERVISOR ACCOUNTANT
GEORGE  610422  630100          11/10/2019           11/6/2021  Y       14          ACCOUNTANT
GEORGE  610422  630100          11/10/2019           11/6/2021  Y       15          SUPERVISOR ACCOUNTANT

George has two current numbers, that is why i don't get your result
http://sqlfiddle.com/#!18/f5920/1


"To many rows being processed at one. Reduce and try again." error in SQL Server Management Studio

To clarify on @McNets comment: The error isn't produced by SQL Server itself. Somebody created a trigger on the table, a trigger that fires on UPDATE. This trigger apparently does a check for number of rows and if above a certain threshold, it generates this error and does a rollback.
You should try to find who is responsible for the trigger and take it from there. Perhaps it is OK to do an exception to that trigger rule in this particular case? Etc.


Your algorithm is "Order(N^2)".  If you have a million rows, it will take about a trillion comparisions!  (Actually closer to N*N/2.)

I assume you want to run this again later?  Or you want to avoid inserting duplicates?  In either case, the following will speed things up greatly.

ALTER TABLE `comment` ADD COLUMN hash BINARY(20);
UPDATE `comment` SET hash = UNHEX(SHA1(body));
ALTER TABLE `comment` ADD UNIQUE(hash);  -- (see discussion below)

When INSERTing, include hash with a value of UNHEX(SHA1(body)).  If the hash is a dup, then body is extremely likely to be a dup, and the INSERT will suitably fail.

INDEX versus UNIQUE:

INDEX gives you a much faster way to do the dup-elimination:  check hash instead of body.
UNIQUE prevents future insertion of dups.

So, let's do it in two phases:

Add INDEX(hash) (not UNIQUE(hash))
Do the dedupping -- With an INDEX(hash), the DELETE is Order(N) -- a million tests, not a trillion.
Change to UNIQUE:  DROP INDEX + ADD UNIQUE. -- Now future INSERTs will balk; so add suitable code to deal with such.  Consider INSERT IGNORE.

All of that will take minutes, not hours.  And you won't have to do any of it again.

(With a newer version of MySQL, you could use GENERATED ALWAYS to avoid some of the code.)

One shot

To do the delete once and do it without modifying the table:

CREATE TABLE t (
        id INT NOT NULL,
        hash BINARY(20),
    PRIMARY KEY(id),
    INDEX(hash) ) ENGINE=InnoDB;

INSERT INTO t (id, hash)
    SELECT id, UNHEX(SHA1(body))
        FROM `comment`;

DELETE FROM `comment`
       JOIN  t AS t1  ON t1.id = comment.id
       JOIN  t AS t2  ON t2.hash = t1.hash
                     AND t2.id < t1.id;

DROP TABLE t;

Caveat:  I have not tested this code.  Recommend you test it separately, perhaps with a subset of the data.


If a rowstore index is stored in a b-tree data structure, what kind of data structure is used for a columnstore index?

We usually think of an index as a way of rapidly finding all references to a given piece of information. In a textbook given a topic we can find all pages that mention that topic. In a database given a column value we can find all rows that have that value.

Calling a columnstore an "index" in this sense is a bit of a misnomer. It is not intended to provide fast lookup for a specific column value's rows. Rather it is intended to quickly provide results of aggregates over large datasets. As such it is a data format in itself. The columnar data store, sometimes called the decompositional storage model (DSM), is well-known and venerable. Many proprietary and open-source DBMS offer it.

The basic idea of a column store is that values for a column are stored contiguously on disk. Then an aggregate on that column can efficiently pull that column, and only that column, from disk reducing the number of disk blocks that must be read. Further, since all the data values in a block are from the same domain compression on that block is likely to be very efficient, further reducing the number of blocks required. Compression can be much better than a generic ZIP - dictionaries, run-length and delta encodings can be used.

For various reasons it is better to break up rows into large groups before compressing and storing them. SQL Server calls these groups "row groups", which are made up of "column segments". Each row group holds just over one million rows. When the segments are constructed the server extracts some metadata such as min, max and (perhaps) sum for that column. This metadata is sometimes called zonemaps. At run time these zonemaps can be compared to the query predicates and, if the predicate does not lie between the segment's min and max values, processing can entirely avoid reading those blocks from disk. This is called segment elimination and is analogous to partition elimination. Moreover some queries can be answered directly from the zonemaps e.g. "select min(column) from table" can be answered by comparing the "min" segment summary values from the zonemaps.

I would note that B-Trees are not the only structure for indexing rowstores, it's just it is the only one available to database developers using SQL Server.


Which of these table designs is better for performance?

A billion records a year isn't much.

With partitioning (per Costtype maybe) and archiving it is manageable.

The number of data items to store is still 200k * 13 * N. As columns, you'll get less rows per page and it will take more space than as rows. You may gain if "CostType1" is not a fixed length datatype, but it's marginal.

"KISS" as they say


Deploying and running SSIS packages to SQL Server with protection level EncryptSensitiveWithPassword

You can try following:

With EncryptSensitiveWithUserKey - Deploy the project with the same ID through which the agent job will run (I always use SSIS Proxy account created). Also, change the Job owner to same ID or SysAdmin.
You can try to execute the package through T-SQL code (there are stored procedures for this).
Try the command line option in agent to execute the dtexec to run the package.


Netezza Equivalent to T-SQL ROWVERSION or PL/SQL ORA_ROWSCN

I'm not sure about the ROWVERSION behaviour, but ORA_ROWSCN I believe would still change if you were to update a row with completely identical values. In other words, the row contents would remain the same but its SCN would be different.

The only practically reliable way to detect the actual change in column values would be to calculate the row checksum or hash.

To answer your question though, consider using the createxid special column in Netezza: http://pic.dhe.ibm.com/infocenter/ntz/v7r0m3/topic/com.ibm.nz.dbu.doc/r_dbuser_data_types_ntz_internal.html

And again, a changed createxid value would not necessarily mean that any column values have changed.


How do I add a time dimension to this forecasting database without having to duplicate every data point @ each reforecast?

Snapshotting vs. Running Sums

Any approach to point-in-time reporting is effectively equivalent to snapshotting or calculating a running sum.  Your options are variations on:

Calculate a running sum from the beginning of time, or some periodicaly checkpointed position.  This requires you to record changes rather than totals.
Calculate periodic snapshots and do your historical statiscs based on the snapshots.
Hybrid running-sum with periodic snapshots to baseline positions and reduce the data set over which the running sum needs to be calculated.

This is essentially a speed for space tradeoff, and temporal databases are really just abstractions on a data structure that is semantically equivalent to one of the schemes described above.

If you want speed, calculate and persist periodic snapshots of your position.  This will use more space, but if your DBMS platform supports partitioning then the snapshots can be managed and disposed quite efficiently.  The snapshot date forms the partition key, so any queries that specify dates will give the optimiser enough information to ignore partitions that are not relevant to the query.

Frequent snapshots will use lots of disk space, so you trade off granularity against disk space usage.

If you want to save space then store changes and calculate running sums for your as-at positions.  You can also use periodic (though less frequent) snapshots in combination with the deltas for a period to optimise the query by starting with balance figures from the appropriate snapshot and adding the deltas to your as-at date.  This technique is commonly used with accounting systems such as Oracle Financials.

Hybrid approaches

Essentially you have a continuum from a pure running sum to a pure snapshot.  Snapshotting is faster to query, and can be used to speed up running sum calculations by allowing you to start the running sum calculation at the snapshot date.  A hybrid approach is more complex as you have to implement both mechanisms, but it does allow you to tune your speed/disk usage tradeoff to whatever is most appropriate to your application.

Some thoughts on what might be applicable when

It's far easier to retrofit snapshots to a base transactional model than to go the other way around.  However, some models - things where you have time-dependent state - only make sense when viewed as a snapshot that shows the state at a given point in time.  An example of this is aged debt reporting where you want to see the invoices with money that's been owing for more than (for example) 180 days.  If you want to retain a reconcilable record of aged debt positions then you may wish to snapshot this periodically.

In the first instance a transactional fact table with the base movements (deltas) gives you the raw data from which you can calculate your snapshotted positions.  It's best to have that available if at all possible, unless recording the data in that format is made infeasible by some limitation of the upstream data source.

I've done an aged debt reporting system and a claims reporting system based on snapshots before.  The aged debt report was an operational report run at the beginning of each week, and the requirement for the claims snapshot table was for a monthly report where the business wished to see counts and outstanding values of claims that were open at the time.

Some things (e.g. claim reserving) are done on a periodic basis, so (for example) ultimates and IBNR1 are only calculated on a monthly or quarterly basis and it only makes sense to report those on a periodic basis.  In this case, snapshotting the figures makes sense due to the nature of the underlying process that generates the data.

1Ultimates are insurance-speak for forecasts of the total claims expected on policies incepting in a given year.  IBNR (Incurred but not Reported) is an estimate of the figures on claims that have not occurred or been reported yet.


Dynamic pivot table filtering and performance

You can use HAVING to filter the columns you created:
select
  subscriber_id,
  MAX(case when field_id = '1' then value else 0 end) as email,
  MAX(case when field_id = '2' then value else 0 end) as first_name,
  MAX(case when field_id = '3' then value else 0 end) as last_name
from test_fields_table

group by subscriber_id
HAVING email LIKE '%com'
AND last_name LIKE 'M%'
limit 100;

See result


How can I write windowing query which sums a column to create discrete buckets?

I am not sure what type of performance you are looking for, but if CLR or external app is not an option, a cursor is all that is left. On my aged laptop I get through 1,000,000 rows in about 100 seconds using the following solution. The nice thing about it is that it scales linearly, so I would be looking at a little about 20 minutes to run through the entire thing. With a decent server you will be faster, but not an order of magnitude, so it would still take several minutes to complete this. If this is a one off process, you probably can afford the slowness. If you need to run this as a report or similar regularly, you might want to store the values in the same table un update them as new rows get added, e.g. in a trigger.

Anyway, here is the code:

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable;

CREATE TABLE dbo.MyTable(
 Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 v NUMERIC(5,3) DEFAULT ABS(CHECKSUM(NEWID())%100)/100.0
);

MERGE dbo.MyTable T
USING (SELECT TOP(1000000) 1 X FROM sys.system_internals_partition_columns A,sys.system_internals_partition_columns B,sys.system_internals_partition_columns C,sys.system_internals_partition_columns D)X
ON(1=0)
WHEN NOT MATCHED THEN
INSERT DEFAULT VALUES;

--SELECT * FROM dbo.MyTable

DECLARE @st DATETIME2 = SYSUTCDATETIME();
DECLARE cur CURSOR FAST_FORWARD FOR
  SELECT Id,v FROM dbo.MyTable
  ORDER BY Id;

DECLARE @id INT;
DECLARE @v NUMERIC(5,3);
DECLARE @running_total NUMERIC(6,3) = 0;
DECLARE @bucket INT = 1;

CREATE TABLE #t(
 id INT PRIMARY KEY CLUSTERED,
 v NUMERIC(5,3),
 bucket INT,
 running_total NUMERIC(6,3)
);

OPEN cur;
WHILE(1=1)
BEGIN
  FETCH NEXT FROM cur INTO @id,@v;
  IF(@@FETCH_STATUS <> 0) BREAK;
  IF(@running_total + @v > 1)
  BEGIN
    SET @running_total = 0;
    SET @bucket += 1;
  END;
  SET @running_total += @v;
  INSERT INTO #t(id,v,bucket,running_total)
  VALUES(@id,@v,@bucket, @running_total);
END;
CLOSE cur;
DEALLOCATE cur;
SELECT DATEDIFF(SECOND,@st,SYSUTCDATETIME());
SELECT * FROM #t;

GO
DROP TABLE #t;

It drops and recreates the table MyTable, fills it with 1000000 rows and then goes to work.

The cursor copies each row into a temp table while running the calculations. At the end the select returns the calculated results. You might be a little faster if you don't copy the data around but do an in-place update instead.

If you have an option to upgrade to SQL 2012 you can look at the new window-spool supported moving window aggregates, that should give you better performance.

On a side note, if you have an assembly installed with permission_set=safe, you can do more bad stuff to a server with standard T-SQL than with the assembly, so I would keep working on removing that barrier - You have a good use case here where CLR really would help you.


SQL Query with multiple conditions on date

You can use isnull to take care of the parameters when they are null.

Something like this should do what you want.

select T.*
from #TempTbl as T
where T.StartDtm <= isnull(@StopDtm, '99991231') and
      (T.StopDtm >= isnull(@StartDtm, '17530101') or T.StopDtm is null)


how long can your replica be offline when using repmgr with Barman?

If restore_command is set to restore from an archive source (in this case Barman), the replica will attempt to fetch any WAL not available on the primary (or upstream in the case of cascading replication) from that source. So in this case as long as Barman has the WALs for the period the replica was offline, it will catch up.
You'll need to check the Barman configuration to see how long it is configured to retain backups/WAL, which will limit how long a replica can be safely offline for. Execute barman show-server $SERVER (probably node1) to list the active configuration (might take a few seconds to run) and look for retention_policy and wal_retention_policy.

I do have restore_command='/usr/bin/barman-wal-restore barman node1 %f %p'
but I thought that was more for initial cloning or recovery.

If the initial cloning is from Barman, it basically rsyncs the latest full backup from the Barman server. The restore_command will then fetch any WAL not available on the primary/upstream from Barman, and it is left in place as a backup source of WAL for situations (like the case described here) where streaming replication is interrupted for whatever reason.


Understanding when to remove Order By or Sort operator from Query/plan

As per my understanding in above scenario we do not need order by as index have those columns already sorted

That understanding is incorrect, and that plan shows one reason why.  A parallel index scan doesn't output rows in index order, as each thread reads at a different location in the sort order.  You can't expect rows in any particular order without an ORDER BY clause.

And the index does not have the rows already sorted.  The ORDER BY is on (DemoID, CaptureDate), but the index is partitioned by CaptureDate.  So if the query crosses a partition bondary, the DemoID values will start over.

eg the rows in (DemoID, CaptureDate)-order:

(1,'20190912'),(2,'20190913'),(3,'20190912'),(4,'20190913')

may be stored over more than one partition:

partition N
--------------
(1,'20190912')
(3,'20190912')

partition N+1
--------------
(2,'20190913')
(4,'20190913')

So even if the plan used a single thread to scan the index, a downstream sort would be required.

  Plan does not have an expensive sort but stats got worst, why?

No. The stats got better.  The second query has the same 330 logical reads and only 78ms of CPU time vs 297ms of CPU time for the first query.  The difference in elapsed time is related to more parallelism and the physical IO, which is not an attribute of the query plan.  Rather it's dependent on the state of the page cache when you run the query.


In the site that you have cited, note the parentheses at the right of the attributes in the second part of the initial column:

There is no context in the picture, but the obvious interpretation of such parentheses is that they denote repeated attributes: in other words, for each course (for which there is only one tutor), there are multiple students. So the notation (actually not very clear) should denote a non-flat situation, in which the 5 attributes are repeated.

This is clearly not a relation (or a "table" in 1NF), and the first step in the normalization is to divide these data in two different tables, one for courses and one for students, and of course add to each row of the second table,  which should represent a student, a foreign key towards the course to relate that particular student to a particular course.

Related Q & A on Computer Science SE: Does 2NF require 1NF?


Enforcing option hint for a statement in a while loop is not possible

The option clause can only be used on standalone SELECT statements.

Not where the SELECT is being used as an expression.

The following examples don't allow you to specify a query hint either.

DECLARE @I INT = (SELECT 1 OPTION (RECOMPILE))

IF (1 = (SELECT 1 OPTION (RECOMPILE))
    PRINT '1';

So you will either need to

restructure your code so that the statement you are trying to apply a query hint to is not directly embedded inline in the WHILE (would require either repeating the statement or wrapping it in a non inline function)
set the trace flag on at the session level
Use a plan guide (potentially - I haven't tested this)

For the specific example in your question you don't need the SELECT at all.

DECLARE @Dummy VARCHAR(100) ='setting initial rowcount'

WHILE @@rowcount > 0
  BEGIN
      DELETE TOP (10000 ) FROM [AdventureWorks].dbo.DatabaseLog;
  END

or (as this will end up deleting all rows anyway).

TRUNCATE TABLE [AdventureWorks].dbo.DatabaseLog;


Database design - logging of same value when recorded in multiple tables

Another option is to use simple table inheritance for your events, with the event as the base type:

CarEvents              RentalEvents
=========              ============
ID <------------------ EventID
EventType         |    CustomerID
CarID             |    RentalDetail03
EventDate         |         ...
MilageReading     |    RentalDetailNN
GeneralDatail05   |
    ...           |
GeneralDatailNN   |    TransferEvents
                  |    ==============
                  `--- EventID
                  |    SourceOfficeID
                  |    DestinationOfficeID
                  |    TransferDetail04
                  |          ...
                  |    TransferDetailNN
                  |
                  |
                  |    MaintEvents
                  |    ==============
                  `--- EventID
                         ...

This means that queries about general properties (event date, car involved, etc.) only hit one table rather than needing to union the distinct sources, without duplicating the general details. Of course to report on a given event type you need to join in the relevant sub-table, and to report on multiple event types you have to join in multiple sub-tables.

Some databases have built in support for this form of inheritance which makes it behave a little more automatically (a bit like partitioning), meaning you have slightly less work to do as a developer, but this is essentially syntactic sugar and as you can see the basic structure is not at all rocket science.


First, just to be certain we're covering our basis, this piece of code:

EXP FILE = B.DMP OWNER(B)

won't work. That's not a properly formatted EXP command. But assuming that you're using a properly formatted command, all that a user really needs is the EXP_FULL_DATABASE role, and they should be able to export any object in the database:

SQL> create user captjackvane identified by x default tablespace users temporary tablespace temp;

User created.

SQL> grant create session to captjackvane;

Grant succeeded.

exp userid=captjackvane/x owner=rpt file=rpt.dmp log=rpt_dmp.log resumable=Y

This results in

EXP-00032: Non-DBAs may not export other users
EXP-00000: Export terminated unsuccessfully

But when I add the grant:

SQL> grant exp_full_database to captjackvane;

Grant succeeded.

We see that I can now do the export:

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user RPT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user RPT
About to export RPT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export RPT's tables via Conventional Path ...
. . exporting table               AD_HOC_FIELD_SET          2 rows exported

If you've granted this permission, then there are a few other things you may want to check. The first is the permissions on the directory you're trying to write your dump file to; your Oracle type user will need to be able to read/write in that directory.

Can you post the exact error message you're getting?


How memory limit for backup process is calculated?

You must have got the output after using trace flags in command something like dbcc traceon(3605, 3004, 3014, 3213, -1). If you read the How It Works: How does SQL Server Backup and Restore select transfer sizes on this TF it says

WARNING: This trace flags should be used under the guidance of Microsoft SQL Server support.  They are used in this post for discussion purposes only and may not be supported in future versions.

The TF is undocumented so is its output parameters. The memory limit is probably calculated internally via code to which a normal Joe do not have information or access. So I would suggest not to waste time on finding how it is calculated. You can focus on MAXTRANSFERSIZE and the BUFFERCOUNT and play around with it.


DB2 Audit Policy not producing EXECUTE data

A note in the manual explains why these statements do not make it into the audit log:

  The preparation of a statement is not considered part of the execution. Most authorization checks are performed at prepare time (for example, SELECT privilege). This means that statements that fail during prepare due to authorization errors do not generate EXECUTE events.

SQL0204N and SQL0206N, both of which complain about invalid object names, are raised during the statement parse phase, that is, before execution starts. Errors raised after execution starts, such as SQL0911N (lock timeout), will be audited.


SSRS on 2008 R2 and its Database on 2016

SSRS just needs a SQL Connection to a database that supports all of the features it requires. The connection type that it uses should be forward compatible to SQL 2016 (and even 2019).

I want to ask, why not upgrade SSRS as well though?


SQL Server 2019 Core - Always on Availability Group - Availability replica is disconnected

I just figured it out.

(You have to have the [Domain\sqlengine] added to the Logins for SSMS.)

In Management Studio
Click [Logins]

Perform the following (2) actions

  Action #1
  Double Click [Domain\sqlengine] Click on [Server Roles] (I
  have the following checked, I actually had them all checked for
  testing, and now these are the ones that are needed.)
  dbcreator
  public (default cannot uncheck this)
  serveradmin
  sysadmin

(This is what did the trick, this is what I was missing.)

  Action #2
  Click on [User Mapping]
  Put a check on [Master]

Click [OK]
Then Re-run the Always On Wizard

This is all it took.
For the last few days.
I have been banging my head against the wall to get this working.
And it was right here all along.

Hope this will help someone out.

Wayne


Why is SQL Server 2012 Recommending This Index Be Created?

Just to clarify, SSMS is not recommending that index, it is from the query engine/optimizer. Just to note as well a good MSDN article notes some limitations to the Missing Index Feature.

The fact that your current non-clustered index contains [ProcessNumber] and [Lineage] the query engine found simply scanning the clustered index of the table to be more optimal. It is suggesting an index strictly on ProcessNumber because the query is filtering on that column only. I believe if this is also the column that the clustered index is based on then your non-clustered indexe will be ignored by the query optimizer.

I would look into all the missing indexes the optimizer sees for this table and evaluate the current unused indexes as well.


Why are transactions running as distributed (DTCXact)?

Have you tried profiling a single session to see if/when its transaction is promoted to distributed? For example, using a server side trace using e.g. the DTCTransaction (and maybe TM: Promote Tran xxx) event classes. You can also monitor detailed DTC activity in Windows.

I would profile all the likely looking connection, execution, and transaction-related events for a single SPID while it did some representative work. Note: profiling is best done on a test system due to overheads, using a server-side traces or the Extended Events equivalent. Do not use the Profiler UI except on a local test instance if you really have no other option.

If I were unable to see the application source code or talk to the developers about the use of transactions, this is likely how I would begin to isolate the cause.

It may also be that the application, its data access layer, or client driver is set to promote transactions when a second local database is accessed or a second connection used in the same transaction. There might even be an explicit BEGIN DISTRIBUTED TRANSACTION. Perhaps TransactionScope (or similar) is being used in ADO.NET with multiple connections. See:

Using System.Transactions
System.Transactions Integration with SQL Server
Distributed Transactions (.NET Framework)

There are many possibilities, but tracing/profiling will at least give you some clues about where to focus your investigations.

My guess is that it's most likely to be an unintended side-effect of the way your application developers are using .NET connections and transactions, perhaps combined with the way IIS is configured.

It does seem likely the distributed transactions are associated with your "Transaction Mgr" class in some way. There doesn't have to be an explicit TransactionScope in your code, that's just one common vector.

Stepping through the application code (and data access components) while watching the effect on DTC and SQL Server may be the best way to determine exactly where, when, and why distributed transactions are being invoked or enlisted to.

Obsolete now the question has been edited, but other causes include:

Linked servers with remote proc transaction promotion set true. This can cause a local transaction to be promoted to a distributed transaction.
The legacy configuration setting remote trans proc in sys.configurations, when used with 'remote servers'. Also see the SET REMOTE_PROC_TRANSACTIONS session option.


Given that the data is changing very infrequently and there will therefore be very little overhead in maintaining the indexed views, an approach using indexed views could help performance.  As you likely noticed when trying to create an indexed view that uses PIVOT, there are a lot of restrictions on what syntactic constructs can be used in indexed views.

To get around this limitation, you can use a combination of aggregation (via SUM) and CASE statements.  However, you'll then run into another limitation, which is that a view that uses UNION cannot be materialized.  However, it is easy to make a separate view for the aggregated inputs and outputs in this case, and that approach is worth testing on your specific workload to see if it yields a performance benefit.  Below is a sample script that illustrates both approaches:

-- Set up test data with 3 records, and each record has 2 inputs and 3 outputs
CREATE TABLE dbo.records (Id INT IDENTITY(1,1) NOT NULL, DTS DATETIME NOT NULL)
GO
INSERT INTO dbo.records (DTS)
VALUES  (GETDATE()-2),
        (GETDATE()-1),
        (GETDATE())
GO
CREATE TABLE dbo.inputs (Id INT IDENTITY NOT NULL, recordId INT NOT NULL, inputId INT NOT NULL, value FLOAT NOT NULL)
GO
INSERT INTO dbo.inputs (recordId, inputId, value)
SELECT r.Id AS recordId, i.inputId, r.Id * i.inputId AS value
FROM dbo.records r
CROSS JOIN (VALUES (1), (2)) AS i(inputId)
GO
CREATE TABLE dbo.outputs (Id INT IDENTITY NOT NULL, recordId INT NOT NULL, outputId INT NOT NULL, value FLOAT NOT NULL)
GO
INSERT INTO dbo.outputs (recordId, outputId, value)
SELECT r.Id AS recordId, i.outputId, -1 * r.Id * i.outputId AS value
FROM dbo.records r
CROSS JOIN (VALUES (1), (2), (3)) AS i(outputId)
GO

-- Create the indexed view for inputs
CREATE VIEW dbo.recordInputs WITH SCHEMABINDING AS
SELECT r.Id AS recordId,
    r.DTS,
    -- A COUNT_BIG() is mandatory to create an indexed view
    COUNT_BIG(*) AS numInputs,
    -- Generate the columns input1, ..., inputM
    SUM(CASE WHEN i.inputId = 1 THEN i.value ELSE 0 END) AS Input1,
    SUM(CASE WHEN i.inputId = 2 THEN i.value ELSE 0 END)Input2
FROM dbo.records r
JOIN dbo.inputs i
    ON i.recordId = r.Id
GROUP BY r.Id, r.DTS
GO
CREATE UNIQUE CLUSTERED INDEX UQ_recordInputs ON dbo.recordInputs (recordId)
GO

-- Create the indexed view for outputs
CREATE VIEW dbo.recordOutputs WITH SCHEMABINDING AS
SELECT r.Id AS recordId,
    r.DTS,
    -- A COUNT_BIG() is mandatory to create an indexed view
    COUNT_BIG(*) AS numRows,
    -- Generate the columns output1, ..., outputM
    SUM(CASE WHEN o.outputId = 1 THEN o.value ELSE 0 END) AS Output1,
    SUM(CASE WHEN o.outputId = 2 THEN o.value ELSE 0 END) AS Output2,
    SUM(CASE WHEN o.outputId = 3 THEN o.value ELSE 0 END) AS Output3
FROM dbo.records r
JOIN dbo.outputs o
    ON o.recordId = r.Id
GROUP BY r.Id, r.DTS
GO
CREATE UNIQUE CLUSTERED INDEX UQ_recordOutputs ON dbo.recordOutputs (recordId)
GO

-- Create the overall view
CREATE VIEW dbo.recordInputsAndOutputs WITH SCHEMABINDING AS
SELECT i.recordId, i.DTS, i.input1, i.input2, o.output1, o.output2, o.output3
FROM dbo.recordInputs i WITH (NOEXPAND) /* In non-enterprise versions of SQL, you will likely need this hint to avoid expanding the indexed view */
JOIN dbo.recordOutputs o WITH (NOEXPAND)
    ON o.recordId = i.recordId
GO

-- Test the view (turn on "Include Actual Execution Plan" first in SSMS to confirm usage of the indexed views)
SELECT *
FROM dbo.recordInputsAndOutputs
GO

-- Cleanup
DROP VIEW dbo.recordInputsAndOutputs
DROP VIEW dbo.recordInputs
DROP VIEW dbo.recordOutputs
DROP TABLE dbo.inputs
DROP TABLE dbo.outputs
DROP TABLE dbo.records
GO

One caveat to point out is that these indexed views aren't really doing any aggregation.  We still have the same number of data elements as before, but they are simply pivoted into a smaller number of rows (and larger number of columns).  Even so, I have often observed significant performance gains from operating on a pivoted row set that contains fewer rows to represent the same data.  I'll defer to deeper experts on why this might be, but it is fairly intuitive given the row-based processing model where SQL Server needs to pull each row through each query plan operator, presumably incurring some overhead for each row while doing so (at least for row-mode execution, which covers everything except parallel columnstore plans).

One last note is that you will often get better and faster responses if you publish a snippet of SQL code that generates some basic test data in your schema (similar to the "set up test data" section of the script above) in addition to just showing the schema.  This makes it much easier for someone to help you out and lowers the possibility of mis-interpreting the question.


Moving away from CmdExec steps in job without losing logging detail (SQL Server 2008)

Set the output of the T-sql job step to output to a file. It's on the advanced tab of the job step properties.


SELECT MAX(id) AS id
                      FROM sw_sowing
                      WHERE status != 0
                      AND id_tenant = :id_tenant
                      AND date <= :dates
                      AND multiply != 1
                      AND id_product = 1

needs INDEX(id_tennant, id_product, dates, multiply, id)

GROUP BY id_production_unit_detail does not make sense since you are not doing SELECT id_production_unit_detail, MAX(ID) and then using id_production_unit_detail in some way.

Eh?  Doing this for each Sunday??  AND date <= :dates -- That means you are scanning more and more of the table.  And, if there are no entries in a week, returning the same data sometimes.  There are much better ways to get the data for a given week withouut scanning the rest of the data.  And it lends itself better to doing everything at once (instead of looping through Sundays).

I assume these are all the same type?

SELECT ..., sw_sowing.type, ...
    GROUP BY ..., sw_sowing.type
    HAVING type NOT IN('ER','PR')

If so, then it would be more efficient to move the test against type into WHERE instead of HAVING.

(There may be more to improve on.  Do these things, then come back for more abuse/advice.)


Convert an int column to bigint with the 4 bytes of padding available

Are you asking if the table heap size would be affected, then no the 4 extra bytes of padding would be used for the bigint, and the table would be unchanged since the following data type (timestamptz) uses 8 bytes and would be placed on the following "column" as it were. This should also hold true for indexes, I believe anything up to 8 bytes will occupy the same space per row.

Tested using postgresql 10:

insert into a (c,d) select generate_series(now(), now()+interval '100000 days', interval '1 day'), now();
INSERT 0 100001

select pg_relation_size('a');
 pg_relation_size
------------------
          5218304
(1 row)

select pg_total_relation_size('a');
 pg_total_relation_size
------------------------
                7479296
(1 row)

alter table a alter column b type bigint;

select pg_relation_size('a');
 pg_relation_size
------------------
          5218304
(1 row)

select pg_total_relation_size('a');
 pg_total_relation_size
------------------------
                7479296
(1 row)

Were you to change your id column to smallint, (smallint primary key on table can only have a maximum of 65535 rows, deleting rows would lead to a different issue...) the data size would likewise still not be affected since the following timestamp column could still not be squeezed next to it.


The UNION command should be your friend: http://dev.mysql.com/doc/refman/5.1/en/union.html

With UNION you can combine several SELECT results into one result. All selects must have the same field structure. In the MySQL docu there is a good example for your problem:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

You create a select for each of your tables with all filter and sorting you need and combine them with UNION to one select. The result of it can be stored by INSERT INTO ... SELECT ... in an archive table. You can also store this query in a MySQL view to have live data of all three tables with one select - Depending on the execution time / indexes etc.


SELECT max value for each group of values order by grouped by MYSQL 8.x

On MySQL 8.0 one solution is using ROW_NUMBER function in this way:
Notice I've used Grp instead of Group, because it's a reserved word.
SELECT
  Person,
  Grp,
  Age
FROM
  (SELECT
    Person, Grp, Age,
    ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY Age DESC) Rn
  FROM
    Ppl) a
WHERE
  Rn = 1
ORDER BY
  Age DESC;

Person | Grp | Age
:----- | --: | --:
Lalo   |   3 |  50
Shawn  |   1 |  42
Laura  |   2 |  39

There is no way to get Julia instead of Lalo, unless you add another column to the ORDER BY clause.
db<>fiddle here


The common way is to use a staging table (the table you called "data_import" which conventionally would be called "data_stg" or similar variation).
Preferably, no "updating" would be involved (maybe it was just a poor choice of words) but only a single insert into ... select ....


How do I verify the uninstall of Grid Control and WebLogic?

Be really, really careful when mucking around in /etc/rc.d/; this is where your startup/shutdown programs live, so removing the wrong file will put you in to a world of hurt. (Unfortunately, I can't tell you which file is the one Oracle tends to use.)

Second, Oracle's uninstall instructions always leave a lot to be desired, as does the actual process, which leaves just about everything hanging around on the file system even when it isn't necessary anymore.

For removing Grid, IIRC (that is a big IF), I think you're in the clear -- pretty much exactly what I would have done. And, as to weblogic, same deal -- I'd have run the uninstall.sh and see what happens. I suppose you could nuke the entire directory at this point so that there'd be no chance of an old file coming back to bite you, so that'd be just about the only other thing I'd do.

Good luck with the re-install...


Delete all rows within transaction: what would happen to other transactions?

The comment by a_horse_with_no_name is actually the answer, but since it was not posted as an answer, I'm not able to mark it so. I've completely forgotten about transaction isolation levels, I've asked the question in VoltDB forums and it appears that as long as make sure that stored procedures are local to nodes (sorry, this is a bit of a VoltDB terminology), that is, they don't process data across partitioned tables, this should work.


Issue with accented characters in SQLiteStudio

> SELECT hex(CompanyName) FROM Customers WHERE CustomerID = 'BOLID';
42F36C69646F20436F6D696461732070726570617261646173

This is not valid UTF-8.

That database was not created correctly.

If you know what the characters are supposed to be, you could manually fix them:

UPDATE Customers SET CompanyName = replace(CompanyName, x'F3', 'ó');

(You'd have to repeat this for all columns and all wrong characters.)


MySQL tables do not show up in the phpMyAdmin and MySQLWorkbench

The account that you are using to log into PHPmyAdmin/Workbench may not have permissions on the required tables. Do you see the required tables when you use the same login in the MySQL monitor console? Try logging in with a higher privileged account and viewing again.


How do I full text search on a datetime column?

FULLTEXT only applies to string datatypes, such as VARCHAR and TEXT.  After all, it is looking for "words".

The charset/collation of a table is only the default for newly added columns.

You can ALTER TABLE ... CONVERT TO ... to change all the columns in a table.  But before doing so, tell us why you are doing it and whether there is already non-English text anywhere in table.  Using that ALTER may just make things worse.

What, in particular, are you searching for in the DATETIME?  It may be possible to change to a 'range' query that may run efficiently with an index.  Let's see the attempted query and SHOW CREATE TABLE.

More

WHERE created = '2018-01-11 20:49:01'

will check for a specific second

or

WHERE created >= '2018-01-11'
  AND created  < '2018-01-11' + INTERVAL 1 DAY

will check for any created in a specific day.

Etc.

Either of these examples may use an INDEX (not FULLTEXT) containing created.  I say "may" because there could be other issues.  Please provide the SELECT and SHOW CREATE TABLE.

(The API, such as mysqli, is not relevant to the question.)

Still More

Looking at this formulation...

SELECT  symbol,created
    FROM  products
    WHERE  symbol='".$ccc['symbol']."'
      AND  created LIKE '%".$date_format."%'
    ORDER BY  id DESC
    limit  0,48

I see that INDEX(symbol) is likely to be helpful, but nothing having to do with created is useful.  This is because of the leading wildcard on the LIKE, and because an index won't be used with a DATETIME and LIKE.  The way to get performance via an index is as discussed above (using a range).


How to create and fill multiple session temp tables with dynamic SQL?

Creating temp tables dynamically means you won't be able to access them in your session as you've discovered because they're scoped to a session and the dynamic execution is not actually in your session scope.

You can get around this by creating global temporary tables (##tablename instead of #tablename) instead which are accessible across open sessions.

If you have multiple sessions running the same query, make sure you dynamically assign a name that will be unique to your session, for example:

DECLARE @TableName NVARCHAR(255)

SELECT @TableName = '##MyTempTable_' + CAST(@@SPID AS NVARCHAR(5))

--CREATE statement for #temp table
SET @dynamicCreateStmt = '< some combination of string constants and results of
STRING_AGG __ Use @TableName here to define the table name for your session only>'

This should allow you to dynamically create and use your temp table but interact with it in isolation to your scope, even if running multiple instances of this query concurrently.


Query to extract all SQL database roles with INSERT and/or UPDATE privileges

Here is a starting point.  The first query will give you a list of roles with either INSERT or UPDATE permission.  From there you will need to look at the permissions being at the DATABASE, SCHEMA, or OBJECT level and decide if they cover the objects you are interested in.  Check the state_desc column to make sure it is GRANT (it probably will be) as DENYs are obviously the opposite of what you want.

The second query will tell you any role that is a member of the db_datawriter role.  These roles will also have INSERT and UPDATE permissions.

SELECT dprin.name, dperm.*
FROM sys.database_permissions dperm
JOIN sys.database_principals dprin
    ON dperm.grantor_principal_id = dprin.principal_id
WHERE permission_name IN ('INSERT','UPDATE')
  AND dprin.type = 'R'

SELECT drole.name as role_name, dprin.name as role_member
FROM sys.database_role_members rmem
JOIN sys.database_principals drole
    ON drole.principal_id = rmem.role_principal_id
JOIN sys.database_principals dprin
    ON dprin.principal_id = rmem.member_principal_id
WHERE drole.name = 'db_datawriter'
  AND dprin.type = 'R'


Table partitioning in MYSQL Master Slave

The manual does not list a problem, but...

Statement Based Replication should not have any problem since the Slave simply re-executes the DML statement without knowing or caring (up front) about whether the table is partitioned.

Some rare statements are very likely to break replication:

SELECT ... PARTITION pnnn -- 5.6.2
ALTER TABLE -- to change PARTITIONs

Please comment on bug report if you find more issues.


Edit:

As commented by user @Rick James, it is always better to try to improve the indexes first before using MySQL Index Hints. Further description for finding the matching INDEX can be found in his excellent cookbook for building the best INDEX for a given SELECT in MySQL. Also please do not add INDEX(a) if you already have INDEX(a,b).

See his answer for a better solution.

Original answer:

Native SQL queries

You can use the MySQL Index Hints to show the optimizer how to choose the indexes.

Queries created by JPA

You can use subqueries to use the preferred index first and to reduce the number of lines for the main query.

Example:

SELECT e.id, e.username, e.agent, e.appInstanceId, e.endTime, e.eventTime, e.lastModified
FROM Event e
WHERE e.username = 'joebloggs'
AND e.appInstanceId <> 'ad69469a-d73f-4b33-a1c5-bde03c765d89'
AND EXISTS(SELECT esub FROM Event esub WHERE esub = e AND esub.lastModified > '2018-08-20 20:14:51.394')
ORDER BY e.lastModified


How to List SYNONYMS that no longer point to a valid object?

You can query the ALL_OBJECTS view for object_type = 'SYNONYM' and status != 'VALID'.
However all_objects status is set based on the status of the object that it is referring to. Synonyms, which aren't automatically invalidated if the underlying table disappears. So the STATUS column of ALL_OBJECTS is not reliable to use.

Try using this query:

SELECT obj.owner, obj.object_type, syn.synonym_name, tab.table_name
    FROM objects_to_update obj
        INNER JOIN all_synonyms syn
            ON obj.object_name = syn.synonym_name
        INNER JOIN all_tables tab
            ON  tab.table_name = syn.table_name
            AND tab.owner = syn.table_owner
    WHERE obj.object_type = 'SYNONYM' ;


SQL Server sqlcmd setvar depending of selected database

You cannot mix sqlcmd mode commands with T-SQL conditional logic.  There is a way to do this, but as to whether it is advisable is another matter.

This technique involves redirecting stdout, writing out to a file and executing that file all under sqlcmd mode to get the effect you require:

:out d:\temp\temp.sql
go

declare @x varchar(5) = 'dev'

if @x = 'dev'
    print ':setvar testVar dev'
else if @x = 'prod'
    print ':setvar testVar prod'
else raiserror( 'Unknown value (%s) for sqlcmd variable testVar.', 16, 1, @x )
go
:out stdout
go

!!REM Read the file to execute the setvar statement
:r d:\temp\temp.sql
go

select '$(testVar)' env

print 'it looks like this is the $(testvar) environment after all'
go

Obviously the process running this will require access to a scratch directory and file, and there are associated permissions (and risks) with this method.  The placement of GOs is important when redirecting output.

As regards usage, I would not particularly advise it.  We experimented with this (hence I know this technique) but moved away from it in favour of either database projects (which handle all this for you) or re-runnable (idempotent) scripts.  Rather than have a load of conditional logic in parent scripts, each object script was built to be re-runnable using 'if not exists' checks, for example:

if not exists ( the table )
  create the table

if not exists ( a column )
  add the column

Then it didn't matter if we were running in dev or prod, or how many times we ran the script.  These can get a bit unwieldy after a time.


That will not work like that. test_table is a table of test_row objects, not 2 columns, so you need to create test_row objects from the 2 columns, like below:

create or replace FUNCTION test_function
(
  p_year_start_in integer,
     p_year_end_in integer
) RETURN test_table AS

      v_ret test_table;
      p_start date;
      p_end date;

BEGIN
    select test_row(td.date_id, td.day)
    bulk collect into v_ret
    from T_D_DATES td
    where date_id between p_year_start_in and p_year_end_in;

  RETURN v_ret;
END test_function;
/


What is a deterministic method for evaluating a sensible buffer pool size?

As you already know there is No general formula to calculate max server memory you can do some quick maths and reach to a value but still you would need help of Perfmon counters at last to monitor memory usage and change accordingly. I know below general formula and I use it as well. I learned this formula from This Link

For SQL Server 2005 to 2008 R2

Please note from SQL Server 2005 to 2008 R2 max server memory only controls buffer pool. So max server memory configuration is bit tedious here and involves few calculations

Leave 2 G memory straight away for Windows OS.
Of course system would have antivirus running. Please leave 1.5G for Antivirus. Please note Mcafee and SQL Server do not go hand in hand so make sure you leave enough for it.
You can also check perfmon counter Perfmon Process-> Private bytes and Working Set to monitor memory usage by AV and other small applications running on SQL Server box

Consider the memory requirements of the drivers/firmwares.You have to derive it based on memory requirements by drivers installed on the system. RAMMAP tool can help
Consider the NonbPool (aka MTL or MTR) memory requirements by SQL Server.

select  sum(multi_pages_kb)/1024 as multi_pages_mb from  sys.dm_os_memory_clerks

+ Max worker threads * 2MB

+ Memory for direct Windows allocations approximately 0 to 300 MB in most of the cases but you may have to increase it if there are many 3 party components loaded in SQL Server process (Including linked server dll’s, 3rd party backup dll’s etc.)

+ If you are using CLR extensively add some additional memory for CLR.
Consider the memory requirement by jobs (Including replication agents, Log shipping etc. )  and packages that will run on the server. It can very from MB's to GB's according to number of jobs running. For medium sized server you can take it as 250 MB
Make sure there is good enough free space for operating system.

Approximately (100 MB for each GB till 4G) + (50 MB for each additional GB  till 12GB) + (25 MB for each additional GB till your RAM size)
Other memory requirements.

If you have any other memory requirement specific to your environment.

Max server memory=  Total physical memory  – (1+2+3+4+5+6+7)

I have not included memory configuration for SSIS.SSRS,SSAS you would also need to subtract memory required by these services from total physical server memory.

After you have configured above you need to monitor following counters

  SQLServer:Buffer Manager--Page Life Expectancy(PLE):
  SQLServer:Buffer Manager--CheckpointPages/sec:
  SQLServer:Memory Manager--Memory Grants Pending:
  SQLServer:memory Manager--Target Server Memory:
  SQLServer:memory Manager--Total Server memory

For SQL Server 2012/2014.

From SQL Server 2012 onwards setting up max server memory has become easy. Because now max server memory almost accounts for all memory consumption. Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

You can allocate 75-80% to SQL Server and then use perfmon counters to monitor memory usage. In SQL Server 2012 few perfmon counters have been deprecated. Buffer manager counter is deprecated you must use memory manager counter

  SQL Server: Memory Manager-- Target Server Memory (KB)
  SQL Server: Memory Manager--Total Server Memory (KB)
  SQL Server: Memory Manager- Free Memory (KB)
  SQL Server: Memory Manager--Database Cache Memory (KB)

On value of PLE, I have used formula by Joanthan and fortunately it has worked for me.


Empty strings in VARCHAR(MAX) consuming space which cannot be recovered

Is it possible the column was populated and it is has since been cleared out, or the data type was recently changed to varchar(max)? Have you tried rebuilding the clustered index (which will touch every row, unlike some ALTERs)?

EDIT

Since rebuilding the clustered index didn't help, I'm at a loss, and since the size of the existing data is so small, I recommend just creating a new version of the table and moving the data over. You can do this by explicitly re-creating the table and then copying the data, or you can use SELECT INTO to minimize the steps.

SELECT ... other cols ...,
    varchar_max_col = CONVERT(VARCHAR(MAX), NULL)
    INTO dbo.newtable FROM dbo.oldtable;
CREATE CLUSTERED INDEX ... ON dbo.newtable( ...cols... );
CREATE INDEX ... ON dbo.newtable( ...cols... );
...
GO
BEGIN TRANSACTION;
EXEC sp_rename 'dbo.oldtable', 'oldtable_backup', OBJECT;
EXEC sp_rename 'dbo.newtable', 'oldtable', OBJECT;
COMMIT TRANSACTION;


Why does mysql binlog use different isolation level?

It seems like there are no concurrent threads during replication or data recovery process, so a lower isolation level helps us to avoid unnecessary locks and speed up the process.


SQL SELECT query takes too long after updating the version

Have you tried the query without joining against a subselect?

SELECT
  pd.meterclass, pd.meterscore, pd.rt_url, pd.imdbId, pd.title, pd.adult,
  pd.backdrop,pd.budget, pd.homepage, pd.plot, pd.poster, pd.release_date,
  pd.revenue, pd.runtime, pd.language, pd.tagline, pd.status,
  pd.imdbRatings, pd.imdbVotes, mg.genre_id, cst.cast_id,
  cst.character_name, cst.name,
  cst.profile_path, crw.job, crw.name as crew_name,
  crw`enter code here`.profile_path as crew_profile_path, crw.id as crew_id,
  crw.credit_id as crew_credit_id
FROM movie_data_primary pd
INNER JOIN movie_genre mg ON pd.cinestopId = mg.cinestopId
INNER JOIN movie_cast cst ON cst.cinestopId  = pd.cinestopId
INNER JOIN movie_crew crw ON crw.cinestopId = cst.cinestopId;
WHERE pd.cinestopId="8QRJTD"