Have a look at this interesting answer of Aaron Bertrand and you'll get a detailed answer about what is happening.

Return All Data From Left Table

You should move all conditions of WHERE clause to the LEFT JOIN clause.

Select mk.storeName
       ,[Ninja Count] = COUNT(kn.ssID)
       ,[Meta Count] = COUNT(mk.storeNumber)
       ,[C1 Total] = SUM(COALESCE(kn.c1,0))
       ,[C2 Total] = SUM(COALESCE(kn.c2,0))
       ,[C3 Total] = SUM(COALESCE(kn.c3,0))
       ,[C4 Total] = SUM(COALESCE(kn.c4,0))
FROM   MetaKnight mk
LEFT OUTER JOIN KnightNinja kn
ON     mk.storeName = kn.storeName
AND    kn.d1 >= '2017-01-01'
AND    kn.d1 < '2017-02-01'
AND    mk.openDate >= '2017-01-01'
AND    mk.openDate < '2017-02-01'
GROUP BY mk.storeName
ORDER BY mk.storeName ASC

GO

storeName | Ninja Count | Meta Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | ---------: | -------: | -------: | -------: | -------:
Store A   |           0 |          1 |        0 |        0 |        0 |        0
Store B   |           0 |          1 |        0 |        0 |        0 |        0
Store C   |           0 |          1 |        0 |        0 |        0 |        0
Store D   |           0 |          1 |        0 |        0 |        0 |        0
Store E   |           0 |          1 |        0 |        0 |        0 |        0
Store G   |           3 |          3 |     1194 |     3021 |     1221 |        0
Store H   |           1 |          1 |       40 |        0 |      146 |      147
Store I   |           1 |          1 |        0 |     4899 |        0 |        0

Warning: Null value is eliminated by an aggregate or other SET operation.

dbfiddle here

Update

On this case there is a problem because none of your queries returns all storeName.

SELECT    mk.storeName,
         [Meta Count] = COUNT(mk.storeNumber)
FROM      MetaKnight mk
WHERE     mk.openDate >= '2017-01-01'
AND       mk.openDate < '2017-02-01'
GROUP BY  mk.storeName;

storeName | Meta Count
:-------- | ---------:
Store A   |          1
Store G   |          3
Store H   |          1
Store I   |          1

SELECT   kn.storeName,
         [Ninja Count] = COUNT(kn.ssID),
         [C1 Total] = SUM(COALESCE(kn.c1,0)),
         [C2 Total] = SUM(COALESCE(kn.c2,0)),
         [C3 Total] = SUM(COALESCE(kn.c3,0)),
         [C4 Total] = SUM(COALESCE(kn.c4,0))
FROM     KnightNinja kn
WHERE    kn.d1 >= '2017-01-01'
AND      kn.d1 < '2017-02-01'
GROUP BY kn.storeName
GO

storeName | Ninja Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | -------: | -------: | -------: | -------:
Store B   |           1 |        0 |        0 |        0 |        0
Store C   |           1 |      219 |      898 |      154 |       11
Store D   |           1 |    25019 |   258947 |     5746 |        0
Store G   |           1 |      398 |     1007 |      407 |        0
Store H   |           1 |       40 |        0 |      146 |      147
Store I   |           1 |        0 |     4899 |        0 |        0

Let me suggest another approach:

WITH sn AS
(
    SELECT DISTINCT storeName
    FROM MetaKnight
)
SELECT sn.storeName,
       kn1.[Ninja Count],
       cnt.[Meta Count],
       kn1.[C1 Total],
       kn1.[C2 Total],
       kn1.[C3 Total],
       kn1.[C4 Total]
FROM   sn
LEFT JOIN (SELECT    mk.storeName
                    ,[Meta Count] = COUNT(mk.storeNumber)
           FROM      MetaKnight mk
           WHERE     mk.openDate >= '2017-01-01'
           AND       mk.openDate < '2017-02-01'
           GROUP BY  mk.storeName) cnt
ON        sn.storeName = cnt.storeName
LEFT JOIN (SELECT   kn.storeName,
                    [Ninja Count] = COUNT(kn.ssID),
                    [C1 Total] = SUM(COALESCE(kn.c1,0)),
                    [C2 Total] = SUM(COALESCE(kn.c2,0)),
                    [C3 Total] = SUM(COALESCE(kn.c3,0)),
                    [C4 Total] = SUM(COALESCE(kn.c4,0))
           FROM     KnightNinja kn
           WHERE    kn.d1 >= '2017-01-01'
           AND      kn.d1 < '2017-02-01'
           GROUP BY kn.storeName) kn1
ON        sn.storeName = kn1.storeName
ORDER BY  sn.storeName ASC

GO

storeName | Ninja Count | Meta Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | ---------: | -------: | -------: | -------: | -------:
Store A   |        null |          1 |     null |     null |     null |     null
Store B   |           1 |       null |        0 |        0 |        0 |        0
Store C   |           1 |       null |      219 |      898 |      154 |       11
Store D   |           1 |       null |    25019 |   258947 |     5746 |        0
Store E   |        null |       null |     null |     null |     null |     null
Store G   |           1 |          3 |      398 |     1007 |      407 |        0
Store H   |           1 |          1 |       40 |        0 |      146 |      147
Store I   |           1 |          1 |        0 |     4899 |        0 |        0

dbfiddle here


MySQL - How can a deadlock b caused by the same transaction?

I suggest you add code to make the product more robust.  Check for deadlocks after every statement.  When you encounter such, replay the statement(s) in the transaction.

That may be cheaper than figuring out all the possible causes of deadlocks (you have shown one, but there could be more).  Later you can worry about the extra unique key.  But leave the checks for deadlocks in place even if they never happen again.

Adding to FOR UPDATE to the SELECT.


SQL Windowing Functions: Are the results ordered?

Without a presentation ORDER BY clause, standard behavior returns a set in no particular order.

I think Itzik Ben-Gan explains it best and even without one of his books, you can find the explanation in this article. About 12 paragraphs down, next to the first code sample, the text describes how the ORDER BY in the window function serves a logical function to describe the set, and how that is completely separate from a presentation ORDER BY clause.

A related question appears here
Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?


It's not clear what you want but this may be it:

SELECT
    Name,
    SUM(CASE WHEN Used IS NULL
           THEN ItemQty
           ELSE 0
        END) AS QtyNotUsed
FROM
    ContainersReceived
GROUP BY
    Name ;

To be efficient, add an index on (Name, Used, ItemQty)

If you have another index, on (Used, Name, ItemQty), this query will be even more efficient - but it will not show Names that have no row with Used IS NULL:

SELECT
    Name,
    SUM(ItemQty) AS QtyNotUsed
FROM
    ContainersReceived
WHERE
    Used IS NULL
GROUP BY
    Name ;


what are steps should i do to make my local sql server connects to a remote sql server on my server?

Are you talking about just accessing the databases and the instance? Or do you mean implementing high availability?

If its the former, then all you technically need to do is ensure tcp/ip is an enabled protocol, install SSMS, and if you can see the server then you should be able to login to the remote instance, provided you have the proper credentials.

Edit: if its not the default instance then you need to ensure the SQL Browser service is started.


One database in AlwaysOn Group not truncating log file

I would like to comment but couldn't due to the lack of reputation.

Is your secondary node being enabled read-only? And if so, did you check if there's any blockings on the secondary node?

Another thing that you could check is if there's any maintenance jobs running.

And... could you try to make everything asynchronous mode to see if it helps? Once log reduced, you may set it back to synchronous mode.

  ... I've created a ticket to ask for this particular database to be removed from it and then added again. However I'm not sure if this;
  a) will fix the problem

Last but not least if everything doesn't work, yes, you can remove that particular DB from the AG group. It should fix the issue. Until the log has reduced, you may add it back to the AG group. Removing DB from the AG group shouldn't cause to any impact as applications should be connecting using the Listener.

  b) a temp fix (truncate once, then slowly start growing again)

Yes, it might occur again if we don't figure out the root cause to it. And you probably would have to repeat the whole procedure in removing/adding back to AG group, though it shouldn't cause to any production impact.


Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

Any solution proposed, or any solution you could envision for the mater, would suffer from the same issue: coalesce point around the schema modification lock.

Whether you do a truncate, or an alter table ...switch, or sp_rename, alter schema whateverver, it doesn't matter. They're all the same solution in disguise. And all will have a point in time when the table that is being truncated/ switched/ renamed/ transfered must be locked with SCH-M. This is, in theory, an 'instantaneous metadata only operation', but in practice the SCH-M cannot be granted until all the other existing locks on the table are released, which means any query that is already running on the table will have to finish first. Fine, the operation will wait until the existing queries 'drain'. The issue is that no other query can get in until the pending SCH-M is granted and released. This is a well know lock starvation issue mitigation. So all of the sudden all your new queries will freeze until the very last old query finishes, then the truncate/ switch/ transfer occurs, and the new queries finally resume. This is not noticeable if all queries last 1 second, but if the queries are reports/ analysis that last on average minutes the effect is very visible, specially so if you have a long tail.

The good news is that you can stop worrying about how to do what you're trying to do: it is impossible. The bad news is that you have to fix the application now.


map non unique attribute to a unique attribute

This will work with the sample data you have provided here. Your data could be entered into your tables in a way so that this will not work. If that is the case, please update your question with data that will show the issues you have.

;with TT as
(
  select table1_name,
         table2_name,
         row_number() over(partition by table2_name order by (select 1)) as rn
  from @TABLE1_TABLE2
),
T2 as
(
  select id,
         table2_name,
         row_number() over(partition by table2_name order by id) as rn
  from @TABLE2
)
select T1.id as T1ID,
       T2.id as T2ID
from TT
  inner join @TABLE1 as T1
    on TT.table1_name = T1.table1_name
  inner join T2
    on TT.table2_name = T2.table2_name and
       TT.rn = T2.rn


How to change database owner for login and database created inside stored procedure?

The way that works best for me, to write stored procedures that execute dynamic SQL, is by not using "EXEC sp_executesql" when I start writing the stored procedure, but by using "PRINT" statements.

I make sure I get to see each and every SQL that the stored procedure generates. I copy them from the Messages tab and execute them from within Management Studio. Only after all SQLs work as intended, I allow my stored procedure to "EXEC sp_executesql" (instead of simply PRINTing the SQL statements).

Debugging works very fast this way. The most problems I encountered so far, are the result of missing spaces, like 'FROMMyTableName' or 'WHEREId = 12'.

