I'd suggest to use a calendar or schedule table.

In my example I've set up a 3-day table calendar with intervals of one hour:

(Do a google search to find out some date series generator, you'll find a lot)

    Hours table
===================
2017/01/01 00:00:00
2017/01/01 01:00:00
2017/01/01 02:00:00
2017/01/01 03:00:00
2017/01/01 04:00:00
2017/01/01 05:00:00
...
...

Then simply LEFT JOIN your table with the calendar table and get NULL rows:

    Hours table               Reservations
===================        ===================
2017/01/01 00:00:00        2017/01/01 00:00:00
2017/01/01 01:00:00  LEFT  2017/01/01 01:00:00
2017/01/01 02:00:00  JOIN  NULL                 <-- Free
2017/01/01 03:00:00        2017/01/01 03:00:00
2017/01/01 04:00:00        NULL                 <-- Free
2017/01/01 05:00:00        NULL                 <-- Free

SELECT    hdate
FROM      calendar
LEFT JOIN reservations
ON        hdate = from_date
WHERE     parking_id is null;

dbfiddle here


Profiler: Only want to see Provider Errors

Thanks Aaron - I confirmed the same number of rows were there between the trace dumped to a table, and what was showing in Profiler.

Then, I used the following WHERE to get the records I wanted to see:

WHERE CAST(textdata AS VARCHAR(MAX)) LIKE '%(Code%'

...because all the Provider errors stated the code for the error.


Following is a SQL query that can be run against all nodes in the cluster to check all of the AG-related health states.  The output could be formatted differently depending on your needs, but this one returns either a list of the objects that are not healthy, or a row that includes the server name and that all objects are healthy.

DECLARE @HealthStates AS TABLE (objectname VARCHAR(50), [type] VARCHAR(20), health_desc tinyint)

/*    Collect local database replica states    */
INSERT INTO @HealthStates
    SELECT cs.[database_name], 'database_replica', rs.synchronization_health
    FROM sys.dm_hadr_database_replica_states rs
    join sys.dm_hadr_database_replica_cluster_states cs ON rs.replica_id = cs.replica_id and rs.group_database_id = cs.group_database_id
    WHERE rs.is_local = 1

/*    Collect local AG states   */
INSERT INTO @HealthStates
    SELECT ag.[name], 'availability_group', gs.synchronization_health
    FROM sys.dm_hadr_availability_group_states gs
    join sys.availability_groups_cluster ag ON gs.group_id = ag.group_id
    WHERE gs.primary_replica = @@SERVERNAME

/*    Collect replica states   */
INSERT INTO @HealthStates
    SELECT cs.replica_server_name, 'server_replica', rs.synchronization_health
    FROM sys.dm_hadr_availability_replica_states rs
    join sys.availability_replicas cs ON rs.replica_id = cs.replica_id and rs.group_id = cs.group_id

/*    States to grid   */
--SELECT objectname, [type], health_desc FROM @HealthStates

/*    Test for not healthy   */
IF EXISTS (SELECT health_desc from @HealthStates where health_desc <> 2)
BEGIN
    SELECT objectname, type, 'NOT_HEALTHY' as health_desc from @HealthStates where health_desc <> 2
END
ELSE
BEGIN
    SELECT @@SERVERNAME AS objectname, 'All Objects' AS type, 'HEALTHY' as health_desc
END


Assuming you are running some flavour of UNIX, tail the alert log to see what is happening with your shutdown


I'll assume you are using innodb_file_per_table for this answer.

There is more than one meaning to "InnoDB fragmentation":

.ibd file is fragmented, and is very large whereas the dataset is small
Index pages are fragmented in that there are too many pages to contain little data, in which case they could be merged.

Please consider this post I wrote a while back: it shows how after purging many rows from a large table, the data file is fragmented (i.e. it is very large in the filesystem -- it's a known issue these files never reduce in size). And yet the indexes were not fragmented by the end of deletion: this is because InnoDB properly merges pages as they become empty(er).

The OPTIMIZE command indeed does not apply on InnoDB. What it does is rebuild the table (exactly like an ALTER). See this:

mysql [localhost] {msandbox} (test) > create table t(id int) engine=innodb;

mysql [localhost] {msandbox} (test) > optimize table t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+

As for DATA_FREE: I suggest that you simply ignore this variable. To be honest, I've been working with InnoDB tables for 10 years, and have never found this value to be very consistent with anything.

And now it's time for the real discussion: what exactly are you trying to achieve? Unless your database is completely stale, there will always be some fragmentation. It is natural to the process of adding, removing and updating rows in your table.

Fragmentation is not that evil: free space can get reclaimed by new data. If you tables are not very large, then just forget about the whole thing. For very large tables, you might gain some disk space by optimizing the table. But ask yourself: how soon would the table reach same fragmentation? An hour? A day? A week? IMHO in all these cases it is pointless to optimize the table.

Nevertheless, if a large table is massively purged of data, which is not expected to return, I'm all for optimizing it. Say you realize you have some redundant data which consists some 30% of your table size. Sure, it would be great to have that disk space back.

Bottom line: only consider these issues with very large tables; only if you have issues with disk space.


A sysadmin can't demote themselves temporarily / most of the time / etc. What you want to do is create a SQL auth login that you use most of the time, and it only has the privileges to do the limited things you want to do. Then when you want to perform DDL operations, log in as your Windows login.

There are probably ways to do this if your primary login is not a sysadmin, but I think they will be self-defeating. You'd have to log in as someone else any time you want to do anything (including things you want your lower-privileged login to be able to do, but didn't foresee when you set up the permissions). As a quick example, here are two database principals, one of which can impersonate the other:

CREATE USER blat WITHOUT LOGIN;
CREATE USER floob WITHOUT LOGIN;
GRANT IMPERSONATE ON USER::blat TO floob;

EXECUTE AS USER = 'floob';
GO
PRINT USER_NAME();
GO
EXECUTE AS USER = 'blat';
GO
PRINT USER_NAME();
GO
REVERT;
GO
PRINT USER_NAME();
GO
REVERT;

Results:

floob
blat
floob

(Now, impersonation is not perfect - there are issues with using this when you need to access resources across database boundaries, for example.)

So, imagining those are real users hooked to real logins, you could add only blat to the ddl_admin role, and log in as floob most of the time. When you needed to, you could simply impersonate blat, do your DDL things, then revert. My suggestion is to just stay away from trying to diminish the privileges of your Windows account / sysadmin, and just get used to logging in a different way so that you can be a peon most of the time, and only elevate when you need to. I'll repeat, though, that this won't eliminate mistakes you would make in any case anyway - it only prevents you from accidentally running DDL, or running the wrong DDL, which could happen when you've knowingly impersonated, too.

And, because the notion of elevating your own privileges only when you need to, in order to protect yourself because you think you'll only make mistakes when you're not elevated, seems weird to me, and because I can't help it (credit xkcd, of course):

Here is a more concrete example, that uses a real SQL authentication login.

CREATE LOGIN peon WITH PASSWORD = 'peon', CHECK_POLICY = OFF;
GO

CREATE DATABASE playground;
GO

USE playground;
GO

CREATE USER peon FROM LOGIN peon;
GO
ALTER ROLE db_datareader ADD MEMBER peon;
-- of course grant all the permissions you want here
GO

CREATE USER ddldude WITHOUT LOGIN;
GO
ALTER ROLE db_owner ADD MEMBER ddldude;
-- doesn't have to be db_owner, just a valid example
GO

GRANT IMPERSONATE ON USER::ddldude TO peon;
GO

Now, create a new session, logging in directly using peon/peon, and run this code:

USE playground;
GO
CREATE TABLE dbo.foo(id INT);
GO

Breaks, right? Yes, of course:

  Msg 262, Level 14, State 1
  CREATE TABLE permission denied in database 'playground'.

Now, you can impersonate another user, without being a sysadmin, so you should file a bug against whatever documentation told you that:

EXECUTE AS USER = N'ddldude';
GO
CREATE TABLE dbo.foo(id INT); -- works
GO
REVERT;

Don't forget to clean up:

USE master;
GO
ALTER DATABASE playground SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE playground;
GO
DROP LOGIN peon;
GO


You can do it in two ways :

1 Backup & Restore :

Take backup using mysqldump & restore backup with new name.

2 Manual Create Table in New Database :

create schema new_schema;

select concat("create table new_schema.`",table_name,"` as select * from `",table_schema,"`.`",table_name,"`;")
from
information_schema.tables
where table_schema='old_schema';

/* Execute the above statement output simultaneously*/

-- i.e
create table new_schema.`XXX` as select * from `njindiainvest`.`XXX`;
create table new_schema.`YYY` as select * from `njindiainvest`.`YYY`;
create table new_schema.`ZZZ` as select * from `njindiainvest`.`ZZZ`;


begin
  for u in (select username from dba_users)
  loop
    execute immediate 'grant select on jsmith to "' || u.username || '"';
  end loop;
end;
/


Optimizing MySQL query with some LEFT OUTER JOINS

Please provide SHOW CREATE TABLE; it is important for optimization to know, for example, which table recycle_bin is in.

Even before looking at the joins, get rid of the "index merge intersect" by building a "composite" index:

 INDEX(sellfast, subcat_id, recycle_bin, hidden)  -- in any order

A LEFT OUTER JOIN (aka LEFT JOIN), per se, is not the villain.  Having a subquery in it may be the real villain.  Several things can be done with such, but I am not sure any cure applies to your case:

Get rid of LEFT.  But this would lose some rows if they are missing from ads_rating.  Without LEFT, the subquery would probably be performed first instead of repeatedly.  (If this is valid, try it first.)
Move the subquery into the SELECT.  But there are two columns being provided, so it is unlikely to be practical.
Use WITH.  But this requires version 8.0.  What version are you using?
CREATE TEMPORARY TABLE for the subquery, then use it in place of the subquery.  (This may be the best choice.)


How do I disable escaping when using COPY FROM in PostgreSQL?

The copy command by default uses text format with tab delimiter. So only one thing you need is to escape backslashes:

copy onegram (ngram, year, match_count, volume_count)
from program 'sed ''s/\\/\\\\/g'' < /home/tims/data/ngram/test.tsv';
select * from onegram;
╔════╤════════════╤══════╤═════════════╤══════════════╗
║ id │   ngram    │ year │ match_count │ volume_count ║
╠════╪════════════╪══════╪═════════════╪══════════════╣
║  1 │ aX13_X     │ 2006 │           8 │            5 ║
║  2 │ aX13_X     │ 2007 │           4 │            3 ║
║  3 │ aX13_X     │ 2008 │           2 │            1 ║
║  4 │ a\         │ 1852 │           1 │            1 ║
║  5 │ a\         │ 1935 │           1 │            1 ║
║  6 │ a\         │ 1937 │           2 │            2 ║
║  7 │ ACT1V1T1ES │ 2003 │          15 │           11 ║
║  8 │ ACT1V1T1ES │ 2004 │          63 │            6 ║
║  9 │ ACT1V1T1ES │ 2005 │           1 │            1 ║
║ 10 │ ACT1V1T1ES │ 2006 │           5 │            4 ║
║ 11 │ ACT1V1T1ES │ 2008 │           4 │            3 ║
║ 12 │ ACTION="   │ 1995 │           3 │            3 ║
║ 13 │ ACTION="   │ 1996 │           6 │            5 ║
║ 14 │ ACTION="   │ 1997 │           9 │            7 ║
║ 15 │ ACTION="   │ 1998 │          19 │           11 ║
║ 16 │ ACTION="   │ 1999 │          11 │            5 ║
╚════╧════════════╧══════╧═════════════╧══════════════╝


Read CSV and perform SQL in SQL Server Management Studio

You may use BULK INSERT for that:

CREATE TABLE #orders (
  Column1 int,
  Column2 nvarchar(max),
  Column3 datetimeoffset
)

