Why does Postgres run SORT on many rows, before GROUP BY?

I ran into the same situation, a large number of rows of raw data being sorted on disk in order to be fed into a GroupAggregate which drastically reduced the number of rows.

I tried removing the ORDER BY clause; that allowed the optimizer to choose HashAggregate, which was significantly faster. To give a picture of the speed increase, a particular subset of my data took about

13.9 seconds using the original Sort and GroupAggregate
9.4 seconds for a simple EXPLAIN ANALYZE SELECT without a GROUP BY clause
9.6 seconds using the unsorted HashAggregate

It also scaled similarly with more data. I'm only using about a million rows, so you may still need a higher work_mem for your larger dataset (I am using 10MB). Although http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/#hash-aggregate suggests that HashAggregate will go to disk if it doesn't fit in memory, I found that reducing work_mem just prompted the planner to revert to the Sort/GroupAggregate plan.

I added SELECT * FROM (<unsorted query>) AS nested ORDER BY 1,2,3 to the outside and this sorts the aggregated output to give the same results as the original query, while still using the fast HashAggregate. I think there is perhaps an optimization opportunity here that the planner is not taking.

I tested this on 8.4 and 9.3 and got the same results with both.

Update:

If one of the aggregates being calculated is a COUNT(DISTINCT <fields>), then apparently HashAggregate can not be used.


Migrating Oracle 9i database to MySql database on a different Windows Server

As this answer on Stack Overflow indicates there is no built in functionality to do what you want but there are alternatives:

export your tables to csv files and then import into MySql using LOAD DATA INFILE. This solution does not scale well for large databases or schemas with clobs and blobs that are images or documents.  SQL Developer can help you recreate your schema in MySql.
roll your own solution with VB reading the data from Oracle and writing to MySql.  This takes time to code and test and is all throwaway code.
Commercial products:  Golden Gate works great but will cost you far more than you expect

The data access code in your application should, in general, work without change but details are everything. Consult the Oracle documentation for a comparison and you can see many differences and possible issues depending on what your schema has.


Cross-Continent SQL High-Latency Data Synchronization

Duplicate keys with AUTO_INCREMENT are not "possible".  See auto_increment_increment, which must be set to 2.

In general M-M is problematic for a number of reasons, which I will not go into, especially if you write to both of them.  You are better off writing to only one of them, leaving the other as a hot failover server.

For pages that only need read access, have Slaves in both datacenters.

Have client machines in both both datacenters; have them hit the local Slave for reading (if it is not down).

But beware of the "critical read" problem.

Plan A:  Change user expectations so that they understand that writing may be slow.

Plan B:  Use NDB Cluster with its "eventual consistency" model of replication.  Caveat: There are a lot of differences between InnoDB and NDB.

A technique that may help:  If you need to send multiple queries to a 'remote' server, package them in a Stored Procedure so that there is only one cross-continent hop.  (This, of course, is not always possible.)


Why the same execution plan causes LOB reads in some cases?

Comparing the two plans reveal this snippet:

                      <Warnings>
                        <SpillToTempDb SpillLevel="1" />
                      </Warnings>

The two plans enter execution with the same memory grant, but one has actual rows 3910 (and spills) while the other has 1502. As far as I can tell, the difference comes from the spatial operator itself. So ultimately the difference in runtime you see comes from the actual spatial data. I can also see that the actual Point you're computing the distance from differs:

          <ColumnReference Column="@Location" ParameterRuntimeValue="POINT (-75.549 39.7366)" />
          <ColumnReference Column="@Location" ParameterRuntimeValue="POINT (-83.6026 41.6721)" />

The plan that spills and has more rows to process is the one you labeled 'FAST'. Are you sure you did not mislabeled them? It makes a lot more sense for the spilling one to be slower.


Those numbers in your performance tests are accurate. Theoretical throughput does not match my experience, after migrating an environment to more than ten DS13v2 instances. I get between 220MB/s and 256MB/s, depending on the type of writes, even with P30s.

To answer your question, the bottleneck is the network itself, which links the drives to the VM.


How to clear SQL Agent Job Scheduler Cache

You may use below script to alter the schedule:

EXEC msdb.dbo.sp_attach_schedule @job_id=N'533fb186-72e9-40c5-af88-c8e57f097126',@schedule_id=16
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_schedule @schedule_id=16,
        @active_start_time=24500
GO

you may find job_id using below script for the job you want to change schedule:

select job_id from msdb..sysjobs where name = 'SomeDb.RunMeEvery3MinsBusta'

To find schedule_id, please use below script:

select * from msdb..sysjobschedules where job_id = '533FB186-72E9-40C5-AF88-C8E57F097126'

Here job_id is taken from previous script of table sysjobs.

I have specified this job to run at 2:45AM on daily basis, you may specify other options too.

EXEC msdb.dbo.sp_attach_schedule @job_id=N'533fb186-72e9-40c5-af88-c8e57f097126',@schedule_id=16
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_schedule @schedule_id=16,
        @freq_type=8,
        @freq_interval=64,
        @freq_recurrence_factor=1,
        @active_start_time=153000
GO

Above script is to change job type to weekly, running at 3:30PM on Saturdays.

Hope above helps.


How To Secure Sql-Server Database From Intended Manipulation by IT mans or Database Admins or System Admins

Sure, this is what compliance auditing solutions like IBM Guardium and Imperva do. They're appliances that sit in between your SQL Server and the rest of the network, and they capture everything that happens without affecting end user activities.

They're expensive - think six figures and up for the full implementation, and it's not going to be discreet.

If you were looking for something free or cheap, you can try using the built-in auditing features in SQL Server, or software products like Idera Compliance Manager, but be aware that sysadmins can disable those tools (because they're likely gonna be the ones installing them anyway.)


Can I downscale/upscale my MySQL server automatically?

the feature you want is currently in preview - it is called flexible server for mysql
https://azure.microsoft.com/en-us/pricing/details/mysql/flexible-server/


Multiple types of parts with multiple types of information (unspecified amount)

This is what I've designed as a solution for this problem but I don't think it's right, due to the tables being linked together in a circle.


SQL Server uses more memory than maximum setting

To contribute a little more to this, there is a post on dba.stackexchange.com that explains this situation a little more: SQL Server Max Memory not limiting use of RAM.  Also, in the answers on that question, Mark Storey-Smith recommends reading Brent Ozar's article "A Sysadmin's Guide to Microsoft SQL Server Memory" to further one's understanding on how Microsoft SQL Server handles memory.  I second that recommendation.


The following SQL will never return any row:

select * from <whatever> where rownum = 2;

The rownum pseudo-column numbers the rows in the result set. The first candidate row before the where filter would get assigned a rownum of 1 and be discarded because of the where clause. The second candidate row would the again be assigned a rownum of 1 and be discarded, etc.

Also be careful with combining rownum and order by clauses. The default doesn't do what you want, it orders after the row numbers have been assigned. (So an order by after selecting a single row with a rownum filter doesn't make sense.) Please look at the ROWNUM documentation for examples of how to do "top N" queries.

You can select a specific row number by "materializing" it in a sub-query:

select * from (
  select foo, bar, rownum rnk
  from some_table
  order by bar, foo
) where rnk = 42;

You'd also be better off filtering out the nulls earlier if nulls are at all a possibility (filter them out in the most nested query if you can). And the above trick doesn't work that well with group by, so using the ROW_NUMBER() analytic function would be more appropriate.

Assuming you do want to give a nice raise to the employee ranked 2nd by Topprofit, you could base your query on something like this:

SELECT
  s.empid employeeid,
  SUM(i.price * oi.qty) - s.salary Topprofit
  ROW_NUMBER() OVER (ORDER BY SUM(i.price * oi.qty) - s.salary desc) rnk
FROM salespersons s
  LEFT JOIN orders o ON s.empid = o.empid
  LEFT JOIN orderitems oi ON o.orderid = oi.orderid
  LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY
  s.empid, s.ename, s.salary
HAVING
  SUM(i.price * oi.qty) - s.salary is not null

Wrap that in select employeeid from (...) where rnk = 2 and you should be good to go.


Does relational design discourage splitting tables unnecessarily?

The “Normal Forms”  are narrowly defined in terms of eliminating redundant data and “update anomalies”.  Whether fixing other schema design problems counts as “Normalization” can be debated, but in general parlance Normalization just means ensuring the database complies with some Normal Form.


How should I design a specific needs mysql table

I suggest you create a table with the following fields:

Page URL
Date (Not datetime)
Count

And add a unique key on (page_url, date).

Then, use INSERT INTO .. ON DUPLICATE KEY UPDATE.

So,

CREATE TABLE `page_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `page_url` varchar(512) NOT NULL,
  `date` date NOT NULL,
  `count` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_url` (`page_url`,`date`)
)

Then, use this insert:

INSERT INTO page_view (page_url, date)
VALUES ('www.example.com/page1.html', date(now()))
ON DUPLICATE KEY UPDATE count=count+1;


How can one create a directory dynamically in PL/pgSQL?

It looks good.  There might be two issues when using COPY {FROM | TO } PROGRAM though:

missing permissions on the directory you want to create the new one in.  With your present code this is unlikely, as your dir will be created in the Postgres data directory.  The directory would be created as owned by the OS user/group postgres, permissions set to 700.
you are trying this as a non-superuser.  That won't work - and also produces a different error:

ERROR:  must be superuser to COPY to or from an external program
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Based on the above, your problem is the first case.  Check if the user has write permission on the target directory.


Querying All Records of All Users who have any instance of purchase behavior in their history

It's common practice to write this sort of query using an exists construct, something like:

select *
from users join credit_purchases using (user_id)
where purchase_type = 'A'
and exists (
    select 'x'
    from credit_purchases
    where purchase_type = 'B'
    and user_id = users.user_id
);

Be careful that this type of query only works properly if you're looking for different records in the sub-query; if it is at all possible to "hit" the same record, you need to exclude that in the subquery's where clause, by doing something like:

select *
from users join credit_purchases cp using (user_id)
where purchase_type = 'A'
and exists (
    select 'x'
    from credit_purchases
    where purchase_type = 'A'
    and user_id = users.user_id
    and credit_purchase_id <> cp.credit_purchase_id
);

I'm assuming your tables have surrogate keys with the obvious names.


You can't reference a column alias within the same SELECT clause as it was created in. In other words sum("total_time") is referencing something that doesn't exist yet. You'll either need to copy your previous CASE statement into your SUM() function call like this:
SUM
(
    CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
         when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
    END
)

Or use something like a subquery to generate the total_time column first, then apply the SUM() function on top of that column from your subquery.


How does PostgreSQL decide which query to cancel during deadlock cleanup

According to the documentation located at http://www.postgresql.org/docs/9.4/static/explicit-locking.html#LOCKING-DEADLOCKS (emphasis mine):

  PostgreSQL automatically detects deadlock situations and resolves them
  by aborting one of the transactions involved, allowing the other(s) to
  complete. (Exactly which transaction will be aborted is difficult to
  predict and should not be relied upon.)


Fail SSIS "Execute Process Task" if false is returned

Try using a Script Task to check the value of the SSIS variable that is set as the StandardOutputVariable of the Execute Process Task.  If this value is false the Dts.Events.FireError method raises an error event that can be used to fail the package.  The code below is an example of this using C#, where an error is raised when the variable is false.  The SSIS variable will need to be set in the ReadOnlyVariables field of the Script Task to allow it to be accessed.

    if (!Convert.ToBoolean(Dts.Variables["User::YourVariable"].Value))
    {
        Dts.Events.FireError(0, "Error", "Error Raised from Folder Check", null, 0);
    }


How to ensure all records of a header are under the same fiscal year?

A before insert trigger might be an option...

However, a "dates table" aka "calendar table" is much more flexible, regardless of the DBMS you are using.

Basically, it's a pre-generated table of dates from say 1900-01-01 to 2200-12-31.  In addition to various date columsn, it would contain various flag columns (weekday, weekend, holiday) and other helpful columns (Fiscal year, Quarter).

A bit of searching will turn up lots of articles about using them.

For Db2 for i specifically, if you have the Web Query product installed, IBM provides a routine (LOAD_DATE_CONVERSION_TABLE) to generate one (QWQCENT.DATE_CONV) for you.  See chapter 5 of the Redbook - IBM DB2 Web Query for i

For your case, you could define a unique key constraint on your dates table over (date, fiscal_year) and then use that as a FK constraint in your BUDGET_DATE_TARGET.

However, you may find you don't need BUDGET_DATE_TARGET or BUDGET_YEAR_TARGET at all once you've got the dates table.


Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different

Just from looking at the query results on my local development instance, there are two differences that I see (there are probably more):

sys.master_files returns the "virtual" size of a file, while sys.dm_io_virtual_file_stats returns the on-disk size of the file. I only see different sizes for database snapshots, which use NTFS sparse files behind the scenes (and I did verify the allocated and on-disk sizes in Windows).
sys.dm_io_virtual_file_stats does not return sizes for databases that are offline, while sys.master_files does.

For the case of tempdb, sys.master_files contains the startup file sizes for tempdb. If you investigate sys.database_files in the context of tempdb, you'll see the current sizes.


"Sniff" SQL queries being sent out from my computer

You do not require sysadmin privileges for what you want. You can use SQL Profiler and for that the required privilege is ALTER TRACE, see Permissions Required to Run SQL Server Profiler. Ask for the minimum required permission and perhaps will be granted.


SQL Query Against Previous SQL Query Results

I have a feeling LINQ is the best way to go but if you want to use TSQL on both the database and your in-memory datatable you could try this in-memory SQL engine.


Users cannot view tables in non-default schema in SSMS

Short answer: Don't use db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only. Using them will cause issues like the one you are facing.

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema Sales then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO Alice ;

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in all schemas then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE TO Alice ;

Metadata visibility will then work correctly.


Missing Index Not Displaying In Execution Plan

Well just while your Tuning Advisor displays a missing index, this won't mean, that your current running query would benefit from this index. The missing index will just be displayed if the current plan would take a benefit from an index.

You can also take a look at one of these DMV's, they will display missing indices for the current db.

SELECT *
FROM sys.dm_db_missing_index_details

If you want additional information, you can also use this query from me:

SELECT  id.index_handle, DB_NAME(id.database_id), id.object_id, id.statement AS object_name,
        gs.avg_user_impact, gs.avg_total_user_cost,
        id.equality_columns, id.inequality_columns, id.included_columns,
        gs.unique_compiles, gs.user_seeks, gs.last_user_seek, gs.last_system_seek,
        gs.user_scans, gs.last_user_scan, gs.system_scans
FROM sys.dm_db_missing_index_details as id
INNER JOIN sys.dm_db_missing_index_groups as g
        ON id.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats as gs
        ON g.index_group_handle = gs.group_handle
ORDER BY gs.avg_user_impact DESC

Edit based on your last update

Well it's quite easy to determine why you won't get a suggestion.

First of all, why you'll get an proposed index for this query:

SELECT SomeVarchar20 FROM SomeTable WHERE Age=30

If you create the index, as suggested below, you'll completely serve the index out of the index.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SomeTable] ([Age])
INCLUDE ([SomeVarchar20])

The SQL Server will be able to search for a data page using the Age, as defined in your index. In addition, due to the fact, that you only select the column SomeVarchar20, it is proposed to be added to the leaf level pages of your index (defined by the INCLUDE). This will help the SQL Server to find the right pages based on the Age in the B-Tree architecture. After he found the last row, which holds a pointer to the HEAP or the Clustered Index where all other properties lies, he also gets to the included columns on the leaf level pages. Due to the fact, that he found everything to display in the index itself, he won't go further to your HEAP or your Clustered Index. This will drastically improve your read (!) performance.

The fact, why you won't get a suggestion to add an index to this column is quite clear to me:

SELECT SomeVarchar FROM SomeTable WHERE Name='Adam'

You say that your Column Name is an varchar(512) this means one value can be 512 Bytes long. In fact a SQL Server page can hold round about 8060 bytes. This means you may store up to 16 names (maybe more if you don't use the 512 bytes all the way) per page. This won't improve performance drastically, as SQL Server might even need to iterate (seek) over many many pages. In fact, he needs to maintain all those pages additionally on each insert/update/delete.

If you just use SomeVarchar, which may be a varchar(512) too, it would drastically raise the leaf level pages, which will make the index even inefficient. This is the reason why your SQL Server might not provide a missing index hint for your SELECT.

Hopefully this will clear up all other things in your mind. Otherwise let me know.


Can I hide my password for data at rest encryption key?

Usually database is started as a service in background, so entering keys at startup will not be possible.
To separate key(s) and data you have the following options:

store keys on a separate device which can be unplugged after start of database server.
use an external key management system, e.g Eperi which also supports key rotation.


How to connect to root with Oracle Developer VM

ALL of the passwords are 'oracle'

That includes:
 OS: root
 OS: oracle
 Database: SYS
 Database: SYSTEM
 Database: HR

oracle@vbgeneric ~]$ sqlplus sys as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri May 11 11:43:25 2018

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

However, you're running into something different.

And it's discussed here.

  Using TWO_TASK. A third way is by defining the TWO_TASK operating
  system variable to point to the PDB you want to connect to:

[oracle@prosrv1 schema]$ export TWO_TASK=PDB1

And, then you can connect as usual without giving a connect string:

[oracle@prosrv1 schema]$ sqlplus system/oracle

I prefer this last approach because it simulates a database connection prior to introduction of the PDB. If you connect to a specific PDB the majority of the time, all you have to do is to set this variable in the shell initialization file (e.g. .profile in case of bourne shell) of the user so that the variable is automatically set when you log in. If you need to know which PDB you are connected right now in SQL*Plus, just use the following command: To show which container (or, the PDB) you are connected to:

SQL> show con_id
CON_ID
------------------------------
5

SQL> show con_name
CON_NAME
------------------------------
PDB1


Using pg_dump and psql to transfer table with timestamp into another Db

When you transfer the table you can alter the table name with ALTER TABLE table RENAME TO table_datestamp. I am not aware of automatic renaming tables while doing backup.

Or you can modify sql dump file and then run the script after modifying it.


Receiving "The SELECT permission was denied on the object" even though it's been granted

I'm not sure here, but I'm going to go out on a limb.  I think your issue might be with your DENY CONTROL record.  See here about half way down the page:

Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. A principal that is denied CONTROL permission on a database will not be able to connect to that database.

I realize that example is for a database, but take it one more granual level.  A DENY CONTROL on a table will deny all privileges on it, I'm guessing.  Do a REVOKE CONTROL to get rid of that and see if that fixes your issue.

If so, you'll have to place the user in a database role or deny them the explicit privileges against the table.


CUBE and calculating the number of dimensions

