Get SSRS Datasources from ReportServer

  I would also like to include the 'Connection String' at least

  How can I display Data Source details with a SQL query?

TSQL for getting the Connection String of SSRS Data sources

-- Transact-SQL script to get connection string of all SSRS Shared Datasources.

/*
Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?
With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources,
to document the usage or to search for a specific server/database.

Please remark: Querying the ReportServer database directly is not a supported way.
Works with SSRS 2005 and higher version ReportServer databases.
Requieres select rights on the "Catalog" table in ReportServer database.
*/

-- Connection strings of all SSRS Shared Datasources
;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;

Here's a query to add the data source name as well using your TSQL:

USE [ReportServer]
GO

SELECT CATALOG.NAME
    ,CATALOG.[Path]
    ,DataSource.NAME datasource
    ,CATALOG.[Description]
    ,Created.UserName AS CreatedByUser
    ,CATALOG.[CreationDate]
    ,Modified.UserName AS ModifiedByUser
    ,CATALOG.[ModifiedDate]
FROM [dbo].[Catalog]
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Created ON CATALOG.CreatedByID = Created.UserID
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID
JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID
JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID
WHERE CATALOG.[Type] = 2
ORDER BY [Path]
    ,NAME


Your specification does raise some questions, but should be easily possible.

Since % is a wildcard character, and * is not, then you might want to use % as your marker.

+----+--------------+----------------------+
| id |  role_code   |      permission      |
+----+--------------+----------------------+
|  1 | ADMIN        | app.%                |
|  2 | LOC_ADMIN    | app.location.%       |
|  3 | LOC_READER   | app.location.read    |
|  4 | LOC_APPROVER | app.location.approve |
|  2 | EMP_ADMIN    | app.employee.%       |
|  3 | EMP_READER   | app.employee.read    |
|  4 | EMP_APPROVER | app.employee.approve |
+----+--------------+----------------------+

If so, then you could run code something like this:

SELECT a.role_code, b.permission
FROM permissionstable a
  JOIN permissionstable b on a.permission LIKE b.permission
       and RIGHT(b.permission,1) <> '%'
  ORDER BY b.permission

This does depend on my understanding of the permission strings.


Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?

You cannot trust the EXPLAIN plan's row count. Why?

Each time Query Optimization takes place where it involves an InnoDB table, the InnoDB Storage Engine does an approximation of the BTREE entries for the keys. They approximations change from query to query.

Simply run SHOW INDEXES FROM guage_data; multiple times and you will get a different set of row counts each time. You could set innodb_stats_on_metadata to 0:

SET GLOBAL innodb_stats_on_metadata = 0;

This will cause SHOW INDEXES FROM guage_data; to give the same number over and over again. This is still self-defeating because the row count would still be approximated anyway. You would have to disable the metadata stats and run ANALYZE TABLE. Again, this is self-defeating.

With innodb_stats_on_metadata on, approximations are always done. I wrote about this back on June 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

In light of all this, you are better off doing SELECT id FROM guage_data if id is the PRIMARY KEY. However, notice in your EXPLAIN plan that MySQL choose to traverse the gauge_data_FI_1 index. This makes all the sense in the world if this table is InnoDB. Why? Every non-unique index stores the rowid of the PRIMARY KEY anyway.


Your existing query is close to something that you could use but you can get the result easily by making a few changes.  By altering your query to use the APPLY operator and implementing CROSS APPLY.  This will return the row that meets your requirements.  Here's a version that you could use:

SELECT
  u.Username,
  u.UserType,
  u.Points,
  lv.Level
FROM Users u
CROSS APPLY
(
  SELECT TOP 1 Level
  FROM Levels l
  WHERE u.UserType = l.UserType
     and l.MinPoints < u.Points
  ORDER BY l.MinPoints desc
) lv;

Here's a SQL Fiddle with a demo.  This produces a result:

| Username | UserType | Points |  Level |
|----------|----------|--------|--------|
|     John |        A |    250 | Silver |
|     Mary |        A |    150 | Bronze |
|     Anna |        B |    600 | Bronze |


Does "alter table … set data type" functionally differ from "alter table … type"?

Keywords in square brackets in the synopsis of SQL commands in the manual are optional. And in this case just noise. Add them or leave them, no functional difference. (The verbose form SET DATA TYPE conforms to standard SQL.)

bigint occupies 8 bytes, integer occupies 4 bytes. A table rewrite is inevitable. The manual:

  As an exception, when changing the type of an existing column, if the
  USING clause does not change the column contents and the old type is
  either binary coercible to the new type or an unconstrained domain
  over the new type, a table rewrite is not needed; but any indexes on
  the affected columns must still be rebuilt.

Bold emphasis mine. Those are the "appropriate cases" that don't trigger a table rewrite since Postgres 9.1.


From PostgreSQL to SQL Server (No User/Pass)

I think currently you are not able to log into the postgresql database? If this is the case you can get access of the database by changing the access authorization. Please open  pg_hba.conf and change the following line:

local   all             postgres                                md5

to:

local   all             postgres                                peer

or

local   all             postgres                                trust

Now restart the postgresql service. You should now be able to connect to the database by typing: psql

You can get list of databases by typing: psql -l


Finding differences between two tables takes too long

Create the result table, my_diff, without index, and when it is
populated, add the desired index.
Make sure you have an index on (name, type) in both tables. i.e. a composite index
It is not a good idea to have primary key on all fields. This is especially true if you are using InnoDB engine. Instead, alter the table to add an integer auto increment field as primary key, and if needed, add a composite unique index on the fields you want to be unique.

From your explain result, it looks like you don't have the index on (name, type), so for each row in AA, it will do a full  scan to BB. This is one enough reason to make the query slow.


Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

If you're going to use a GUID key, simply use a clustered index with NEWSEQENTIALID() and move on with your application.  The alternative designs are simply not predictably and significantly better when you consider the extra data structures and complexity they introduce.  So while careful testing of your actual application might reveal a superior alternative design, just start with a simple clustered index on a sequentially-generated GUID.
For the gory details on why it doesn't really matter that the GUIDs are generated in the "middle" of the sort order, and the insertion point changes see: Good Page Splits and Sequential GUID Key Generation.


How to filter specific entries from a MySql table

This is what you need.

SELECT DISTINCT `original_results`.`Reg.No`
FROM  `results` AS `original_results`
WHERE NOT EXISTS (
    SELECT DISTINCT `helper_results`.`Reg.No`
    FROM `results` AS `helper_results`
    WHERE `helper_results`.`Reg.No` = `original_results`.`Reg.No` AND `helper_results`.`Pass/Fail` = 'pass'
)


Insert bulk Parent and Children rows using SQL Query

Once you have normalised you need 2 queries, one to load parents then one to load children (you can only insert into 1 table at a time using sql)

It sounds like you do this ofter, so why not create a file with the sql commands that you use, and each time you do this task open the file up in your databases sql editor window and run (may-be first modifying) these commands. If it always the same sort of task, you could write a script that connects to the database and runs the sql statments you need - then it is a one click or command task.


An interesting way of achieving this is using a psql variable to store the whole function body, like

\set body '$$SELECT 1$$'

CREATE FUNCTION bla() RETURNS integer LANGUAGE SQL AS :c;

SELECT bla();
 bla
─────
   1

Alternatively, you can pass the whole definition into the variable, and then run it:

\set function 'CREATE FUNCTION bla() RETURNS integer LANGUAGE SQL AS $$SELECT 1;$$;'

:function

SELECT bla();
 bla
─────
   1

So, the question is how to put the body into a suitable variable.

From psql 9.3 above, there is the \gset command, that comes to the rescue.  We build a query that produces the function body as an output, then assign it to a psql variable, and use it like above (with format()):