You will have to test this as the certificate user or with an account that has similar rights. And if you can avoid using dynamic SQLs, avoid them.


SSIS Raw Data Import Into SQL Server 2008 R2 - Truncation and Condensed Data Type

AFAIK a condensed type is only an alias used in Management Studio, it's nothing else then the definition of the Type including length information (instead of having two fields: Type and Length - Varchar and 60, you have only one = Varchar(60)).

Now, that error of yours happens obviously because of data truncation. In order for it to pass, you need the original and the source to be of the same type and length (either NVARCHAR(50) or CHAR(6)). For the length it's more important that the source is smaller than the destination.

So you need to pick your necessary type (according to the input data or the destination) and make them both similar. If the input data needs a cleanup, then you better prepare it before inserting it.


Maximum length or special character restrictions for PostgreSQL 13 user password?

I was able to answer this one:

What is the maximum length of a password?

The answer seems to be 99. At 100 I was getting the "password validation failed," but not at 99.


You cannot alter the same table in trigger.

Use rotate (update the oldest record) instead of add and delete. Insert 100 records into your table (if there is no enought records in it) with their timestamp in far past and use

UPDATE logs
SET field1=value1, field2=value2 -- , ...
ORDER BY record_timestamp ASC
LIMIT 1;

The records count will be always unchanged. To increase it simply add the amount of records you need with timestamp in far past, to decrease - delete the amount of oldest records you do not need.

And you do not need in trigger at all...


Use a linked server as Aaron mentioned and bulk load your data to your SQL 2000 tables after doing the transformation within SQL 2012. You could just create a few staging tables in SQL 2012 where you can place your transformed data as it comes in.

Beware of linked server from 2012 to 2000 using SQLNCLI10: see here
SQLNCLI10

The new client SQLNCLI11 does not work with SQL 2000 as far as I know.


Is it possible to set order in which inherited tables a scanned in postgresql?

I ...  want to select latest row with city_id = 125.

Bold emphasis mine. Your query does not match the declared objective. Should be something like:
SELECT * FROM measurement WHERE city_id = 125 ORDER BY logdate DESC LIMIT  1;
Which should make Postgres scan the latest partition first.
Assuming logdate is defined NOT NULL. Else use ORDER BY logdate DESC NULLS LAST:

PostgreSQL sort by datetime asc, null first?

If that doesn't work for some reason (your setup and Postgres version are undisclosed), you can also spell it out:
(SELECT * FROM  measurement_y2006m03 WHERE city_id = 125 ORDER BY logdate DESC)
UNION ALL
(SELECT * FROM  measurement_y2006m02 WHERE city_id = 125 ORDER BY logdate DESC)
UNION ALL
(SELECT * FROM  measurement_y2006m01 WHERE city_id = 125 ORDER BY logdate DESC)
LIMIT  1;

Parentheses are required to make the ORDER BY apply to each SELECT instead of the whole query. Maybe you get an even better query plan by adding LIMIT 1 to each SELECT additionally. You'll have to test.
Postgres will stop scanning as soon as it has found the first row. Related answers with more explanation:

Way to try multiple SELECTs till a result is available?
Use column values in ORDER BY and LIMIT clauses

Index
For best performance you would have composite indices on (city_id, logdate DESC) on all child tables (or at least the latest ones).


Question about set log_slow_verbosity='query_plan' in MariaDB ans MySQL

The mysql manual shows you what parameters are accepted, and allow you to tune your system.  It's possible this is limited to long_query_time and min_examined_row_limit, while log_slow_verbosity is specific for mariadb.


What's the best way to translate a website

You want to use a TRANSLATIONS table, that looks something like:

CREATE TABLE TRANSLATIONS
(
  translation_id    INTEGER,
  language_id       INTEGER,
  translation       VARCHAR(255),
  PRIMARY KEY (translation_id, language_id)
);

Then edit your current schema and/or code to reference the TRANSLATIONS table, rather than any hard-coded values (using translation_id,language_id to lookup). For example, rather than having a VARCHAR column in a table, you'd instead have a NUMBER column that would "link" to the TRANSLATIONS table.

Probably also best to have a LANGUAGES table that lists what each language_id represents.

For example:

CREATE TABLE LANGUAGES
(
  language_id   NUMBER PRIMARY KEY,
  iso_code      CHAR(4), -- eg "en"
  language_name VARCHAR(32) -- eg "English"
);


How to check total allotted space inside a HDFS 'group'

It is not possible to set space quotas on Hive level only because Hive is quite detached from HDFS storage. In Hive you can create tables not only in the hive.metastore.warehouse.dir but also can have external tables in other directories in HDFS. External tables can be being loaded by not only hive, even Hive-managed tables can be loaded by some other tools without Hive(for example you can put files manually into table directory, does not matter Managed or External). Also you can use HDFS not only for Hive. So, it is not possible to set space quotas in Hive and even if it was possible, it is not practical, Hive is not aware what else you are doing in HDFS.
Read about HDFS space quotas. It is possible to set space quotas for files inside directory. Also it is possible to set permissions for users (groups) allowing them to access some directories. HDFS directory ACL + directory space quota can be combined to restrict users or groups to use only allowed directories (whith space quotas).
You can check directory quota using
hadoop fs -count -q /path/to/directory
To check directory ACL use this command:
hdfs dfs -getfacl [-R] <path>
See more in FS shell commands guide.
Read also about Hive Authorization Options


How to get the definition of a database trigger?

how about

select m.definition from sys.triggers t
inner join sys.objects o on t.object_id = o.object_id
inner join sys.sql_modules m on m.object_id = o.object_id


Does the compatibility level for sql server work reliably for scripts?

No the compatibility level is to allow deprecated features to work not to prevent you from writing code that won't work on a lower version.

If your production machine is SQL Server 2005, you must develop on a SQL server 2005 machine to ensure that features allowed in 2005 are the only ones in the code.

Of course in Books online there is a page which shows all the features that are new in 2008, so you could simply forbid your developers from using them. And check for them in code reviews, but developing against a correct database is the only real solution.

The list is not short and exotic, the compatibility mode is not intended to allow you to develop on a higher database and deploy to a lower one.


MySql : Grant privileges for specific session

Another approach is to provide a general read-only user and publicize the password.

To create the hundred GRANTS,

SELECT  CONCAT("GRANT SELECT   ON ",
                schema_name, ".* TO 'read_only_user'@'*'
                IDENTIFIED BY 'foo'"
              )
    FROM  `SCHEMATA`
    WHERE  schema_name NOT IN ('information_schema', 'mysql',
                               'performance_schema');

(Then copy & paste the results into the mysql commandline tool.)  (Or make a Stored Procedure to do the work.)


You can self join the table so that on the left there are the hosts for month 1, on the right for month 9:

SELECT host, risk, plugin_id, month, year
  FROM your_table t1
  JOIN your_table t2 ON t1.month = 1
                    AND t2.month = 9
                    AND t1.host = t2.host
                    AND t1.plugin_id = t2.plugin_id
 WHERE risk = 'High';

This will return the hosts where plugin_id is the same for the two months.  It is not exactly clear what the other queries should be - I imagine it is not like the plugin is the same for a given month.  Please edit your question with the clarification.


SQL Server - How to get corrupted rows id

There is probably a better way, because the following is time consuming, but I know it will work:  First, backup your corrupted database.  Second, try to bcp out the data.  As it copies the data out, it will display on the screen the number of rows copied.  When it errors, note the number of rows copied.  Those are all the rows prior to hitting the corruption.  Let's say the last entry of number of rows copied prior to hitting the error is 1078466. You narrow down your bcp command by adding -F 1078566 -L 1080466.  This will skip over 100 rows and try to copy out the next 1900 rows.  If it succeeds, then you know that the corruption is somewhere between rows 1078466 and 1078566.  Adjust your -F and -L to narrow down your search.  If it errors, then you know that the corruption is after 1078566.  Adjust your -F and -L to narrow down your search.  Repeat as necessary to locate your corrupted rows.


pgAgent does not require a graphical user interface at all. It is a process that runs as daemon on unix.
pgAdmin is used for configuring the job details. The configuration is stored in database postgres where pgAgent will read it.
Once you configured your jobs, you may disconnect pgAdmin safely.


PostgreSQL ERROR: cannot freeze committed xmax

Two leads:

If "jiradb.public.propertyentry" is a materialized view, you might be running into a bug that was fixed with the release of 9.4.17 on 2018-03-01. The release notes:

  Repair pg_upgrade's failure to preserve relfrozenxid for materialized views (Tom Lane, Andres Freund)

  This oversight could lead to data corruption in materialized views
  after an upgrade, manifesting as “could not access status of
  transaction” or “found xmin from before relfrozenxid” errors. The
  problem would be more likely to occur in seldom-refreshed materialized
  views, or ones that were maintained only with REFRESH MATERIALIZED VIEW CONCURRENTLY.

  If such corruption is observed, it can be repaired by refreshing the
  materialized view (without CONCURRENTLY).

The last line also hints how to repair this.

Or, if you upgraded from Postgres 9.2 or older, this other issue resulting in cannot freeze committed xmax <xid> may be yours:

https://www.postgresql.org/message-id/E1erVTy-0005Lp-8p%40gemulon.postgresql.org
https://www.postgresql.org/message-id/CAPYLKR6yxV4=pfW0Gwij7aPNiiPx+3ib4USVYnbuQdUtmkMaEA@mail.gmail.com

Fixed, but not released, yet. Should be in 9.4.18.


Behavior Differences Running SQL 2017 in 130 Compat Mode vs. Running SQL 2016 in 130 Compat Mode?

It will not behave exactly the same. Compatibility mode works at the database level, not the Instance level, which is still 2017. But it will mostly work the same.

I would check the breaking feature list for 2017 as some Instance level changes may still affect your code, despite it being in 2016 compatibility mode. However, the breaking changes from 2017 are relatively minor so it is probably unlikely you'd be affected. In general, some version breaking changes are covered under compatibility mode and some are not.

The documentation gives good examples of this,

  For example, the FASTFIRSTROW hint was discontinued in SQL Server 2012
  (11.x) and replaced with the OPTION (FAST n ) hint. Setting the
  database compatibility level to 110 will not restore the discontinued
  hint.