BULK INSERT #orders
FROM 'X:\orders.csv'
WITH
(
  FIRSTROW = 1,
  DATAFILETYPE='widechar', -- UTF-16
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS -- Treat empty fields as NULLs.
)

If you exported the CSV from SQL Management Studio then some preparations are needed:

The file should be converted to UTF-16.
All NULLs should be replaced with empty strings.

Here is a Powershell script for that:

Get-Content .\orders_raw.csv -Encoding UTF8 | % { $_ -replace "NULL","" } | Out-File ".\orders.csv"


Unknown column 'sitename' in 'where clause'

Sitename is a string, but you're not enclosing it in apostrophes, so SQL is interpreting it as a column. Please try adjusting like so:

"option_name='" . $value . "'"


This is caused MySQL's sloppy data type checking. As far as I know there is no workaround, except to write correct SQL.

In general it is highly recommended to never rely on implicit data type conversion. Make sure your literals match the datatype of the column.

So you should write:

select * from mytable where colb = '0';


Selecting records based of other table records

Putting data (here: "old"/"new") into the table name is a bad idea becaus it usually complicates queries. It should be stored in a column instead.

The easiest way to handle this is to create such a table will all warnings:

WITH all_warnings(warning_table, id, warning_kind, creation_date) AS (
  SELECT 'old_warnings', id, kind, creation_date FROM old_warnings
  UNION ALL
  SELECT 'new_warnings', id, kind, creation_date FROM new_warnings
)
SELECT *
FROM reports
WHERE id IN (SELECT report_id
             FROM sections
             JOIN all_warnings USING (warning_table, warning_kind)
             WHERE creation_date = ?
            );

If you need all warnings in other queries, then you should make all_warnings a view.


Find problematic MySQL users and databases

I helped a company find which databases were accounting for the greatest load. We collected slow-query logs, and then ran a report with pt-query-digest. Except we subtotaled by db, instead of by query fingerprint.

$ pt-query-digest --group-by db mysql-slow.log

There are lots of other options in this tool, so you could filter or sort on the total IOPS, or total number of temp tables, or total duration of queries, etc.

Once you identify the worst db, move it off your server until its owner learns to write more reasonable SQL queries.


PostgreSQL equivalent of SQL Server SQL_VARIANT_PROPERTY

The fact that pg_typeof doesn't show the typmod is frustrating.

To get the fully qualified type you can query the system catalogs. Let's look at how psql does it using psql -E:

$ psql -E regress
psql (9.2.1)
Type "help" for help.

regress=> CREATE TABLE typmodtest ( a numeric(16,2), b varchar(32) );
CREATE TABLE
regress=> \d typmodtest

... tons of information printed, including this query which produces the information we want:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '24641' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

This query can clearly be trimmed if you're not interested in the field collation, NOT NULL status, etc. We can also change the attrelid filter from an oid to a regclass filter:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'typmodtest'::regclass
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

producing:

 attname |      format_type
---------+-----------------------
 a       | numeric(16,2)
 b       | character varying(32)

This will only work on relations. I'm not aware of a reliable, generic way to get the typmod-qualified type for any result set, and it's a serious irritation. Among other things it makes it hard for the PostgreSQL JDBC driver to be compliant.


Can SQL server deny connections until the AG failover has completed ?

There is not a built in option to do this directly from AlwaysOn.  You would have to go with something a bit custom.  Depending on your setup, you could do something as simple as a job or a trigger scanning for changes in alwayson state.  Which then disables/enables the login for the application during a failover. But then you may see other errors in your log for the failed login attempt.


Getting 2 columns from sets of 2 rows in Postgresql

You can use a self-join for that:

select ni.date,
       ni.stock_id,
       ni.report_value as net_income,
       nr.report_value as total_revenue
from report_items ni
  join report_items nr
    on ni.stock_id = nr.stock_id
   and ni."date" = nr."date"
   and nr.report_item = 'totalRevenue'
where ni.report_item = 'netIncome'
order by date desc limit 100


SQL Server 2012 Explicit transactions taking time with Always on Setup

Got all the answers to my question mentioned above. If anyone facing the same problem I would like to point them to the answer :
Here's the answer to all the questions why explicit commit takes time :

[A commit transaction (FlushToLSN/StartLogFlush) can trigger similar behavior, even when the block is not full, so a commit transaction does not have to wait for the block to become full.]

Please find more details on following link :
https://support.microsoft.com/en-gb/kb/230785]

Thank you.


What is the most effective way to compress and store a SQL Server backup?

I've been doing some testing of different methods for compressing and storing MS SQL Backups (using MS SQL 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms.

Since you are using SQL 2008 R2 Enterprise edition, you can/must leverage

Data Compression - Row and Page compression. at Data level
Backup Compression at taking backup to minimize the disk footprint of the backups.

USE master;
GO
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;

Backup compression uses CPU cycles to compress the data before it leaves the server, and that’s why in the vast majority of scenarios, compressed backups are faster than uncompressed backups.

Note that when you use Open source tools you need to uncompress the database backup file before you can start the restore process it self.

e.g: When you receive a SQL database backup of 50 Gb which is compressed to 5 GB. To restore this database, you need much more diskspace:

5 Gb for the zip file
50 Gb for the backup file
50 Gb for the restored database. (assume their is no empty space in the database)

In total 105 Gb of diskspace is needed.

You can still use opensource compression tools like gzip , 7Zip, bzip2 or QuickLZ after the backup compression to benefit.

Also, have a look at MSSQL Compressed Backup on codeplex.

Good references for comparison stats

File Compression in the Multi-Core Era.
comparison of compression methods for database dumps


Is a Named Instance more Secure than the Default Instance?

Not really.

The SQL Browser service is always port 1434: can't be changed. So you need to know the port first before you disable (can't resolve it). Clients then have to know the port which can be packet sniffed.

You can make any SQL Server more secure my enforcing SSL encryption, reducing Surface area, network zoning, proper Group Policy and SQL Server security etc. But not by using Named Instances


Fillfactor is ignored when non-clustered index added

Fillfactor can be set once over the server (so it will be used in all newly created indexes unless another FF is explicitly specified in CREATE INDEX statement) or it can be defined individually for each index.

So if you want your non-clustered index to have FF = 90 just specify it when creating it.

FF of clustered index has no relation to FF of non-clustered indexes defined over that clustered index.

In other words, when creating an index, the specified FF is used. If nothing is specified, server default FF is used. In your case server default FF is 100%.

............................................................................

It seems I understood your question only now.
You are asking about why don't they maintain FF when INSERTs happen to your table.

Here is documentation:

Specify Fill Factor for an Index

  The fill-factor option is provided for fine-tuning index data storage
  and performance. When an index is created or rebuilt, the fill-factor
  value determines the percentage of space on each leaf-level page to be
  filled with data, reserving the remainder on each page as free space
  for future growth. For example, specifying a fill-factor value of 80
  means that 20 percent of each leaf-level page will be left empty,
  providing space for index expansion as data is added to the underlying
  table. The empty space is reserved between the index rows rather than
  at the end of the index.

So, fillfactor (the percentage of fullness of the leaf level) is maintained only when you create or recreate your index. When you subsequently use your table (make INSERTs or UPDATEs) your index leaf level becomes full. It's normal, It does not depend on non-clustered index existence, it's just how it should be. The empty space created at index creation time serves to mitigate page splits, but if you want to maintain it just to maintain empty space it just has no sense.

UPDATE

  It would seem that if an empty table only has a clustered index then
  SQL will maintain fill factor on initial population but not if it has
  a non-clustered index as well.

You still do not understand how FF works.
When you define it on an index, i.e. the index is created, the FF is respected.
This index will be populated with this FF in mind.
The next time it will be respected is only when the index is rebuilt.

So when you define FF on a table containing rows, these rows will be put in index with respect of the FF defined.

If you define the FF in an index created on empty table, nothing happens, there are no rows, how can you leave empty space between them?

I wrote a small repro where you can clearly see that no FF is maintained when you define it in the index created on an empty table.

This does not depend on any non-clustered index, it's just how FF works:

create table dbo.nums_guid (id uniqueidentifier, n int);
create unique clustered index ix_id on dbo.nums_guid(id) with (fillfactor = 50);

insert into dbo.nums_guid (id, n)
select newid(), n
from s1057.dbo.nums;

create table dbo.nums_guid_1 (id uniqueidentifier, n int);
create unique clustered index ix_id on dbo.nums_guid_1(id) with (fillfactor = 50);
create unique nonclustered index ix_n on dbo.nums_guid_1(n);

insert into dbo.nums_guid_1 (id, n)
select newid(), n
from s1057.dbo.nums;

select object_name( object_id) as tbl_name,
       object_id,
       index_id,
       page_count,
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0

select object_name( object_id) as tbl_name,
       object_id,
       index_id,
       page_count,
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid_1'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0

There are 2 tables in my repro, both with FF = 50 defined: one has a clustered index only and another clustered + non-clustered indexes.
In both cases clustered index is defined on empty table.
When I do my INSERT, no FF is maintained:

And only after rebuild I'll have FF = 50 on both clustered (and still it does not depend on existence of non-clustered index):

--------
alter index ix_id on dbo.nums_guid rebuild;
alter index ix_id on dbo.nums_guid_1 rebuild;

select object_name( object_id) as tbl_name,
       object_id,
       index_id,
       page_count,
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0

select object_name( object_id) as tbl_name,
       object_id,
       index_id,
       page_count,
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid_1'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0


Having Non-Numeric values as measures in Fact table

As you noted in the comments for your other question your fact table data seems to be something like this:

+------------+
+ Sick leave +
+            +
+ Comp off   +
+ Sick leave +
+            +
+------------+

This kind of depends on your ETL loading procedures and reporting requirements.

You essentially have 2 options

Create separate measures

To achieve this you need to have to create 4 columns in  your fact table and have the corresponding value set to 1 or 0.

For example

+--------------------------------------------------------+
| Casual Leave | Sick Leave | UnInformed Leave |Comp off |
+--------------------------------------------------------+
| 1            | 0          | 0                |0        |
| 0            | 0          | 0                |0        |
| 0            | 1          | 0                |0        |
| 0            | 0          | 1                |0        |
| 0            | 0          | 0                |0        |
| 0            | 0          | 0                |1        |
+--------------------------------------------------------+

Then you can have a measure simply using a sum on all four columns to end up with 4 measures.

Create an extra dimension

You could create an extra dimension table for LeaveType like this:

+-----------------------------+
| Type_key | Type_Label       |
+-----------------------------+
| 0        | None             |
| 1        | Casual leave     |
| 2        | Sick leave       |
| 3        | Uninformed leave |
| 4        | Comp off         |
+-----------------------------+

And then in your ETL you can determine which type of leave it is.

+---------------------------+
| Leave_Type   | Leave_Days |
+---------------------------+
| 1            | 1          |
| 0            | 0          |
| 2            | 1          |
| 3            | 1          |
| 4            | 1          |
| 0            | 0          |
+---------------------------+

Then you can link that dimension to your fact table.

Decision points

The ease of implementation depends on your ETL process (do you have one?). One can be easier to implement over the other.

The behavior of the result is also a bit different. For example with multiple measures you have to add every measure to your report, with a dimension you have to put the dimension on an axis.

It also has an impact on possible ways to do MDX calculations, filtering and so on.

I cannot decide in your place what the best option is and would suggest you do a small proof of concept and play around with it to see what suits you best.

For example if you put your dimension on the horizontal axis in excel the leave types with no values will not be shown.
If you have several measures you will have an empty column the length of your report.

Another example is maintainability. Depending on your tooling and possible ETL setup, how much work is it to add a new leave type 'child sick'.


Best way(s) to time ad hoc queries and stored procedures

Profiler is easier to understand (all in one line) and will capture hidden IO, CPU etc in UDFs (scalar and multi-statement TVFs)

SET STATISTICS TIME ON and SET STATISTICS IO ON will not show you IO and CPU from scalar UDFs and multi-statement TVFs: only the "outer" query statistics

From SO:

SQL Server Profiler discrepancy
Table Valued Function where did my query plan go?
Does query plan optimizer works well with joined/filtered table-valued functions?

And no doubt some folk will disagree with me, so here is Adam Machanic on the subject too

Scalar functions, inlining, and performance


Best practice for creating tablespaces in Postgresql

Tablespaces in PostgreSQL exist for some really particular needs (and I doubt a less than 500 GB is in that case) and for SQL compliance.
If you plan to create tablespaces to store eventually everything on the same disk, please don't. If you plan to create tablespaces inside $PGDATA, please don't.

Tablespaces lead to more complex recovery operations (if you need one). You'll curse yourself later, trust me.

You'll find a lot of excellent advices in Christophe Pettus slides (PostgreSQL when it's not your job). The slide 27 is about tablespaces and why not using them.


SQL Query Optimizer - how does cost vary with resource availability

First, some background information

So far (until SQL Server 2014) the query optimiser will only take a few things into account:

The number of CPU cores available on the system
The current amount of memory in use

CPU cores: The core count assigned to SQL Server from the affinity mask determines how efficient it can possibly be to create a parallel plan. For example, on a system with 4 cores, you can expect a 4x speedup when switching to a parallel plan, whereas a system with 2 cores only get a doubling. Because of this, the core count can affect the choice of plan. Unfortunately, not every query can scale linearly with the core count, yet the optimiser will believe it does. This means that on machines with more than around 12-16 cores, getting more parallelism will actually SLOW the query down. The speed of the CPU is not taken into account, only the number of cores.

Memory Available: When the plan is made, the amount of memory available is taken into account. This determines strategies like hash joins, sort space and other memory intensive operations. Mis-estimation here is very dangerous and can lead to poor performance. Especially if you over estimate the memory available for a hash join and have to spill into tempdb.

Your specific case

Without measurements of your system it is difficult, if not impossible, to know exactly what happened in your scenario. There are too many variables that potentially changed at the same time. It may simply be that something else has changed in the environment. Any diagnosis is pure guesswork and real DBA work is a science, not an arty exercise in guessing.

You would need to collect the following to get knowledge.

The query plans before/after
Wait stats
Exact machine configuration (both for the host and the VM in the virtualised scenario)


You are right in that audits of "audit" statements need to be made at the object level.
Using Traditional Auditing, you can use a query to generate a script to enable audit tracking for existing objects:
select 'audit audit on '||owner||'.'||object_name;' from dba_objects where owner='[owner]';

After that, or ideally when you first configure your database and before you install any schemas, you can set auditing of audit to happen by default for all future created objects with:
audit audit by default;

With Unified Auditing you can do something like this:
create audit policy audit_actions actions audit table, audit view, audit procedure, audit package, audit directory;
audit policy audit_actions;


Can't connect to new instance of SQL Server

You have installed a new instance of SQL Server, so you need to change the way you connect to that new instance.

Previously, you had a default instance, so connecting using any of these in the Server name: box would work fine:

localhost
.
127.0.0.1
<any_local_IP>
<your_machine_name>
<your_FQDN>

Now that you have installed a new instance of SQL Server, you need to reference that instance name as part of the connection string, so any of the above need to become the following:

localhost\MSSQLSERVER14
.\MSSQLSERVER14
127.0.0.1\MSSQLSERVER14
<any_local_IP>\MSSQLSERVER14
<your_machine_name>\MSSQLSERVER14
<your_FQDN>\MSSQLSERVER14


Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table.

UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you want to store 1 byte character in UTF8 it takes 1.5 bytes (1*1.5=1.5).

For further details, see:

AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]


Execute Queries Against CMS within SSRS Reports?

This is not possible using SSRS, but you can use powershell to read from CMS and then use the SQLCMD cmdlet to execute queries.


How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE?

If you use EXPLAIN you'll see the ordering. For a random plan I concocted with some throwaway data and similar query (ORDER BY ... LIMIT 1 FOR UPDATE) I got the plan:

 Limit  (cost=33.09..33.10 rows=1 width=26)
   ->  LockRows  (cost=33.09..39.88 rows=543 width=26)
         ->  Sort  (cost=33.09..34.45 rows=543 width=26)
               Sort Key: t_id
               ->  Seq Scan on m  (cost=0.00..30.38 rows=543 width=26)
                     Filter: (b_id <= 33)
(6 rows)

Here, you can see the rows are potentially locked before the limit is applied.

Now, in practice, the first row that gets matched will generally get locked and returned, causing LockRows on the later rows to get skipped ... but there's no guarantee of this. So you shouldn't rely on it.

In general, mixing row-locking and LIMIT is a bad idea that should generally be avoided.

If you must do it, you'll want to do something like:

SELECT *
FROM my_table
WHERE id = (SELECT id FROM my_table
            WHERE mywhereclause
            ORDER BY ... LIMIT 1)
  AND mywhereclause
FOR UPDATE;

where you force a target row to be found, and only then locked.

You should repeat the WHERE clause in the outer query to ensure that the row still matches the predicate after any lock-wait occurs. Otherwise what can happen is that the inner query finds the row, returns the ID, and you try to lock the row with that ID. PostgreSQL sees that someone else has a lock on that row, so it waits for that lock to be released. Then it re-checks the WHERE clause on the outer query to make sure it still matches (in case the row was deleted, or was UPDATEd). Because the inner query is an uncorrelated subquery it won't re-calculate it, though, so the inner WHERE clause doesn't get re-run, and you can get rows that no longer match the inner WHERE clause returned if you don't repeat it in the outer WHERE clause.

Mixing row limits and row locking is hard. Building correct queuing systems that actually perform better than a single worker is even harder. Use an off the shelf queuing system and save yourself a lot of hassle.

(PostgreSQL 9.5 will have SKIP LOCKED, which makes this way easier, but there won't be a stable release of it for over a year from now, so don't hold your breath.)


Using EXCEPT in a recursive common table expression

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows:

Split the CTE expression into anchor and recursive members.
Run the anchor member(s) creating the first invocation or base result set (T0).
Run the recursive member(s) with Ti as an input and Ti+1 as an output.
Repeat step 3 until an empty set is returned.
Return the result set. This is a UNION ALL of T0 to Tn.

Note the above is a logical description. The physical order of operations can be somewhat different as illustrated here

Applying this to your CTE I would expect an infinite loop with the following pattern

+-----------+---------+---+---+---+
| Invocation| Results             |
+-----------+---------+---+---+---+
|         1 |       1 | 2 | 3 |   |
|         2 |       4 | 5 |   |   |
|         3 |       1 | 2 | 3 |   |
|         4 |       4 | 5 |   |   |
|         5 |       1 | 2 | 3 |   |
+-----------+---------+---+---+---+

Because

select a
from cte
where a in (1,2,3)

is the Anchor expression. This clearly returns 1,2,3 as T0

Thereafter the recursive expression runs

select a
from cte
except
select a
from r

With 1,2,3 as input that will yield an output of 4,5 as T1 then plugging that back in for the next round of recursion will return 1,2,3 and so on indefinitely.

This is not what actually happens however. These are the results of the first 5 invocations

+-----------+---------+---+---+---+
| Invocation| Results             |
+-----------+---------+---+---+---+
|         1 |       1 | 2 | 3 |   |
|         2 |       1 | 2 | 4 | 5 |
|         3 |       1 | 2 | 3 | 4 |
|         4 |       1 | 2 | 3 | 5 |
|         5 |       1 | 2 | 3 | 4 |
+-----------+---------+---+---+---+

