intel p3600 SSD no increase in read speed - postgres rdbms vs hdd

You should store your value for FIELD1 in a TABLE, instead of in a bunch of variables.  Make a three level index, a COVERED index specifically.

your IN clause is preposterous. I mean.. Learn how to Normalize.

THIS:

WHERE Field1 IN ('aaaaaaa','bbbbbbb','ccccccc',...,'nnnnnnn' )

is actually SLOWER than this:

SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'aaaaaaa'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'bbbbbbb'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'ccccccc'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'nnnnnnn'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
order by Field1,Date


Find Candidate Keys by looking to Functional Dependencies

The relation with the above functional dependencies has two different candidate keys: B and G.

You can verify this if you compute the closure of both to see if it contains all the attributes:

B+ = B
B+ = BCEF (by using the dependency B → CEF)
B+ = BCEFDG (by using B → DG)
B+ = BCEFDGA (by using G → AD), all the attributes, B is a candidate key

While for G:

G+ = G
G+ = GAD (by using G → AD)
G+ = GADB (by using GA → B)
G+ = GADBCEF (by using B → CEF), all the attributes, G is a candidate key

No other set of attributes is a candidate key, since the only other attribute on the left side is A, but GA is determined by G, so it can be eliminated from that dependency.

Actually a canonical cover of the original set of dependencies is:

{ B → C
  B → E
  B → F
  B → G
  G → A
  G → D
  G → B }


How do I terminate a running SELECT query without restarting MySQL?

Based on the comments trail on this question, I have some bad news and good news.
BAD NEWS
I believe dastnumbers_0 is an InnoDB table. You can verify what with
SELECT engine FROM information_schema.tables
WHERE table_name = 'dastnumbers_0';

You summoned all the rows with your SELECT query because of there being no WHERE clause. An attempt to sort 2 billion rows must take place.
Even worse, InnoDB attempts to keep a snapshot of the entire table in the rollback segments and undo tablespace within ibdata1 ( See picture of this in http://www.scribd.com/doc/31337494/XtraDB-InnoDB-internals-in-drawing ). This is manifested as an increased size of ibdata1. When you killed the query, InnoDB attempts clear out whatever was collected in a snapshot.
You will have to just let go ahead and clear. If you kill the mysqld process prematurely, it will no matter. On the next mysql startup, it will pickup where it left off.
I was in commentary discussion with @a_horse_with_no_name about this. In theory, snapshots should not be happening on a straightforward SELECT. The only way this could happen with a SELECT query is if the DB Connection's session has autocommit disabled or a BEGIN TRANSACTION ... COMMIT/ROLLBACK block was issued. Please let us know if this is the case.
GOOD NEWS
You could refactor your query. First look at the query:
select * from dastnumbers_0 order by addeddate desc limit 25;

It will get everything, sort it in descending order, then that the first 25 rows. That's an expensive operation. Make sure you have an index on addeddate . If you do not have an index on addeddate or you are not sure, you may need to create it with this:
ALTER TABLE dastnumbers_0 ADD INDEX (addeddate);

This will take a long time since you have 2 billion rows, but you only have to do this once.
Then, make the query get only the 25 keys for the table. Suppose dastnumbers_0 has id as its primary key. You could now have the query do something like this:
SELECT B.* FROM
(
    SELECT id FROM dastnumbers_0
    ORDER BY addeddate DESC LIMIT 25
) A LEFT JOIN dastnumbers_0 B USING (id);

This technique can work because I answered a question like this in StackOverflow and got a 200-point bounty out of 11 submitted answers : https://stackoverflow.com/a/6023217/491757
Give it a Try !!!


HeidiSQL solution for SQL Server error 18488?

You can change the password from sqlcmd using -z or -Z:

  -z new_password Change password:

sqlcmd -U someuser -P s0mep@ssword -z a_new_p@a$$w0rd

  -Z new_password Change password and exit:

sqlcmd -U someuser -P s0mep@ssword -Z a_new_p@a$$w0rd


How to get user name to appear in Alerts

Alerts generated by the SQL Server Agent don't include this information, and you can't add it, unfortunately.

One option would be to set up an Extended Events session to capture the error_reported event and filter by the specific error number. This will allow you to capture additional info such as program name, username, client machine etc.

Log this to a file and use an Agent job to periodically query the file and alert if entries are found.

Links:

Login Failures in Extended Events: https://www.mssqltips.com/sqlservertip/3214/use-extended-events-to-get-more-information-about-failed-sql-server-login-attempts/

Extended Events Quickstart Guide: https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-2017


Use sp_Blitz to gather information on all servers in a centralized server/database

Brent Ozar folks already have got you 50% - Inserting sp_Blitz® Output Into a Table

My steps would be :

Log the output to a local dbastats or some dba database.
Have a list of all sql servers in your central server.
use sqlcmd and opendatasource to query remote servers and dump all the info into centralized server .. make sure you include something like collection_datetime and server_name. You can use powershell (Write-DbaDataTable) as well.
schedule above logic using sqlagent job as per your needs.
Make sure you have proper purge policy defined locally per server. I would add custom column like notified = 0 (as default) and update to 1 when you dump the data into central database. The collection_datetime and server_name will help you find out what has changed or do aggregation per server instance.


postgreSQL how can I make this work with an aggregation alias?

A "WITH query" (CTE) may be useful for you (see: https://www.postgresql.org/docs/current/static/queries-with.html - read at least the first paragraph ...)

Here's an example, very close to your situation.  Notice: date_ is the equivalent of time::date in your code:

with stats as (
  select
    date_
  , sum((status='404 NOT FOUND')::int) as not_found
  , sum((status='200 OK' OR status='404 NOT FOUND')::int) as total
  , sum( (status='404 NOT FOUND')::int) /
         sum((status='200 OK' OR status='404 NOT FOUND')::int)::float
    as percent_error
  from log
  group by date_
)
select not_found, total, percent_error
from stats
where percent_error > 0.01 ;


What steps are necessary to safely restart a machine hosting Microsoft SQL Server?

You don't have to be fancy/worried or scared when you are restarting sql server.

Just make sure that you dont have any long running transactions. Best is to restart sql server using console or shutdown command during a low/minimum activity period also called maintenance window to minimize impact on your business.

If you have any DR setup and you dont want to be down, then best is to failover and then restart the passive or secondary node.

Clean Shutdown SQL Server occurs in below scenarios :

Stop sql server using Services console.
Shutting down your server
running SHUTDOWN command in SSMS

In above all situations, sql server cleanly shutsdown all its databases and then terminates the service which involves commiting or rolling back all the transactions, writing all dirty pages to disk and then writing an entry into transaction log.

Improper shutdown of sql server :

shutdown with nowait
pulling power cable from your server (if you have access).
killing sqlserver.exe from task manager
Dirve failure on which sql server binaries, exe, system databases reside or windows system drive failure .. usually C:\ drive.
overheating of the server causing it to shutdown (should rarely happen !!)

SQL Server will always try to do a clean shutdown ...unless you do something improper as stated above.

Some really good reading links on what happens behind the scenes during recovery phase :

Understanding Logging and Recovery in SQL Server
Understanding How Restore and Recovery of Backups Work in SQL Server
Simple overview of the SQL Server Recovery Process


Return Distinct Records by Category Based Multiple Column Values

This query gives the required output:

WITH data AS(
    SELECT [ID], [MODEL], [MODELSTATUS], [CONTROL]
        , r = ROW_NUMBER() OVER(PARTITION BY [MODEL] ORDER BY
            CASE WHEN MODELSTATUS = 'TRENDING' THEN 1 ELSE 0 END DESC
            , CASE WHEN MODELSTATUS IN ('PENDING', 'BAD') THEN 1 ELSE 0 END DESC
            , CASE WHEN [CONTROL] = 'XN' THEN 1 ELSE 0 END DESC
            , [ID] DESC
        )
    FROM @Table1
)
SELECT [ID], [MODEL], [MODELSTATUS], [CONTROL]
FROM data
WHERE r = 1;

See this SQL Fiddle.

I assume that this is only a sample table with easy to understand char values and real data is using int instead of varchar for status and control.

In term of performance and IO in real life, if this type of table gets bigger, it is wise to improve the design and replace varchar by tinyint ids for each Control and Status.

Output

ID  MODEL   MODELSTATUS CONTROL
24  100     TRENDING    BF
33  101     GOOD        XN
46  102     BAD         BF
50  103     BAD         XN
89  104     TRENDING    BF
93  105     TRENDING    XN


Is it safe to use the Windows "Compress directory to save space" feature on the directory that contains my MySQL general log file?

The Windows feature to compress a directory is transparent to all applications.  However, it does slow down I/O operations some.
The General Log should not be left on forever; it is a big disk hog.  Instead, turn it on only long enough to get the necessary debugging info, then turn it off and toss the log file.
Or, you could log to TABLE and not FILE; I think the table has a limit.  (Check the settings.)


The process cannot access the file because it is being used by another process 2

As you said the error

  The operating system returned the error '32(The process cannot access
  the file because it is being used by another process.)' while
  attempting 'RestoreContainer::ValidateTargetForCreation'

The reason for this is that the name of the database files of the databases you are trying to restore already exists.

This could be for two reasons, either you have used the same create scripts for the same database on all instances or you have already done a restore of the database you are trying to restore now to that instance.

To solve this issue do the following:

  In the restore page in SSMS, press the "options" tab.
  In the "Restore as" column change the name of the datafile(s) and log file to something unique.

Then hit the restore button and that should be it.

for your ref Here


What should I use as a primary key/index for a table with no unique columns that are mostly written to?

Q: If were to to have every column indexed, would that be a big
  performance hit?

A: Uh - yes :)  It would :)

Don't do it :)

  Q: I'd like to be able to search the large quantities of data in as
  short a period of time as possible