It seems like the "hierarchical dimension"s example given as (year, month, week) is treated really the same as 3 independent dimensions - all 8 possible combinations are allowed - that implies they are independent/orthogonal.
So if you have 4 of these triplet dimensions, that's the same as 12 independent dimensions.
But in any case, the answer is basically the same.
The number of possible combinations of 0 to m possible choices of m independent dimensions is a sum of the binomial coefficients and Wolfram helpfully even simplifies that for us:
 (https://www.wolframalpha.com/input/?i=sum+m+choose+n+for+n+%3D+0+to+m)
This is where the 8 possible combinations come from for 3 dimensions and shows why that list contains combinations with between 0 and 3 elements.
You can also look at that as a binary choice for each dimension - we know you can either choose to include or exclude a certain dimension, so you have 2 choices for whether each dimension is included or not, so you have 2 choices for the first, 2 for the second etc.  The product of all those is 2 * 2 * ... = 2^n
So for 12 dimensions, that is 2^12 or 4096.
If you treat it as 4 of the fatter dimensions (basically going from binary choices to octal choices), then the number of combinations in the product is 8 for each hierarchy, so you have 8 combinations possible of the first "dimension", combined with 8 combinations of the second "dimension", and so on, giving 8 * 8 * 8 * 8 = 8^4 = 4096 - and you can simply just view this as regrouping of the 2s into groups of 3.
I don't want to be remiss in pointing out that, in practical terms, many times hierarchical dimensions are not grouped in arbitrary ways as if the component dimensions are independent.
After all, they are in a hierarchy for a reason!
I notice even in the example given with (year, month, week), the (month, week) combination is pretty weak - because I am assuming that week is week number within the year.  That aggregating would be for all years, and the weeks don't fit nicely into months, so is probably pretty rare for analysis.
There are many cases of 3-hierarchies where you would only be interested in grouping (d1, d2, d3), (d1, d2), (d1), () - never leaving out a parent - (country, state, city, street, ...) is a good example - a real state entity doesn't really exist as a concept independent of the country-state combination.


You can just do it with a UNION ALL (you may or may not need the ORDER BY part, depending on your use-case):

SELECT
    a, b as `E`
FROM
    a
UNION ALL
SELECT
    a, c as `E`
FROM
    a
ORDER BY
    a ;

You can check it here.


Which version of Oracle supports Streams

XE does not support Streams, as officially documented. SE and SE1 support Streams but without redo capture. EE and PE (Personal Edition) fully support Streams, and also support Logical Standby.


Check if an index exists by comparing columns

It is possible to check if an index using a number of columns exists.

  WITH
     index_cols AS (
        SELECT
           table_name,
           index_name,
           COLLECT(column_name) AS column_names
        FROM all_ind_columns
        WHERE table_name = '&table_name'
        GROUP BY
           table_name,
           index_name
     ),
     target_cols AS (
        SELECT '&column_name_1' AS col_name FROM dual
        UNION SELECT '&column_name_2' FROM dual
     )
  SELECT
     ic.*
  FROM index_cols ic
  WHERE
     (
        SELECT COUNT(*)
        FROM target_cols tc
        JOIN TABLE(ic.column_names) icc
           ON (tc.col_name = icc.COLUMN_VALUE)
     ) = (
        SELECT COUNT(*)
        FROM target_cols
     )
  ;

Is one (a bit convoluted) way to do that.

But as aasim.abdullah noted in a comment, two indexes on the same set of columns but in different order, aren't the same. And it is possible to have both indexes side by side.


Using your base sample query above, this code should do what you are looking for. The important changes:

CROSS JOIN to a list of IDs that should be in the report
JOIN to the data table using Date and ID
SELECT the id from the CROSS JOIN table

The code:

DECLARE @s DATE = '20140518', @e DATE = '20140520';

SELECT
    i.ID,
    c.[date],
    Frequency = COALESCE(a.Frequency,0)
FROM
    dbo.Calendar AS c
CROSS JOIN (SELECT DISTINCT x.ID FROM dbo.table x WHERE x.date >= @s AND x.date < DATEADD(day, 1, @e)) AS i
LEFT OUTER JOIN
    dbo.table AS a
ON
    c.[date] = a.[date]
    AND a.ID = i.ID
WHERE
    c.[date]>= @s
    AND c.[date] < DATEADD(DAY, 1, @e);


Configure Email to be sent on restoring a database

That's suprisingly difficult.

SQL Agent Alerts were the first stop, but because a restore doesn't generate an "error message" there is no way to trigger it (I tried message levels 1 and 10, but no banana). It might be possible with a WMI query, but I'm not proficient in that.

Policies and extended events can create the necessary conditions but have no way to alert you.

Audits don't have the necessary conditions.

So, brute force then. The "restoring ..." message goes into the SQL Server log and into the Windows application event log with source MSSQLSERVER and event id 3402.

Working with that, you can attach a task to that event - unfortunately without having the option to filter based on the text which you need for the database name filter. So, configure the task to start a powershell script which then reads all the "MSSQLSERVER, 3402" events in , say, the last 2 minutes from the application log (hopefully you don't have mass restores all the time), checks for the correct db name and sends an email to you if it finds one with the wanted name.

As I said, brute force, but works.


How to backup SQL Server database once evaluation period has expired

I found a solution myself. Once SQL Server Management Studio expires the evaluation period it will not open anything. It shows go to this site to upgrade product or something else.

First, change the system date to 2 or 3 days back, then open the SQL Server Management Studio. Now you might get this error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
  (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Now follow this step:

Check if your SQL Server service is up and running properly:

Go to All Programs > Microsoft SQL Server 2008 > Configuration Tools >
SQL Server Configuration Manager > SQL Server Services
Check to make sure SQL Server service status is Running.

In addition, ensure that your remote server is in the same network. Run sqlcmd -L to ascertain if your server is included in your network list.


What is the optimal way to select the parent and child rows on a table with a self-relationship?

See the comment on the question about #1 and #2.

I don't believe MySQL supports recursive CTEs, so a union is likely to be the best way to do this.

The self joins are only going to work where the parent key is not null, because NULL will never evaluate as equal to anything (unless MySQL has weird NULL semantics).  In order to capture the parent the union is a reasonable approach.  Left joins for this are barking up the wrong tree because you can only specify them as a join predicate for the child and you're trying to include data from a parent row that actually has no join against itself.

Another approach would be to make parent_genre_id equal to id on the pop (id = 10) row, so the self join would also pick up the parent.  You can tell the parent because the id and parent_genre_id keys are the same value.


what could make examined_rows change radically?

What is happening -- The Optimizer picked a different way to perform the query.

You are at the whim of

The Optimizer -- It gets better with each new version.
The statistics -- ANALYZE TABLE may help, or could hurt.  (OPTIMIZE is overkill, and ends with ANALYZE; "never" use it.)
The constants in the query -- May lead the optimizer to pick a different explain plan.
The "phase of the moon", as I call this combination of whims.

If id is the PRIMARY KEY and there is no JOIN, you are asking for a lot of extra work: COUNT(DISTINCT(t.id)),; switch to simply COUNT(*).

When a query is run, some EXPLAIN plan is generated.
When you use EXPLAIN, a plan is generated.
There is no guarantee that they match.  (This is solved only in the latest version, wherein the EXPLAIN is optionally included in the slowlog.

Any single-column index on any of these may be best; there is no way to know without a lot more info on the distribution of the data:  tag_1, tag_3, tag_4, site_id, message_time.  I would have several 2-col "composite" indexes starting with each and ending with message_time.  This way, if the query actually says foo IN (just-one-item), it can move past foo and also use message_time.

Before discussing further, please add SHOW CREATE TABLE.


Here is a safer and more elegant version of your function:

CREATE OR REPLACE FUNCTION maskunion(_schema text, _tblbase text, _target text)
  RETURNS void AS
$func$
DECLARE
   _safe_target text := quote_ident(_schema) || '.' || quote_ident(_target);
BEGIN
   -- EXECUTE 'DROP TABLE IF EXISTS ' || _safe_target;  -- possibly dangerous!
   -- safer alternative:
   IF to_regclass(_safe_target::cstring) IS NOT NULL THEN
      RAISE EXCEPTION 'Target already exists: >>%<<', _safe_target;
   END IF;

   EXECUTE (
      SELECT 'CREATE TABLE ' || _safe_target || ' AS TABLE '
          || string_agg(quote_ident(schemaname) || '.'
                     || quote_ident(tablename), ' UNION ALL TABLE ')
      FROM   pg_tables
      WHERE  schemaname = _schema
      AND    tablename LIKE (_tblbase || '%')
      ORDER  BY tablename  -- better retain order
   );
END
$func$ LANGUAGE plpgsql;

This assumes the same schema for target and source tables.
Same call as in your own answer:

SELECT maskunion('public', 'tiles_2015_10_', 'newtable');

This concatenates and executes a command of the form:

CREATE TABLE newtable AS
          TABLE t_2015_10_10_z15
UNION ALL TABLE t_2015_10_11_z15
UNION ALL TABLE t_2015_10_12_z15
...

Major points

Like @Craig commented: CREATE TABLE AS is much more elegant.
I added a check whether the target table exists. Blindly dropping any table passed as parameter would make the function quite dangerous.
TABLE tbl is just short for SELECT * FROM tbl.
How to check if a table exists in a given schema
Make sure to escape identifiers properly:

SQL injection in Postgres functions vs prepared queries

BTW, the footgun function you refer to is a bit dated. You can TRUNCATE or DROP multiple tables with one command, and current Postgres has more elegant options for dynamic SQL:

Truncating all tables in a Postgres database


Importing a large mysql dump while replication is running

I don't think there needs to be any downtime on the Master.  See how this feels.

Configure it so that you have Master-Master.  That is, turn on the binlog and a few flags on the Slave so that it also acts like a Master; plus do CHANGE MASTER on the Master so that it is also a slave.  (But no traffic will yet flow from the Slave to the Master.)
Load the new database on the Slave.  It will replicate to the Master (and clog up replication in that direction).

I think that is all.  You could turn off M-M and leave it just M-S, but I prefer keeping "M-M, but writing only to one M".

(Caveat:  I have not tried anything close to this.)


Where's my Network Alias list in Oracle?

tnsnames.ora should be located in TNS_ADMIN, which is defined as ORACLE_HOME/network/admin/.

Go to a command prompt (assuming Windows from your mention of "c:\") and execute the following:

echo %TNS_ADMIN%

echo %ORACLE_HOME%

Verify the directories returned, make sure that your tnsnames.ora file is in there.


This is normal situation

when You run query without ORDER BY - LIMIT 30; work perfect - it stop query after first 30 records, any 30 records
When You add ORDER BY - MySQL first send all records to temporary table (if dataset big), than sort it, and then return to You 30 correct records

So on huge dataset - it work as expected.
Solution really only one - analyse query and reduce number of total records returned by query, how - depend from business logic

For example - posts.created_at <= '2016-10-24 10:30:53' (it mean yesterday) return all records from 5 000 000, but if add posts.created_at >= '2016-09-24 10:30:53' it return only month of data and etc - any legal ways for reduce number of records.

Second recommendation depend from Your table structure, some time replace SELECT * to the SELECT "WHAT REALLY NEED" could dramatically increase speed - for example one of column BLOB or TEXT, so exclude this column from SELECT - reduce size of data before sort for many. many times.
Even if this BLOB column need for You - You can request it by ID (single record) by second query from the page


(Copied from https://stackoverflow.com/questions/2391399/three-phases-of-recovery-the-analysis-phase-the-redo-phase-and-finally-the-u)

You might find this document interesting : Simple overview of the SQL Server Recovery Process

  Phases of recovery

  the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.

  Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.

  Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.

  Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.

Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).


Wide clustered index vs multiple narrow nonclustered indexes?

If you will always have SchoolId then you could benefit from making the clustered index a composite key of SchoolId, Id as you won't have to have an additional index on SchoolId to avoid a table scan.  Not having the additional index will let Inserts/Updates/Deletes complete faster as the transaction only has to update one index.

You may find as you create other queries that use the other fields in the where clause that an additional index on those might be beneficial so SQL Server can seek right to that index leaf which will help reduce the number of row returned.


Scripting drop/create of dependencies when altering view with schemabinding

I've found a way to do this by using a couple of different scripts.

First, I needed to setup the "PrintString" stored procedure from Greg M. Lucas in order to print all of my statements without them being truncated.

CREATE PROCEDURE [dbo].[PrintString]
(
  @InputString     nvarchar(max)  = NULL
, @MaxPrintLength  int            = 4000
)
AS
BEGIN
    SET NOCOUNT ON
    --! CONSTANTS (keep it SQL2005 compatible)
    DECLARE @LF          char ( 1 ); SET @LF         = CHAR(10);
    DECLARE @CR          char ( 1 ); SET @CR         = CHAR(13);
    DECLARE @CRLF        char ( 2 ); SET @CRLF       = CHAR(13) + CHAR(10);
    DECLARE @LINE_BREAK  char ( 3 ); SET @LINE_BREAK = '%' + @LF + '%';
    --! Working Values
    DECLARE @WorkingLength    bigint
    DECLARE @WorkingString    nvarchar  (  max )
    DECLARE @SubString        nvarchar  (  max )
    DECLARE @SubStringLength  bigint
    --! Validate/correct inputs
    SET @MaxPrintLength = COALESCE(NULLIF(@MaxPrintLength, 0), 4000)
    IF @MaxPrintLength > 4000
        BEGIN
            RAISERROR('The @MaxPrintLength value of %d is greater than the maximum length supported by PRINT for nvarchar strings (4000)', 17, 1, @MaxPrintLength);
            RETURN(60000);
        END
    --! Working variables
    DECLARE @InputLength bigint = LEN(@InputString)
    IF @InputLength = 0
        GOTO OnComplete;
    --!
    --! Our input string may contain either carriage returns, line feeds or both
    --! to separate printing lines so we need to standardise on one of these (LF)
    --!
    SET @WorkingString = REPLACE(REPLACE(@InputString, @CRLF, @LF), @CR, @LF);
    --!
    --! If there are line feeds we use those to break down the text
    --! into individual printed lines, otherwise we print it in
    --! bite-size chunks suitable for consumption by PRINT
    --!
    IF PATINDEX(@LINE_BREAK, @InputString) > 0
        BEGIN --[BREAK_BY_LINE_FEED]
            --! Add a line feed on the end so the final iteration works as expected
            SET @WorkingString  = @WorkingString + @LF;
            SET @WorkingLength  = LEN(@WorkingString);
            DECLARE @LineFeedPos    bigint    = 0
            WHILE @WorkingLength > 0
                BEGIN
                    --!
                    --! Get the position of the next line feed
                    --!
                    SET @LineFeedPos = PATINDEX(@LINE_BREAK, @WorkingString);
                    IF @LineFeedPos > 0
                        BEGIN
                            SET @SubString        = SUBSTRING(@WorkingString, 1, @LineFeedPos - 1);
                            SET @SubStringLength  = LEN(@SubString);
                            --!
                            --! If this string is too long for a single PRINT, we pass it back
                            --! to PrintString which will process the string in suitably sized chunks
                            --!
                            IF LEN(@SubString) > @MaxPrintLength
                                EXEC [PrintString] @InputString = @SubString
                            ELSE
                                PRINT @SubString;
                            --! Remove the text we've just processed
                            SET @WorkingLength    = @WorkingLength - @LineFeedPos;
                            SET @WorkingString    = SUBSTRING(@WorkingString, @LineFeedPos + 1, @WorkingLength);
                        END
                END
        END --[BREAK_BY_LINE_FEED]
    ELSE
        BEGIN --[BREAK_BY_LENGTH]
            --!
            --! If there are no line feeds we may have to break it down
            --! into smaller bit size chunks suitable for PRINT
            --!
            IF @InputLength > @MaxPrintLength
                BEGIN
                    SET @WorkingString    = @InputString;
                    SET @WorkingLength    = LEN(@WorkingString);
                    SET @SubStringLength  = @MaxPrintLength;
                    WHILE @WorkingLength > 0
                        BEGIN
                            SET @SubString        = SUBSTRING(@WorkingString, 1, @SubStringLength);
                            SET @SubStringLength  = LEN(@SubString)
                            --!
                            --! If we still have text to process, set working values
                            --!
                            IF (@WorkingLength - @SubStringLength + 1) > 0
                                BEGIN
                                    PRINT @SubString;
                                    --! Remove the text we've just processed
                                    SET @WorkingString    = SUBSTRING(@WorkingString, @SubStringLength + 1, @WorkingLength);
                                    SET @WorkingLength    = LEN(@WorkingString);
                                END
                        END
                END
            ELSE
                PRINT @InputString;
        END --[BREAK_BY_LENGTH]
--/////////////////////////////////////////////////////////////////////////////////////////////////
OnComplete:
--/////////////////////////////////////////////////////////////////////////////////////////////////
    SET NOCOUNT OFF
    RETURN
END
GO

Second, I created a temporary table to store all of the dependent entities so I can use it later to print my script.  Included in the temporary table is an "Order" column for doing descending order on the drop statements and ascending order on the create statements.

CREATE TABLE #DependentEntitiesInformation
(
    ObjectId INT,
    SchemaName NVARCHAR(MAX),
    EntityName NVARCHAR(MAX),
    ObjectDefinition NVARCHAR(MAX),
    [Type] NVARCHAR(MAX),
    [Order] NVARCHAR(100)
)
GO

Third, I fill my temporary table using a recursive CTE.  In this example my parent view is called 'dbo.Project_Expanded'.

SET NOCOUNT ON;

DECLARE @ObjectSchemaAndEntity NVARCHAR(MAX) = 'dbo.Project_Expanded';

WITH DependentEntities (ObjectId, SchemaName, EntityName, ObjectDefinition, [Type],[Order])
AS
(
    SELECT
        e.referencing_id,
        e.referencing_schema_name,
        e.referencing_entity_name,
        OBJECT_DEFINITION (e.referencing_id),
        ao.[type],
        CAST(ROW_NUMBER() OVER(ORDER BY e.referencing_id) AS NVARCHAR(100))
    FROM
        sys.dm_sql_referencing_entities (@ObjectSchemaAndEntity, 'OBJECT') e INNER JOIN
        sys.all_objects ao ON ao.[object_id] = e.referencing_id
    UNION ALL
    SELECT
        e.referencing_id,
        e.referencing_schema_name,
        e.referencing_entity_name,
        OBJECT_DEFINITION (e.referencing_id),
        ao.[type],
        CAST(de.[Order] + '.' + CAST(ROW_NUMBER() OVER(ORDER BY e.referencing_id) AS NVARCHAR(100)) AS NVARCHAR(100))
    FROM
        DependentEntities de OUTER APPLY
        sys.dm_sql_referencing_entities (de.SchemaName + '.' + de.EntityName, 'OBJECT') e INNER JOIN
        sys.all_objects ao ON ao.[object_id] = e.referencing_id
)
INSERT INTO
    #DependentEntitiesInformation
SELECT
    ObjectId,
    SchemaName,
    EntityName,
    ObjectDefinition,
    [Type],
    [Order]
FROM
    DependentEntities
GO

Fourth, I create my drop statements and then my create statements using my temporary table and I copy out the results.  Note that my version only handles inline table-valued functions, procedures, and views.  You will need to update it if you need to handle more object types.

DECLARE @StatementEnd NVARCHAR(4000) = CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);

DECLARE @DropStatements NVARCHAR(MAX) =
    (
        SELECT
            STRING_AGG(
                'DROP ' +
                CASE
                    WHEN [Type] = 'IF' THEN 'FUNCTION'
                    WHEN [Type] = 'P' THEN 'PROCEDURE'
                    WHEN [Type] = 'V' THEN 'VIEW'
                END +
                ' [' + SchemaName + '].[' + EntityName + ']',
                @StatementEnd
            ) WITHIN GROUP (ORDER BY [Order] DESC)
        FROM
            #DependentEntitiesInformation
    ) + @StatementEnd;

EXEC @DropStatements = [dbo].[PrintString] @DropStatements, 4000

DECLARE @CreateStatements NVARCHAR(MAX) =
    (
        SELECT
            STRING_AGG(ObjectDefinition, @StatementEnd) WITHIN GROUP (ORDER BY [Order])
        FROM
            #DependentEntitiesInformation
    );

EXEC @CreateStatements = [dbo].[PrintString] @CreateStatements, 4000

DECLARE @Statement NVARCHAR(MAX) = @DropStatements + @CreateStatements;

EXEC [dbo].[PrintString] @Statement, 4000
GO

Finally, I cleanup the temporary table and drop the "PrintString" procedure:

DROP TABLE #DependentEntitiesInformation
GO

DROP PROCEDURE [dbo].[PrintString]
GO


Your option 2 will be the way to go, smaller chunks. Depending on the size of your rows, You could increase the rows to around 1M.


Cannot add an account [non domain account ] to SQL Server 2012

It's probably because that's not the way that the SQL Server instance is seeing the application.  Attempt to use the application.  This should log an error in the SQL Server's ERRORLOG file when the application isn't able to connect.  This will tell you how the SQL Server is seeing the login.  Just copy the login from the ERRORLOG file and create a login for it.


How can I prevent gaps in a MySQL table AUTO_INCREMENT field?

An auto increment column will always provide the highest available id for a new user in your case.

However re-using missing numbers from failed transactions is not possible in a single step, since the primary key will probably have been used in other tables which will need to be updated.

There are no performance issues associated with having missing numbers, and there are a common occurrence. This can only be an issue if you have more failed transactions than those which complete, however there I would recommend you set a larger size for your primary key.


Under load, the following job-queue design does not work reliably, why?

In similar situation I use:

DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;

REPEAT

    UPDATE job_queue
    SET status = "WORKING", job_id = <arg_unique_job_id>
    WHERE status = "NEW"
    LIMIT 1;

    SELECT *
    FROM job_queue
    WHERE job_id = <arg_unique_job_id>;

UNTIL row_count() < 1 END REPEAT;

It's never gone wrong before.

PS. This construction was used in the case when a record free for reservation exists with guarantee. If not use some local variable (some @loopcount) for to limit the max amount of cycles to avoid infinite loop.


Automatically import .bak file on a daily basis

backup files (BAK files) can't be read to take only some records inside. You can only restore them. Then you can import from the restored database what you like.


Is it possible to create a SourceURI Column in SQL?

Common practice when modelling compound values like your SourceURI is to split them into the component parts, which have a column each. These are given the types basic to the DBMS i.e. string and UUID.

On retrieval the parts are passed to the application as-is, or concatenated with appropriate punctuation. You may choose to create a calculated column or similar to ensure consistentcy across the application.

Postgres has the composite type construct which may simplify implementation. I have not used it so cannot comment on the implications.


Converting a composite primary key to composite unique, and adding an auto-incremented PK column

That smells a lot like a "many-to-many" relationship table.  Is it?

As for linking from a changelog -- space is not the issue; correctness is.  A change log should have exactly what existed at the time the log was taken.  What if the original table is updated?  If the table in question is changed for any reason, I think you do not want the change reflected in the existing changelog entry.  (Though, perhaps in a new changelog entry.)

If you would like to discuss your particular case further, please provide SHOW CREATE TABLE and a few queries that use the table.

Here's another tip: It is OK to do the following:

id INT UNSIGNED AUTO_INCREMENT,
...
PRIMARY KEY (...),
INDEX(id)

That keeps the original PK clustered, which might be beneficial, yet still allows for an AI key.

Another tip:  Don't say UNIQUE(foo) in place of INDEX(foo) unless there is some reason for doing the uniqueness constraint.  (Example:  INSERT ... ON DUPLICATE KEY ...)

Further discussion about many-to-many.


Yes the code in Update 3 will work and you will be able to select the information you need. If you are comfortable with that then go for it.  Keep in mind that means you will have to either duplicate your Painter type in both the sub and super type, or leave Painter as a sub type without a super type.  Either way this is a little bit awkward but workable.

Assuming the tables really are as small as you say,and your requirements as simple as they seem you might want to consider compressing the business data down into one table.  It won't look as sexy as other solutions but CRUD operations will be dead simple. I don't know MySql very well so please excuse any silly syntax errors:

CREATE TABLE users (user_id INT NOT NULL, user_type_id INT NOT NULL, busniess_id INT, etc);
CREATE TABLE user_type (user_type_id INT NOT NULL, name VARCHAR(50) );
CREATE TABLE business (business_id INT NOT NULL, business_name VARCHAR(50) NOT NULL, business_type_id INT NOT NULL, business_subtype_id INT, etc);
CREATE TABLE business_type (business_type_id INT NOT NULL, name VARCHAR(50) );
CREATE TABLE business_sub_type (business_sub_type_id INT NOT NULL, name VARCHAR(50) );

Like I said that isn't sexy but it will get the job done.  It is a bummer that MySql doesn't offer check constraints because they could offer a touch of protection.

If your requirements get any more involved I would seriously reconsider @MDCCL's post.  My gut says this is the best solution out of the mostly ho-hum solutions I can envision.  To quote another answer to that question :

  The Answer by MDCCL is fascinating, educational, and presumably correct (though above my pay-grade).


How do I use the smallest amount database file space to rebuild an index on Mircosoft SQL Server Express due to the 10GB limit

The reason is that index rebuild means creating a new index under covers and then dropping the old index. I.e., while the operation is running you need twice the amount of storage available. If that isn't doable for you, you have basically two options:

Do it yourself in the opposite order: Drop first and then create.

Or do REORGANIZE instead of REBUILD.


Mysql join query improvement and GROUP_CONCAT on GROUP BY

Generally, you should avoid JOIN ( SELECT ... ).  In the past it was notoriously inefficient.  In later versions it is sometimes somewhat slower than alternatives.

Instead, let's get rid of the JOIN some of the time:

...
    Ifnull(a.transfered, b.transfered)   AS transfer,
    ...
FROM   cdr_temp a
   LEFT OUTER JOIN (SELECT USER,
                           Substring(number_dialed, 18, 11) AS transfered,
                           uniqueid
                    FROM   transfertable)
                   b
                ON a.uniqueid = b.uniqueid
WHERE  ...
GROUP  BY a.lead_id,
          b.uniqueid

-->

...
    Ifnull(a.transfered,
             ( SELECT substring... FROM transfertable
                          WHERE uniqueid = a.uniqueid
             )
          )   AS transfer,
    ...
FROM   cdr_temp a
   -- gone:  LEFT OUTER JOIN
WHERE  ...
GROUP  BY a.lead_id,
          a.uniqueid  -- Note "a."

It is "improper" to GROUP BY only some of the non-aggregate columns; you will get random values for the others.  Is a "skill" associated with a "lead"?

It would be more informative to have SHOW CREATE TABLE for each table.  What are the PRIMARY KEYs of the tables?  Are there any other UNIQUE columns?


Question about database structure for network visualuzation

The common way of representing network (or graphs) in a tabular / relational structure is to divide it into two tables:

1) A node table where each node has a unique numeric identifier, together with any attributes. Something like this:

CREATE TABLE net_nodes (
  node_id         NUMBER PRIMARY KEY,
  node_type       ....
  ... other attributes
);

2) A links table to represent the connections. Each link has a unique numeric identifier and the IDs of the origin and destination nodes. And of course any attributes, such as direction/orientation (does not apply in your case), throughput, etc. Something like this:

CREATE TABLE net_links (
  link_id         NUMBER PRIMARY KEY,
  start_node_id   NUMBER NOT NULL REFERENCES net_nodes,
  end_node_id     NUMBER NOT NULL REFERENCES net_nodes,
  link_type       ....
  active          ....
  ... other attributes
);

That data model then lets you perform simple graph traversals using recursive SQL. That should work fine given you have a tiny network (you mention 200 nodes). For more serious graphs (10s or 100s of nodes or more) specialized graph databases / engines become necessary.


What am I doing wrong with the Database Tuning Advisor?

I wouldn't bother with DTA. It's a whole lot of work for results that are sketchy at best. Everyone that gets into index tuning eventually realizes it's both a science and an art, and there's a lot to it. You can use tools to get recommendations, but implementing those recommendations blindly almost always results in issues down the road.

Instead, I'd give BlitzIndex a try. It'll take advantage of the statistics SQL Server already maintains to determine where you can improve your indexing (and does so within seconds). It also provides very accessible documentation on how to use the results. Every result includes a link to the pertinent doc. This is how I maintain my indexes these days.


Key column 'FK_UserQuestion' doesn't exist in table error

Your foreign keys have the wrong format. The syntax should be:

FOREIGN KEY <name> (column_1, ..., column_n)
REFERENCES <parent table> (column_1, ..., columns_n)

In your case:

FOREIGN KEY FK_UserQuestion (UserID)
REFERENCES QA.Users (UserID)

I.e.

CREATE TABLE QA.Question
( QuestionID int AUTO_INCREMENT PRIMARY KEY
, UserID int
, Question varchar(65535)
, FOREIGN KEY FK_UserQuestion (UserID)
  REFERENCES QA.Users (UserID)
) engine = INNODB;

A similar change for QA.Answer should also be made


what is the downtime of mongodb cluster when upgrade to 3.0

If you follow the upgrade process it is designed to avoid downtime -- for example using rs.stepDown() to gracefully step down the replica set primaries rather than abruptly triggering an election by shutting down the server.

Of course there is no guarantee if something doesn't go to plan, but you should test your upgrade in a QA/staging environment before upgrading production.


How to lock all tables except some specific table?

Do not use LOCK TABLES with InnoDB.  For this situation consider the following...

Leave read_only = OFF
Provide a readonly login for your application when it goes to the Slave in addition to a readwrite login for when it talks to the Master.

The app user would have grants something like:

GRANT SELECT         ON app_db.*   TO app_user@slave
GRANT ALL PRIVILEGES ON sessions.* TO app_user@slave   -- and cache.*
GRANT ALL PRIVILEGES ON app_db.*   TO app_user@master


What are the concepts that need to be gone through for MySQL DBA?

Storage Engine
You should be very fluent with the use of two main storage engines: MyISAM and InnoDB
You must learn :

how data and index pages are laid out
how table and indexes are cached
how table formats can make significant differences in reads and writes
how table-level and row-level locking are handled
how queries that mix both storages engines affect performance
how to backup data from each storages engine

MySQL Replication
You should understand such things as

What are the I/O and SQL Threads
When log-slave-updates is necessary for a slave
What allows you to write on a read-only slave
When it's appropriate to use MyISAM/blackhole on a slave for tables that are innodb on the master
How relay logs are populated
How to recognize slave errors
How to filter out/fileter in SQL for specific databases and tables
Why run backups for slaves rather than masters

Troubleshooting and Tuning Queries

Learn how to run EXPLAIN on queries
Learn how to create indexes to properly speed up queries
Learn how to tune queries
Learn how to activate the slow log
Learn how to convert the slow log into MyISAM

INFORMATION_SCHEMA
You should learn how to query the INFORMATION_SCHEMA database for table info, row counts, last access info, and things like these.
Third Party Tools
The best tools that you should embrace are the following:

MAATKIT
Percona Toolkit (fork of MAATKIT also from Percona)
XtraBackup
MONyog / SQLYog

This is not a comprehensive list. However, it should be enough for anyone to be proficient as a Developer/DBA.
Here is Oracle's Certification Exam Descriptions that provide lists of skills within MySQL to have and know: EXAM1 and EXAM2
IMHO Here is a good set of MySQL Books to learn from


Design for user profile information and track changes

You can follow a proven design from data warehousing called a Slowly Changing Dimension.

Essentially on each row you add some combination of an ActiveFlag, DateTimeStart and DateTimeEnd. When you get a change you end the old record and write a new one, which will include the changes that triggered the historic change. If you do not care about some fields' history you can simply update in place on the active row.

There will only ever be one active row for any given login and any number of historic versions of the information. If you keep the start date and end dates you can see when changes occurred in addition to simply keeping older records around. You can always move ended rows to a different table, and retain only the most recent record in your system table... I'd make sure that the performance hit and maintenance costs of doing so outweigh simply keeping them in place.


How to Limit DTU Percentage Consumption for a Given Session in Azure SQL Database?

Have you considered using the Resumable Online Index Rebuild feature so you can schedule X executions of one minute of those indexes?

ALTER INDEX [ix_CustomerIDs]
ON [ContosoSales].[ConstosoTransactionData]
REBUILD
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1 MINUTES);
GO

ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] PAUSE

ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] RESUME