From using OPTION (MAXRECURSION 1) and adjusting upwards in increments of 1 it can be seen that it enters a cycle where each successive level will continually toggle between outputting 1,2,3,4 and  1,2,3,5.

As discussed by @Quassnoi in this blog post. The pattern of observed results is as if each invocation is doing (1),(2),(3),(4),(5) EXCEPT (X) where X is the last row from the previous invocation.

Edit: After reading SQL Kiwi's excellent answer it is clear both why this occurs and that this is not the whole story in that there is still loads of stuff left on the stack that never can get processed.

  Anchor Emits 1,2,3 to client Stack Contents 3,2,1

  3 popped off stack,  Stack Contents 2,1

  The LASJ returns 1,2,4,5,  Stack Contents 5,4,2,1,2,1

  5 popped off stack,  Stack Contents 4,2,1,2,1

  The LASJ returns 1,2,3,4  Stack Contents 4,3,2,1,5,4,2,1,2,1

  4 popped off stack,  Stack Contents 3,2,1,5,4,2,1,2,1

  The LASJ returns 1,2,3,5  Stack Contents 5,3,2,1,3,2,1,5,4,2,1,2,1

  5 popped off stack,  Stack Contents 3,2,1,3,2,1,5,4,2,1,2,1

  The LASJ returns 1,2,3,4  Stack Contents
  4,3,2,1,3,2,1,3,2,1,5,4,2,1,2,1

If you try and replace the recursive member with the logically equivalent (in the absence of duplicates / NULLs) expression

select a
from (
    select a
    from cte
    where a not in
    (select a
    from r)
) x

This is not allowed and raises the error "Recursive references are not allowed in subqueries." so perhaps it is an oversight that EXCEPT is even allowed in this case.

Addition:
Microsoft have now responded to my Connect Feedback as below

  Jack's guess is correct: this should have been a syntax error;
  recursive references should indeed not be allowed in EXCEPT clauses.
  We plan to address this bug in an upcoming service release. In the
  meantime, I would suggest to avoid recursive references in EXCEPT
  clauses.

  In restricting recursion over EXCEPT we follow the ANSI SQL standard,
  which has included this restriction ever since recursion was
  introduced (in 1999 I believe). There is no widespread agreement over
  what the semantics should be for recursion over EXCEPT (also called
  "unstratified negation") in declarative languages such as SQL. In
  addition, it is notoriously hard (if not impossible) to implement such
  semantics efficiently (for reasonably sized databases) in an RDBMS
  system.

And looks like the eventual implementation was made in 2014 for databases with compatibility level of 120 or higher.

  Recursive references in an EXCEPT clause generates an error in compliance with the ANSI SQL standard.


Subselect dependent on first select value

You can use 2 subqueries, one joining with the contact_id and another joining with the alt_contact_id. Then UNION them and ORDER BY:

SELECT cont.*, cred.*, 'main' AS contact_type
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.contact_id
WHERE cont.contact_id = 1

UNION ALL

SELECT cont.*, cred.*, 'alternate'
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.alt_contact_id
WHERE cont.contact_id = 1

ORDER BY contact_id ;

If you want this for all contacts, just remove the two WHERE clauses.


Getting the page name from a web URL in Oracle

For something more complex you might need a regular expression, but in this case, the following should suffice.

SELECT DISTINCT SUBSTR(URL,INSTR(URL,'/',-1)+1)
FROM TABLE1
WHERE URL IS NOT NULL
ORDER BY 1;

Sample data.

create table table1 (URL Varchar2(1000));
insert into table1 values ('Test1/Admin/Page7.asp');
insert into table1 values ('Test1/One/Page3.asp');
insert into table1 values ('Test1/Another/Page2.asp');
insert into table1 values ('Testing1/Admin2/Page12.asp');
insert into table1 values ('Page7.asp');
commit;


CPU,Memory and disk I/O utilization for each query in mysql

Using IOTOP command you can see CPU,Memory and disk I/O utilization for each query. For each query mysql generates an ID. You can find CPU,Memory and disk I/O utilization against your query ID.

How ever you may find more command in detail at http://www.dbatodba.com/db2/useful-aix-commands/monitor-cpu-mem-and-i-o-usage/

SET profiling = 100
-- Now you can find query ID and running sql query by
SHOW PROFILES; IF you want TO CHECK IN detail FOR ANY QUERY, run this QUERY-
-- SHOW PROFILE FOR QUERY QueryID;
-- If you want to check CPU USAGE for any query, run this query-
SHOW PROFILE CPU FOR QUERY QueryID;


There is no similar option assuming that "db1" and "db2" are actually Oracle databases.

What SQL Server calls a "database" is roughly equivalent to what Oracle calls a "schema".  In Oracle, you would generally only run one database on a server though that database may have many different schemas to support multiple applications.  Each Oracle database has a completely separate set of users.

You could create a database link in db2 that connects to db1 and allow the test user to use that database link to access objects in schemas on the db1 database.  Going further, you could use that database link to replicate data to db2 from db1 so that you could query it locally.  That's as close as you'll get to exposing data in one database to a user in another database.

Of course, from your description, it is entirely plausible that you merely have two schemas in the same database in which case you can freely grant access on objects in any schema to any user using the appropriate GRANT statement.


How do I check if a VARCHAR column has control characters in it?

Well I found others that might have had a similar questions,

Before I go on... dont forget about Collation.

That alone can make your life a nightmare,
If you have differences between Server level / Database level / Column level / Expression level, can cause strange behaviour and incorrect query results,if not properly managed.

I would suggest a regex expression to find what you are looking for.

Found on: How to compare Unicode characters in SQL server?

SELECT COUNT(*) FROM t_question WHERE question LIKE N'%é%'

Found on: How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?:

CREATE TABLE dbo.Numbers
(
    number  INT NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (number)
)
GO
DECLARE @i INT

SET @i = 0

WHILE @i < 1000
BEGIN
    INSERT INTO dbo.Numbers (number) VALUES (@i)

    SET @i = @i + 1
END
GO

SELECT *,
    T.ID, N.number, N'%' + NCHAR(N.number) + N'%'
FROM
    dbo.Numbers N
INNER JOIN dbo.My_Table T ON
    T.description LIKE N'%' + NCHAR(N.number) + N'%' OR
    T.summary LIKE N'%' + NCHAR(N.number) + N'%'
and t.id = 1
WHERE
    N.number BETWEEN 127 AND 255
ORDER BY
    T.id, N.number
GO

Some Documentation from Microsoft: Pattern Matching in Search Conditions

And alternatively: Search Text with Regular Expressions

In my experince with mysql there are build in functions like so:

SELECT '\0x01' REGEXP '[[:graph:]]'; -- returns true
SELECT '\0x01' REGEXP '[[:print:]]'; -- returns false

As a start, I Hope this helps!


You might be better off and find it easier to query Active Directory with a CLR stored procedure or CLR function. See this walkthrough on how to do so, as this is how I've done it in the past. Also someone else made one already you can look into (but be careful installing CLR assemblies from outside sources).


Why more (and varying number of) logical reads with read ahead (prefetch)?

The query plan for the ORDER BY l_partkey example almost certainly reads the nonclustered index in order (with read-ahead), followed by a Key Lookup to retrieve the uncovered columns.

The Nested Loops Join operator above the Lookup probably uses additional prefetching (WithOrderedPrefetch) for the Lookup. See the following article I wrote for full details:

Nested Loops Prefetching

Also, as I mentioned in answer to the linked Q & A, the number of read-ahead reads depends on timing and storage subsystem characteristics. The same sort of considerations apply to Lookup prefetching at the Nested Loops Join.

Note that SQL Server issues read-ahead for pages that might be needed by the index scan, but this is not limited by the TOP specification in the query. The TOP is a query processor element, whereas the read ahead is controlled by the Storage Engine.

The activities are quite separate: read-ahead (and prefetching) issue asynchronous I/O for pages that may be needed by the Scan (or Lookup).

Depending on the order in which I/Os actually complete and make rows available to the query processor (among other things), the number of pages actually touched (logical reads) or physically read may vary. Note in particular that Lookup delayed prefetch also contributes to the logical reads when it checks to see if a page needed for the Lookup is already in memory or not.

So, it all comes down to the detailed timing of overlapping operations: The query processor will start to shut the query execution pipeline down as soon as the required number of rows (100) has been seen at the Top iterator. Quite how many asynchronous I/Os (read-ahead or prefetch) will have been issued or completed at that point is essentially non-deterministic.

You can disable Nested Loops Join prefetching with trace flag 8744 to explore this further. This will remove the WithOrderedPrefetch property from the Nested Loops Join. I usually use OPTION (QUERYTRACEON 8744) on the query itself. In any case, you need to be sure you're not reusing a cached plan that has the prefetch. Clear the plan cache each time or force a query recompilation with OPTION (RECOMPILE).

Logical reads is a simple measure of the number of cache pages touched on behalf of the query. Where read-ahead (and/or prefetching) is enabled, more (and different!) index and data pages may be touched in order to issue that read-ahead or as part of the prefetching activity.


How do I select MAX/MIN from multiple tables, along with other columns from MIN/MAX row

As a another option

WITH Ago AS (
    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
    ),
    Ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits, Date) LowUnitRank,
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits DESC, Date) HighUnitRank
    FROM Ago
    )
SELECT L4.UCC12UPCCode,
    MAX(CASE WHEN LowUnitRank = 1 THEN TotalUnits END) LowTotalUnits,
    MAX(CASE WHEN HighUnitRank = 1 THEN TotalUnits END) HighTotalUnits,
    MAX(CASE WHEN LowUnitRank = 1 THEN Date END) LowUnitDate,
    MAX(CASE WHEN HighUnitRank = 1 THEN Date END) HighUnitDate
FROM RTG_LOOKUP_LAST4 L4
    INNER JOIN Ranked r ON L4.UCC12UPCCode = r.UCC12UPCCode
GROUP BY L4.UCC12UPCCode;

I've done this using a CTE query as I find it easier to follow and explain.

The first CTE is just your Ago data.

The second CTE ranks each row of the Ago data using ROW_NUMBER based on the ordering of the TotalUnits.  I have include the Date in the order so that if there are duplicate TotalUnits the first occurrence will be picked.

