Multi-tenant database using SQL Server 2008?

To be clear.  When talking about a DSN-less connection what you are really saying is that you are using a connection string that uses the native OLE DB driver to talk to SQL as opposed to ODBC.  Just want to clarify.

Usually with a tenant data model the really critical thing to solidify is the process that authenticates users and issues them a connection to the tenant database.  Most tenant database models don't use windows authentication as they are hosted or an SaaS product.  Your question makes it sound like you are using domain accounts however.

If you were assigning sql logins to each tenant or each windows login was viewed as a unique tenant then the easiest thing to do would be to define the TenantID and the sql login used in the connection to be the same value.  Then you wouldn't have to worry about a mapping table.  Why?  Because SUSER_SID() (the login id) IS the TenantID.  You could also use SUSER_SNAME() if you wanted.

CREATE VIEW TenantEmployees AS
SELECT * FROM Employees WHERE TenantID = SUSER_SID()

If you are not using sql logins or each tennant is associated with more than one login (such as with windows authentication) or business rules preclude you from using the login as the TenantID then yes.  The next best thing would be a mapping table.  Something like this for example that stores logins and the tenantID they are associated with.

UserKey   |  Tenant       |  TenantID  |  LoginName    |  LoginID
------------------------------------------------------------------------
1         |  Bills Bolts  |  111       |  billbolt     |  0x010050
2         |  Sams Screws  |  111       |  samscrew     |  0x010010
3         |  Nicky Nuts   |  222       |  nickynut     |  0x010000

In this case the view would look like this:

CREATE VIEW TenantEmployees AS
SELECT Employees.*
FROM Employees E INNER JOIN TenantMapTable T
    ON E.TenantID = T.TenantID
WHERE T.LoginName = SUSER_SNAME()  /* or T.LoginID = SUSER_SID() if preferred */

Another critical step however is that rights to the actual table are revoked.  Only grant access to the table through the view - or this was all for nothing.

Don't forget to remove access to anything but the views from the tenant user login.

Last - keep in mind that you can query a view just like a table.  So nothing is stopping you from filtering it further based on the needs of your application via procs or your ORM or where-ever.

select [firstname] from tenantemployees where [dob]='12/25/1980'


Generally in our setup guide, I recommend that you leave 4GB or 10% free for the operating system, whichever is larger. In this case, 4GB is larger, so you'd set max server memory to 28000.

The free memory is for:

Other services like SSIS, SSAS, SSRS (and if you use those extensively, you'll want to leave more memory aside)
Remote desktopping in (I wish people didn't, but so many folks RDP in and run SSMS or other tools)
Systems management apps (Dell OpenManage, HP SIM, etc that fire up and gobble memory)
Backup tools

If you monitor the Perfmon counter for Available Memory over time, and you consistently notice that most of the memory is going unused, then you can inch up max server memory to a higher number.


SQL Server Management Studio Change User Server Roles to sysadmin?

You've explained that you don't have the SA password and you're not an administrator. In that case, you're going to have to hack your way around it.

See Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again. by Argenis Fernandez.

That post explains how to impersonate the NT AUTHORITY\SYSTEM login using PsExec (or creating a Windows Scheduled Task running under the SYSTEM account) to create a new login for yourself. Then, you can log in via that new login, and grant your Windows account SA permissions.


How to fix error recording sp_whoisactive results to table?

You will have to download a newer version of Adam Machanic's sp_whoisactive stored procedure.
Preferably 11.16 or newer:

Version 11.16 - October 18, 2016 (Box versions 2005-2017 only. NOT for Azure PAAS.)

Fixed algorithm for identifying "special" database pages (e.g. GAM, SGAM). Thanks, Robert Davis!
Fixed @blockers duplicate key bug

In the code the definition of the @blockers table variable was changed to:
...
DECLARE @blockers TABLE
(
session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON) /*<<== HERE!*/
);
...

And then again, you could modify your existing code.


OBSERVATION #1
When committing to use InnoDB, you also need to commit to tuning for multiple cores.

innodb_read_io_threads : The default is 4. I would raise it 16.
innodb_write_io_threads : The default is 4. I would raise it 16.

OBSERVATION #2
I see you have innodb_thread_concurrency = 8. If you set innodb_thread_concurrency to 0 (which is the default), you will have infinite concurrency. That let's the InnoDB storage engine decide how to many threads it feels it needs and can handle.
OBSERVATION #3
Your DB Server has 12GB of RAM. Your InnoDB Buffer Pool is bigger than half the RAM. You need to partition the Buffer Pool by setting innodb_buffer_pool_instances to 2. In conjunction with this, you need to run numactl --interleave=all (Not applicable to VMs).
OBSERVATION #4
I see you have innodb_file_format = Barracuda. I wish you can go back to innodb_file_format = Antelope. Why go back to uncompressed? It tends to bloat the InnoDB Buffer Pool because compressed and uncompressed data and index pages coexist in the Buffer Pool. I just wrote about this : See my post innodb_file_format Barracuda
EPILOGUE
Here are some of my past posts on tuning InnoDB

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?

Give it a Try !!!


Is it a good idea to use null values to distinguish between records?

Based on your comment, I would suggest that the addition of the 'State' column adds additional application complexity (constraints necessary to keep the 'State' column in sync with the value in the 'Name' column) for, what appears to be, no added benefit.


Running two DELETE statements causes a syntax error

You can't* combine multiple queries like this.  You need to execute them separately.

When you see the error ...the right syntax to use near... that's telling you that MySQL's parser (lexer?) can't come up with any valid reason why it found whatever it's complaining about in the specific place within the statement where it was encountered... in this case, the beginning of a second query.

You'll need to send them to the server separately.

If these two deletions need to occur together, atomically, that should be handled in a transaction.

*"can't" -- actually, with some libraries, you can enable multi-statement execution, but don't do that. It opens up a whole new world of sql injection vulnerabilities.


How to create a trigger that update the date field and time field upon modify of the row?

It would be fairly easy but I would actually recommend changing the logic of the command that is inserting/updating the data so that it adds the additional information at this point.

However, should you wish to proceed with a trigger you could do something like this:

create trigger my_trigger
on my_table
after insert, update
as
begin
declare @dt datetime = getdate();
update a
set expire_date = CONVERT(DATE, @dt, 101),
    expire_time = CONVERT(TIME, @dt)
from my_table as a
join inserted as b
on a.product_no = b.product_no;
end
go

This uses the INSERTED table to find out the product_no of the rows that have been changed/created. You can find out more about triggers at the link below:

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

I hope this helps you.


Data read from linked server returns different data-type on production server

The observed difference in behavior is due to the difference in patch levels between the two servers.

Per the resolution of KB3051993, which is included in Cumulative Update 6 For SQL Server 2012 SP2):

  After you apply this fix, NUMBER values with unknown precision/scale
  are treated as double values with OLE DB provider. If the precision is
  important and the range of the values is not large enough, you can
  enable the new Trace Flag 7311 that would start treating such values
  as numeric (38, 10).


A dimension represent a category of information: e.g. date, product...
  in your case you have three attributes in the fact table referring to
  the same analysis axis which is 'DATE', so if you are using a star
  schema only one date dimension is needed, this is a dimension role playing implementation.
  There are two dimension role playing implementations :

The table alias type uses the dimension more than once in an SQL
statement by assigning an alias for each use.
The database view type, you create as many views as the number of
roles you need the dimension on the fact.

You need to only one date dimension, for exemple  :
the 'type' of date dimension will mentioned in the fact table as above.

  Updated :

Modeling Date and Time dimensions
There should be only one Date dimension and one Time dimension across all facts and dimension tables.  Not all date and time fields needed to be mapped to date and time dimensions. Map a date-time field to a Date or Time dimension only when you need the extra attributes from the dimension.
Typically the lowest granularity of the Date dimension is Day, and the lowest granularity of the Time dimension is Second. Many warehouses do not need a Time dimension, but a Date dimension is used in virtually every data warehouse system.
In general, create separate dimensions for Date and Time.
If it is necessary to extract contiguous chunks of time that cross day boundaries (for example 11/24/2000 10 p.m. to 11/25/2000 6 a.m.), then it is easier if the hour and day are in the same dimension. Otherwise, separate dimensions for Date and Time are easier to manage and to query.
It is easier to analyze cyclical and recurring daily events if Date and Time are separate dimensions.
Example:
Number of meetings active at 9AM this week.  This is an easy query if Date and Time are separate; it is much more complex if Date and Time are combined into a single dimension.
Following are issues of having date and time dimensions together in one Date/Time dimension :

  Issue#1:

  Combining Date and Time into a single dimension creates an extremely    large dimension, particularly if you use Seconds as the
  grain. The    problem is not as great if you use Hour as the grain of
  the combined    table.
  Cardinality of Date and Time Dimensions (separated) for 10 years of    data Date Dimension:  10 * 365 = 3650 Time Dimension (granularity:
  seconds): 24 * 60 * 60 = 86400
  Cardinality of Date and Time Dimensions (combined) for 10 years of    data DateTime Dimensions (granularity Hourly): 10 * 365 * 24 = 87600
  DateTime Dimensions (granularity Seconds): 10 * 365 * 24 * 60 * 60 =
  315,360,000

The more records in the DateTime dimension, the slower query performance will be.

  Issue #2 Having both date and time dimension in the same dimension can
  cause interpretation problems for fact tables that have granularity of
  a Day. It would be easy to inadvertently enter two records into the
  fact table for the same day, because of the extra granularity in the
  dimension table.

Find more : source


70-432 : SQL Server 2008 Implementation and Maintenance

You can learn more reading Guide To MCITP: SQL Server 2008 Administration, but the impression I got was that the 70-432 is an exam to test your basic skills and can be handled by even an "accidental DBA" with the right amount of studying and exploration in SQL Server 2008 Developer's Edition

  70-432 is the basic exam and covers a range of general SQL Server
  administration areas. 70-450 is more advanced and covers all the areas
  of 70-432, plus higher-level DBA tasks such as high availability and
  infrastructure design. Be aware that all questions are multiple-choice
  (more on this later).