A: And that's precisely what you want to build your indexes on.

What are you likely to be "searching"?

I'm guessing date/time (a certain range of minutes/hours/days/etc) and some "type".  For example, users might be "eating", "sleeping" or "studying".

You definitely want at least one index, and you definitely want to "strategically determine" what should be your "clustered index".  Clustered indexes are a Sql Server thing.

You can learn more here:

https://stackoverflow.com/questions/9085390/best-practice-for-storing-millions-of-rows-with-tsql-sql-server-2008
http://msdn.microsoft.com/en-us/library/aa933131%28v=sql.80%29.aspx


Drive Configuration In Virtual Environment

I think it comes down to the IO subsystem your vendor is using. If it is still spinning disks, then for performance purposes, what you suggested is best practice. However nowadays, with all-flash storage arrays (SSDs), there are no moving parts, thus the IO patterns are not much of a concern anymore.

Bottom line, your vendor should be well-versed with what best practice is for SQL Server install. I suggest asking for supporting documentation to be safe. For example, VMWare has a SQL Server on VMWare Best Practices Guide.pdf which pretty much spells out the above.


why stored procedure performance is very high compared to plain select query

Could you check your execution plans for the procedure/query? The execution plan of a procedure is also compiled and stored in the library section, while most of the times, the one for a query is computed ad-hoc (not always though). But still, the generation of a execution plan, surely doesn't take that long. So maybe, because of expired statistics (for instance), the plan of the query is not optimal, while the one stored in the procedure it is.

In other words, a good starting point are the plans. If you wish, you could post them here. Then you could check the resource profiles under each operation it is executed, to make sure that both of them are able to use the same amount of resources.


Automatic aging-out (deletion) of old records in Postgres

There is no feature built in to delete rows automatically on a time-based regime (that I would know of).
You could run a daily (you decide) cron-job to schedule simple DELETE commands or use pgAgent for the purpose.
Or you could use partitioning with weekly partitions. That makes deleting very cheap: just keep the latest two weeks and drop older partitions.
Before Postgres 10, partitioning was always based on inheritance. The new declarative partitioning has a lot of advantages and is improved further with every release. Inheritance still provides one key advantage, though. The manual:

Some operations require a stronger lock when using declarative
partitioning than when using table inheritance. For example, adding or
removing a partition to or from a partitioned table requires taking an
ACCESS EXCLUSIVE lock on the parent table, whereas a SHARE UPDATE EXCLUSIVE lock is enough in the case of regular inheritance.

Meaning, adding or removing a partition does not interfere with readers and writers of other partitions with inheritance, while everything is blocked with declarative partitioning (when going through the parent table). It's a very fast operation, though.
With inheritance, create partitions that inherit from a "master" table, lets call it log. Create a RULE or a TRIGGER on the master table that redirects INSERTs to the partition of the current week based on system time.
Either way, you can always log to the master table log. Create partitions ahead of time. Make that several weeks ahead to be sure and run a weekly cron job that adds future child tables ...
There are code examples in the manual for inheritance and declarative partitioning..
Related answer with a plpgsql function creating tables for inheritance automatically:

Drop certain tables in a function managing partitions

The related solution recreates a RULE to redirect INSERTs. A trigger function could write to the current partition dynamically ...


MySQL DELETE statement doesn't use index although the same SELECT query does

By deleting the data first, you are essentially making the data inaccessible.  Don't you want to avoid this "downtime"?

Consider loading the replacement data into a temp table, then doing IODKU to update the main data:

INSERT INTO main (...)
        ON DUPLICATE KEY UPDATE
             col1 = VALUES(col1),
             ...
    SELECT ... FROM temp;

If there could be rows to remove; IODKU won't provide that feature.  However, you could precede it with something like

ALTER TABLE temp ADD INDEX (...);  -- to speed up the LEFT JOIN below

DELETE FROM main
       USING main
        LEFT JOIN temp ON ...
       WHERE temp... IS NULL;


How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

You should use the option -b in sqlcmd.

-b
Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0

http://msdn.microsoft.com/en-us/library/ms162773.aspx


using count from subquery results to order parent query results

Your ORDER BY is this:
order by (
           select count(file_id)....
       ) DESC

The count will return a (constant) integer, such as 300. Which means you are effectively saying ORDER BY 300 DESC (where 300 is the number of file_id values for the specified user_id). This isn't using a column to sort, but a constant.
Sorting by a constant does nothing. You presumably expect your results to be ordered based on a value in a column (or a calculation derived from a column), but that's not what the query does


executing shell (bash) commands from within the mongo client

There are a number of system command equivalents available:

ls()
mkdir()
hostname()
pwd()
sleep() (not a system command, but similar functionality)

It should be noted that these are all executed on the client (local) machine, not the server.  There is no general construct for executing arbitrary system commands that I know of in the shell.  To be honest, I suspect that is intentional because it would be a security nightmare even on the client side.

Allowing arbitrary commands to be executed from your product (database or not) is a massive potential security hole because instead of being vulnerable to just your own issues, you are now liable for any security bugs in any other system utility people choose to install.

Hence the potential for the nightmare - I can't say for sure, but I'll bet the MySQL devs would love to get rid of that command. Here's an example of someone patching the client to get rid of it:

MariaDB/MySQL Security Essentials – Disable System Commands


Why can MySQL handle multiple updates concurrently and PostgreSQL can't?

Rolando already more or less described the reason why the deadlocks happen.  Let me add that PostgreSQL has to take locks on the rows to be updated, in order to maintain consistency.  (now() is fixed to the beginning of the transaction, meaning that two concurrent updates will want to update a given row to a different timestamp, which has to be resolved somehow - taking locks on the rows is the PostgreSQL solution.)

The locks are assigned to the transaction on a first come - first served basis.  Deadlocks occur when two transactions try to obtain locks on the same rows in different order.  The way to do this is - now not surprisingly - requesting the locks in a predictable order.  This is done by

SELECT id -- could be any column, even a constant
FROM position
WHERE latitude between 234.12 and 235.00
ORDER BY id
FOR UPDATE;

Here the crucial part is ORDER BY id and FOR UPDATE.  The latter takes the necessary locks of the same type the subsequent UPDATE needs.

The SELECT ... FOR UPDATE statement might looks superfluous otherwise (especially when you don't need the selected data), and it also means disk writes even before actually changing the data.  This has to be considered when using it.


How to Drop Database in Single_User Mode

If you are going to drop a database, you have to be the only connection to that database. If any other connections are there, you can't drop it. From the error message (that error means your database is in Single_User mode but there is already a connection so you can't connect) my assumption here is that you tried to set it to Single_User mode and then attempted to do the drop but either you grabbed a connection that you didn't know about, or some other process has. The fact that restarting SSMS worked for you tells me it was probably you grabbing that connection. So here is how you can fix that.