In the final query we are using CASE statements to pivot the data and aggregating the result for each UCC12UPCCode with MAX to create a single row for each UCC12UPCCode.

This query can be run over multiple UCC12UPCCode's


Unclear.

What does drink_no signify?
You should not try to maintain a "running count" of how many drinks a person has consumed on a given day.  Instead, simply store that they have consumed another drink and let your queries do the "adding up":

select
  drinker
, odate
, count( odate ) total
from table1
group by drinker, odate
order by drinker, odate ;

+---------+------------+-------+
| drinker | odate      | total |
+---------+------------+-------+
| Claude  | 2020-01-12 |     4 |
| Claude  | 2020-01-15 |     2 |
| Claude  | 2020-01-19 |     1 |
| Claude  | 2020-04-04 |     1 |
| Claude  | 2020-04-05 |     1 |
| Claude  | 2020-04-12 |     1 |
| Claude  | 2020-04-15 |     1 |
| Claude  | 2020-04-16 |     1 |
| Claude  | 2020-04-17 |     1 |
| Claude  | 2020-04-18 |     1 |
| Claude  | 2020-04-19 |     1 |
| Claude  | 2020-04-20 |     1 |
+---------+------------+-------+

Also, please tell me that odate is an actual Date Data Type.

Never store Date values in anything else.  Doing so will make date manipulation far, far more difficult and, potentially, much slower.


How to take a remote SQL server database backup to local machine?

try to use an unc path in this way:
sqlcmd -S xxx.xxx.xxx.xxx -U <username> -P <password> -Q "BACKUP DATABASE [TestUser] TO DISK='\\server\shared_folder\TestUser.bak';"

You have to give SQL Server Service User the right to write to the remote path
\\server\shared_folder\
To do this you have to configure a local samba share connected to the source domain (as a domain member)


where is memory going in In-Memory OLTP?

Memory for In-Memory OLTP is under the sp_configure setting for max memory, but is not part of the buffer pool. Not sure if that helps or not. You could have a lot of stale rows that are waiting until there is memory pressure in order to be "garbage collected". I wrote a script that's part of Brent Ozar's First Responder Kit that might be of some help to you, located here:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_BlitzInMemoryOLTP.sql


How to do a case-insensitive LIKE in a case-sensitive database?

You can append a new collation to your select query to find case sensitive or insensitive.

-- Case sensitive example
SELECT *
FROM TABLE
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

-- Case insensitive example
SELECT *
FROM TABLE
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero   from MS SQL Tips.

MSDN on Collate.


MySQL Workbench Automatically Capitalized and Reformatted Section of SQL Script

Have you looked at

Edit -> Preferences -> SQL Editor -> Query Editor?

There are a couple of "beautifier" and "capitalise" options there.


For SQL Server, you could argue that a commit operation is nothing more than writing LOP_COMMIT_XACT to the log file and releasing locks, which is of course going to be faster than the ROLLBACK of every action your transaction performed since BEGIN TRAN.

If you are considering every action of a transaction, not just the commit, I'd still argue your statement is not true. Excluding external factors, speed of log disk compared to data disk speed for example, it's likely the rollback of any work done by a transaction will be faster than doing the work in the first place.

A rollback is reading a sequential file of changes and applying them to in-memory data pages. The original "work" had to generate an execution plan, acquire pages, join rows etc.

Edit: The it depends bit...

@JackDouglas pointed to this article which describes one of the situations where rollback can take significantly longer than the original operation. The example being a 14 hour transaction, inevitably using parallelism, that takes 48+ hours to rollback because rollback is mostly single threaded. You would most likely also be churning the buffer pool repeatedly, so no longer are you reversing changes to in-memory pages.

So, a revised version of my earlier answer. How much slower is rollback? All other things considered, for a typical OLTP transaction it isn't. Outside the bounds of typical, it can take longer to "undo" than "do" but (is this a potential tongue twister?) why will depend on how the "do" was done.

Edit2: Following on from discussion in the comments, here is a very contrived example to demonstrate that the work being done is the major factor in determining the relative expense of commit vs rollback as operations.

Create two tables and pack them inefficiently (wasted space per page):

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET NOCOUNT ON;
GO

CREATE TABLE dbo.Foo
(
    col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , col2 CHAR(4000) NOT NULL DEFAULT REPLICATE('A', 4000)
)

CREATE TABLE dbo.Bar
(
    col1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , col2 CHAR(4000) NOT NULL DEFAULT REPLICATE('A', 4000)
)
GO

INSERT dbo.Foo DEFAULT VALUES
GO 100000

INSERT dbo.Bar DEFAULT VALUES
GO 100000

Run a "bad" update query, measuring time taken to do work and the time taken to issue the commit.

DECLARE
    @StartTime DATETIME2
    , @Rows INT

SET @Rows = 1

CHECKPOINT
DBCC DROPCLEANBUFFERS

BEGIN TRANSACTION

SET @StartTime = SYSDATETIME()

UPDATE
    dbo.bar
SET
    col2 = REPLICATE('B', 4000)
FROM
    dbo.bar b
INNER JOIN
    (
    SELECT TOP(@Rows)
        col1
    FROM
        dbo.foo
    ORDER BY
        NEWID()
    ) f
ON  f.col1 = b.col1
OPTION (MAXDOP 1)

SELECT 'Find and update row', DATEDIFF(ms, @StartTime, SYSDATETIME())

SET @StartTime = SYSDATETIME()

COMMIT TRANSACTION

SELECT 'Commit', DATEDIFF(ms, @StartTime, SYSDATETIME())
GO

Do the same again but issue and measure rollback.

    DECLARE
    @StartTime DATETIME2
    , @Rows INT

SET @Rows = 1

CHECKPOINT
DBCC DROPCLEANBUFFERS

BEGIN TRANSACTION

SET @StartTime = SYSDATETIME()

UPDATE
    dbo.bar
SET
    col2 = REPLICATE('B', 4000)
FROM
    dbo.bar b
INNER JOIN
    (
    SELECT TOP(@Rows)
        col1
    FROM
        dbo.foo
    ORDER BY
        NEWID()
    ) f
ON  f.col1 = b.col1
OPTION (MAXDOP 1)

SELECT 'Find and update row', DATEDIFF(ms, @StartTime, SYSDATETIME())

SET @StartTime = SYSDATETIME()

ROLLBACK TRANSACTION

SELECT 'Rollback', DATEDIFF(ms, @StartTime, SYSDATETIME())
GO

With @Rows=1 I get a reasonably consistent:

5500ms for the find/update
3ms commit
1ms rollback

With @Rows=100:

8500ms find/update
15ms commit
15ms rollback

With @Rows=1000:

15000ms find/update
10ms commit
500ms rollback

Back to the original question. If you're measuring time taken to do work plus the commit, rollback is winning hands down because the majority of that work is spent finding the row to update, not actually modifying data. If you're looking at the commit operation in isolation, it should be clear that commit does very little "work" as such. Commit is "I'm done".


Does failed task considered to be run to completion in SQL Server maintenance plan terminology?

Completion is a task state that is true if:

a. The task state is Success.

b. The task state is Failure.

A task will never reach any of these states until a TaskStart event has caused the task to start executing. A disabled task, or a task that is never executed, will not reach a state of Completion.


MongoDB: Grant roles to multiple databases based on a pattern

Resource document for the latest mongo has db and test attributes which can be only fixed strings.

I have tried with various wildchar: *, ., % also escaping but with no luck.


Can I Set The Value of App_Name() AFTER Login?

What about using SET CONTEXT_INFO? It takes a varbinary(128) value, but you can easily cast that to/from varchar or whatever is appropriate. Read it back from sysprocesses.

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


Trigger xp_LogEvent based on inserted cell value

Let's start with the main issue of this trigger:

SET @@Message = (SELECT STAT_MESSAGE FROM dbo.TABLE_NAME)

This will produce the following error

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.

as soon as your INSERT statement will have more than one row.

Others are more suggestions than corrections.

It's a good practice to check if the insert did insert any row and
if not just exit the trigger (if @@rowcount = 0 return;)
You also should not execute the code that reads from inserted
twice.
Finally, I changed it to read only the first occasion of
STAT_MESSAGE LIKE '%bcp.exe%' (select top 1) but you should think
about the case when multiple rows LIKE '%bcp.exe%' are inserted: if
you want to log all of them you should use a loop.

Here is your trigger modified:

alter TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS
BEGIN
if @@rowcount = 0 return;

SET NOCOUNT ON;

DECLARE @@Message varchar(255);

SELECT @@Message = (select top 1 STAT_MESSAGE
                    FROM INSERTED
                    where STAT_MESSAGE LIKE '%bcp.exe%')
if @@Message is NULL return;

EXEC xp_logevent 60000, @@Message, ERROR;
END;


MS SQL Server Database Option - Transaction Log

The transaction record not important because I can retrieve from my source again if needed.
. . .
What database recovery mode should I set

SIMPLE.  Then shrink the log one time.


One SQL Server. One SSIS. Two versions of DTExec.exe?

No.

SSD-BI edition is now a free download. That license allows you to develop SSIS packages that target a 2012 SQL Server Integration Services machine. That license does not grant you the right to use it for production-like usage.

Furthermore, when you try to run a package from the command line, you're going to be met with an exception stating that ~ this is only for invocation through Visual Studio. The only way to make that go away is by installing the SQL Server Integration Services Service which is part of the 2012/2014 installation media.


SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0

This has now run successfully for ~1.5 weeks. The more I think about it I believe that the ssis error (posted in title) was a side effect, and was the result of the timeout caused by the dataload connection.

I can't prove or disprove if installing SP3 had any effect at all.

In my opinion the comment from @TheGameiswar is the best indicator pointing to root cause.


You have to escape the ].
That means before any character you want to literally search for, example % or _ or ], you have to add an escape character. For example \% to search for a literal %

Example:

CREATE TABLE #Test( text varchar(50) )

INSERT INTO #test  VALUES (']'),('atest'),('zz')

SELECT * FROM #test  WHERE text LIKE '[a\]]%' {escape '\'}

To read more about escape chars in LIKE, you can check :

LIKE Predicate Escape Character

You can also test it using SQL fiddle:
SQL Fiddle


How do I use the input from a \prompt in a conditional (\if)?

You can accomplish this using using \gset to coerce the variable into a boolean which \if can use,

$ cat if_prompt.sql
\prompt 'enter abc here: ' x
select ('abc' = :'x') as is_abc \gset
\if :is_abc
  \echo you can read