Tab color for registered servers in Management Studio

After you have set up all of your registered servers the way you want them, you should be able to export the whole set under any server group by right-clicking the Local Server Groups node in View > Registered Servers and selecting Tasks > Export...

You can save individual servers or the whole lot. When you lose them, you can then import the file you backed up to reset them. I did not test (sorry, on the move) but this should save off all your servers, their credentials (which should be encrypted if present), and any personalized settings including custom colors.

(I suspect you are losing them because you are using a roaming profile or have some other AD/Group Policy perhaps resetting your profile.)

These settings are also stored in RegSrv.xml, which is going to be in a path something like:

  C:\Users\<you>\AppData\Roaming\Microsoft\Microsoft SQL Server\<version>\Tools\Shell\

You will see lines like this, which you can change on the fly. I believe they will require a restart of Management Studio though, and you'll have to map your desired RGB color with the int equivalent (there are many calculators available online). Carriage returns added by me:

<RegisteredServers:UseCustomConnectionColor type="boolean">
  true
</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">
  -16777216
</RegisteredServers:CustomConnectionColorArgb>

The point is more that you can back up that RegSvr.xml file to save all your settings somewhere, and restore them whenever this happens. But you should also look into why this file is getting reset or why your profile gets re-created (that may be a question your system administrator can answer even if you can't).

The custom connection settings you set in the Connect to Server dialog are stored separately; these are in a file called SqlStudio.bin, in the following path:

  C:\Users\<you>\AppData\Roaming\Microsoft\SQL Server Management Studio\<ver>

However, knowing where the file is will not really help you - the information about servers is largely not stored in a human readable format, and after connecting to a server with a custom color set, the change I saw when I performed a diff against the before and after files yielded nothing intelligible (and in fact I can't even confirm that that color change is saved there, except that when I open SSMS again it remembers it, like all the server credentials I've ever used). So:

You can't very easily hand-modify the SqlStudio.bin file to put your chosen colors back in there.
While it's possible you could back up your SqlStudio.bin file at a time when you've connected to your servers and SSMS currently remembers all of the settings, it would be missing anything else you've changed since you backed it up, so you might lose other things.

Therefore, I propose that it would just be much easier to just use registered servers, and stop manually setting a color when you connect, because with a moving profile you're constantly going to get SqlStudio.bin blown away (other things you can easily save off and import when that happens, e.g. fonts and colors).

With registered servers, you can keep a separate server group for the important servers where you want to be careful, and keep the other ones in a different group. And you can back them all up so it is easy to restore them on a different machine, when they get blown away, when your profile gets nuked again (or fixed), etc.


Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable

If you've no need to store the date component, then an interval day(0) to second is the most practical approach. The biggest advantage comes if you need to do any calculations finding how long has elapsed between two times as you don't need to do any extra processing out of the box.

For example:

create table times ( t1 interval day(0) to second, t2 interval day(0) to second);

insert into times values ( interval '0 09:30:00' day(0) to second, interval '0 12:30:00' day(0) to second);
insert into times values ( interval '0 10:30:00' day(0) to second, interval '0 13:45:00' day(0) to second);
insert into times values ( interval '0 11:30:00' day(0) to second, interval '0 12:05:01' day(0) to second);

commit;

SELECT * FROM times
where  t1 between interval '0 10:00:00' day to second and interval '0 11:00:00' day to second ;

T1          T2
----------- -----------
0 10:30:0   0 13:45:0

SELECT max(t2-t1) FROM times;

MAX(T2-T1)
-----------
0 3:15:0.0

You also have standard functions to extract the various components of the time (hour, minute, etc.):

SELECT extract(hour from t1) hr, extract(minute from t1) mn, extract(second from t1) sec
FROM times;

        HR         MN        SEC
---------- ---------- ----------
         9         30          0
        10         30          0
        11         30          0


Merge replication - Could not remove directory

The account under which xp_cmdshell is executing must have the proper access to the replication data share.

As a first step I would recommend check if xp_cmdshell is configured or not. If not, you have to enable for your purposes.

  -- To allow advanced options to be changed.
  EXEC sp_configure 'show advanced options', 1;
  GO
  -- To update the currently configured value for advanced options.
  RECONFIGURE;
  GO
  -- To enable the feature.
  EXEC sp_configure 'xp_cmdshell', 1;
  GO
  -- To update the currently configured value for this feature.
  RECONFIGURE;
  GO


select obj.PK_ObjectID, obj.name, obs.timestamp, obs.note -- etc
from   Objects as obj
join   Pts     as obs
on     obj.PK_ObjectID = FK_ObservationID
order by obj.PK_ObjectID, obs.FK_ObservationID

You're googling the wrong places, if I may say so.  You yourself say you're new to SQL, and yet you've figured out how to implement a cursor instead of writing an elementary JOIN.  You wrote 33 lines where 5 will do, and ensured your query will run 1% as fast as it normally would.

I hearby award you one gold star for asking!  Now get cracking with a good SQL book such as An Introduction to Database Systems (8th Edition), 2003, C.J. Date.


sqlite - Distinguish a column with the same value by range of id and count this range, take the higher one

For each lower boundary, search the matching upper boundary:

WITH uppers(rowid) AS (
  SELECT f.rowid FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid+1)
)
SELECT f.rowid AS lb,
       (SELECT u.rowid FROM uppers u
        WHERE u.rowid >= f.rowid
        ORDER BY u.rowid ASC LIMIT 1
       ) ub
FROM intersection f
WHERE cnt = (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
    SELECT * FROM intersection s
    WHERE s.cnt = f.cnt
    AND s.rowid = f.rowid-1);

lb  ub
--- ---
106 113
152 161

These values can then be used for BETWEEN:

WITH uppers(rowid) AS (
  SELECT f.rowid FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid+1)
),
bounds(lb, ub) AS (
  SELECT f.rowid,
         (SELECT u.rowid FROM uppers u
          WHERE u.rowid >= f.rowid
          ORDER BY u.rowid ASC LIMIT 1)
  FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid-1)
)
SELECT (SELECT count(*) FROM intersection
        WHERE rowid BETWEEN bounds.lb AND bounds.ub
       ) AS count,
       (SELECT avg(sweep) FROM intersection
        WHERE rowid BETWEEN bounds.lb AND bounds.ub
       ) AS avg_sweep
FROM bounds;


You can shrink an MDF file using:

USE [Databasename]
GO
DBCC SHRINKDATABASE(N'Databasename' )
GO

and you can shrink an LDF file using:

USE  Data base
GO
DBCC SHRINKFILE(databsename_Log, 1)
GO


Oracle PL-SQL "Regular expression" to replace each "(space)and(space)" with ',' in a string

select regexp_replace('x=y and g=h and u=1','([[:space:]]*)and([[:space:]]*)', ',')
from dual;

REGEXP_REPL
-----------
x=y,g=h,u=1


Local indexes vs Global indexes for partitioned tables in Oracle

…does partitioning alleviates slowness with inserts and updates or partitioning indexes is enough to guarantee a faster insert and update…Does it make sense to partition tables to speed up inserts?

No, absolutely not. The primary benefit of partitioning is the low cost of dropping partitions. You said "data can be archived or discarded a few weeks later", which might make you a genuine candidate for partitioning.

  Is it optimal to define local indexes on the unique id or is it better to define global indexes and partition them as I have already done?…What are the downsides of having global partitioned indexes for constantly growing data? Once I decide to remove the partitions at a later point of time, since the indexes are not partitioned by timestamp but instead by unique id, will the operation cause a direct impact on functioning indexes?

Using global indexes goes against the grain of partitioning in the first place — they are marked 'unusable' if you drop a partition.

You may find this ORACLE-BASE article a useful read.


What Happens to dirty pages if the system fails before the next checkpoint?

Both the log and data page entries are made in RAM and committed to
  disk later by a Checkpoint.

This statement is not completely true. It is correct that data pages are written to disk by Checkpoint (and Lazy Writer). Log records, however, are physically written to disk when the transaction is committed to guarantee transaction durability. A committed transaction data will never be only memory resident (barring delayed durability).

All data modifications are first written to the log (write-ahead logging) and dirty pages written afterward. Pages and log records may include both committed and uncommitted data on disk.

Regardless of the recovery model, SQL Server scans the log during crash recovery to the last checkpoint, rolls forward all data modifications from that point forward, and finally rolls back uncommitted transactions.


SQL Server Failed Login - SQL Server login (not windows) - Random times

I would look for some kind of automated process... either a SQL Agent job, a batch file connecting to SQL, etc.
There is some good chance that the login use for this process does not have the right password.

You can create an extended event (or a trace) and capture the "Error Reported". Make sure you include the "application name" in the collected field and you should see something like :

This should give you some good info to find the cause of your issue.


I have never tried but I am quite positive about editing index file. You can add filenames to index without restarting.

That said you actually don't need to. You can copy over the files to slave and play the sql.
Generate sql using mysqlbinlog utility.


Do I need separate indexes for each type of query, or will one multi-column index work?

You are right in that your example query would not use that index.

The query planner will consider using an index if:

all the fields contained in it are referenced in the query
some of the fields starting from the beginning are referenced

It will not be able to make use of indexes that start with a field not used by the query.

So for your example:

SELECT [id], [name], [customerId], [dateCreated]
   FROM Representatives WHERE customerId=1
   ORDER BY dateCreated

it would consider indexes such as:

[customerId]
[customerId], [dateCreated]
[customerId], [dateCreated], [name]

but not:

[name], [customerId], [dateCreated]

If it found both [customerId] and [customerId], [dateCreated], [name] its decision to prefer one over the other would depend on the index stats which depend on estimates of the balance of data in the fields. If [customerId], [dateCreated] were defined it should prefer that over the other two unless you give a specific index hint to the contrary.