SELECT format('CREATE OR REPLACE FUNCTION %1$s.getLast()
    RETURNS text AS $$
    SELECT my_col FROM %1$s.my_table ORDER BY id DESC LIMIT 1;
    $$ LANGUAGE sql STABLE;', 'test') AS function;

\gset

:function

And done.


UTC date in DMV dm_broker_queue_monitors

To get local time, your best bet is to use the CLR, if that is available to you.  Matt Whitfield and Slawomir Brys have sample code you could use to convert which can handle Daylight Savings Time.

Regarding why the DMV is in UTC rather than local time, Service Broker can work across instances, so having service broker-related functionality all in UTC makes sense--that way, there isn't a problem with date synchronization between your east coast server and Tokyo branch.  That'd be my best guess as to why that particular DMV is in UTC whereas a number of others (e.g., dm_exec_sessions) are in local time.


How do I load them as varbinary(MAX) format into SQL Database?

Yes, use OPENROWSET with BULK. You need a format file though.

Assuming you want to attach blobs to existing records, something like:

INSERT SomeTable (id, blob)
select
    X.SomeID, B.Blob
from
    SomeKeyTable X
    JOIN
    OPENROWSET (
             BULK 'c:\myfile.txt',
             FORMATFILE = 'c:\myfileformat.txt'
    ) B ON X.AKey  = B.AKey


Differences between .out and .sql in pg_dump PostgreSQL

Actually in these examples the used extensions don't really matter: both exports are plain text (i.e SQL) scripts. You decide which extension to use: if you export in plain SQL it's better to use .sql extension but you can choose another one.

In general you decide which extension you use. Neither psql nor pg_restore have a mandatory extension for the export file. If you use another output file format (-Fc or -Ft) for pg_dump it's better to use another extension than .sql to remember which export format has been used.


Convert MyISAM to InnoDB in PXC master master

My comments on converting from MyISAM to InnoDB:  http://mysql.rjweb.org/doc.php/myisam2innodb

My comments on Galera (PXC):  http://mysql.rjweb.org/doc.php/galera

ALTER, on Galera/PXC, comes in two flavors;

⚈  Rolling Schema Upgrade = manually do one node (offline) at a time
⚈  Total Order Isolation = Galera synchronizes so that it is done at same time
     (in the replication sequence) on all nodes.

Further discussion (in addition to the manual, and my doc):  http://www.codership.com/wiki/doku.php?id=rolling_schema_upgrade


It depends™
Let's go through this step by step. We'll create a table and all relevant indexes and then populate our table with some data.
Create Table Q275204
USE [StackExchange]
GO

/****** Object:  Table [dbo].[Q275204]    Script Date: 10.09.2020 07:54:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Q275204](
    [A] [nchar](1) NULL,
    [B] [nchar](1) NULL,
    [C] [nchar](1) NULL,
    [D] [nchar](1) NULL,
    [E] [nchar](1) NULL,
    [F] [nchar](1) NULL
) ON [PRIMARY]

GO

Now we have an empty table. Let's add the indexes.
Create Index CPIX_Q275204_A_hot2use_20200910
This will be a clustered primary key index on the table Q275204 containing the column A.
USE [StackExchange]
GO

/****** Object:  Index [CPIX_Q275204_A_hot2use_20200910]    Script Date: 10.09.2020 07:56:44 ******/
CREATE CLUSTERED INDEX [CPIX_Q275204_A_hot2use_20200910] ON [dbo].[Q275204]
(
    [A] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Create Index NIX_Q275204_BC_hot2use_20200910
Next index up is the non-clustered index on table Q275204 containing the columns B and C.
USE [StackExchange]
GO

/****** Object:  Index [NIX_Q275204_BC_hot2use_20200910]    Script Date: 10.09.2020 07:58:40 ******/
CREATE NONCLUSTERED INDEX [NIX_Q275204_BC_hot2use_20200910] ON [dbo].[Q275204]
(
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Create Index NIX_Q275204_B_hot2use_20200910
USE [StackExchange]
GO

/****** Object:  Index [NIX_Q275204_B_hot2use_20200910]    Script Date: 10.09.2020 08:00:09 ******/
CREATE NONCLUSTERED INDEX [NIX_Q275204_B_hot2use_20200910] ON [dbo].[Q275204]
(
    [B] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

First Query Without Data
We now have an empty database without any data. What index will be used for your query? Will an index be used?
SELECT A, B, C, D, E FROM dbo.Q275204 WHERE B = 'X'

As it turns out, the Query Engine selects the Clustered Index. This is because the Clustered Index is the actual data and because of that, the fastest way to retrieve the data is to just scan the clustered index:

The query plan can be viewed here online (Brent Ozar's Paste The Plan)
Add Data And Query Table For 2nd Time
Let's add some data to the table to change things:
USE [StackExchange]
GO
INSERT INTO [dbo].[Q275204]
  ([A], [B], [C], [D], [E], [F])
VALUES
  ('X', 'X', 'X', 'X', 'X', 'X')
GO 10000

...and query the table again
SELECT A, B, C, D, E FROM dbo.Q275204 WHERE B = 'X'

...to get the following execution plan:

And online (Brent Ozar's Paste The Plan)
Differences
This time the Query Engine has decided to use clustered index scan again, but is suggesting that we should create a new index on column B and to include the columns A, C, D and E.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Q275204] ([B])
INCLUDE ([A],[C],[D],[E])
GO

The query optimizer hasn't used the existing index NIX_Q275204_BC_hot2use_20200910 (columns B and C) nor has it used the index NIX_Q275204_B_hot2use_20200910 (column B).
Musings
At this point you could be misled to believe that deleting all the indexes would be the best option. I mean, hey, they aren't being used and only taking up space.
That assumption could be wrong. The indexes aren't being used yet, because the distribution of the current data (all data are X) doesn't require the query optimizer to consider using other indexes other than the clustered index (with the scan option). The query optimizer knows (because of the statistics of the index) that the data in the table are all X and so scanning the clusterd index to retrieve all the data is still the fastest option available.
Statistics?
Yes. When you create an index the database engine will create statistics based on the distribution of the data in the table/index. The statistics assist the query optimizer in deciding which indexes to use when it is building the query execution plan of a statement.
Here are the indexed and statistics of our current table:

The data or histogram of the distribution of the data in an index can look like this:
Statistics for INDEX 'NIX_Q275204_B_hot2use_20200910'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NIX_Q275204_B_hot2use_20200910  Sep 10 2020  8:59AM             10000                           10000                           1                               0                               4                               YES                                                             10000

All Density                     Average Length                  Columns
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                               2                               B
1                               4                               B, A

Histogram Steps
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
X                               0                               10000                           0                               1

The statistics provide information regarding the distribution of the data in the index (and the table). Based on this information the query optimizer decides on which (existing) plan it will create (use).
Add More Data And Query Table For 3rd Time
We'll add another 20'000 records of Ys and Zs and see what happens:
USE [StackExchange]
GO

INSERT INTO [dbo].[Q275204]
  ([A], [B], [C], [D], [E], [F])
VALUES
  ('Y', 'Y', 'Y', 'Y', 'Y', 'Y'),
  ('Z', 'Z', 'Z', 'Z', 'Z', 'Z')

GO 10000

The query is the same so let's go straight to the query plan.

And online (Paste The Plan)
(No) Differences
The query plan is the same and the missing index is still being suggested.
Musings
Let's go and have a look what Microsoft has to say about statistics and especially about density. (Have a look at the statistics for non-clustered index after the table contains 30'000 records)

Density is information about the number of duplicates in a given column or combination of columns and it is calculated as 1/(number of distinct values). The query optimizer uses densities to enhance cardinality estimates for queries that return multiple columns from the same table or indexed view. As density decreases, selectivity of a value increases. For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). An index on the VIN is more selective than an index on the manufacturer, because VIN has lower density than manufacturer.

So as the density of an column goes down the selectivity increases and the probability that the index will be used in a SELECT statement is higher.
Let's go and add more data and see if the index will be used at some point...
Add Lots More Data And Query Table For 4th Time
We'll add another 230'000 records of all the letters we haven't yet used and see what happens:
USE [StackExchange]
GO

INSERT INTO [dbo].[Q275204]
  ([A], [B], [C], [D], [E], [F])
VALUES
  ('A', 'A', 'A', 'A', 'A', 'A'),
  ('B', 'B', 'B', 'B', 'B', 'B'),
  ('C', 'C', 'C', 'C', 'C', 'C'),
  ('D', 'D', 'D', 'D', 'D', 'D'),
  ('E', 'E', 'E', 'E', 'E', 'E'),
  ('F', 'F', 'F', 'F', 'F', 'F'),
  ('G', 'G', 'G', 'G', 'G', 'G'),
  ('H', 'H', 'H', 'H', 'H', 'H'),
  ('I', 'I', 'I', 'I', 'I', 'I'),
  ('J', 'J', 'J', 'J', 'J', 'J'),
  ('K', 'K', 'K', 'K', 'K', 'K'),
  ('L', 'L', 'L', 'L', 'L', 'L'),
  ('M', 'M', 'M', 'M', 'M', 'M'),
  ('N', 'N', 'N', 'N', 'N', 'N'),
  ('O', 'O', 'O', 'O', 'O', 'O'),
  ('P', 'P', 'P', 'P', 'P', 'P'),
  ('Q', 'Q', 'Q', 'Q', 'Q', 'Q'),
  ('R', 'R', 'R', 'R', 'R', 'R'),
  ('S', 'S', 'S', 'S', 'S', 'S'),
  ('T', 'T', 'T', 'T', 'T', 'T'),
  ('U', 'U', 'U', 'U', 'U', 'U'),
  ('V', 'V', 'V', 'V', 'V', 'V'),
  ('W', 'W', 'W', 'W', 'W', 'W')

GO 10000

That's looking good.
Hold The Horses!
Before we go along and select the data, let's have a look at the statistics for the index that was created for column B.
Statistics for INDEX 'NIX_Q275204_B_hot2use_20200910'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NIX_Q275204_B_hot2use_20200910  Sep 10 2020  8:59AM             10000                           10000                           1                               0                               4                               YES                                                             10000

All Density                     Average Length                  Columns
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                               2                               B
1                               4                               B, A

Histogram Steps
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
X                               0                               10000                           0                               1

No change!
Statistics aren't updated automatically, because it can be an expensive operation. The database engine will update the statistics when required or when manually triggered by a maintenance job.
Query Table For 4th Time
We'll run the query for the 4th time and see what happens.

And Paste The Plan
Findings
The index on column B still isn't being used, but the missing index is now shouting that the benefits would be immense:

Missing Index (Impact 98.0765): CREATE NONCLUSTERED INDEX ....

Let's take a short break and go back to your question.

Your Question / My (Current) Answer

If I wanted to run run the following query below, is Index 2 needed or could it use Index 1 and not impact on performance?

Up to know you could drop both indexes and you wouldn't notice much difference. But as the density in the index/statistic sinks, the selectivity increases up to a point where your index might be required.
It depends....

Add More Data And Query Table For 5th Time
We'll add another 230'000 records containing all the other letters, except X, Y and Z and see how the query responds.
Lo and behold: no change.

The Missing Index is still shouting and we are still triggering the use of the Clustered Index.
Add More Data And Query Table For 5th Time
Let's add a 0 to the GO statement of our INSERT statement and add 2.3 Mio records to the table and then select away....
2.3 Mio records added and the query is still using the Clustered Index. The Missing Index impact has increased again, but we still aren't using any of the non-clustered indexes.
Add More Data And Query Table For 6th Time
Let's add a further 0 to the INSERT and add 23 Mio records to our already existing 3.04 Mio records. Then we'll SELECT again and see if the query optimizer starts using the NIX_Q275204_B_hot2use_20200910 index on column B.
Sorry, that took a while [18:00 Min] to insert the 23 Mio records

And the Paste The Plan to examine online.
Musings / Findings
Well what a surprise. Your query has used the index that is covering the B and C columns of your table. Nearly like what the MISSING INDEX  was shouting about.

Your Question / My (Current) Answer

If I wanted to run run the following query below, is Index 2 needed or could it use Index 1 and not impact on performance?

Well, with enough data, your index 1 (covering columns B and C) will eventually be used. Index 2 is not (yet) required.
But It depends...
Parameterized Queries
If you change the query to use parameters, then the index on columns B and C is no longer used.
DECLARE @x AS NVARCHAR(1)
SET @x = 'X'
SELECT A, B, D, E FROM dbo.Q275204 WHERE B = @x

The query engine goes back to scanning the Clusterd Index.
Missing Index
If you create the missing index as suggested earlier on, then the query optimizer uses that index.
CREATE NONCLUSTERED INDEX NIX_Q275204_B_INCL_ACDE_hot2use_20200910 ON [dbo].[Q275204] ([B]) INCLUDE ([A],[C],[D],[E])

Run the query and:

See also Paste The Plan

Conclusion
Optimizing Indexes is a very time-consuming activity. Determining whether an index should be used/dropped/created equally so.
You have to observe your environment, your application and your database, to determine if and when an index could be used. Don't create every index that is suggested, but chose wisely. Sometimes they can help, other times they are just additional data in a database.


This is probably the most straightforward way to do this (edited to handle gaps):
SELECT
  Worker
 ,WorkHours
 ,startofperiod
 ,LEAD(priorend,1,'2111-11-11') OVER (Partition By Worker ORDER BY startofperiod) AS endofperiod
FROM
  (
    SELECT
      Worker
     ,startofperiod
     ,endofperiod
     ,LAG(endofperiod) OVER (PARTITION BY Worker ORDER BY startofperiod) AS priorend --Get prior end date in case of gap
     ,WorkHours
     ,CASE
        WHEN WorkHours <> LAG(WorkHours,1,-1) OVER (PARTITION BY Worker ORDER BY startofperiod) THEN 'Y'  --If hours change
        WHEN LAG(endofperiod + Interval '1 DAY') OVER (PARTITION BY Worker ORDER BY startofperiod) <> startofperiod THEN 'Y'  --If gap
        ELSE 'N'
      END AS Change_Ind
    FROM
      workhours_over_time
  ) summary
WHERE
  Change_Ind = 'Y'

Returns:
Worker  |   WorkHours   |   startofperiod   |   endofperiod
W1      |   35          |   2020-01-01      |   2020-02-29
W1      |   39          |   2020-03-01      |   2020-04-15
W1      |   35          |   2020-04-16      |   2111-11-11

This will handle gaps as long as your intervals are correct (in your example they are not as 2020 is a leap year).  You could change the logic to LAG(endofperiod + Interval '2 DAYS') OVER (PARTITION BY Worker ORDER BY startofperiod) < startofperiod or just fix the end dates.


How to find consecutive non zero values from a column

Test something like
WITH cte AS ( SELECT *, SUM(CASE WHEN counts=0 THEN 1 ELSE 0 END) OVER (ORDER BY timestamp) grp
              FROM sourcetable )
SELECT MIN(timestamp) start_time, MAX(timestamp) end_time, SUM(counts) total_count
FROM cte
WHERE grp = 0

DEMO fiddle (MySQL 8 used)


SQL Server: Collation of the resource database

The database Collation for the first SELECT statement in my query is Latin1_General_CI_AS.

Well, not exactly. There are a few problems with this statement:

The Database's default Collation only matters in a query when using string literals, variables, and return values from UDFs. AND, that default Collation only matters if there is no column or COLLATE keyword being used.
SELECT statements / queries, as a whole, do not use Collations. Collation is assigned per each string field, and it can be different for each field in a query.
The column in the first / top query, stopword, is not using the Latin1_General_CI_AS Collation (more on this in a moment).

  I see that the system-stopwords are stored in the resource database, which can explain the difference in collation.

A column (or expression) coming from a different Database does not necessarily explain a difference in Collation. As stated above, Collation is set per each field of a query, whether that field comes from a column in a table or is an expression. Collation is usually derived naturally from Collation Precedence: Column Collation overrides literals and variables, and the COLLATE keyword overrides both. When there is a conflict, then you need the COLLATE keyword.

The main point here, however, is that if the stopword column in sys.fulltext_system_stopwords comes from a column in a Table in the Resource Database (i.e. mssqlsystemresource), OR if it comes from an expression in a View in the Resource Database that has its Collation set via the COLLATE keyword, then the default Collation of the Resource Database is irrelevant.

  I can 'solve' my error by using COLLATE in my query

Yes, the COLLATE keyword is the way to go. However, to fix this conflict, you only need to specify the COLLATE keyword in one query, though it does not matter which query.

For example, if I run the query with no COLLATE keyword, I get:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw

UNION

SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

  Msg 468, Level 16, State 9, Line 4
  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the UNION operation.

So, assuming that the conflict is in the second (i.e. bottom) SELECT statement, I could fix it by applying the current DB's default Collation:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.fulltext_system_stopwords ftssw;

And that works. BUT, what if we try putting COLLATE on the first / top SELECT:

SELECT ftsw.stopword COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

That also works. And in fact, both of the following also work:

SELECT ftsw.stopword COLLATE Hebrew_100_CI_AS
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

-- and:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword COLLATE Hebrew_100_CI_AS
FROM sys.fulltext_system_stopwords ftssw;

Also, rather than use the DATABASE_DEFAULT option, which here equates to Latin1_General_CI_AS, I would use Latin1_General_BIN (or better yet: Latin1_General_100_BIN2, which is newer and better) in this particular case because it will ensure that different strings that can be normalized into the same string via the "distinct" behavior of the UNION (without the ALL) and the case-insensitivity of the Latin1_General_CI_AS Collation show up as different rows.

  Where does the Latin1_General_BIN collation come from? It looks like the sys.fulltext_system_stopwords table has a different collation, but why?

That Collation comes from an unlikely source. Let's look at the system catalog views in the query:

EXEC sys.sp_help N'sys.fulltext_stopwords';

The results indicate that this is a View (as expected) and that the stopword column has a Collation of Latin1_General_BIN (not expected). But wait, if the Latin1_General_BIN Collation is coming from sys.fulltext_stopwords, then what about sys.fulltext_system_stopwords and where is the other Collation coming from? Let's look:

EXEC sys.sp_help N'sys.fulltext_system_stopwords';

The results indicate that this is a View (as expected) and that the stopword column has a Collation of SQL_Latin1_General_CP1_CI_AS (not expected).

We can now dig a little deeper into the definition of each of those system catalog views:

EXEC sys.sp_helptext N'sys.fulltext_stopwords';

Returns (simplified):

SELECT
    fts.stopword,
FROM sys.sysftstops fts

And then:

EXEC sys.sp_help N'sys.sysftstops';

The results indicate that this is a system table, and that the stopword column indeed has a Collation of Latin1_General_BIN.

Next we can move on to the other system catalog view:

EXEC sys.sp_helptext N'sys.fulltext_system_stopwords';

Returns (simplified):

SELECT convert(nvarchar(64), stopword) as stopword, language_id
FROM OpenRowset(TABLE FTSYSSTPWD)

And FTSYSSTPWD comes from the Resource Database, so there isn't much more we can do at this point.

Still, there is one last thing we can do to be clear about the Collation of the data coming from the Resource Database -- sys.fulltext_system_stopwords.stopword:

CREATE DATABASE [FullTextCollationTest] COLLATE SQL_EBCDIC277_CP1_CS_AS;
GO
USE [FullTextCollationTest];

EXEC sys.sp_help N'sys.fulltext_stopwords';
-- Collation for [stopword] column: Latin1_General_BIN      (same as before)

EXEC sys.sp_help N'sys.fulltext_system_stopwords';
-- Collation for [stopword] column: SQL_EBCDIC277_CP1_CS_AS (same as DB's default Collation)

GO
USE [master];
DROP DATABASE [FullTextCollationTest];


The source of stored procedures is stored in the SYSTEM tablespace in the SOURCE$ table.

You need not to restore any user data at all, simply restore the SYSTEM, SYSAUX and UNDO tablespaces and skip the rest. You can then open the database with the other tablespaces offlined, and you can access the stored procedures as usual, or query DBA_SOURCE or SOURCE$ directly.


If you absolutely need to give direct database access to someone, then go with option a) and all that it entails.

Else, then go with option b).

Option a) is a poor choice because, as of right now (MongoDB 4.2), permission granularity is still at collection level. This means that if users have write access to the same collection, they can overwrite documents of each other.

Even for your use case, if each user has their own collection or database, this would only work well with few users (less than five thousand active users). I suppose you could have a million users this way, even a billion, but MongoDB sharding doesn't work this way, and will never scale properly.


Performing query based on another query's result

One possible way is an inner self join.

SELECT DISTINCT
       p1.Flyerid,
       p1.adblockid,
       p1.OriginalPagedetailid,
       p1.Pagedetailid,
       p1.category,
       p1.brand,
       p1.[Sales Price],
       p1.Size,
       p1.Format,
       p1.manufacturer
       FROM prdcnen p1
            INNER JOIN prdcnen p2
                       ON p2.Adblockid = p1.Adblockid
       WHERE p2.pagedetailid = '67197228466';


Database Design: Should I use a Materialized View

Unless you work on that database, that's not really something we can answer. However, we can speculate.

It could be a materialized view, but it could also be that a job runs on a schedule to calculate the stats. Maybe computed columns. Maybe just really fast hardware with efficient queries and the perfect indexes. So many possibilities.

My bet would be on the job though.


Verifying connection pooling server side with CONTEXT_INFO on

First, CONTEXT_INFO is a property of the session, not the connection. It gets reset by sp_reset_connection when the same session is reused and the first batch is executed. It seems that CONTEXT_INFO was not reset in SQL Server 2000 and possibly earlier versions, but starting with SQL Server 2005 it is definitely reset to NULL.

Part of the confusion here is that the question is specific to "Microsoft Dynamics AX" since general .NET programming wouldn't have the registry key noted in that blog article. Also, the information that Microsoft Dynamics is then storing in CONTEXT_INFO is its application session details, which have nothing to do with SQL Server SPIDs and cannot be used to infer that connection pooling is occurring since the application session will naturally span multiple connections as well as SPIDs.

The mechanism being used to set CONTEXT_INFO pretty much has to be a separate, additional query executed prior to any other queries for that session. Something along the lines of:

SqlConnection _Connection = new SqlConnection("{connection-string}");
_Connection.Open();

if(_IsConnectionContextRegistryKeySet)
{
  SqlCommand _Command = _Connection.CreateCommand();
  _Command.CommandType = CommandType.Text;

  _Command.CommandText = @"DECLARE @BinaryInfo VARBINARY(128);
                           SET @BinaryInfo = CONVERT(VARBINARY(128), @StringInfo);
                           SET CONTEXT_INFO @BinaryInfo;";

  SqlParameter _ParamInfo = new SqlParameter("@StringInfo", SqlDbType.VarChar, 100);
  _ParamInfo.Value = String.Format("{0} {1} {2}...", AXuserID, AXsessionID, ...);
  _Command.Parameters.Add(_ParamInfo);

  _Command.ExecuteNonQuery();
  _Command.Dispose();
}

Hence, the small amount of additional overhead incurred for enabling this info to be set comes from the execution of this additional query.

Second, you can test for connection pooling by using SQL Server Profiler. Select the "RPC:Completed" event in the "Stored Procedures" category, make sure that "TextData", "ClientProcessID", and "SPID" are checked for that event (at the very least, you can select other columns if you like). Then, go to "Column Filters", select "TextData", and in the "Like" condition add the following condition: exec sp[_]reset[_]connection. Now run that trace. If you see instances of exec sp_reset_connection coming through, then that is due to connection pooling being used.

Additionally, there are two side-effects of connection pooling that may or may not show up in the DMVs depending on how many connections are being requested. The following query should capture many / most of the connections that are pooled (please note that it has nothing to do with CONTEXT_INFO):

SELECT sssn.login_time,
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       conn.*
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
WHERE conn.session_id <> conn.most_recent_session_id
OR    DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time) > 50
ORDER BY conn.connect_time;