\else
  \echo you are illiterate or impertinent
\endif

Testing it,

$ psql test -f if_prompt.sql
enter abc here: abc
you can read

$ psql test -f if_prompt.sql
enter abc here: 123
you are illiterate or impertinent


Why am I getting a syntax error when trying to create a stored procedure in MySQL?

If you want to create a stored procedure, you have to use the CREATE PROCEDURE syntax, see the documentation. You can't use procedural language constructs such as DECLARE in normal SQL.

Also, you have to use the special delimiter you've declared after the final END, so the last line will be END\\. (And after that, don't forget to set DELIMITER back to ;, or you'll struggle.)

(There could be other problems as well, these are just two I spotted right away.)


PostgreSQL stored procedure which can return one of two custom rowtypes

Since your two types are composed from the same basic types in the same order, they are compatible with each other.  So the return type will be the same for functions using them - if I understand correctly, your problem is you want to have the different names of the returned columns depending on the type.

This cannot be done from the database, but you can easily do it from your application.  It can be done like (ugly pseudocode)

if type == 1 {
    execute("SELECT 1 AS id_normal_number, '00001' AS normal_number FROM your_function()")
} else {
    execute("SELECT 1 AS id_special_number, 'S0001' AS special_number FROM your_function()")
}

ie. changing only the names, but using the same DB function.  Or you can invoke two different functions, this way you can avoid dynamic SQL, which may be desirable for readability/performance, depending on some circumstances.


How to use db2look to get the ddl of sequence?

You want to use the -e option. That will export sequences along with a few other things. Depending on your sequences you could either use the -z option for schema or the -a option for all schemas. You may also want to use the -o option to output to a file.

We tend to use the following a lot where I work

db2look -d my_db_name -e -o my_db_name_look.sql

  EDIT: Based on the comments you have below, you can use db2look, or you could also use the system catalogs to retrieve what you need.

  Why not run the following:

select seqschema,seqname from syscat.sequences where cycle = 'N'

  That will retrieve those same sequences for you, but through querying
  and you won't have to use the db2look utility to retrieve everything
  you want. Of course if you want the DDL, you'd still have to use
  db2look to do that, but at least you can then use -z better to narrow
  down you schemas or do a search to find the names that came from your
  query.


Can a SELECT statement block an ALTER TABLE?

You got it right.

This is how PostgreSQL prevents conflicts: you cannot drop or modify a table while someone is using it.


No, Sql server doesn't let you turn off synchronous writes to the log file.  If you were able to do so, then you would lose ACID compliance.

If this is a "make it go as fast as possible" scenario, then a better approach is to analyze wait statistics to identify what the bottleneck actually is.  Risking data corruption is never a good idea.


Never Ending OLEDB Wait for Linked Server

For SQL Server targets:
On the target server (the one that's receiving the linked server query), run sp_WhoIsActive and look at the linked server query.

If the query doesn't show up, something's going awry with the linked server query and it's never reaching its destination.

If it does show up, look at the Wait column, and see what the wait type is. (It won't be OLEDB.) That'll help you troubleshoot the reason it's not completing. Feel free to post that in with your question, and I can elaborate more on that specific wait type.

For non-SQL Server targets like the IBM/Rocket one in this case: you'll want to work with the sysadmin/DBA on that platform, and have them check the progress of the query. When you see OLEDB on the SQL Server sending side, you just can't tell anything about what the holdup is on the other end.


Strange behaviour with Computed Columns in SQL-Server

2.Because nothing is stored for the computed column, I assume no
  index can be created for the column.

This assumption is not true - either kind can be indexed. The computed column must be deterministic in either case but when the computed column is persisted, the requirement that the computation is also precise is relaxed (ie it can involve floating point operations).

  How can an index be created for something that is not persisted and is
  this detrimental in the long run?

The result of the function is 'persisted' in the index in either case - the only difference is whether it is persisted in the table.


You have CONNECT DATA instead of CONNECT_DATA (space instead of underscore). Fix that, and try again.


What is the Always On Sync sync frequency?

Database replication does not occur on a specific timeframe--the primary replicates data to each secondary constantly.  If the secondary is asynchronous, the primary will replicate data to it as fast as it can.  If there are no bottlenecks, an asynchronous secondary will only be behind by what is currently in flight.

There are DMVs that provide statistics showing how much data is queued for each secondary and the last commit times.  There is a log send queue, which is basically transactions in the log on the primary that haven't yet been sent the secondary.  The other queue is the redo queue on the secondary, which is transactions that have been received and written to the log, but haven't yet been committed to the data file.

There are a number of articles, such as Measuring Availability Group synchronization lag, that have nice queries for quickly seeing how much data is in each of the queues, and any good monitoring software will also provide a view with this information.

Also, to be clear, your reference to "blocks" is incorrect as the database replication does not work on a block basis.  Database replication uses transactions.

So to answer your question about what you can do to minimize the amount of potential data loss, you simply have to make sure that the secondary has adequate storage performance to keep up, and that there is adequate network throughput to transfer data from the primary to the secondary as fast as it is generated.

One item to note is that bandwidth is not equal to trhoughput.  If you have a 100 Mbs link, you will not get 100 Mbs throughput.  If there is significant latency, your throughput will be much less. See  Why your maximum throughput is less than your bandwidth for a good explanation. WAN accelerators can be used if needed to overcome high latency on a link and provide better throughput.


SQL2012 - Send backup to client via SQL Job

Assuming that all the various security concerns are met; I would use SQL Agent to kick off a PowerShell Step that would move the file for you.  Depending on where you end up pushing the file to this can be easier or harder.

Depending on what version of SQL you are using, you also have the option of backing up directly to Azure Blob storage, builtin. More details here Backup and Restore with Azure Blob Storage


Finding empty columns of a table in PostgreSQL

testbed:

create role stack;
create schema authorization stack;
set role stack;

create table my_table as
select generate_series(0,9) as id, 1 as val1, null::integer as val2;

create table my_table2 as
select generate_series(0,9) as id, 1 as val1, null::integer as val2, 3 as val3;

function:

create function has_nonnulls(p_schema in text, p_table in text, p_column in text)
                returns boolean language plpgsql as $$
declare
  b boolean;
begin
  execute 'select exists(select * from '||
          p_table||' where '||p_column||' is not null)' into b;
  return b;
end;$$;

query:

select table_schema, table_name, column_name,
       has_nonnulls(table_schema, table_name, column_name)
from information_schema.columns
where table_schema='stack';

result:

 table_schema | table_name | column_name | has_nonnulls
--------------+------------+-------------+--------------
 stack        | my_table   | id          | t
 stack        | my_table   | val1        | t
 stack        | my_table   | val2        | f
 stack        | my_table2  | id          | t
 stack        | my_table2  | val1        | t
 stack        | my_table2  | val2        | f
 stack        | my_table2  | val3        | t
(7 rows)

In addition you can get an approximate answer by querying the catalog - if null_frac is zero that indicates no nulls but should be double-checked against 'real' data:

select tablename, attname, null_frac from pg_stats where schemaname='stack';

 tablename | attname | null_frac
-----------+---------+-----------
 my_table  | id      |         0
 my_table  | val1    |         0
 my_table  | val2    |         1
 my_table2 | id      |         0
 my_table2 | val1    |         0
 my_table2 | val2    |         1
 my_table2 | val3    |         0
(7 rows)


What is the best approach to index varchar and date range?

Seq Scan means sequential Scanning, PostgreSQL enum have to enum all records for filtering result.

You should use Btree index.
https://www.postgresql.org/docs/current/static/indexes-types.html

  B-trees can handle equality and range queries on data that can be sorted into some ordering


To take the Reporting Services databases out of commission on this server without disrupting the service itself, you'll need to point the service to databases elsewhere. To do this, open Reporting Services Configuration Manager on the server running the RS service, and change the "Database Name" entry in the Database menu page:

To shut down the Report Manager web portal, open up the RSReportServer.config file. This is located in the install directory (usually C:\Program Files\Microsoft SQL Server\[Instance Name]\Reporting Services\ReportServer).

Under the <Service> element, there will be several elements that begin with "Is". These control which services/functions are enabled. The last entry will be <IsReportManagerEnabled>.

Change this value from True to False. Save the file and close.

Some changes to RSReportServer.config require a service restart, so you may have to restart the RS service to get this change to stick. Once it does, the RS service will no longer respond to requests to the Report Manager portal but the separate web service that processes report requests will continue running.

You can read more about RSReportServer.config and switching individual services on/off here: https://msdn.microsoft.com/en-us/library/ms157273.aspx#bkmk_service


How do I set the option use_remote_estimate?

postgres_fdw doesn't use GUC variables. The settings for postgres_fdw are actually set with options under CREATE SERVER,

CREATE SERVER [IF NOT EXISTS] server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ];

Or with ALTER SERVER

  Change options for the server. ADD, SET, and DROP specify the action to be performed. ADD is assumed if no operation is explicitly specified. Option names must be unique; names and values are also validated using the server's foreign-data wrapper library.

Like,

-- Set it if it's never been set
ALTER SERVER flybase
  OPTIONS (ADD use_remote_estimate 'true');

-- Change it if it has been set
ALTER SERVER flybase
  OPTIONS (SET use_remote_estimate 'false');

-- Drop it if it has never been set
ALTER SERVER flybase
  OPTIONS (DROP use_remote_estimate);


An exchange spill (ExchangeSpillDetails) occurs only in response to an intra-query parallelism deadlock. SQL Server resolves the deadlock by forcing one or more of the exchanges (parallelism operators) to write its buffers to tempdb.

There is no concept of a spill "level" for an exchange spill (unlike sort or hash spills, which may require multiple passes or recursion). The unpopulated value is reported as zero.

You will generally want to avoid parallel deadlocks because they are resolved by the Resource Monitor, which by default only wakes up every five seconds. It will check for deadlocks more frequently than that if there has been a recent deadlock (of any kind) but it's never going to be great for performance.

One more general note: Parallel deadlocks occur due to dependencies between threads, almost always related to preserved ordering. Parallel merge join with order-preserving exchanges on both inputs are a good example of this.

For a cool visualization of an intra-query parallel deadlock, please see Grokking the Paul White Parallel Deadlock Demo by Forrest McDaniel.


How to join 2 tables when second table will return more rows?

So if I understand your examples correctly it looks like you want the unique list of NumerCzesci values from both tables, except when Zlozenie.NumerCzesci contains "ZŁOŻENIE". Then you want to use PodZlozenie.NumerCzesci instead. You should be able to achieve that with a FULL JOIN and a DISTINCT clause like so:
SELECT DISTINCT
    CASE
        WHEN Zlozenie.NumerCzesci LIKE '%ZŁOŻENIE%' OR Zlozenie.NumerCzesci IS NULL THEN PodZlozenie.NumerCzesci
        ELSE Zlozenie.NumerCzesci
    END AS NumerCzesci
FROM Zlozenie
FULL JOIN PodZlozenie
    ON Zlozenie.NumerCzesci = PodZlozenie.NumerPodZlozenia

(You can optionally add ORDER BY NumerCzesci to the end of the above query if you want the results sorted by NumerCzesci in ascending order.)


May be you didn't do a restore, but someone surely did, as the indicated tablespace state is only possible when a redirected restore has been initiated -- this is well described in the manual.

Normally to complete the redirected restore you will need to issue SET TABLESPACE CONTAINERS FOR 6 USING (PATH '/whatever'), where "6" is the tablespace ID, and similarly for all other tablespaces in this state, then run RESTORE DB yourdb CONTINUE to complete the operation (details here).

However, since you apparently don't recognize this restore operation, you'll probably want to abort it instead: RESTORE DB yourdb ABORT.


This may not directly answer your question, however you have multiple potential issues in your query, that are immediately visible to me.

AND Convert(DateTime,Valor) BETWEEN '2015-03-01' AND '2015-03-31'
AND DateDiff(day,Valor,GetDate()) > 15

Why are you doing CONVERT(DateTime, Valor) ?  Is Valor not a DateTime type in the table?  If not, you're doing that wrong.
Running a function on a column (DateDiff(day, Valor, GetDate())) and comparing that to a value makes it non-SARGable.  Try this, instead:

AND Valor > DATEADD(DAY, -15, GETDATE())
BETWEEN (x and y) may not function the way you think it does.  You're better off to use the following construct since it explicitly defines the precise date range you desire.

WHERE Valor >= '2015-03-01T00:00:00' AND Valor < '2015-04-01T00:00:00'
WITH (NOLOCK) is NOT a go-faster button!  See http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ and http://sqlperformance.com/2015/04/t-sql-queries/the-read-uncommitted-isolation-level for excellent details about why that is a non-starter most of the time.


How to add `--sslAllowInvalidCertificates` on mongodb uri?

There's a detailed post that might answer this question here:

Connecting to MongoDB over SSL with Node.js


Oracle scheduler max_jobs_Slave_processes is set to null, how many will be allocated?

NULL implies no limits. Anything other than NULL sets a limit.

See the documentation here.


How can I guarantee that inserts to SQL Server 2008 R2 are cached in RAM first?

Have you tried just writing and see what happens? Do you have a known bottleneck?

If you need to prevent your app being blocked then you one way would be to queue the writes to defer the database call. However, I'd expect the queue to clear in a second or 2: so do you need an queue if this is OK?

Or you can spool to a staging table and then flush later? We use this technique to deal with sustained writes of millions of new rows per minute (we actually use a staging DB with Simple recovery): but we didn't implement it until we had experience of just writing rows.

Note: Every write in SQL Server will go do disk as part of the Write Ahead Logging (WAL) protocol. This applies to the t-log entry for that write.

The data page with the row will go to disk at some point (based on time, use, memory pressure etc) but generally your data will be in memory anyway. This is called "Checkpointing" and doesn't evict data from memory, just flushes changes (edited 24 Nov 2011)

Edit:

For throughout considerations, based on ther last paragraph above, shift your LDF for this database to a dedicated set of disks for more performance. Ditto a staging database (one each for MDF/LDF). It's quite common to have a dozen or 3 different volumes (via a SAN normally) for your database server


Oracle becomes too slow after system crash

Several things might be going on here and might be difficult to pin down but here's a few things.

After Oracle crashes, it must do some type of recovery (as in media or crash recovery) to bring the database back to a consistent state.  Depending on what you were doing (and whatever else was going on in the instance at the time of the crash) transactions may need to be kept or rolled-back and this is done before the instance will open so you need to wait until it completes.

This should, however, only happen the first time you start the database after the crash.  If it happens every time afterwards, there may be something else going on. If your job needs to roll back data, depending on the amount of data, that too, can be a lengthy process which might be running while you are re-trying your operation and may slow down other operations.

Also, since the database has crashed, you've lost any data cached in the db blocks buffers as well as anything in the shared pool, so any operations needing access to data may now have to go to disk and any shared SQL in the shared pool may need to be re-parsed, and so on and so forth.

Before re-trying your operation, check all your objects (as in tables, indexes, etc.). If you can, do a little testing - truncate the tables to be loaded (and remove any indexes, constraints, etc.) before you try to re-load, because you don't want to have to wait for rows to be deleted that you're just going to re-insert anyway or for indexes that might be corrupt and slowing down insert operations.

Lastly, instead of SQL Developer, you might want to try using SQL Loader or load in smaller increments in case there's some type of data causing the crash (SQL Loader will leave a "bad" file of data it could not load).

Also, what's your hardware setup? Are you running Oracle and SQL Developer on the same machine?  If so, since you're trying to load 100 million rows, try splitting the load.  If you are running SQL Developer from a PC, make sure you're not running out of PC resources - 100 million rows for Oracle is not a big deal, but it might for your PC.


MySQL Regex matching newline or end of string

Found the solution myself:

SELECT * FROM table WHERE column REGEXP 'myValue(\r\n.*)?$'


Best way to drop / recreate a table that has dependent views?

If you have the luxory of exclusive access a very simple method is to draw a backup with pg_dump, reorder columns as you chose in the CREATE TABLE scripts of the dump and restore the database. Other parts of the dump do not depend on column order. For instance, COPY statements list columns explicitly.

If you don't have this luxory, the whole operation is a pain. You need an exclusive lock on the table while dropping and recreating it - and all depending objects like views in its tail.
And concurrent queries on the table will still produce exceptions because they wait for the lock to go away, but then the table is gone (the table name has been resolved to a particular OID early in the process).

The Postgres Wiki on "Alter column position" has some more advice.

Decoupling logical column ordering from physical column ordering has been discussed repeatedly on pgsql-hackers, there is an entry in the ToDo Wiki by the name:

Allow column display reordering by recording a display, storage, and permanent id for every column?

And efforts are being made to maybe get this into Postgres 9.6:


MySQL: sum time ranges exluding overlapping ones

Original

You want to sum up all time ranges, except those that fit inside other time ranges on the same day:

SELECT DATE_FORMAT(DAY, '%Y-%m-%d')
        ,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(
            SUM(TIMEDIFF(TIME_EXIT,TIME_ENTER))
          )), '%H:%i') AS Total