Have you considered to scale up the tier to Premium to speed up things when performing index maintenance during any small maintenance window? You will pay Premium for just the hour where you scaled up and users may perceive less performance impact.


Does SQL server 2008 log instance level security permission changes

If I'm not mistaken the default trace contains property changes to logins which should include an account being disabled. An article that walks though the various information you can get from the default trace.

EDIT

OK, already confirmed not much at all is captured with manipulating logins on an instance. So unless you had something already capturing the information like SQL Server Audit (if on 2008 or higher), there is nothing natively logged by SQL Server to let you know who did it.

If you happen to be logging successful logins to that instance you can determine who was logged in that had the permissions but that is the closest you will likely get.


Why does NOT IN with a set containing NULL always return FALSE/NULL?

Boolean logic - or Three valued logic

IN is shorthand for a series of OR conditions
x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)
... is the same as x <> 1 AND x <> 2 AND x <> NULL
... is the same as true AND true AND unknown **
... = unknown **
... which is almost the same as false in this case as it will not pass the WHERE condition **

Now, this is why folk use EXISTS + NOT EXISTS rather than IN + NOT IN. Also see The use of NOT logic in relation to indexes for more

** Note: unknown is the same as false at the end of an expression in a WHERE condition.
While the expression is being evaluated, then it is unknown
See @kgrittn's comment below for why