Logically you have to put the database back into multi_user mode so you can then put it into single_user mode again (but this time you'll be in control of that single connection allowed and drop the database before something else connects) and then your database will be gone.

In code here is how you need to do this (but first close your query windows that are connected to that database. Restart SSMS and make sure you don't select this database in the object browser):

-- Then attempt to take your database to multi_user mode, do this from master
USE MASTER
GO

ALTER DATABASE myDatabaseName
SET multi_user WITH ROLLBACK IMMEDIATE
GO

-- Now put it into single_user mode and drop it. Use Rollback Immediate to disconnect any sessions and rollback their transactions. Safe since you are about to drop the DB.
ALTER DATABASE myDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE myDatabaseName
GO


Difference between a unique clustered index and a clustered primary key?

why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

To answer this question: The primary key on the table does not have to be the Clustered Index Key. A Clustered Index is the place where SQL Server stores the data of the entire table. The Index itself has what are called "key values." Key Values (1 to many) define how the index is ordered, along with statistics and other useful things.

In fact, often it's useful to have a Primary Key that is not the Clustered Index Key. For example, some applications create their own keys or GUIDs in order to relate different tables through foreign keys. Using a GUID as a Clustered Index Key is a bad idea though, since GUIDs don't always have an order, unless you create them sequentially. To solve this, you could create a Clustered Index on an identity column, and place the Primary Key on the GUID column.

Your question has many more questions. I think we need to break this down into many questions, since you're asking about a lot of index concepts.


The table still exists in the InnoDB dictionary. It's similar to how MySQL left temporary tables after a failed ALTER.

I found that the easiest way to remove an entry from the dictionary tables is to drop whole database. Obviously you want to keep the data, so moving it to a temporary database will work. Anyways, the steps are:

Create an empty temporary database. Let it be tmp1234.
Move all tables from the original database to tmp1234.
Drop the original database (it’s empty by now, all tables are in tmp1234).
Create the original database again.
Move all tables from the temporary database to the original one.
Drop the empty temporary database.

Check out https://twindb.com/resolving-error-1050-42s01-at-line-1-table-already-exists/ for some background.


Equivalent to BIT_OR function in SQLite?

SQLite supports using the pipe symbol as a bitwise-OR operator, as in:
SELECT col1 | col2
FROM sometable;

So, if col1 contains 1 and col2 contains 2, the result would be 3.
Here's a SQLFiddle.uk demo showing how that works under SQLite 3.27.


Could I use the SQL statement, SELECT * from sysobjects, with C# MySQL Connector - .NET?

The answer is SELECT * FROM INFORMATION_SCHEMA;


Add values from two existing column into new columns for a total

Just change your table create statement to use a computed column.

CREATE TABLE [dbo].[Incomes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientIncome] [float] NOT NULL,
    [PartnerIncome] [float] NULL,
    [HouseholdIncome] AS COALESCE(ClientIncome, 0.) + COALESCE(PartnerIncome, 0.)
)

If your table is already created, you can add a computed column:

ALTER TABLE dbo.Incomes ADD HouseholdIncome
    AS COALESCE(ClientIncome, 0.) + COALESCE(PartnerIncome, 0.);


Total Fragments 99% and I cant get it to stay down

I have tried to rebuild the indexes as most of them are sitting at 99%. after the re-build they go to 0. but with in a few minutes they are back at 99%.

I guess I know the answer here and have seen many times. Can you please check if auto shrink is on for the databases. You can use below query

select name,is_auto_shrink_on from sys.databases where name='db_name'

You must also check if there is any job running daily which is shrinking data file or may be shrinking whole database using DBCC SHRINKDATABASE command or may be using DBCC SHRINKFILE command.

Of course this could only be complete answer if you confirm whether any such activity is going on in database.


Get the deleted value using while looping through the columns

I'm short on time so I wasn't able review your code, but I'm basing this answer off your question: How to get the deleted values/capturing changes?

Just yesterday I was reading from CH. 8 of Itzik Ben-Gan's "Microsoft SQL Server 2012 T-SQL Fundamentals" and he had a good section on creating audit tables with the use of the OUTPUT clause. To me this was eye opening and a really cool and simple way of tracking changes such as UPDATES or DELETES.

Assume we have an Orders table with 4 columns: orderid, custid, empid, and orderdate. If we wanted to create an audit table that tracks delete statements the code would be:

DELETE FROM dbo.Orders
OUTPUT
    deleted.orderid,
    deleted.orderdate,
    deleted.empid,
    deleted.custid
Into dbo.OrdersAudit
WHERE orderid = '5';

This code will run your delete statement, and store the all the values into your audit table. The OUTPUT clause can also be used to track your inserts and updates. From what I can tell, the downside is you have to incorporate the OUTPUT clause into all of your procedures. But I wasn't even aware of this clause, and it's super simple to learn the syntax. This probably wasn't what you were looking for, but I was really excited when I read about this clause, and looks like an easy way to track data changes for audits.


Autoincrement primary key in CREATE TABLE ... AS SELECT

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`;

As long as you don't have an 'id' column in your SELECT, this appears to do exactly what you want.  The columns in the select will be appended to the right of the columns you declare.

http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html (also in previous versions).


WHEN MATCHED clause fails if there's a NULL in the ON columns

The predicate for MATCH doesn't fail, it behaves exactly like it is supposed to. NULL is not a value, NULL is a state that indicates there is no value.
Therefore, any predicate with any comparison to NULL will resolve to 'UNKNOWN' and not to 'TRUE', causing the row to be considered as a non-match.
To deal with that, you will need to decide on the logic and how you would like to evaluate the rows in case you have NULLs.
For example, if It is only for last name, and you want to consider it a match, you may solve it by extending the predicate to handle the NULL:

MERGE PEPS AS peps
USING (Lots of Cols..
       FROM PEPS_temp)
AS temp (Lots of Cols..)
ON peps.[Name] = temp.[Name] AND
   (peps.[LastName] = temp.[LastName] OR peps.LastName IS NULL) AND
   peps.[TypeEntity] = temp.[TypeEntity] AND
   peps.[Source] = temp.[Source]
WHEN MATCHED THEN
UPDATE SET peps.[Category] = temp.[Category]
WHEN NOT MATCHED THEN
INSERT (Lots of Cols..) VALUES (temp.[Col], temp.[Col], temp[Col]....)


Postgres partition tables 1 million rows

Your database is small and doesn't require partitioning.

A quick guesstimate gives storage requirements: two 2,000 byte columns times 1M rows is 4GB; times 3-4M rows is 12-16GB. A proper calculation would include a fudge factor for the other columns, indices, and other overheads, but it's still obviously an amount that fits in RAM on anything but the most crusty of servers.

So to answer your questions:

You can partition a 1M row table, but it's not worth the effort with PostgreSQL. (PostgreSQL is not MySQL.)
This is not a question!
PostgreSQL's "about" page states there is no limit on the number of rows, but a table cannot exceed 32TB.


In stored procedure %Rowtype is not working while deploying

Db2 for LUW 9.1 did not have the Oracle compatibility feature, which was added since v9.7, so you cannot use the PL/SQL dialect. Moreover, the CREATE OR REPLACE syntax or row types were not supported either.

If you wish to use these features, upgrade to a supported version of Db2.


Can't connect to a remote server using phpMyAdmin

Check your PHP version (and in particular, your PHP MySQL connector version), it may not have support of the newer authentication plugin. If for some reason you cannot upgrade you client PHP installation, you can change the user you are using to connect on the server to revert back to the more insecure native authentication so it works with older connectors, but try to upgrade the client first.

More info:

http://blog.ulf-wendel.de/2012/mysql-5-6-sha256-secure-password-support-for-php-mysqlnd/
https://dev.mysql.com/doc/refman/5.6/en/sha256-pluggable-authentication.html
https://stackoverflow.com/questions/30710146/php-mysqlnd-sha256-password-plugin-access-denied-user-works-from-cli-mysql


How to select the set of last non-NULL values per column over a group?

Using the concatenation technique from The Last non NULL Puzzle by Itzik Ben Gan would look like this with your sample table and column data types.

select T.cat,
       cast(substring(
                     max(cast(T.t as binary(2)) + cast(T.val1 as binary(4))),
                     3,
                     4
                     ) as int),
       cast(substring(
                     max(cast(T.t as binary(2)) + cast(T.val2 as binary(1))),
                     3,
                     1
                     ) as char(1))
from #tab as T
group by T.cat;

Another way to write this query that divides the steps into CTE's to perhaps better show what is going on. It gives the exact same execution plan as the query above.

with C1 as
(
  -- Concatenate the ordering column with the value column
  select T.cat,
        cast(T.t as binary(2)) + cast(T.val1 as binary(4)) as val1,
        cast(T.t as binary(2)) + cast(T.val2 as binary(1)) as val2
  from #tab as T
),
C2 as
(
  -- Get the max concatenated value per group
  select C1.cat,
         max(C1.val1) as val1,
         max(C1.val2) as val2
  from C1
  group by C1.cat
)
-- Extract the value from the concatenated column
select C2.cat,
       cast(substring(C2.val1, 3, 4) as int) as val1,
       cast(substring(C2.val2, 3, 1) as char(1)) as val2
from C2;

This solution uses the fact that concatenating a null value with something results in a null value. SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)


How to query an Extended Events target file on a SQL Azure Managed Instance by using T-SQL?

I don't know if this is the right solution, but here's is the workaround that worked for me, in case someone is facing the same issue.

I created a small Azure SQL Database (S1).
I created a master key for this database.
I created a scoped credential pointing to the Azub Blob where the XEL files from the Managed Instance reside in (using the same Shared Signature)

I was able to query by using
SELECT event_data FROM sys.fn_xe_file_target_read_file (N'https://whatever.blob.core.windows.net/xevents/MyTrace_132410674100570000.xel',NULL, NULL, NULL);

It seems like Managed Instance does not support the use of this function (or I wasn't able to find the way to do it).
Hope this helps someone.


How to filter for consecuting timeline events in pure SQL?

You don't need cursor for that.

If your id are automatically incrementing, you also don't need ordering by the timestamp, the id is enough. If not, then use timestamps. Note, timestamps have some possibility of funny effects (timezones, daylight saving times, simultaneous effects inside a single second and many similar "surprises").

This query filters those camera1 events, which are not followed by a camera2 event - either because the car is in the lot, or because they are tricking. The query handles also "funny" cases, for example, if a car would be detected by the camera on the entrance multiple times.

It does not handle the case somebody leaves the lot without entering it, but understanding the logic, it is easy to improve.

WITH Raw AS (
  SELECT RANK() OVER (PARTITION BY Plate ORDER BY Id) AS EventNo,
  Id,
  Camera,
  Plate,
  Timestamp
  FROM CameraLog
)
SELECT Prev.*
FROM CameraLog AS Prev
LEFT JOIN CameraLog AS Next
  ON Prev.Plate = Next.Plate AND Prev.EventNo + 1 = Next.EventNo
WHERE Prev.Camera = 1 AND Next.Camera <> 1;

The logic is that first we extend the table with the "event history" order of a single car by the RANK() windowing function: the Raw subquery essentially extends the CameraLog table with a new, EventNo column. This column is essentially an auto-incrementing id, but it is ordered for all the plates induvidually.

In the second step, in the main query, we join this table with itself together. Essentially, we try to find for all event the next one.

The result is that we have such a table:

columns of camera events | columns of the consecuting camera event

In this column structure, it is already easy to filter with a WHERE (last line).

Note, this query will be highly suboptimal, if the size of the tables is in the order of the available RAM in your server. If you need to handle large data tables quickly, you will need more advanced techniques.


Trouble removing filestream file / filegroup

Indeed, that was the answer.  I set the db recovery model to "Simple", waited a minute for the filestream data to clear up, and then I could remove the filestream file and filegroup.


Select images where ALL images are greater than pixel width

SELECT id
FROM table
GROUP BY id
HAVING MIN(CDN_W) > 800
   AND MIN(LG_W) > 800
   AND MIN(LGR_W) > 800

?


Generic SQL Job Scheduler for multiple RDBMS's?

What you are looking for is an Enterprise Job Scheduler.  Computer Associates has a good one called AutoSys.  There's also JAMS, OpCon, Tivoli as well as others.  There's a decent list available on wikipedia.  I've only had experience with AutoSys, and it's expensive, but VERY powerful, but it takes some work to get setup.  I don't know the cost or complexity of the other options.

These sort of schedulers allow you to schedule commands to run on just about any OS or application that the scheduler supports without using the native schedulers (so you don't need SQL Agent or anything).


SQL Server R2 Standard Edition MAXDOP setting

Standard edition will not generate a parallel plan that uses more than 4 processors i.e. MAXDOP 4. Setting MAXDOP to a value higher than this will not alter the limitation imposed by the edition. You get no warning or error when setting the higher value, it will be accepted.


Alter a view that refers to a non-readable secondary database

Can you create a linked server on each AG node that references the AG listener?
You can then alter your view to query DB1 via the linked server using 4-part naming, instead of querying DB1 using 3-part naming. Then you shouldn't encounter this error as the linked server will redirect to the AG primary.


MySQL - LOCK IN SHARE MODE for EXISTS query

There are multiple type of locks in Mysql.
LOCK IN SHARE MODE in a shared mode lock on any rows that are read. So you need to pay attention to your query, how many rows are read (look also at examined column when doing explain).

The isolation level will also affect behaviors of certain locks.
I strongly advise to let Mysql take care of the locks.
When an update needs to occur it is usually the first come first serve.
make sure your Exist query is fast (best is to query on the PK).

If exists (select 1 from tbl1 WHERE ) Then
 perform your update
Else
Select "record does not exists"
End if;


A login is a member of db_datareader and public roles only, but it is not read-only

I was looking through all items in Security nodes in SSMS and it seems that somehow the properties of the public database role have been changed. They looked like this:

I don't really know how they should look like normally, but clearing the Delete, Insert, Update checkboxes seems to do the trick.

The immediate problem is solved now and I'll have to read more about the public role and what settings it should have.


How to get dates from a table while the date range is from the date belonging to another table?

You can use the BETWEEN conditional operator to search for anything in a range. The BETWEEN operater is inclusive so will find records for the start and end dates too.

You will be able to query the attendance table directly:

SELECT * FROM attendance WHERE attendancedate BETWEEN '2019-04-03' AND '2019-06-03'

This question has some examples:
https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql

I hope that helps!


Using ddl triggers with in-memory tables

No, databases with In-Memory OLTP enabled do not support DDL triggers.

  Both database-level and server-level DDL triggers are not supported with In-Memory OLTP tables and natively compiled modules.

You cannot create DDL triggers in a database where In-Memory OLTP is enabled since the code path required to run the trigger would interfere with the In-Memory functionality.  The workaround is to put all your In-Memory OLTP tables in a single database without a DDL trigger, and the rest of your tables into a separate database without memory-optimized tables, and enable the DDL Trigger there.


I gave 3 large Excel files need to be loaded in Single SQL table

Here is an example of using Powershell to import Excel data to SQL Server.  Referencing the example in Using Powershell to Import Excel file into SQL Server:

The script uses the Provider=Microsoft.ACE.OLEDB.12.0 which I downloaded from
Microsoft Access Database Engine 2010 Redistributable.  I had to download the 32-bit version on my computer due to other Office products being installed.  Due to the fact that I had to use the 32-bit version, I also had to run Powershell under the 32-bit version (which on my computer was located at C:\Windows\SysWOW64\WindowsPowerShell\v1.0).  I created the sample table ExcelData_fill (which is referenced in the script below) before running the Powershell script.  You'd need to make changes to the top of the script to point to your Excel files and also specify the correct SQL Server instance information.  You should be able to import your data by modifying the script to point to each Excel file.

#Change these settings as needed
$filepath = 'C:\temp\ps.xlsx'
#Comment/Uncomment connection string based on version
#Connection String for Excel 2007:
$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
$qry = ‘select name from [sheet1$]’
$sqlserver = 'localhost'
$dbname = 'test'
#Create a table in destination database with the with referenced columns and table name.
$tblname = 'ExcelData_fill'

#######################
function Get-ExcelData
{

    param($connString, $qry='select * from [sheet1$]')

    $conn = new-object System.Data.OleDb.OleDbConnection($connString)
    $conn.open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn)
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
    $dt = new-object System.Data.dataTable
    [void]$da.fill($dt)
    $conn.close()
    $dt

} #Get-ExcelData

#######################
function Write-DataTableToDatabase
{
    param($dt,$destServer,$destDb,$destTbl)

    $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = "$destTbl"
    $bulkCopy.WriteToServer($dt)

}# Write-DataTableToDatabase

#######################
$dt = Get-ExcelData $connString $qry
Write-DataTableToDatabase $dt $sqlserver $dbname $tblname


How to get an array as output parameter from stored procedure?

Array is not supported in stored procedure, you can use temporary table.

Try this code:

CREATE PROCEDURE `getAttritionData`(IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT)
DETERMINISTIC
BEGIN
    DECLARE i INTEGER DEFAULT 0;
    DECLARE CNT_EMP INTEGER DEFAULT 0;
    DECLARE CNT_EMP_SUS INTEGER DEFAULT 0;
    DECLARE TOT_EMP INTEGER DEFAULT 0;
    CREATE TEMPORARY TABLE T1(CDATE DATE,TOTAL_EMP INTEGER, TOTAL_SUSPENDED INTEGER);
    WHILE from_date<=to_date DO
        SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP
        FROM employee
        WHERE sup_id=supid AND doj<from_date AND (term_date>from_date OR term_date='0000-00-00') AND app_role=1;
        SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP_SUS
        FROM employee
        WHERE sup_id=supid AND term_date=from_date AND app_role=1;
        INSERT INTO T1(CDATE,TOTAL_EMP,TOTAL_SUSPENDED) VALUES (from_date,CNT_EMP,CNT_EMP_SUS);
        SET from_date=date_add(from_date, INTERVAL 1 DAY);
    END WHILE;
    Select * from T1;
    SELECT *,count(CDATE),sum(TOTAL_EMP),SUM(TOTAL_SUSPENDED),((SUM(TOTAL_EMP)/count(CDATE))*365/12) AVG_HEAD_COUNT, (SUM(TOTAL_SUSPENDED)/((SUM(TOTAL_EMP)/count(CDATE))*365/12)) atr_rate FROM T1 group by MONTH(CDATE);

END

Hope your issue will be fixed.


SQL Server Rollback being performed - how to stop it?

@MaxVernon already gave  all the info you need. Just some addition from my side. The bigger the transaction the harder the (pre)calculation for the SQL Server. That's why your remaining time in seconds may increase while the process runs. After running a query the SQL Server can't grant more memory to the process. For big calculations your tempdb will most probably be used. And yes this will lead to a disk intensive call.  That's why it's nearly always best to keep the transactions small.

Also please note that restarting SQL Server is in most cases a bad idea, since it has multiple negative side effects. It will for example rebuild the TEMP DB and also clear statistics...
I


Similar, but not exactly.

They are both related to the MVCC architecture, and related to garbage collection, broadly speaking.

Vacuum in PostgreSQL removes obsolete or deleted record versions, and marks the space as available for re-use. This is accomplished by running the VACUUM command periodically, or by enabling the optional autovacuum daemon.

Purge in MySQL removes obsolete InnoDB record versions from the rollback segment, so that space becomes available for re-use. Purging happens automatically every 10 seconds in the InnoDB server master thread, or in a dedicated purge thread. In fact, you can't disable purging unless you run MySQL in a forced-recovery mode.

The difference is subtle and related to the respective implementation of MVCC. Probably the answer you're looking for is simply, yes.


A scalar UDF with table access acts like a lookup inside a RBAR curso. That is, it executes once per row. And it's black box to the optimier
TVF is a macro that expands like a view and is consider part of the outer query

Scalar UDF have uses, but not in this case.

The performance difference can be huge: I've achived x100,000+ improvements by removing idiotic uses of UDFs


So, I want this server (srv1) to be as a shrad and config server at
  the same time. The second server (srv2) is going to be a second shard.
  And the third one (srv3) is going to be a replica server.

  Is that correct? Or it's necessary to create an additional server,
  that will be the config server?

Yes, you can, As per MongoDB documentation here Changed in version 3.4. Replica Set Config Servers.

Starting in MongoDB 3.2, config servers for sharded clusters can be deployed as a replica set (CSRS) instead of three mirrored config servers (SCCC). Using a replica set for the config servers improves consistency across the config servers, since MongoDB can take advantage of the standard replica set read and write protocols for the config data. In addition, using a replica set for config servers allows a sharded cluster to have more than 3 config servers since a replica set can have up to 50 members. To deploy config servers as a replica set, the config servers must run the WiredTiger storage engine.

In version 3.4, MongoDB removes support for SCCC config servers.

The following restrictions apply to a replica set configuration when used for config servers:

Must have zero arbiters.
Must have no delayed
members.
Must build indexes (i.e. no member should have buildIndexes setting
set to false).

Examples

The following command adds a replica set as a shard:

use admin
db.runCommand( { addShard: "repl0/mongodb3.example.net:27327"} )

  WARNING Do not use localhost for the hostname unless your config server is also running on localhost.


I'll expand my comment into a proper answer, as requested.

Since, in this situation, the desired result is more of an automatic redirection to the new server without needing to change connection strings then the following options are available:

DNS Alias (CNAME)
Hardware Load Balancer
SQL Alias
Local Host Record

Each of these has its' own merits, however since this change is environment wide you'll want to have the least amount of moving parts and administrative overhead.

A Local Host Record (editing the HOSTS file) would have to be completed on each server so that it could resolve the proper address. This would be bother administratively burdensome and also riddled with troubleshooting issues.

A SQL Alias would also work but would fall under the same set of issues as the Host Record.

Hardware Load Balancers (or software load balancers, depending on implementation) are a great alternative as they can do the DNS swap for you. This could bring up some issues with Kerberos or Certificate signing, however those application could be singled out and fixed (potentially, depending on your environment and resources).

The DNS Alias is roughly the same as the Hardware Load Balancer option and the same set of issues would occur. However this option does not require specialized hardware or software in order to complete.

I would recommend either the HLB or DNS Alias options.


How to find foreign keys that don't have corresponding indexes?

Oracle SQL Developer ships with a report to find these unindexed columns.

Here's the SQL behind this report:

select a.owner            "Owner",
                                 a.table_name       "Table_Name",
                                 a.constraint_name  "Constraint_Name",
                                 a.columns          "Foreign_Key_Column_1",
                                 b.columns          "Foreign_Key_Column_2",
                                 a.owner             sdev_link_owner,
                                 a.table_name        sdev_link_name,
                                 'TABLE'             sdev_link_type
                            from
                            ( select a.owner, substr(a.table_name,1,30) table_name,
                                 substr(a.constraint_name,1,30) constraint_name,
                                   max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
                                from sys.dba_cons_columns a,
                                     sys.dba_constraints b
                               where a.constraint_name = b.constraint_name
                                 and a.owner = b.owner
                                 and (:OWNER is null or instr(b.owner, upper(:OWNER))>0)
                                 and b.constraint_type = 'R'
                               group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
                            ( select table_owner,
                                     substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
                                   max(decode(column_position, 1,substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
                                from sys.dba_ind_columns
                               group by table_owner, substr(table_name,1,30), substr(index_name,1,30) ) b
                      where a.owner      = b.table_owner (+)
                        and a.table_name = b.table_name (+)
                        and substr(a.table_name,1,4) != 'BIN$'
                        and substr(a.table_name,1,3) != 'DR$'
                        and b.table_name is null
                        and b.columns (+) like a.columns || '%'
                      order by a.owner, a.table_name, a.constraint_name


Use min max value from a CTE to define interval range on a next CTE

Range returns exactly 1 row, so you can use a cartesian product (cross join):

, series AS (
    SELECT (row_number() over (ORDER BY 1)) + (range.min_id - 1) AS id,
    FROM logs
    CROSS JOIN range
    WHERE active_connections.id BETWEEN range.min_id
                                    AND range.max_id
)

I'm not familiar with redshift so if cross join is not supported you can use a join where the join predicate always evaluates to true:

    FROM logs
    JOIN range ON 1=1
    WHERE active_connections.id BETWEEN range.min_id
                                    AND range.max_id

or an old style "," join;

    FROM logs, range
    WHERE active_connections.id BETWEEN range.min_id
                                    AND range.max_id

You can also use a normal join and use the where predicate in the on clause:

    FROM logs
    JOIN range
        ON active_connections.id BETWEEN range.min_id
                                     AND range.max_id


In theory you could do something as like wrap the script execution in a transaction. You can execute the .sql batch files from an application library like dbutilssqlcmd or SMO's ServerConnection.ExecuteNonQuery which handles the GO batch separator or sqlcmd extensions in the script.

However in practice this is nearly impossible to do. Wrapping a script in a transaction may yield unexpected results because the locking changes. Certain DDL statements are not allowed inside a transaction. The script may contain sqlcmd extension directives like :connect which would mess up the entire transaction handling.

Another approach is to use migrations, which are coded in the application and handle both the roll forward and the roll back of the change. But to be effective the steps must be idempotent (yield the same result even if invoked multiple times) and this is very hard to achieve in practice.

The safest and also easiest approach is to rely on a backup taken prior to migration, which would give the ability to rollback to the backup if anything goes wrong during the deployment of V. next. This is very easy to implement and very efficient if database snapshots are used as backups.

Of course if there is a 24/7 availability requirement things change dramatically, but rolling out upgrades with no downtime is a subject much bigger that this post and solutions roll in the $millions, unlikely something you would fancy.


What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx?

Making an account db_owner, even for [tempdb], is still risky. Fortunately, it's not necessary to do this in order to accomplish the goal. You can simply use Module Signing to allow a stored procedure residing in [tempdb] make use of the db_owner database role:

Initial Setup

This just gets us to the same state as the code provided in the question (minus the testlimiteduser_app role, which is unnecessary for this goal).

IF (DB_ID(N'NotTempDB') IS NULL)
BEGIN
    CREATE DATABASE [NotTempDB];
    ALTER DATABASE [NotTempDB]
      SET RECOVERY SIMPLE;
END;
GO

USE [NotTempDB];

IF (SUSER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE LOGIN [testlimiteduser]
      WITH PASSWORD = N'apassword',
      DEFAULT_DATABASE = [tempdb],
      CHECK_EXPIRATION = OFF,
      CHECK_POLICY = OFF;
END;
GO

IF (USER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE USER [testlimiteduser]
      FOR LOGIN [testlimiteduser];
END;

Basic Setup

We need a module that can be signed (ad hoc SQL will not work), but we are not signing it just yet.

Here we:

create the stored procedure
grant the ability to execute that stored procedure to the test user.

GO
CREATE OR ALTER PROCEDURE dbo.[UpdateStats]
(
  @TableName sysname,
  @RowCount INT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = CONCAT(N'UPDATE STATISTICS ',
    QUOTENAME(@TableName),
    N' WITH ROWCOUNT = ',
    @RowCount);

EXEC (@SQL);
GO

GRANT EXECUTE ON dbo.[UpdateStats] TO [testlimiteduser];
GO

TEST 1

Please note: is best to use EXECUTE AS LOGIN, not AS USER as LOGIN is not only more accurate in terms of what happens when the app really does connect to SQL Server, but doing USER will add restrictions that might skew the behavior / test results.

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;

Module Signing Setup

Create the certificate
Sign the stored procedure with that certificate
Copy certificate (public key only) to [master]
Create the certificate-based login from it.

IF (CERT_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE CERTIFICATE [Permissions$UpdateStats]
    ENCRYPTION BY PASSWORD = 'Super Bass-o-matic ''76'
    WITH SUBJECT = N'Permission to exec UPDATE STATISTICS ... WITH ROWCOUNT = ...',
    EXPIRY_DATE = '2099-12-31';
END;

-- Associate permissions (via future cert-based login) to stored procedure:
ADD SIGNATURE TO dbo.[UpdateStats]
  BY CERTIFICATE [Permissions$UpdateStats]
  WITH PASSWORD = 'Super Bass-o-matic ''76';

-- Copy certificate (public key only) to [master]
-- and create the cert-based login from it.
DECLARE @CopySQL NVARCHAR(MAX) = N'
USE [master];
    CREATE CERTIFICATE [Permissions$UpdateStats]
      FROM BINARY = ' + CONVERT(NVARCHAR(MAX),
      CERTENCODED(CERT_ID(N'Permissions$UpdateStats')), 1) + N';

    CREATE LOGIN [Permissions$UpdateStats]
      FROM CERTIFICATE [Permissions$UpdateStats];
';

EXEC(@CopySQL);

TEST 2

We signed the stored procedure and associated it with a login, but we haven't yet given that login the necessary permission, so the results are the same.

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;

Final Setup: Assign Permission to Login OR User

This was kept as a separate step only to make it very clear that it is indeed the module signing that is getting this to work. And you have two options (pick only one):

add the existing certificate-based login to the sysadmin instance-level role

ALTER SERVER ROLE [sysadmin]
  ADD MEMBER [Permissions$UpdateStats];

OR:
Create a User in tempdb from the certificate-based login, then add the new User to the db_owner database-level role

USE [tempdb];
IF (USER_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE USER [Permissions$UpdateStats]
      FOR LOGIN [Permissions$UpdateStats];

    ALTER ROLE [db_owner]
      ADD MEMBER [Permissions$UpdateStats];
END;

TEST 3

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- SUCCESS!!!!

REVERT;

Conclusion

The main differences between the options are:

Option 1

Good: no object in tempdb (that needs to be created upon each start of the SQL Server service)
Bad: sysadmin permission is more powerful (not exactly "least privileged")

Option 2

Good: db_owner is more contained than sysadmin, in terms of power
Bad: object in tempdb (that needs to be created upon each start of the SQL Server service)

The difference between sysadmin and db_owner` is minimal, if not even relevant here, since the only thing that can be done with either of those permissions is what the code in the stored procedure does (this is one of the main benefits of module signing: it's highly granular).

So on a practical level, the complexity of creating the User in tempdb and adding it to db_owner upon each start of the SQL Server service (which requires creating a stored procedure in master, marking it as a "startup procedure", and enabling the instance-level config option "scan for startup procs") is worse than making the login sysadmin.

If the DB needing the additional permission was a user database, then I would probably go with Option 2, even if only for the purpose of having it match more closely with the intended use, as opposed to their being any real concern over sysadmin vs db_owner.

For a detailed explanation of the steps taken to apply module signing, please see my post:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

For more information about module signing in general, please see my post:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining


Force Client to Validate SSL Certificate?

My question is this how we can force the client (by the server or any way) to verify the certificate regardless of the connection string value?

Connection string values override defaults set in the driver if the connection string has the item filled in. There is no overarching way of forcing this except to use TrustServerCertificate=false in the connection string to zero out any other options.


Reduce the number of queries to the same table

You can cut this down quite a bit - the first thing to notice is that you don't need the information in the author_titles table at all to get your results:

select author_id
     , sum(case max_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)/count(*)
         max_items_sold_wavg
     , sum(sold)/count(*) items_sold_wavg
     , created_at
from ( select author_id
            , created_at
            , sum(case items_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)
                sold
            , max(items_sold) max_sold
       from author_reports
       group by author_id,title_id,created_at ) z
group by author_id,created_at;

SQLFiddle here


The transaction log for database 'tempdb' is full but it should have grown

I've seen this occur when the file growth setting is set too high, so the growth event takes too long. Obviously this depends on your storage environment and performance details.

In my case, the 10% default growth rate was fine when the database was small, but at some point when the database got huge enough that the growth event took over 30 seconds, which was long enough that the original query timed out and rolled back. The rollback then cancelled the growth event, until the application re-tried the same query, and we got an endless loop.

So check your growth rates, or just pre-size your tempdb to a larger size.


What is the impact of temporarily setting ALLOW_PAGE_LOCKS ON

Presuming the database is not actually "in-use" at the time this rebuild process starts

Assuming what you are presuming, I don't what side-effect it could have, outside of the obvious allowing for index REORG to happen :-).

Of course, I have no proof, but not sure what proof to look for that nothing bad will happen when nothing is happening in the DB outside of this REORG operation.

I am also not aware of there being any analogy here to Foreign Keys being untrusted, and hence not considered by the Query Optimizer, if created using the WITH NOCHECK option to skip validation (and not trusted until altering using WITH CHECK).

Regarding the notion of index property vs structure of the index pages: I would say that this could be answered by altering a large index to just turn this option ON and OFF. If it happens instantly, then it is not writing the change to all pages. And, it is also probably a moot point since even if there is a change, it will be handled via the ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = OFF); and completed before the COMMIT. Still, I can't imagine this being anything other than a simple index property, since it can't change on a per-page basis. Of course, this can also be seen by using DBCC PAGE to view the contents of an index before and after you make this change.


Have aggregate function group results in one row

You are looking for the crosstab() function provided by the additional module tablefunc.

Then your function could look like this:

SELECT * FROM crosstab(
   'SELECT class, payment_method, sum(debit - credit) AS saldo
    FROM   tbl
    GROUP  BY 1,2
    ORDER  BY 1,2'

   ,$$VALUES
        ('payment_method1'::text), ('sumofpayment_method1')
      , ('payment_method2'), ('sumofpayment_method2')
      , ('payment_method3'), ('sumofpayment_method3')$$
   )
AS ct (class text
     , payment_method1 numeric, sumofpayment_method1 numeric
     , payment_method2 numeric, sumofpayment_method2 numeric
     , payment_method3 numeric, sumofpayment_method3 numeric);

I am using all 6 "types" you mentioned. Well, I see that only half of them are actually intended as type. Either way, substitute your actual types.
More details, links and explanations in this related answer on SO.


what is the performance impact if we have two asymmetric files on a Filegroup in SQL server database?

I wouldn't say it's a bad practice, but definitely  not a best practice.

It looks like you're using the primary filegroup for user objects, which is not a best practice. You'd better separate system and user objects.

It also appears that you're storing all the objects in the same filegroup. It might be a good idea to isolate some critical objects from the other ones, in order to put them in a dedicated disk for performance or recoverability reasons. Having multiple filegroups allows you to perform file and filegroup backup/restores, which can help you if you have challenging backup schedules.

If your files were the same size, SQL Server could use the proportional fill algorithm to stripe writes over the files. The storage engine tries to write to files that have the most free space available: you get best results (both files getting written in turn) when the files are the same size.

Autogrowth is nice to have to avoid running out of space when you have unplanned growth, but ideally you should grow your files in advance and keep them the same size.

Unlimited growth can be problematic if the file grows until you run out of disk space. In that case, no other database file on that disk will be able to grow and you could end up with multiple databases in trouble instead of just one.


How to resolve unmodifiable replica set state

When you add hosts, _id is set always one bigger than what is current max _id. If you really want to "fix" that, you can always do:

var cfg=rs.conf()
cfg.member[3]._id=0
cfg.member[4]._id=1
rs.reconfig(cfg)

Same way, you can change anything or everything in your rs configuration. Remove or add new hosts, change an order of hosts in the list.


Index usage and update statistics do not correspond?

About: sys.dm_db_index_operational_stats

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).
The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available.