It is not uncommon to see one index defined for every field in my experience either, though this is rarely optimal as the extra management needed to update the indexes on insert/update, and the extra space needed to store them, is wasted when half of them may never get used - but unless your DB sees write-heavy loads the performance is not going to stink badly even with the excess indexes.

Specific indexes for frequent queries that would otherwise be slow due to table or index scanning is generally a good idea, though don't overdo it as you could be exchanging one performance issue for another. If you do define [customerId], [dateCreated] as an index, for example, remember that the query planner will be able to use that for queries that would use an index on just [customerId] if present. While using just [customerId] would be slightly more efficient than using the compound index this may be mitigated by ending up having two indexes competing for space in RAM instead of one (though if your entire normal working set fits easily into RAM this extra memory competition may not be an issue).


It is fairly direct to push an already existing select to XML: http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1028612

Creating XML is part of the basic SQL specifications and is available on most servers. You can check out SQL server's version of it here: http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/


It is not in the default PATH, so you'll have to invoke it with its absolute path:

/usr/lib/postgresql/11/bin/pg_waldump <WAL file name>


You can use your actual query as a subquery and GROUP BY CodArt, Prezzo.

SELECT CodArt, Prezzo, SUM(Quantita) AS Quantita, SUM(Totale) AS Totale
FROM (
        SELECT
            Articolo.CodArt,
            Articolo.Prezzo,
            DistintaSemplice.Quantita,
            ROUND((DistintaSemplice.Quantita*Articolo.Prezzo),2) AS Totale
        FROM [Fatture].[dbo].[SottoCapitolo]
            INNER JOIN Fatture.dbo.Distinta ON Distinta.IdDistinta=SottoCapitolo.Codice
            INNER JOIN Fatture.dbo.DistintaSemplice ON DistintaSemplice.IdDistinta=Distinta.IdDistinta
            INNER JOIN Fatture.dbo.Articolo ON Articolo.IdArticolo=DistintaSemplice.IdArticolo
        WHERE SottoCapitolo.Tipo='Distinta'
    )
GROUP BY CodArt, Prezzo;

If you want to calculate Totale in function of SUM(Quantita):

;WITH Qty AS
(
    SELECT
        DistintaSemplice.IdArticolo,
        SUM(DistintaSemplice.Quantita) as Quantita,
    FROM [Fatture].[dbo].[SottoCapitolo]
        INNER JOIN Fatture.dbo.Distinta ON Distinta.IdDistinta=SottoCapitolo.Codice
        INNER JOIN Fatture.dbo.DistintaSemplice ON DistintaSemplice.IdDistinta=Distinta.IdDistinta
    WHERE SottoCapitolo.Tipo='Distinta'
)
SELECT
    Articolo.CodArt,
    Articolo.Prezzo,
    Qty.Quantita,
    ROUND((Qty.Quantita*Articolo.Prezzo),2) AS Totale
FROM Fatture.dbo.Articolo
    LEFT JOIN Qty ON Articolo.IdArticolo=Qty.IdArticolo
ORDER BY Articolo.CodArt;


how do I prevent my postgres connection from timing out? (python, psycopg2)

PostgreSQL doesn't drop idle connections. It must be something else, probably a misconfigured firewall.
To defend against that, set the parameter tcp_keepalives_idle on the server to something less than the default 2 hours.
There is also a corresponding parameter keepalives_idle on the client side that you could set when you connect with psycopg2.
Both should do the trick.


Stored procedure is running slow but it used to perform well

I'm curious about what the plans look like even performance is bad. Hopefully you can tune those plans, and then make sure the dynamic SQL produces the right queries for those conditions.

Some things you may want to look for though:

Make sure your view is set up to simplify out joins that are not needed. Watch http://bit.ly/Simplification to get a picture of how.

Consider using additional predicates that could be used by the QO if particular plan shapes are used. Watch http://bit.ly/Sargability at the section about inverted predicates. But also, consider things such as looking out for year(thedate) = @y so you can change it to thedate >= datefromparts(@y,1,1) and thedate < datefromparts(@y+1,1,1) instead (or as well, in case you have an indexed computed column).

Additional IS NOT NULL predicates can be useful for helping some joins to be simplified out.

Check that you're avoiding OR and IN where possible, or at least considering what the impact is. You may want to change someval in (1,2,4) (which is three seeks within one operator) to someval >= 1 and someval <= 4 and someval != 3 (one seek over a range) in some situations, but be wary of the impact of that on composite indexes. Read http://sqlperformance.com/2016/06/sql-plan/whats-actually-going-on-with-that-seek for more on that.

When a filter isn't used, make sure you're completely leaving it out rather than using catch-all predicate. You may find you're asking it to check values you really don't care about. Hopefully you're doing this already.


How to get goup concat of count value using sub query

If you use

GROUP BY model;

the result will be one line per each model.

You shall make a sub query (eliminating the group concat of month and monthname in your query) with this

GROUP by left(entrydate, 7), model;

and then group concat the month, monthname and count columns in another select.


Can I use result of one query inside other query in Cassandra

In CQL, you can't directly use the result of one query inside another, like you would in SQL.
You need to execute separate queries, and your application needs to handle the logic. If the result of your first query is very large, try to use paging.


Clustered columnstore indexes - Do I need to create a non clustered index on a column that's used in a join?

You should add a nonclustered columnstore index on the recommended columns. This will be compressed and should also provide a performance improvement.


Of the tasks that you've listed the creation and maintenance of the user accounts in production should be done by the DBAs.  The developers shouldn't have the passwords of the production accounts at all.

Designing the tables, indexes and performance testing should all be done by the developer.  The DBA should be handling the creation of the objects on the production server, backups, etc.  The DBA will find any additional indexes which are needed after the system goes into production and create them in production.


Merge Replication - Merge initial data contained on subscriber

You will need to manually merge the existing databases and initialize from that source.

One challenge you might face in manually merging the databases is conflicting primary keys.  If so, this is typically handled by adding a location-specific identifier column and extending the primary key to include this column.  This is known as partitioning your data.


What happens to the index of a primary key after a DROP CONSTRAINT?

Disclaimer
This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.
For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):
pg_index.indisprimary:
For PRIMARY KEY constraint  ... TRUE
For UNIQUE constraint       ... FALSE
pg_constraint.contype:
PRIMARY KEY constraint  ... 'p'
UNIQUE constraint       ... 'u'
You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:
UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:
UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;


Migrate on-premise SQL Server databases to Azure SQL Database

I would recommend you to migrate first only the schema of your on-premises databases to Azure SQL Databases and then let Azure SQL Data Sync to migrate the data to Azure and keep it updated on Azure SQL Database.

My suggestion to start with an empty schema on the Azure SQL Database side is because when SQL data Sync finds data on-premises and on Azure it start comparing both databases and that consumes a lot of resources.

On the initial sync SQL Data Sync may consume a lot of resources on the on-premises database server even when having an empty schema on the Azure side, for that you can use SQL Server Resource Governor to cap the CPU used by the data sync sessions in your on premises SQL Server, and this way avoid big performance impact possibly affecting database users.

When you are ready, you can switch your users (gradually or not if SQL Data Sync is on bi-directional mode) to Azure. Once your users have been migrated, you can then remove the member database (the on-premises database) from the SQL Data Sync configuration and stop SQL Data Sync operation.


I'll work from the assumption that all of the tables listed in the query above are external tables pointing to the same external data source.  With that in mind, here are a few considerations.
Distributed Requests
There are two DMVs which will provide a bit more information than what you have currently:  sys.dm_exec_distributed_requests and sys.dm_exec_distributed_request_steps.  Try running your slow query and see what shows up in the distributed requests DMV.  Here is a sample query I like to use for this purpose:
SELECT TOP(100)
    r.execution_id,
    r.status,
    r.error_id,
    r.start_time,
    r.end_time,
    r.total_elapsed_time,
    t.text
FROM sys.dm_exec_distributed_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY
    r.end_time DESC;
GO

You might end up seeing multiple results like so:

For each one of these, you can get the set of steps involved (changing the query to use whatever your execution IDs are):
SELECT
    rs.execution_id,
    rs.step_index,
    rs.operation_type,
    rs.distribution_type,
    rs.location_type,
    rs.[status],
    rs.error_id,
    rs.start_time,
    rs.end_time,
    rs.total_elapsed_time,
    rs.row_count,
    rs.command
FROM sys.dm_exec_distributed_request_steps rs
WHERE rs.execution_id IN ('QID573', 'QID574')
ORDER BY
    rs.execution_id DESC,
    rs.step_index ASC;
GO

What I tend to look for is "excessive" row counts.  For example, if I'm expecting a small number of rows back but the row count is much higher for a given step, then the PolyBase data movement service is sending over many more rows than I'd ideally like and forcing the PolyBase engine to do the dirty work of combining tables together.  That leads to the next consideration.
Filters and Predicates
In your query, I don't see any explicit filters or predicates, but I wonder if there are implicit filters.  For example, in the StocktypeTerms table, I see ParentHeaderKey = 3 in the join criteria.  If this is a highly selective join criterion, it is likely that PolyBase is streaming all of the rows over and then performing the filters on the local side rather than performing the filter operation remotely and pulling in just the rows it needs.
There are several reasons why this might be the case, including complex filters (which this might be), a predicate which PolyBase cannot push down (there are some stringent limitations on what is available for pushdown), or a predicate formed from two different external data sources (which is a scenario I'd love to see but does not work well today).  Given the lack of a WHERE clause, I won't dive into more detail on this section.
Network Performance
If you have network issues between the external data source and your local SQL server instance, that can result in slowdowns.  In your second update, you mention creating an external table against a single table and streaming all of its data down, and the timing differences being minor.  This is an indication that network speed is not a significant issue in your case.
Specific Guidance
With the above in mind, here's what I would recommend for your specific scenario as a way of figuring out what might be the issue.
If you see that more rows are coming to the local SQL Server instance than the 313,585 indicated in your execution plan, that means there is extra work happening on your local SQL Server instance which could be handled remotely.  There are two things which might help.
First, try to run your query with OPTION(FORCE EXTERNALPUSHDOWN) on.  You mentioned enabling and disabling predicate pushdown (by setting PUSHDOWN = ON and PUSHDOWN = OFF on the external data source definition), but it wasn't clear if you specified this hint.  There is a non-zero chance that this will trigger the PolyBase engine to behave differently than what you see simply by writing the query.  Given your specific query, my guess is that this query hint will not make a difference.
Second, create a view with the above SQL query on your remote data source.  Then, create an external table locally that references that remote view.  What this does is force the external data source to do the work of joining together all of these tables before sending anything to the local SQL Server.
In my simple case, creating a view for my query resulted in fewer distributed requests and less time retrieving data:


Why does a subquery reduce the row estimate to 1?

This definitely seems like unintended behavior. It is true that cardinality estimates do not need to be consistent at each step of a plan but this is a relatively simple query plan and the final cardinality estimate is inconsistent with what the query is doing. Such a low cardinality estimate could result in poor choices for join types and access methods for other tables downstream in a more complicated plan.

Through trial and error we can come up with a few similar queries for which the issue does not appear:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT -1)
  END AS ID2