This query looks for the following indications of connection pooling being used:

Current session_id is not the same as the prior session_id. If these two IDs are the same then it may or may not be a connection that is using pooling, since the same SPID can be reused. But, if they are different, then that can only be the result of connection pooling.
Time between the connection being made and time that the session starts is over 50 milliseconds (though that threshold might vary by system). Usually the first session to get created upon a connection is less than 30 milliseconds after the connection, but it "generally" shouldn't be above 50, even if executing multiple SqlCommands.

Along similar lines, it should also be possible to test for connection pooling by creating a temp table, and every few seconds, capturing both [session_id] (INT) and [connection_id] (UNIQUEIDENTIFIER) from sys.dm_exec_connections. Then, just look for rows that have the same connection_id but different session_id.

Finally, the query posted in the question is not valid for indicating connection pooling with respect to most web applications. The issue here is that typically, the properties of "program_name", "login_name", and "host_name" would be the same for all connections made by web/app server (hence the need for the per-processor / per-core licensing models instead of just having the CAL model).


MySQL Community Manager Morgan Tocker posted a blog earlier this year on how to reduce memory consumption in MySQL.

http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html

Some of the settings he uses are not really realistic for a production instance of MySQL, because the settings are far below what it takes to give good performance. But his blog was an exercise, not a recommendation. Anyway, it can show you many of the settings that do have an effect on memory use.

