This should address the jist of your question.  You will need to check the aggregation to make sure it is appropriate for the data, though at a quick glance everything seemed to be good with SUM or Group.  You may want to replace the CTE with a real table as well.

;WITH
LLCCustNameMapping AS (
    SELECT * FROM (VALUES
        ('Wine Group', 'Other/Us Direct')
    )V(From, To)
)

  SELECT  [LoadDate]
       ,[Company]
       ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
       ,[ShipToCountryCode]
       ,IsNull(map.To,  [LLCCustName]) [LLCCustName]
       ,SUM([TodayGrossSalesUSD]) [TodayGrossSalesUSD]
       ,SUM([TodayGrossSalesEURO]) [TodayGrossSalesEURO]
       ,SUM([CurrMonthInvoiceUSD]) [CurrMonthInvoiceUSD]
       ,SUM([CurrMonthInvoiceEURO]) [CurrMonthInvoiceEURO]
       ,SUM([CurrMonthShipUSD]) [CurrMonthShipUSD]
       ,SUM([CurrMonthShipEURO]) [CurrMonthShipEURO]
       ,SUM([CurrMonthBudgetUSD]) [CurrMonthBudgetUSD]
       ,SUM([CurrMonthBudgetEURO]) [CurrMonthBudgetEURO]
       ,SUM([CurrMonthLinearUSD]) [CurrMonthLinearUSD]
       ,SUM([CurrMonthLinearEURO]) [CurrMonthLinearEURO]
       ,SUM([LastYearCurrMonthUSD]) [LastYearCurrMonthUSD]
       ,SUM([LastYearCurrMonthEURO]) [LastYearCurrMonthEURO]
       ,SUM([LastYearCurrMonthLinearUSD]) [LastYearCurrMonthLinearUSD]
       ,SUM([LastYearCurrMonthLinearEURO]) [LastYearCurrMonthLinearEURO]
       ,SUM([TodayOrderIntakeUSD]) [TodayOrderIntakeUSD]
       ,SUM([TodayOrderIntakeEUR]) [TodayOrderIntakeEUR]
       ,SUM([TodayVolume]) [TodayVolume]
       ,SUM([CurrMonthInvoiceVolume]) [CurrMonthInvoiceVolume]
       ,SUM([CurrMonthShipVolume]) [CurrMonthShipVolume]
       ,SUM([CurrMonthBudgetVolume]) [CurrMonthBudgetVolume]
       ,SUM([CurrMonthLinearVolume]) [CurrMonthLinearVolume]
       ,SUM([LastYearCurrMonthVolume]) [LastYearCurrMonthVolume]
       ,SUM([LastYearCurrMonthLinearVolume]) [LastYearCurrMonthLinearVolume]
       ,SUM([TodayOrderIntakeVolume]) [TodayOrderIntakeVolume]
       ,SUM([NextMonthVolume]) [NextMonthVolume]
       ,SUM([NextMonthGrossSalesUSD]) [NextMonthGrossSalesUSD]
       ,SUM([NextMonthGrossSalesEURO]) [NextMonthGrossSalesEURO]
       ,SUM([LastYearCurrMonthAsOfVolume]) [LastYearCurrMonthAsOfVolume]
       ,SUM([LastYearCurrMonthAsOfUSD]) [LastYearCurrMonthAsOfUSD]
       ,SUM([LastYearCurrMonthAsOfEURO]) [LastYearCurrMonthAsOfEURO]
       ,SUM([CurrMonthForecastVolume]) [CurrMonthForecastVolume]
       ,SUM([CurrMonthForecastUSD]) [CurrMonthForecastUSD]
       ,SUM([CurrMonthForecastEURO]) [CurrMonthForecastEURO]
       ,DayType
  FROM [BI_DB].[dbo].[DailySummary] s
    LEFT JOIN LLCCustNameMapping map ON s.LLCCustName = map.From
  WHERE [LLCCustName] IN ('Wine Group', 'Other/Us Direct')
  GROUP BY [LoadDate]
       ,[Company]
       ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
       ,[ShipToCountryCode]
       ,IsNull(map.To,  [LLCCustName]) [LLCCustName]
       ,DayType


I am also observing the same issues, I would suggest to please check the permissions to the mentioned path of my.cnf and the path mentioned by my.cnf.


AG groups don't sync user DB collation change to replica servers?

1) When we changed the user DB collation on server A, on server B, the user DB still shows the old collation. Is that by design? So we would need to redo AG to make the change show on the replica servers?

It may show the old collation, but it's been changed. Please note that changing the collation at the database level does not change it on any of the items underneath until it is specifically changed, however all new items will use that by default.

If you don't trust me (I'm a random internet person!) and you have a test system you'll see that this data is cached. Change the collation, then restart the secondary replica. Now check the collation. I do not know, though, why the metadata cache is not refreshed.

  2) When we change the columns and other objects' collation on server A, does that get changed automatically on the replica server through AG sync process?

Yes, assuming it's a database level object in a database in the AG.

  3) Is there any advantage of having server collation being SQL_Latin1_General_CP1_CI_AI while the DBs being Latin1_General_CP1_CI_AI ?

Depends, does you application require a specific collation or options? The advantage is for the application to sort and display data properly.

  4) Lastly, if the default DB collation is different than SOME of the table/column collation, would that be an issue for AG/DB in general?

It's not an issue for AGs, it's a potential issue for the application. The AG will happily send the log block changes all day.


How to resolve this SQL Server 2016 master data services authentication error on the web application?

Thanks to @sqlworldwide for pointing me to this question, which worked

https://stackoverflow.com/questions/23358903/troubleshooting-master-data-services-mds-permission-authorization


Specified key was too long; max key length is 1000 bytes in mysql 5.6

You can increase the maximum InnoDB index prefix size in MySQL 5.6 to 3072 bytes by setting innodb_large_prefix to ON along with other settings that you'll also need in order to enable that one, discussed here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

These changes should allow these indexes to be valid for InnoDB tables.

With a character set of (I assume) utf8, a VARCHAR(1024) would need 1024 x 3 = 3072 bytes for its index.

Updates:

I incorrectly showed the value of the variable as Yes when it should have been ON.

But also:

  I think it needs 3072+2 – ypercube

When I thought about this, it sounded correct, because 1 byte is needed to store the size of the value when the value is 255 bytes or less, and 2 bytes are needed otherwise.

However, testing reveals that this isn't the case, in this case -- InnoDB using the COMPRESSED row format from Barracuda can actually index the full size of a VARCHAR(1024)... so either they've documented it strangely or the COMPRESSED row format stores the length out-of-band along with another block of metadata, so it doesn't count in the total bytes in this format.

COMPRESSED doesn't truncate the index into a prefix index and throw a warning until you go to VARCHAR(1025).

+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+

It's nice the way it throws a warning instead of throwing an error, but that doesn't help us here because this still requires the explicit ROW_FORMAT declaration to trigger this behavior.

So, my initial answer is still wrong, because you have to explicitly add ROW_FORMAT=COMPRESSED to the end of the table definition.  Otherwise you still get the same old error.

Actually, you get two different errors in 5.6.10.  If you try to create a table with a fully-indexed VARCHAR(1024) you get this:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

...but if you try with a fully-indexed VARCHAR(1025) you get this:

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

That's sloppy code but the bottom line is that my answer doesn't actually fix this problem.

I don't see a way to use ROW_FORMAT=COMPRESSED by default, nor does it seem like a good idea if it were possible, and I'm inclined to suggest that the answer is...

...there's not a readily available workaround here.  My other thought was "character sets" but the difference between latin1 and utf8 still isn't sufficient to explain 1,024 vs 1000 or 767.  I'll happily get behind a better idea but at the moment, I can't think of a version of MySQL Server that this code would work properly on.


select  ara.ApprovalStatus
    from  `approval_request_approvers` as ara
    where  ara.ApprovalRequestID = rt.ApprovalRequestID
      and  ara.IsDeleted = 0
    order by  ara.ModifiedDate desc
    limit  1

is executed in 7 places.  See if you can make it only once.

And it could use

INDEX(IsDeleted, ApprovalRequestID,  -- in either order for WHERE
      ModifiedDate,                  -- 3rd; for ORDER BY
      ApprovalStatus)                -- for "covering"

You have some LEFT JOINs.  Could the be changed to JOIN?

Change from

or
            (
                select count(*)
                from ...
            ) > 0

to

or EXISTS ( SELECT * from ... )

For this:

p_createdBy is null or p_createdBy = ''

Consider deciding on NULL or '', then sticking with it.  This gets rid of one OR.

Any place where you use OR, see if you can reformulate the query.

Testing against NULL always fails in inequalities, so

(p_createdDateFrom is not null and p_createdDateFrom <= t.CreatedDate)

-->

p_createdDateFrom <= t.CreatedDate

The main WHERE has at least 11 tests AND'd together.  Are all of them TRUE most of the time?  If not, then rearrange them so that the most likely to fail are first.

Rather than passing in NULL/blank for things you don't want tested, have your client software construct the query with just the things you do want tested.  Then we could discuss other options for indexes.

Please provide EXPLAIN SELECT ... to discuss further.


Create two columns from one, based on two different WHERE clauses

;WITH x AS
(
  SELECT TOP (1000) Value, row_num = ROW_NUMBER() OVER (ORDER BY Value)
    FROM OnlineQnres.dbo.tmp_oDataSets
    WHERE VariableID = '1'
), y AS
(
  SELECT TOP (1000) Value, row_num = ROW_NUMBER() OVER (ORDER BY Value)
    FROM OnlineQnres.dbo.tmp_oDataSets
    WHERE VariableID = '4'
)
SELECT val1 = x.Value, val2 = y.Value
FROM x INNER JOIN y
ON x.row_num = y.row_num;

As I stated in a comment, for this correlation to be meaningful, you need to define how the ROW_NUMBER() is calculated (or indicate how these two sets of values are related). In this case it will correlate the smallest value for variableID = 1 with the smallest value for variableID = 2, the 2nd smallest value for variableID = 1 with the 2nd smallest value for variableID = 2, etc.

Also, as others noted, to get predictable results, you need to define TOP (1000) with a separate ORDER BY.

Essentially, if you have these rows in the table, and pretend you are only looking for two of each variableID:

VariableID    Value
----------    -------
1             foo
1             bar
4             mort
4             splunge

What does the result look like? Should it be:

val1    val2
------  -------
foo     mort
bar     splunge

If so, why? If not, why not? Or should it be:

val1    val2
------  -------
foo     splunge
bar     mort

If so, why? If not, why not? Or should it be:

val1    val2
------  -------
foo     NULL
bar     NULL
NULL    mort
NULL    splunge

Or something else entirely?

There are other query approaches too, but they depend on your requirements, and how the rows are linked other than the fact that they are "any" row that match the WHERE clauses.

Flimsy requirements lead to flimsy answers, sorry.


Troubleshooting Resource Governor / Classification Function

Make sure your group name returned matches (identically) to your actual workload group name. Some times, I've found, it's helpful to reconfigure even if it doesn't say it needs to be done.

use master
GO

-- Create a test DB which our CF will be based on
CREATE DATABASE RGDB;
GO

-- enable RG
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

CREATE FUNCTION dbo.RGClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    IF (ORIGINAL_DB_NAME() = 'RGDB')
        RETURN N'OriginalDBWG'

    RETURN N'Default'
END
GO

-- Create a new Pool
CREATE RESOURCE POOL OriginalDBPool
WITH (
MAX_CPU_PERCENT = 10
)
GO

--Create a new WG
CREATE WORKLOAD GROUP OriginalDBWG USING OriginalDBPool
GO

ALTER RESOURCE GOVERNOR WITH ( CLASSIFIER_FUNCTION = dbo.RGClassifier)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Open a new query window after running this and check the pool name by running:

SELECT wg.name FROM sys.dm_exec_sessions s inner join sys.dm_resource_governor_workload_groups wg on s.group_id = wg.group_id where s.session_id = @@SPID

Then, change the connection and add in an additional parameter

database=RGDB;

Then run the query again. You should see the workload group name change.


The most "normal" way to prevent database backup - simply ask persons who have permissions to backup databases, do NOT do any full backups (that are not copy_only) until migration is complete...


MySQL - How much physical space will be reduced by changing the column type?

I think TINYINT would be a good choice. I would suggest not indexing relationship_status at all. You should not index relationship_status by itself because the index cardinality is 2. The Query Optimizer would never use the index. You could index as follows:
ALTER TABLE my_table ADD INDEX (relationship_status,user_id);

That way, in the worst case, the Query Optimizer would perform an index scan on queries like:
SELECT user_id FROM my_table WHERE relationship_status = 1;
SELECT relationship_status,COUNT(1) statuscount
FROM my_table GROUP BY relationship_status;

The EXPLAIN plan should improve in that sorting and temp table usage would be minimized or eliminated.
Now concerning diskspace, let's compute the savings in space. First, What is the length of each string?
mysql> select length('following') followlength,length('not_following') notfollowlength;
+--------------+-----------------+
| followlength | notfollowlength |
+--------------+-----------------+
|            9 |              13 |
+--------------+-----------------+
1 row in set (0.01 sec)

Each VARCHAR has an additional byte for tracking string length. The values are really 10 and 14. That gets ignored since you are shrinking the field to 1 byte. If you collapse relationship_status to TINYINT, the savings would be as follows
SELECT
    BB SavingsBytes,
    BB/POWER(1024,1) SavingsKB,
    BB/POWER(1024,2) SavingsMB,
    BB/POWER(1024,3) SavingsGB
FROM
(
    SELECT
        fcount*length('following')+ncount*length('not_following') BB
    FROM
        (
            SELECT COUNT(1) fcount FROM my_table
            WHERE relationship_status='following'
        ) F,
        (
            SELECT COUNT(1) ncount FROM my_table
            WHERE relationship_status='not_following'
        ) N
);

Since the the number of rows is about 100M, the Savings in Diskspace would have to be between 900M and 1.3G.
UPDATE 2012-08-23 13:00 EDT
To answer your comment

lets say the table has 2 more columns, 1 which is varchar(255) and another one which is DATETIME, lets say I can change both column types to int(11) unsigned, how much disk space that would save?

First of all, a DATETIME is 8 bytes and INT(11) is 4 bytes. Since 8 - 4 = 4, your savings would be 4 bytes per row. For the table, SELECT COUNT(1)*4 FROM my_table;
Now, the varchar(255) field. As I mentioned before, a varchar has an extra byte for length management. Sum up all the lengths of all the varchar fields and add the row count (the extra from field on every row) and subtract 4 bytes times the row count.
Putting the DATETIME and VARCHAR calculations together, you get this
SELECT
    BB SavingsBytes,
    BB/POWER(1024,1) SavingsKB,
    BB/POWER(1024,2) SavingsMB,
    BB/POWER(1024,3) SavingsGB
FROM
(
    SELECT
        (VarCharBytes - (RowCount * 3)) BB
    FROM
    (
        SELECT SUM(LENGTH(varcharfield)) VarCharBytes
        FROM my_table
    ) A,
    (SELECT COUNT(1) RowCount FROM my_table) T
);


That is not an authoritative source.  Please follow Paul Randal's process here, as he's the one who "decided to add a new feature to SQL Server 2005 called EMERGENCY-mode repair".


You need to grant select on the tables in schema A to the user B WITH GRANT OPTION.

From the documentation:

  To grant SELECT on a view to another user, either you must own all of
  the objects underlying the view or you must have been granted the
  SELECT object privilege WITH GRANT OPTION on all of those underlying
  objects. This is true even if the grantee already has SELECT
  privileges on those underlying objects.


Why parameter for one query returning the same # of results as a different parameter, is extremely disproportionately slower

Look at the execution plans.

SQL Server will build different execution plans based on the different number of rows that it expects to return. (Even though the ACTUAL number of rows that may be returned could be the same, the ACTUALS could be different.)

To get the actual execution plans in SSMS, click Query, Include Actual Plan. Then you can compare the different plans to see if they have different shapes.

If you're using SQL Server Management Studio 2016, you can use the Compare Showplan feature to make that easier. (It works even for querying 2008 boxes.)

If you can share the execution plans publicly, use PasteThePlan.com to upload the two different execution plans, and then folks can point out what's different between them. (Be aware that this tool is completely public - everyone will see the full text of your query, and your parameters.)


SQL Server Linux Disable Remote Connections

Thanks to David Browne for pointing me in the right direction. Sometimes knowing what terms to search for is the hardest part. I also got direction from this article

So to change the SQL Server to listen on localhost or 127.0.0.1 all you have to do is add the following configuration to the file /var/opt/mssql/mssql.conf:

[network]
ipaddress = 127.0.0.1

then restart the server with:

sudo systemctl restart mssql-server

If you want to get a complete list of all configuration parameters you can run

/opt/mssql/bin/mssql-conf list

Note a sample of the output is like

hadr.hadrenabled                           Allow SQL Server to use availability groups for high availability and disaster recovery
language.lcid                              Locale identifier for SQL Server to use (e.g. 1033 for US - English)
memory.memorylimitmb                       SQL Server memory limit (megabytes)
network.disablesssd                        Disable querying SSSD for AD account information and default to LDAP calls
network.enablekdcfromkrb5conf              Enable looking up KDC information from krb5.conf
network.forceencryption                    Force encryption of incoming client connections
network.forcesecureldap                    Force using LDAPS to contact domain controller
network.ipaddress                          IP address for incoming connections

So you can add any of those to the /var/opt/mssql/mssql.conf file. The syntax is that the prefix before the dot goes in square brackets with the suffix listed below (see above for example).

To test it was working I checked:

1) That the applications using it were up

2) Running sudo nmap -PN <servername> to determine if the port was open

3) Trying to open a connection from a remote computer where I previously could with sqlcmd -S <servername> -U <username> which threw the error

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..


I resolved this following Kumar's comment:

  I fired up fresh Ubuntu 18.0.4 virtual box installed open jdk 8 and Oracle JDeveloper without any errors. From log files it looks like self extraction is failing something to do with parameter AuthParam and what exactly is that? What happens if you try without Authparam?

Just remove the AuthParam on that file.  Just Like this:

before:

jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427

after:

jdev_suite_122130_linux64.bin

And then install that as a usual way:

./jdev_suite_122130_linux64.bin


STRTOMEMBER function throws error about finding a dimension

Spotted the error - it was a typo..."Time Dimenson" has the word "dimension" misspelled. The error in this case was between the keyboard and the chair.


Is it possible to reduce a group to a row based on some criteria?

The traditional solution, the one you may find in books, is to do a self join: first find that "max date per group" of yours, then join to self table on rows with said max date.

However, some hacks allow you to avoid that. Consider the following query:

SELECT
  MAX(date_added) AS date_added,
  SUBSTRING_INDEX(GROUP_CONCAT(some_column ORDER BY date_added DESC), ',', 1) AS some_column,
  SUBSTRING_INDEX(GROUP_CONCAT(another_column ORDER BY date_added DESC), ',', 1) AS another_column
FROM t
GROUP BY whatever

GROUP_CONCAT is an aggregation function which implodes values onto one string. It allows for ORDER BY, which we utilize via ORDER BY date_added DESC so as to implode our desired value first. We then slice up the first token in the string via SUBSTRING_INDEX.

The downside here (apart from making the query quite the frightening appearance) is that your numerical values are transformed into texts. Typically no big deal with SQL, but please be aware.

See also my old post: Selecting a specific non aggregated column data in GROUP BY

There's another option where you do a semi-self-join, a much lighter one; you will have to give up usage of index. It's quite long to describe; it still uses GROUP_CONCAT and SUBSTRING_INDEX, but only for the purpose of creating a derived table with only relevant keys. This derived table is then joined with original table. See an example in SQL: selecting top N records per group, another solution.


INSERT INTO... SELECT over a LinkedServer in smaller batches

You could split them up in batches inside the dynamic SQL statement.

An example applied to your insert statement, per 1000 rows with the use of OFFSET ... FETCH :

EXECUTE (

'
DECLARE @RowsProcessed int = 0;
DECLARE @Batch int = 1000;
DECLARE @rowcount int = 1;

WHILE @rowcount!= 0
BEGIN

'INSERT INTO dbo.Notifications
(
    [Subject],
    [Body],
    [PriorityId],
    [StartDate],
    [EndDate],
    [IsActive],
    [UserId],
    [SourceNotificationId]
)
SELECT
    [Subject],
    [Body],
    [PriorityId],
    [StartDate],
    [EndDate],
    n.[IsActive],
    [DS.DataMigration].[Migration].[ufnGetNewUserId](n.[UserId]),
    [NotificationId]

FROM ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Notifications] n
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Users] u ON n.UserId = u.UserId
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Applications] a ON u.ApplicationId = a.ApplicationId
WHERE A.ApplicationURL = ''' + @ApplicationURL + '''
ORDER BY n.NotificationId
OFFSET @RowsProcessed ROWS
FETCH NEXT @Batch ROWS ONLY;