See: sys.dm_db_index_operational_stats (Transact-SQL)
On the other hand, sys.dm_db_index_usage_stats, which is mentioned above, is only reset when the service is restarted:

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.


First make sure the file exists really in that location. Run ls $PGDATA if there is a mistake in the environment variable you will either see the wrong files or get an error because the path does not exist.

Then make sure the owner of the parent directory /home/destination_data_directory and all directories and files under match the user you start pg_ctl under. If they are not either use

su postgres_user

to switch to the right user before running pg_ctl or use

chown -R postgres_user /home/destination_data_directory

to change the owner of the directory and everything under it.


Oracle 19c Installer Can't Select National Character Set

Just install the DB software then use DBCA to create the database.
There you can choose the national characterset.

Typically noone uses the installer to create a database. Some others (including me) avoid even DBCA.


How do I create a MySQL slave server of a bunch of MyISAM databases on a master

Can you shutdown the master server long enough to copy the data folder to another location?

You don't have to perform a mysqldump. You could just shutdown MySQL on the master server and copy the 'data' folder (folder having your database files) to another location (or server). Next, start both servers with replication disabled (skip-slave-start). Next, issue a RESET MASTER and RESET SLAVE on both servers. Then do a CHANGE MASTER on the slave. Finally, start the slave. If you are using GTIDs, then remove the auto.cnf file on the SLAVE so that it generates a new UUID (auto.conf is usually stored in the data folder).