FROM dbo.X_HEAP;

SELECT
  ID
, CASE
    WHEN ID < 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    WHEN ID >= 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END AS ID2
FROM dbo.X_HEAP;

We can also come up with more queries for which the issue appears:

SELECT
  ID
, CASE
    WHEN ID < 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    WHEN ID >= 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE)
  END AS ID2
FROM dbo.X_HEAP;

SELECT
  ID
, CASE
    WHEN ID = 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT -1)
  END AS ID2
FROM dbo.X_HEAP;

SELECT
  ID
, CASE
    WHEN ID = 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END AS ID2
FROM dbo.X_HEAP;

There appears to be a pattern: if there is an expression within the CASE that is not expected to be executed and the result expression is a subquery against a table then the row estimate falls to 1 after that expression.

If I write the query against a table with a clustered index the rules change somewhat. We can use the same data:

CREATE TABLE dbo.X_CI (ID INT NOT NULL, PRIMARY KEY (ID))

INSERT INTO dbo.X_CI WITH (TABLOCK)
SELECT * FROM dbo.X_HEAP;

UPDATE STATISTICS X_CI WITH FULLSCAN;

This query has a 1000 row final estimate:

SELECT
  ID
, CASE
    WHEN ID = 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
  END
FROM dbo.X_CI;

But this query has a 1 row final estimate:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END
FROM dbo.X_CI;

To dig into this further we can use the undocumented trace flag 2363 to get information about how the query optimizer performed selectivity calculations. I found it helpful to pair that trace flag with the undocumented trace flag 8606. TF 2363 seems to give selectivity computations for both the simplified tree and the tree after project normalization. Having both trace flags enabled makes it clear which calculations apply to which tree.

Let's try it for the original query posted in the question:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
  END AS ID2
FROM X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

Here is part of the part of output which I think is relevant along with some comments:

Plan for computation:

  CSelCalcColumnInInterval -- this is the type of calculator used

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID -- this is the column used for the calculation

Pass-through selectivity: 0 -- all rows are expected to have a true value for the case expression

Stats collection generated:

  CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter) -- the row estimate after the join will still be 1000

      CStCollBaseTable(ID=1, CARD=1000 TBL: X_HEAP)

      CStCollBaseTable(ID=2, CARD=1 TBL: X_OTHER_TABLE)

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1 -- no rows are expected to have a true value for the case expression

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1 x_jtLeftOuter) -- the row estimate after the join will still be 1

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter) -- here is the row estimate after the previous join

          CStCollBaseTable(ID=1, CARD=1000 TBL: X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: X_OTHER_TABLE_2)

Now let's try it for a similar query that doesn't have the issue. I'm going to use this one:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT -1)
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

Debug output at the very end:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollConstTable(ID=4, CARD=1) -- this is different than before because we select a constant instead of from a table

Let's try another query for which the bad row estimate is present:

SELECT
  ID
, CASE
    WHEN ID < 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    WHEN ID >= 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE)
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

At the very end the cardinality estimate drops to 1 row, again after Pass-through selectivity = 1. The cardinality estimate is preserved after a selectivity of 0.501 and 0.499.

Plan for computation:

 CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.501

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.499

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1

Stats collection generated:

  CStCollOuterJoin(ID=12, CARD=1 x_jtLeftOuter) -- this is associated with the ELSE expression

      CStCollOuterJoin(ID=11, CARD=1000 x_jtLeftOuter)

          CStCollOuterJoin(ID=10, CARD=1000 x_jtLeftOuter)

              CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

              CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

          CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

      CStCollBaseTable(ID=4, CARD=1 TBL: X_OTHER_TABLE)

Let's again switch to another similiar query that does not have the issue. I'm going to use this one:

SELECT
  ID
, CASE
    WHEN ID < 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    WHEN ID >= 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

In the debug output there is never a step which has a pass-through selectivity of 1. The cardinality estimate stays at 1000 rows.

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.499

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

End selectivity computation

What about the query when it involves a table with a clustered index? Consider the following query with the row estimate issue:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END
FROM dbo.X_CI
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

The end of the debug output is similar to what we already have seen:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_CI].ID

Pass-through selectivity: 1

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

However, the query against the CI without the issue has different output. Using this query:

SELECT
  ID
, CASE
    WHEN ID = 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
  END
FROM dbo.X_CI
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

Results in different calculators being used. CSelCalcColumnInInterval no longer appears:

Plan for computation:

  CSelCalcFixedFilter (0.559)

Pass-through selectivity: 0.559

Stats collection generated:

  CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

      CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

      CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

...

Plan for computation:

  CSelCalcUniqueKeyFilter

Pass-through selectivity: 0.001

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE)

In conclusion, we appear to get a bad row estimate after the subquery under the following conditions:

The CSelCalcColumnInInterval selectivity calculator is used. I don't know exactly when this is used but it seems to show up much more often when the base table is a heap.
Pass-through selectivity = 1. In other words, one of the CASE expressions is expected to evaluated to false for all of the rows. It does not matter if the first CASE expression evaluates to true for all rows.
There is an outer join to CStCollBaseTable. In other words, the CASE result expression is a subquery against a table. A constant value will not work.

Perhaps under those conditions the query optimizer is unintentionally applying the pass-through selectivity to the row estimate of the outer table instead of to the work done on the inner part of the nested loop. That would reduce the row estimate to 1.

I was able to find two workarounds. I was not able to reproduce the issue when using APPLY instead of a subquery. The output of trace flag 2363 was very different with APPLY. Here's one way to rewrite the original query in the question:

SELECT
  h.ID
, a.ID2
FROM X_HEAP h
OUTER APPLY
(
SELECT CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
  END
) a(ID2);

The legacy CE appears to avoid the issue as well.

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
  END AS ID2
FROM X_HEAP
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

A connect item was submitted for this issue (with some of the details that Paul White provided in his answer).


SQL Server 2012 - Common Table Expression (CTE) returning more rows than it should

Your problem is the anchor in the CTE returns all 120 rows instead of a single anchor row. Try this code instead, notice the WHERE clause in the CTE SELECT:

DECLARE @gender VARCHAR(10) = 'MALE'
DECLARE @interestRate NUMERIC(7, 5) = 0.0354

;WITH cte AS (
    SELECT
        lt.Age,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Male
            ELSE
                lt.Female
        END AS qx,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Female
            ELSE
                lt.Male
        END AS qy,
        CAST(1.0 AS NUMERIC(7, 5)) AS vx
    FROM LifeTable AS lt
    WHERE Age = 1
    UNION ALL

    SELECT
        cte.Age + 1,
        cte.qx,
        cte.qy,
        CAST((vx / (1 + @interestRate)) AS NUMERIC(7, 5)) AS vx
    FROM cte

    WHERE cte.Age < 120

) SELECT COUNT(*) AS NumberOfRows FROM cte OPTION (MAXRECURSION 0);


IF NOT NULL then UPDATE else keep the value of the field

I think this will solve the issue:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN COALESCE(@Accessories, Details)
          WHEN 'Description'   THEN COALESCE(@Description, Details)
          WHEN 'Specification' THEN COALESCE(@Specification, Details)
          ELSE Details
              END
WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN @Accessories
          WHEN 'Description'   THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
              END
WHERE DeviceID = 10
  AND ( Name = 'Accessories'   AND @Accessories IS NOT NULL
     OR Name = 'Description'   AND @Description IS NOT NULL
     OR Name = 'Specification' AND @Specification IS NOT NULL
      ) ;

or this, using a table value constructor:

UPDATE da
SET da.Details = upd.Details
FROM DeviceAttribute AS da
  JOIN
    ( VALUES
      ('Accessories'   , @Accessories),
      ('Description'   , @Description),
      ('Specification' , @Specification)
    ) AS upd (Name, Details)
    ON upd.Name = da.Name
WHERE da.DeviceID = 10
  AND upd.Details IS NOT NULL ;


Try:

SELECT *
FROM yourtable
ORDER BY chargeTime DESC
LIMIT 3


Database design for conditional detail table

Without knowing more about the business requirements, with the given structure you defined I would probably go with something like this:

Expense (Id, DetId, TypeId, Amount, Date, EmployeeId)
ExpenseType (Id, Name)

Where Expense.TypeId is a foreign key constraint to ExpenseType.Id.  ExpenseType could contain rows for daily or monthly expenses.  This is scalable, as if down the road you have the requirement for weekly expenses, or yearly expenses (etc.) then this normalization will benefit.  Expense.EmployeeId would be a foreign key constraint to your employee table, and Expense.DetId sounds like it'd be a foreign key constraint to another table that I can't seem to translate from the column name.  If it is just details for each expense, no reason not to just put it directly in the Expense table (provided a 1:1 relationship between expense and expense details).  I may be missing a few columns, but I'm sure you get the idea.