FROM Table1 AS t
WHERE NOT EXISTS (
  SELECT *
  FROM Table1
  WHERE DAY = t.DAY
  AND TIME_ENTER <= t.TIME_ENTER AND TIME_EXIT >= t.TIME_EXIT
  # not the same row; if you have an ID column, use it instead
  # eg AND NOT ID = t.ID
  AND NOT (TIME_ENTER = t.TIME_ENTER AND TIME_EXIT = t.TIME_EXIT)
)
GROUP BY DAY;

SqlFiddle

Edit: the above solution won't always work, as pointed out by ypercube. The following solution should cover all scenarios.

Revised solution

SELECT DATE_FORMAT(DAY, '%Y-%m-%d')
        ,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(
            SUM(TIMEDIFF(TIME_EXIT,TIME_ENTER))
          )), '%H:%i') AS Total
FROM (
  SELECT   DISTINCT t.DAY
        ,(SELECT MIN(TIME_ENTER) FROM Table1
          WHERE TIME_EXIT BETWEEN t.TIME_ENTER AND t.TIME_EXIT) AS TIME_ENTER
        ,(SELECT MAX(TIME_EXIT) FROM Table1
          WHERE TIME_ENTER BETWEEN t.TIME_ENTER AND t.TIME_EXIT) AS TIME_EXIT
  FROM Table1 t
  ) AS o
GROUP BY DAY;

SqlFiddle


Postgresql 9.4.1 stuck all queries when making multi updates

There was a bug with commit_delay in PostgreSQL 9.4.X. It was just fixed, and the fix will appear in version 9.4.5. See the thread on the PostgreSQL mailing list: http://www.postgresql.org/message-id/CACKh8C-tDjQUceMQeG3BUSHS5AA35SjvCD6HgRm-Vj7x5rXmeQ@mail.gmail.com


Stored procedure deadlocking itself, how do I resolve it?

Each of your 2 update statements may cause deadlock when executing within different transactions, no matter explicit or implicit.  They both change the same set of rows  , and nothing guaranties in which order the rows are locked.
One option is just to catch error and re-run failed transactions. Another approach is  to somehow "serialize" updates.

In my opinion, the easiest and fastest way is to modify procedure so it waits until the other instance of the same procedure completes updates.  It can be implemented, for instance, by using sp_getapplock.

Another option is to ensure that rows are locked in the same order (e.g. -open cursor that reads all required rows in particular (unique) order, iterate through it, and for each row issue SELECT WITH ROWLOCK), and issue update after all rows are locked.  Then in case of simultaneous execution the second transaction will wait for the first one. I think that for SQLServer a very little chance of deadlocks might exist because of potential lock escalation though.


SSRS 2012 Report with Oracle database as datasource never finishes

I would recommend the following:

1) Run the report and check Oracle's session (v$session) view to see if the Oracle database even registered the connection and the query was passed to it.

a) if YES, then check your report server's timeout settings. If the initial data return is elapsing the timeout period in either your report or your RS config file, this may likely be the reason for your timeout issue. You can check out report timeout periods in your data source configurations. You can check time out RS settings in your report server's config file.

b) if NO, you've got something else going that is more fundamental in connecting to the Oracle db and submitting the query.


Possible to have duplicate fully qualified schema names with separate data in DB2 for z/OS?

If the application is hard-coded with the qualifier, then it can only use that qualifier and SCHEMA.table-name is unique.

The best approach is rewrite the application to use unqualified table names.  If you do end up considering creating additional subsystems, you might consider zD&T.  This is z/OS running on Linux on x86 for dev/test environments.  https://developer.ibm.com/mainframe/products/z-systems-development-test-environment/


Why does sp_rename not affect the output of sp_helptext?

Don't know why but it is kind of documented.