See this for step-by-step instructions: http://mysql-vsjr.blogspot.com/2015/09/how-to-clone-gtid-mysql-56-server-using.html


What are the names of these Postgres concepts?

If you want relational theory names, then:

Heading (but nobody calls it that, usually "field names" or "attribute names" or "column names" is more typical)
Tuple
Attribute name
Attribute (but that'll confuse people)
Attribute value

... and a data type is a domain. The whole table toether is a relation.

Nobody really uses that language though. More typical would be:

Table definition (if it includes data types, constraints, etc), or column names if it doesn't.
Tuple or row
Field name or column name
Column (if it includes name) or column values (if it doesn't); but the latter is uncommon in relational DBs since you rarely refer to a column except by selecting fields out of tuples.
Field or attribute value

The whole thing together is usually called a table, but relation is common enough too.

Whatever names you choose to use, please do not assume that tuples have any natural ordering in a relation. They don't.

And don't bake column ordering into your API. Column ordering is stable within a query result, of course, but apps should be discouraged from hard coding column ordinal positions. This is OK (pseudocode):

 zip(['col1', 'col2', 'col3'], do_query('SELECT col1, col2, col3 FROM my_table WHERE ...'))

but this is a bad, bad idea:

 zip(['col1', 'col2', 'col3'], do_query('SELECT * FROM my_table WHERE ...)]

as any addition of a column will break the code. So will less obvious changes like altering the data type of a column.

Apps should be encouraged to use query interfaces that produce a field-name to value map as a result, where field names are discovered from the result set metadata.

For efficiency you still want to offer ordinal access. But field name access is usually preferable.

Python for example does this with a result object that can behave as both an array (ordinal access) and dictionary (key access).

You've omitted data typing though. Relational DBs are strongly typed.

Here's a more typical table definition:

demo-> \d address
                                Table "public.address"
   Column    |  Type   |                          Modifiers
-------------+---------+--------------------------------------------------------------
 address_id  | integer | not null default nextval('address_address_id_seq'::regclass)
 customer_id | integer | not null
 address     | text    |
Indexes:
    "address_pkey" PRIMARY KEY, btree (address_id)
Foreign-key constraints:
    "address_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)

Critically, note that every column has a name, a type, and zero or more constraints like not null or primary key. There are also various other details like foreign key constraints.


SQL Server: Creating a user database with permissions

Some Ideas (Anyone: please feel free to Edit/Add)
ApplicationPermissions (Application Level Permissions)

ApplicationID
PermissionID
Allow (Y/N/1/0)
Value (As in a Max Number of "X" allowed)
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

User_Application_Permissions (User Level Permissions)

UserID
ApplicationID
Allow (Y/N/1/0)
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

UserPermissions (Descriptive/Attribute Table)

PermissionID
PermissionDescription
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

Users (Descriptive/Attribute Table)

UserID
UserName
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID


For questions like that, you'll have to consult the log files.

But the likely explanation is that the primary server was ahead of the standby when it crashed (this can also happen with synchronous replication). Then it cannot act as standby to the newly promoted server.

There are some solutions:

Easiest, but takes long: Reinitialize the server with pg_basebackup.
Faster, but more complicated: get the new primary in backup mode and use rsync.
Easiest, but requires that wal_keep_segments is high enough: pg_rewind the failed server.

You'll have to implement this in the failover scripts.

Why don't you use Patroni which does all that out of the box?


Export just rows modified in last month (date defined by user)

This would depend on the table having a timestamp column.
mysqldump has a --where option that you can define when dumping a specific table
EXAMPLE
If you have a table called mydb.mytab defined as follows
CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    tm TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

You could specify the date range on that table as follows (for June 2013):
WHERE_CLAUSE="(tm >= '2013-06-01 00:00:00') AND"
WHERE_CLAUSE="${WHERE_CLAUSE} (tm < '2013-07-01 00:00:00')"
mysqldump -uroot -p mydb mytable --where="${WHERE_CLAUSE}"

This would be most helpful since timestamps can be updated automatically.
I wrote a post on Aug 15, 2011 on how to use --where to dump a subset of data not based on a timestamp : Is it possible to mysqldump a subset of a database required to reproduce a query?
If you do not have a timestamp column in the desired tables, you are better of just keeping all binary logs generated during any given month.


Get count based on 15 minutes of interval

This is a classic example of how a "Numbers table" can really help get the results you need.

Essentially, you create a table containing the 15 minute increments you desire, then join your table to obtain an aggregate number of calls for each 15 minute increment.

In example, I'm using temporary tables for both tables.  You'd likely want to make the #Intervals table permanent.

Create the testbed, and populate some sample data:

USE tempdb;

IF (OBJECT_ID('tempdb..#Calls') IS NOT NULL)
DROP TABLE #Calls;

CREATE TABLE #Calls
(
    CallID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CallStart DATETIME NOT NULL
    , CallEnd DATETIME NOT NULL
);

;WITH cte AS
(
    SELECT rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
        , rn1 = ROW_NUMBER() OVER (PARTITION BY o.object_id
                                     ORDER BY o1.object_id)
    FROM sys.objects o, sys.objects o1
)
INSERT INTO #Calls (CallStart, CallEnd)
SELECT DATEADD(MINUTE, c.rn, DATEADD(DAY, -1, GETDATE()))
    , DATEADD(MINUTE, c.rn + c.rn1, DATEADD(DAY, -1, GETDATE()))
FROM cte c;

IF (OBJECT_ID('tempdb..#Intervals') IS NOT NULL)
DROP TABLE #Intervals;

CREATE TABLE #Intervals
(
    DateStart DATETIME NOT NULL
    , DateEnd DATETIME NOT NULL
);

;WITH cte AS
(
    SELECT TOP(35040) /* approx. number of 15 minute intervals in a year */
        rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id) * 15
    FROM sys.objects o
        , sys.objects o1
        , sys.objects o2
)
INSERT INTO #Intervals (DateStart, DateEnd)
SELECT DATEADD(MINUTE, c.rn, '2015-10-01T00:00:00')
    , DATEADD(MINUTE, c.rn + 15, '2015-10-01T00:00:00')
FROM cte c;

Show the rows in both tables:

SELECT *
FROM #Intervals i
ORDER BY i.DateStart;

SELECT *
FROM #Calls c
ORDER BY c.CallStart;

Join both tables to get the aggregate count of Calls between the 15 minute date ranges:

SELECT i.DateStart
    , i.DateEnd
    , TotalCalls = COUNT(1)
FROM #Calls c
    INNER JOIN #Intervals i ON c.CallStart >= i.DateStart
        AND c.CallStart < i.DateEnd
GROUP BY i.DateStart
    , i.DateEnd
ORDER BY i.DateStart;

On my test platform, I get the following results from the three select statements:

Looking at your comment on @Thofle's answer, it looks like you want to see all time intervals, even if there were no calls during the given interval.  To accomplish that, you can simply modify the select query to use a LEFT JOIN, and COUNT(...) the number of Calls rows, like:

SELECT i.DateStart
    , i.DateEnd
    , TotalCalls = COUNT(c.CallID)
FROM #Intervals i
    LEFT JOIN #Calls c
        ON i.DateStart <= c.CallStart
            AND i.DateEnd > c.CallStart
GROUP BY i.DateStart
    , i.DateEnd
ORDER BY i.DateStart;


Use BCP To Copy When Data Has Apostrophe In It

Presumably you are expecting the .csv file to be opened using Excel or some equivalent.  I'd recommend wrapping the outputted text columns in double-quotes, such as:

declare @sql VARCHAR(1000);

Select @sql = 'bcp.exe "SELECT ''"userid"'',''"name"'',''"data"'',
''"data1"'',''"data2"''
UNION ALL
SELECT CHAR(34) + CAST([userid] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([name] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data1] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data2] As VARCHAR(MAX)) + CHAR(34)
FROM dbo.OneTwoThree" queryout "'
SET @sql = @sql + 'C:\temp\BCPTest.csv' + '" -c -t, -T -S '+ @@servername
PRINT @sql;
exec master..xp_cmdshell @sql

The output will look similar to:

If the BCPTest.csv is then opened with Excel, the double-quotes will be automatically interpreted as indicating those columns are text columns, and Excel will not show the quote marks.  Therefore, you only want to wrap text (CHAR, VARCHAR, NVARCHAR etc) columns in CHAR(34).

At that point, you'll start to see issues with columns that contain double-quotes; however you might decide to use the REPLACE() function to convert them into single-quotes or some other innocuous character.


Problem with EXEC MyLinkedServer...sp_executesql within transaction

The error message refers to the OLE DB provider defined for the linked server. Hence the SQL code has no error.

The problem is the linked server is not properly defined. You should test the connection with the linked server and you will see the full error.

To fix it: recreate the Linked server setting an OLE DB provider supported in your Sql Server version. For example: 'SQLNCLI' or 'SQLNCLI11'.

To recreate a linked server:

Right click -> Script Linked Server as -> Create To -> New query editor window.
Change the OLE DB provider and the correct password (it will be scripted as @rmtpassword='########').
Test the connection with the linked server.
Try to execute again:

EXEC MyLinkedServer...sp_executesql @sql


SQL Server: Remove public role privileges from login account

Is there a way for me to say "Yes, I know you are a member of the public role, but I'm revoking privileges X,Y,Z"?

You can use DENY :

  "Denies a permission to a principal. Prevents that principal from
  inheriting the permission through its group or role memberships. DENY
  takes precedence over all permissions, except that DENY does not apply
  to object owners or members of the sysadmin fixed server role.
  Security Note Members of the sysadmin fixed server role and object
  owners cannot be denied permissions."

So to DENY the DELETE permission to a user Fred database-wide:

DENY DELETE to Fred;

Or for just one schema

DENY DELETE ON SCHEMA::dbo to Fred;

But I would advise you to investigate why Public has all those permissions.  That's not a common or recommended security model.


Do covering indexes in PostgreSQL help JOIN columns?

Will the index be used as a covering index to help the JOIN in the above query?

It depends. Postgres has "index-only" scans as index access method, there are no "covering indexes" per se - up to Postgres 10.
Starting with Postgres 11 true covering indexes with INCLUDE columns are available. Blog entry by Michael Paquier introducing the feature:

https://paquier.xyz/postgresql-2/postgres-11-covering-indexes/

Related answer with code example:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

That said, the index CREATE INDEX ON table2 (t2c1, t1); makes perfect sense for the query you demonstrate. It can be used for an index-only scan if additional preconditions are met, or it can be used in a bitmap index scan or a plain index scan. Related:

Index usage on a temporary table
Is a composite index also good for queries on the first field?

JOIN conditions and WHERE conditions are almost completely equivalent in Postgres. They certainly can use indexes in the same way. You can rewrite your query:
SELECT table1.t1c1
FROM   table1
JOIN   table2 ON table2.t1 = table1.id
WHERE  table2.t2c1 = 42;

With this equivalent:
SELECT table1.t1c1
FROM   table1 CROSS JOIN table2
WHERE  table2.t1 = table1.id
AND    table2.t2c1 = 42;

The first form is obviously preferable, though. Easier to read.
Why "almost" equivalent? (Makes no difference for the simple query at hand.)

Why does this implicit join get planned differently than an explicit join?

Related:

Are implicit joins as efficient as explicit joins in Postgres?
What does [FROM x, y] mean in Postgres?


Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are:

Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much prefer this method as it gives you complete control over the Table that is being created, such as including the auto-increment column and specifying that it be the Primary Key. For example:

CREATE TABLE dbo.tmpTable
(
  tmpTableID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ...
  {all columns represented by aa.* in the sample query in the Question}
);

If you can't change how / when / where the table is being created, you can always add a Column later, and when doing so, you are allowed to specify both that it be an IDENTITY column and have the Primary Key created on it. For example:

ALTER TABLE [dbo].[tmpTable]
  ADD [tmpTableID] INT NOT NULL
  IDENTITY(1, 1)
  PRIMARY KEY;

This even works if the Table already has data in it: the new IDENTITY column will be populated as expected, starting with the value specified for the seed parameter. However, there is no way to control the order in which the values are assigned (which is one of several reasons to go with option #1, if at all possible).

Additional notes:

Are you sure that you need a Primary Key and not simply an auto-incrementing / unique column? While it is typically a good idea to have a Primary Key, it is neither required nor the same thing as an auto-incrementing column. I only ask because both the title and text of this Question state that you need a Primary Key, yet you are saying in a comment on the Answer that you accepted that simply having the auto-increment column worked.
The table you are using is not actually a temporary table. Real Temporary Tables have names starting with #, or ## for Global Temporary Tables. The table you are using dbo.tmpTable is just a regular, permanent table that is prefixed with "tmp" to indicate that it is probably just for this process and not part of the data model.

If the app code doesn't need to access this "temporary" Table, and the only reference to it is within this Stored Procedure, then you might consider changing it to be a real Temporary Table, which has the advantage of being cleaned up when the process completes, in which case you wouldn't need the DROP TABLE statement.
If you will be using a permanent Table instead of a Temporary Table (in which case you need to clean it up yourself), then the DROP TABLE statement should be conditional, such that it doesn't error if the Table doesn't exist:

IF (OBJECT_ID(N'dbo.tmpTable') IS NOT NULL)
BEGIN
   DROP TABLE dbo.tmpTable;
END;

Rather than doing SELECT *, you should specify the full column list. Using * makes the process more likely to break when you add columns / fields to tables or subqueries (whatever aa is an alias for).


First, make sure the cluster's user account can create computer objects in Active Directory. See this link for the specific steps you need to do in Active Directory Users and Computers to grant the permissions. By default, the cluster does NOT have the rights it needs and the AG wizard doesn't really do anything to warn you or to grant those rights for you.


I can't figure out how to call a function with a trigger, keeps saying function doesn't exist

Before creating trigger, you need to create a trigger function, which has to be (quoting the manual):

  A user-supplied function that is declared as taking no arguments and returning type trigger

Like:

CREATE OR REPLACE FUNCTION calculate_t_f()
  RETURNS trigger AS
$$
BEGIN
...
-- do something
-- maybe with with calculate(char)...
...
END
$$ LANGUAGE plpgsql;

Then you can create your trigger with that trigger function (not your original function):

CREATE TRIGGER trigger_calculate
AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate_t_f();


Create a function column that is the result of OR between 3 columns

The syntax for virtual columns is very simple:

ALTER TABLE tableX
  ADD abc AS (COALESCE(a, b, c)) ;

Tested at rextester.com.


Synchronising batch delete from multiple applications with advisory lock in Postgres

Manual locking is probably unnecessary.  When the second process to get to a row in order to delete it discovers that it is already in the process of being deleted, it will inherently block until that deleting transaction commits. So this should already yield the behavior you want.

It is possible (but I think unlikely) that the processes will choose different execution plans which delete rows in different orders, and deadlock against each other.  In that case, one will get served an ERROR, and the other will proceed.  You will still get an acceptable outcome if you retry the ERRORed process, or if you are sure this is the only source of deadlocks you could just ignore deadlock errors and trust that the winner of the deadlock did the job.  This is somewhat wasteful to have one process do a lot of work which just gets aborted and repeated, so you it might be worthwhile to use advisory locking to avoid that.  But I wouldn't bother until I had a demonstrated problem that needs to be solved.

Depending on the execution plan for the deletions, verifying that no rows need to be deleted might itself be a lot of work.  If you want to reduce that work, you could use "try" advisory locks to skip the work for all but the first session, but then if the first session fails before it finishes but after it has warned off the others, then the job just won't get done.  And if your app uses advisory locks for other purposes, you would want to make sure you don't get collisions on the lock keyspace.


move a database on sql server 2017 with memory OLTP feature

Once a memory-optimized filegroup is added to a database, it's not possible to remove it. You would need to migrate your "traditional" data to a new database, then drop the original database, and rename the new to the old.

Why do you want to remove the memory-optimized filegroup?


Alright so I believe you doing and INSERT is increasing the size then after you do your OLEDB command which is sending hundreds of thousands of DELETE commands to SQL Server is what's causing your problem. So here is a short Demo/Tutorial on how to do this DELETE operation more effectively. Even if this doesn't solve your index problem it will make your package run more efficiently.

STEP 1: Build a staging table for this information.

You're going to want to CREATE a table that's sole purpose is to hold the columns necessary for the delete. Whatever makes up your Primary Key will do.

STEP 2: Truncate the Staging table.

It's important to truncate first as this will all for re-run if the package fails.

Step 3: Data Flow task.

Next add a Data Flow Task and open.

If you are doing your archive elsewhere than disregard the Multicast and the Archive destination in the next image.

STEP 3: Delete

That should give you a pretty good idea of how to do this if you have any other question feel free to ask.

The reason this will be more efficient is because you are then doing set based operations instead of sending 350K DELETE commands to SQL server.


Get the Record where only Two Records exists in SQL

If I understand you correctly, what you want is
SELECT ParentID
FROM   UserTbl
GROUP BY ParentID
HAVING SUM(CASE WHEN userID IN (1, 2) THEN 1 ELSE 0 END) = 2
AND    COUNT(*) = 2

The first condition SUM(CASE WHEN userID IN (1, 2) THEN 1 ELSE 0 END) = 2 is to check User 1 and 2 are in
The second condition COUNT(*) = 2 users count = 2 (only user 1 and 2)


It's quite difficult to give any suggestions based on that information, but here's the approach I normally use for performance tuning. The order of different steps can vary, but these usually give quite a good idea where the improvements should be done.

1) Check for blocking

if someone else is blocking the query, you should focus on the blocking query first

2) Set statistics io on.

This will show you the I/O count per table per statement when you run the procedure. If your sql is simple statements, I/O count and time spent in that table should be quite linear. Amount of data in cache makes a big difference for first vs. second run. If you're using user defined functions, I/O generated by those is not shown with statistics io.

3) Query plan, preferably actual plan.