How could a "SECURITY DEFINER" function in PG be insecure with an improper search_path?

If pg_temp is not present in search_path at all, then the temp scheme is always searched first, i.e. as if it were implicitly prepended to search_path.

The only way to prevent it from being searched first is to give it an explicit presence in search_path.


Why do uncommitted transactions rollback instead of redo after a crash in ARIES?

Community wiki answer - please edit to add your contribution:

Generally speaking, there is no guarantee that an uncommitted transaction had actually completed everything it needed to do.

A transaction that withdrew $100 from BankA, but has not deposited the money in BankB is an uncommitted transaction.  If a crash occurred between those two processes, there is no way to tell what to redo because we didn't even get to the deposit part of the transaction which could also involve other calculations and updates.

An external application begins a transaction in code and runs several updates, inserts and deletes within that transaction.  Somewhere in the middle of that transaction, the server crashes.  Now, even if the database could replay and harden all of the updates that had happened up until the crash, how would the application know where to restart in its logic?

In a complete undo, you simply start the external application back at the beginning. The absolute safest way to deal with uncommitted transactions during crash recovery is to roll it back as if it had never occurred.

Atomicity - Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.

In a properly-implemented ARIES - Crash recovery will start from a checkpoint (stored in master record), then it will need to figure out which transactions committed since checkpoint and which failed , then redo all actions and then UNDO effects of failed Transactions. See the answer to What happens when a DBMS crash during the recovery phase of ARIES algorithms? by Remus Rusanu.

Also, remember that during crash "the contents stored in memory (RAM) are assumed to be corrupted or lost" so they have to be rolled back. Only the ones that are written to disk are valid / stable.


How do I automatically grant all privileges to a schema created by a user in mysql?

To grant all privs on database mydb to username

GRANT ALL PRIVILEGES ON mydb.* TO username;

To grant privs on a table mydb.mytable to username

GRANT ALL PRIVILEGES ON mydb.mytable TO username;

I suggest you drop the user first to clear up any server-wide privileges.

DROP USER username;
CREATE USER username;
GRANT ALL PRIVILEGES ON mydb.* TO username;

Why drop the user first? When you first create a user called username and login, username has no privs. Just run SHOW GRANTS; to see it. It should say something like GRANT USAGE ON *.* TO username. At that moment, username can only login. That's your chance to create privileges.

If you run

GRANT ALL PRIVILEGES ON *.* TO username;

username will have global database privileges (all databases)

If you run

GRANT ALL PRIVILEGES ON mydb.* TO username;

username will have database-wide privileges on the mydb database only.

If you run

GRANT ALL PRIVILEGES ON mydb.mytable TO username;

username will have table privileges on the mydb.mytable only.

You cannot mix them. If you do you accidently slip username privileges that username should not have.

If you want to see what privileges exist at the three levels run the the following queries

SELECT table_name,column_name
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv'
ORDER BY table_name,column_name;

Here are privs for MySQL 5.5

mysql> SELECT table_name,column_name
    -> FROM information_schema.columns
    -> WHERE table_schema='mysql'
    -> AND table_name IN ('user','db','tables_priv')
    -> AND column_name LIKE '%\_priv'
    -> ORDER BY table_name,column_name;
+-------------+------------------------+
| table_name  | column_name            |
+-------------+------------------------+
| db          | Alter_priv             |
| db          | Alter_routine_priv     |
| db          | Create_priv            |
| db          | Create_routine_priv    |
| db          | Create_tmp_table_priv  |
| db          | Create_view_priv       |
| db          | Delete_priv            |
| db          | Drop_priv              |
| db          | Event_priv             |
| db          | Execute_priv           |
| db          | Grant_priv             |
| db          | Index_priv             |
| db          | Insert_priv            |
| db          | Lock_tables_priv       |
| db          | References_priv        |
| db          | Select_priv            |
| db          | Show_view_priv         |
| db          | Trigger_priv           |
| db          | Update_priv            |
| tables_priv | Column_priv            |
| tables_priv | Table_priv             |
| user        | Alter_priv             |
| user        | Alter_routine_priv     |
| user        | Create_priv            |
| user        | Create_routine_priv    |
| user        | Create_tablespace_priv |
| user        | Create_tmp_table_priv  |
| user        | Create_user_priv       |
| user        | Create_view_priv       |
| user        | Delete_priv            |
| user        | Drop_priv              |
| user        | Event_priv             |
| user        | Execute_priv           |
| user        | File_priv              |
| user        | Grant_priv             |
| user        | Index_priv             |
| user        | Insert_priv            |
| user        | Lock_tables_priv       |
| user        | Process_priv           |
| user        | References_priv        |
| user        | Reload_priv            |
| user        | Repl_client_priv       |
| user        | Repl_slave_priv        |
| user        | Select_priv            |
| user        | Show_db_priv           |
| user        | Show_view_priv         |
| user        | Shutdown_priv          |
| user        | Super_priv             |
| user        | Trigger_priv           |
| user        | Update_priv            |
+-------------+------------------------+
50 rows in set (0.01 sec)

mysql>

Run the query to see the privs for 5.1 and 5.0


The SELECT permission was denied on the object 'configurations', database 'mssqlsystemresource', schema 'sys' (Error 229)

The answer was in fact the logins being in a AD group account also which I didn't see that they were members of and someone had selected all permissions on the master database so obviously selecting denydbreader/denydbwriter.

My fault for missing this group out from my checks!


Clustered Index 'Seek predicate' and 'predicate' on the same column

Starting with your original query:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower+1 and @upper;

The 1 that you added has a data type of integer by default. When adding an integer value to a numeric(18,0) value SQL Server applies the rules of data type precedence. int has a lower precedence so it gets converted to a numeric(1,0). Your query is equivalent to the following:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;

A different set of rules around Precision, scale, and Length is applied to determine the data type of the expression involving @lower. It isn't safe to just use NUMERIC(18,0) because that could be overflowed (consider 999,999,999,999,999,999 and 1 as an example). The rule that applies here is:

╔═══════════╦═════════════════════════════════════╦════════════════╗
║ Operation ║          Result precision           ║ Result scale * ║
╠═══════════╬═════════════════════════════════════╬════════════════╣
║ e1 + e2   ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2)    ║
╚═══════════╩═════════════════════════════════════╩════════════════╝

For your expression, the resulting precision is:

max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19

and the resulting scale is 0. You can verify this by running the following code in SQL Server:

declare
@lower numeric(18,0) = 1000,
@upper numeric(18,0) = 1005;

SELECT
  SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
, SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
, SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;

This means that your original query is equivalent to the following:

declare
    @lower numeric(19,0) = 1000 + 1,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower and @upper;

SQL Server can only use @lower to do a clustered index seek if the value can be implicitly converted to NUMERIC(18, 0). It is not safe to convert a NUMERIC(19,0) value to NUMERIC(18,0). As a result the value is applied as a predicate instead of as a seek predicate. One workaround is to do the following:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;

That query can process both filters as seek predicates:

My advice is to change the data type in the table to BIGINT if possible. BIGINT requires one fewer byte than NUMERIC(18,0) and benefits from performance optimizations not available to NUMERIC(18,0) including better support for bitmap filters.


How do I get the total number of databases that are active?

In singletenant (non-CDB, old) architecture: one instance - one database. In multitenant architecture (CDB): one instance - multiple databases.

So first of of you have to know the relationship between instance and database. Then you can identify how many instances do you have on your server (how, it depends on your operating system) and then you have to identify what architecture do you have (non-CDB, CDB).

Than you can make a list of all databases. Take a look at https://stackoverflow.com/questions/36957919/how-to-list-down-the-list-of-pdb-and-cdb-in-oracle-12c

Edit:
As mentioned by jmk, you can meet also Oracle RAC.


Migrating from Postgres 8.4 to 9.4 in different data centers

You can set an encoding that is different from the cluster-wide one the following way:

CREATE DATABASE important WITH ENCODING 'LATIN1' TEMPLATE template0;

but the documentation further says:

  Notice that the above commands specify copying the template0 database. When copying any other database, the encoding and locale settings cannot be changed from those of the source database, because that might result in corrupt data.

All this can be done when creating the database, there is no ALTER DATABASE syntax for changing it later.

Setting client_encoding on the server might be tempting, but that you cannot do for a single DB only, it's all or none.  A slightly ugly idea could be to set this parameter to 'UTF8' upon connection, before any data is accessed.


how to prevent an oracle block corruption from duplicating a database with a few nologging tables

The reason for the databa block corruption is that ther was data inserted in the  table with nologging in the timespan between the begin of the backup and '01-JUN-2015 02:00:00'. So the information needed to fully restore the datafiles (by applying the archive log) is not contained in the archives log. Mainly there are two possibilities for you:

1) you try to avoid corrupt blocks
 2) you repair the tables

1) To avoid corrupt blocks you can
1.1) schedule you duplicate so, that there is no nologging operation in the critical time interval
1.2) you can change the nologging operation to an operation with loggin
1.3) reduce the size of the critical time interval by doing an incremental backup before the until-point-of-time, e.g. '01-JUN-2015 02:00:00'. (but this has to be done on the production database)

2) you can truncate the affected tablels after the duplicate. If you don't need this tables on the your duplicate then you are done. Otherwise you have to fill them with the correct data , e.g. by loading the table with the same data as the production table. Of course this kind of repairing a table depends of the special situation.


Unable to see list of tables in Oracle SQL Developer

For where you are looking, you can only see the tables that exist in the schema that belongs to the user you supplied in your connection details.

If you want to see 'the tables' - you need to expand the 'Other Users' node, and explore the users there, and go to their respective tables list.

If you can query these objects in a worksheet w/o adding a SCHEMA. prefix to the FROM clause, it's because there is a SYNONYM that allows you to do so.