SET @rowcount = @@ROWCOUNT;

SET @RowsProcessed += @Batch;
END
')

Note that you have to add an ORDER BY with the OFFSET ... FETCH. I have chosen the NotificationId  field of the dbo.notifications table. You could change this if needed.

Here is a smaller working db<>Fiddle example.


How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)

The types of indexes in Postgresql are stored in the pg_am catalog table. So to get a list of all tables and their indexes with the index type (or access method ("am") as PostgreSQL calls it) you can run the following

SELECT tab.relname, cls.relname, am.amname
FROM pg_index idx
JOIN pg_class cls ON cls.oid=idx.indexrelid
JOIN pg_class tab ON tab.oid=idx.indrelid
JOIN pg_am am ON am.oid=cls.relam;


That's a tricky job for MySQL (PostgreSQL and other DBs) can handle it much easier.

For the sake of clarity, I've changed your tag.name to tag.tag and user.name to user.user_name, so that there's less risk of confusing the names. Note also that your data contains a user 4. I've called him James.

Let's do it one step at a time.

Step 1

First, let's get your expected output minus the tags:

SELECT
     u.id,
     u.user_name,
     coalesce(sum(r.score), 0) as score,
     coalesce(sum(r.reputation), 0) as reputation
FROM
    users u
    LEFT JOIN reputations r
        ON r.user_id = u.id
           AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
GROUP BY
    u.id, u.user_name
ORDER BY
    reputation DESC, score DESC ;

id | user_name | score | reputation
-: | :-------- | ----: | ---------:
 1 | Jack      |     0 |         18
 4 | James     |     1 |          5
 2 | Peter     |     0 |          0
 3 | Ali       |     0 |          0

NOTE: the condition r.date_time > 1500584821 must be part of your JOIN ON condition. If it is in the where clause, your LEFT JOIN becomes just an INNER JOIN, and you will lose users with no reputation.

Step 2

Now we make another query that returns, for every user_id, the list of tags and its corresponding reputation:

SELECT
    u.id, u.user_name, t.tag, sum(r.reputation) AS tag_reputation
FROM
    users u
    LEFT JOIN reputations r
        ON r.user_id = u.id
            AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
    JOIN post_tag pt ON pt.post_id = r.post_id
    JOIN tags t ON t.id = pt.tag_id
GROUP BY
    u.id, u.user_name, t.tag
ORDER BY
    u.id, tag_reputation DESC;

id | user_name | tag   | tag_reputation
-: | :-------- | :---- | -------------:
 1 | Jack      | css   |             15
 1 | Jack      | php   |              5
 1 | Jack      | mysql |              5
 1 | Jack      | html  |             -2
 4 | James     | html  |              5

Step 3

We LEFT JOIN the two together (after a smallish simplification of the second, to ignore user names there), ON user_id and GROUP BY user_id, user_name, score, reputation

SELECT
    q1.user_id, q1.user_name, q1.score, q1.reputation,
    substring_index(group_concat(q2.tag  ORDER BY q2.tag_reputation DESC SEPARATOR ','), ',', 2) AS top_two_tags
FROM
    (SELECT
        u.id AS user_Id,
        u.user_name,
        coalesce(sum(r.score), 0) as score,
        coalesce(sum(r.reputation), 0) as reputation
    FROM
        users u
        LEFT JOIN reputations r
            ON    r.user_id = u.id
              AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
    GROUP BY
        u.id, u.user_name
    ) AS q1
    LEFT JOIN
    (
    SELECT
        r.user_id AS user_id, t.tag, sum(r.reputation) AS tag_reputation
    FROM
        reputations r
        JOIN post_tag pt ON pt.post_id = r.post_id
        JOIN tags t ON t.id = pt.tag_id
    WHERE
        r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
    GROUP BY
        user_id, t.tag
    ) AS q2
    ON q2.user_id = q1.user_id
GROUP BY
    q1.user_id, q1.user_name, q1.score, q1.reputation
ORDER BY
    q1.reputation DESC, q1.score DESC ;

user_id | user_name | score | reputation | top_two_tags
------: | :-------- | ----: | ---------: | :-----------
      1 | Jack      |     0 |         18 | css,mysql
      4 | James     |     1 |          5 | html
      2 | Peter     |     0 |          0 | null
      3 | Ali       |     0 |          0 | null

Note that group_concat(q2.tag  ORDER BY q2.tag_reputation DESC SEPARATOR ',') does return all tags associated with the user. We cut to two by means of substring_index(string,separator,number).

You can find all the settings, and queries at dbfiddle here

Side note (ties when ranking): for user Jack, the php and mysql tags have the same tag_reputation. I.e.: there's a tie between second and third. In that case, and without a second criterion for sorting the tags, both css,mysql and css,php are acceptable answers for the "top two" tags.


How to solve "Can't connect to local MySQL server through socket" error?

Check the folowing

First check if another instance is runnig in the same machine

ps ax | grep mysql
Then, check if your file my.cnf (probably in /etc/mysql/ folder) is correctly configured with

[mysqld]
datadir = /var/lib/mysql/
socket = /var/lib/mysql/mysql.sock

Next, you have changed the default data directory, did you gave the right permissions to the new directory now

 chown mysql:mysql -R /var/lib/mysql/*
 chmod 755 -R /var/lib/mysql/*

After changing the permission restart the service and check

service mysql restart


Cannot access SQL Server - SQL Error 18456

You need to figure out what user does have rights to SQL server. Usually this is the user that you logged in with when you installed SQL Server or the user that is the default administrator on the computer.

I am assuming you have windows server 2012 and above.

I am write down some tricks to short out the Microsoft SQL Server , Error : 18456.

1. Launch Server Manager – Start – Click Server Manager . In the
    upper right corner, click Tools, then Computer Management. Expand
    Local Users and Groups (Under Computer Management-System Tools) then
    click Users

Notice under Description… There is a Built-in account for
administering… This is very likely an account that has access to SQL
server. Try logging onto windows with that account that is Built-in
account for administering then we can grant rights to the user you
want to use to login to SQL Server.

Now that you are logged into Windows with an account that has access
to connect to SQL Server, Let’s go in and grant rights to the user
that you want to be able to use to access SQL Server.

2. Launch SQL Server Management Studio again and you should be able
    to Connect. Expand your ServerName, then Expand Security, then
    Logins. Click Logins

Right-Click Logins Select New Login…

Click the Search Button

Type in the Windows User Name you would like to add as an SQL
Administrator then click Check Names (or you can click advanced and
select from a list)

Click Server Roles in the left pane; then turn on the sysadmin
checkbox, then click OK.

You can then confirm you have sa rights by double-clicking the user
name in the left Object Explorer and clicking on Securables.

Now you can log out of Windows, login as the user you just granted
rights to and all should work fine.

For your further ref : Here


Dealing with corruption: file-level backup vs. pg_basebackup

pg_basebackup and other methods like pg_dump might not actually finish due to the corruption, and you really need that backup in that case. Being independent of PostgreSQL and its tools, tar is not as likely to run into those issues.

In the case of corruption, that backup will be an invaluable insurance policy when you try to recover your data or do forensics, so playing it safe is always the best way to go.


adding another table to existing MySQL replication scenario

Are you using InnoDB? Xtrabackup will allow you to do "hot" or "online" copies of your running master.

Propagate the backup made via xtrabackup to the slave. There is no need to disrupt the relay (blackhole) server, just add the extra tables to be replicated to the relay server config before you "START SLAVE" on your slave.


Can't connect to MySQL Cluster Storage (data) nodes

Couple of things to check...

In your config.ini file, check that you haven't specified the host that is allowed to connect to this ndbapi slot - check the 'hostname' parameter in your config.ini file or run "ndb_mgm -e show". If you leave out the hostname parameter then you can connect the data node from any host
Firewall. Temporarily turn off your firewall and see if that fixes it. If you need to lock down the ports used by the data nodes then set the ServerPort parameter in the [ndbd] sections or the [ndbd default] section of the config.ini file. The firewall also needs to allow port 1186 (by default) for the management node.
You've filled up all of the API slots - run "ndb_mgm -e show" to check. If needed, add extra [mysqld] or [ndbapi] sections to your config.ini file and restart your nodes.


Something like this should work :

select nm_linha from linhas where cidades_origem like CONCAT('%', (subquery_goes_here) ,'%')

For example :

SELECT *
FROM films
WHERE titre LIKE CONCAT('%', (SELECT  'fella'),  '%')

+---------+------------+
| id      | titre      |
+---------+------------+
|       1 | Goodfellas |
+---------+------------+
1 row in set (0.01 sec)


Permission Denied for a User that is part of AD

As the comment by Randi Vertongen suggested, more than likely that user belongs to more than one group, one of which has explicit deny permissions on the object in question.

This query will show you which group(s) that user belongs to.

EXEC xp_logininfo 'DomainName\AD_User', 'all'

You can then look at the output for all of the results (making sure to also check for that specific user) to see if they have any deny permissions.


Best Practices for Separating Content from Presentation in SQL

It depends.  There is no one-size-fits-all answer.  Yes, a database is a persistent store for data -- focus on that when designing the schema.
Meanwhile, let me point out that
DATE_FORMAT(last_edit, '%d/%m/%Y') AS last_edit,

implies something that needs to be strongly stated:  Store the data in a generic format:

Store Dates and times in the database in a format suitable to the database.  This may require conversion both during input and output.  It facilitates filtering and sorting (as in your example, the typos notwithstanding).
Store text as UTF-8, not html-entites, latin1, etc.  This implies adding entities (or whatever) after SELECTing the data back out.
Monetary values stored as DECIMAL; scientific as FLOAT/DOUBLE. etc.
JSON can be handy for storing random attributes (think EAV) that don't need to be sorted/searched too much.

But, let's study a common example:  Should an "address" be split into address1, address2, city, province, country, postal_code.  Versus a directly printable address.
One rule for deciding is whether you will ever need to search/sort on individual pieces of the data.  Keep in mind that it is very hard and very inefficient to split apart fields for such purposes.
At the other extreme, would you even think about splitting up an image into pixels?  (I hope not.)
Now, back to your question.  Your example seems to have reasonable datatypes under the covers.  But your question is who does the formatting as it comes out.  You have given simple examples where it is quite simple to use SQL to do the formatting.  I would probably write it the way you did for a "simple" system.
If there is any chance of changing the formatting (eg, flipping and adding a comma: "last_name, first_name") you should probably leave that for later code.
For something more complex, it is better to postpone formatting.
Some things are messy in SQL, so it is better to leave for later code:

Pivoting -- You have 2-dimensional data stored in 1D.  (Follow tag [pivot-table])
Want to build tabular data but not duplicate the value (vertically) until it changes.  This is too cryptic to do in SQL; it is better to do in formatting code.

As for IF (private = 1, ' (Private)', ' (Public)'), consider switching from numeric to ENUM('public', 'private').


Make a newer version of the LDF-file work with an older version of the MDF-file

Is it possible to combine a "newer version" of the LDF file with an older version of the MDF file and keep changes logged in the LDF?

Assuming you want the database to be transactionally consistent, no.

  Just attaching the MDF and LDF gives an error

In this case it's because the log number is no valid, for whatever reason given the database file's metadata. This is a symptom of the exact process you've described. In this case it'd just be better to use the create database to regenerate the log (or DBCC commands) because either way the database isn't going to be consistent which it already wouldn't be with the above steps you've outlined. Six of one, half dozen the other.


The tasks initiated by pg_cron must be able to connect with the
parameters mentioned in cron.job, without a password.

If steps have not been taken in pg_hba.conf or through a .pgpass file
in the server's environment to allow for that, the task won't be able to run.

Concerning the log file, error reports would be expected there, but
pg_cron logs at the LOG level. Maybe it's below your current level
(see log_min_messages) or there's another problem.


If an 'after' DDL trigger causes an error, is the DDL rolled back?

The answer, at least on 11.2, is "It depends":

This create is rolled back:

create trigger trig_foo after create on schema
begin
  raise_application_error(-20001, 'Dont do it!');
end;
/
--
create table foo as select level as id from dual connect by level<=10000;
/*
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Dont do it!
*/
--
select count(*) from foo;
/*
SQL Error: ORA-00942: table or view does not exist
*/