sp_rename (Transact-SQL)

  Renaming a stored procedure, function, view, or trigger will not
  change the name of the corresponding object name in the definition
  column of the sys.sql_modules catalog view.

sys.sql_modules is a view that uses the base table sys.sysschobjs. sp_helptext uses the view syscomments that in turn uses the base table sys.sysschobjs.


Azure Analysis Service Cube Sizes Changed Since July

it should have resolved now, Microsoft mentioned that was bug which hit most of the regions , we are on WEST US and MS released patch to make the size look accurate now


Check out db2move - it uses export under the covers and offers options to load or import them into the target.

Details are documented here
db2move in KnowledgeCenter

An alternative - for those large volumnes - might be a "load from cursor".
Once you have created the new database and its objects (db2look) can help here you load the data into the new database. For this solution you have to create individual statements for each table.
Check out this article about load from cursor


No, the syntax you have is not valid, it can be corrected by the use of a CASE expression.
(and I guess you have a GROUP BY a, b as you'd get an error otherwise).

select
  a
  b,
  count(case when t1.u = 'UAE' then c else null end) as c1
from t1
group by a, b ;

Note that the ELSE NULL is redundant and can be removed as that is the default ELSE behaviour anyway:

select
  a
  b,
  count(case when t1.u = 'UAE' then c end) as c1
from t1
group by a, b ;

There is a (SQL Standard) FILTER syntax that is close to your attempt but SQL Server has not yet implemented it:

select
  a
  b,
  count(c) filter (where t1.u = 'UAE') as c1
from t1
group by a, b ;


It isn't always bad.

Of course it allows you to read uncommitted values (that may be rolled back and hence never logically existed) as well as allowing phenomena such as reading values multiple times or not at all.

The only isolation levels that guarantee that you won't encounter any such anomalies are serializable/snapshot. Under repeatable read values can be missed if a row is moved (due to a key update) before the scan reaches this row, under read committed values can be read twice if a key update causes a previously read row to move forward.

These issues are more likely to arise under nolock however because, by default, at this isolation level it will use an allocation ordered scan when it estimates there is more than 64 pages to be read. As well as the category of issues that arise when rows move between pages due to index key updates these allocation ordered scans are also vulnerable to issues with page splits (where rows can be missed if the newly allocated page is earlier in the file than the point already scanned or read twice if an already scanned page is split to a later page in the file).

At least for simple (single table) queries it is possible to discourage the use of these scans and get a key ordered scan at nolock by simply adding an ORDER BY index_key to the query so that the Ordered property of the IndexScan is true.

But if your reporting application doesn't need absolutely precise figures and can tolerate the greater probability of such inconsistencies it might be acceptable.

But certainly you should not be chucking it on all queries in the hope that is a magic "turbo" button. As well as the greater probability of encountering anomalous results at that isolation level or no results at all ("Could not continue scan with NOLOCK due to data movement" error) there are even cases where the performance with nolock can be much worse.


Why does jsonb use more space for storing integers?

It's simple. JSON doesn't have a concept of an integer, only a "number". From the docs

  When converting textual JSON input into jsonb, the primitive types described by RFC 7159 are effectively mapped onto native PostgreSQL types, as shown in Table 8-23.

That table shows a JSON "number" is mapped to a numeric. The max precision on numeric is

  up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

However, I don't think it actually stores that. It does store a numeric of some sort. The numeric source is quite frankly kind of difficult to follow.

Using an array of smallint is clearly better if it fits your workload. It has less wasted space for packing, and less wasted space for the data itself. smallint[] however

Does not support the same range of numbers.
Does not support embedding different types.
Does not support any precision at all.
Does not exchange well with web-clients.


Verify failed SQL Server connections to instances on a given server and email them

You can use server audit specification to track failed logins.
use ssis and a script task to read audit file and email it if needed.


auto-rehash is database, table and column completion. It isn't a generic syntax completion aid.


Group by's in materialized views and query planning

The feature is called 'indexed view matching' and it can be tricky to get right.  There are a number of prerequisites outlined here: Resolving Indexes On Views, plus the indexed view needs to be able to answer the question that your query is asking.  I would expect it to work in Developer/Enterprise Edition for straightforward scenarios, and I got it to work with a simple schema and query:

-- Indexed view matching
USE tempdb
GO

IF OBJECT_ID('dbo.BaseTable') IS NOT NULL DROP TABLE dbo.BaseTable
GO

CREATE TABLE dbo.BaseTable (

    rowId       INT IDENTITY PRIMARY KEY,

    slot        INT NOT NULL,
    someDate    DATETIME DEFAULT GETDATE(),
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
)
GO

-- Add dummy data
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.BaseTable ( slot, someDate, someData )
SELECT
    rn % 999,
    DATEADD( day, rn % 333, '1 Jan 2016' ),
    NEWID()
FROM cte

CHECKPOINT
GO --10

DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

CREATE VIEW dbo.IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;
GO

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);
GO

-- Indexed view matching
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

-- Indexed view not used as it can't 'cover' the query
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
AND rowId = 42
OPTION ( RECOMPILE );
GO

-- Query Indexed view directly with NOEXPAND hint
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView WITH ( NOEXPAND )
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

-- EXPAND VIEWS hint ensures Indexed view is NOT used.
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView
WHERE Slot = @slot
OPTION ( RECOMPILE, EXPAND VIEWS );
GO

Execution Plan:

There are a few other queries in my test script above and you can see the indexed view is not used for some of them.

Here are some quotes about indexed views from one of the architects of the query optimizer, Conor Cunningham:

  The Query Optimizer contains logic to use this index both in cases when
  the original query text referenced the view explicitly as well as in cases when the user submits a
  query that uses the same components as the view (in any equivalent order).

but ...

  [complexity] ... makes it difficult for the Query Optimizer to consider plans that perform
  the view evaluation first, then process the rest of the query. Arbitrary tree matching is a
  computationally complex problem, and the feature set of views is too large to perform this operation
  efficiently.

'Microsoft SQL Server 2008 Internals',
Chapter 8: The Query Optimizer - Conor Cunningham

  ...indexed views work for basic SPJG (SELECT-PROJECT-JOIN-GROUPBY) queries.  The joins must be inner joins, not self joins, and join on a key.   Each operator has a series of restrictions...

source

I think those quotes are quite telling.  You may also wish to consider filtered indexes.


Oracle SQL: How to give user the permissions to SELECT from a table?

Objects owned by other users are not listed under your objects in SQL Developer.

Expand Other Users, Owner (username), Tables, and you will find the table there.


How many tables are needed to implement the scenario depicted in my entity-relationship diagram?

Your answer is mostly correct, except Q is a dependent attribute of R3, not a table on its own.


MySQL dead-locking with UPDATE JOIN and INSERT SELECT statements

Add these indexes:

link_weight:  (link_id, start_at, weight) -- in this order
link:         (owner_id, id)              -- in this order
link_click:   (state, owner_id)           -- in this order
owner:        (owner_id, total_processed) -- in either order

If owner has PRIMARY KEY(owner_id) then don't add the owner index above.

The goal is to speed up processing to minimize deadlocks.  Since deadlocks probably cannot be completely eliminated, be read to rerun transactions when they do occur.

If that does not suffice, break up the UPDATE into two steps:

CREATE TEMPORARY TABLE raw
      ( INDEX(owner_id, total_processed) )
AS
    SELECT ...;   -- from your existing code
UPDATE owner o
  JOIN raw
    ON raw.owner_id = o.id
   SET o.total_pending = raw.total_pending,
       o.total_confirmed = raw.total_confirmed,
       o.total_awaiting_processing= raw.total_awaiting_processing,
       o.total_processed= raw.total_processed,
       o.total_invalid = raw.total_invalid;

If you need further help, please provide

EXPLAIN SELECT ...
EXPLAIN UPDATE ...
SHOW CREATE TABLE ...


The following logins will be created on installation. They are containers for the accounts that the SQL Server Database Engine & Agent run under.

 [NT SERVICE\MSSQLSERVER]

 [NT SERVICE\SQLSERVERAGENT]

The other accounts are groups that have been manually added into your SQL instance.

BUILTIN\Administrators was installed by default in SQL Server 2005 but not in SQL Server 2008 R2.

I'd look at the permissions on the existing server to see what accounts are using these groups and then determine if they are needed.


Database normalization: Which is the best fit?

Having the same types of columns is not the same thing as being the same entity type.  Normalization is not the same thing as code reuse.

Normalization is about arranging your columns into tables in such a way that you avoid insert, update and delete anomalies.  It is about reducing the kinds of redundancy that can lead to these anomalies, it isn't about putting things together because they look alike.

As a first choice, you should let your database engine use its declarative constraints (foreign keys, unique keys, etc.) to protect the integrity of your data.  This saves writing application code to do this and makes your system less buggy in the long run.

This means that you should aim for at least third normal form (3NF) by default and then consider later whether any denormalization is necessary.

From this perspective, your first option is not good, because you are jamming three different kinds of child data into one table.  You can't control referential integrity as cleanly or easily this way.  The only two conventional, practical options you have are 2 and 3.

Choosing between options 2 and 3 depends on the circumstances, and may depend quite a bit on your personal preferences.  Some people would avoid option 2 because they believe all nulls are evil.  Other people would avoid option 3 because they don't like having too many different tables.  This is a "holy war" debate without a definitive, logical answer.

For practical purposes, looking at your three main tables, I would say that they don't look like they have very much in common.  There is a little overlap, but not very much.  Therefore, I would tend towards option 3.  Keep your tables separate so that your code and your data stay clean.

As an aside: You don't show the details of the child table(s).  If these three child tables have an identical structure and you're worried about having application code that is duplicated, consider creating a class that handles the child table structure and inheriting from this class for each of the three child tables.  This will give you code reuse without compromising data integrity.


Why can't this mysql user log in with password?

Because you granted it access without password...
If a user has no password, logging in with one will always fail.

Set the password for user, only then you'll be able to use -p

In addition, you might want to remove the Any user.


How many rows can SQL Server process in a single INSERT statement into a table?

According to the documentation the only limitation on the number of rows stored per table is as below

  Limited only by available resources

What local resources does this potentially include?

Memory
Space in the database data files to physically write the inserted data to
Space in the database log files
Space in TempDB (Data and log)

As for how much a single SELECT statement can extract, I cannot find any documentation pertaining to this directly, but I would assume it is safe to take this to be exactly the same as the above.