You can configure SQLDev to show these SYNONYMS as tables in your tree.

I talk about how to do this here.


If you are using Developer or Enterprise Edition, you can use Database Snapshots to get you most of the way.  Basically, this allows you to make virtual "copies" of your database and apply your object changes.  You will still need to change your connection string, but it could give you an easy to maintain alternative for standing up your alternate database branches.

Regardless of how you approach this, you will need to have some different database name and/or instance name for your database.  Data and databases are stateful in nature, so simply swapping structures in and out isn't an option.  After all, you can't just turn off columns and the data they contain.  You have some options for disabling objects, like indexes and triggers.  But beyond that, you can't just switch data "off".


That's how joins work!  Don't select the wall posts in the same query if you just want the comments.


Upgrade older SQL 2008 to 2008 R2 on active/passive cluster

It shouldn't be that complex.

On the passive node upgrade the instance to SQL 2008 R2.  Then reboot that node.  Then failover.  At this point the database will be upgraded to SQL 2008 R2.

On the now passive node upgrade the instance to SQL 2008 R2.

There's an article I wrote about upgrading a SQL 2005 cluster to SQL 2008.  It's basically the same process.


Levenshtein vs MATCH vs Others for best MySQL string match

A FULLTEXT index is very efficient for small resultsets in a huge table.  Using levenshtein involves checking each row.  So, I agree with your approach to do FT as a first step.

I would suggest

Remove short words, etc, from the string.  (not necessary for MyISAM since it ignores them; necessary for InnoDB, else it returns nothing)
IN BOOLEAN MODE but without any "+" on the words;
ORDER BY MATCH... DESC and add LIMIT.  This keeps edge cases from coming up with thousands of rows for the next step.
Check levenshtein distance.

Keep in mind that the end result will be less than perfect, but at least it should be "fast enough".


PostgreSQL : cannot connect with a created role

I found where the problem was! I had changed the line

password_encryption = scram-sha-256

in /var/lib/pgsql/data/postgresql.conf

but had forgotten to uncomment it…


Knowing if Oracle account has been locked?

If you don't have admin rights you probably won't be able to troubleshoot the issue without your DBAs.
Some hints for troubleshooting (again, with your DBAs)

If the user is locked after some time (and not multiple times a day) the PASSWORD_LIFE_TIME in the (default) profile for your user might be the culprit.
If the user is locked randomly and you don't have something fancy like auditing enabled, you can look at your listener.log and check if there are connections from hosts you  aren't aware of. Often some web service or workstation with old credentials caused user locking at my place.

If you don't have any access to the server's log files and/or the admin views this issue is hard to resolve. Team up with your DBAs


Show create view 'viewName'

returns the SQL definition with create view statement while

SELECT  VIEW_DEFINITION
    FROM    INFORMATION_SCHEMA.VIEWS
    WHERE   TABLE_SCHEMA    = 'DATABASENAME'
    AND     TABLE_NAME      = 'VIEWNAME';

returns only definition.

Both are correct for getting view definition.


MySQL Client out of memory when running a simple select query from MSSQL ODBC

The comparison of datetime with a string may cause odbc to retrieve the entire table before eliminating rows.  Either compare with a datetime constant or set the prefetch parameter in the odbc config to a reasonable value.


Update a column for every row of a large table to a unique value in a set range

Four hours is only 14,400 seconds and you have 3M rows.
If we generate random timestamp with 1 second precision within 4 hour interval we'll get approximately 3,000,000 / 14,400 ~= 208 rows for each second.
To reduce the number of rows that have exactly the same timestamp we should use fractions of the second.
If you use datetime type, its precision is about 3 milliseconds, which should be good enough.

So, for each row of the table I'll generate a random timestamp within a 4 hour interval with a millisecond precision,
which will be rounded to the precision of the datetime type.

I will use CRYPT_GEN_RANDOM function for it.

For example,

(CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5)

This generates 4 random bytes as varbinary. We have to explicitly cast them to int first. Then result is transformed into a float number between 0 and 1.
This can be miltiplied by the size of the window in milliseconds and added to the timestamp that defines the start of the window.

Sample data

DECLARE @T TABLE
(ID int, Supplier nvarchar(50), ProductCode char(5), LastUpdated datetime);

INSERT INTO @T (ID, Supplier, ProductCode, LastUpdated) VALUES
(1, 'Acme',  '00001', '2016-03-23 06:00:00'),
(2, 'Acme',  '00002', '2016-03-23 06:00:00'),
(3, 'XYZ',   '00023', '2016-03-23 05:30:00'),
(4, 'XYZ',   '00055', '2016-03-23 05:30:00'),
(5, 'Q & B', '00453', '2016-03-23 04:15:00'),
(6, 'Q & B', '00045', '2016-03-23 04:15:00');

SELECT * FROM @T ORDER BY ID;

+----+----------+-------------+-------------------------+
| ID | Supplier | ProductCode |       LastUpdated       |
+----+----------+-------------+-------------------------+
|  1 | Acme     |       00001 | 2016-03-23 06:00:00.000 |
|  2 | Acme     |       00002 | 2016-03-23 06:00:00.000 |
|  3 | XYZ      |       00023 | 2016-03-23 05:30:00.000 |
|  4 | XYZ      |       00055 | 2016-03-23 05:30:00.000 |
|  5 | Q & B    |       00453 | 2016-03-23 04:15:00.000 |
|  6 | Q & B    |       00045 | 2016-03-23 04:15:00.000 |
+----+----------+-------------+-------------------------+

Query

DECLARE @VarWindowStart datetime = '2016-03-23 06:00:00';
DECLARE @VarWindowSizeMilliseconds int = 4 * 3600 * 1000;

UPDATE @T
SET LastUpdated =
    DATEADD(millisecond,
    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * @VarWindowSizeMilliseconds,
    @VarWindowStart);

SELECT *
FROM @T
ORDER BY LastUpdated;

Result

+----+----------+-------------+-------------------------+
| ID | Supplier | ProductCode |       LastUpdated       |
+----+----------+-------------+-------------------------+
|  2 | Acme     |       00002 | 2016-03-23 06:02:57.260 |
|  6 | Q & B    |       00045 | 2016-03-23 06:48:07.203 |
|  3 | XYZ      |       00023 | 2016-03-23 06:52:38.813 |
|  1 | Acme     |       00001 | 2016-03-23 07:20:16.750 |
|  4 | XYZ      |       00055 | 2016-03-23 07:23:21.140 |
|  5 | Q & B    |       00453 | 2016-03-23 08:59:53.303 |
+----+----------+-------------+-------------------------+

I'm pretty sure it will take less than 15 hours to process 3M rows using this query.

To clarify, this method doesn't guarantee that generated values in LastUpdated will be unique. There is a pretty good chance that for 3M rows there will be few duplicates and I'm too lazy now to calculate the probability, but I think that for your purposes the result will be good enough.

You can use datetime2(7) with a microsecond precision, which would substantially reduce the probability of collisions. There still will be a chance of collision with this method. If this is not acceptable, you should use some other method.

Update

I will try to guess what you mean by

  We must also ensure that the suppliers are spread out evenly, so
  suppliers' products are not bunched together

The query above generates random number for each row independently. As a result, if your table has, for example, supplier A with 100 rows and supplier B with 10 rows, you would get ~10 rows from A, then 1 row from B, then another ~10 rows from A, then 1 row from B, etc (with various random fluctuations, of course).

This result will look like rows from A are clustered together at the top (and at the bottom, which is not so important).

If you want to guarantee that when table has N suppliers, the first N rows are all from N different suppliers, the next N rows are again from N different suppliers, etc., then below is one way to achieve it.

In the example with suppliers A and B you may want to have at first 2 random rows from A & B, then another 2 random rows from A & B, and so on for 10 pairs of rows, then the rest 90 rows from A in random order. This result will look thoroughly shuffled at the top between suppliers, while it will have only supplier A at the bottom.

I'll extend your sample data slightly.

DECLARE @T TABLE
(ID int, Supplier nvarchar(50), ProductCode char(5), LastUpdated datetime);

INSERT INTO @T (ID, Supplier, ProductCode, LastUpdated) VALUES
(1, 'Acme',  '00001', '2016-03-23 06:00:00'),
(2, 'Acme',  '00002', '2016-03-23 06:00:00'),
(3, 'XYZ',   '00023', '2016-03-23 05:30:00'),
(4, 'XYZ',   '00055', '2016-03-23 05:30:00'),
(5, 'Q & B', '00453', '2016-03-23 04:15:00'),
(6, 'Q & B', '00045', '2016-03-23 04:15:00'),
(10, 'ABC',   '00010', '2016-03-23 06:30:00'),
(11, 'ABC',   '00011', '2016-03-23 06:30:00'),
(12, 'ABC',   '00012', '2016-03-23 06:30:00'),
(13, 'ABC',   '00013', '2016-03-23 06:30:00'),
(20, 'QWE',   '00020', '2016-03-23 02:30:00'),
(21, 'QWE',   '00021', '2016-03-23 02:30:00'),
(22, 'QWE',   '00022', '2016-03-23 02:30:00'),
(23, 'QWE',   '00023', '2016-03-23 02:30:00'),
(24, 'QWE',   '00024', '2016-03-23 02:30:00'),
(25, 'QWE',   '00025', '2016-03-23 02:30:00'),
(26, 'QWE',   '00026', '2016-03-23 02:30:00');

Query

WITH
CTE_Rnd
AS
(
    SELECT
        ID
        ,Supplier
        ,ProductCode
        ,LastUpdated
        ,CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5 AS RandomNo
    FROM @T
)
SELECT
    ID
    ,Supplier
    ,ProductCode AS Code
    ,LastUpdated
    ,RandomNo
    ,ROW_NUMBER() OVER (PARTITION BY Supplier ORDER BY RandomNo) AS RowNo
FROM CTE_Rnd
ORDER BY RowNo, RandomNo
;