But this truncate is not:

create table foo as select level as id from dual connect by level<=10000;
--
create trigger trig_foo after truncate on schema
begin
  raise_application_error(-20001, 'Dont do it!');
end;
/
--
truncate table foo;
/*
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Dont do it!
*/
select count(*) from foo;
/*
COUNT(*)
----------------------
0
*/


What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012

The short answer is that TABLESAMPLE is not guaranteed to return the number of rows you specify (or the %) because it bases this on pages and not on rows, and returns all of the rows from the pages it identifies. Unless your table only has fixed width columns, the pages pulled out based on % or number of rows could contain vastly different numbers of rows.

You can make this more consistent using the REPEATABLE option, but that still won't make it return the right number of rows. If you are simply trying to retrieve an exact number or percentage of random rows, use:

SELECT TOP (10) -- or 10 PERCENT
  columns
FROM dbo.table
ORDER BY NEWID();

Please read through this tip and this question and all the answers.


How to represent it with UNIQUE indexes and foreign keys?

This is a common problem. The "trick" is to:

add in Groups - besides the Primary Key - a redundant Unique constraint on (Topic, Group).
this allows you to add the Topic attribute in the Participates relation and
forces you to modify the Foreign Key constraint from Participates to Groups so it includes Topic, not only Group.
then you can add a Unique constraint on Participates (Pupil, Topic) (meaning a pupil can participate only once in a topic, not twice or more, which is the wanted result.)

Topics
  Topic  PK

Lessons
  Lesson PK
  Topic         FK -> Topics

Groups
  Group  PK UQ1
  Topic     UQ1 FK -> Topics

Pupils
  Pupil  PK

PupilParticipatesInGroup
  Pupil  PK UQ1 FK1 -> Pupils
  Group  PK     FK2 -> Groups
  Topic     UQ1 FK2

Appendix

 Abbreviation   Constraint
 ------------   -----------
   PK           Primary Key
   UQ           Unique
   FK           Foreign Key

Note that the design is in 3NF and that the redundant Unique constraint on Topics is only needed for the implementation in most DBMS (so the Foreign Key is enforced.)

The only other issue is the Group -> Topic dependency in the Participates relation which makes the schema to violate BCNF.

In the linked page, in paragraph Achievability of BCNF, you'll see that it has been proven that not all tables can be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. The provided example is striking similar to the Participates table here:

  In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. Beeri and Bernstein showed in 1979 that, for example, a set of functional dependencies {AB → C, C → B} cannot be represented by a BCNF schema.[6] Thus, unlike the first three normal forms, BCNF is not always achievable.

Lets make one more change nonetheless, in order to remedy that (if we can!):

Lets examine what happens if we remove (as if it never existed) the Primary Key (Group) constraint from Groups (and put the Unique in its place.) Lets also change the Primary key in Participates - notice - to be exactly the Unique constraint that is our target from the beginning. The design becomes (first two tables omitted as they remain intact):

    Groups
      Group  PK
      Topic  PK     FK -> Topics

    Pupils
      Pupil  PK

    PupilParticipatesInGroup
      Pupil  PK     FK1 -> Pupils
      Group         FK2 -> Groups
      Topic  PK     FK2

No redundant columns at all. Magic! The Participates is a common many-to-many relation between Groups and Pupils (as our model wants) and the two Foreign Keys are referencing the Primary Keys of the corresponding tables.

So, what happened? Magic? (not really, no) And isn't the Group still a candidate key in Groups? (removing it was only a thought experiment anyway.) Don't we lose something by omitting this constraint from the actual tables?

The issue - and why the above is solving it - is that the Unique constraint you are trying to model needs the Topic attribute in the Participates relation. So, you have to use a candidate key in Groups which is compound and includes Topic. It doesn't necessarily have to be (Topic, Group). It could be some other combination. What other attributes does Groups have?

If, for example, there is an attribute OrderNo in Groups and (Topic, OrderNo) is unique, then we could have this design (at last!) where there is no redundancy, no superfluous attributes, all constraints are there and the schema is in BCNF:

    Groups
      Group   PK
      Topic      UQ1 FK -> Topics
      OrderNo    UQ1

    Pupils
      Pupil   PK

    PupilParticipatesInGroup
      Pupil   PK     FK1 -> Pupils
      Topic   PK     FK2 -> Groups
      OrderNo        FK2


It can be done with a simple join (assuming the (user_id, follower_id) combination has a unique constraint):

SELECT
    f1.followed_id AS friend_id
FROM
    follow AS f1
  JOIN
    follow AS f2
      ON  f1.user_id = f2.followed_id
      AND f1.followed_id = f2.user_id
WHERE
    f1.user_id = 36 ;


Insert/Update SQL Server Stored Procedure

As a very generic answer, if you want to test code of this nature without actually changing the data then it is possible to run your code, query the results, and then undo any changes before they are committed.

-- Begin a new transaction
begin transaction

    -- Show initial data
    select * from [dbo].[employee]

    -- Your statement to change the data
    MERGE [dbo].[employee] AS [Target]
    ....
    ....
    ....

    -- Show the new data
    select * from [dbo].[employee]

-- Rollback the transaction, leaving the data in its original state.
rollback transaction


visual studio database project - publish did not include drops but schema compare did

Publish>Advanced Settings>Drop>"Drop objects in target but not in source"

https://stackoverflow.com/questions/1799802/dropping-a-table-with-visual-studio-2010-database-project


Force a Member to Become Primary in mongodb

The reason nms02 becomes Primary when it joins the replicaset is due to having the higher priority setting.
According to the documentation:

Specify higher values to make a member more eligible to become primary, and lower values to make the member less eligible.
snip
A priority of 0 makes it impossible for a member to become primary.


Why does JSONB show a different date format than Javascript?

With ECMA-262 v6, the Z must be present in the result of .toISOString if a time zone is provided, you do not need millisecond precision. Javascript on the other hand has no date types to begin with. Both of these formats are ISO 8601. PostgreSQL doesn't specify a Z and it gives only the precision provided. Nothing prohibits that.

The Date() constructor will gladly accept both formats, and you should be using Moment.js for all your date time needs in Javascript. You should be immediately constructing date objects with the output of PostgreSQL. This may not be done for you, it also may be something you have to do as JSON doesn't know the difference from a ISO 8601 date/time object, and a string that looks exactly like it. From that point, it's out of PostgreSQL's hand. All that PostgreSQL ensures is that the output is valid JavaScript Date() input, which is above and beyond because the JSON spec doesn't even require that.

ECMA-262 v6 spec,

The ECMA-262 v6 spec says,

  Date.prototype.toISOString() This function returns a String value representing the instance in time corresponding to this time value. The
  format of the String is the Date Time string format defined in 20.3.1.16. All fields are present in the String. The time zone is always UTC, denoted by the suffix Z**. If this time value is not a finite Number or if the year is not a value that can be represented in that format (if necessary using extended year format), a RangeError exception is thrown.

That says this,

  Z is the time zone offset specified as "Z" (for UTC) or either "+" or "-" followed by a time expression HH:mm

  This format includes date-only forms:

YYYY
YYYY-MM
YYYY-MM-DD

  It also includes “date-time” forms that consist of one of the above date-only forms immediately followed by one of the following time forms with an optional time zone offset appended:

THH:mm
THH:mm:ss
THH:mm:ss.sss

JSON Spec

The JSON spec says nothing about date formatting. In fact, there isn't even a date type mentioned. This is probably because Javascript dates are objects not primitives.

PostgreSQL

PostgreSQL outputs only the minimum precision provided to the constructor, and without the z (still in ISO 8601).