and

  An example of a breaking change protected by compatibility level is an
  implicit conversion from datetime to datetime2 data types. Under
  database compatibility level 130, these show improved accuracy by
  accounting for the fractional milliseconds, resulting in different
  converted values. To restore previous conversion behavior, set the
  database compatibility level to 120 or lower.

You should also look at the deprecated feature list for 2017 and try to remove any of those areas from future development in order to future-proof your applications. And of course, any new features from 2017 may require updates to take advantage of them as well, but I get the impression you're more concerned about breaking changes.

With all that said, you're probably fine, but should still carefully and thoroughly test upgrading the compatibility mode before moving it to production.


EXECUTE AS LOGIN failed with local windows group login

you can only impersonate login that appears in sys.server_principals
here an example from microsoft doc
https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15#_user


SSMS does not open popup wizard to create a new schema

How can I access this popup wizard?

This popup is disabled for Azure SQL Database and Azure SQL Data Warehouse.  It works for on-prem SQL Server.

Why?  Probably because it didn't work as-is against Azure SQL DB/DW, and it wasn't deemed worth fixing.  This is a really low-value feature, as Schemas don't really belong to Security and creating them in TSQL is so simple.

If you run Profiler you can see that SSMS runs the following batch:

declare @MasterPath nvarchar(512)
declare @LogPath nvarchar(512)
declare @ErrorLog nvarchar(512)
declare @ErrorLogPath nvarchar(512)
declare @Slash varchar = convert(varchar, serverproperty('PathSeparator'))
select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'master'
select @LogPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
select @ErrorLogPath=IIF(@ErrorLog IS NULL, N'', substring(@ErrorLog, 1, len(@ErrorLog) - charindex(@Slash, reverse(@ErrorLog))))

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

Which wouldn't work on SQL Database or SQL Data Warehouse.


What is the difference between UPDATE ... LIMIT and SELECT ... FOR UPDATE?

Pattern 1:

BEGIN;
SELECT stuff ... FOR UPDATE;
    fetches `stuff` and prevents (via a lock) other connections from messing with row(s).
work with `stuff`.
UPDATE the rows SELECTed (or a subset of them)
COMMIT;

Pattern 2:

(autocommit=1)
UPDATE Queue... WHERE ... LIMIT...  -- Grab row for self
SELECT ... WHERE ...           -- Make sure I got it, and fetch id
if got it     -- Perhaps someone else got it
then
    BEGIN;
    work on application things
    COMMIT;
endif
DELETE FROM Queue WHERE ...   -- remove from queue

Pattern 1 locks the row(s) longer, but allows more flexibility in what is done.

Pattern 2 has two quick locks.  But, because of other threads potentially messing with the same row(s), you have to check that you really got the row.

Since you mentioned queuing, think of it this way...  You have two structures: (1) the queue, and (2) your application data.  In general it is better to handle them separately.  The way to do that is to avoid having both in the same transaction.

Having both in the same transaction causes locks to last longer for both.  And it forces rollback of both, even if that is inappropriate.

Pattern 1 is better when the SELECT and the UPDATE are both working on application stuff.

Something like Pattern 2 is useful with you need to hang onto something "too long".  (That is, more than a few seconds.  Or from one html page to another, where you have lost the connection.)


I see a couple of ways this could be done without resorting to anything overly fancy.

The first would be to use Windows integrated authentication and assign permissions to the groups which represent users of the application.
You could create roles which could be granted, and then use set role to assume the role in your code in the db.  This way you aren't passing in a password and you are authenticating the application first.

The advantage of the first is that it is easy to manage, in an AD domain, who has access to the application.  The management would be relatively simple.  The major disadvantages would be that it would be limited to cases where integrated auth makes sense, and you'd have to support integrated auth all the way through.  Additionally every hop ends up requiring 3-way authentication (between server, client, and KDC).

The advantage of the second is that it is probably easiest to shim in, and it exposes no security info over the network, and it works in cases where integrated auth is not going to work well.

I would try both of those approaches before trying to extent the reporting service.  As the comment suggested, http://msdn.microsoft.com/en-us/library/aa237582%28v=SQL.80%29.aspx may be helpful and may work, but it is conceptually more complex than trying to manage roles directly.


Suggestion required for Large Database Optimization and performance

SQL Server DENALI will have column store indexes but this won't come until the end of this year or early next year but from what I have seen so far (very little), it will definitely help.

In SQL Server partitioning is your best friend here. And to your question abt the limitation of # of rows in a table, it is limited only by storage space.

http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx

As tables grow, adding and updating the data will take some time and you may want to look at using minimal logging if you are using SQL Server 2008.

http://sqlcat.com/whitepapers/archive/2009/02/15/the-data-loading-performance-guide-2008.aspx

And for large tables, updating indexes frequently can take significant times and have seen cases where updating the statistics will give you a quick boost instead of frequent index rebuilds.


Can't resume synchronization after patch failed on secondary node of Availability Group

When you restored the VM, its LSN was no longer in sync with the primary and thus it can't be resumed. The primary is too far ahead to resume.

You'll need to drop the secondary and add it to the Availability Group again in order to resume synchronizing. You can do this with an existing FULL backup plus the LOG chain.


Insert blank row in the postgres query result

Spacer between all rows
UNION ALL with the same query and blank columns. The only purpose of the subquery is to hide the additional column rn, which I use for sorting.
SELECT tbl_id, type, name
FROM  (
   SELECT tbl_id AS rn, tbl_id, type, name
   FROM   tbl

   UNION  ALL
   SELECT tbl_id AS rn, NULL::int, NULL::text, NULL::text
   FROM   tbl
   OFFSET 1
   )
ORDER BY rn, tbl_id NULLS FIRST

OFFSET 1 in combination with the later NULLS FIRST  in ORDER BY avoids a dangling spacer.
Spacer between groups of rows
Answer to updated question:
SELECT tbl_id, type, name
FROM  (
   SELECT type AS rn, tbl_id, type, name
   FROM   tbl

   UNION  ALL
   SELECT DISTINCT type AS rn, NULL::int, NULL::text, NULL::text
   FROM   tbl
   OFFSET 1
   )
ORDER BY rn, type NULLS FIRST


This works as you want on Enterprise/Developer Edition:
use tempdb
go

CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE OR ALTER VIEW dbo.V
WITH SCHEMABINDING AS
  SELECT T.Id, T.txt
  FROM dbo.T AS T
  WHERE T.b = 1;

GO
CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id);
GO

UPDATE V
SET txt = 'Foo';

The update has this plan

running
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)
    Sep 23 2020 16:03:08
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: ) (Hypervisor)

You can add a feedback item here to request that NOEXPAND be supported on UPDATE queries.  I put in a PR to update the docs to clarify that NOEXPAND is not available on UPDATE.


Doh! @jynus was exactly correct.  Here is the fix:

$eventid = intval(mysqli_real_escape_string($dbconnect, $_POST['eventid']));
$sql="DELETE FROM Events WHERE EventID='$eventid' LIMIT 1";
if (!mysqli_query($dbconnect,$sql)) {
  die('Error: ' . mysqli_error($dbconnect));
}
echo mysqli_affected_rows() . " Record(s) Dropped";


Joining 2 tables- in Select statement show how many fields matched directly (A1 = B1), vs matched on isnull(A1,B1) = B1

How about :

CASE WHEN t2.A IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.B IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.C IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.D IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.E IS NOT NULL THEN 1 ELSE 0 END


What are "Invisible Columns" and how are they used?

Here's an actual use-case that came up just days after I learned about the invisible columns feature in MariaDB 10.3:

Following a new requirement, I had added a new column in the middle of one of our big tables. (This is accessed by multiple different applications.) While this is the kind of menial task that you wouldn't imagine could result in a problem even in your worst nightmares, this is exactly what happened.

Unbeknownst to me, we had some "innovative" legacy code that was executing SELECT * against that particular table (and several others, it turns out), and then populating a set of variables based on the column names and column order that existed at the time. The end result was that the wrong values were displayed in the user interface, causing much confusion with the users.

I could have fixed the issue in minutes by making the column "invisible", but since we were still running 10.2 that wasn't an option, so we decided to go the hard route by patching and re-deploying the software. Had we not had access to the source code or for other reasons not been able to modify the code, an upgrade to 10.3 could have been our only option.

So while this feature is not covered by the SQL Standard, it can nevertheless be useful in some rare cases.


Restoring Postgresql database from WAL without backup

If you only have the WAL, with no base backup (copy of the data directory, pg_basebackup, etc), you cannot restore. Full stop. And no, you cannot use a dump from pg_dump to restore WAL on top of.

WAL only contains changes to the data directory, and is meaningless without a base backup to apply it to.

Imagine you have a half page from one of your bank statements, without a running balance shown in a column. You want to use it to find out the balance on your account. You cannot possibly do that, since you don't know the starting or ending balance. Same issue here.


MySQL / Fusion IO Configuration Question

This is similar to the question I answered two months ago : MySQL using too much CPU

FusionIO tends to be CPU aggressive. You will have to fight back by

Tuning InnoDB

more read threads (innodb_read_io_threads), perhaps 16
more write threads (innodb_write_io_threads), perhaps 16
raising or lowering innodb_io_capacity to match IOPs of your FusionIO device
raise the Log Buffer (innodb_log_buffer_size) to 1G
raise the Log File (innodb_log_file_size) to 128M

Converting as much of your MyISAM to InnoDB as possible because changes to .MYD are cached in the OS memory rather than any MySQL buffers

I would also lower max_connections to 2000 unless you know for a certainty to will get over 4500 simultaneous DB Connections. If you really need 5000, upgrade the VM's RAM to 48GB.

Give it a Try !!!


Splitting a column in two and returning a summary of results

You could do something like this.

declare @T table
(
  Department varchar(50),
  [Staff Name] varchar(50)
)

insert into @T values
('Web - Electronics',    'Matthew'),
('Store - Electronics',  'Mark'),
('Web - Home',           'Luke'),
('Store - Home',         'John'),
('Store - Home',         'Paul'),
('Store - Home',         'Simon'),
('Web - Electronics',    'Peter')

select T3.Department,
       sum(case T3.WebStore when 'Web' then 1 else 0 end) as Web,
       sum(case T3.WebStore when 'Store' then 1 else 0 end) as Store
from @T as T1
  cross apply (select replace(Department, ' - ', '.')) as T2(Department)
  cross apply (select parsename(T2.Department, 1),
                      parsename(T2.Department, 2)) as T3(Department, WebStore)
group by T3.Department


Is it possible to `DELETE` in a subquery?