Here we assign a random number for each row and partition all rows by supplier, so that we get one random row from each supplier first, then another random row from each supplier again, and so on.

Result

+----+----------+-------+-------------------------+----------------+-------+
| ID | Supplier | Code  |       LastUpdated       |    RandomNo    | RowNo |
+----+----------+-------+-------------------------+----------------+-------+
|  5 | Q & B    | 00453 | 2016-03-23 04:15:00.000 | 0.027009220917 |     1 |
| 10 | ABC      | 00010 | 2016-03-23 06:30:00.000 | 0.091210213162 |     1 |
| 24 | QWE      | 00024 | 2016-03-23 02:30:00.000 | 0.128781275971 |     1 |
|  3 | XYZ      | 00023 | 2016-03-23 05:30:00.000 | 0.248359622609 |     1 |
|  1 | Acme     | 00001 | 2016-03-23 06:00:00.000 | 0.347667260759 |     1 |
| 13 | ABC      | 00013 | 2016-03-23 06:30:00.000 | 0.154523770012 |     2 |
| 20 | QWE      | 00020 | 2016-03-23 02:30:00.000 | 0.211252812229 |     2 |
|  2 | Acme     | 00002 | 2016-03-23 06:00:00.000 | 0.580557575235 |     2 |
|  6 | Q & B    | 00045 | 2016-03-23 04:15:00.000 | 0.921966472273 |     2 |
|  4 | XYZ      | 00055 | 2016-03-23 05:30:00.000 | 0.952229986538 |     2 |
| 11 | ABC      | 00011 | 2016-03-23 06:30:00.000 | 0.425760054944 |     3 |
| 23 | QWE      | 00023 | 2016-03-23 02:30:00.000 | 0.455146492682 |     3 |
| 21 | QWE      | 00021 | 2016-03-23 02:30:00.000 | 0.485142913877 |     4 |
| 12 | ABC      | 00012 | 2016-03-23 06:30:00.000 | 0.554860722984 |     4 |
| 25 | QWE      | 00025 | 2016-03-23 02:30:00.000 | 0.668510247526 |     5 |
| 22 | QWE      | 00022 | 2016-03-23 02:30:00.000 | 0.815049941724 |     6 |
| 26 | QWE      | 00026 | 2016-03-23 02:30:00.000 | 0.818780491668 |     7 |
+----+----------+-------+-------------------------+----------------+-------+

As you can see top 5 rows are all from different suppliers in random order. Next 5 rows are again from different suppliers in random order. Then we have only supliers ABC and QWE left, and finally only supplier QWE.

I will leave it to you to figure out how to combine two numbers from RowNo and RandomNo columns into one datetime value in LastUpdated. RowNo could become number of minutes and RandomNo could become seconds plus milliseconds.


You could use this technique by Jonathan Kehayias to create a stored procedure that will allow the user to TRUNCATE a table.

As Johathan says:

  "You can't grant truncate because for whatever reason, that permission
  doesn't exist.  What you can do is use a stored procedure and use
  EXECUTE AS OWNER to get around it."

Here is the sample code from the above post.

CREATE DATABASE foo
GO

CREATE LOGIN foobar
    WITH password = 'Pa$$w0rd';
GO

USE foo
GO

CREATE user foobar
FROM LOGIN foobar;
GO

CREATE TABLE test (rowid INT identity)
GO

INSERT INTO test DEFAULT
VALUES;
GO

SELECT *
FROM test
GO

CREATE PROCEDURE dbo.truncate_test
    WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE test
GO

GRANT EXECUTE
    ON dbo.truncate_test
    TO foobar
GO

EXECUTE AS LOGIN = 'foobar'

EXECUTE dbo.truncate_test

REVERT
GO

SELECT *
FROM test
GO

USE master
GO

DROP DATABASE foo

DROP LOGIN foobar


Getting error "must declare the scalar variable" in SQL function even though it is declared

There are two problems here:

For Multistatement TVFs, you just need RETURN; instead of RETURN @variable;.
It does not appear as though you can use a User-Defined Table Type (UDTT) as the return table type. That will need to be specified explicitly (i.e. each column name and datatype).

If this were a Scalar UDF, then the syntax of specifying only the datatype name in the RETURNS clause, and specifying the variable name in the RETURN statement, would be correct. However, you cannot return TABLE (or CURSOR) types from a Scalar UDF.

Please see the MSDN page for CREATE FUNCTION for more details.

You will need something along the lines of:

ALTER FUNCTION [dbo].[fn_CalculateListing]
(
  @Listing dbo.TypeListingDatePrice READONLY
)
RETURNS @ListingDateDetails TABLE (ColumnName DataType,...)
AS
BEGIN
    -- Do stuffs

    RETURN;
END;
GO

For a broader discussion of the available techniques to pass sets of rows around, see: How to Share Data between Stored Procedures by Erland Sommarskog.


How mysql (innodb) handles auto increment column

Auto increment values are assigned sequentially.  In the event there is a rollback or deadlock you will find gaps in the auto inc value.
The auto inc is always assigned previous_auto_inc_value+1.  If a value is manually bumped you will get another gap in ids according to this rule.  If there are gaps you could manually specify a value for a "missing" id but that won't bump the next generated auto inc value.

If you delete rows with max auto inc values that will not reset the next generated value unless you do an alter table specifying the lower auto increment value that has no values larger than it.

Values for all columns in a trigger are accessible in a NEW object in the event of a before trigger or in the event of an after trigger you also have an OLD object.
Since ids are assigned sequentially there is a shared lock and there may be some contention between concurrent inserts but one will not completely block another.  You shouldn't expect the auto inc values for an insert to all be sequentially +1 of each other, although they likely may be.


Anyway to view original query on top of views using postgres

You can use EXPLAIN (ANALYZE, BUFFERS) on the query, which will show what is really executed on the base tables. That allows you to understand where the slowness is and what causes it. This works for catalog tables just as well as for other tables.


It's just a simple integer division.

So select (123456/100).

But that only valid is the column is integer type.

The example provided is big. If the column is integer then using a simple case logic is suffice. First check if the value is bigger than 99 then divide by 100, if not use what it is.

Declare @field_name;
Set @field_name = 123456;

Select (case when @field_name > 99 then (@field_name/100) Else @field_name);

change field name to any column you wish and adjust the query to target table.

Rather than using convert or cast , integer divide provide less cpu cycle because of dedicated math block inside the cpu. When using convert or cast you have many more cpu cycle because it will also include memory operation.


No, this feature was put in to record the last time that DBCC CHECKDB ran successfully. It would be extremely unlikely that a feature would be added that allows this value to be edited at will and effectively lie about the history.

The only way to update this that does not actually involve hacking the database boot page is to execute DBCC CHECKDB.

So you should customise these scripts to not report that issue or ignore this result if it is entirely expected that this database isn't being checked due to alternate arrangements.


How can I check available space of an index's pages in PostgreSQL?

Albeit not very prominently, the documentation of pg_freespacemap mentions the following:

  For indexes, what is tracked is entirely-unused pages, rather than free space within pages. Therefore, the values are not meaningful, just whether a page is full or empty.

Your query results simply mean that you don't have a single fully free index page.


Attach Database is disabled in Microsoft SQL Server 2012 Management Studio

If you need help attaching via T-SQL, if you see the SCRIPT button on the image you attached, you will be able to output the SCRIPT syntax to attach via 'T-SQL'. Just fill it in with the required information like target path to MDF file etc.. Give that a shot and see what you can come up with.

Also make sure you are on the same version of SSMS as the database to help prevent issues.

Also, when you connect to the DB instance, make sure you are using a Administrative account, it might be different than your current log-in or Windows Auth account.


Update record with converted epoch timestamp from another column

Your timestamp_epoch is defined with the data type text (which is what "function to_timestamp(text) does not exist"  is telling you).

You need to first convert your text value to a bigint:

to_timestamp(timestamp_epoch::bigint) AT TIME ZONE 'EST'


difference between UPDLOCK and FOR UPDATE

FOR UPDATE is not valid SQL Server syntax in a regular SQL statment, it is used when you create a cursor.

But you are probably using ADO with CursorLocation adUseServer and then your query actually works because ADO will use sp_cursoropen which accepts the syntax used for cursors.

The default behavior in SQL Server is that cursors can be updated so specifying for update does nothing for you unless you also specify a column list.

Specifying the updlock query hint on a cursor will only do things for you if you are running in a transaction. With updlock the locks is placed when you do fetch next from ... and without updlock the lock is placed when you do update ... where current of, still only if you are in a transaction.

So in your case, using updlock will place locks when you fetch data if you are in a transaction. If you don't use updlock, in a transaction, you will place the locks when you update the data. If no transaction is present there is no difference between the two and you could as well not use any of them.


identity value way off in a restored database

If you're on SQL Server 2012 (or newer), you may be running into a planned change, where identity values are retrieved in batches, and the next ID value may be incremented after a shutdown, or a restore.

Quick summary: SQL grabs its IDENTITY values in batches, and if it's not sure what the last ID used was, it will increment the next IDENTITY value by 10 (for tinyint) to 10000 (for bigint) when starting back up.

(Compiled from comments)


You need to bring the CSV table into MySQL, then you can:

update child
left join parent_child on child.child_id = parent_child.child_id
left join parent on parent_child.parent_id = parent_id
set child.parent_id_num = parent.id_num


MySQL inserts/deletes slowing down after a while after Start/Reboot

I found the problem, in the my.cnf file I had turned on TABLE_CACHE.
The way I resolved it without having to restart it was by using the command FLUSH TABLES. Shortly after that the inserts/updates/deletes resumed working at normal speeds.


how to change this dynamic rows to columns

Following is the solution for static version:

SELECT id,
group_concat(DISTINCT IF(type='A',value,null)) 'A',
group_concat(DISTINCT IF(type='B',value,null)) 'B',
group_concat(DISTINCT IF(type='C',value,null)) 'C',
group_concat(DISTINCT IF(type='D',value,null)) 'D',
group_concat(DISTINCT IF(type='E',value,null)) 'E',
group_concat(DISTINCT IF(type='F',value,null)) 'F'
group_concat(DISTINCT IF(type='G',value,null)) 'G',
group_concat(DISTINCT IF(type='H',value,null)) 'H',
group_concat(DISTINCT IF(type='I',value,null)) 'I'
FROM test
GROUP BY id;

To implement dynamic version you must implement it via Stored Procedure or  from your programming language implementing solution based on your logic.


MySQL : DROP TABLE Starting with a Prefix

Here is a stored procedure that accepts the Database and Prefix String as Parameters:

DELIMITER $$

DROP PROCEDURE DropTablesWithPrefix $$
CREATE PROCEDURE DropTablesWithPrefix(db VARCHAR(64),prfx VARCHAR(20))
StoredProcedure:BEGIN
    DECLARE ndx,maxidx INT;
    DECLARE giventable,SQLSTMT VARCHAR(500);

    CREATE TABLE TableZapList
    (
        droptablesql VARCHAR(512),
        idx INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ) ENGINE=MyISAM;

    INSERT INTO TableZapList (droptablesql)
    SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name)
    FROM information_schema.tables
    WHERE table_schema = db AND SUBSTR(table_name,LENGTH(prfx)) = prfx;
    SELECT COUNT(1) INTO maxid FROM TableZapList;

    IF maxid = 0 THEN
        LEAVE StoredProcedure;
    END IF;

    SET ndx = 0;
    WHILE ndx < maxid DO
        SET ndx = ndx + 1;
        SELECT droptablesql INTO SQLSTMT FROM TableZapList WHERE idx = ndx;
        SET @sql = SQLSTMT;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END WHILE;

    SELECT droptablesql FROM TableZapList;
    DROP TABLE TableZapList;
END;

DELIMITER ;

UPDATE 2011-07-12 14:55 EDT

I just thought of a cleaner, simplistic way. Instead of using a stored procedure, simply use the GROUP_CONCAT function to gather all the tables to zap. Then compose into a single query:

Here is a query to drop all tables that start with wp_pol in the current database:

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
FROM information_schema.tables
WHERE table_schema=database()
AND table_name like 'wp_pol%';

Next thing to do is store the result of it in

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
INTO @dropcmd
FROM information_schema.tables
WHERE table_schema=database()
AND table_name like 'wp_pol%';

Last thing is to execute the dynamic SQL using these three(3) commands:

PREPARE s1 FROM @dropcmd;
EXECUTE s1;
DEALLOCATE PREPARE s1;

Here is a demonstration using MySQL 5.5.12 in Windows that works :

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use garbage
Database changed
MySQL (Current garbage) :: show tables;
+------------------------------+
| Tables_in_garbage            |
+------------------------------+
| datas                        |
| rolando                      |
| wp_commentmeta               |
| wp_comments                  |
| wp_contact_form_7            |
| wp_links                     |
| wp_most_read_hits            |
| wp_options                   |
| wp_pollsa                    |
| wp_pollsip                   |
| wp_pollsq                    |
| wp_postmeta                  |
| wp_posts                     |
| wp_posts_idtracker           |
| wp_tantan_wordpress_s3_cache |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
| wp_w3tc_cdn_queue            |
+------------------------------+
21 rows in set (0.00 sec)

MySQL (Current garbage) :: SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema=database() AND table_name like 'wp_pol%';
Query OK, 1 row affected (0.00 sec)

MySQL (Current garbage) :: SELECT @dropcmd;
+--------------------------------------------------------------------+
| @dropcmd                                                           |
+--------------------------------------------------------------------+
| DROP TABLE garbage.wp_pollsa,garbage.wp_pollsip,garbage.wp_pollsq; |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL (Current garbage) :: PREPARE s1 FROM @dropcmd; EXECUTE s1; DEALLOCATE PREPARE s1;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL (Current garbage) :: show tables;
+------------------------------+
| Tables_in_garbage            |
+------------------------------+
| datas                        |
| rolando                      |
| wp_commentmeta               |
| wp_comments                  |
| wp_contact_form_7            |
| wp_links                     |
| wp_most_read_hits            |
| wp_options                   |
| wp_postmeta                  |
| wp_posts                     |
| wp_posts_idtracker           |
| wp_tantan_wordpress_s3_cache |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
| wp_w3tc_cdn_queue            |
+------------------------------+
18 rows in set (0.00 sec)

MySQL (Current garbage) ::

Give it a Try !!!


Initialize, Start Server and Create Database in Single Script

Try adding the "-w" flag to your pg_ctl -D . -l run.log start command, so that pg_ctl should only return once the server is up. If that worked, the problem was that your createdb command was running too quickly after pg_ctl, before Postgres was actually up and accepting connections, when those commands were executed in a script as opposed to manually.

(Re-posting comment as an answer, as requested.)


Documenting a collection of databases and their relationships with each other

Firstly, let me say from the outset that you have my deepest sympathies! :-) Maintaining a true-to-life up-to-date schema of even a single database is bad enough, but having to maintain a single point of truth (SPOT) for a multi-database disparate data-source environment is going to be nothing short of hell on earth!

You _must_ establish a SPOT for your systems - one path in, one path out. If you don't have serious senior management buy-in for this project, it is doomed from the start!

Take a look here and follow the links to K. Scott Allen's posts. Check out Agiledata.org and Allen's site ambysoft.com. Establish the relationships - there are schema generation tools for every database under the sun, but given that I imagine you have a large hetergeneous environment, you probably have everything from Oracle Enterprise servers down to spreadsheets on individual's machines.

Bear in mind that there's only one thing worse than no documentation and that's inaccurate documentation (think prominent, but defective, fire extinguisher).
Furthermore, if your central system is seen to sub-optimal, all that will happen is that workers on the ground it will be bypassed and become a shelf-ware irrelevancy.

The approach I would take in this situation is to establish a framework whereby there is [1 person | a small group] responsible for a given system and it is up to them to maintain their part of the schema documentation and ensure that they have the tools to do so. Yours should be more of an organisational/oversight role. Make it SOP that your group be informed of any schema changes (immediately) so that their (and by extension the central) meta-data repository is kept up to date. IMHO, you face an uphill battle and I wish you the best.

As for tools for documenting a given system, leave that up to the experts in those systems. As for a tool which consolidates "meta-meta"-data, I'm not aware one - apart from graft that is.


MySql 5.6: Ignores innodb_page_size setting in my.ini

According to the MySQL Documentation on innodb_page_size, Paragraph 2

  innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 14.6.1, “InnoDB Startup Configuration”.

You would had to have initialized that value in my.ini the day you first created the database and started mysqld for the first time. No restart of mysqld afterwards can ever change that.

If you want to create a MySQL Instance with a new size for innodb_page_size

you must setup a new datadir with no data (but do not start mysqld)
set the innodb_page_size to 32768 in the my.ini for that new instance
start mysqld against that new my.ini and new datadir
mysqldump the data out of the old instance
load the mysqldump into the new instance

Give it a try and let us know !!!

CAVEAT

Please be aware of disk I/O changes. Paragraph 3 of the same document says:

  The default 16k page size is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when single pages contain many rows. Smaller pages might also be efficient with SSD storage devices, which typically use small block sizes. Keeping the InnoDB page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.


sqlcmd returns Msg 701 when importing +10MB queries

I ended up using the solution to the following StackOverflow question:
https://stackoverflow.com/questions/36896391/insert-new-line-with-text-after-n-lines-and-3-lines-at-beginning-from-other-file


SQL Server 2016 Powershell: Restore-SqlDatabase with -ReplaceDatabase and -RelocateFile

The following worked:

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Database, $DestinationDatabaseFile)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($LogName, $DestinationLogFile)
Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $DatabaseBackupFile -ReplaceDatabase -NoRecovery -RelocateFile @($RelocateData,$RelocateLog)
Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $LogBackupFile -RestoreAction 'Log' -NoRecovery


Installing instances of older versions of SQL Server

Installing an older version of the database engine should be fine and completely separate (though the superstitious side of me would prefer to do it the other way around).

Don't install client tools or anything - keep it to the engine. If you need visual studio, BIDS, SSDT, SSIS etc., good luck...

Use the latest version of SQL Server Configuration Manager to manage the instances on the box, since newer versions are typically backwards compatible.


PostgreSQL: Table and index on different tablespace

The idea to separate tables and indexes in different tablespaces (which must be on different devices) as a performance tuning measure is an old myth that is slow to die.

It may be a good idea to spread I/O over several devices, but you should do that by using striping on the file system or hardware layer. Of course you can also do it by using different tablespaces, but it is unclear why specifically separating indexes and tables should be beneficial.

People who know other databases think similar.


Is it possible to reduce the duplication in this simple query?

Two levels of derived tables (subquery or CTE) to avoid all repeated calculations:
SELECT a, b, c
FROM  (
   SELECT z, a, b, a / b AS c
   FROM (
      SELECT z
           , x + y AS a
           , x + z AS b
      FROM   integers
      ) i
   ) i
ORDER  BY  z + c DESC
LIMIT  10;

With simple expressions, it won't matter for performance.
What might matter with repeating more expensive expressions is that all involved functions have (correct!) volatility STABLE or IMMUTABLE. See:

Can declaring function volatility IMMUTABLE harm performance?


Performance Metrics: Columnstore Indexes vs SSAS Cubes

There is no single right answer to your question.

Its worth noting they are not mutially exclusive technologies. You can have an OLAP cube reading from table using column store indexes.

The performance of each in various scenarios will differ depending upon the size, number, complexity of tables used and granularity/sparcity of data within them.

It will also depend upon the variability of queries used. Are they frequently aggregating? Or constantly seeking row level data?

The only way to know for sure in your case is to test your queries and scenarios with representative data.