Is deriving a value using multiple joins a good idea?

If the specification states that you should store the final cost then you should store the final cost (even if this is is not normal form) but it is not saying anything about how you compute that cost before storing it.

If you are proposing that you compute the price each time you look at it you need to carefully consider if the base data could change over time. If a product can change price you would need to keep a full audit of the prices over time so you could find the right price when deriving the total. If you are maintaining such an audit already then this is "free" but if not it could be a significant amount of extra work to save storing the derived value at the point of sale. If course you could add an extra Entity between order and Product which stores the price paid for that particular product in that particular sale, which means you could easily implement ad-hoc price changes such as temporary "manager's special"s or one-off discounts.

To answer the direct question from your title: finding a values with many joins is fine as long as appropriate index structures are present to make this process efficient. Whether you are best doing this in the database or another layer of your application is subject to much debate and depends on your overall design philosophy and efficiency (follow the design philosophy to be consistent, except where that has significant impact on efficiency (though at this point question the philosophy too instead of just skirting around it this once)). It is probably more efficient for a low-load use case to have the database due the work as this is the sort for thing SQL as a query language was created for and the DB has all the information at its fingertips. For working at larger scales though pushing logic to the outer layers can be preferable because (in a DB server plus web server example) it is easier to scale the web server (a web far is relatively easy to create) than the database side (where you have a whole family of new issues like whether you need master/master replication or one of the other many options, and whichever option you chose how due you make sure every node is in sync at any given time and how much of a hit is acceptable to take to ensure full transactional consistency over all the DB nodes, is "eventually consistent" good enough, and so forth). As the DB side can be ore complex to scale out, there is a strong case for pushing as much logic as possible to outer layers and letting the DB be in charge of nothing more than data storage/retreval and core constraint policing (this also makes it easier to migrate between DB platforms if needed in future).