You can DELETE in a CTE - which is a different kind of subquery.

But you probably don't need it for this simple case, you just need to use RETURNING and fix the syntax errors in your code:

DELETE
FROM posts AS p
WHERE p.title = 'whoops'
RETURNING p.* ;


How can I speed up an sql query that round trips between two tables

From my (rather limited) knowledge of php, I assume you want to combine the two queries into one:

SELECT m.*, p.frequency
FROM
  ( SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time > NOW() - INTERVAL 3 DAY
      AND sid <> ''
    GROUP BY sid
    ORDER BY COUNT(*) DESC
    LIMIT 50
  ) AS p
  JOIN music AS m
    ON m.sid = p.sid
WHERE m.perms <> 'c'
ORDER BY m.frequency DESC ;

As for efficiency, it's better to tune the first query separately and then this combined one.

An index on plays (sid, time) should help for the first query. For the second, if there is an index on music (sid), it should be quite efficient as it's a join to only 50 rows of music.


From MS:

When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Where possible I'd run it in a dev environment first and run sp_whoisactive @get_locks=1. This will give you some reassurance over what's going to happen in terms of locking/blocking. Appreciate this may not be possible with a massive DB.

Regarding the mirror, TRUNCATE is still a logged operation so the table will also be truncated on the mirror. This may help https://www.sqlservercentral.com/forums/topic/truncate-table-on-mirrored-db


You get NULL values probably because MySQL cannot determine automatically that first column in you CSV is a timestamp.
You could try to change table structure so first column becomes a VARCHAR, then import data, then transform dates using mysql date functions (change date to mysql standard format 'Y-M-D H:i:s'), then convert back this column to timestamp.

Alternatively, you could use LOAD DATA INFILE to insert CSV data directly, but for first column use SET directive in LOAD DATA INFILE to change first column data to MySQL standard format. Example:

LOAD DATA INFILE youfile.csv
  INTO TABLE yourtable
  FIELDS TERMINATED BY ","
  LINES TERMINATED BY "\n"
  (@var1, col_name_2, ...) SET col_name_1 = STR_TO_DATE(@var1,'%m/%d/%Y %k:%i');

This converts date to MySQL format while importing data.


Instead of looking on the json functions docs page, look instead on the aggregate functions docs page. jsonb_object_agg() will aggregate the object without an additional level of nesting as seen in this demo.

select
    b_d.b_id,
    jsonb_object_agg(c.name,json_build_object('d1',d.d1,'d2',d.d2)) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

...returns the original object as desired.

{
    "c1 ": {
        "d1": 1,
        "d2": 2
    },
    "c2 ": {
        "d1": 1,
        "d2": 2
    },
    "c3 ": {
        "d1": 3,
        "d2": 4
    }
}

See also


I've seen this behavior as well. Mine was due to server memory, which I'll explain in the guidelines. This was pesky because it was intermittent.

With partial cache, the cache starts off empty then queries until it finds a match. If you have multiple matches, the first one wins. With full cache, if you have multiple matches, I'm not sure which would win. Probably the first in the cache order.

Partial cache has an option for miss cache, which will remember which records don't have matches and won't query them again. This will be a problem if you are inserting into the table that you are doing a lookup on. Also with full cache, if your source contains duplicates, the second won't get a match after the first is inserted which will be a problem if you want to suppress all but the first.

Here are a few guidelines that I try to follow when using lookups:

The lookup will never swap cache to disk. If it runs out of memory, the task will fail. If this is your issue, get more ram or try partial.
Use integers whenever possible. If the field is string but can be cast to int, do it. This remove case and white space issues all together
Trim strings. White space will give you fits.
If it is a string, upper case both sides before comparing (lookups are case sensitive and will fail if the cases don't match).
Null never equals Null. If the column is nullable, replace both sides with "UNKNOWN" if string and a irrational number like -999 if it is integer.
If the column is empty, replace both sides with the text "EMPTY"

These are a pain in the butt, but it's better than getting a call at 3am because a tier 1 customers ETL failed.


SQL Server Linked Server error and Query Problem

Need some more information for the Linked Server error - asked a question in a comment for more info there and will clean this up based on your thoughts there. Though it appears as though you are experiencing issues with kerberos delegation on the face of it. (More info here)

For the second question - it sounds like you are just asking how can that user connect directly to the SQL2005 instance without using a linked server. If I've understood correctly - you just grant their login access to the SQL Server 2005 server (either a SQL authenticated login or a domain login) and grant them the database and server access they need. Then they can use SQL Server Management Studio or whatever tool you use to connect and connect directly.


SQL Server 2016 Restore Failure on DB Using Clustered Shared Volumes (Error 7627)

In Windows Failover Cluster 2016, you need to ensure all storage (except for node-local-tempdb) is configured as a dependent resource in the SQL Server cluster role.

In the example below, you can see this cluster has two disks, Disk_F and Disk_G; those are the only places where SQL Server will allow us to create data and log files for SQL Server.

When restoring a SQL Server 2005 database that has a full text catalog, you likely need to specify the location of the full-text catalog files, otherwise SQL Server will attempt to put them in the default location, which may not be on a cluster shared volume.  Use RESTORE FILELISTONLY FROM DISK = '\path\to\backup'; to determine the files that exist in the backup.  Ensure your restore statement has a MOVE clause for each file in the backup.

As a workaround, you might try restoring the SQL Server 2005 full-text-search-enabled database onto a non-clustered SQL Server 2016 instance, then take a backup of the database from the 2016 instance to restore onto the clustered 2016 instance.  I'm thinking it's possible there is a bug in the restore process for old full-text-enabled databases onto modern clustered instances.


No, it shouldn't prevent the table from being altered. Though, if you drop the underlying table a view depends on, or alter/remove the columns from the table the view uses, the view can become invalid. You can check what views are invalid in your system with the following query:

sql> select name from dba_objects where object_type = 'VIEW' and status = 'INVALID';

You should fix the problems before the view can be made VALID. The view can be compiled manually:

sql> alter view foo compile;

Or you can allow the system automatically compile it when someone attempts to query the view. However manual compilation is the recommended way because it reduces the execution time of the first query. Usually views and other invalid objects (procedures, triggers etc.) are recompiled in bulk using a supplied script utlrp and this is a routine task for a DBA.

If you want to create the view but the base table doesn't yet exist, you can use the option FORCE:

sql> create force view foo as select * from moo;


One way of trying to match something against array elements is using ANY.  The only thing is to find the right operator that can be used with it.  In this case, <@ will be our friend (you can find it and others at the right page of the docs).  When that's done, it becomes easy:

SELECT * FROM test WHERE '{"key2": "456"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │                  json_key
────┼─────────────────────────────────────────────
  1 │ {"{\"key1\":\"123\"}","{\"key2\":\"456\"}"}
  2 │ {"{\"key1\":\"789\"}","{\"key2\":\"456\"}"}

To prove it really does what we expect:

test=# SELECT * FROM test WHERE '{"key1": "456"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │ json_key
────┼──────────

test=# SELECT * FROM test WHERE '{"key1": "123"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │                  json_key
────┼─────────────────────────────────────────────
  1 │ {"{\"key1\":\"123\"}","{\"key2\":\"456\"}"}

Watch it in action on dbfiddle.


Delete from table causes the PostgreSQL database to enter recovery mode

As suggested by @a_horse_with_no_name, there is one bugfix related to crashes with DELETE statements here and here.

I waited for the problem to occur again. After I updated to version 11.1, the delete statement worked as expected.


Most of that model looks very good to me.
I think the piece you're missing is the concept of a package.
I would make some products packages (or sets) of products.
product
product_id
product_name (could be "diamond package" rather than "ski")
sale_price (if product available for sale)
is_set (boolean indicator that this is a set, not a single item)
set_detail
set_detail_id (just a serial / autonumber)
set_product_id (references the product_id of the set)
set_item_id (references the product_id of the item within the set, e.g. ski or boot)
With that, I believe the rest of your model, including the complicated pricing, works.


If you have SQL Server and SSIS, you should also have Reporting Services.

This can generate Word documents. This can be interactive, or you can use URL access, or SOAP calls to get at data.

May not be ideal, but it takes away the need to try and write Word documents via scripting or code: you just need some way to invoke what MS provide


How are other computers able to connect to my database

UDP 1434 is only for the discovery of database and port. If you still have named pipes enabled and provide the correct name or IP Adress of the host where your database is running and it runs on default port 1433 you will be able to connect. With right username + password.


Slow MySql server - even on a super fast server

I think you should change your server parameter to optimize server performance, you can go through this Percona Wizard https://tools.percona.com/wizard

Also you can refer a blog post http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ this shows what parameter to consider to get optimize server performance.

I personally recommend that you should upgrade your MySQL server to latest v5.6 GA and start using it as it has lot of optimization and InnoDB improvements.


One of the advantages of the FRA is that it is self-managing. IF you use the FRA, then when there is space pressure on it, any files that can be deleted will be deleted - automatically.  With the FRA, you don't need your powershell scripts to try to manage your backup storage.  In fact, you don't want your powershell script to manage your storage.  Let Uncle Oracle and rman do it for you.  You say the directories and file names it will create create are not the same as your PS scripts?  Why does that matter, since Oracle is managing it all for you.
One caveat that a lot of people miss.  Let's say you declare your FRA to be at '/u01/oracle/FRA'.  Now, say you specifically specify a backup destination format of '/u01/oracle/FRA/mydb/20201008/'.  Doing so is not using FRA functionality, and those files will not be known to be in the FRA, even though they are there and consuming space thought to belong to the FRA.


ODBC Data Source Administrator: Driver name is an empty string

I had a key in this registry path that was not pointing to anything.  So I removed it.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

(this is the 64 bit window that pops up)

Realize that there is also a 32 bit window for ODBC that exists for this registry path:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI

C:\Windows\SysWOW64\odbcad32.exe

(Yes, even though it says WOW64, it means 32 bit.)


Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?

Generally speaking, no. SQL Server compiles the whole batch at the current scope before execution so referenced entities have to exist (statement-level recompilations may also happen later). The main exception is Deferred Name Resolution but that applies to tables, not columns:

  Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

Common workarounds involve dynamic code (as in Joe's answer), or separating the DML and DDL into separate batches.

For this specific case you could also write:

BEGIN TRANSACTION;

    ALTER TABLE dbo.foo
        ALTER COLUMN a varchar(11) NOT NULL
        WITH (ONLINE = ON);

    EXECUTE sys.sp_rename
        @objname = N'dbo.foo.a',
        @newname = N'b',
        @objtype = 'COLUMN';

COMMIT TRANSACTION;

You still will not be able to access the renamed column b in the same batch and scope, but it does get the job done.

With regard to SQL Server, there is a school of thought that says mixing DDL and DML in a transaction is not a great idea. There have been bugs in the past where doing this has resulted in incorrect logging, and an unrecoverable database. Nevertheless, people do it, especially with temporary tables. It can result in some quite hard-to-follow code.


Why isn't transaction log free space always reused?

You may have got the answer already, but it interested me so I thought I'd take a look, and expand a bit more with sources.

As hinted briefly above, the extra space reserved for a possible rollback.
As I can't say it any better, Microsoft TechNet describes it thus:

  Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.
  - Log Architecture and Management

So basically your transaction required that "extra" space reserved until it was actually committed, as that space was not available in your log file, it grew. Your transaction was committed (and therefore not rolled-back), and so that space was not actually needed.

I won't labour the point further, but just this might be of use too:

The ever-wise Aaron Bertrand talks about some events that SQL Server tracks, including log growth. These might help you track down what's happening with your log autogrows in the future


SSIS: "The buffer manager cannot write 8 bytes to file"

You don't have sufficient disk space to process everything you're trying to do concurrently.  Data flow operations that spill to disk and temp files that get created are just some of the many reasons you might run out of space.

You should be able to run Perfmon during one of these runs to see how disk space and activity play out.

It's next to impossible for us to recommend anything without intimate knowledge of your system.  The fact you're running several processes concurrently - where the actual speed and state of each process can vary widely from run to run - makes it impossible to know when or why the processes are encountering the "perfect storm" of starving each other of disk space.

You will have to understand the resource usage of each one of your processes independently then you'll be able to know if you should even try to run them concurrently.


Problem when add administrator for Analysis Server ( SQL Server 2008 R2 )

After days with this problem, I deleted that number, and could add users with no problem.


MySQL status variable Handler_read_rnd_next is growing a lot

First of all, let us look at the definition of Handler_read_rnd_next.

According to the MySQL Documentation on Handler_read_rnd_next:

  The number of requests to read the next row in the data file. This
  value is high if you are doing a lot of table scans. Generally this
  suggests that your tables are not properly indexed or that your
  queries are not written to take advantage of the indexes you have.

Now, look at your query:

select data from test where data = 'vwx';

You said that the table has 10 rows. As a rule of thumb, the MySQL Query Optimizer will dismiss the use of an index if the number of rows that needs to be examined is greater that 5% of the total number of rows.

Let us do the math. 5% of 10 rows is 0.5 rows. Even if the number of rows need to locate your data is 1, that is greater than 0.5. Based of this lower number of rows and that index rule I just mentioned, MySQL Query Optimizer will always do a table scan.

Since the column data is itself indexed, instead of a table scan, mysql was performed an index scan.

If you know for a certainty that the test table will never grow, you can remove all indexes and let table scans happen. Handler status variables should stop incrementing.


Cannot open database properties dialog on a database NOT in an availability group

Closing and re-starting SQL Server Management Studio fixed the problem.

Thanks to @SeanGallardy-Microsoft.


SQL Server 2017: How does Batch Mode Memory Grant Feedback Work?

What's Memory Grant Feedback All About?

Batch Mode Memory Grant Feedback attempts to right-size memory grants for queries, correcting for both over- and under-estimates.

When a query runs that requires a memory grant, the optimizer will ask for a grant of a size that it thinks will keep all row operations in memory.

Things that commonly require memory grants:

Sorts
Hash Joins

When too much memory is asked for and granted, concurrency may suffer as a result. Memory is a finite resource, and not everything can use all of it all the time. There's no such thing as unlimited access to such resources.

When too little memory is asked for, queries may spill to disk. Look, no one wants their queries spilling anywhere. Disk is dreadful.

How Does The Magic Work?

When a plan that requires a memory grant executes and is cached, actual memory needed to run the plan is recalculated, and the plan information is updated accordingly. Right now, it requires the presence of a ColumnStore index to achieve Batch execution mode.

What If The Magic Isn't So Magical?

This feature does have a terminating point in which it will fall back to the original memory grant. If queries run that need constant recalculating, our magical feature will give up. Eventually. As of this writing, I don't have all of the implementation details on when it will quit.

How Do I Know If It's Working?

You can use Extended Events:

You may also observe it during regular query tuning over multiple runs in the actual execution plan.

Can You Show Me An Example?

Of course! Here's a stored procedure. Under the right circumstances, it will ask for an incorrect memory grant.

CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant (@OwnerUserId INT)
AS
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts_cx AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO

The query plan has a warning on the select operator.

Sadness indeed! Our query asked for too much memory.

On second execution, the warning will disappear.

I've Heard About Some Sneaky Tricks...

Itzik Ben-Gan and Niko Neugebauer have both come up with workarounds to features and operators that require Batch Mode execution.

Create a filtered, non-clustered ColumnStore index with a WHERE clause that contains 0 rows
Join your table to a temp table with a Clustered ColumnStore index on it with no rows using a superfluous Left Join

Both methods are valid workaround to get this to work!

Creating an empty nonclustered ColumnStore index:

/*Itzik's Trizik*/
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_helper
    ON dbo.Posts
(
    Id,
    AcceptedAnswerId,
    AnswerCount,
    ClosedDate,
    CommentCount,
    CommunityOwnedDate,
    CreationDate,
    FavoriteCount,
    LastActivityDate,
    LastEditDate,
    LastEditorDisplayName,
    LastEditorUserId,
    OwnerUserId,
    ParentId,
    PostTypeId,
    Score,
    ViewCount,
    IsHot )
    WHERE ( Id = -2147483647 AND Id = 2147483647) -- eez impossible!

Running a different version of the proc against the table with the empty index:

CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt1 (@OwnerUserId INT)
AS
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO

Using the same value:

EXEC dbo.LargeUnusedGrant_alt1 @OwnerUserId = 8672;
GO

First run: sad memory grant

Second run: happy memory grant

Superfluous left join:

/*Niko's Triko*/
CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt2 (@OwnerUserId INT)
AS
BEGIN

    CREATE TABLE #t1 (Id INT, INDEX cx_whatever CLUSTERED COLUMNSTORE);

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    LEFT JOIN #t1 ON 1 = 1
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO

EXEC dbo.LargeUnusedGrant_alt2 @OwnerUserId = 8672;
GO

First run: sad memory grant

Second run: happy memory grant


Technically no there isn't a standard for table naming conventions. As long as you use something consistent across your environment there shouldn't be a problem. It should also be well documented as to why you are using this prefix.

That being said however here are few rules that I follow. IAlwaysBookEndMyTables the reason for doing this is it make it clear to read without any spaces or underscores. The reason I don't use underscores is that I'm a SQL server guy and they can be used as a wild card. When naming a lookup table I've typically used LU. Once again this doesn't matter as long as you are doing things consistently. LKMetalsMetalType isn't really any different from LUMetalsMetalType.

One other thing be as descriptive with your table names as possible. Whoever picks up after you leave a project should be able to look at your table structure and have a good idea what's going on. There's plenty of room for a long table name but very limited sanity in trying to figure out what the heck the Pl_uva table means. FYI that was just an example.

I shouldn't have to say this and am assuming you already know but I'll say it anyway. Do not use reserved words or include spaces in your table names. I swear to god I saw a guy on Stack Overflow yesterday who had named a column "from" so it was literally.

SELECT
    [FROM]
FROM HisTable


Machine account causing a Token-based server access validation failed with an infrastructure error

It seems I have found the issue.  It was System Center Operations Monitor trying to log into SQL via the cluster name however it was set to use Local System and didn't have access.  I changed this to a specific monitoring account and its working now.


Get Results from DMV Query into SQL Server Table

In SSAS, there's no such thing as a table; it's all multi-dimensional stuff. What you need to do is get the results into a table in a database engine instance.

Here's one way to do it:

Create a linked server to SSAS on your management SQL Server database engine instance. (Note: you'll need to install the SSAS drivers if the two instances aren't on the same box.)
Create a job in SQL Agent that periodically queries the SSAS DMV over the linked server and dumps the results into a relational table.


How to pivot a column showing its content as new columns

Found the answer in the next url:
https://vitobotta.com/2015/12/10/mysql-dynamic-column-name/
And I post the example for those who have a similar problem

The final query is:

DROP TABLE IF EXISTS tmp_proyecto;

CREATE TEMPORARY TABLE tmp_proyecto AS
SELECT DISTINCTROW a.pro_id, a.programa, b.proyecto, CAST(0 AS DECIMAL(8,2)) as total
FROM programas as a
INNER JOIN presupuesto_aprobado as b ON a.direc = b.direc
WHERE a.direc='31111-0601' AND a.pro_ano=2018
ORDER BY proyecto, pro_id;

SELECT CONCAT('
SELECT pro_id, programa, ',proyectos,'
FROM tmp_proyecto
GROUP BY pro_id
ORDER BY pro_id'
)
INTO @query
FROM
(
SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN proyecto=''',proy_actual,''' THEN total END), ''-'') AS "',col_name,'"')) proyectos
FROM (
SELECT proy_actual, CONCAT('P_',proy_actual) AS col_name
FROM (SELECT DISTINCT proyecto AS proy_actual FROM tmp_proyecto) AS unicos
) proy_como_campos
) result;

PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;

As a result:

pro_id  programa                              P_K0002   P_K0003
191     Empresa Exportadora                   0.00      0.00
192     Apertura de Empresas                  0.00      0.00
223     Promoción y Atracción de Inversiones… 0.00      0.00

Thanks anyway


When should I extract a field into a new table?

It depends... as a lawyer might say
The columns in your car table might seem to be data that will not change but let's take a closer look:

condition: this is definitely a temporal fact.  Today the car is new, drive it off the lot and it is used, wait long enough and it is an antique.  You might have to allow for the possibility that the program will store data for the same vehicle more than once during it's lifetime.  Lease it as new, get it back as used, sell it....
release year: will an auto manufacturer always officially release a car on a discrete date?  Or is it possible that it will be shown at an auto show this year, released in Europe next year and released in North America a year after that?

My experience with data modelling is that the most common mistake is to assume that a fact like a year or a condition will never change when it is really slowly changing data.

