Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server?

I ran the query with Visual Explain and the query completes in about 1 second.

Then it probably didn't fetch 2.5 million rows, which is likely your bottleneck.

  I am trying to identify the bottleneck here and I believe it is the INSERT.

Then try just running

select *
from OPENQUERY(linked_server, 'select * from linktable')

To eliminate the INSERT.

  running this as SELECT * INTO may increase performance

Test that too.  And test INSERT INTO with a Temp Table.

Using a 4-part name should not improve performance.


Add a column with name as a result from a SELECT

Solved my problem with the following function:
CREATE OR REPLACE FUNCTION client_schema.update_geometry_type(my_table text, my_schema text, OUT update_result boolean)
LANGUAGE plpgsql
AS $function$
   declare new_projection varchar(255);
   declare my_database varchar(255);
   declare old_projection varchar(255);
   begin
   CREATE extension if not EXISTS dblink;
   select split_part(current_database(), '-', 1) into my_database;
   select c.projection_key from client_schema.dblink('dbname=adm-3','SELECT projection_key, client_name FROM "adm-3".public.client as c ')
   AS c(projection_key text, client_name text) where c.client_name = my_database into new_projection;
   select Find_SRID(my_schema, my_table, 'geom') into old_projection ;
   select split_part(new_projection , ':', 2) into new_projection;
   EXECUTE 'ALTER TABLE '
   || my_schema::text || '.' || my_table::text
   || ' ALTER COLUMN geom type geometry(Polygon, ' || new_projection ||') USING ST_Transform(geom, ''EPSG:' || old_projection || ''', ' || new_projection || ')';
   END;
$function$
;


Look carefully at the error message

  ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB

What is error 64 ? perror says the following:

$ perror 64
OS error code  64:  Machine is not on the network

The ALTER TABLE command is create a temp file with the new compression. However, it is trying to create a temp file but it cannot write to the directory tmpdir is mapped to.

If tmpdir is mapped to a SAN that disconnected from the network, that error would make sense.
If tmpdir ran out of space, the error message makes no sense but the end result would still stop ALTER TABLE from completing its operation.

If this problem is repeatable, you may want to try doing this change manually.

create table positions_comp like positions;
alter table positions_comp KEY_BLOCK_SIZE=8;
insert into positions_comp select * from positions;
alter table positions rename positions_old;
alter table positions_comp rename positions;


As it states on the ALTER INDEX documentation, using ALTER INDEX ...REBUILD is equivalent to DBCC DBREINDEX.

ALTER INDEX has far more functionality in terms of other arguments and DBCC DBREINDEX is deprecated, however the performance issues you face should not  be related to using the old method of re-indexing.


This seems to be a long standing error:

swasheck - March 5, 2015 posted:

https://connect.microsoft.com/SQLServer/feedback/details/1163126

  MSDN notes that sys.stats_columns.stats_column_id is "1-based ordinal
  within set of stats columns." However, it seems to actually reflect
  table definition order. Altering index order is not reflected in
  sys.stats_columns.

Max Vernon and James Lupolt seem to agree based on their comments/encouragement.


I am having the exactly the same issue and I come to this web page. But it really did not solve the issue other than what Shawn Metlton mentioned to use write-dbadatatable in the comment.

Since the original poster @discipline did not give the whole PS script, I cannot fix his issue. But I figure out how to do the work after one hour's try.

So here is how to repeat the same error message

We first create a table in [TempDB] on my local sql server instance

use tempdb
if object_id('dbo.t', 'U') is not null
    drop table dbo.t;
create table dbo.t (name varchar(100), gender varchar(10), id int)

Now we write a few lines of PS codes as follows:

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city='Toronto'}
#this can be successful
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $p;

After execution, we can check the sql table and see it is indeed successful as shown below

Now if we change the $p to have its [city] property to DBNull as the following

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city=[dbnull]::Value}
#this will fail
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $p;

we can see the following error

The alternative solution is to create a system.data.datatable and then pass the object value to this datatable and then pass datatable to write-sqltabledata as shown below

$dt = new-object 'System.Data.DataTable';
$dt.Columns.Add((new-object System.Data.DataColumn('name', [System.String])));
$dt.Columns.Add((new-object System.Data.DataColumn('gender', [System.String])));
$dt.Columns.Add((new-object System.Data.DataColumn('city', [System.String])));

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city=[dbnull]::Value}

$p | % {$r = $dt.NewRow(); $r.name=$_.name; $r.gender=$_.gender; $r.city=$_.city; $dt.rows.add($r);}
#this will succeed
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $dt;

Now if we query the sql table again, we will see it works

If you have other better ideas, please share for the commuity.


Clustered Index Scan (Clustered) vs. Index Seek (NonClustered) + Key Lookup (Clustered)

Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria.

Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria.  Once the query returns a certain percentage of the table (AKA The tipping point) the optimizer falls back to a  Clustered Index scan, as that is the more efficient plan.

See Kimberly Tripp's blog post about The Tipping Point


Why does ANSI SQL define SUM(no rows) as NULL?

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*).

It's just one of the extremely many inconsistencies in the SQL language.  They make the language harder to teach, harder to learn, harder to understand, harder to use, harder to whatever you want, but that's just the way things are.  The rules cannot be changed "cold" and "just like that", for obvious reasons of backward compatibility (If the ISO committee publishes a final version of the standard, and vendors then set out to implement that standard, then those vendors will not appreciate it very much if in a subsequent version, the rules are changed such that existing (compliant) implementations of the former version of the standard "automatically fail to comply" the new version ...)

(*) It is now better understood that aggregations over an empty set behave more consistently if they systematically return the identity value (= what you call the 'neutral element') of the underlying binary operator at hand.  That underlying binary operator for COUNT and SUM is addition, and its identity value is zero.  For MIN and MAX, that identity value is the highest and lowest value of the type at hand, respectively, if the concerned types are finite.  Cases like averaging, harmonic means, medians, etc. are extremely intricate and exotic in this respect, though.


What would be the best way to model my simple table?

If we're talking about something where you may want to query on one or more of the attributes my suggestion would be multiple columns, indexed appropriately (for a small, infrequently-changing data set, index all the columns).
This works best for the case where you may want to run queries like
SELECT * FROM table WHERE attribute1=1 AND attribute3=2
since you will have several indexed columns and searches on one or more of them will be relatively efficient.

If we're talking about something where you only ever want to query on the aggregate (all attributes match) a single column will be more efficient in terms of storage, and likely in terms of indexing as well.
This works best for cases where the previous example would always devolve into
SELECT * FROM table WHERE attribute1=1 AND … AND attribute17=17
as rather than having to cross-check across multiple columns it will simply be
SELECT * FROM table WHERE attributeColumn=1234567  -- Rather than having to scan 17 indexes (or examine the contents of 17 columns in every row) you will just have the single index scan.


Query 'optimizing' state is taking too long

SELECT min(updated_date) as oldest, max(updated_date) AS newest FROM `order`;

is well optimized if you have INDEX(updated_date) (which you do).  It will do two probes.  No need for two queries, etc.

The EXPLAIN said 'optimized away' because it could see that it did not need to do any work.

I checked with a similar table, then checked the Handler% STATUS values -- Handler_read_first and Handler_read_last incremented by 1 each.  This is indicative of optimizing MIN and MAX.

Do not use OPTIMIZE TABLE it is (usually) a long wast of time.

It is a huge table.

DELETEing a million rows will queue up a lot of stuff, especially because of 10 secondary indexes.  I'll bet that when you asked for MAX and MIN, it had to finish up all the pending index updates.

If that is the case, none of the suggestions will really solve the problem.  Chunking the deletes (which is a good idea anyway) might have "hidden" the problem by slowing down the delete task.  As @eroomydna says, the undo logs must have been huge.

On what basis are you DELETEing?  If it is "purge all records older than X", then this is a cure:  PARTITION BY RANGE(...) on the date (to use for purging) and DROP PARTITION to jetison old rows.  This is also much cleaner (in the sense of OPTIMIZEing to defragment) than DELETEing.

Rolando, ANALYZE TABLE will recalc the stats "instantly".  OPTIMIZE TABLE rebuilds (and ANALYZEs) the table but takes forever on this sized table.

Potentially valid is to copy over the useful rows, a la Rolando's #4.  But only if you are jettisoning "most" of the table.  I suggest that is not the case here -- "deleting a few million rows" out of 618M.

10 secondary keys is a lot.  Would you care to show them; we may have suggestions on pruning the list.  For this sized table, it is costly to maintain that many.

Edit -- "Change buffering"

I believe what I have described is called change buffering for DELETEs.  More discussion:
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-performance-change_buffering.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-change_buffering.html

You could try innodb_change_buffering = none.


Optimizing query for large number of rows in mysql

Your query:

SELECT scratch_tickets.id AS ticket_id,
       -- more columns
FROM (scratch_tickets)
WHERE scratch_tickets.created_at >='2015-02-12 00:00:00'
  AND created_at <= '2015-02-18 23:59:59'
  AND scratch_tickets.status = 4
LIMIT 50 ;

needs a simple index on (status, created_at):

ALTER TABLE scratch_tickets
  ADD INDEX status_created_at_idx         -- pick a name for the index
  (status, created_at) ;

After testing that the query is using the new index and is more efficient, you can probably drop the old status index. ("probably" because you may have some rare query that profits a lot from a (status) index but is slow when using the (status, created_at) one.)

Some more notes:

Having LIMIT without ORDER BY is a recipe for indeterminate results. Always use ORDER BY so you are in control of which (50) rows will be chosen for return (unless you really don't mind semi-random results or there are serious performance issues).
Also note that adding or changing the ORDER BY expression may affect performance. For the specific query, the index will be used effectively if you have ORDER BY created_at or ORDER BY created_at DESC or ORDER BY created_at, ticket_id. If a different column or expression is used, the efficiency may not be as good.
Instead of inclusive (>= and <= - or BETWEEN) it's easier and less error-prone to use inclusive-exclusive ranges for datetime types. This way, you don't care much if the type is DATE, DATETIME or TIMESTAMP or if it has second, millisecond or microsecond accuracy.
Queries are more clear with aliases. See a rewrite:

SELECT st.id AS ticket_id,
       -- more columns
FROM scratch_tickets AS st
WHERE at.created_at >= '2015-02-12 00:00:00'       -- you could even use '2015-02-12'
  AND st.created_at <  '2015-02-19 00:00:00'       -- notice the   <
  AND st.status = 4
ORDER BY <some_expression>
LIMIT 50 ;


Removed privileges from root user and can't access database

You can use following steps to regrant root priviledge.

Shutdown mysqld.
Start mysqld with --skip-grant-tables.
Use mysql to connect mysqld. (Type mysql only, No need to input password -p, maybe No need to input username root, either)
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

FLUSH PRIVILEGES;
Restart  mysqld normally.


Alter column opreation failed due to disk space in SQL Server

Numeric(9, 10) is not a valid data type specification.  The scale must be less than or equal to the precision.  I'll assume the actual column has a smaller scale specification.

When you change a column from a precision of 1-9 to one of 10-19, the storage size increases from 5 to 9 bytes.  This is a fully logged operation regardless of your recovery model and logging of DDL operations is expensive as you have found.

One method is to add a new NULL column, and then update the new column value from the old one in batches in order to reduce log space requirements and allow updates to be applied to the mirror.  Once all rows are updated, drop the old column and rename the old to the new.  It is best to update in ranges of your clustered index key.  If you need help with the batching of updates, post your existing table structure.

Keep in mind that mirroring might not be able to keep up during the operation (increasing log space needs) even if you backup the log during the update.  You should do this during a maintenance window anyway so you have the option of removing the mirror during the maintenance and reestablishing afterward.

Another method is to create a new table with only the clustered index, load with INSERT...SELECT, drop the old table and rename the new table to the old name, and recreate constraints and indexes.  This would allow you to minimize logging by removing the mirror and changing the recovery model to SIMPLE or BULK_LOGGED during the maintenance.


Try

SELECT
  tblTraining.clientTrainingID AS lastID,
  MAX( tblTraining.trainingDate ) AS lastDate
 FROM tblTraining
 WHERE tblTraining.trainedID =37
 GROUP BY tblTraining.clientTrainingID
 ORDER BY tblTraining.clientTrainingID desc
 LIMIT 1

This will order the max() and limit the result to the first line being the highest value.


How to distinguish between system and user defined roles in a single database?

The column 'is_fixed_role' on sys.database_principals is your answer, check the MS document:

  If 1, this row represents an entry for one of the fixed database
  roles: db_owner, db_accessadmin, db_datareader, db_datawriter,
  db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader,
  db_denydatawriter.

Just use

SELECT * FROM sys.database_principals WHERE [type]='R' and [is_fixed_role]=1


Here are the steps to layout in the Stored Procedure

Create the Target database
Determine how many elements are in the list of tables
Loop through each element of the list of tables using the ELT() function
Take each element and form SQL Statement to create the new table in Target DB

Here is that Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`TableCookieCutter` $$
CREATE PROCEDURE `test`.`TableCookieCutter`
(
    SourceDB VARCHAR(64),
    TargetDB VARCHAR(64),
    TableList VARCHAR(1024)
)
BEGIN

    # Create the Target DB
    SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',TargetDB);
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

    # Strip Table List of Blanks
    # Enclose each table in quotes
    SET @table_list = CONCAT('''',REPLACE(REPLACE(TableList ,' ',''),',',QUOTE(',')),'''');

    # Count the number of tables in the list
    SET @table_stub = REPLACE(@table_list,',','');
    SET @array_count = LENGTH(@table_list) - LENGTH(@table_stub) + 1;

    # Loop through list of tables, creating each table
    SET @x = 0;
    WHILE @x < @array_count DO
        SET @x = @x + 1;
        SET @sql = CONCAT('SELECT ELT(',@x,',',@table_list,') INTO @tb');
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
        SET @sql = CONCAT('CREATE TABLE ',TargetDB,'.',@tb,' LIKE ',SourceDB,'.',@tb);
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$

DELIMITER ;


Oracle Datapump between different database releases

Be sure to use the version 12 impdp to import into the Oracle 11.2 XE Database.


The following query will return all IDs that do not have all translations:

SELECT id, COUNT(*) AS count
FROM vocabulary
GROUP BY id
HAVING count < (SELECT COUNT(DISTINCT language)
                FROM vocabulary);

If you want to know which languages are missing for each ID, use a compound query to look up those languages that are in the list of all languages but not in those for this ID:

WITH missing_ids AS (
  SELECT id, COUNT(*) AS count
  FROM vocabulary
  GROUP BY id
  HAVING count < (SELECT COUNT(DISTINCT language)
                  FROM vocabulary)
)
SELECT id,
       (SELECT group_concat(language)
        FROM (SELECT DISTINCT language FROM vocabulary
              EXCEPT
              SELECT language FROM vocabulary WHERE id = missing_ids.id)
       ) AS missing_languages
FROM missing_ids;


How to join tables when one of them doesn't have any relationship with the others

How about this:

SELECT Authors.manufacturers_id AS AuthorID, Authors.manufacturers_name AS AuthorName, count(products.products_id) AS '# of Products'
FROM Authors
LEFT JOIN
(
    SELECT manufacturers_id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.manufacturers_name, ',', n.n), ',', -1) value
      FROM Manufacturers t CROSS JOIN
    (
       SELECT a.N + b.N * 10 + 1 n
         FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        ORDER BY n
    ) n
     WHERE n.n <= 1 + (LENGTH(t.manufacturers_name) - LENGTH(REPLACE(t.manufacturers_name, ',', '')))
     ORDER BY value
) AS manufacturers ON manufacturers.value = Authors.manufacturers_name
LEFT JOIN products ON manufacturers.manufacturers_id = products.manufacturers_id
GROUP BY Authors.manufacturers_id, Authors.manufacturers_name


What are sensible/rational character limits in a database e.g. for a persons name

I think some of it goes back to requirements. How much of a name are you storing? Do you ever want to keep the "names" separate (like first, middle, last)? Do you want to handle multiple middle names?

I'd say 50 characters should be fairly sufficient for each name if you wished to separate them out. (That will be way more than enough for most names, but will also cover names that are larger.) I'd also probably go with VARCHAR than CHAR for names since they vary so much.

If you were going to put the full name in one field, I'd go with 150 - 200 characters then.

Again....it all goes back to requirements...what do you need?


What are the practical reasons behind learning relational algebra?

When Codd defined the relational model he defined a set of operators which could be applied to relations.  In specifying a relational algebra, much like specification of an integer algebra, we are able to use symbols in place of relations to solve queries.  These operators are subject to the same algebraic properties that integer algebra operators (+, -, *, /) are.  As a result, we can assume certain laws that always apply to a relation, any relation, undergoing that operation.  For example, in integer algebra we know that addition and multiplication are associative in that we can change the grouping of operands and not change the result:

  a + ( b + c ) = ( a + b ) + c

Similarly, in relational algebra we know that natural join is associative and thus know that A join B join C can be executed in any order.  These properties and laws create the power to re-write query formulations and be guaranteed to get the same results.  The book Applied Mathematics for Database Professionals provides significant detail on the various re-write rules you can use to precisely formulate the same query in different ways.  In a perfect world any formulation producing the same result would have the same performance.  A modern optimizer, while an amazing piece of software, isn't perfect however.  Thus if you have formulated a query one way and are getting poor performance, you have the skills to formulate it a different way and know it has the same semantics.  Another practical advantage to this is in the specification of database constraints.  First, understanding the relational algebra enables you to determine the simplest way to formulate the constraint.  Second, by formulating the constraint in formal logic, you can immediately clarify any ambiguity in intent from the business subject matter experts who formulated the business rule in loose English and avoid bugs.

It was Leonardo da Vinci who said:

  He who loves practice without theory is like the sailor who boards ship without a rudder and compass and never knows where he may cast.

In this same way, a data practitioner who doesn't understand the fundamentals of relational theory cannot be in as complete command of the technology as they can be with that understanding.  Some great references on relational algebra are SIRA_PRISE's Introduction to the Relational Algebra page, and CJ Date's SQL and Relational Theory.  Date's book shows the practicality in understanding relational algebra so that you can write much more accurate SQL queries.  SQL has many quirks and pitfalls and having a sound grasp of how it works vs. the original relational algebra operators really helps realizing where the pitfalls are and avoiding them.


Drop database owned by specific user in PostgreSQL

You could also try this from your shell:

-bash-4.1$ bad_dbname=$(psql -t -q -c "select datname from pg_database where datname like 'bsw_ui%' limit 1")
-bash-4.1$ echo "the next command will drop ${bad_dbname}"
-bash-4.1$ psql -c "drop database ${bad_dbname}"

I verified this works using bash4.1 on centos6.3 with posgresql8.4 for a database using this unicode character. YMMV


Why these two queries have different result?

I suspect that lf_transit_cage.trayNo contains some NULL values, which causes problems for a NOT IN check but not an IN check. This is an issue that we've all hit at some point as it doesn't seem intuitive, you expect that the two checks will work more similarly.

The quick fix is to alter SELECT lf_transit_cage.trayNo FROM lf_transit_cage to SELECT lf_transit_cage.trayNo FROM lf_transit_cage WHERE lf_transit_cage.trayNo IS NOT NULL, and/or if there shouldn't be NULL values in that column fix the data and perhaps set the column to not accept NULLs.

With ANSI_NULLS set off NOT IN does work as you were expecting. See this answer for an explanation as to why in both cases.


SQL Server DIFFERENTIAL BACKUP question?

Generally speaking a differential works very similarly to a full backup, except that only the changed extents in the DCM are backed up. Differential backups still contain a portion of the log needed to be transactionally consistent. This will differ for every environment and is not "known" at the time of backup. You can use the RESTORE HEADERONLY of the differential to check LSNs that are included and covered. When you think the transaction finished and when SQL Server wrote the logs that may or may not be flushed to disk when the checkpoint ran before the differential backup may or may not be included.


Find all `NaN` values in a postgresql database

You may loop through the Information_schema.columns and check tables dynamically.

DO $$
DECLARE
rec RECORD;
v_found BOOLEAN ;
BEGIN

for rec IN (   SELECT column_name,table_name,table_schema
               FROM  information_schema.columns
             WHERE  data_type IN ( 'numeric', 'real', 'double precision' )  )
LOOP
   v_found := FALSE;
   EXECUTE format ( 'select TRUE  FROM %I.%I WHERE %I = %L LIMIT 1' ,rec.table_schema,rec.table_name,rec.column_name,'NaN') INTO v_found;

   IF v_found = TRUE
   THEN
        RAISE NOTICE 'Found Column %  in Table %.%' , rec.column_name,rec.table_schema,rec.table_name;
   END IF;

END LOOP;

END$$;

Testing

knayak=# create table x as select CAST( 'Nan' as NUMERIC) as n ;
SELECT 1

Result

NOTICE:  Found Column n  in Table x
DO


How to get capacity specifications for a particular Oracle release?

Physical Database Limits

Logical Database Limits

The maximum number of users (schemas): 2^31 = 2147483648

The official answer to your other questions: unlimited.

The practical answer is: as many as your hardware/operating system/design can handle.


Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error?

Use a cursor variable (details here), then you don't have to worry about closing and deallocating (which, as David points out, isn't necessary inside a TVF, but it - and/or LOCAL - is required in other contexts). Just change:
 DECLARE c CURSOR ... FOR SELECT ...
 ...
 OPEN c;
 FETCH NEXT FROM c INTO ...

 ...

 CLOSE c;
 DEALLOCATE c;

To:
 DECLARE @c CURSOR;
 SET @c = CURSOR READ_ONLY FORWARD_ONLY FOR SELECT ...
 ...
 OPEN @c;
 FETCH NEXT FROM @c INTO ...

 ...

Work on the logic that you think requires a cursor and conditional/flow logic, so that you can eliminate the MSTVF and re-write it as an inline TVF. While it wasn't true before SQL Server 2012, I can assure you that most cursor logic in SQL Server 2016 can be rewritten using window functions. There are still isolated cases where a cursor is a better choice (not always for performance reasons), but it's doubtful that a TVF is one of them.


How to create an UTC index from a 'DATE' typed column with Oracle?

To be precise, the date type stores timestamps without any timezone information rather than "in local time zone".

  How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?

You have three options:

index the date and convert your timestamp to date when querying, eg:

select * from foo where foo_date=cast(systimestamp as date) from dual;

create a functional index on the date converted to a timestamp with local time zone, eg:

create unique index i_foo on foo(cast(ts as timestamp with local time zone));
select * from foo where cast(ts as timestamp with local time zone)=
                        cast(X as timestamp with local time zone);

change the type of your columns of type date to timestamp with time zone or timestamp with local time zone

Of these, it is very likely that the last option is the best one - timestamp with local time zone is the 'correct' type for most data that refers to a fixed point in time. (As opposed to 'noon' which is always 12:00 no matter which time zone you are in.)


Is select case when null better than iif(exists)?

First of all, the two queries are only equivalent if and only if there is a UNIQUE or primary key constraint on B (id). If there isn't, there is no point in using one instead of the other or in comparing their efficiency.

Now, assuming there is such a constraint, the question is confused about CASE and EXISTS. The IIF vs CASE choice is orthogonal to the LEFT JOIN vs EXISTS subquery choice. Meaning, we could easily rewrite the first query with IIF() and the second with CASE. See:

(by the way, I removed the TOP 1 as it is redundant in an EXISTS subquery)

-- query 2a
select
        a.Id,
        case when exists (select 1 from B where Id = a.Id)
             then convert(bit,1)
             else convert(bit,0)
        end IsActive
from A a ;

There is also a 3rd way to rewrite the query, similar to the 2nd query, using a correlated subquery but without the EXISTS. This could also be written with either CASE or IIF() but I'm writing only the case here:

-- query 3
select
        a.Id,
        case when (select B.Id from B where B.Id = a.Id) is not null
             then convert(bit,1)
             else convert(bit,0)
        end IsActive
from A a ;

So we have 3 ways to write the query (and 6 if we count the IIF versions) and there are possibly more ways. Using OUTER APPLY comes to mind so lets have a 4th way:

-- query 4
select
        a.Id,
        case when b.Id is not null
             then convert(bit,1)
             else convert(bit,0)
        end IsActive
from A a
  outer apply (select B.Id from B where B.Id = a.Id) b ;

The real question is of course which is more efficient. Regarding CASE vs IIF() there is probably no difference at all and both ways produce identical plans in all 4 cases. IIF() was added in recent versions of SQL Server but can't do anything that wasn't already possible with CASE. So I can't find any real use of it, unless to make a query friendly to developers coming from MS-Access.

For the 4 different ways though, there is no better answer than testing it yourself! In your environment, with your tables/views, your data and sizes, your hardware, your settings, etc. All these may matter in one way or another.

If I were to blindly guess without testing, I'd choose the LEFT JOIN way, since correlated subqueries (like the EXISTS or the 3rd subquery) may not always yield the best plan. The optimizer is pretty clever at understanding that all the queries would be equivalent but that also depends on the complexity of the queries. If A and B are base tables, ok. But if they are complex views over complex views over ..., then it may not.

And if you plan to define this query as a view and then use it in queries like this:

select *
from my_view
where IsActive = convert(bit, 0) ;

you might fall into performance problems. Hiding the values into computed columns inside views will prohibit the optimizer from using indexes on the underlying tables.


Performance of a Trigger vs Stored Procedure in MySQL

For the sake of simplicity, triggers are the way to go for implementing any kind of tracking of database changes. However, you need to be aware of what happens under the hood when you use triggers.

According to MySQL Stored Procedure Programming, page 256 under the head "Trigger Overhead" says the following:

  It is important to remember that, by necessity, triggers add overhead
  to the DML statement to which they apply. the actual amount of overhead
  will depend upon the nature of the trigger, but --- as all MySQL
  triggers execute FOR EACH ROW --- the overhead can rapidly accumulate
  for statements that process large numbers of rows. You should
  therefore avoid placing any expensive SQL statements or procedural
  code in triggers.

An expanded explanation of trigger overhead is given on pages 529-531. The conclulding point from that section states the following:

  The lesson here is this: since the trigger code will execute once
  for every row affected by a DML statement, the trigger can easily
  become the most significant factor in DML performance. Code inside the
  trigger body needs to be as lightweight as possible and -- in
  particular -- any SQL statements in the trigger should be supported by
  indexes whenever possible.

Not mentioned in the book is another factor when using triggers: When it comes to audit logging, please be aware of what you log data into. I say this because should you choose to log to a MyISAM table, each INSERT into a MyISAM table produces a full table lock during the INSERT. This can become a serious bottleneck in a high-traffic, high-transaction environment. Additionally, if the trigger is against an InnoDB table and you log changes in MyISAM from within the trigger, this will secretly disabled ACID compliance (i.e., reduce block transactions to autocommit behavior), which is cannot be rolled back.

When using triggers on InnoDB tables and logging changes

The table you log to is also InnoDB
You have autocommit turned off
You setup START TRANSACTION...COMMIT/ROLLBACK blocks thoroughly

In this way, audit logs can benefit from COMMIT/ROLLBACK as would main tables.

Concerning using stored procedures, you would have to painstakingly call the stored procedure at every point of DML against the table being tracked. One could easily miss logging changes in the face of tens of thousands of lines of application code. Placing such code in a trigger eliminates finding all those DML statements.

CAVEAT

Depending on how complex the trigger is, it can still be a bottleneck. If you want to reduce bottlenecks in audit logging, there is something you can do. However, it will require a little infrastructure change.

Using commodity hardware, create two more DB Servers

Distribution master (DM) to relay audit info only
MySQL Server for Auditing (AU)

This will server to reduce write I/O on the main database (MD) due to audit logging. Here is how you can accomplish it:

Step 01) Turn on binary logging in the main database.

Step 02) Using an inexpensive server, setup MySQL (same version as MD) with binary logging enabled. This will be DM. Setup replication from MD to DM.

Step 03) Using a second inexpensive server, setup MySQL (same version as MD) with binary logging disabled. Setup each audit table to use --replicate-do-table. This will be AU. Setup replication from DM to AU.

Step 04) mysqldump the table structures from MD and load it into DM and AU.

Step 05) Convert all audit tables in MD to use the BLACKHOLE storage engine

Step 06) Convert all tables in DM and AU to use the BLACKHOLE storage engine

Step 07) Convert all audit tables in AU to use the MyISAM storage engine

When done

DM will replicate from MD and record stuff in its binary log only
With --replicate-do-table filter on all audit tables, AU will replicate from DM

What this does is store audit info on a separate DB server and also reduce any write I/O degradation that MD would normally have.


Why mongodb requires arbiter in a 4 members replica set?

In a replica set configuration with an even number of voting members, an arbiter or additional secondary is strongly recommended (but not required) because:

An odd number of voting members avoids potential cases where you could have a tied vote (extending the election process) or fail to elect a primary (if it is possible to have a network split between half your nodes).
An odd number of voting members increases fault tolerance. A five member replica set has the same majority as a four member replica set (3 votes required), but the five member replica set allows for 2 members to be unavailable (versus only 1 node of fault tolerance for a four member replica set).
As you have noted, if you have a replica set with only two members the strict majority of 2 (n/2+1) does not allow for any fault tolerance. Three members is the minimum recommended replica set size if you want to allow for automatic failover and high availability.

MongoDB 3.0+ will log a warning if you have an even number of voting replica set members, but does not prevent you from using this configuration. The log warning will be similar to:

  replSet warning: even number of voting members in replica set config - add an arbiter or set votes to 0 on one of the existing members

As at MongoDB 3.2, replica sets can have a maximum of 7 voting members with up to 50 members in total (see: Replica Set Limits).


Will SSDT publish always fail with "Data Loss May Occur" when table is rebuilt?

Yes, you can rely on that. When you enable the "BlockOnDataLoss" attribute what actually happens is that SSDT adds to the deployment script a check like this one

IF EXISTS(SELECT 1 FROM TableToChange)
BEGIN
     RAISEERROR ...
END


mysqlbackup mysql enterprise utility issue

Since it is a backup, you are right to be concerned with whether it can be restored or not.

To be on the safe side, you must run

chown -R mysql:mysql /var/lib/mysql

immediately after the backup so mysql doesn't barf when restoring it.

Otherwise, you may get an OS error concerning file permission during the restoration or mysql startup

[root@******* 20130101_000]# perror 13
OS error code  13:  Permission denied


MS SQL Server - update row value to next row with row id

It can be done in one set based operation
Using a WHILE risks invoking the "Halloween problem" as you update rows over and over.

It's easier with SQL Server 2012 using LAG

DECLARE @t TABLE
(
    Newid1 int
    , a int  NULL
    , b int
    , c int
);

INSERT INTO @t VALUES(1, NULL, 40, 50)
        ,(2, NULL, 45, 55) ,(3, NULL, 46, 56)
        ,(4, NULL, 47, 57) ,(5, NULL, 48, 58);

-- SQL Server 2012
WITH t AS
(
    SELECT a, LAG(c) OVER (ORDER BY Newid1) AS nextC
    FROM @t
)
UPDATE t SET a = nextC;

SELECT * FROm @t;

But for SQL Server 2005+

DECLARE @t TABLE
(
    Newid1 int
    , a int  NULL
    , b int
    , c int
);

-- SQL Server 2005+
WITH t AS
(
    SELECT Newid1, ROW_NUMBER() OVER (ORDER BY Newid1) +1 AS nextID, a, c
    FROM @t
)
UPDATE t1
SET a = t2.c
FROM
   t AS t1
   JOIN
   t AS t2 ON t1.Newid1 = t2.nextID;

SELECT * FROM @t;


Mapping an MSSQL Login to the secondary replica in a High Availability Group

If the login is a Windows login, and is present on both the Primary and Secondary servers, you should only need to add the database user to the Primary Replica.

This is because database users are at the database level and the creation of the user itself will be replicated to the secondary replica. As this is a Windows login, it then follows that the SID will already match.


SSRS: textbox format hide complete row (group header)

Manually changed the height of the row and it's working. I don't know if was problem with the screen high resolution (2560 x 1440), I saw similar issues with some windows in SSMS.


How does Oracle Application Express connect to the database?

Application Express, together with either ORDS (Oracle REST Data Services) or a similar web service such as Glassfish, is basically a set of processes that run in the database, usually connecting via internal connections. Application Express basically serves HTML web pages to allow access the database and display reports, and so end users only need a web browser to use an Application Express application, with reports, ability to modify data, etc.  This is a huge simplification, but for your purposes, all you need to know is that end-users just need a fairly current web browser that supports HTML5 to get all the fancy web pages for accessing/modifying the data in the database.  APEX comes with a security framework, or you can also use your own corporate security.


PostgreSQL 11 error: column p.proisagg does not exist

In Postgres 11 proisagg was replaced with prokind in the system catalog pg_proc:

  prokind | char | f for a normal function, p for a procedure, a for an aggregate function, or w for a window function

The query needs to be adapted. Like:

SELECT ...
FROM pg_catalog.pg_proc p
 ...
WHERE p.prokind = 'f'   -- to only get plain functions
...

Related:

How to drop all of my functions in PostgreSQL?
PostgreSQL: How to list all stored functions that access specific table

The error you observed from the \df meta-command in psql is most likely due to using an outdated version of psql. psql 11 and up are updated to deal with  this change, of course.


REPLACE is DELETE any or all rows that match, then INSERT the one row.

For REPLACE to be useful, you need an index, preferrably UNIQUE or PRIMARY so that it knows what to DELETE.

Do you have an index on group; that is what the first REPLACE needs.  Or am I confused about the columns?

Also, ... Consider whether it would be better to use INSERT IGNORE instead of REPLACE.  The semantics is similar, but different.

If those suggestions do not work, then I recommend, LOADing the CSV files into temp tables, then perform queries to copy the data into the 'real' tables.


How do I model the following requirement in ER-Diagram?

I think you could do this with three tables and a view, User, Bookmark, Story_View and Story. User would contain all the users (log in information and an ID).

Story would contain a field in which you could put whatever the story is then when you want another user to modify that story you could just select that story and make a new record adding on whatever the next user adds on. Also it would have a number primary key as well as a foreign key to user ID so we can select the original owner of the story.

Story View would be what the users actually see. Users would only be able to see the data for stories they have made or are able to write on. There's definitely a better way of doing this using something outside of SQL.

Bookmarks table would simply store a foreign key to both user and story every time a user adds something to their bookmarks a record would be added to the bookmark table containing the user Id and story id so you could easily use a user ID to select all that users bookmarks.

I'm not going to draw a diagram because I think it's pretty easy best on this design.


How to catch all the errors happening during the execution of an SQL file containing multiple INSERT INTO statements to insert multiple rows?

Maybe a better approach would be to verify if the record already exists and would violate the constraint, and only then try to insert it. You would have to script a bit around this file.

Another possibility is to insert everything to a temp table first, and then do the insert to table conversion_fee in batch, selectively with a WHERE/IN/EXISTS clause, rather than on a row by row basis.
The temp table should simply be a copy of conversion_fee, without data and the constraints.

Then it should not be too difficult to idenfity the duplicate rows, for example by doing a JOIN between conversion_fee and the temp table.

With a text editor, you can easily replace the table name in your source file to something else (the name of a temp table). Import your file to that temp table, and then compare with conversion_fee.

I can't provide full code right now, since the table structure is not known, nor is the constraint. But this is an idea.


Does tables under schema uses multiple tablespaces other than default tablespace

A default tablespace is just that, a default.  If you specify a different tablespace when you create a table, the table will be created in the tablespace you specified.  Though it would generally not make sense to do so, you could put each table in a different tablespace so that the user owned 20 tables in 20 different tablespaces none of which were the default tablespace.

Alternately, a user's default tablespace can be changed so it is possible that the 20 tables were created without a specified tablespace but when the user's default tablespace was different.  Then the user's default tablespace was changed after all the tables were created.


Are many two column indexes a bad smell?

I hope for you that most failures are cleared.  In that case, when you query for cleared failures, the index will hurt performance.  If you'd put that column in the second place, on the other hand, it might be benificial.  When you're looking for not cleared - if those are rare - the index will help.


PostgreSQL COPY: is it always faster to delete and recreate indexes?

My guess would be going with some benchmarking, measuring the time it's taking you to recreate the full index, the time it takes to insert 10k rows without index, with index. then I'd try with 1000 rows, 5000 rows, 10000 rows, to try to figure out a trend.

Because even if you try to reduce the amount of factors that could affect the operation time (as Auto-Vacuum) it still depends on your hardware and your whole postgres.conf

BONUS: if you share with us some benchmarks, we could gladly help you to figure out the trend.


Adding users to a list and sort by category?

The process of data modeling is complex enough that you will need to go read some books about it to learn the details.  The subject goes way beyond what can be covered here.  However, to get you started, you can follow these (drastically oversimplified) steps:

Step 1: Make an Entity-Type for Each Tangible Thing

Start with making a logical data model.  For each thing your system cares about (people, places, things, events) make an entity-type. For your system it might look like this:

Step 2: Identify Your Relationships

Look at your logical entity-types and determine how they relate to each other.  Remember to focus on the relationships that matter to your system.  Don't get bogged down looking at relationships that are possible or that are real, but which are not pertinent to your system.

Step 3: Transform Your Logical Model Into a Physical Model

Once you have a logical model that covers the objects and properties that are of interest to your system, you need to figure out how the logical model can be implemented using physical tables in your database.

This involves at least two very important steps:

Resolving many-to-many relationships into intersection tables
Normalizing your tables

In the case of your system, there are some many-to-many relationships that can be resolved.  Normalization involves looking at table fields.  Since we don't know what your fields are from your question, we can't really get into that topic here.  Normalization is very important, however, and you should always make sure that you start from properly normalized tables and de-normalize only when you have very good, well considered, reasons to do so.

Your model with m:n relationships resolved would look like this:

Note that there isn't enough information in your question to be able to say for sure that this model is the start of what you need.  I've had to make a number of assumptions about what your entities are and how they're related.  This site isn't meant to be used for collaborative design in any case (see the FAQ) - but I've tried to give you a quick outline of the process so you have someplace to start.  If you get stuck with a specific design issue along the way, then you can definitely come back to ask for advice about it here on dba.se.


How to reduce the number of things SQL Server puts in the windows event viewer?

Note: This will not limit what gets logged. It justs stops logging in Windows Event log.

There are some stuff that you can prevent like - successful completion of log backups using Trace Flag 3226

Have a look at -n startup parameter.

From Database Engine Service Startup Options :

  Does not use the Windows application log to record SQL Server events. If you start an instance of SQL Server with -n, we recommend that you also use the -e startup option. Otherwise, SQL Server events are not logged.


Aggregate values by interval range given by parameter

CREATE TABLE T(amount INT, index INT);
INSERT INTO T VALUES
(55, 1),(88, 5),(45, 6),(86, 7),(87, 10),
(88, 11),(57, 16),(58, 17),(59, 20),(60, 21);

You can take advantage of Postgres function generate_series().

generate_series(start, stop, step)

  Generate a series of values, from start to stop with a step size of step

The example below uses a series from 0 to 14 with an interval of 4.

SELECT generate_series(0, 14, 4) Serie;

| serie |
| ----: |
|     0 |
|     4 |
|     8 |
|    12 |

WITH CTS AS
(
    SELECT generate_series(0, 14, 4) Serie
)
SELECT    COALESCE(SUM(T.amount),0) AS amount, CTS.Serie
FROM      CTS
LEFT JOIN T
ON        T.index >= CTS.Serie
AND       T.index <  CTS.Serie + 4
GROUP BY  CTS.Serie
ORDER BY  CTS.Serie;

amount | serie
-----: | ----:
    55 |     0
   219 |     4
   175 |     8
     0 |    12

You can use a user defined function that allows you to use parameters

CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int)
RETURNS TABLE (amount int, interv int) AS
$$
BEGIN
    RETURN QUERY
    WITH CTS AS
    (
        SELECT generate_series(low, high, step) Serie
    )
    SELECT    COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv
    FROM      CTS
    LEFT JOIN T
    ON        T.index >= CTS.Serie
    AND       T.index <  CTS.Serie + step
    GROUP BY  CTS.Serie
    ORDER BY  CTS.Serie;
END;
$$ LANGUAGE plpgsql;

✓

SELECT * FROM GroupIntervals(0, 14, 4);

amount | interv
-----: | -----:
    55 |      0
   219 |      4
   175 |      8
     0 |     12

SELECT * FROM GroupIntervals(5, 14, 5);

amount | interv
-----: | -----:
   219 |      5
   175 |     10

dbfiddle here

Update

As Evan Carrol has pointed out on him comments you can avoid use CTE and change function language from plpgsql to plain SQL.

CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int)
RETURNS TABLE (amount int, interv int)
AS $$
    SELECT    COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv
    FROM      generate_series(low, high, step) AS CTS(Serie)
    LEFT JOIN T
    ON        index >= CTS.Serie
    AND       index <  CTS.Serie + step
    GROUP BY  CTS.Serie
    ORDER BY  CTS.Serie;
$$ LANGUAGE sql;

SELECT * FROM GroupIntervals(0, 14, 4);

amount | interv
-----: | -----:
    55 |      0
   219 |      4
   175 |      8
     0 |     12

SELECT * FROM GroupIntervals(5, 14, 5);

amount | interv
-----: | -----:
   219 |      5
   175 |     10

dbfiddle here


Why does Management Studio add the second alter table "CHECK CONSTRAINT" for a foreign key constraint

With foreign key and check constraints, specifying CHECK or NOCHECK enables or disables it.  It's just explicit DDL that is generated by the GUI that, in your case, enables the foreign key constraint:

  { CHECK | NOCHECK } CONSTRAINT
  Specifies that constraint_name is
  enabled or disabled. This option can only be used with FOREIGN KEY and
  CHECK constraints. When NOCHECK is specified, the constraint is
  disabled and future inserts or updates to the column are not validated
  against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE
  constraints cannot be disabled.

Reference: BOL ALTER TABLE

It is redundant and unnecessary, but I just take it as "verbose". If I was to defend it forcefully, I'd say it leaves the script reader without any question of functionality.


It is very much normal for there to be gaps in identity values (caused by rolled-back transactions, over estimated pre-allocations, and so forth) as you are already aware, though that sort of sparse use of values is rather unusual and indicates that there is something going on which you need to be aware of (either a design issue that may be unavoidable and you just need to plan around or a fault that needs fixing).

The first thing I would ask is are you touching the IDENTITY column at all in the bulk insert? If you are working with IDENTITY_INSERT ON or similar circumstances and the imported data contains a large number there then that is the source of the problem. Another thing to check for is hidden processes that might touch the table - do you have any triggers that might be firing as data is added to the table and doing something odd?

Otherwise, if you could provide more detail in your question that might help someone help you:

The structure of the table in question (and any others touched by the same bulk process): column/key/index definitions and any triggers
The code you are using for the bulk insert (TSQL, a call to BCP, what-ever method you are using)
A sample of the data (perhaps anonymised if it is sensitive)
When you performed your test was the database otherwise quiet or was it active with other transactions potentially being processed?
Are the gaps following any sort of regular pattern, or do they seem random?

As a work-around you might consider using a BIGINT for the column instead, though if it is referred to by foreign keys elsewhere that could be a big job to change (and if the value isn't purely internal to the database you need to consider if the code touching it can cope with values beyond the 32-bit limits).


How to store a version sequence per product

If you can trust the IDENTITY column to be ever-increasing, I see no reason to store this information. It becomes redundant because it's information you can obtain from data already stored in the table (as you have already shown). Storing it is wasteful in terms of:

additional disk space
additional memory, since the data will hopefully be in memory most of the time
calculating the version # on every insert
re-calculating the version # for all related rows whenever any row is deleted

Now, the trade-off is measurable, since it is possible that the data is updated very infrequently, yet queried millions of times between updates. In that case, it may make sense to pre-calculate the data and store it. But don't prematurely optimize for that; make that decision when you can demonstrate that calculating it at runtime is adding measurable impact to your workload.

If you can't trust the IDENTITY column to be ever-increasing (after all, someone could go back and fill gaps left by deletes using IDENTITY_INSERT), then you could add a SMALLDATETIME column as NOT NULL with a default of CURRENT_TIMESTAMP, at a cost of 4 bytes per row (or DATETIME2 at varying cost depending on precision, if you need more granularity than minute). Then you should still calculate the version # at query runtime, IMHO, using a similar query to the one you proposed:

SELECT Id, Product,
ROW_NUMBER() OVER (PARTITION BY Product ORDER BY inserted_date)
FROM dbo.Products; --*

* Please always use the schema prefix

If you really, really, really, really, really think this is a good idea, then you can achieve this with an after trigger. Note that even with a very granular datetime with a default, you can't rely on just that for order-of-insert semantics (think multi-row inserts like below). Again, assuming you can rely on the IDENTITY column to be ever-increasing and never back-filled, you can use that column to break any ties.

USE tempdb;
GO

CREATE TABLE dbo.Products
(
  Id INT IDENTITY(1,1) PRIMARY KEY,
  Product INT,
  inserted_date DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  [Version] INT
);
GO

CREATE TRIGGER dbo.AddRedundantInfoToProducts
ON dbo.Products
AFTER INSERT, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH src AS
  (
    SELECT Id, [Version], rn = ROW_NUMBER() OVER
      (PARTITION BY Product ORDER BY inserted_date, Id)
    FROM dbo.Products
    WHERE Product IN
    (
      SELECT Product FROM inserted
      UNION ALL
      SELECT Product FROM deleted
    )
  )
  UPDATE src SET [Version] = rn;
END
GO

Now we can test it out:

INSERT dbo.Products(Product) VALUES(1),(1),(2),(1),(2);

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

Results:

1   1   2015-05-27 16:18:19.723 1
2   1   2015-05-27 16:18:19.723 2
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 3
5   2   2015-05-27 16:18:19.723 2

Now let's delete a couple of rows:

DELETE dbo.Products WHERE Id = 2;

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

Results:

1   1   2015-05-27 16:18:19.723 1
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 2
5   2   2015-05-27 16:18:19.723 2

Now we'll insert a few new rows:

INSERT dbo.Products(Product) VALUES(2),(2),(3);

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

Results:

1   1   2015-05-27 16:18:19.723 1
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 2
5   2   2015-05-27 16:18:19.723 2
6   2   2015-05-27 16:19:00.773 3
7   3   2015-05-27 16:19:00.773 1
8   2   2015-05-27 16:19:00.773 4

Clean up:

DROP TABLE dbo.Products;

Of course this can be easier if you control DML to the table through a stored procedure; you could calculate the new version(s) without the need for a trigger. However, I feel this is a waste of effort to even show you; I wouldn't recommend this approach at all because you can't ever really ensure all data manipulation will go through stored procedures, which means your data could easily get out of sync anyway (whereas the rank calculated at query runtime is always guaranteed to be current). Given the same table above:

CREATE PROCEDURE dbo.InsertProductRow
  @Product INT
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN TRANSACTION;
  DECLARE @rn INT;
  SELECT @rn = COALESCE(MAX([Version]),0) + 1
    FROM dbo.Products
    WHERE Product = @Product;
  INSERT dbo.Products(Product, [Version]) VALUES(@Product,@rn);
  COMMIT TRANSACTION;
END
GO

CREATE PROCEDURE dbo.DeleteProductRow
  @Id INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Product INT;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN TRANSACTION;
  SELECT @Product = Product FROM dbo.Products WHERE Id = @Id;
  DELETE dbo.Products WHERE Id = @Id;
  ;WITH src AS
  (
    SELECT Id, [Version], rn = ROW_NUMBER() OVER
      (ORDER BY inserted_date, Id)
    FROM dbo.Products
    WHERE Product = @Product
  )
  UPDATE src SET [Version] = rn;
  COMMIT TRANSACTION;
END
GO

The following batch will yield the same results at each step as above:

EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 2;
EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

EXEC dbo.DeleteProductRow @Id = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

EXEC dbo.InsertProductRow @Product = 2;
EXEC dbo.InsertProductRow @Product = 3;
EXEC dbo.InsertProductRow @Product = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

Don't forget to clean up:

DROP TABLE dbo.Products;
DROP PROCEDURE dbo.InsertProductRow, dbo.DeleteProductRow;


SQL Full-text Filter Daemon Launcher with Startup set to Manual is Running after Bootstrap

I found out the answer by myself.
I share it in case it might be useful for someone else
The default Shutdown setting for Windows 10 is "Fast Startup", that means that part of the running system processes are Hibernated instead than shut-down. This way at startup they are loaded in memory in the state they where when the PC was shut-down.
So long to the "turn it off and on again" to be working as expected

"Fast startup" can be disabled in the settings


What run-time configuration parameters are used for concurrent index creation

In your example, the maintenance_work_mem you have set of 256MB should be seen by the subsequent CREATE INDEX CONCURRENTLY command, because you have changed this GUC inside your session.

In fact, the docs suggest bumping up maintenance_work_mem (as you showed) for just such a purpose.


Instead of delete and then reseed, why not just truncate them? This deletes all the rows (in a slightly more efficient way than DELETE) AND reseeds the IDENTITY columns.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  TRUNCATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS -- leave this out if you want to truncate ALL tables
(
  SELECT 1
    FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
);

PRINT @sql;
-- EXEC sp_executesql @sql;

You could generate a similar script, in a similar way, that applies the RESEED to each table, but why?

Also, stay away from awful stored procedures like sp_MSforeachtable. They're undocumented and unsupported for a reason. Check out the problems with sp_MSforeachdb:

http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb

(I haven't gotten around to proving it yet, but I suspect the procedure for tables - which has the same cursor and overall logic as for databases - can break in a similar way.)

Finally, if you can't truncate because of foreign keys, check out this script (#4):

https://www.sentryone.com/blog/aaronbertrand/grouped-concatenation-use-cases


Multi tenancy by schema - Deny access to another tenants

The solution is to add the Grant permissions and do not add any read or write role to the user.

GRANT SELECT ON SCHEMA::[SCHEMA] TO [USER]

GRANT INSERT ON SCHEMA::[SCHEMA] TO [USER]

GRANT UPDATE ON SCHEMA::[SCHEMA] TO [USER]

GRANT DELETE ON SCHEMA::[SCHEMA] TO [USER]


Your insert to #temp and #temp2 works perfectly fine.

I believe you are getting the below error.

  Msg 156, Level 15, State 1, Line 70 Incorrect syntax near the keyword
  'AS'.

This error is due to the explicit aliasing on the below part of the code.

td = T1.avg_fragmentation_percent AS avg_fragmentation_percent_pre_change, '',
td = T2.avg_fragmentation_percent AS avg_fragmentation_percent_post_change

since you have already aliased it as 'td'. it will become like this

td = T1.avg_fragmentation_percent , '',
td = T2.avg_fragmentation_percent

Remove the explicit aliasing using AS and run the code and see it works. I did test run
and it worked.


Oracle how do i see who connected to my database

you need auditing in your database, by default Oracle database audit LOGON/LOGOFF action, please take a look into following article Auditing in Oracle 10g Release 2. query from DBA_AUDIT_TRAIL is a good starting point


Proper way to add new column in database

As someone who used to try to keep columns together like this, I highly suggest that you go with option #2.

The debatable upside of keeping the columns visually together (in the SSMS column list / when you SELECT * from the table) is absolutely dwarfed by the downsides of option #1.

As the tables get larger, doing this "copy and replace" operation is going to take longer and longer, and utilize more resources (I/O, memory, CPU).

Additionally, you really need to make sure you don't lose any data during the copy and replace, so using the least concurrency friendly isolation level (SERIALIZABLE) is going to be necessary.  This means that access to modify the existing table will be blocked until the operation is complete.  So you can add long periods of blocking to the list of downsides.

SSDT publish uses this sort of code automatically when re-ordering columns like that (from my blog post on the subject):

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_Post] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [CommentCount] INT          NULL,
    [PostType]     VARCHAR (10) NOT NULL
);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[Post])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Post] ON;
        INSERT INTO [dbo].[tmp_ms_xx_Post] ([Id], [PostType])
        SELECT [Id],
               [PostType]
        FROM   [dbo].[Post];
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Post] OFF;
    END

DROP TABLE [dbo].[Post];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Post]', N'Post';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

On top of that, if you're doing this manually (not with SSDT schema compare), then it's really easy to make a mistake (put data into the wrong column, copy / paste errors, etc) - especially on a table with a lot of columns.


How to shrink big database files faster than dbcc command?

An alternative to the DBCC command in this szenario is what I am
  looking for.

One possible alternative to shrink is described in Paul Randal's article:
Why you should not shrink your data files

  The method I like to recommend is as follows:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the    CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or    shrink it way down if its the primary filegroup)

Even if this method will lead to desired result and even more: you'll achieve defragmentation of all your indexes, it is not faster than shrink, it requires additional free space before you can shrink the old files, it can hardly be automated and there will be a problem with LOB data as it will remain in the old FG unless you copy it manually to new FG.


Profiling collects information about "what, when and how information was queried + how much time it took + what was DB engines solution"

Auditing tell "who did what and when" (who red/accessed f.ex. user information)

Logging is higher level information telling what DB did and when. When client makes/closes connection, new index is created, data is replicated, engine errors happened...

So, if law says that "we need to know who (as a person) accessed specific information", you do auditing.

To find out "which queries are slowest/take time and WHY" you use profiling.

To know what's happening and what are errors on database level, you use log files.


How to reclaim database space on Azure SQL Server after setting nvarchar values to null

Please defrag all indexes. As mentioned on this article, fragmentation can claim a lot space.

Run sp_helpfile to verify the log is not consuming space also. If the log is big run the following statement to recover space.

DBCC SHRINKFILE (log, 0)

On this thread I provided some queries that may be useful about database size.

Hope this helps.


Does SQL Server Compact Edition 4 finally support Views?

I installed WebMatrix w/ CE 4.0 last night.  I can't confirm for sure that CE 4.0 can't do views, but I can tell you that there's no support for creating them in WebMatrix.


Short answer: No, and yes, respectively.

SQL databases do store data in some sort of order; whether by allocation, or (when there is a clustered index on a table) by the clustering key.

However, the process of executing a query does not guarantee that the data will be returned in the order in which is was stored. To guarantee the order of your data, you must use the ORDER BY clause in your SELECT statement.

Yes, you can create an auto-incrementing ID value in PostgreSQL. While this would normally be used to create a surrogate key to be used as the primary key, it appears it can be used to merely create a unique column, if you like.

The accepted answer to this question should give you what you need. You either use a sequence, or the SERIAL pseudo-datatype (which creates a sequence for you behind the scenes). Check the docs for your version for more details on sequences.

(I believe SERIAL will most likely start from 1, not 0 - you would probably have to set up the sequence yourself to get a specific starting point)

Note: This answer is for the tagged version of PostgreSQL, 9.4. As of version 10, PostgreSQL now has an identity column mechanism similar to that in Oracle or DB2, per the SQL:2003 & SQL:2008 standard (feature # T174). See the doc for GENERATED ... FOR IDENTITY here.


How to display values excluding maximum value based on column that is duplicated

You cannot use an aggregated on WHERE clause. Instead, you could join your table with your grouped result set and discard maximum value.

select t1.event_no,
       t1.bs_id
from   bs_bank_mast t1
join   (select   event_no, max(bs_id) max_bs_id
        from     bs_bank_mast
        group by event_no
        having   count(*) > 1) t2
on     t2.event_no = t1.event_no
and    t1.bs_id <> t2.max_bs_id;

event_no | bs_id
-------: | ----:
 1692163 | 40672
 1692163 | 40672
 1723264 | 67460
 1723264 | 67499
 1723265 | 67383
 1723266 | 67735
 1723266 | 67423
 1723266 | 67969
 1723266 | 67501

dbfiddle here


Simplify SQL Query which aggregates per year

A CTE can be used in this case:

WITH yourdata as (
    SELECT TO_CHAR(date_created, 'YYYY') AS tc_date_created
    FROM ticket
    WHERE date_created >= '2013-01-01' AND
          date_created <= '2017-06-01'
)
SELECT tc_date_created, count(*)
FROM yourdata
GROUP BY tc_date_created
ORDER BY tc_date_created;


Truncate partitioned table with values from partitioned column?

Since you didn't provide a minimal, complete, and verifiable example, I've created one:

DROP TABLE IF EXISTS dbo.pt;
IF EXISTS (SELECT 1
    FROM sys.partition_schemes ps
    WHERE ps.name = N'ps'
    )
BEGIN
    DROP PARTITION SCHEME ps;
END
IF EXISTS (SELECT 1
    FROM sys.partition_functions pf
    WHERE pf.name = N'p'
    )
BEGIN
    DROP PARTITION FUNCTION p;
END

CREATE PARTITION FUNCTION p
(
    int
)
AS RANGE RIGHT
FOR VALUES (10, 20, 30);

CREATE PARTITION SCHEME ps
AS PARTITION p ALL TO ([DEFAULT]);

CREATE TABLE dbo.pt
(
    i int NOT NULL
        CONSTRAINT pt_pk
        PRIMARY KEY
        CLUSTERED
) ON ps(i);

INSERT INTO dbo.pt (i)
SELECT TOP(30) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc;

That will create a partition scheme, a partition function, and a partitioned table with 30 rows inserted across the three paritions.

The following query determines which values lie on what partitions:

;WITH rowsource AS
(
SELECT pt.i
    , plc.file_id
    , plc.page_id
    , plc.slot_id
FROM dbo.pt
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc
WHERE pt.i = 1
    OR pt.i = 22
)
SELECT rs.*
    , dpa.partition_id
FROM rowsource rs
OUTER APPLY sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.pt', N'U'), NULL, NULL, 'DETAILED') dpa
WHERE dpa.allocated_page_file_id = rs.file_id
    AND dpa.allocated_page_page_id = rs.[page_id]
ORDER BY rs.i
    , dpa.partition_id;

Note in the Common Table Expression (CTE), there is a WHERE clause limiting the output to the values (1) and (12) in the i column of the dbo.pt table.

The output looks like:

╔════╦═════════╦═════════╦═════════╦══════════════╗
║ i  ║ file_id ║ page_id ║ slot_id ║ partition_id ║
╠════╬═════════╬═════════╬═════════╬══════════════╣
║  1 ║       1 ║      40 ║       0 ║            1 ║
║ 22 ║       1 ║     328 ║       2 ║            3 ║
╚════╩═════════╩═════════╩═════════╩══════════════╝

As you can see, those values lie on partitions 1 and 3 respectively.

You could extend the query above by joining to a #temp table with the list of values you need the partition_id values for.

The following code will automatically truncate the partition for the row containing the value 14:

DECLARE @cmd nvarchar(max);
DECLARE @partitions nvarchar(max);

SET @partitions = N'';
;WITH rowsource AS
(
SELECT pt.i
    , plc.file_id
    , plc.page_id
    , plc.slot_id
FROM dbo.pt
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc
WHERE pt.i = 14
)
SELECT @partitions = STUFF(q.p, 1, 2, N'')
FROM (
SELECT ', ' + CONVERT(nvarchar(max), dpa.partition_id)
FROM rowsource rs
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.pt', N'U'), NULL, NULL, 'DETAILED') dpa
WHERE dpa.allocated_page_file_id = rs.file_id
    AND dpa.allocated_page_page_id = rs.[page_id]
GROUP BY dpa.partition_id
ORDER BY dpa.partition_id
FOR XML PATH(N'')
) q(p);

SET @cmd = N'TRUNCATE TABLE dbo.pt WITH (PARTITIONS (' + @partitions + N'));';
PRINT @cmd;
--EXEC sys.sp_executesql @cmd; --uncomment this line to actually truncation the partitions


How to model parent-child relationship correctly in multi-tenant, multi-schema Postgres database

There are many ways to implement this, but let me suggest partitioning.

The table public.invoice would then be list partitioned, with the tenant being the partitioning key. Each partition sits in the schema of the respective tenant.

That way everybody can query their own invoices in their own schema or all invoices in the public schema.

The big advantage of partitioning here is that it becomes very easy to remove a tenant: simple detach the respective partition and drop it.

Note that partitioning becomes impractical with thousands of partitions. But if you plan to have that many tenants, it is a bad idea to implement that with thousands of schemas with identical tables in a single database. Having too many tables will slow down metadata operations, and it makes life harder for autovacuum (which in turn will make your life hard).


How to UPDATE a table for the level of parents in a column?

I would suggest to create stored function for that.

Table definition I've used for debugging:

DROP TABLE IF EXISTS `test`;

CREATE TABLE IF NOT EXISTS `test`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
parent INT NULL,
parent_level INT DEFAULT 0
)ENGINE = InnoDB;

START TRANSACTION;

INSERT INTO `test`
    (`id`, `parent`, `parent_level`)
VALUES
    (1, NULL, 0),
    (2, 1, 0),
    (3, 1, 0),
    (4, 3, 0);

COMMIT;

Now, create a stored function:

DELIMITER $$

DROP FUNCTION IF EXISTS `getLevel`$$ -- drop if exists

CREATE FUNCTION `getLevel`(`anId` INT) -- argument: current row id
RETURNS INT                            -- returns int
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE `aResult` INT DEFAULT 1;       -- result buffer, default value = 1
DECLARE `aParent` INT;                 -- buffer for parent ids

-- select first parent of this row:
SET `aParent` = (SELECT `parent` FROM `test` WHERE `id` = `anId`);

-- while parent id is set:
WHILE NOT `aParent` IS NULL DO

    -- increase result:
    SET `aResult` = `aResult` + 1;

    -- select next parent:
    SET `aParent` = (SELECT `parent` FROM `test` WHERE `id` = `aParent`);

END WHILE;

-- return result
RETURN `aResult`;
END$$

DELIMITER ;

Now, how to update:

UPDATE
    `test`
SET
    `parent_level` = `getLevel`(`id`)
WHERE           -- if safe mode is on
    `id` > 0;   -- I have to use primary key column in update or it will fail

SELECT
    *
FROM
    `test`;

Results:

1   NULL    1
2   1       2
3   1       2
4   3       3


How to find a query that is use deprecated option?

You need go thru your source code! Search all aggregate commands and add needed ,{cursor:{}} section. Check here!


(I think this is what you are asking about.)

InnoDB "blocks" are 16KB.  But most disk subsystems work in smaller units -- 4KB or 512 bytes.  For InnoDB data to remain intact, the disk needs to write all 16KB as a unit -- either all written or none.  What can happen is that the pieces of the 16KB are written one after another, and the power fails part way through.  This causes a "torn page" ("page" referring to the 16KB block).

To recover from a torn page, InnoDB does two things.  It checksums each block in order to discover it, and it uses a "double write" for recovery.  The block (or at least certain critical blocks) are written twice - to some relatively constant spot, then to the desired location on disk (in the data or index).

When recovering from a crash, the "double write buffer" is checked to see if it leads to a "torn page"; in which case, it is repaired.

The double write, as its name implies, is costly.  (I have no metrics on how costly; I suspect the cost depends heavily on HDD vs SSD and RAID controllers.)  Turning it off is a way to gain some more speed, but with risk.  FusionIO was (it's been bought out) the only drive manufacturer who guaranteed 16KB atomic writes; I hope others have added this feature.

RAID with Battery Backed Write Cache should make the double write virtually zero cost.

A different checksum...  The Percona Toolkit uses a "checksum" for data.  Since rows are not necessarily laid out identically between Master and Slave, checksumming the files is not useful to seeing if the tables match.  I suspect it involves reading the rows in a repeatable order, and checksumming each row or clump of rows.

"rsync" does a similar thing, but at the file level.


Here is one way to know

Login to MySQL and run this

SELECT USER(),CURRENT_USER(); SELECT SLEEP(15);

What will this tell you ?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

Look for the SELECT SLEEP(15); query in the slow log. From there, you should be able to tell whether USER() or CURRENT_USER() was used to set # User@Host:.


Number of ISA relationship triangles in an Entity-Relationship Diagram

ISA in First diagram has no point. ISA is used as "either".

Movie CAN BE a Cartoon or Natural or movie MUST BE a cartoon or Natural.

There is no point to append an ISA while there is no either condition


Pgpool2 doesn't use the slave for load balancing

I found this page http://lists.pgfoundry.org/pipermail/pgpool-general/2010-May/002711.html

They say that I need to remove file pgpool_status. I did it and pgpool works now with two nodes! Seems the file was created when I had not the second node and after pgpool just trusted this file all time. So removing this file solved the problem.


Why bulk loading a Vertica database table does not load all lines?

Try option ABORT ON ERROR - will output a reason for rejection.

COPY 'table name' FROM '/path/to/data.txt' DIRECT RECORD TERMINATOR ',' ABORT ON ERROR

Check rejected and exception files or define it in COPY:

COPY 'table name' FROM '/path/to/data.txt' DIRECT RECORD TERMINATOR ',' REJECTED DATA 'path' EXCEPTIONS 'path'

https://community.vertica.com/vertica/topics/bulk_loading

(Original URL is dead; you can try this one instead)


According to the MySQL Documentation on LOAD DATA INFILE

  For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 6.2.1, “Privileges Provided by MySQL”. For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

As long as you have FILE privilege, you can do this in SQL Editor

SET GLOBAL local_infile = 1;


What to do after deleting a million records

The problem of deleting large portions of a table is far from a trivial problem. The best approach, by far, is partitioning. A daily partition scheme with a sliding window is really a magic bullet for this problem, see How to Implement an Automatic Sliding Window in a Partitioned Table.

If you cannot afford partitioning (eg. non-enterprise license on site) then I would recommend clustering by schDateCreated. If you need primary key on the GUID+smallint then move it to non-clustered. Delete in batches (eg. TOP 10000), in a loop, to reduce pressure on the log. Consider updating stats after the operation.


Trusted Constraints and NOT FOR REPLICATION

NOT FOR REPLICATION was indended for this scenario:

You have a Order table with a FK to Person table.
Order table is in publication no.1 and Person table is in publication no.2.
The Order pub and Person pub will happen at different times. Therefore if some new records were in the Order table were replicated down to the subscriber and the Person table was missing the person that new Order was for, you don't want the replication insert to fail at the subscriber. Instead, it can be trusted that the new Person record will come down at some point because the FK was used at the Publisher.
At the subscriber, if you insert an Order record, the FK will check that the Person record exists.

I would always stick with this policy and use NOT FOR REPLICATION only for that purpose of FK's relating to tables in different publications, and not for the purpose of query plan optimisation.


Cannot execute stored proc linked to C routine in DB2

Your compiled shared library is named myprog.dll, but you've told it to look for one called myprog in the create procedure call (the .dll suffix is a Windows-ism).

Rename it from myprog.dll to myprog.


Allow a user to create folders and linked reports, but not "real" reports

Yes. As per MSDN

  Create linked reports | Reports | Create Link, Read Properties
  ...
  Manage folders | Folders | Create Folder, Delete Update Properties, Read Properties

From the same link, one can see that "Create linked reports" does not infer or grant the separate permission "Manage reports"


For your first question:

  I want to configure it to have server name localhost-sql-server

This is normally done during installation.  From the "Install SQL Server from the Installation Wizard (Setup)" article:

  On the Instance Configuration page, specify whether to install a default instance or a named instance.

You would want a named instance.  Instance names are limited to 16 characters and cannot contain hyphens (thanks to Dan Guzman for pointing that out), so you unfortunately can't name it "localhost-sql-server" per your original request.

I typically have multiple versions of SQL Server installed on my laptop, for different development projects, and name them "SQL2017" or "SQL2016" per the version installed.  You may want to go with that route.

Once that's done, you can connect to it using the server name ".\SQL2017" (the computer name is part of the connection, which you can replace with ".\" for local connections).

I would recommend uninstalling SQL Server and setting it up this way from the start.  I'm not aware of a simple way to change a default (standalone) instance to a named instance.

For your second question:

  with a login/password

That's also normally done during installation, here's a relevant quote from the same docs page:

  Use the Database Engine Configuration - Server Configuration tab to specify the following:

  Security Mode - Select Windows Authentication or Mixed Mode Authentication for your instance of SQL Server. If you select Mixed Mode Authentication, you must provide a strong password for the built-in SQL Server system administrator account.

You would want mixed mode.  Once the server is set up, you could connect with the "sa" account and create a login and user with the desired password:

CREATE LOGIN YourUserName
    WITH PASSWORD = 'a strong password';
GO
CREATE USER YourUserName FOR LOGIN YourUserName;
GO

Good luck!


a) between LOCALSQL and REMOTESQL

Your PHP client never connects to the REMOTESQL server. It only connects to LOCALSQL and then LOCALSQL connects to REMOTESQL.

Keep in mind that your PHP availability is now driven by the REMOTESQL availability and consider that distributed queries are notoriously problematic to perf tune. Good luck!


Permission issue on restore of the db from primary to secondary

Permissions

from https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
  database exists, RESTORE permissions default to members of the
  sysadmin and dbcreator fixed server roles and the owner (dbo) of the
  database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists). RESTORE permissions are given to roles in which membership
  information is always readily available to the server. Because fixed
  database role membership can be checked only when the database is
  accessible and undamaged, which is not always the case when RESTORE is
  executed, members of the db_owner fixed database role do not have
  RESTORE permissions.


PostgreSQL 10 - single big query in UDFs or multiple smaller ones?

If you're able to write it as a single query Q, the best alternative is to avoid writing the UDF in the first place, and embed Q as a subquery in the calling query. That's probably the best if performance is your main concern.

If code reuse, modularity, readability are more important than performance, IMO you can choose option 2.


Pre-formated varchar variable does no work in select query

@Sites is a string, not an array. So you are literally searching for:

S.[Name] IN ('''Bayside'',''Collaborative''')
-------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- this is one single string

I highly recommend, instead of passing in a comma- or semi-colon-separated string, you pass in a table-valued parameter. First, create this in your database:

CREATE TYPE dbo.Sites AS TABLE(Site NVARCHAR(255));

Then your procedure would say (leaving out the less relevant bits):

ALTER PROCEDURE dbo.ProcedureName
  @Sites dbo.Sites READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ...
  FROM dbo.Sites AS s
  INNER JOIN @Sites AS tvp
    ON s.[Name] = tvp.Site;
END
GO

Then your code would pass a structured parameter (I assume the set of sites originally comes from a DataTable or some other collection):

SqlCommand cmd = new SqlCommand("dbo.ProcedureName", conn);
c2.CommandType = CommandType.StoredProcedure;
SqlParameter tvp = cmd.Parameters.AddWithValue("@Sites", DataTableName);
tvp.SqlDbType = SqlDbType.Structured;
...

If you really want to do this the slow, old-fashioned way, you can create a UDF that splits the string:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM
      (
        SELECT x = CONVERT(XML, ''
          + REPLACE(@List, @Delimiter, '')
          + '').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

Then your query is (again leaving out the far less relevant bits):

  INNER JOIN dbo.SplitStrings_XML(@Sites, N';') AS f
    ON s.[Name] = f.Item;

Now, XML might not be the safe approach here, but it's the simplest to repro without offering even slower methods or auxiliary objects such as a numbers table. For other alternatives, and more on why you don't want to do it this way, see:

Split Strings The Right Way - Or The Next Best Way
Splitting Strings : A Follow-Up
Splitting Strings : Now with less T-SQL


How to manipulate the query plan so that I can have more control on memory grants?

I'm not sure I understand how a table is "too big" to update the statistics. When people start calling tables "too big" for anything, they usually start being candidates for table partitioning. In which case you can use incremental statistics to focus your stats updates on the active partitions (the optimizer won't use the per-partition stats independently, but it will still be beneficial for queries and reduces your maintenance because, presumably, not all of your partitions will have significant, or any, data fluctuations between every maintenance window).

Anyway, instead of trying to manipulate which operators are used in order to affect how much memory grant you have (which may make your performance or resource usage worse), why not use hints that control min/max grants directly? KB #3107401 talks about new query hints MIN_GRANT_PERCENT and MAX_GRANT_PERCENT that control the grant a query will get (the article says 2012 SP3, but these were also added in 2014 SP2).

Tread lightly, though. My suspicion is you are trying to solve the wrong problem with the wrong solution. If the source of the problem is bad estimates, fix that problem.


ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX; JOB_SCHEDULER is biggest occupant

To release space to SYSAUX tablespace you can try:

alter table sys.scheduler$_event_log enable row movement;
alter table sys.scheduler$_event_log shrink space;
alter table sys.scheduler$_event_log disable row movement;


Shopping cart schema with multiple product tables

I would solve this by adding a generic PRODUCT table containing the attributes which are common for all products (including the ProductID) and a ProductType column describing the type of product (Car, Book etc). Tables CAR, BOOK can then contain the same ProductID as in the main PRODUCT table and attributes which are specific to their product type.


Read MS Sql query high latency w.r.t. Remote Query

Create a copy of the table into a #temporary table on the server you are running the query on, then run the query based off the temp table.

I am not an expert on why certain queries run slower over linked servers and others do not. However, if you move that data temporarily over to the server you are running the query on, it often makes it run faster.  Someone more experienced can answer why this is the case.


Restore to a point between two full SQL Server backups

If you are in full recovery mode but NOT backing up transaction logs, then any state between two full backups is permanently lost. Sorry. You should either change your mode to basic recovery (since you are not making any use of your transaction logs) or you should do at least one transaction log backup before full backups.


Detect bottleneck of server via psycopg2

If you have access to PostgreSQL superuser account, then you can run arbitrary OS commands on the server as the OS user who is running the postgres server.  For example:

create temp table top (x text);
copy top from program 'top -b -n1';
select x from top limit 25;

However, if you were intentionally not given direct shell access to this server, whoever is running the server could consider this a hostile action.  It would probably be better to ask whoever is in charge to set up some OS-level monitoring solution, or explain to you any existing ones.


T-SQL being executed is different than T-SQL that will be executed if the function is run.

To see what is currently executing, you would use either SQL Server Profiler, or Extended Events. Then you can execute a function and see what T-SQL it runs.
To see what the function might execute, that is a bit trickier. You do have the code in the sys.assembly_files system catalog view. The content field contains the binary representation of the assembly. But, to see the code you need to find a way to extract it  to a binary file (no built-in method, and no, not a text file containing that "binary" string) and then decompile it.

String literals should be contained as-is in the assembly, so theoretically you should be able to convert the VARBINARY value in sys.assembly_files.content to NVARCHAR, but there are two problems there:

Queries can be constructed from several literals and/or variables, so it's hard to say if viewing this would be conclusive, and
There are many 0x00 bytes, and those will stop the displaying of what comes after it because it is the "null" terminator for strings, so you need to find a way to convert those to spaces (while also not converting a trailing 0 of one byte followed by a leading 0 of the next byte).

So, perhaps just use SQL Server Profiler or Extended Events to see what T-SQL is submitted when the functions are executed.


SQL - How to return first encountered row that matches specific criteria

Answering for SQL Server. I don’t know that there will be a single answer that will work on all three platforms you’ve tagged. If that was unintentional, please only tag the engine you’re using. If you want an answer per platform, might be better to ask separate questions.

You can leave which id is returned up to SQL Server by using an arbitrary TOP (1) (no order by):

SELECT TOP (1) id
  FROM dbo.table
  WHERE price IS NULL
    AND category = 'hardware';

I would comment the code to say “don’t care which one and I know this is arbitrary” so a future reader doesn’t think you made any assumptions about deterministic order.


pt-table-checksum with slaves using non-standard ports

The issue is that the replicas don't report their ports to the master.  By default pt-table-checksum tries to connect by filling unknowns with defaults; if the unknowns aren't using defaults, this will fail.  In this case, there are two options:

1) Use the --recursion-method option to change from inspecting SHOW PROCESSLIST to inspecting SHOW SLAVE HOSTS, and configure slaves to report their host.  I don't recommend this.

2) I recommend, instead, that you use --recursion-method=dsn and create a table full of DSN (data source name) instructions that tell the tool how to connect to each replica.  If this table is foo.bar, then you will use --recursion-method=dsn=D=foo,t=bar.

This is only available in the 2.0 series of the toolkit, but you should be using that anyway, because pt-table-checksum is hugely improved in 2.0.


11g dataguard broken after primary ran out of space

Why would you re-initalize everything just because 1 datafile is missing?

On the primary database, create a backup of the datafile:

rman target /
backup datafile 71 format '/tmp/whatever.bkp';

Copy the backup to the standby, then use it:

rman target /
catalog start with '/tmp/whatever.bkp';
run
{
  set newname for datafile 71 to '/path/to/datafiles/whatever.dbf';
  restore datafile 71;
  switch datafile 71 to copy;
}

Then try again.


Regarding your question about how to have triggers from two independent tables (A, and B) insert rows into a third table (C), you can certainly do that.  Presumably both A and B have a column that identifies the specific row in C that needs to be updated.

To prove this, I've created the two source tables A and B, with a trigger on each table, that inserts into the C table.

CREATE TABLE dbo.A
(
    RowID INT NOT NULL
        CONSTRAINT PK_A
        PRIMARY KEY CLUSTERED
    , SomeDataA VARCHAR(386) NOT NULL
);

CREATE TABLE dbo.B
(
    RowID INT NOT NULL
        CONSTRAINT PK_B
        PRIMARY KEY CLUSTERED
    , SomeDataB VARCHAR(386) NOT NULL
);

CREATE TABLE dbo.C
(
    RowID INT NOT NULL
        CONSTRAINT PK_C
        PRIMARY KEY CLUSTERED
    , SomeDataA VARCHAR(386) NULL
    , SomeDataB VARCHAR(386) NULL
);
GO

CREATE TRIGGER dbo.A_Update_C
ON dbo.A
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @t TABLE
    (
        RowID INT NOT NULL
    );
    UPDATE dbo.C
    SET SomeDataA = i.SomeDataA
    OUTPUT i.RowID INTO @t (RowID)
    FROM inserted i
    INNER JOIN dbo.C c ON i.RowID = c.RowID;

    INSERT INTO dbo.C (RowID, SomeDataA)
    SELECT i.RowID
        , i.SomeDataA
    FROM inserted i
    WHERE NOT EXISTS (
        SELECT 1
        FROM @t t
        WHERE t.RowID = i.RowID
        );
END
GO

CREATE TRIGGER dbo.B_Update_C
ON dbo.B
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @t TABLE
    (
        RowID INT NOT NULL
    );
    UPDATE dbo.C
    SET SomeDataB = i.SomeDataB
    OUTPUT i.RowID INTO @t (RowID)
    FROM inserted i
    INNER JOIN dbo.C c ON i.RowID = c.RowID;

    INSERT INTO dbo.C (RowID, SomeDataB)
    SELECT i.RowID
        , i.SomeDataB
    FROM inserted i
    WHERE NOT EXISTS (
        SELECT 1
        FROM @t t
        WHERE t.RowID = i.RowID
        );
END
GO

Here we insert test data into A and B, then show the rows in C:

INSERT INTO dbo.A (RowID, SomeDataA)
VALUES (1, 'x');

INSERT INTO dbo.B (RowID, SomeDataB)
VALUES (1, 'y');

SELECT *
FROM dbo.C;

The rows in C are:

The code relies on the RowID value being identical in both A and B; without some commonality, it is impossible to know which rows in C need to be updated.

The triggers above only inserts new rows into C if they don't already exist by using the OUTPUT clause to insert the RowID values into a temporary table which is subsequently used to prevent those rows from being inserted into the C table.  Only rows that don't already exist in the @t table variable get inserted into C.

If there is a high degree of concurrent activity against A and B, with different processes potentially updating the same rows in those tables, it may be possible to attempt to insert duplicate rows into C.  This would fail, causing the entire transaction to fail, rolling back the changes made by both the trigger in C, as well as the changes made to either A or B.  The code above does not account for this edge-case, and in fact has no error checking at all.

The below answer and code is regarding the first "version" of your question, which was "can multiple triggers write to the same row".

You can very easily test that using:

USE tempdb;

CREATE TABLE dbo.TriggerTest
(
    TriggerTest_ID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData VARCHAR(255)
);
GO
CREATE TRIGGER dbo.TriggerTest1 ON dbo.TriggerTest
FOR INSERT
AS
BEGIN
    UPDATE dbo.TriggerTest
    SET SomeData = COALESCE(tt.SomeData, '') + 'TriggerTest1'
    FROM dbo.TriggerTest tt
        INNER JOIN inserted i ON tt.TriggerTest_ID = i.TriggerTest_ID;
END
GO
CREATE TRIGGER dbo.TriggerTest2 ON dbo.TriggerTest
FOR INSERT
AS
BEGIN
    UPDATE dbo.TriggerTest
    SET SomeData = COALESCE(tt.SomeData, '') + 'TriggerTest2'
    FROM dbo.TriggerTest tt
        INNER JOIN inserted i ON tt.TriggerTest_ID = i.TriggerTest_ID;
END
GO
TRUNCATE TABLE dbo.TriggerTest;
INSERT INTO dbo.TriggerTest DEFAULT VALUES;
SELECT *
FROM dbo.TriggerTest;

If you run this, you'll see the following results:

This clearly proves that two separate FOR INSERT triggers can, and in fact do, update rows independently of each other.  If both triggers are coded to affect the inserted rows, then that is what they will do.

Unless you specify the trigger order using the sys.sp_settriggerorder stored procedure, it is not guaranteed that the trigger created first will fire first.  If the logic contained in the these triggers depends on a certain logical firing order, you must ensure you specify the order.  For instance:

--make TriggerTest2 run first:
EXEC sys.sp_settriggerorder
    @triggername = 'dbo.TriggerTest2'
    , @order = 'First'
    , @stmttype = 'INSERT';

--set TriggerTest1 to run in any order.  The @order parameter could be
--  changed to 'Last' to ensure it runs after all other triggers on
--  that table
EXEC sys.sp_settriggerorder
    @triggername = 'dbo.TriggerTest1'
    , @order = 'None'
    , @stmttype = 'INSERT';

Also of note, you should realize triggers fire once for any given INSERT statement.  Take for instance:

TRUNCATE TABLE dbo.TriggerTest;

INSERT INTO dbo.TriggerTest (SomeData)
SELECT o.name
FROM sys.objects o;

SELECT *
FROM dbo.TriggerTest;

In my tempdb, this created 101 rows, a sample of which is:

I coded my triggers to properly handle this by using a set-based approach to updating the underlying table with a JOIN to the special table, inserted, which is a system-created virtual table containing the rows to be inserted into the table.


Use a Matrix control in SSRS.

But in T-SQL:

You want a row per ID so you'll need GROUP BY ID

In the first column you want a time value where the activity is A, so use: MAX(CASE WHEN Activity = 'Activity A' THEN time END)

And then apply the pattern for all the others.


How to join multiple columns from different tables

Try this one, use INNER JOIN from booking to aircraft_model

SELECT
    b.booking_id,
    c.firstname AS First_Name,
    c.last_name AS Last_Name,
    am.aircraft_model
FROM booking b
INNER JOIN customer c ON b.customer_id = c.customer_id
INNER JOIN aircraft a ON b.aircraft_id = a.aircraft_model_id
INNER JOIN aircraft_model am ON a.aircraft_model_id = am.aircraft_model_id