With regard to triggers, consider their flexibility against the fact that they can be harder to debug as they are essentially things that happen behind your code's back. They have their place, you need to decide if your project is one of those places. Stored procedures for saving are another option, as is moving the logic to another layer of the application.

Your lecturer is being deliberately cagey about your query, and I've made an effort to not give any straight answer but instead listed some "things to consider" (there may be more "things to consider", please try to think about it), because he/she wants you to come to a design decision by yourself to prove that you have an understanding of the general field rather than just an ability to follow specific instructions. Don't worry too much as long as you can offer some justification for the choices you make - in an academic test you will get marks for giving a less-than-optimal solution if you can somehow justify the choices you made and any you rejected. Be careful to state the assumptions you made and factors you thought of but discounted as not relevant to this implementation - if your assumptions are wrong then you'll still get some credit for a reasonable choice based on those iffy assumptions, if you make a bad choice because of bad assumptions (that would be good if the assumptions were correct) and don't document the assumptions you'll get neither half of the credit as the marker can't tell why you made the choice (bad input assumptions or just simple bad thinking).

Some lecturers are clue up enough to scan sites like this to see if people are asking questions about their assignments. Don't be tempted to try get an answer from here that you can just copy+paste as you'll get zero marks (or worse!) if it is noticed that is what you have done.


Multi-field unique constraint where we include one or another field based on nullity

First, for constraint number 2, nothing special is needed. A UNIQUE constraint ignores - and accepts - NULL values so a simple unique constraint on (w,x,y) will work just fine enforcing that.

For constraint number 1, I don't think there's a way with DDL in version 5.6 so your best option is probably the trigger solution by @stefan.

If you can upgrade to 5.7, you can use a GENERATED column and a unique index on it:

ALTER TABLE t
  ADD wz int GENERATED ALWAYS AS (CASE WHEN w IS NULL THEN z END),
  ADD UNIQUE INDEX x_y_z_uq (x, y, wz) ;


Permissions required for running extended events

As Jonathan stated and the link shows CONTROL SERVER is required prior to SQL Server 2012 to create an Extended Event session. The ALTER ANY EVENT SESSION permission prior to SQL Server 2012 only allowed a user to start/stop a session, or change a current session; it will not allow them to create a session. CONTROL SERVER is required for that ability.

If this is a production system it is based on your company's policies but I would not provide a developer that level of access to anything in production. I would likely create a few different sessions that they might need for troubleshooting code in production, and then grant them ALTER ANY EVENT SESSION so they could run that as needed. They would also have the ability to then change the session as they see fit. However, be aware that even though Extended Events has less overhead than Profiler it still has overhead depending on what you are trying to capture. I would hope the developers have an understanding on what events can cause additional overhead.


sp_repladdcolumn - is it possible to supply a list of Publications to add this column on?

sp_repladdcolumn is deprecated unless you have SQL2000 subscribers, you should use the Replicate Schema changes option on the publication. Assuming you have that set to No, I guess you could still use the sp_repladdcolumn, and if you look at the source for the proc, it looks like it has handling for accepting a comma separated list of publications, but using it is venturing into undocumented handling of a deprecated procedure...i.e. no guarantees.

sp_repladdcolumn  @source_object =  'fooTable',
                  @column =  'fooNewColumn',
                  @typetext =  'int' ,
                  @publication_to_add =  '[pub1],[pub2]'

EDIT: Publication names need to be in [] for it to work as in the code of sp_repladdcolumns it has:

select p.name, a.name, a.pubid from dbo.sysmergepublications p, dbo.sysmergearticles a
where p.pubid=a.pubid and a.objid=@objid and p.snapshot_ready=0
     and (p.name like @publication_to_add
         or charindex('[' + p.name + ']', @publication_to_add)>0)


Your host is wrong, to use the socket it must be "localhost", not the socket address.

The url for JDBC to use a socket for connection is:

jdbc:mysql://localhost:3307/my database name?socket=/home/aspect_mkn8rd/tmp/mysql.sock

(leaving out here the definition of a user and password, as your software seems to require it apart, it would be &user=my user&password=my password)


Like most things, it's a cost vs effort/complexity trade-off.

RDS is easier, but less flexible, and costs more for the same level of performance.