Your server with only 2GB of RAM is pretty undersized for a MySQL server. Consider this survey of MySQL users, asking how much RAM they have in their servers. The top answers were 16-64 GB and 4-16 GB.

http://www.mysqlperformanceblog.com/2012/11/02/how-much-memory-do-you-use-to-run-mysql/


You need to store the value that you want to return somewhere:

CREATE OR REPLACE FUNCTION f_get(ikey text)
  returns integer
  AS
$func$
DECLARE
  l_id integer;
BEGIN
  LOCK TABLE foo IN SHARE ROW EXCLUSIVE MODE;
  INSERT INTO foo (type)
  SELECT ikey
   WHERE NOT EXISTS (
     SELECT * FROM foo WHERE type=ikey
   )
   returning id into l_id; --< store the returned ID in local variable
   return l_id; --< return this variable
END
$func$ LANGUAGE plpgsql;

You could have done that with the original function and an OUT parameter as well. In that case you just need to use returning id into out_key;


To prove the benefit of data compression for this question, I quickly built the following test:

USE tempdb;

CREATE TABLE dbo.IntTest
(
    IntTest_ID INT NOT NULL
        CONSTRAINT PK_IntTest
        PRIMARY KEY CLUSTERED
)
WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.SmallIntTest
(
    SmallIntTest_ID SMALLINT NOT NULL
        CONSTRAINT PK_SmallIntTest
        PRIMARY KEY CLUSTERED
)
WITH (DATA_COMPRESSION = NONE);
GO

INSERT INTO dbo.IntTest (IntTest_ID)
SELECT TOP(65535)
    CONVERT(SMALLINT, ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id, o2.object_id) - 32768)
FROM sys.objects o
    , sys.objects o1
    , sys.objects o2
ORDER BY o.object_id, o1.object_id, o2.object_id;

INSERT INTO dbo.SmallIntTest (SmallIntTest_ID)
SELECT TOP(65535)
    CONVERT(SMALLINT, ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id, o2.object_id) - 32768)
FROM sys.objects o
    , sys.objects o1
    , sys.objects o2
ORDER BY o.object_id, o1.object_id, o2.object_id;

SELECT o.name
    , i.name
    , p.partition_number
    , p.rows
    , au.used_pages
    , au.total_pages
    , AvgRowsPerPage = p.rows / CONVERT(DECIMAL(10,2), au.used_pages)