For your purposes if there is any possibility that a release year could have more than one value or span over a year end or be amended by the manufacturer then you could have a release_year_id in CAR which is a foreign key to the RELEASE_YEAR table which would have:

ID NUMBER(10)
YEAR_START  DATE
YEAR_END    DATE
GEO_AREA    NUMBER(10)
CURRENT_VALUE  NUMBER(1) and constrained to value of 1 or 0

The current value field would be implemented if there is the possibility of the start of the release year or end of the release year changing and you wanted to record this.  Further information is described temporal databases.

Your final reality check is to create some sample reporting queries and find out how much it hurts.  This will give you a good indication of whether to normalize or denormalize

As Jeff Atwood wrote

  normalize until it hurts, denormalize until it works


This should work (sqlfiddle):

SELECT distinct SUBSTRING_INDEX( SUBSTRING_INDEX( transcriptions, ' ', l10.n * 10 + l1.n + 1), ' ', -1) AS transcription
FROM data
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UN ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) l1
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) l10
WHERE LENGTH(transcriptions) - LENGTH( REPLACE( transcriptions, ' ', '')) >= l10.n * 10 + l1.n
;

Please note that it may not be extremely efficient. You may consider changing your data model and avoid spliting string using SQL.

Each CROSS JOIN (l1 and l10) create a number table from 1 to 100 (10*10). If strings are longer that 100, extra CROSS JOIN must be added (10*10*10 => 1.000, 10*10*10*10 => 10.000, ...).
The number table is used on characters at each position in the number table and search for spaces.

Sample Data:

CREATE TABLE data(id int, transcriptions varchar(100));
INSERT INTO data(id, transcriptions) VALUES
    (1, 'th i s')
    , (2, 'i s')
    , (3, 's o m e')
    , (4, 't e st')
    , (5, 'd a t a');

Output (no specific order):

transcription
i
s
o
e
a
m
st
t
th
d


How to convert unsigned int accidentally stored as signed int?

A smallint in SQL Server is stored as a signed int16, or 2 bytes, or 16 bits with the 16th bit reserved to indicate the sign (0 = positive, 1 = negative).

Here are your two examples, converted to the original un-signed int (uint), and int16 values: It looks like in your examples that your bits have been shifted to the left by 8 bits. Noticed the bolded items below are the same.

Original Value 1: uint = 141    = 0x008D = 00000000 10001101
Was converted to: int16  = -29440 = 0x8D00 = 10001101 00000000

Original Value 2: uint = 362   = 0x016A = 00000001 01101010
Was converted to: int16  = 27136 = 0x6A00 = 01101010 00000000

Unfortunately, in your second scenario (the conversion from 362 that resulted in 27136), the 1 in the 9th bit position (00000001 01101010) has been dropped during your data conversion.

It appears your conversion process was something like:

Shift the original # to the left by 8 bits (this will add 8 bits of 0 padding added on the right-hand side),
Convert to smallint (16 bit data type), in a way that truncated the 8 left-most bits.

In this case, then any # greater than 256 in your original dataset would have been converted to a # that represents only the 8 right-most bits with 8 bits of 0 padding adding on the right, and you will not be able to recover any number from your original data that was above 256 because it looks like the left-most 8 bits have been truncated during the failed data conversion to a 16 bit data type.

For example, given the # 27136 (01101010 00000000) in your current data, you cannot know if this original # was 106 (00000000 01101010) or 362 (00000001 01101010) or 874 (00000011 01101010), or any # that originally had a 1 within the position of bits 9-16.


SQL Select query is taking so long time on new Server

DISTINCT is not a function.  It applies to the whole row.  And it is probably unnecessary.  It takes an extra pass over the data to perform the DISTINCT functionality.
LIMIT 200000 seems quite unrealistic.  What is the goal?
The query, without a WHERE clause, will scan the entire tbcc_l_lists table.  That takes time if the table is big.
COUNT(*) is the usual pattern.  COUNT(x) is often misused; this means "count the row if x is NOT NULL.  Or did you really want that test?
Please provide EXPLAIN SELECT so we can see what parts are being inefficient.
That may lead to requesting SHOW CREATE TABLE for each table.
You say the configuration is the same -- are you referring to both the memory size and my.cnf?


Is it possible to accidentally use some paid feature in Oracle XE database while in an Enterprise environment?

Oracle Express Edition is free software that can be used even for commercial purpose.

What the DBA said is true for Enterprise Edition where some features can not be completely disabled (e.g. Advanced Compression), and it was true for Standard Edition in versions below 11g (e.g. Diagnostic Pack).

Express Edition will be fine.

For a personal learning/development environment, you could even use Enterprise Edition, if you will be the only one using it.


Join tables but keep matches in a single row

You need either 2 more joins, or to do a GROUP BY first (to collapse the two rows into one) and then join Map_ServerSB and SB (twice). In a query:

SELECT
    s.Id     AS ServerId,
    s.Name   AS Server_Name,
    sb1.Name AS SB_1,
    sb2.Name AS SB_2
FROM
    (
        SELECT
           ms.IdObj1,
           MIN(ms.IdObj2)
               AS sb_id_1,
           CASE WHEN MIN(ms.IdObj2) <> MAX(ms.IdObj1)
               THEN MAX(ms.IdObj1) ELSE NULL
           END
               AS sb_id_2
        FROM
            Map_ServerSB AS ms
        GROUP BY
            ms.IdObj1
    ) AS ms
    JOIN Server AS s
      ON s.Id = ms.IdObj1
    JOIN SB AS sb1
      ON ms.sb_id_1 = sb1.Id
    LEFT JOIN SB AS sb2
      ON  ms.sb_id_2 = sb2.Id
  ;


Normalization for postal addresses in US (address, county, city, state, zipcode)?

I deal with millions of existing international addresses.  The following design works for my project:

Address Table

AddressLine1 varchar(255)
AddressLine2 varchar(255)
City varchar(50)
PostalCode varchar(20)
State      varchar(50)
CountryID  int  (FK to Country table)

Avoid the temptation to normalize postal codes and states, unless you really do have a master list that is updated frequently.

Countries are easier to manage, so they belong in their own lookup table. Master lists can be found online readily.


Can I rely on the estimate execution plan to recommend indexes?

There's a limitation where it will not give you missing indexes when the query plan is trivial, perhaps that's what you are encountering.

Right click on the SELECT operator and select Properties. Check if the Optimization Level is TRIVIAL.

I use the estimated execution plan a great deal when performance troubleshooting. It's rare that I'll have the actual plan. Regarding missing indexes, I don't just add what the plan thinks is missing. I test the index, variations of it, etc.


Writing transactions to .MYD file to take Backup

Your problem may have to do with the session you ran FLUSH TABLES WITH READ LOCK;.

If you launched FLUSH TABLES WITH READ LOCK; like this:

# mysql -uroot -p... -e"FLUSH TABLES WITH READ LOCK;"

all that would do is close all open tables, reopen those tables, and the session dies. The lock is not maintained.

You would have to launch FLUSH TABLES WITH READ LOCK; like this:

# mysql -uroot -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &

This forces the session to stay open and maintain the lock. You would have to open another session, get the process ID of the SELECT SLEEP(86400), start your backup, and then kill that DB Connection when your backup is complete.

Here is a sample script on how to mysqldump all databases locked with FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400):

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Here are my other posts where I discuss this and other backup variations involving FLUSH TABLES WITH READ LOCK:

Safest way to perform mysqldump on a live system with active reads and writes? (Jun 20, 2012)
Difference between backup methods: Oracle and MySQL (Apr 18, 2012)
Quick MySQL Backup (1 file per table) (Jan 31, 2012)
mysql db backup excluding specific tables (Nov 30, 2011)
Quick MySQL Backup (Oct 01, 2011)
How can I optimize a mysqldump of a large database? (Apr 17, 2011)


Part of your initial query is as follows.

  FROM   [dbo].[calendar] a
          LEFT JOIN [dbo].[colleagueList] b
            ON b.[Date] = a.d
   WHERE  DAY(a.[d]) = 1
          AND a.[d] BETWEEN @dateStart AND COALESCE(@dateEnd,@dateStart)

That section of the plan is shown below

Your revised query  BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart) has this for the same join

The difference seems to be that ISNULL simplifies further and as a result you get more accurate cardinality statistics going into the next join. This is an inline table valued function and you are calling it with literal values so it can do something like.

 a.[d] BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart)
 a.[d] BETWEEN '2013-06-01' AND ISNULL(NULL,'2013-06-01')
 a.[d] BETWEEN '2013-06-01' AND '2013-06-01'
 a.[d] = '2013-06-01'

And as there is an equi join predicate b.[Date] = a.d the plan also shows an equality predicate b.[Date] = '2013-06-01'. As a result the cardinality estimate of 28,393 rows is likely to be pretty accurate.

For the CASE/COALESCE version when @dateStart and @dateEnd are the same value then it simplifies OK to the same equality expression and gives the same plan but when @dateStart = '2013-06-01' and @dateEnd IS NULL it only goes as far as

a.[d]>='2013-06-01' AND a.[Date]<=CASE WHEN (1) THEN '2013-06-01' ELSE NULL END

which it also applies as an implied predicate on ColleagueList. The estimated number of rows this time is 79.8 rows.

The next join along is

   LEFT JOIN colleagueTime
     ON colleagueTime.TC_DATE = colleagueList.Date
        AND colleagueTime.ASSOC_ID = CAST(colleagueList.ID AS VARCHAR(10))

colleagueTime is a 3,249,590 row table which is (again) apparently a heap with no useful indexes.

This discrepancy in estimates affects the join choice used. The ISNULL plan chooses a hash join that just scans the table once. The COALESCE plan chooses a nested loops join and estimates that it will still just need to scan the table once and be able to spool the result and replay it 78 times. i.e. it estimates that the correlated parameters will not change.

From the fact that the nested loops plan was still going after two hours this assumption of a single scan against colleagueTime seems likely to be highly inaccurate.

As for why the estimated number of rows between the two joins are so much lower I'm not sure without being able to see the statistics on the tables. The only way I managed to skew the estimated row counts that much in my testing was adding a load of NULL rows (this reduced the estimated row count even though the actual number of rows returned remained the same).

The estimated row count in the COALESCE plan with my test data was in the order of

number of rows matching >= condition * 30% * (proportion of rows in the table not null)

Or in SQL