(In particular, note that there's no way to replicate into or out of RDS)


I will try to answer to you; it's not fully clear to me , but anyway , here is my first version

create table SCHOOL (
     id int,
     name varchar(30),
     type_id int
)

create table  SCHOOL_STUDENT(
     school_id int,
     student_id int
)
create table  SCHOOL_TYPE (
   id int,
   name varchar(30)
)

insert into SCHOOL(id, name , type_id)
values(1,'abc school',3)
       ,(2,'xyz school',4)
       ,(3,'HEH school',3);

insert into SCHOOL_STUDENT (school_id, student_id)
values(1,123),(3,123),(2,56);

insert into  SCHOOL_TYPE (id,name)
values (1,'junior school'),
       (2,'tech school'),
       (3,'High school'),
       (4,'Ultra High school');

Here is my query:

select
   ss.student_id
   ,case when max(isHighSchool) = 1
         and min(isHighSchool) = 1 then 'YES' else 'NO' end as isHighSchool
from
(
    select
        ss.student_id--, st.name
        ,case when st.name = 'High school' then 1 else 0 end as isHighSchool
    from
         school_type st
         inner join school s on s.type_id = st.id
         inner join school_student ss on ss.school_id = s.id
)ss
group by ss.student_id

ouput:

student_id  isHighSchool
56          NO
123         YES

dbfiddle here


Geographical replication zones in MySQL Cluster?

In MySQL Cluster tables are spread among all nodes by default.
It is possible to specify a table to only reside on a subset of
the node groups by either setting the number of partitions or
by setting PARTITION_BALANCE (in 7.5). It is not intended to use
this for geographic replication.

It is not possible to specify exactly on which node group these
partitions will reside (at least I don't think, there was some
work in 5.1 to provide NODEGROUP when creating partitions, but
this is not fully supported, so a bit unsure what works here and
what doesn't).

For geographic replication MySQL Cluster supports replication from
one cluster to another cluster. This is using MySQL Replication and
can thus be done selective for certain tables. It is possible to
replicate different tables to different clusters by using multiple
MySQL replication servers inside one MySQL Cluster.


Updating large table worked before, now taking hours

It might help a lot if you would have listed the indexes you have or the query plan. If the columns 4, 5 or 6 are indexed, that can slow the process down, and having a clustered index on columns 1, 2 and 3 should speed it up.

If columns 4,5 or 6 are variable length (e.g. varchar) then updating new value can cause a lot of page splits, that definitely will make it slower.

I think what John M meant was that if there's a reasonable amount of distinct values in for example column1, you could first collect the distinct into a temp. table and then run the update separately for each of the values because doing the joining the tables in smaller parts is usually a lot faster.

Of course everything depends on several things, like data types, number of distinct values in the columns 1-3 etc.


When should I think about upgrading our RDS MySQL instance based on memory usage?

Before you switch server models, you need to know what differences there in configuration

I sat down and carefully looked over all default MySQL settings that would normally be written in a my.cnf. All are values are the same with the exception of two

max_connections
innodb_buffer_pool_size

Amazon set the following values based on server model

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

For more information see my past posts on MySQL in Amazon RDS

Sep 21, 2012 : MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases
Sep 18, 2012 : How do I reduce the database size on AWS RDS MySQL?
Sep 10, 2012 : Does the Amazon RDS backup/snapshot service lock tables?
Aug 02, 2012 : Local database vs Amazon RDS
Jul 25, 2012 : Scaling Percona datacenters: setup and replication


Extract specific backupfiles from SQL Server 2008 backup

Not with the native tools.  Thinking about it, I can see how somebody could write a tool to do it, but there's nothing off-the-shelf.


Replication SQL Server to a Cluster with error

you got SQL Server error number "1326" which mean there is a connection issue to the destination server, how to troubleshoot that

ping the server and check if you can reach it, and connection allowed via firewall.
check if SQL Server services are up and running (you can do that from services window).
check if you can access the server remotely using SQL server, if not open SQL server configuration manager, choose "SQL Server Network Configuration", then "protocol for MSSQLSERVER", open TCP/IP, and make sure Sever allowing traffic to the destination.
Open SSMS, connect, right-click on the server, properties, connections, and check "Allow remote connections to this server". (you have to restart the service to make sure changes took effect).


SQL Anywhere Long Varchar/Binary Column Length Limited to 32767

Found the answer:

The issue was not with SQL Anywhere, but rather in our server datasource administration settings.

We use ColdFusion 10 on our web server, and the datasources have a Long Text Buffer setting which was limiting the size of the string being returned from a query of the database to 64000

Changing this value to 2000000 solved the issue.


Avoid PLE drops while performing DELETE operations

For one particular table Which has the biggest(in terms of size)
  records it take around 3-5 hours to delete all 2 millions of old
  records

we need more detail like table structure along with data type and index details.

Try opposite method.

Keep data of last 30 days and truncate old table.

For example (Step 1),

declare @CurDate datetime=getdate()

select *
into NewTable
from ExistingTable
where datecol >= @CurDate and datecol <= dateadd(day,-30,@CurDate)

Rename old table to anything unique or drop table as per requirement. In fact drop table only after testing and approval from other.
Rename new table to existing table


Data Warehouse -- Generating natural keys to be used in WHERE clauses

Just use composite keys. If the table's natural key is (user_id, branch_id), use that.

CREATE TABLE whatever
(
    user_id ....,
    branch_id ...,
    PRIMARY KEY(user_id, branch_id);
);

In joins you can do it like you showed:

SELECT ...
FROM sometable s
INNER JOIN whatever w ON (s.user_id = w.user_id AND s.branch_id = w.branch_id)

or pairwise:

SELECT ...
FROM sometable s
INNER JOIN whatever w ON (s.user_id, s.branch_id) = (w.user_id = w.branch_id)

For use with IN, use lists of anonymous records:

SELECT ...
FROM whatever w
WHERE (w.user_id, w.branch_id) IN ( ('a','1'), ('b', '2'), ... )

or if you have a lot of values, where this starts getting inefficient, join on a temp table. Big IN lists are bad whether they're singular values or composite.


Again, the failure rate is like 1 out of every 10 runs, and no one is
manually removing these objects

Although no one is manually dropping these objects, it seems SqlDependency is used to detect database changes. SqlDependency creates/drops ephemeral objects so, if the application is running during publish with the with the /p:DropObjectsNotInSource=True option, the object might longer exist when the deployment script is executed by sqlpackage.
One option is to stop the application while the dacpac is published. You could also exclude the problem objects during publish (e.g. using the DoNotDropObjectTypes option).


Syncing two (or more) PostgreSQL 9.1 clusters

There isn't currently anything in the PostgreSQL core which would help with that, but there is work in progress to do what you want that is likely to make it into version 9.3, which will probably be released in the summer of 2013.  If you can't wait for that, there are a number of solutions available from third parties. From what you describe, Bucardo sounds like it might be the best fit.

A new option, which recently hit production release 1.0, is Postgres-XC.  It is exciting technology, but if you have a stable production environment you might want to just keep an eye on it for now; it's pretty "bleeding edge" at the moment, and they haven't yet figured out how to incorporate all of the features of normal PostgreSQL in that environment.


How to grant only functions while revoking tables?

You want to make your functions SECURITY DEFINER and have them owned by a user that does have the requisite rights.

Be very careful when coding SECURITY DEFINER functions. Don't make them owned by a superuser and read the manual carefully. Create a role that has only the rights required and no more; give it ownership of the SECURITY DEFINER functions. Where appropriate create multiple roles for different access levels.

See CREATE FUNCTION.


I'm guessing you don't have any covering indexes for the select columns.  Because your Count(*) runs faster, you probably have the columns in the joins or where clause indexed.  As soon as you add your select columns back, the query engine now has to scan the whole table again.  If possible, add the select columns as included columns to the indexes (not sure if this is ms sql server).


Postgresql performance issues when issuing many small inserts and updates one at a time

In order to avoid overloading my server, the requests are queued and
  handled one at a time.

That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details there may be transaction overhead and even connection overhead, too.

Try to run multi-row INSERT / UPDATE commands instead. Or use COPY, or one of the fastest options would be to define a foreign table and fetch all data from a remote DB directly in a single SQL command (if that's at all possible).

Or depending on details, some other replication method may fit your needs.

This is how you update multiple rows at once:

Update multiple rows in a table from another table when condition exists

And here is how to do that from a CSV file:

How to update selected rows with values from a CSV file in Postgres?


What is the best practice for treating fields with only two possible values?

I would start with a check constraint that limits the column to those two values.

If the table containing those values is expected to get really big (tens or hundreds of millions of rows), then using a lookup table and only storing a foreign key reference as a number might save some space and might increase performance if you don't need that value all the time.

Plus it will make adding new values easier because you don't need to re-create the check constraint.

If the end-user is expected to maintain the list of allowed values, then a check constraint is not an option at all.

Another thing to consider is localization (translating those labels). If you have to translate those display values into other languages, then using a lookup table is the better way to go.


How to avoid modifications during pg_dumpall

From the pg_dump documentation :

  It makes consistent backups even if the database is being used
  concurrently.

So the backup process takes a snapshot of the DB on a specific moment so each commits which are made during the process are ignored.


Need to upgrade SQL Server from 2012 to 2017 on a Server 2008 box

This will be somewhat an opinion answer, so likely to be downvoted.
First up, in-place upgrades are fraught with issues. So many things can go wrong along the way and you have no fallback position except a restore. This means you must first ensure that at every step you have a restore option, for the databases and the OS. However, I recognize that not everyone can do a side-by-side upgrade.
Yes, you're doing this in the correct order. Get the OS upgraded first and ensure it's stable. Then do the SQL Server upgrade. Both these are fairly standard upgrades and under normal circumstances should go off without a hitch. However, you're missing steps on the upgrade.
While I understand that you can't do a side-by-side upgrade, I'd still do side-by-side testing. Get a second server, even if it's a VM on a dev laptop, and run through this whole process. Validate that it works. Don't trust some idiot on the internet. Most of your application code should work just fine. Some may not. Find that early, before you've committed to a difficult to undo upgrade.
I'd also add the step of recording standard behavior of the system now using Extended Events and then again after the upgrade so you have data to understand how the upgrade affected performance and behavior.
Because you're crossing the 2014 threshold, moving from 2012 to 2017, you have to deal with the new cardinality estimation engine introduced in 2014. For most queries, no difference will be apparent. For a few queries, you may see a performance enhancement. For a minority of queries, you may see horrific degradation of performance.
After you restore your databases to 2017, leave the compatibility mode in place, at 2012. Enable Query Store on your databases. Run your normal load for a period of time. It's on you to figure out how long. Depends on the system. Some could be a day, some a month, somewhere in between. I don't know. After running the load for this period, change the compatibility level to the new version. When you see regressed queries, use Plan Forcing to make them run the old way (the old plans will be in Query Store).
That should cover you better.


For a software application, does every workstation need database drivers?

It all depends on the application and how it is designed.

Two main cases:

Client application connects to an application server, application server connects to the database.

In this case, no need for database drivers on the client side. The client application never talks directly to the database but calls the server-side software only. The client application in this case can be a web browser or a custom application (large or small) - doesn't matter. The application server software hides the details, and the end-user workstation doesn't even know what type of database (if any!) is used in the backend.

This is how most of the web works.

Here, end-users don't necessarily need credentials on the database. Some applications require it (they essentially delegate authentication to the database), some on the other hand deal with the authentication themselves. In that case, the server-side application software will have credentials on the database but the end-users will not. The server-side application will usually use the database to store identifiers and privileges in its own format, and allow/deny actions based on that.

The end-users won't need network access to the database server(s) in this scenario, only to the application servers.
Client application connects directly to the database

Usually in this case, the client-side application is fairly large (for "Enterprise" software). All the application processing is done in the client-side software and/or stored procedures in the database, the client interacts directly with the database.

Here you need drivers on the client side. (But that may not require a separate driver installation. E.g. a Java application could come bundled JDBC drivers and use those directly.)

End-users need to have credentials in the database, with appropriate privileges (I'm completely ignoring some horrors with shared accounts). They also need network access to the appropriate port(s) on the database server(s), either directly, via proxies, VPNs or whatever.

(And there are hybrids of these two approaches, with some work done via an application server, some directly on the database. I haven't seen many of those though.)

The first case is more common these days in my experience, with the browser having become the most commonly-used client-side software.


Can I create a new column with default and a foreign key as a metadata-only operation?

You can do this, but only if you don't check the FK on creation.  The Foreign Key will be enforced for subsequent INSERT and UPDATEs, but won't be trusted by the Query Optimizer.  EG:

drop table if exists MyReallyBigTable
drop table if exists MyThing
go
select o.* into dbo.MyReallyBigTable
from sys.objects o, sys.columns c

go
create table MyThing(MyThingId int primary key )
insert into MyThing(MyThingId) values (0)

set statistics io on
go
ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL
    DEFAULT 0
    --REFERENCES dbo.MyThing(MyThingId);
go
alter table MyReallyBigTable
with nocheck
add constraint  fk_MyReallyBigTable_MyThing
foreign key (MyThingId) references MyThing(MyThingId)
set statistics io off

You can come back later when you have time for a table scan and check all the rows to make the FK trusted:

select name, is_not_trusted
from sys.foreign_keys
go
alter table MyReallyBigTable with check check constraint fk_MyReallyBigTable_MyThing
go
select name, is_not_trusted
from sys.foreign_keys


Best way to move sybase IQ table to Sybase ASE

IQ has the ability to output data directly from the data server to a local filesystem.  It's faster than returning data over the network to a client tool.  Create the output file that way, and then bulkcopy it into Sybase ASE using bcp -c :

set temporary option Temp_Extract_Name1='my_output_file';
-- Set column tab delimiter via hex value for use by bcp -c
-- Note, character between the single quotes needs to be an actual
-- tab character. \t and \0x09 don't work.  Might need to be
-- in a script where you can insert a tab character using vi or
-- something similar.  Stupid IQ.
set temporary option TEMP_EXTRACT_COLUMN_DELIMITER=' ';
select * from my_table;
set temporary option Temp_Extract_Name1='';

This should produce a file that in a suitable format for bcp with the -c option.  Be careful about tabs embedded in the data.  I'm not sure if they'll be escaped.


How can a database parameter be used on `sp_addextendedproperty`?

The grammar for exec accepts a @module_name_var which allows the module name to be in a variable.

This does accepts three part names so the below does the trick.

DECLARE @DbName SYSNAME = 'AdventureWorks2012';
DECLARE @module_name_var NVARCHAR(500) = QUOTENAME(@DbName) +
                                              '.sys.sp_addextendedproperty';

EXEC @module_name_var
  @name = N'Caption',
  @value = 'AdventureWorks2012 Sample OLTP Database';

Or alternatively having just reviewed the question again you may just need this anyway.

EXEC AdventureWorks2012.sys.sp_addextendedproperty
  @name = N'Caption',
  @value = 'AdventureWorks2012 Sample OLTP Database';


Change clustered index without dropping the primary key

Just drop the existing clustered index, then create a new one on the ID column.

DROP INDEX [Created] ON dbo.Realty;
GO

CREATE UNIQUE CLUSTERED INDEX CX_Realty ON dbo.Realty (ID);
GO

Of course, you'll want to do this during a maintenance window so you don't cause too much blocking.  If you have Enterprise Edition you can do the CREATE INDEX operation online by adding WITH (ONLINE=ON) to the statement.


would it even care to go through this memory grant queue or right away spill to the disk?

It doesn't work that way. Once a plan was chosen that requires a memory grant, the query must obtain the grant so it goes through the queue. Spilling, if any, occurs much later in the execution cycle. A query cannot decide to go ahead without the grant and 'spill' instead. It must get the grant and, based on that, start the execution. If the grant turns out to be insufficient (due to bad estimate or due to obtaining a much lower grant than requested) then the query will be forced to spill.

if query optimizer realize that due to bad statistics or missing indexes

Strictly speaking it's not the optimizer, it's the query execution. Query Optimizer has only a say in deciding the plan, but once the plan is chosen and launched into execution the optimizer is out of the picture. Also, 'missing indexes' play no role here. A missing index can force a bad plan, but it cannot influence how that 'bad' plan is executed since that plan was built considering exactly what indexes actually exist so it knows exactly what it can and cannot do. Spills occur almost always due to bad estimates, ie. bad stats or bad cardinality estimation algorithms in the optimizer and in the execution (it gets really complex if you dig into the details so I'll stop here).

Even if system has plenty of workspace memory available during the execution once the query exceeded the granted workspace memory during the execution it will has to spill to the disk

Unfortunately, yes. However the optimizer should come up with a plan that leverages the available RAM.
I recommend reading Understanding SQL server memory grant, which is the best info on the subject by a wide margin.


postgres materialized views and indexes to improve aggregate performance

No. How would the query processor recognize that it should use the materialized view.  The materialized view is another object.

See  http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html

In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view."

So materialized views are useful, but you need to program not only for their maintenance, but also doing the necessary coding to use the materialized view.


Shared and IX locks causing deadlock (Sql server)

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues]
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table.  Another thread is then taking an IX on the issue table (which is blocked).  This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.


For batch operation I generally use:

psql -v ON_ERROR_STOP=1 -P pager=off --single-transaction

... however, generally psql should detect that stdout is not a terminal and automatically turn pager off if you're redirecting its stdout.


Why does using a Real data type in a mathematical equation return an incorrect result?

See Using decimal, float, and real Data, and Data Type Precedence

In your second query, 776.2384 is a decimal number (and so are the two other values). So the calculation is done in that type, and the result is mathematically exact.

In the first, you introduce a real. The other literals are promoted to that type, and the calculation is done according to IEEE 754 rules, in round up mode. IEEE 754 floating points are not exact, so you will generally not obtain the "mathematically exact" result you're expecting.

Quote from the articles above:

  Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.


How can I identify when to create a new table to hold data that can be obtained from a query?

One issue not covered in the accepted answer is "do you need this value over time" and "will the formula possibly change".

For instance consider the commision example. If the commission is paid, the amount should be stored as that is a historical figure of what was actually paid. The way to calulate commisions could change next month (and frequently does) but that won't change what was actually paid which must be stored separately.

It is the same idea as storing the price the customer actually paid for a product (after a calculation of discounts etc.) rather than relying on a formula against a price table to do anything except the initial calculation because the product price next month might not be the same as what the price was when the cutomer made the order.

If you need a historical record of what the value was at a point in time, always store that value after using the formula for the inital calulation.


Is there an easy way to trigger automatic failover of a single mirrored database?

If your mirroring is configured correctly for high safety/automatic failover, then I would be inclined to think that you could just take the principal database offline with this:

alter database YourDb set offline
with rollback immediate
go

I believe by taking the database offline, you will have automatic failover without affecting the other two databases that you don't want to failover.


Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair.

Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. Are you sure there are no system functions affected or functions installed by an additional module?
It would also be pointless to change the owner of functions that already belong to the designated owner.

First, check if REASSIGN OWNED could work for you:

  change the ownership of database objects owned by a database role

You have to list all roles to be disowned explicitly. But it also reassigns functions.

To assign all functions (and no other objects) in a given schema to a new owner (optionally regardless of previous owner):

SELECT string_agg('ALTER FUNCTION ' || oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public';
-- AND p.relowner <> (SELECT oid FROM pg_roles WHERE rolname = 'foo')
-- AND p.proname ~~ 'f_%'

This generates the canonical SQL commands ALTER FUNCTION ... to change all functions (in the specified schema). You can inspect the commands before executing them - one by one or all at once:

ALTER FUNCTION public.bar(text, text) OWNER TO foo;
ALTER FUNCTION public.foo(x integer) OWNER TO foo;
...

I included some commented WHERE clauses you might want to use to filter the results.

The cast to regprocedure produces a valid function name with parameters, double-quoted where necessary, schema - qualified where necessary for the current search_path.

The aggregate function string_agg() requires PostgreSQL 9.0 or later. In older version substitute with array_agg() and array_to_string().

You could put all of this into a DO statement or a function like demonstrated in this related answer:

Drop all functions in a namespace? (Execute generated DDL commands?)

In Postgres 9.5 or later, you can simplify the query using new object identifier types regnamespace and regrole:

SELECT string_agg('ALTER FUNCTION '|| oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc
WHERE  pronamespace = 'public'::regnamespace;
-- AND relowner <> 'foo'::regrole
-- AND proname ~~ 'f_%'


The sequence will return values in order, whether or not they exist in the table. Your code will need to defend against that. Any method to defend against possibly already-in-use serial values will really hurt concurrency - and if your DB is big enough that you're wrapping int4 serial columns, you care about concurrency.

Most people just use bigint keys and don't worry about ID re-use.  You won't run out.