SELECT to_jsonb(dt::timestamp with time zone)
FROM ( VALUES
  ('2018-02-01T13:24:36.466849-06:00'),
  ('2018-02-01T13:24:36-06:00');


Lock time not involved in MySQL slow query selection?

Confirming what you already know, for future reference:

"The time to acquire the initial locks is not counted as execution time."
— http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html

It stands to reason, at least to me, that a "slow query" is best defined as a query that is poorly-optimized and takes longer than it should even when run in isolation.  It follows from that, that queries which only coincidentally take a long time to run because of circumstances unrelated to the query itself -- such as waiting for table locks -- should not be included in the slow query log.
I base this on two things: intuition, since the slow query log can also "log_queries_not_using_indexes" -- which is presumably also intended to catch bad optimization before the data set grows to the point that the queries actually turn into "slow" queries... and the fact that it doesn't seem to have been designed that way just because it was easier to implement or for any architectural limitation.
In sql/sql_class.h:
void update_server_status()
  {
    ulonglong end_utime_of_query= current_utime();
    if (end_utime_of_query > utime_after_lock + variables.long_query_time)
      server_status|= SERVER_QUERY_WAS_SLOW;
  }

If utime_after_lock were replaced with start_utime ... that should drive the behavior you are expecting.  YMMV, but that's what it looks like from here, from a brief look into the source code.
Alternately, you could write a script in your favorite language to hold a connection to the server and execute SHOW PROCESSLIST every "n" seconds.  The script would "remember" what each thread's Time value was from iteration to iteration, and could report execution times to you with the granularity of the polling interval when it found interesting patterns.  When it sees an interesting query, it could switch to SHOW FULL PROCESSLIST to get the entire body of the query.
I would poll my servers for their processlist (not "full") at 1 second intervals -- yes, in production -- and display the number of threads and the current longest-running query on my real-time status dashboard and have never seen evidence of an ill-effect from this.


It continues to become suspect after. Please provide me the permanent
  solution.

The permanent solution is to figure out WHY/WHAT is causing the database to go suspect. Is it a filter drivers, bad hardware, etc. We don't know and can't tell you, this is something you'll need to inspect on your own. You'll want to get your sysadmins involved as well.

  Many times I have recovered it with: ALTER DATABASE Your_Db SET
  EMERGENCY DBCC checkdb('Your_Db') ALTER DATABASE Your_Db SET
  SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('Your_Db',
  REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE Your_Db SET MULTI_USER

Please, don't ever use this as the first line to attack - unless you don't like your job and are looking elsewhere for employment.

Check to see what is being corrupted, how bad it is, the extent. If you aren't sure, hire a consultant that specializes in corruption.


Flat file manager TextQualifier not working

It seems a bug, I have found: SSIS flat file parser does not read Column delimiters embedded in text data. Work around: using some exotic separator.


Why not use SQL_Latin1_General_CI_AS for a global system?

Collations in SQL Server handle several aspects of string data:

Locale / LCID (referring to the Culture: en-US, fr-FR, etc)

This is used to determine culture-specific overrides to the default linguistic sorting and comparison rules used by Unicode / NVARCHAR data across all Collations as well as non-Unicode / VARCHAR data for Windows (i.e. non-SQL_) Collations.
Code Page

This is the character set used for non-Unicode / VARCHAR across all Collations. To be clear, Code Pages do not apply to Unicode / NVARCHAR data as Unicode is a single character set. And to be super clear, Unicode is a single character set regardless of how it is encoded: UTF-8, UTF-16, or UTF-32.
Sensitivity

Case and Accent sensitivity can be controlled across all Collations. Kana and Width sensitivity can only be controlled when using the Windows Collations and are assumed to be "insensitive" when using the SQL_ Collations.

Also, all of the Windows Collations should have a binary option (at least the deprecated _BIN, if not also _BIN2) whereas there are only two SQL_ Collations that have the _BIN / _BIN2 options: SQL_Latin1_General_CP850 and SQL_Latin1_General_CP437.
Ability to handle Supplementary Characters

A set of Collations with names ending in _SC were added in SQL Server 2012. These allow for proper sorting / comparison as well as handling by built-in functions for UTF-16 Surrogate Pairs (which is how UTF-16 encodes Code Points above U+FFFF). This option is not available to any of the SQL_ Collations.

Please note that regardless of Collation, all UTF-16 data can be stored and retrieved without any data-loss in NVARCHAR / NCHAR / XML columns and variables, even if the Collation does not allow for properly interpreting Supplementary Characters.

Also, there are some behavioral differences for non-Unicode / VARCHAR data only when using the SQL_ Collations:

ASCII value 0 / CHAR(0) does not equate to an empty string.
When sorting data using a case-sensitive Collation (e.g. SQL_Latin1_General_CP1_CS_AS), upper-case characters will sort ahead of lower-case characters.
String-sort is used, which sorts all punctuation ahead of all letters, instead of Word-sort, which ignores dashes and apostrophes within words (e.g. a-f sorts before aa using String-sort, but after it when using Word-sort).
No culture-specific character expansions are done (e.g. 'æ' = 'ae')

There is, however, behavioral consistency between NVARCHAR data using any collation and VARCHAR data using a Windows Collation.

Hence, ideally, the SQL_ Collations shouldn't be used given the above restrictions and differences, not to mention that they are also deprecated (and there are only 77 of them and 3810 Windows Collations as of SQL Server 2014). If anything, try to use the most recent version of a particular Collation (e.g. _100_), and if offered, use one ending in _SC.

Unfortunately, SQL_Latin1_General_CP1_CI_AS is the default when installing a new instance in the US (at least). But one should not willingly pick a SQL_ Collation for new development, especially when needing to deal with multiple cultures.

But to answer the 4 additional questions:

  How is Japanese_CI_AS different from SQL_Latin1_General_CI_AS when it comes to sorting non-Japanese characters?

This is with regards to NVARCHAR data only, right? The LCID determines which culture-specific overrides to apply to the default sort options. I would suspect that the US-English characters will sort the same between Japanese and Latin1 Collations, but I am not sure if that holds true for other languages that use those characters as well, or for characters not found in US-English, such as letters with accents. And an added complication is that you have both letters already with accents and then letters without the accents combined with just the accents (i.e. combining characters) and those things might not equate the same across the English / German / Japanese / Chinese locales.

  Does UCA sort differently than Japanese_CI_AS and SQL_Latin1_General_CI_AS?

I am not sure this question makes sense. There is a default sort order given to all characters. Then each locale can override (either replace or remove) any of those defaults or add new rules. So the UCA is the base weights for characters, but then each culture can deviate from those defaults. Hence there will be a large amount of overlap in the rules, but also a large amount of variation between them.

  What is standard industry practice for systems used globally? (For example, what does Facebook, Twitter, Google, Flickr, Baidu or Microsoft, IBM, and SAP use?)

Not sure what those companies do exactly, but I doubt it is pre-indexed with culture-specific linguistic rules. At least not across ALL data. Most sites do ask for your preferred language and might use that to handle some of the data. Either way, there is no way to have a single, truly culture-independent sorting.

  Does SQL_Latin1_General_CI_AS define a sort order for Japanese characters? If not, how/why does Japanese text work in SQL_Latin1_General_CI_AS?

Not sure what is meant by Japanese text "working", but Unicode is a single character set for all languages. So, the ability to store the characters of a particular language does not imply the rules by which those characters will be sorted.

As mentioned above, the UCA is a default sort order for all characters. The Latin1 Collations can do basic sorting across all languages (in terms of Unicode / NVARCHAR data), but it would only have the default rules. The Latin1 Collations wouldn't have any culture-specific rules, and there can be multiple sets of those rules. Also, as stated above, the SQL_ Collations do not have the ability to turn on Kana or Width sensitivity, which you might need.


Case-insensitive collation still comparing case-sensitive

The version of ICU shipped with the Windows builds is a pretty old one, so maybe that's the reason.

Try

CREATE COLLATION collat_ci (
  provider = 'icu',
  locale = '@colStrength=secondary',
  deterministic = false
);

This should work with older ICU versions.


VLDB - Create SQL DB Snapshot And Run Granular DBCC CHECKDB Commands

I think Robert Davis answered your question in two posts:

How to manually create a database snapshot and get SQL Server to use it for CHECKDB:

http://www.sqlsoldier.com/wp/sqlserver/day1of31daysofdisasterrecoverydoesdbccautomaticallyuseexistingsnapshot

Performance comparison of CHECKTABLE commands run against database vs manually created snapshot w/ different options:

http://www.sqlsoldier.com/wp/sqlserver/day15of31daysofdisasterrecoveryrunningdbccchecktableinparalleljobs


SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Tweaking is left as an exercise to the requester.

Using PIVOT and UNPIVOT

SELECT
    ProjectId,
    [95] AS [ExecutiveChampion],
    [96] AS [...],
    [97] AS [...],
    [100] AS [...],
    [101] AS [...],
    [102] AS [...],
    [103] AS [...],
    [104] AS [...],
    [105] AS [...]
FROM (
    SELECT
        pl.ProjectId,
        StakeholderCID,
        FullName
    FROM @pList pl
    INNER JOIN StatusCode sc
        ON 1 = 1
            AND SCID IN (8, 9)
    LEFT OUTER JOIN ProjectStakeholder ps
        ON pl.ProjectId = ps.ProjectId
            AND sc.CID = ps.StakeholderCID
) AS SourceTable
PIVOT (
    MAX(FullName)
    FOR StakeholderCID IN ([95], [96], [97], [100], [101], [102], [103], [104], [105])
) AS PivotTable;


I think you can calculate each 15 min time slot before doing transaction backup, because after transaction backup, the previous transaction logs will be rewriteable, and that will be the maximum of the transaction log file size.

DBCC Shrinkfile sometimes could not work well because there are some transaction not write down to database already (transaction will be wrote to log file first and    then write to database), you can wait a while and DBCC again.


Using OPENDATASOURCE to insert mdb data into SQL Server

This is what worked for me:
Add full permissions to Everyone on C:\Users\SQL Service account name\AppData\Local\Temp

Here's the full thread:
https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null


How to balance SQL Server memory with SQL Server Analysis Services memory

I think it is feasible to force both services' hands by having a scheduled task or service that:

(a) before SQL Server's nightly processing, shuts down the SSAS service, and increases the memory allotted to SQL Server

(b) after SQL Server has done its processing, reduces max server memory and restarts SQL Server

(c) starts SSAS

This assumes that SSAS isn't required to be online while SQL Server is processing. Otherwise change (a) from "shuts down" to "restarts"... as long as SSAS isn't actively doing anything while SQL Server is processing, there shouldn't be any concern that it will steal memory back.

This also assumes that you want each service to overlap memory consumption, and use the same memory at different times - I'm sure there are ways with affinity / NUMA etc. to completely segregate their operations space or, in the worst case, to host them on different machines.


How do you capture the output message from a successful MERGE statement when using it in a procedure?

You can use SQL%ROWCOUNT. This return rows processed in last sql statement
For example:

declare
res number;
begin
select 0 into res from dual;
dbms_output.put_line(sql%rowcount || ' rows selected');
end

This code prints 1 rows selected because you selected one row. I hope this helps


Why does ENCODE on BYTEA not seem to have any performance impact?

encode() is a very cheap function. I would not expect any measurable impact at all  in your test.

The difference is almost certainly due to the much smaller storage size of bytea as compared to char(64). Consider:

SELECT pg_column_size('90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271'::char(64)) AS size_char64
     , pg_column_size(decode(text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271', 'hex')) AS size_bytea;

 size_char64 | size_bytea
-------------+------------
          68 |         36

The dominant factor for performance of simple SELECT queries is the number of data pages that have be read.

"Best" data type?

  Obviously the choices are BYTEA and CHAR(64).

If your goal is to optimize performance, consider a third option:
2 uuid columns

To understand, first read:

What is the optimal data type for an MD5 field?

And:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Then consider this demo (executed on pg 11, but true for all modern versions):

RAM size:

SELECT pg_column_size(t64)                   AS c_text
     , pg_column_size(t64::char(64))         AS c_char64
     , pg_column_size(decode(t64, 'hex'))    AS c_bytea
     , pg_column_size( left(t64, 32)::uuid)
     + pg_column_size(right(t64, 32)::uuid)  AS c_2x_uuid
FROM  (SELECT text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271') t(t64);

 c_text | c_char64 | c_bytea | c_2x_uuid
--------+----------+---------+-----------
     68 |       68 |      36 |        32

Disk size (compressed format):

CREATE TEMP TABLE c64 AS
SELECT t64                    AS c_text
     , t64::char(64)          AS c_char64
     , decode(t64, 'hex')     AS c_bytea
     , left (t64, 32)::uuid   AS c_uuid1
     , right(t64, 32)::uuid   AS c_uuid2
FROM  (SELECT text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271') t(t64);

SELECT pg_column_size(c_text)    AS c_text
     , pg_column_size(c_char64)  AS c_char64
     , pg_column_size(c_bytea)   AS c_bytea
     , pg_column_size(c_uuid1)
     + pg_column_size(c_uuid2)   AS c_2x_uuid
FROM   c64;

 c_text | c_char64 | c_bytea | c_2x_uuid
--------+----------+---------+-----------
     65 |       65 |      33 |        32

db<>fiddle here

The seemingly minor difference between 33 and 32 bits can actually make a difference of 8 bytes, because several storage mechanisms require padding at multiples of 8 bytes.

Calculating and saving space in PostgreSQL

Repeat your test with 2 UUIDs. I am confident it will come out on top.


How to Get the Last Day of the Previous Month?

This one works in SQL Server 2008 R2, will probably work on SQL Server 2008 too :

DECLARE @DSL [DATETIME]
SET @DSL = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1))
PRINT 'Declar variabila pentru ultima zi din luna anterioară.'
PRINT @DSL


Backing up Oracle home directory while instances are running

Yes you can do that. There's nothing dynamically changeable here in normal circumstances. All the files here are not touched except in case you apply some patch.

Files specific to the particular database resides in:

$ORACLE_HOME/dbs
$ORACLE_HOME/network/admin

I do that now and then to create test environment. Just for such more complex directory structure I prefer rsync. With it you can resume copying if you run into some disk space or network issues.


How to show multiple record if has top 1

Top is only giving you one record because you're asking for the top '1' row.  If you want all, just change it to:

select (md2.table2) from table2 md2
where md2.EFFECTIVEDATE < '2019-12-05'
and md2.key2 in (select key2 from table1)
order by EFFECTIVEDATE desc


optimize the query with big and low cardinality

To check exactly how selective your filter is for your particular query, you could execute the following queries:

SELECT
   (SELECT count(*) FROM indi WHERE sex = '2')
   /
   (SELECT count(*) FROM indi)
   * 100
   as selectivity;

SELECT
   (SELECT count(*) FROM c_loc WHERE location_id IN (3,4,5,6))
   /
   (SELECT count(*) FROM c_loc)
   * 100
   as selectivity;

The first query will probably return something around 50% in most applications, the second will give you around 1%, if the locations are more or less equally distributed, but it can vary.

Having a low cardinality is not per se a bad thing, the problem is when you need to search only on that column in a efficient way with an index. Having low cardinality means that a full table scan may actually be faster. The limit depends on many factors, and the MySQL optimizer is a bit more complex than a simple percentage calculation, but here you have a real example of when an index is no longer useful:

When the selectivity is higher than around 20%, the index tends to be no longer useful for filtering.

There is no "real" solution modifying the query. For those cases, you may need a different method for indexing, different than the standard BTREE algorithm. In most cases, you can provide a solution on MySQL using partitioning (even doing it manually, storing on a different table males and females, for example).

For your particular case, the index is still being used, which means it may be still good enough, and you may not need to do any additional work, but I though it was important to understand the consequences of low selectivity, and playing around with indexes to see if you were getting an optimal query plan. However, if the final number of rows is relatively large, there is no good solution: if you select a lot of rows, it will take a lot of time to do it. If your final result set is relatively small, try to force the join order with the most selective clauses at the beginning, by using STRAIGHT_JOIN, if the logic allows it. This is dangerous without proper monitoring, because cardinality may change with time, and so does the selectivity for the same query just by using slightly different parameters.


Watch "Alter Table" statement changes in Mysql database

Just some ideas:

Set a trigger on INFORMATION_SCHEMA

Not possible, according to docs:

  The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Monitor the DDL log

I wouldn't go down that road (docs):

  The DDL log, or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE. [...] This is a binary file; it is not intended to be human-readable...

Use events and track changes yourself

You could keep a copy of the relevant INFORMATION_SCHEMA tables, schedule a recurring event and check for any difference between your copy and the current state.

You would need a very strong use case to justify the effort to develop this, I think.

Track the binary log or general log

If you just want to get a quick heads up, when the schema has changed, you might try to set up a cron job and enable either (a) binary logging to use mysqlbinlog to check for ALTER TABLE statements or (b) to enable the general log and do the same.
You could then send yourself an email, when that happens.

Might be more or less reliable than trusting the developers to notify you - depending on your implementation and on the developers ;)


Multidimensional databases on SQL Server 2008 R2?

On the trials and tribulations of making generic B.I. products

For the moment I'm assuming that the physical structure of the data model remains the same and you just want to change fields and their definitions to site-specific customisations.  If you want to frig the database structure you're really into building bespoke systems.  The other assumption that I'm going to make is that you have a star schema.

This can be done with metadata for each of the dimensions and fact tables that has the attributes for each table.  This metadata allows you to construct a customised view over a generic base structure.  You will have to structure the ETL to map to the base data but you can configure a customised view over it.

There are a couple of ways you can manifest the base tables: generic columns and an entity-attribute-value structure.  In each case, the customer's view is customised by metadata stored in the system.

Generic columns

A table with generic columns will have columns with names like 'Code1', 'Code2' etc. and groups of columns of particular types (code, money, float, int etc.).  You implement a table like this for every dimension in your system, and a table with some integer and money values for each of the fact tables.

This is customised by creating a view that links the columns to a business facing name.  Te metadata is used to generate the view.  If you have cubes and/or a suite of reports sitting on top of this, you can also programatically annotate the dimensions and report fields based on the metadata, allowing them to be productised but customisable.

The view is simply a view over the base table that renames the columns.

Advantage: The structure is relatively efficient to query, even through the views.
Disadvantage: The tables have a finite number of columns, so you may need to alter them if you run out.
Disadvantage: The meaning of the underlying data is quite opaque without the view, which doesn't help the readibility of the ETL code.

Entity-Attribute-Value

An entity-attribute-value structure has a 1:M relationship against the base table.  The relationship has the attribute type (from a reference table) and the value against the parent entity's primary key.

You have a base table, a reference table for the attribute names and types and a link table that records the value for each attribute against the entity.  It is commonly used on software products.  The view has to calculate a pivot or similar function to flatten the data out and can be generated from the attribute list for the entity type.

Advantage: Infinitely flexible - you can add attribubtes by updating a reference table, although you will need to modify the ETL code to populate it.
Disadvantage: Relatively inefficient and fiddly to query directly.  You may want to build a supplementary process to produce a flattened view.  The code that does this can be generated from the metadata, though.

Similar things can be done with base metrics on the fact tables.

A bit of experimentation will show you how to programatically annotate columns onto a cube dimension or report model, so it is possible to make a template and use a metadata based system to synchronise the cubes or front-end artifacts by generating columns.

If you build reports in a templatable way (base columns with particular signatures) you may also be able to do something similar with canned reports if you have a significant body of these as a part of your product.  This might be getting into diminishing returns, though.

One golden rule:

Never, ever edit generated code, or make an architecture that relies on subsequent human intervention in generated code.  That way lies madness.


Blue icon with question mark - what does it mean?

Enabling these Windows Firewall rules did the trick for me

Windows Management Instrumentation (WMI-In)
Windows Management Instrumentation (DCOM-In)

These two rules are predefined and you can enable them by right clicking and enabling these rules in Windows Firewall advanced settings

I Also opened port 135 in the windows firewall, but I don't know if this was really neccessary.

I now confirmed it for Windows Server 2016 and Windows Server 2019 and SQL Server 2017


Take a step back and ask, why are we copying log files?  SQL Server LDF files contain transaction log data, but they're not terribly useful by themselves.  To work with a database, you need both the data files and log files.  In order to access those files directly via the file system, though, you'll either need to stop SQL Server or use snapshot technologies like VSS.

If you're trying to synchronize transactions across multiple databases, check out technologies like transactional replication.

If you're trying to back up the database, use the SQL Server BACKUP DATABASE command.


Help with designing a multi relational schema / domain model

It might help if you also had some table descriptions in your question, but here goes...

  A network has many blocks.

You could do this very simply with a network_id in each block. That way there is no restrictions on how many blocks can reference a network, but you ensure that each block is only in one network.

  Each block belongs to a single client. A client may have many blocks in one network, and can also have many blocks in many networks.

Having a client_id in your block would ensure that each block can only reference one client, but a single client could be referenced by many blocks (and it won't matter which network they're in). So maybe your block table needs to look something like this:

block
-----
  ID
  client_id (FK to client.id)
  network_id (FK to network.id)
  [other fields...]

  I also then need to be able To browse a client and query all his blocks which he has

The SQL for that could look something like:

select client.name, client.id, block.id
from block
inner join client
on client.id = block.client_id
where client.id = $SOME_PARAMETER_FOR_CLIENT_ID


New PostgreSQL 9.1 configuration doesn’t work

pg_config_manual.h is not a configuration file, it's part of the source code. After a change in the source code, you have to compile all the code and install the program you just compiled.

Changing the max length for names is not trivial.


Directly acessing the NULL bitmap of a row in SQL Server

No, there isn't. Besides, this is a very very very misguided effort. Since you've already loaded the page in memory, you have already paid the price for any row and any column on that page. This is a given for any row oriented storage. The price for fetching the page overwhelms the price of looking up values in the page.

If you want to optimize for retrieving the value (including whether it is NULL) for only a specific column then what you are asking for is columnar oriented storage, which is an entirely different game.


Is there any way to temporarily enforce columnar uniqueness on items currently being inserted?

You could use INSERT IGNORE. This would reject any INSERTs where the primary key or unique key already exists.

If you have to update non-keyed columns in a table in the event that the primary key or unique already exists, you can use INSERT ... ON DUPLICATE KEY UPDATE. I demonstrated how to use this in another question in the DBA StackExchange.

For your particular query, please make sure the title column is either the primary key or has a unique key defined. Then, INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE will work for you.

Give it a Try !!!

UPDATE

If the payments table is not too big, try this crazy ideas

Create a UNIQUE INDEX on this one-off

ALTER TABLE payments ADD UNIQUE INDEX title_amount_due_date (title, amount, due_date);

Perform your INSERTs IGNORE from the multiple servers. When done...

ALTER TABLE payments DROP INDEX title_amount_due_date


Inserting variables into another variable

From the comments it sounds like you already have this fixed but I thought I would put in my 2 cents anyway.

You have a handful of problems based on the code you have listed.

@DimCount is declared as an int where you are trying to use it as a string.
You are putting quotes around @dimtablename.  I understand what you are trying to do there but you should be using quotename() with the default []s instead.
In the line where you set @sqlstmt you have ''' at the end of the INSERT part of the string.  This is going to put in an extra ' that you don't want.  The same is true at the end of the string.
Last but not least if you are going to do it this way you will need to convert the int variables to varchar explicitly otherwise it tries to convert the whole thing to an int and fails.

Here is the corrected bit of code

DECLARE @counter INT = 1
    ,@dimtablename NVARCHAR(50) = 'dimtablename'
    ,@dimKey INT = 1
    ,@processdatekey INT = 1
    ,@DimCount VARCHAR(8000)
    ,@sqlstmt VARCHAR(8000)

SET @DimCount = '(SELECT COUNT(*) from ' + quotename(@dimtablename) + ' with(nolock)) ';
SET @sqlstmt = 'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (' + cast(@processdatekey as varchar(30)) + ',' + cast(@dimkey  as varchar(30)) + ',' + @DimCount + ')'

print @sqlstmt

All of that said you probably should look into using sp_executesql instead of just EXEC.  This will let you parameterize your queries.  Here is the same code using parameters.

DECLARE @counter INT = 1
    ,@dimtablename NVARCHAR(50) = 'dimtablename'
    ,@dimKey INT = 1
    ,@processdatekey INT = 1
    ,@DimCount NVARCHAR(4000)
    ,@sqlstmt NVARCHAR(4000)

    SET @DimCount = N'(SELECT COUNT(*) from ' + quotename(@dimtablename) + N' with(nolock)) ';
    SET @sqlstmt = N'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (@processdatekey,@dimkey,' + @DimCount + N')'

PRINT @sqlstmt

EXEC sp_executesql @sqlstmt, N'@dimKey INT, @Processdatekey INT', @dimKey, @processdatekey

This is particularly important when using character parameters to help avoid SQL Injection but it's also a good practice to get into in general.


Should Crystal Report be allowed for certain employees for production database?

It will depend on the type of data being access and your company's security requirements/policy. In general you do not want users sharing an account, especially if that account has more than read permissions on your database. I would verify with your security folks or legal group if there are any specific requirements for managing access to data, or with auditing. If you fall under any industry standards like HIPPA or PCI they have controls that require being able to map access to a particular user.

With regards to how you allow users to run reports that is usually driven by policy where I have worked. Do you experience any performance issues now with how they are pulling reports and "peak" at data? As a DBA, I'm a control freak to a certain degree, I like to know who and what is making calls to my data. I have been in one environment where reports were pointed to a database snapshot of the production database, so their was less of a performance concern. Another environment it was not until their report caused a performance issue in production that we would tell them if you want that information run it after business hours.


MariaDB Parallel Replication Drifting at random times

innodb_file_per_table = On  -- only applies during CREATE TABLE or ALTER TABLE
table_open_cache = 8192
table_cache = 70000  -- old name for table_open_cache; 70K is too high
init-connect='SET NAMES utf8'  -- keep in mind that user=root skips init_connect
long-query-time = 30  -- so high as to be virtually useless

"one of these only has a 2.6G ibdata1 file" -- What do you mean?

"But some servers will just not correct" -- Do you mean 'some Slaves'?  Or 'some Clients'?

"no latency ~6ms ping and stable" -- that implies several hundred miles or km.

"Some servers drift" -- clocks drift; servers don't.  What do you mean?

"3 hours behind" -- Do SHOW SLAVE STATUS;

Replication existed for more than a decade before GTID was added.  That won't be to blame for what you are seeing.


Inconsistencies with ADD CONSTRAINT index_name in MySQL

See if these sound close.  (Taken from Changelogs)

----- not yet released 5.7.6 Milestone 16 & 2015-02-02 5.6.23 -- Bugs Fixed -- InnoDB -----

An ALTER TABLE ( http://dev.mysql.com/doc/refman/5.6/en/alter-table.html ) operation that changed the name of a foreign key column resulted in a failure when reloading the foreign key constraint. The previous column name remained in the data dictionary cache instead of being evicted. (Bug #20031243)

----- 2014-02-10 MariaDB 10.0.8 -- -- -----

MDEV-4439 ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS does not work if constraint name is not used. Patches for server and the Innodb engine. Server is fixed so it does nothing if no indexes left to alter. Innodb parser is fixed so it looks for the IF [NOT] EXISTS option in a string. Another change is that it uses the index name for the internal dictionary. Prior to that it only used the CONSTRAINT name for it.

----- 2009-07-13 5.1.37 -- Bugs Fixed -- -----

Renaming a column that appeared in a foreign key definition did not update the foreign key definition with the new column name. (Bug #21704)

----- 2008-01-29 5.1.23 -- Bugs Fixed -- -----

User-supplied names foreign key names might not be set to the right key, leading to foreign keys with no name. (Bug #30747)


How to recreate an Index with the same settings?

You should probably play with scripting options in Tools-Options-SQL Server Object Explorer-Scripting in SSMS menu.

There you can define what you would like to script and what not. Compression is there.

As for the other properties - have a look at this StackOverflow question - it will probably shed some light:

SORT_IN_TEMPDB is not a property of an index, but rather an option that you can specify that says how the index is created. Once the index exists (i.e. the create index statement completes), whether or not SORT_IN_TEMPDB was specified at creation time isn't stored anywhere because it doesn't matter.


MySQL Galera cluster can't initiate first node

Have you tried creating the /var/run/mysqld directory, with rwx permissions for the mysql user? Judging by the error messages, that seems to be a good place to start.

The other error message should probably also be addressed, regarding not writing to syslog and the mysql error log at the same time. I'd suggest inspecting your MySQL config files under /etc/ to see if you can disable the syslog option.

Also, it looks a bit strange that you're using mysql-wsrep-5.6 with MySQL 5.7. Is there a mysql-wsrep-5.7? If not, perhaps you need to use MySQL 5.6.


How to count rows based on a column value

You need to use a Window Function like row_number()

SELECT
  id,
  section,
  views,
  row_number() OVER (PARTITION BY section ORDER BY views DESC)
FROM table;


SQL Server Grant execute on all stored procedures without ddl permissions

NO, With just Execute permission doesn't allow a user to execute a stored procedure successfully if it contains some DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) statements.


Difference in between MySQL affected num rows and num rows

Here's mysql_num_rows:

int mysql_num_rows ( resource $result )

  Retrieves the number of rows
  from a result set. This command is only valid for statements like
  SELECT or SHOW that return an actual result set. To retrieve the
  number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query,
  use mysql_affected_rows().

And here's mysql_affected_rows:

int mysql_affected_rows ([ resource $link_identifier = NULL ] )

  Get the number of affected rows by the last INSERT, UPDATE, REPLACE or
  DELETE query associated with link_identifier.

Notice that the input types are different:  the first wants a result set, from which it counts the number of rows returned; the second wants a db handle.  That makes sense, though, because when you INSERT (or UPDATE, DELETE, REPLACE) a row, you typically don't want a result set -- you just want to know if it succeeded or failed, or how many rows it updated/replaced/deleted.


When would I want to use COMPRESS and DECOMPRESS functions in SQL Server 2016?

Use them when you want to achieve the benefits and you are prepared to pay the costs.

Compression can significantly reduce the number of bytes required to represent a large value. This saves the cost of disk, reduces the time to take backups, and restore after an incident. Performance can improve as fewer bytes must be pulled off disk and passed over the network.

The cost is in CPU to (de)compress the value before it can be used. This adds latency, consumes cycles which aren't available for real work and burns electricity.

So if you have large objects (compression would make a significant difference) such as text or XML (compress well) that are used used infrequently (minimize overhead), disk is expensive and CPU is cheap compression maybe helpful.

If you're storing jpg (already compressed), are on a SAN (built-in compression & de-dupe, likely fast network) and on a small, busy server then likely compression is not beneficial.


Query to map a particular database to a syslogin

You're missing a CREATE USER to add them in as a database principal.

CREATE LOGIN [TRAINW7X64CA04\Test8] from Windows with default_database = Eclipse;
Use Eclipse;
CREATE USER [TRAINW7X64CA04\Test8];
EXEC sp_addrolemember N'db_owner', N'TRAINW7X64CA04\Test8';


"Warnings: Operation caused residual I/O" versus key lookups

The cost model used by the optimizer is exactly that: a model. It produces generally good results over a wide range of workloads, on a wide range of database designs, on a wide range of hardware.

You should generally not assume that individual cost estimates will strongly correlate with runtime performance on a particular hardware configuration. The point of costing is to allow the optimizer to make an educated choice between candidate physical alternatives for the same logical operation.

When you really get into the details, a skilled database professional (with the time to spare on tuning an important query) can often do better. To that extent, you can think of the optimizer's plan selection as a good starting point. In most cases, that starting point will also be the ending point, since the solution found is good enough.

In my experience (and opinion) the SQL Server query optimizer costs lookups higher than I would prefer. This is largely a hangover from the days when random physical I/O was much more expensive compared to sequential access than is often the case today.

Still, lookups can be expensive even on SSDs, or ultimately even when reading exclusively from memory. Traversing b-tree structures is not for free. Obviously the cost mounts as you do more of them.

Included columns are great for read-heavy OLTP workloads, where the trade-off between index space usage and update cost versus runtime read performance makes sense. There is also a trade-off to consider around plan stability. A fully covering index avoids the question of when exactly the optimizer's cost model might transition from one alternative to the other.

Only you can decide if the trade-offs are worth it in your case. Test both alternatives on a representative data sample, and make an informed choice.

In a question comment you added:

  Are you telling me that SQL Server does not know the cost of the residual IO?

No, the optimizer does consider the cost of residual I/O. Indeed, as far as the optimizer is concerned, non-SARGable predicates are evaluated in a separate Filter. This filter is pushed into the seek or scan as a residual during post-optimization rewrites.


SQL Server 2005: Integration Services Stored Packages is empty. Why?

The packages themselves are stored in the msdb sysdtspackaes90 table.

  It’s important to note that the SSIS
  server isn’t aware of packages stored
  in the File System until those
  packages have been imported to the
  File System folder in the SSIS
  service.

...

  While the SQL Server Management Studio
  is shipped using the default folder
  locations of MSDB and File System, you
  can freely add new folders to this
  structure using the Create New Folder
  option. When you create a new folder
  beneath the File System system folder,
  a new directory will be created in the
  file system. By default, these
  directories are located in the
  c:\Program Files\SQL
  Server\90\Packages directory.
  Importing packages to a File System
  folder will result in the package
  being copied to the like named
  directory in the file system. For
  folders that are created under the
  MSDB folder, a new entry is added to
  the sysdtspackackefolder90 table that
  tracks the folder structure. However,
  it’s important to realize that the
  packages themselves are still stored
  in the msdb sysdtspackaes90 table. The
  Folders option in the SQL Server
  Management Studio essentially gives
  you a way to apply and organization
  structure to your packages, enabling
  you to group like packages together.

Managing and Deploying SQL Server Integration Services

Anyway, I recommend to install all SPs.


how to send mail from gmail smtp server by using the ssis

There is a built in task for sending SMTP mail in SSIS.  If you want to use a script task to send mail via SMTP the post below lists several ways to do what you are looking to do.

http://social.msdn.microsoft.com/Forums/en/netfxnetcom/thread/a75533eb-131b-4ff3-a3b2-b6df87c25cc8


how to overcome these type of issues where null value inserted into primary key not null column in table

As was stated in the comment

  You're not inserting a NULL value, you're inserting ""

If you want to prevent an empty string ("") and NULLs from being used as ai_name, you need a trigger. In your particular case, you need a BEFORE INSERT trigger:

DELIMITER $$

CREATE TRIGGER check_for_blank_ai_name
BEFORE INSERT ON actionitems
FOR EACH ROW
BEGIN
  DECLARE dummy INT;
  IF ( IFNULL(NEW.ai_name,'') = '' ) THEN
    SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';
  END IF;
END $$

DELIMITER ;

The line of code

SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';

will break. That's by design. This will prevent the INSERT from occurring.

I have written triggers that break midstream before in my other posts:

Jun 09, 2013 : BEFORE INSERT trigger in MySQL
Dec 11, 2012 : Comparing dates in a BEFORE INSERT trigger
Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

Give it a Try !!!


Export data using bcp to deal with corrupted data

First, here's my checklist on how to tackle corruption issues. It's a big deal - bigger than I can cover here - but based on the sounds of this, I don't think you've covered some of the basics (like trying to restore from backup, or making sure you've even got backups.)

When you start getting corruption errors, every time you access storage, you may be making the problem worse. For example, I dealt with one broken storage array where the more we read data, the more it became corrupted. (I'd read one table and it was fine, and then fifteen minutes later, it was hosed too.) Just like a haunted house, you need to get off that storage device as quickly as possible, and tackle your recovery efforts somewhere else.

Once you're on another storage device, if you can isolate the corruption down to a single table (which isn't likely, but you're hinting that you've done that), then:

In CHECKDB's output, identify which indexes are impacted (you can see via the index ID - 0 is a heap, 1 is a clustered index, and 2 & higher are nonclustered indexes)
If you have corruption in index 0/1, then for bonus points, copy data out from each index separately. You may be able to get some of the columns back for the corrupted rows in the clustered index. (If you have corruption across multiple nonclustered indexes, you may be able to get all of the rows from some nonclustered indexes, but not others.)
If you can identify which pages are affected, then you'll need to design your WHERE clauses to work around the corrupted pages. (SQL Server won't gracefully skip corrupt pages - it's up to you to design your query to stop before it encounters corruption.)

Think about a phone book, for example, organized by last name, then first name. If you know that the page containing Ozar, Brent is corrupted, then you may have to select users up to my last name, but stopping before that - and then run a second query for users with a last name higher than mine.

Here's the trick, though: corruption is going to strike all over the table. You're going to be doing a ton of work trying to get around different corrupt pages.

It's not easy, and that's why I wrote my checklist - and why it doesn't try to tackle this portion. You're talking about days, if not weeks, of work when the corruption is widespread.


First a note: when relational databases filter rows, they check each row separately if it fulfils given conditions. Other rows from the same table are not taken into account at that moment but again checked separately for the same (row-independent) conditions. So there is no "simple" command for what you want.

But there are multiple ways to accomplish it:

Use a variable which is initialized to true and then for each row it is used in the filter and set to @var = @var AND (y = 0) - that way first encountered non-zero value will set the variable to false and no other row can fulfil the condition. There is a problem with this - setting a value of user variable in the same statement as reading it is sort of undefined behavior - it often works but it may not work sometimes..
select the lowest x for which the y is non-zero select min(x) from T where y > 0; or select x from T where y > 0 order by x asc limit 1; - keep the value in app or in variable and then just select all the rows where x is less than the value you found select x, y from T where x < @stored_x order by x asc;
instead of app/sql variable use a subquery for the previous - select x, y from T where x < (select min(x) from T where y > 0) order by x asc; - this uses a sependent subquery which may be evaluated once per each row if the server version does not implement subquery caching or some kind of materialization so it might be slow
as previous but use join and a derived table (which is evaluated only once and materialized into a temp table):

-

select T.x, T.y
from T
join (
    select min(x) as limit
    from T
    where y > 0
) T2
where T.x < T2.limit
order by T.x asc;

The important thing is - you cannot think of it as "Select rows until some condition is met." but as "Which rows do meet this condition? The ones where X is smaller than that of a(ny) row with nonzero Y." and write that using standard SQL.


My guess is that you would want to use an Oracle wallet so that you don't need to embed the username and password in your application at all.  Depending on the architecture of your application, there may be other options as well-- if you're building a Java application that is deployed to an application server, for example, most application servers have a way to configure connection pools such that only the encrypted password is stored in a configuration file.


The major difference between sql server 2012 and below vs sql server 2014 and up is the new cardinality estimator (CE)

See my answer here as well.

My Story

When we migrated from sql server 2008R2 to 2014, we saw huge query regressions happening on our application. So we decided to enable TF 9481 but still have the latest compatiblity level.

Upgrading from sql server 2014 to 2016, I did not see any query regressions. So no Traceflag or older compatiblity mode required in sql server 2016.

In sql server 2016, there is database scope configuration option to govern the CE and optimizer fixes (equivalent of TF 4199)

  Should I expect differences in performance after the
  compatibility level change?

Its a big change for your application. SO test it properly. You can use Database Experimentation Assistant (New & in preview)

  What is the best way to measure the performance before and after?

You can use Query store (new in sql server 2016) to measure performance and find easily regressed queries interms of IO, CPU, etc.

  Should I take special care when moving the databases the SQL
  server 2016?

I have written lenghty answer for migrating from lower version of sql server to higher version of sql server.


SSISDB AlwaysOn Support in 2016 - SSIS Failover Monitor Job fails on non-readable secondaries

Consider wrapping the statement in the BEGIN END block inside of sp_executeSQL so that it bypasses the syntax checking until runtime.

DECLARE @role int
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB')
IF @role = 1
BEGIN
    exec sp_executeSQL N'DECLARE @status tinyint;
    EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = N''YourServerName'', @status = @status OUTPUT
    IF @status = 1
        EXEC [SSISDB].[catalog].[startup]'
END

Note: edited the script


Can I normalize my experimental data table further?

Second normal form

If I understand you correctly, the combination of {date, compound_type, location, method} uniquely identifies {value, units}, and all four are needed in order to identify a unique sample ({date, location, method} isn't enough by itself, for example).

I'm going to write this as if I hadn't received an answer on my question about functional dependencies, since other people might be interested in an explanation of both possibilities.

If there are no partial dependencies

1) Assuming none of the non-prime attributes {value, unit} depend on part of the candidate keys {id} or {date, compound_type, location, method}, your table is in 2NF since, as Wikipedia puts it, "every non-prime attribute of the table is either dependent on the whole of a candidate key, or on another non-prime attribute."

If there are partial dependencies

2) One or both of the non-prime attributes {value, unit} depend on only parts of the candidate key {date, compound_type, location, method}. You've confirmed this is the case with {compund}->{unit}, so your table is not in 2NF.

In order to fix the violation of 2NF, I would suggest moving {unit} to the compound table, which I'm guessing would end up looking something like this: {id, name, unit}. Here, the candidate keys are {id} and {name}. Since there are no composite candidate keys, the table is automatically 2NF. It's also 3NF since there are no transitive dependencies, I.E. there's no attribute that's dependent on unit.

Third normal form

OK, that leaves us with the samples table looking like this: {id, date, compund_type, location, method, value}. The two candidate keys are {id} and {date, compund_type, location, method}, which leaves {value} as the single non-prime attribute. Assuming that there are no more 2NF violations (you can't use a subset of {date, compund_type, location, method} to uniquely determine value), we can check the table for violations against 3NF.

3NF states that every non-prime attribute (attributes that don't belong to a candidate key)  must be directly dependent on every superkey. Since we only have one non-prime attribute, {value}, it's impossible for the table to violate 3NF, since there's no non-prime attribute for {value} to be dependent on, and no non-prime attribute that can depend on {value}.

I'm going to leave discussions about BCNF out of this for simplicity.

Surrogate key vs. natural key

As for your other questions: "is using an id(PK) column like I have above the best way to go with all of the repeating dates?"
I think so. Semantically, the surrogate key id isn't necessary, but it does help keep things simple. I'm not sure how MySQL works underneath the hood, but in other DBMSs composite primary keys with non-integer data types can lead to unnecessary overhead for example when indexing. Another problem with composite keys is that it gets annoying to query them.

Imagine that you need to add information about which labs each sample was sent to. A sample can be sent to several labs and each lab can receive several samples, so you create a table to connect the two tables. Would you rather write this

SELECT *
FROM samples s
JOIN labs_samples ls ON
    s.date = sl.date,
    s.compund_type = sl.compund_type,
    s.location = sl.location,
    s.method = sl.method

or this

SELECT *
FROM samples s
JOIN labs_samples ls ON s.id = ls.id

?