FROM sys.allocation_units au WITH (NOLOCK)
    INNER JOIN sys.partitions p WITH (NOLOCK) ON ((au.type = 1 OR au.type = 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id)
    INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
    INNER JOIN sys.objects o WITH (NOLOCK) ON p.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name IN (
        'IntTest'
        , 'SmallIntTest'
    );

The output from the last query, showing actual pages used by each table, is:

+-----------------+-----------+-------+------------+-------------+-------------+
|      NAME       | PARTITION | ROWS  | USED_PAGES | TOTAL_PAGES | AVG ROWS    |
|                 | NUMBER    |       |            |             | PER PAGE    |
+-----------------+-----------+-------+------------+-------------+-------------+
| PK_IntTest      | 1         | 65535 | 92         | 97          | 712.3369573 |
| PK_SmallIntTest | 1         | 65535 | 92         | 97          | 712.3369573 |
+-----------------+-----------+-------+------------+-------------+-------------+

As you can see from the above results, compressed INTs containing SMALLINTs actually consume precisely the same amount of space.


SQL Server 2008 Analysis Management objects work with SQL Server 2005?

I believe that it can, and the API wraps a fairly well documented web service interface called XML/A.  The SQL Server 2008/2008R2 management software will work with SSAS 2005 cubes as well.


How are deadlocks detected and reported in an RDBMS?

In SQL Server there is a separate thread that periodically (default 5 seconds, lower interval if a deadlock has just been detected) checks a list of waits for any cycles. I.e. it identifies the resource a thread is waiting for, then it finds the owner of that resource and recursively finds which resource that thread is in turn waiting for, thereby identifying threads that are waiting for each others resources.

If a deadlock is found then a victim is chosen to be killed using this algorithm:

Identify threads that are not unkillable (e.g. a thread that is rolling back a transaction is unkillable).
Find the thread with the lowest deadlock priority.
Chose the one that is cheapest to roll back, i.e. the one that has done the least work so far.

You can find more information about SQL Servers deadlock detection here:
http://msdn.microsoft.com/en-us/library/ms178104.aspx

The transaction owner/application developer is responsible for minimizing the risks of deadlocks occurring, and to do that they should:

Make sure to keep transactions as short as possible. E.g. don't show a login form after starting a transaction and wait for user input, instead gather all the info you need and then run the transaction.
Use the lowest possible isolation level, e.g. don't set serializable when you just want to temporarily show some values to the user. Please note that setting correct isolation level is a science in itself and out of scope in this answer.
If you are the victim of a deadlock, i.e. you get error# 1205, then re-run your transaction transparently to your user. Since the other, competing, transaction has now hopefully acquired the resources it was waiting for and finished, it is unlikey that you will encounter the same deadlock again.


You should either remove the checkbox to limit the size of the job history or increase it.  One of your maintenance steps should be to run the sp_purge_jobhistory MSDN command to remove job history older than your retention period.

The "Remove Agent History" checkbox does this behind the scenes but is NOT an automated process that removes history older than the time period specified.  It's really badly named and placed.

If you have lots of jobs or a few jobs that run very often then you will quickly use up 1000 logs of job executions.  As mentioned in the comments, another solution is to archive the contents of the sysjobhistory table.


merge multiple fields of csv file while running mysqlimport command

Because it is a CSV file (Comma Separated Values), it thinks that the each of the marks is a different field. Your initial export should either separate values by a different char, like "#", or quot the values bu ".


Running each SQL Server service under different Windows accounts

Typically they aren't run under a different account, but all under the same Windows account.  This makes the management much simpler as there is only one account's password to change.  The downside to this is that if the password needs to be changed for the account all the servers need to be restarted at once.

As for permissions, just set the SQL account as a normal domain user, and let the SQL installer modify the rights on the machine that are needed.  It will give the account the rights that it needs to run SQL.

The benefit to having one account per service is that you get a more fine grained control of what objects on the domain each account gets rights to.  While more secure in larger environments it often isn't practical to control things at this level as you'll quickly end up with hundreds or thousands of accounts just for running SQL Services.


http://antapex.org/traceflags_sqlserver.txt

Although this isn't complete, it is the biggest single list I know of.

This MSDN article lists the Query Optimizer Trace Flags necessary to support several changes to the QO in various QFEs and hotfixes, etc.

http://support.microsoft.com/kb/974006

Technet also has a list of various trace flags (probably already documented in the links above:

http://technet.microsoft.com/en-us/library/ms188396.aspx

This documents trace flag 610:  http://msdn.microsoft.com/en-us/library/dd425070%28v=SQL.100%29.aspx (controls minimally logged inserts into indexed tables)

Technet has another huge list of trace flags at: http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx


Prevent Data Sources from being deployed outside designated folder

Why not change permissions so that folk can't deploy datasources to anything other than this folder?

So, remove "Manage data sources" from all folders except for /Data Sources. This can be done at root level and then set custom permissions on /Data Sources

You may need to setup a custom role for this if you can't change the existing ones.


Is 'desc' a valid name for a column in mysql?

"ASC" or "DESC" are the reserved keyword in MySQL. you can't use it for column name. "ASC/DESC" is the keyword used to sort result sets in either ascending or descending order. "ASC" is used as the default.


Linked server error not caught by TRY-CATCH

One thing you can try is to use sp_testlinkedserver. You can also issue the OPENQUERY using dynamic SQL (as Max correctly pointed out), to defer the parser validating the server name until runtime.

BEGIN TRY
    EXEC sp_testlinkedserver N'server1';

    EXEC sp_executesql N'SELECT * FROM OPENQUERY([server1],
      ''SELECT 1 AS c;'');';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;

PRINT 'We got past the Catch block!';

While this works equally well without sp_testlinkedserver, that procedure can still be useful in preventing you from trying a whole bunch of code against that server...

Also, since if sp_testlinkedserver fails it actually fails at compile time, you can defer that and still capture it by using dynamic SQL there, too:

BEGIN TRY
  EXEC master.sys.sp_executesql N'EXEC sp_testlinkedserver N''server1'';';
  ...
END TRY


What's difference between these 2 statements?

I was answering the question it its first form, where there were parentheses and the queries were syntactically correct (before the edits that removed parentheses):

  Which is correct? or both? Why?

  1st:

INSERT INTO table (num) VALUES
((SELECT COUNT(*) + 1 FROM table));

  2nd:

INSERT INTO table (num) VALUES
((SELECT COUNT(*) FROM table) + 1);

In this instance, they are identical except for perhaps the execution plan which honestly probably wouldn't be very different anyways.
I prefer the first one in terms of readability.  Generally, unless there is a windowed function or sub-query, there's no reason to have the +1 after the full select/from statement.  There's no really a good reason to nest the statement with all the extra parenthesis.  In fact, I could see how the second option would cause some confusion in terms of readability.

One more point, when you're using a SELECT for the values on an insert statement, you don't actually need the keyword values.  To simplify try this

INSERT INTO table (num)
SELECT COUNT(*) + 1
FROM table


Your regional settings control the way dates are displayed. Try changing settings there.


How is an AAG listener implemented? Where does it run?

The Availability Group Listener (AGL) is part of the AG's Windows Server Failover Cluster (WSFC) Resource. It is a Virtual Network Name with n number of IP addresses. Being in a cluster resource it will only run on a single node of the WSFC and that node will be the primary node of the AG. It is not a thread within SQL Server. The WSFC handles the failover of the AG resource and the AG databases will not come online without the AG resource residing on its node of the WSFC.

The listener just handles connection forwarding. When connecting to the read/write replica the connection will hit the listener and then the listener will tell the client to connect to the primary replica's IP address. In the case of read-only routing it will follow the below work flow.


Transaction terminated running SELECT on secondary AG group

This is caused by one or two things, much like the error says:

A config change was made to the AG
The primary restarted
The ghost cleanup low watermark was set

The first two are fairly obvious as to why this would be an issue. The 3rd is not.

Since secondary replicas automagically remap read-committed isolation level to the snapshot isolation level a few things need to kick in. One of those things is row versioning, since after all SI is an optimistic concurrency.

Row versioning depends on the version store rows to be available on the secondary. If the low water for ghost cleanup is changed, then the secondary may need to cleanup records that are currently used for queries being satisfied by the version store which have a timestamp lower than the new ghost cleanup low watermark. When this is the case, those versions will be cleaned up and any SI datasets that required them will no longer be valid. That's when this error will surface for the session for that dataset.

select distinct some_column
from some_table oo (nolock)
inner join some_other_ ss (nolock) on ss.some_column= oo.some_other_column

If this query runs quickly, I don't see why it should error out like that as I doubt your low watermark is changing that rapidly. Thus, I'm going to assume this takes some time to run. Having the appropriate indexes, not using a long transaction, and up-to-date statistics will help with this query. Additionally, the NOLOCK hint is completely ignored on the readable secondary, you may already know that and it may just be code re-use which is fine but I wanted to point it out anyway.

  We are considering using read-intent to resolve the issue, do you think that will resolve the problem?

ApplicationIntent is only used for two things in general with AOAGs:

read only routing (ROR)
to access AG Replicas that are set as secondary mode read-intent only.

This will not solve the underlying problem. IMHO you have two options, make the query run faster (if it isn't already) or open up a support ticket with MS. The reason I say open a ticket is this query should not constantly fail 24/7 and the issue should be looked into.


Sync MySQL 4.0 table to MySQL 5.6 server every few minutes

This looks interesting and might (just might) be a solution to your issue. It shows how a 4.1 server can simulate Stored Procedures (SPs - not available till version 5 of the MySQL server) using the Federated storage engine. Basically, it shows that a 5.x server can access data on a 4.x server and even update that data. It also shows that you can use 5.x to read the data also. This is probably a good fit for your requirements, however the federated engine does not support transactions. It's also covered here (Morgan Tocker is a MySQL community manager).


How to speed up datetime MySQL queries or improve db scheme for time series data?

You may need to add proper indexes to your data... My guess is that you need a multi-column index on (data_node_id, timestamp).

Check the mySQL documentation on Multiple-Column Indexes

--

Side note:

I suspect that a data_node cannot have two readings at the same time. That would make the (data_node_id, timestamp) pair always unique (and not null). If this were the case, I would use this as the primary key, and take out of the table the id column (and the corresponding index). Taking out the surrogate id key will save some data that doesn't need to be read... if you're looking for the last bit of performance, it also helps.

As mySQL + innoDB keeps your data clustered by primary index, having (data_node_id, timestamp) as your PK would clearly be an advantage for retrieving information. Like always, it comes with a price: INSERTS will cause fragmentation (and thus, be more expensive), because your PK will not be an increasing number.


Oracle Online Patching -- How To Exclude Tables in a Custom Schema

@Jeff As your database is 12c, you can query dba_objectes and check the the oracle_maintained column, e.g. select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS, ORACLE_MAINTAINED from dba_objects where OBJECT_NAME='MyObject';. If ORACLE_MAINTAINED is set to 'Y', then your object is likely to be affected by patching, if not it will be ignored.


database design question with Partition and Replication

Looks like you are looking for NoSQL DB with Data shard across nodes. Check the MySQL NDB Cluster.


how to create ambari database backup without enter password

You are trying to connect to a database called ambari this way (when specifying only the -U parameter, the DB name is taken from its value).

From the .pgpass file it looks you want to connect to the postgres DB.  Specify -d postgres, too, or set

localhost:5432:*:ambari:bigdata

in your .pgpass file.  (As it turns out, there is an ambari DB, so the connection parameters look all right.)

Note that by default this file has to be in the OS user's (in this case postgres) home directory.  Alternatively, you can specify its location in other ways.


Environmental Variables for OracleDB 12 in Oracle Linux are Overriding Each Other with SQLPlus Variables

I found a solution.

I noticed that the path ORACLE_HOME wasn't working properly. I used a deductive method. I determined the error by executing common commands that normally run by supplying the name of the application (Within OracleDB suite). For example:

First I checked the oratab file, as follows:

 vi /etc/oratab

Which gave me the following output:

orcl:/u02/app/db12c/product/12.1.0/dbhome_1:Y

Then, I started executing common commands. For example, I ran the "Listener Controller application" as follows:

lnsrctl status

Which gave me the following error:

bash: /u02/app/db12c/product/12.1.0/dbhome_1/lnsrctl: No such file or directory

Then, I decided to use the command above by stating the path explicitly.

/$ORACLE_HOME/bin/lsnrctl

Which gave me the following:

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2017 20:28:22

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

Then I ran the status of the Lnsrctl, as follows:

LSNRCTL> STATUS

Which gave me the following:

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                27-JUN-2017 20:28:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u02/app/db12c/diag/tnslsnr/{ServerURL}/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST={ServerURL})(PORT=1521)))
The listener supports no services
The command completed successfully

After the above, I was able to run the commands normally. For instance,

sqlplus / as sysdba

Which gave me the familiar screen of:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 27 20:29:11 2017

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

Connected to an idle instance.

SQL>

And that was the problem. I'm not really sure if this is a glitch, setup problem, or design feature, but I hope this post can help someone who is going through the same situation. From now on, I have to use the command:

/$ORACLE_HOME/bin/lsnrctl

explicitly.

Thanks for everyone who posted.


Error: "mod_fcgid: can't apply process slot for" makes my server unresponsive for a period of time

There is surprisingly little information about this error out there.

My server provider suggested that FastCGI might be a poor PHP interpreter for potentially high traffic websites. I switched my interpreter to NGINX with support of PHP-FPM.

I dont have enough data yet to confirm that freezing was solved completely (but now it wont be related to FastCGI), NGINX is the preferred option for maintaining high performance server as suggested by various sources.


Why doesn't `1` fit into a decimal(4, 4) column?

The Precision specifies the total number of digits that can be stored, the Scale specifies, how many of those digits live behind the decimal point.

A DECIMAL(4,4) therefore has four digits and all are behind the decimal point. That means you can store values form -0.9999 to 0.9999. You cannot store -1 or 1 as they have a digit before the decimal point.

SQL Fiddle

Query 1:

SELECT CAST(-0.9999 as DECIMAL(4,4)),CAST(0.123456 as DECIMAL(4,4)),CAST(0.9999 as DECIMAL(4,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -0.9999 |   0.1235 |   0.9999 |

To store 1 you have to add an additional digit as in DECIMAL(5,4). That allows you to store values from -9.9999 to 9.9999:

SQL Fiddle

Query 2:

SELECT CAST(-9.9999 as DECIMAL(5,4)),CAST(1 as DECIMAL(5,4)),CAST(9.9999 as DECIMAL(5,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -9.9999 |        1 |   9.9999 |


how to speed up cpu limited mysql database backup


Where would the table data be if you had 3 filegroups, 2 with tables and 1 with all indexes?

It depends on what you mean by an index, and what you mean by a table object, because every table is either a heap or a clustered index. Either type of table can also have non-clustered indexes as well, which are copies of the data.

If you have all the non-clustered indexes in a separate filegroup, then you're in a space that was described by Greg Linwood in chapter 33 of the first SQL MVP Deep Dives book, where the suggestion was that because it's only a copy of data you might not have to back it up. It's very complicated to recover from though, so it's not for everyone.

Typically when I've heard people suggesting that all indexes are stored separately, it's because they figure that data that has a lot of Scans run against it might suit a different type of storage to data that has a lot of Seeks (I'm capitalising as I'm referring to the type of database operation, not a general 'scan'). And they figure that indexes have Seeks, and heaps have Scans. But they're forgetting that any kind of lookup (including RID Lookups against heaps) are the same kind of operation as a Seek, and that indexes often have Scans run against them too.


Is it safe to initially rsync without pg_start_backup, provided you snapshot and rsync afterwards?

Yes, completely safe. The subsequent rsync performed under snapshot conditions will ensure that the data is properly aligned using the delta method.

One might want to ensure that there isn't an accumulation of irrelevant data files by using the --delete option (think carefully about what you are excluding) but this is a mostly separate issue.


High Latch Wait time with Logon trigger enabled while full backups are running

This isn't an answer really, but it's too big for a comment. I hope it will be helpful.
Using version (got the same results with SQL Server 2016 on Windows 10):

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64)
Jan 25 2021 20:16:12   Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 19042:
)

I see peculiar behaviour with the trigger in question. I used SqlQueryStress to simulate some workload on my local instance (you probably need to build it from newest commit, older versions were too slow for me).
As query I used: SELECT 1, Number of iterations: 100000, Number of Threads: 10. Everything else left as default. I started the workload with GO button and after few seconds stopped it with Cancel. When the trigger is enabled, the moment I stop the workload, I get following pair(s) of messages in error log:

Logon failed for login '***' due to trigger execution. [CLIENT: ]

The client was unable to reuse a session with SPID 73, which
had been reset for connection pooling. The failure ID is 1. This error
may have been caused by an earlier operation failing. Check the error
logs for failed operations immediately before this error message.

This isn't the same issue, because it seems I can use the instance normally. I also haven't observed any latch problems. The only queries affected are the ones I cancelled. But the underlying cause may be the same. This happens only under certain workload (1k+ batch requests in my case). It could be some race condition in code, which is exposed on newer Windows versions. I would speculate it manifests itself differently under different workloads. Personally I think you should contact Microsoft.


Will a VM backup of a SQL Server work for restoring the SQL database

I am wondering if a VM backup will restore a SQL database with out integrity issues

Maybe. If you have databases that span local volumes, then your VM backup must be "app consistent" by either using the Windows Volume Shadow Copy Service (VSS), or by ensuring write-ordering across the VM's volumes.

If you're not 100.000% sure of this, you can't rely on VM backups for database recovery.  You can always start with a VM backup and restore your SQL Server backups on it for a more robust recovery solution.


What are the events that EVMd process in Oracle RAC distributes across cluster?

The Cluster Ready Services (CRS) process is the primary program for managing high availability operations(start, stop, monitor, and fail over) in a cluster.

The crsd process generates events when the status of a resource changes and EVMd publishes these events.

For example, if one of the cluster node goes down then the VIP running  should fail over to another node.
In this case crsd does the work of fail over and that changes the status of the resource-which is VIP in our example.

EVMd process distributes such events to all of the cluster members so that they are aware of the cluster changes.


With a linked server reference you must use the four part name format, for instance SELECT * FROM server.database.schema.object. In your example it will be looking for an object called IFSAPP.C_LIMS_IFS in a local database called IFS. If that is your database name then you need to prepend the server name, if that is the linked server name then you need to insert the database name after it.
In some linking schemes you attach directly to one database not the whole server, in which case you can leave the database to default, but you must give all four parts just with that one blank, perhaps IFS..IFSAPP.C_LIMS_IFS is what you need.
To get better help you need to edit some further detail into your question:

What process did you follow to link the servers? Perhaps include the SQL server statements, with authentication detail removed of course, or a screenshot of you used a GUI.

What are the server, databases, and schema names, in Oracle, and the name you have to the looked server in SQL Server? (I've tried to guess a bit from your current SQL statements but if they are wrong my guess may be). Including the connection details mentioned above may implicitly include this information.

Also, I'm not sure I would expect intellisense to work well over linked servers so perhaps be wary of relying on that.


SQL Server heap tables fragmentation monitoring

I would suggest that, rather than 'roll-your-own', you save yourself a whole bunch of time and effort and implement Ola Hallengren's Index Maintenance script.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

And, check out Brent Ozar's tweaks for Ola's script

https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/


Is it possible to install a service pack to a SQL 2012 Express instance?

I just now figured it out. right after posting the question... should have waited a few more min.

I was trying to install SQL 2012 SP4 but it wasn't letting me choose the express instance, to apply the update to, only our other standard edition instance. I realized I had downloaded the x64 SP4 package and that when the Express instance was installed it was installed with the x86 version. I downloaded the x86 SP4 package and now it is letting me update the instance.

Maybe this will help someone in the future.


Oracle - Delete dmp files from ASM diskgroup

Just did a small test.
# find some ASM files
asmcmd find DATA/T101N/AUTOBACKUP/2020_09_20/ \*
+DATA/T101N/AUTOBACKUP/2020_09_20/s_1051641713.313.1051641713
+DATA/T101N/AUTOBACKUP/2020_09_20/s_1051658719.331.1051658719

# remove them one-by-one
asmcmd rm DATA/T101N/AUTOBACKUP/2020_09_20/s_1051641713.313.1051641713
asmcmd rm DATA/T101N/AUTOBACKUP/2020_09_20/s_1051658719.331.1051658719

# they are gone
asmcmd find DATA/T101N/AUTOBACKUP/2020_09_20/ \*
ASMCMD-8002: entry 'AUTOBACKUP' does not exist in directory '+DATA/T101N/'

Your scenario should be similar:
#!/bin/bash

# set grid home
. oraenv <<< +ASM

# delete dump-files
asmcmd rm -rf DATA/EXP exp\*.dmp

Best of luck!


On the query planners's reasons for choosing merge joins vs nested loops

In your sample case SQL Server made very good choice.
Here is what it was actually doing:
- In the first set of data it extracts 10 rows from Fact table based on the value column. Then it extracts 10 corresponding IDs from Property table using kind of SEEK operation for each ID.
- In the second scenario, when there are 100 matching rows in Fact table SQL decided not to do SEEK operation, but use SCAN instead, which is much cheaper from the I/O perspective.

Yes, you can use hints for your queries (https://msdn.microsoft.com/en-us/library/ms181714.aspx):

OPTION (LOOP JOIN)
OPTION (MERGE JOIN)

Try both of them for both of your data sets to see the difference in query cost and amount of I/O using SET STATISTICS IO ON.

For instance, when you force LOOP JOIN for the second data set I/O for Property table jumps 24 to 215 reads.
So, be VERY CAREFUL using any kind of these hints.


What is the best way of checking the current user privileges on a particular table from another schema?

ALL_TAB_PRIVS is a superset of ALL_TAB_PRIVS_RECD. You probably want ALL_TAB_PRIVS as it includes tables that the current user owns.


mongos is not listen when configured for sharded architecture (version 3.4)

Your config servers mongod should have --configsvr parameter


Ola's maintenance solution is not processing sysjob* tables

You need an additional parameter to tell Ola's script to consider Microsoft-shipped objects, since those job-related tables in msdb are marked as is_ms_shipped:

@MSShippedObjects = N'Y'

(This parameter defaults to N'N', which means ignore Microsoft-shipped objects, and is documented here.)


Compare two similar Postgres databases for differences

Have a look at the Stack Overflow Q & A, How to check difference between two databases in PostgreSQL?; in particular Another PostgreSQL Diff Tool (apgdiff) (schema comparison only). Apgdiff is recommended by Alexander Kuznetsov - a published database book author and bit of a guru.

Maybe also look at Liquibase or Compare Database Table Data.

There are also commercial products - e.g. Altova Database Differencing with DiffDog


How does SQL*Plus connect to Oracle without a listener?

As @BalazsPapp mentioned, the oracle db can be connected locally even without a listener. In this case, the BEQ (Bequeath) protocol is used, instead of the tcp-based normal oracle communication.

Although on Balazs's link there is nothing about the details of the BEQ. On unix, it probably uses unix domain sockets. On windows, it uses probably some local rpc.


MongoDB: co-locate the mongos process on application servers

Since there is already and answer submitted, and a useful and valid one at that, I do not want to distract from its own usefulness but there are indeed points to raise that go way beyond just a short comment. So consider this "augmentation", which is hopefully valid but primarily in addition to what has already been said.

The truth is to really consider "how your application uses the data", and to also be aware of the factors in a "sharded environment" as well as your proposed "container environment" that affect this.

The Background Case

The general take on the practice recommendation for co-locating the mongos process along with the application instance is to obviate any network overhead required in order for the application to communicate with that mongos process. Of course it is also "recommended practice" to specify a number of mongos instances in the application connection string in the case where that "nearest" node should not be available for some reason then another could be selected, albeit with the possible overhead of contacting a remote node.

The "docker" case you mentions seems somewhat arbitrary. While it is true that one of the primary goals of containers ( and before that, something like BSD jails or even chroot ) is generally to achieve some level of "process isolation", there is nothing really wrong with running multiple processes as long as you understand the implications.

In this particular case the mongos is meant to be "lightweight" and run as an "additional function" to the application process in a way that it is pretty much a "paired" part of the application itself. So docker images themselves don't have an "initd" like process but there is not really anything wrong with with running a process controller like supervisord ( for example ) as the main process for the container which then gives you a point of process control over that container as well. This situation of "paired processes" is a reasonable case and also a common enough ask that there is official documentation for it.

If you chose that kind of "paired" operation for deployment, then it does indeed address the primary point of maintaining a mongos instance on the same network connection and indeed "server instance" as the application server itself. It can also be viewed in some way as a case where the "whole container" were to fail then that node in itself would simply be invalid. Not that I would recommend it, and in fact you probably should still configure connections to look for other mongos instances even if these are only accessible over a network connection that increases latency.

Version Specific / Usage Specific

Now that that point is made, the other consideration here comes back to that initial consideration of co-locating the mongos process with the application for network latency purposes. In versions of MongoDB prior to 2.6 and specifically with regard to operations such as with the aggregation framework, then the case there was that there would be a lot more network traffic and subsequent after processing work performed by the mongos process for dealing with data from different shards. That is not so much the case now as a good deal of the processing workload can now be performed on those shards themselves before "distilling" to the "router".

The other case is your application usage patterns itself with regard to the sharding. That means whether the primary workload is in "distributing the writes" across multiple shards, or indeed being a "scatter-gather" approach in consolidating read requests. In those scenarios

Test, Test and then Test Again

So the final point here is really self explanatory, and comes down to the basic consensus of any sane response to your question. This is not a new thing for MongoDB or any other storage solution, but your actual deployment environment needs to be tested on it's "usage patterns" as close to actual reality just as much as any "unit testing" of expected functionality from core components or overall results needs to be tested.

There really is not "definitive" statement to say "configure this way" or "use in this way" that actually makes sense apart from testing what "actually works best" for your application performance and reliability as is expected.

Of course the "best case" will always be to not "crowd" the mongos instances with requests from "many" application server sources. But then to allow them some natural "parity" that can be distributed by the resource workloads available to having at "least" a "pool of resources" that can be selected, and indeed ideally in many cases but obviating the need to induce an additional "network transport overhead".

That is the goal, but ideally you can "lab test" the different perceived configurations in order to come to a "best fit" solution for your eventual deployment solution.

I would also strongly recommend the "free" ( as in beer ) courses available as already mentioned, and no matter what your level of knowledge. I find that various course material sources often offers "hidden gems" to give more insight into things that you may not have considered or otherwise overlooked. The M102 Class as mentioned is constructed and conducted by Adam Commerford for whom I can attest has a high level of knowledge on large scale deployments of MongoDB and other data architectures. Worth the time to at least consider a fresh perspective on what you may think you already know.


How can I unload Oracle database tables into a csv files?

Arun,

now that sqlcl is available fromOracle SQL Developer 4.1 EA2 (4.1.0.18.37)
you can use it a lot like the old and famous sqlplus.
sqlcl has an output format setting for csv

set sqlformat csv
spool x.csv
select * from yourtable[s];
spool off

for more info about sqlcl checkout Kris' blog

before sqlcl was around
easiest for this was to use APEX and export the report to csv.
In plain old sqlplus you can do this by using

set lines 9999 -- the appropriate size
set head off  -- no header lines
set colsep ';' --column separator to ;
set pages 0 -- no pages
set feed off
select your data;
spool to_tofile
/
spool off

This works best if the results are to be written on an application server or client. If they have to be written on the database server, utl_file might be a better option.

Ronald.


A constraint has been made to solve this issue:

 ALTER TABLE public.ja_customers
      ADD CONSTRAINT cc_check_empty_strings CHECK (("name_last" != '' AND "company" != '' AND "address" != ''));


Is 3 minutes to join a 50 million table too much?

First, add these indexes:

ALTER TABLE cat_01
ADD INDEX `ShopBuyDates` (`shop_id`,`buy_date`)

ALTER TABLE orders
ADD INDEX `OrderSales` (`order_id`,`sales`)

Then try this query, and report the results. Make sure to run it at least twice, and discard the first result's performance, to flush out the effects of populating the cache.

SELECT
'cat_01' as category,
m.shop_id,
o.name,
count(m.shop_id) as total,
tr2.traffic,
IF(mm.bought IS NULL,0,mm.bought) as bought
FROM cat_01 m
JOIN shops o
ON m.shop_id = o.id
JOIN (
    select
    mmm.shop_id,
    max(mmm.buy_date) as bought
    FROM cat_01 mmm
    GROUP BY mmm.shop_id
) mm
ON o.id = mm.shop_id
LEFT JOIN (
    SELECT mmmmm.shop_id, sum(bs.sales) AS traffic
    FROM cat_01 mmmmm
    JOIN orders bs
    ON mmmmm.order = bs.order_id
    group by mmmmm.shop_id
) tr2
ON tr2.shop_id = mm.shop_id
WHERE (m.buy_date IS NULL)
OR (m.buy_date = 0)
OR (m.buy_date < 1)
GROUP BY m.shop_id

After we see the improvement from this (a new EXPLAIN would be good too, from this query), we can iterate to see what else is slowing down your query.


SQL Server 2012: NUMA node shown as offline without special affinity settings

Found the answer:
http://blogs.msdn.com/b/sql_shep/archive/2012/06/21/sql-server-2012-license-core-limitaion.aspx

My consultants installed the wrong version (CAL instead of core), so it's limited to 20 cores.


Can I use SQL Server Standard Edition as Primary and Enterprise SQL Server as Secondary node?

No, this is not possible. Even if you'd manage to do such an installation, it would not be supported by Microsoft.
The documentation explicitly states that the editions must match:

The SQL Server edition you are installing must match across all the
nodes in a SQL Server failover cluster instance. When you add a new
node to an existing SQL Server failover cluster instance, make sure
that you specify that the edition matches the edition of the existing
failover cluster instance.

Although licensing questions are off-topic, a warning about your purposed installation is in order. In case of a licensing audit, the auditor would be quite likely to accuse you of running unlicensed setup. Licenses for Enterprise Edition are not cheap. You don't really have much bargaining power when license auditor has proof that you are not compliant.


Even though Mysql InnoDB allows to reference non-unique column or set of columns, it's very bad practice, and not recommended especially with CASCADE option :

  Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.
  (https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html)

The right way is to have a reference to USER.ID , not to USER.NAME.

However, adding and index on USER.NAME will let you create a constraint you want :

CREATE INDEX IDX_NAME ON USER(NAME)

Your code would work as well if you removed size while creating index:

ALTER TABLE `USER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `MAIL` (`MAIL`),
  ADD KEY `NAME_LINK` (`NAME`) USING BTREE;
   --NOT `NAME_LINK` (`NAME`(10))  !!
  ALTER TABLE CUSTOM_TRANSLATION add constraint fk1 foreign key (AUTHOR) references user(name);


flatten JSON array into comma delimited list

Unnest the array, then aggregate back:

select string_agg(city, ',')
from (
  select x.val ->> 'Name' as city
  from the_table t
     cross join jsonb_array_elements(t.the_column -> 'Cities') as x(val)
) t;

If you have a bigger query, use that in a derived table:

select string_agg(t2.city, ',')
from (
  select x.val ->> 'Name' as city
  from (
    select eo.citydata
    from orderline o
      join eventorders eo on eo.orderlineid = o.id
      join events e on e.id = eo.eventid
    where e.id = 123
  ) t1
     cross join jsonb_array_elements(t1.citydata -> 'Cities') as x(val)
) t2;

Alternatively - if you need that very often - you can create a function that does this:

create function get_element_list(p_value jsonb, p_keyname text)
  returns text
as
$$
   select string_agg(x.val ->> p_keyname, ',')
   from jsonb_array_elements(p_value) as x(val);
$$
language sql;

Then you can use it like this:

select get_element_list(eo.citydata -> 'Cities', 'Name')
from orderline o
  join eventorders eo on eo.orderlineid = o.id
  join events e on e.id = eo.eventid
where e.id = 123;


Stop never ending backup in SQL Server 2012 Express

Restarting SQL Service will kill backup process and then you can shrink. Below query will show you spid of backup process, if any running on server.

SELECT *
       FROM master.dbo.sysprocesses
       WHERE --dbid = db_id('YourDatabase') AND --if you want to see status for database
       cmd LIKE 'BACKUP DATABASE%'

Below query will kill backup process if you don't want to restart SQL service.

Declare @SPIDU Varchar(10), @Query Varchar(50);
SELECT top 1 @SPIDU = spid
       FROM master.dbo.sysprocesses
       WHERE --dbid = db_id('YourDatabase') AND --if you want to see status for database
       cmd LIKE 'BACKUP DATABASE%'
Select @Query = 'KILL ' + @SPIDU + ';'
Exec( @Query)

Make sure you do clear trans log by taking backup or else you wont be able to reclaim space from trans log file.


Supplying root password to MySQL V8 sort of fails

Step 1 : Add below parameter in my.cnf file under [mysqld] section.
skip-grant-tables
Step 2 :  Restart MySQL instance.
Step 3 : Login MySQL instance. (This will not ask you password)
mysql -uroot -p
Step 4 : Reset root password.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewRoot@123Pass';
Step 5 : Remove skip-grant-tables parameter from my.cnf file & restart MySQL instance.
Step 6 : Now Login MySQL instance using root user & new password.


Stored procedure for selecting table rows from a date range used to populate two other tables

I'm not sure why you need a recursive CTE. If you just need to split the data into two tables based on a date range you can do the following:

CREATE PROCEDURE FillTables
(
    @dateFrom DATETIME,
    @dateTo DATETIME
)
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRANSACTION;
BEGIN TRY

    -- GET ONLY THE DATA WE NEED AND CACHE IT
    SELECT doc_num, doc_date, shop_name, shop_address
      INTO #results
      FROM dbo.shipment_data
     WHERE doc_date BETWEEN @dateFrom AND @dateTo;

    -- ADD THE SHOPS TO GENERATE THE ID's
    -- (COULD USE OUTPUT HERE AS AN ALTERNATIVE)
    INSERT dict_shops (shop_name, shop_address)
    SELECT DISTINCT shop_name, shop_address
      FROM #results
    EXCEPT
    SELECT DISTINCT shop_name, shop_address
      FROM dict_shops;

    -- JOIN ON THE NEWLY CREATED SHOPS TO POPULATE SHIPMENT DOCS
    INSERT shipment_docs (doc_num, doc_date, shop_id)
    SELECT DISTINCT r.doc_num, r.doc_date, s.shop_id
      FROM #results AS r
      JOIN dict_shops AS s
        ON s.shop_name = r.shop_name
       AND s.shop_address = r.shop_address;

COMMIT;
END TRY
BEGIN CATCH

     IF @@TRANCOUNT > 0
        ROLLBACK;
     THROW;

END CATCH

END


Switching from SIMPLE to FULL recovery mode implications

There are lot of things I would like to say

  What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model.

As far as I know if you change recovery model of database during maintenance window time or when load is relatively less there wont be any problem. It wont create a situation.

  I'm also aware that as soon as we switch to FULL recovery that I need to run a full backup on that database. (I understand that FULL mode won't be truly enabled until we do this).

You can take differential backup also perhaps this would be very helpful if you have big database. anything that links LSN chains would do.See below link it has good information about switching between recovery models

http://msdn.microsoft.com/en-gb/library/ms189272.aspx

  My plan is also to weekly run a full backup, wipe the log files from the previous week and, essentially 'start over'.

Stop there, this is not correct approach just because you did a full backup and log backup please don't be in idea that you can safely remove old log files and full backup. Do you have a plan to test old backup by restoring to actually see that in case of disaster this backup files would work. Remember only successful restore guarantees that your backup is totally consistent. Do you have a option to check backup integrity.If not please include it into your backup plan. At least keep 4 day old backup files( this is what I do on local disk) before if you delete backups This strategy is upto you keep backups for time where you are sure they wont be useless if disaster strikes. Sometimes business want only specific data change to which was made couple of days. I also have my database backup up on tape and that tape is stored for 6 months.

  (maybe switch to BULK_LOGGED for that time period as to not explode the log file?)

Changing recovery model to bulk logged and doing bulk logged operation would make you loose point in time(PIT) recovery so if you are concerned about PIT recovery dont do that. Instead rebuild index through intelligent script which only rebuild fragmented index like one Is Ola Hallengren index rebuild solution please note that if Index is rebuild with full scan for that index stats is already updated with rebuild process.

If you do heavy DML please break it into batches so as not to explode log files.


DBCC CHECKDB Results: page ID contains an incorrect page ID in its page header

Never use REPAIR_ALLOW_DATA_LOSS.  I would do a database restore before that.

I suggest following this order:

REPAIR_FAST (works 1 of 10 times).
REPAIR_REBUILD (works up to 50% of the time).
Restore database.

Full syntax:

DBCC CHECKALLOC
[
    ( database_name | database_id | 0
      [ , NOINDEX
      | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    )
    [ WITH
        {
          [ ALL_ERRORMSGS ]
          [ , NO_INFOMSGS ]
          [ , TABLOCK ]
          [ , ESTIMATEONLY ]
        }
    ]
]


Does a primary key contain exactly one Null value...?

No attribute that is part of a key can contain nulls; if a column is nullable then it isn't part of any key. It is also the case that the set of columns defined by a UNIQUE or PRIMARY KEY constraint is NOT a key unless it is a minimal superkey and also non-nullable.
Why can't keys permit nulls? There are lots of potential reasons:

Keys are a relational concept whereas nulls (as we know them today) are essentially a SQL invention which came along years later. SQL DBMSs are different to Relational DBMSs.
Nulls are not values so they are not key values. Nulls are markers for unspecified values.
Keys are supposed to be irreducible (minimal) - in other words all the components of the key are required to make a superkey. Since null means that some components are missing it contradicts this most basic definition of a key.
In standard SQL a uniqueness constraint does not enforce uniqueness if any column permits nulls.
SQL DBMSs also permit nulls in other constraints (except PRIMARY KEY): FOREIGN KEY, CHECK, ASSERTION. Nulls never violate the constraint so the null acts as a kind of "get out clause" for data integrity. Hypothetically, if nulls were permitted in keys then that would seem to imply a stricter form of referential integrity that didn't permit nulls in foreign keys unless there was a corresponding null key in the parent table. In that case every nullable foreign key would have to reference a nullable key and could not reference a non-nullable one.
There are no nulls in reality, in everyday mathematics or logic; they are just a programming feature in SQL and are not particularly "mathematical" or even consistent in the way they work. Don't look for more reasons: nulls work the way they do just because the designers of SQL say so.


It is a bad thing to put backups and live files on the same san array (or even on the same SAN). Your first step should be to allocate new storage for the backups, copy the backups on to that storage and then verify at least one of those backups.  Then you should run check DB on the existing database to ensure it is not corrupt. After that create a fresh backup onto the separate storage for backups.  For extra safety I would also take the databases offline, copy the MDF and ldf files onto the backup storage or another separate storage unit and then, after the array rebuild, see if the databases will come back up and pass another check DB. If not, then you can restore the backup or copy the file copies you made from the separate storage back to the live disk. Oh and you should enable checksum on the backup if you havent already done that.  Having copies of the mdf and ldf files will give you a last resort option of data repair if the database was already corrupt.


Linked server - connection fail from remote computer (using ssms)

It is as you guessed a double hop issue.

Recreating the issue, trying to connect from my local SSMS, to the source server, and then connecting to a second destination server via a Linked Server "connection test" that is configured as "be made using the current security context".

Workaround #1

Implement kerberos authentication

The kerberos configuration manager is much easier than manually creating the commands

Download Link

More information on implementing kerberos Here

Also check out step 11 of This link:

  How do I set up Kerberos for linked servers?

  Set up SPNs for both instances. Set delegation to Trust this user for
  delegation to any service on the service account being used on the
  instance where the linked server is set up. Basically, the server that
  must pass the ticket along must be set up for delegation.

Workaround #2

Using a SQL Login to map to the windows login.

Step 1: Create the Login on the destination server and give it the needed rights

Step 2: Change Linked server mapping

Note: if you are adding an other account than the one you are currently creating the linked server with, then it will still throw an error.

Step 3: Test the connection again:


Table scans are not evil per se, it depends on what the query is supposed to do. If a large portion of the table is either returned to the application or used in some aggregate (like sum), it is probably most efficient to do a table scan.
If on the other hand, a small percentage of the table is to be used, looking up the rows via an index is much more efficient.
A scan uses disk resources as you mention. Another effect is that cached data in memory might have to be flushed to make room for the scan. I.e. applications may have to read data from disk instead of memory due to unnecessary scans.


Concatenate columns uniquely on the same table in MS ACCESS

Looks like you just need to add a WHERE filter to your query:

SELECT
  A, B, C, ...
FROM
  YourTable
WHERE
  D IN ('IA1', 'IA2', 'IA3')
GROUP BY
  A, B, C
;

That way those A, B, C groups that do not have 'IA1', 'IA2' or 'IA3' in column D will be excluded from the output.


Londiste replication fails intermittenly

Sorry for the answer, but I cannot comment....

You got the pretty straightforward message :

ERR: mydb_data_0: [ev_id=84594950,ev_txid=702851528] duplicate key value violates unique constraint "dmn_pkey"

It says you're trying to insert a value which is already present in the table.
You have to work on your enqueue/dequeue process to avoid duplicates.


Weak entity having n instances as the 'one'

You can combine B and C to one new key that is used in A and also have a key from B directly, it is no problem as you have two keys.
If you don't add something new to the key you would have the same key, but referring to different entities A and C.
Ok as long as you don't try to 3NF or higher, it is at least possible but redundant.

so C being weak can take n instances of A which is also weak and attached to the same owner?

Your relationship T is wrong. A weak relationship is always 1 to n or 1 to 1, never n:m. The essential part is that the weak entity has to know which key it relates to, see http://infolab.stanford.edu/~ullman/fcdb/slides/slides2.pdf, so if it where a 1:n , it is possible.


T.H.  Everyone has a their own logins.  I love your idea to try someone elses SS login from her machine.  Unfortunately the export was successful from her machine with my SS login and also a success from my machine with her SS login.  I say unfortunately because it now works from her machine with her own SS login.  The issue has resolved itself but I don't know why! :-(


SSIS: Move data between servers - will data travel through my workstation?

The data will travel through where the SSIS package is running. In dev this will be your workstation, once deployed this will be where ever your package(s)/project were deployed to. SSIS will not produce a direct connection as you describe between two data sources of any type

This is true even if there is no other processing involved beyond "read from here, write to there", and there are practical and security reasons for this. Practical reasons include mainly the fact that most data sources/sinks don't support remote connections to other resources like that, and the security concern would be that even if SSIS could instruct a data sink to directly talk to a data source (or vice versa) it would have to hand over security credentials in order for it to know how to.

Of course if your SSIS packages are deployed on one of the servers in question then there is effectively a direct connection, but the data will still go through the SSIS process rather than being sent directly to the SQL Server instance. Furthermore, even if you are moving data between tables in the same database (so the same database on the same SQL instance is both the source and destination of an ETL process) the data will still leave SQL Server, be processed by SSIS (however minimally) then sent back to SQL server.

If you need direct communication between SQL Servers then you can use the linked server feature to read/write data directly, but this would not allow SSIS's other processing features. Also this will not work on Azure SQL. There is a per table/view option that will work on Azure SQL (and newer on-prem SQL instances) in external data sources. Both these options require at least one of the SQL instances to have credentials for accessing the other, which may be undesirable depending on your situation and security model.