SELECT 1E0 * COUNT([Date]) / COUNT(*) * ( COUNT(CASE
                                                  WHEN [Date] >= '2013-06-01' THEN 1
                                                END) * 0.30 )
FROM   [dbo].[colleagueList]

but this does not square with your comment that the column has no NULL values.


Locking in Postgres for UPDATE / INSERT combination

I have heard of concurrency problems like that in MySQL before. Not so in Postgres.

Built-in row-level locks in the default READ COMMITTED transaction isolation level are enough.

I suggest a single statement with a data-modifying CTE (something that MySQL also doesn't have) because it's convenient to pass values from one table to the other directly (if you should need that). If you don't need anything from the coupon table you can use a transaction with separate UPDATE and INSERT statements just as well.

WITH upd AS (
   UPDATE coupon
   SET    used = true
   WHERE  coupon_id = 123
   AND    NOT used
   RETURNING coupon_id, other_column
   )
INSERT INTO log (coupon_id, other_column)
SELECT coupon_id, other_column FROM upd;

It should be a rare thing that more than one transaction tries to redeem the same coupon. They have a unique number, don't they? More than one transaction trying at the same moment in time should be much rarer, yet. (Maybe an application bug or somebody trying to game the system?)

Be that as it may, the UPDATE only succeeds for exactly one transaction, no matter what. An UPDATE acquires a row level lock on each target row before updating. If a concurrent transaction tries to UPDATE the same row, it will see the lock on the row and wait till the blocking transaction is finished (ROLLBACK or COMMIT), then being the first in the lock queue:

If committed, recheck the condition. If it's still NOT used, lock the row and proceed. Else the UPDATE now finds no qualifying row and does nothing, returning no row, so the INSERT also does nothing.
If rolled back, lock the row and proceed.

There is no potential for a race condition.

There is no potential for a deadlock unless you put more writes into the same transaction or otherwise lock more rows than just the one.

The INSERT is care-free. If, by some mistake the coupon_id already is in the log table (and you have a UNIQUE or PK constraint on log.coupon_id), the whole transaction will be rolled back after a unique violation. Would indicate an illegal state in your DB. If the above statement is the only way to write to the log table, that should never occur.


Is MSDTC required for SQL Server 2012 Fail Over Cluster?

MSDTC is not required for a SQL Server 2012 fail over cluster.  However, if you plan to use Linked Servers, then you will need to create a clustered MSDTC resource.  The good news is that can be setup after the cluster is already built and after SQL Server has been installed.


Postgresql 9.0 adding new database to streaming replication

Hz.Root, your question has many open ends but I will try to answer.

  What is the best way of doing this?

Just create a new database in master server and populate it.

  I have a backup file about 20GB. should i restore to that cluster?

Yes. To be precise, you restore it to the database, not to the cluster.

  can standby server have all the data?

Standby server MUST have all the data - that is it's purpose. Every database, and every other object that is in primary PostgreSQL cluster, will be replicated to secondary server.


Removing duplicate rows and changing related foreign keys in PostgreSQL

From http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no

SELECT
  confrelid :: REGCLASS,
  af.attname AS fcol,
  conrelid :: REGCLASS,
  a.attname  AS col
FROM pg_attribute af, pg_attribute a,
  (SELECT
     conrelid,
     confrelid,
     conkey [i]  AS conkey,
     confkey [i] AS confkey
   FROM (SELECT
           conrelid,
           confrelid,
           conkey,
           confkey,
           generate_series(1, array_upper(conkey, 1)) AS i
         FROM pg_constraint
         WHERE contype = 'f') ss) ss2
WHERE af.attnum = confkey
  AND af.attrelid = confrelid
  AND a.attnum = conkey
  AND a.attrelid = conrelid
  AND confrelid :: REGCLASS = 'my_table' :: REGCLASS
  AND af.attname = 'my_referenced_column';

It produces

   confrelid   |         fcol         |   conrelid    |     col
---------------+----------------------+---------------+----------------
   my_table    | my_referenced_column | another_table | some_column

Update: RhodiumToad on freenode #postgres channel gave a simpler query which however needs some parsing and filtering if only specific column is needed.

SELECT
  conrelid :: REGCLASS,
  conname,
  pg_get_constraintdef(oid)
FROM pg_constraint
WHERE confrelid = 'my_table' :: REGCLASS;

producing

  conrelid  |       conname        |         pg_get_constraintdef
------------+----------------------+----------------------------------------------------------------------------------------------------
 some_table | some_constraint_name | FOREIGN KEY (some_column) REFERENCES my_table(my_referenced_column) DEFERRABLE INITIALLY DEFERRED


Unfortunately, you can't define a foreign key against a partitioned table, only the partitions themselves - but, of course, the referencing table can have only on FK on the column in question...

So you have to either partition the referencing table, too, or live together with foreign-keylessness (with help from a trigger or the application code).

Let me note that similarly, you will have to define the eventual (unique) indexes on the child tables one by one.  This also means that unique constraints have to be defined this way, too.


They are binary logs. The system variable expire_log_days can be used to limit their growth.

They are used for replication and point in time recovery. Except for these functions they are not needed for the functioning of the database.


From you comment:

  "importe_trx" and "precio_venta" stores the same data (float) that are the prices

That means you are using float data type to store these numerical values.
Problem with the float data type is that it always store rounded values, it can not precisely represent decimal values. Each values is saved as (0 or 1)*1/2+(0 or 1)*1/4+(0 or 1)*1/8+..., so 18.42 + 24.56 can be 42.98000000001 in float terms. SSMS will round these numbers, so you will not see the difference.

To fix this problem you should use decimal data type. Never use floats in any accounting data.


SELECT fields from table having some conditions

The cleanest has to be:

SELECT T1.id
       COALESCE(t2.name, t1.name) as name
FROM t1
LEFT JOIN t2
     ON t1.id = t2.id


SQL access query to count and delete rows > n

I don't have access to a working copy of MS Access to test this but what you might be looking for is this:

delete * from table_name where ID not in(
select top 200 ID from table_name order by id desc
);


Failover Cluster, Availability Group, and Disaster Recovery

Is our understanding that all nodes need to be on the same Windows Failover Cluster for Availability Groups to work correct? We read it here but aren't fully sure of what's being said.

Yes this is correct. Although starting from SQL Server 2016 you have Distributed Availability Groups in which replicas can reside in different WSFC

  Is it possible to set up HA in this manner? Where Node1/Node2 share resources but Node3 has a separate instance which cannot ever be a part of Node1/Node2.

Yes this is very much possible and I have this in my environment. Please note that in this case you would not be able to leverage automatic failover of replicas from SQL Server FCI to standalone node 3. You would have to reply on manual failover. This is because the AG replica resides on FCI which has its own failover.


What role is able to grant role permissions on MSSQL 2016?

A restricted admin should only add users to roles through ALTER ROLE.  The ability to grant new permissions to a role or user should be reserved for a real admin.


Need help with query over three related tables

I believe you are wanting something a little like this;

SELECT DISTINCT
Subscriber.id
FROM Subscriber
 INNER JOIN ShippingAddress ON (Subscriber.id =
ShippingAddress.subscriber_id)
  LEFT OUTER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.shipping_address_id IS NULL OR NonDeliveryReport.resolved IS NOT NULL;


Filter rows by range dates and only get the rows in a range of hours


The gin_trgm_ops is an operator class over text. Your tsv column is a tsvector, and not text. So simply that won't work. A tsvector stores lexemes not words. That means the data you're searching for may not even be there. Observe,

SELECT to_tsvector('Christopher');
  to_tsvector
---------------
 'christoph':1
(1 row)

That means after you convert Christopher to a tsvector, you lose some data in the algorithmic affix stemmer. That's fine for FTS, it's part of the compromise and how you achieve the amazing speed. It may not be desirable if that's what you're looking for though, so you have two options.

Use lexeme prefix matching with :* (assuming the lexeme has the needed information, if it doesn't you may be able to hack either the text search configuration to not-stem, or the stemmer to stem to your workload)

## returns false
SELECT to_tsvector('Christopher') @@ to_tsquery('Chris');
## returns true
SELECT to_tsvector('Christopher') @@ to_tsquery('Chris:*');

Index the actual column you're using to generate the TSV or wish to search in, using the trigram operator classes (like gin_trgm_ops). If you're always doing left-anchored search, you may consider the more efficient text_pattern_ops.


Does full text search works with characters?

CONTAINS and LIKE are two completely different beasts.

As it is a full text index search the wildcards (% %) become redundant.

For your query I belive you would need to do something like:

SELECT *
FROM AMPP AS A
WHERE CONTAINS(A.NM, '"Amoxici*"');

Refer to MS Books Online


to avoid mentionned error, try changing the order:

CREATE OR REPLACE FUNCTION public.chown(in_schema character varying, new_owner character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;

  r record;
BEGIN
  object_types = '{type,table,table,sequence,index,view}';
  object_classes = '{c,t,r,S,i,v}';

  FOR object_type IN
      SELECT unnest(object_types) type_name,
                unnest(object_classes) code
  loop
    FOR r IN
      EXECUTE format('
          select n.nspname, c.relname
          from pg_class c, pg_namespace n
          where n.oid = c.relnamespace
            and nspname = %I
            and relkind = %L',in_schema,object_type.code)
    loop
      raise notice 'Changing ownership of % %.% to %',
                  object_type.type_name,
                  r.nspname, r.relname, new_owner;
      EXECUTE format(
        'alter %s %I.%I owner to %I'
        , object_type.type_name, r.nspname, r.relname,new_owner);
    END loop;
  END loop;

  FOR r IN
    SELECT  p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
    FROM    pg_catalog.pg_namespace n
    JOIN    pg_catalog.pg_proc p
    ON      p.pronamespace = n.oid
    WHERE   n.nspname = in_schema
  LOOP
    raise notice 'Changing ownership of function %.%(%) to %',
                 r.nspname, r.proname, r.args, new_owner;
    EXECUTE format(
       'alter function %I.%I (%s) owner to %I', r.nspname, r.proname, r.args, new_owner);
  END LOOP;

  FOR r IN
    SELECT *
    FROM pg_catalog.pg_namespace n
    JOIN pg_catalog.pg_ts_dict d
      ON d.dictnamespace = n.oid
    WHERE n.nspname = in_schema
  LOOP
    EXECUTE format(
       'alter text search dictionary %I.%I owner to %I', r.nspname, r.dictname, new_owner );
  END LOOP;
END;
$function$

Also read about REASSIGN OWNED

Alse here is info on relkind if you wander why I changed the order:

  r = ordinary table, i = index, S = sequence, v = view, m =
  materialized view, c = composite type, t = TOAST table, f = foreign
  table


What is the best way to automatically create a snapshot of the table at the certain time?

I'd use either of pg_dump or create table foo_log as select * from foo

Added: Actually I have one idea to add insert/update trigger for each table that should be shapshoted and write all changes to another table that is partitioned on daily basis.
Please tell me if it is a god idea or not?

Sounds good too. That works with rules too, e.g.:
create rule as on insert to yourtable_log__ins
do also
insert to yourlogs select new.*;

And then, daily:
begin;
create table yourlogs_[date] () inherits (yourlogs);
create or replace rule yourlogs__ins as on insert to yourlogs
do instead
insert into yourlogs_[date] select new.*;
commit;

The main difference with creating the table on the fly on a daily basis will be the overhead that you'll be introducing on every insert/update, as opposed to grabbing the updated rows all in one go.


Will the transaction log shrink automagically in SQL Server?

It will truncate automatically but that is very different to shrink. Truncation reclaims log space for re-use, shrinking physically reduces the file size to release space back to the OS. If your log has grown to its current size its likely that it will grow again if you shrink it.

I'd suggest getting a handle on what typical and maximum log usage is for your system. The query below (not mine, boosted from Glen Berrys DMV scripts) could be run manually or you could capture the output to a table via an agent job. If you log it to a table for a week or so you'll get a picture of typical usage and more importantly, when a process is causing the log to grow beyond what you expect.

SELECT
     db.[name] AS [Database Name]
   , db.recovery_model_desc AS [Recovery Model]
   , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
   , ls.cntr_value AS [Log Size (KB)]
   , lu.cntr_value AS [Log Used (KB)]
   , CAST(
        CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
        AS DECIMAL(18,2)
     ) * 100 AS [Log Used %]
   , db.[compatibility_level] AS [DB Compatibility Level]
   , db.page_verify_option_desc AS [Page Verify Option]
   , db.is_auto_create_stats_on, db.is_auto_update_stats_on
   , db.is_auto_update_stats_async_on, db.is_parameterization_forced
   , db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on
FROM sys.databases AS db
   INNER JOIN sys.dm_os_performance_counters AS lu
     ON db.name = lu.instance_name
   INNER JOIN sys.dm_os_performance_counters AS ls
     ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
   AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
   AND ls.cntr_value > 0
OPTION (RECOMPILE);

Transaction Log Truncation describes both the when and why log truncation occurs.

  If log records were never deleted from the transaction log, it would
  eventually fill all the disk space that is available to the physical
  log files. Log truncation automatically frees space in the logical log
  for reuse by the transaction log.

Factors That Can Delay Log Truncation is a useful reference for understanding why your log may fail to truncate and therefore grow larger than expected.


Efficient way to generate Time Slots for a schedule


SQL Server 2008 R2 - Change distributor schedule

Right click distributor job and change its schedule.


BACKUP AND RESTORE FileGroup (PRIMARY) - WHY after restore I still see Other FileGroups?

Is there any way to restore only the [PRIMARY] filegroup?

That is what you have already done. The other filegroups exist only in metadata at this stage. Piecemeal restore allows you to restore filegroups in stages, or mark them defunct if they are no longer needed. There is no way to delete them from the system metadata though.

After the restore operation in the question, the data on the secondary filegroup is not accessible, because that filegroup has not been restored (yet). You can't see everything in the SSMS GUI only. For example:

SELECT
    [FilegroupName] = F.name,
    F.data_space_id,
    F.is_default,
    F.is_read_only,
    DF.[file_id],
    [FileName] = DF.name,
    DF.state_desc,
    DF.is_read_only
FROM sys.filegroups AS F
JOIN sys.database_files AS DF
    ON DF.data_space_id = F.data_space_id;

The results show recovery is pending for the secondary filegroup files:

  If not, How can I get rid of the ReadOnlyFG after the restore?

You can't delete it from the metadata, but you can make it defunct by dropping it (something that is not normally possible for a filegroup that still contains files):

ALTER DATABASE PiecemealDB_NEW
REMOVE FILEGROUP ReadOnlyFG;

The query above now shows the filegroup as defunct rather than recovery pending:

Objects that were stored on the defunct filegroup cannot be accessed, modified, or dropped. The only way to get to a database state as if the defunct filegroup had never existed is to create a new database and copy the data you want to keep across to it.


You need to consider the storage engine you will be using for your project because MySQL caches differently for each storage engine.

MyISAM caches index pages only. InnoDB caches data and index pages.

Given that you have a table with large ids, I hope you are using MyISAM.

Here is a query to help you determine the best key_buffer_size:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.

UPDATE 2011-09-16 14:49 EDT

If you are using InnoDB, sizing the key_buffer_size will not help. InnoDB and MyISAM cache differently.

Here is the same kind of query to size your InnoDB Buffer Pool:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;


Can't create trigger in MySQL to make a simple audit

; at the end of INSERT statement is lost.

Use

CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log ( sales_id,     previous_amount,  new_amount)
VALUES                ( NEW.sales_id, OLD.sales_amount, NEW.sales_amount) ;

DELIMITER reassign is excess for single-statement trigger.


Database Design For reporting comparison results

This is common task which may be solved using FULL OUTER JOIN by any NOT NULL expression which is unique over each separate table and is present in both tables. Mismatches are detected by column compare, missing rows - by NULL value for joining expression in according table.
SELECT COALESCE(t1.id, t2.id) id,
       CASE WHEN t1.id IS NULL
                 THEN 'Absent in TableA'
            WHEN t2.id IS NULL
                 THEN 'Absent in TableB'
            WHEN t1.columnX != t2.ColumnX
                 THEN 'Differs in ColumnX at least' -- may differ in another columns too
            WHEN t1.columnY != t2.ColumnY
                 THEN 'Differs in ColumnY at least' -- but identical by columnX due to previous condition
            -- and so on
            ELSE 'Are identical'
            END AS MismatchType
FROM tableA t1
FULL OUTER JOIN tableB t2 ON t1.id = t2.id

If you need detailed difference diagnosis then you must build complex conditions included all columns in interest, like
WHEN t1.columnX != t2.ColumnX AND t1.columnY != t2.ColumnY AND t1.columnZ = t2.ColumnZ
    THEN 'Differs in ColumnX and ColumnY'


Calculate balance for every row by sequentially subtracting each row's amount from an initial sum

This is a "running total" type of problem: each row's total is calculated based on that row's value added to, or subtracted from, the previous row's total.

Since you are using SQL Server 2014, Transact-SQL has built-in syntax available to you to help you with getting the results.

Using the simplified model of "One deposit per month, many withdrawals per month", the SQL statement could go like this:

SELECT
  d.TotalAmount,
  w.WithdrawAmount,
  Balance = d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)
FROM
  dbo.Deposit AS d,
  dbo.Withdrawal AS w
WHERE
  d.DepositDate  >= start_of_this_month AND d.DepositDate  < start_of_next_month
  AND
  w.WithdrawDate >= start_of_this_month AND w.WithdrawDate < start_of_next_month
;

The WHERE clause is supposed to filter the tables down to one deposit row from Deposit and the however many corresponding withdrawals from Withdrawal. If the tables support multiple accounts and the amounts need to be further related by account, you may want to replace the FROM clause with something like this:

FROM
  dbo.Deposit AS d
  INNER JOIN dbo.Withdrawal AS w ON d.AccountNumber = w.AccountNumber

The SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC) expression calculates a running WithdrawAmount total for each row. So, the total increases with each row (sorted in the ascending order of WithdrawDate) – thus with each row an ever increasing amount is subtracted from TotalAmount, drawing Balance nearer to 0.

The above query will give you each row's balance but not the status. To get the status, you will need to reference the Balance value to compare it 0 and select a corresponding status string to return. Balance is a calculated column and in order to be able to reference it you need to nest the above query and reference Balance at the outer level. Nesting could be done with either a derived table or a common table expression (CTE). This query uses a CTE:

WITH balances AS
  (
    SELECT
      d.TotalAmount,
      w.WithdrawAmount,
      Balance = d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)
    FROM
      dbo.Deposit    AS d,
      dbo.Withdrawal AS w
    WHERE
      d.DepositDate  >= start_of_this_month AND d.DepositDate  < start_of_next_month
      AND
      w.WithdrawDate >= start_of_this_month AND w.WithdrawDate < start_of_next_month
  )
SELECT
  TotalAmount,
  WithdrawAmount,
  Balance,
  Status = CASE WHEN Balance > 0 THEN 'StillAvailableYAY' ELSE 'ZeroBalanceOops' END
FROM
  balances
;

Of course, if you do not really need to return the balance – only to compare it to 0, then nesting is not needed and you can put the d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC) expression directly into the CASE, replacing Balance.