Look at think arrows (=a lot of rows). There might be something to improve here, indexing, statistics, something to adjust in the query itself. This should be give similar picture as statistics io.

Key lookups, especially ones with big percentages or big actual execution count. If the number of output columns is small you could add include columns to the index. Remember, percentages are just estimates.

Look out for index / table scans, especially in cases where you're not reading most of the table.

4) Plan cache.

You can find statement level statistics from sys.dm_exec_query_stats. I usually look into total_worker_time and total_logical_reads, other columns can also give valuable information about where the problem could be.


Is there a way to run this query in all databases ? ( SQL Server 2008 R2 )

First, create a temporary table (not a table variable) to hold the results:

SET NOCOUNT ON;
GO
CREATE TABLE #results
(
  db SYSNAME,
  [schema] SYSNAME,
  [table] SYSNAME,
  [column] SYSNAME,
  ColumnValue NVARCHAR(3640)
);
GO

Now, the following gets a little convoluted, because this is like peeling an onion. We need to build dynamic SQL commands that query directly against tables, based on the innermost scope (which is dynamic SQL against each database's metadata), and finally execute all of that for each database.

DECLARE @search NVARCHAR(100) = N'%foo%', -- N prefix is important!
  @sql          NVARCHAR(MAX) = N'',
  @cmd          NVARCHAR(MAX) = N'';

-- build a command per database
SELECT @sql += N'EXEC ' + QUOTENAME(name)
  + '.sys.sp_executesql @cmd, N''@search NVARCHAR(MAX)'', @search;'
  FROM sys.databases
  WHERE database_id > 4 AND [state] = 0 AND user_access = 0 AND is_read_only = 0;

-- need an inner command to build a separate query
-- for every string-based column
SET @cmd = N'DECLARE @inner NVARCHAR(MAX) = N'''';
  SELECT @inner += CHAR(10) + N''SELECT
        [db]     = DB_NAME(),
        [schema] = N'''''' + s.name + '''''',
        [table]  = N'''''' + t.name + '''''',
        [column] = N'''''' + c.name + '''''',
        ColumnValue = LEFT('' + QUOTENAME(c.name) + '',3640)
      FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
      WHERE '' + QUOTENAME(c.name) + N'' LIKE @search;''
  FROM sys.schemas AS s
    INNER JOIN sys.tables AS t
    ON s.[schema_id] = t.[schema_id]
    INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239);

  EXEC sys.sp_executesql @inner, N''@search NVARCHAR(100)'', @search;';

-- now multi-nested actual execution
INSERT #Results(db,[schema],[table],[column],ColumnValue)
  EXEC [master].sys.sp_executesql @sql,
  N'@cmd NVARCHAR(MAX), @search NVARCHAR(100)', @cmd, @search;

Now, select the results and clean up:

SELECT db,[schema],[table],[column],ColumnValue
  FROM #Results
  ORDER BY db,[schema],[table],[column],ColumnValue;

DROP TABLE #Results;

This solution only deals with string-based columns; in your question you list int and decimal, but that strikes me as odd...


You can create tables because the RESOURCE role has the CREATE TABLE privilege granted. To create a view, you need to have granted the CREATE VIEW privilege additionally, because, by default that privilege is not granted, not even to RESOURCE.

SQL> select privilege from dba_sys_privs where grantee='RESOURCE';

PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

So you need to do the below:

grant create view to hrs;


Knowing that a CHECKDB returns errors right after restoring a backup, as stated by Remus Rusanu, you have a corrupted backup. As fixing this afterwards is usually much more painful then before, you should plan (or ask your DBA) to check the consistency of your databases on a very regular basis. I remember having anxious moments waiting for the results of a first DBCC CHECKDB for databases that have been in production for a few years, long before I started my job.

If you want to know more about corruption and DBCC, have a look at:

How to corrupt a database, by Kendra Little: http://www.littlekendra.com/2011/01/24/corrupthexeditor/. I found it a great way to understand how things were working under the hood.
Paul Randal's articles, for example http://www.sqlskills.com/blogs/paul/category/checkdb-from-every-angle/ . He is THE authority on DBCC and you can also subscribe to their newsletter. The last one has a great introduction video on corruption and how to fix it.

So check (sorry for this one) the health before, try to restore a valid backup, and if you have no other choice, try DBCC REPAIR_ALLOW_DATA_LOSS. But as stated before, you'll lose data and that can even make your database unusable.


Is there a data type I can use for RA & Dec in astronomy

If you just want to store the coordinates, for later searching, your best bet is to convert from degrees-minutes-seconds to decimal degrees, and store as DECIMAL. Here is an answer giving mysql functions that do that conversion, in both directions.

If you want to do more complex things like "Is this point within the constellation Orion" then you probably need to use spatial data extensions, as @RLF suggested in a comment.
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
http://dev.mysql.com/doc/refman/5.0/en/using-spatial-data.html


Eliminating effectively-redundant entries with Inner Join

SELECT ANY_VALUE(t1.nickname) nickname, t2.stipend
FROM moniker_tbl AS t1
INNER JOIN student_tbl AS t2 ON t1.name = t2.name
GROUP BY t2.name, t2.stipend;


You'd have to optimize the source and I'm not sure what optimizations are available for Access - a quick Google search reveals this and something about indexes here but I'm honestly clueless about how Access indexes work.

It occurs to me that you could create a linked server to the Access database and then just schedule a job to run a query against the link.

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyAccessSource',
    @srvproduct=N'',
    @provider=N'Microsoft.Jet.OLEDB.4.0',
    @datasrc=N'C:\my\accessdb.mdb';

or even query the source using OPENROWSET

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\my\accessdb.mdb';Table);
GO

or

select * from
  openrowset('Microsoft.ACE.OLEDB.12.0', 'C:\my\accessdb.mdb',
  'select * from Table');
GO

I guess this would save you the overhead of firing up SSIS packages and worrying about data flow tasks.


How can SQL server AlwaysOn AG can be used as a DR feature?

AlwaysOn supports multiple replica databases, so you can have one replica as your HA database, and another replica as your DR database. AlwaysOn is great in that one technology potentially provides your HA and your DR.

The HA database would typically be in the same datacentre, and be set to synchronous commit with automatic failover. The DR database would typically be in another datacentre, but because of the latency it would typically be asynchronous as first choice. This means that data-loss is possible. You could experiment to see whether the DR replica, set as synchronous, performs well enough for your application.

Please remember that if you use AlwaysOn as your DR technology, you still need to take database backups like normal - and get them shipped off the server.