Should I divide my table into simpler ones or should I leave it the way it is?

No, breaking the table up that way is generally not a good idea unless you are talking about table partitioning in which case you have just one table as far as the overall design is concerned and the partitioning is a performance/administration difference.

With a separate distinct table per year you have the following problems, amongst others:

To query based on more than on year you have to UNION the tables back together (or take some other extra steps) which will make the resulting queries less efficient and less easy to maintain.
If extra years are needed later you will have to add new tables and update any views/procedures/queries/other that refer to multiple years as in the above point.
You will need to enforce (by adding database constraints if supported by the DB you are using or in your business logic) that the right rows go in the right tables which adds complexity.


Just use the function hstore().

There are several overlaoded versions of that functions. One takes a single text array with keys and values. Another one takes two text arrays, one with keys, the other one with values. Either works for you. Demo:

SELECT hstore(array_agg(ARRAY[key, value::text]))     AS option1
     , hstore(array_agg(key), array_agg(value::text)) AS option2
FROM  (VALUES ('foo', 1), ('bar', 2)) AS t(key, value);

option1 requires Postgres 9.5 or later, where array_agg() for multidimensional arrays was introduced. For older versions see:

Is there something like a zip() function in PostgreSQL that combines two arrays?

If value is not type text, you may need the cast I added: value::text

The additional module hstore has to be installed in the database, of course.

CREATE EXTENSION IF NOT EXISTS hstore;

Related:

Assignment of a column with dynamic column name
Is it recommended to install extensions into pg_catalog schema?