How does a Page in SQL Server distinguishes between the data of different columns of a table?

Column meta-data are stored in system catalog tables. These are held separately from the physical record.  The meta-data can be queried using the sys.columns catalog view. Records within a data page are a structure that includes a null bitmap, fixed-length fields, variable-length fields, and a column offset array entry for each variable length field present in the record. See Paul Randal's Anatomy of a Record for specifics of the internal data record structure. The physical record structure is significantly different when PAGE or ROW compression is used, for BLOBs (varchar(max) et al), and when a clustered columnstore index exists.

SQL Server uses the schema defined by the column meta-data to determine how to interpret the physical record structure and return the requested column values from the fields in the record. The physical record may or may not contain fields for each column, depending on whether the data type is fixed-length or variable, a NULL value, or columns added after initial table creation.

For example, if one adds a varchar NULL column to an existing table, that is a meta-data only operation that doesn't modify existing physical records. Similarly, adding a NOT NULL column with a default constraint is a meta-data only operation in Enterprise Edition because SQL Server uses meta-data to return the default value for existing rows even though the value is not physically present in records until later modified.


Missing wsrep variables in mysql / percona / galera cluster

After starting up the whole cluster, I'm missing some of the wsrep variables.

I'm not sure if this is the issue, but
wsrep_local_state_comment is not a wsrep variable, it's a status variable. Which means you have to use SHOW GLOBAL STATUS LIKE ... rather than SHOW GLOBAL VARIABLES LIKE ..., i.e. in this case:

SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';

And more generally:

SHOW GLOBAL STATUS LIKE 'wsrep%';


Ensure integrity of other columns from foreign record

Here is a rough sketch of the comments:

CREATE TABLE Race
( Race_id INT NOT NULL PRIMARY KEY  -- I prefer to use the same name through out the model
);

CREATE TABLE Racer
( Racer_id INT NOT NULL PRIMARY KEY  -- See above
, Rank INT NOT NULL
,   CONSTRAINT AK1_Racer UNIQUE (Rank, Racer_id) -- Super key
);

CREATE TABLE Result  -- Is not "Result" a better name than Winner?
-- I removed id
( Race_id INT NOT NULL
, Racer_id INT NOT NULL
, Rank INT NOT NULL  -- See below
, ...
,   CONSTRAINT PK_Result PRIMARY KEY (Race_id, Racer_id)
,   CONSTRAINT AK_Result UNIQUE (Race_id, Rank)
,   CONSTRAINT FK_Race FOREIGN KEY (Race_id)
                       REFERENCES Race (Race_id)
,   CONSTRAINT FK_Racer FOREIGN KEY (Rank, Racer_id)
                       REFERENCES Racer (Rank, Race_id)
);

EDIT:

I'm not sure why RANK is part of RACER. It seems like all kinds of possible ranks can be derived from WINNER (RESULT):

SELECT Racer_id
     , COUNT(1) as #races
     , AVG(Rank) as avg_rank
     , MIN(Rank) as lowest_rank
     , MAX(Rank) as highest_rank
     , ...
FROM Result -- Winner
GROUP BY Racer_id

If there is some other metric like score, time, etc that determines the rank per race, you may also consider storing that and derive rank from there. PostgreSQL and newer versions of MySQL have window functions like RANK() that is very useful for such calculations.


What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?

There are some defaults that exist merely because nobody really knows what the effect of changing them would be. For example, the default instance-level collation when installing on a system that uses "US English" as the OS language is SQL_Latin1_General_CP1_CI_AS. This makes no sense since the SQL_* collations are for pre-SQL Server 2000 compatibility. Starting in SQL Server 2000 you could actually choose a Windows collation, and so the default for US English systems should have been changed to Latin1_General_CI_AS. BUT, I guess nobody at Microsoft really knows what the impact will be to all of the various potential sub-systems and system stored procedures, etc.

So, I am not aware of any specific negative impact of setting it to ON as either a database default or even instance-wide. At the same time, I have not tested it. But even if I had tested it, I might still not use the same code paths as your application, so this is something that you really need to test in your environment. Set it to ON at the instance level in your Dev and QA environments and see how that works for a month or two. Then enable it in Staging / UAT. If all continues to go well for several weeks, roll that config change to Production. The key is to give as much time as possible for testing various code paths that are not hit daily. Some are hit weekly or months or annually. Some code paths are only hit by support, or some ad hoc report or maintenance proc that someone created years ago and never told you about and only gets used at random intervals (nah, that never happens ;-).

So, I did some testing on an instance that still has the default "user options" setting as I have never changed it.

Please note:

@@OPTIONS / 'user options' is a bitmasked value
64 is the bit for ARITHABORT ON

SETUP

I tested with both SQLCMD (which uses ODBC) and LINQPad (which uses .NET SqlClient):

SQLCMD -W -S (local) ^
-Q"SELECT CONCAT(DB_NAME(), N': ', @@OPTIONS & 64, N' (', ses.[client_interface_name], N')') FROM sys.dm_exec_sessions ses WHERE ses.[session_id] = @@SPID;"
echo .

(the ^ is the DOS line continuation character; the . on the last line is just to force the extra line to make it easier to copy-and-paste)

In LINQPad:

using (SqlConnection connection =
    new SqlConnection(@"Server=(local);Trusted_Connection=true;Database=tempdb;"))
{
  using (SqlCommand command = connection.CreateCommand())
  {
    command.CommandText = @"SELECT @RetVal =
CONCAT(DB_NAME(), N': ', @@OPTIONS & 64, N' (', ses.[client_interface_name], N')')
FROM  sys.dm_exec_sessions ses
WHERE ses.[session_id] = @@SPID;";
    SqlParameter paramRetVal = new SqlParameter("@RetVal", SqlDbType.NVarChar, 500);
    paramRetVal.Direction = ParameterDirection.Output;
    command.Parameters.Add(paramRetVal);

    connection.Open();
    command.ExecuteNonQuery();

    Console.WriteLine(paramRetVal.Value.ToString());
  }
}

TEST 1: Before

SQLCMD returns:

master: 0 (ODBC)

LINQPad returns:

tempdb: 0 (.Net SqlClient Data Provider)

CHANGE DEFAULT CONNECTION OPTION:

The following T-SQL enables ARITHABORT without removing any other options that might be set, and without changing anything if ARITHABORT is already set in the bitmasked value.

DECLARE @UserOptions INT;

-- Get current bitmasked value and ensure ARITHABORT is enabled:
SELECT @UserOptions = CONVERT(INT, cnf.[value_in_use]) | 64 -- enable "ARITHABORT"
FROM   sys.configurations cnf
WHERE  cnf.[configuration_id] = 1534 -- user options

-- Apply new default connection options:
EXEC sys.sp_configure N'user options', @UserOptions;
RECONFIGURE;

TEST 2: After

SQLCMD returns:

master: 64 (ODBC)

LINQPad returns:

tempdb: 64 (.Net SqlClient Data Provider)

Conclusion

Given that:

There does not seem to be any benefit to having ARITHABORT OFF
There is benefit to having ARITHABORT ON
The default connection setting (unless overridden by the connection) = OFF
It does not appear that either ODBC or OLEDB / .NET SqlClient attempt to set ARITHABORT, thus they accept the default setting

I would suggest changing the instance-wide default connection options (as shown above). This would be less obtrusive than updating the application. I would only update the app if you find a problem with changing the instance-wide setting.

P.S. I did a simple test with changing tempdb and not changing the instance-wide setting and it did not seem to work.


Is there a postgres function documentation pattern that is similar in concept to javadoc?

Not really, however in Postgres and many other DBMS vendors you can comment on various database objects like:

COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral';

As far as I can tell there is no way you can comment on individual arguments for a function.

For the presentation of the documentation, you may want to have a look at:

http://schemaspy.org/


Does shrinking a SQL Server transaction log remove transactions from the log?

"Shrinking" the log is actually "clearing" the log.

The log is basically a list of operations that have been performed, and is used as a reference for rollbacks, restores, checkpoints, etc.

The space wasn't "unused" but it contained transactions that had been committed and were reflected in the backup that was performed.

Your boss cleared out all the transactions in the log file, but since you had just run a backup this is acceptable since none of those transactions were uncommitted.


Optimal table design for a medical center rental service and recurrent shifts

You could have a bookings table like this:

bookings
--------
  id (PK)
  user_id (FK to table with all medical professionals)
  room_id (FK to table with all rooms)
  start_date - datetime value
  end_date - datetime value

This structure will let you track which professional is using which room, and when:

bookings
--------
id  | user_id  | room_id  | start_date    | end_date
---------------------------------------------------------
1   | 001      | 123      | 20140101T1030 | 20140101T1045
2   | 001      | 123      | 20140101T1100 | 20140101T1115
3   | 002      | 120      | 20140101T1000 | 20140101T1100

To track historic data, you'll probably want to use "soft" deletes on rooms and professionals so that even if you decide to delete Dr. Phillips from the list of current renters, you can still look up when he used which rooms. Your application will have to be smart enough to not create new bookings with professionals/rooms that are marked as "deleted".

professionals
-------------
  id
  name
  deleted
  (other fields...)

rooms
-----
  id
  deleted
  (other fields)

Example:

professionals
-------------
id  | name       | deleted
----+------------+--------
1   | Dr. Philips| N
2   | Dr. Nick R.| Y

rooms
-----
id  | deleted
----+--------
123 | N
120 | N
837 | Y

To handle recurring appointments, you could start with something like this:

recurring_bookings
------------------
  id (PK)
  user_id (FK to med. professionals)
  room_id (FK to rooms)
  day_of_week (could be FK to table/enum of days of week)
  start_time
  end_time (or you could have a "duration", in minutes, if you prefer)
  start_of_recurrence
  end_of_recurrence

recurring_bookings
------------------
id   | user_id    | room_id   | day_of_week | start_time | end_time | start_of_recurrence | end_of_recurrence
-----+------------+-----------+-------------+------------+----------+---------------------+------------------
1    | 001        | 120       | MONDAY      | 1030       | 1100     | 2014-01-01          | 2014-12-31
2    | 001        | 120       | WEDNESDAY   | 1030       | 1100     | 2014-01-01          | 2014-12-31

The above example shows a user with a recurring booking every monday and wednesday in room 120, running from 10:30 to 11:00 AM. It also shows that the period over which both recurrences are defined runs from 2014-01-01 to 2014-12-31

The tricky part is handling exceptions. For example, suppose a user wants to eliminate only one instance of a recurring booking, because they'll be on vacation that week. One strategy might to think of the recurring bookings table as a rule-set which can be used to populate real bookings into the main bookings table. So that way, you use recurring_bookings to populate all appointments for a user in bookings, and then rmeove or modify them from bookings as needed. This requires you to pre-populate a lot of data into your main bookings table (based on start_of_recurrence and end_of_recurrence), which you may or may not want to do, for recurring appointments. To handle open-ended recurring appointments, it might require a bit more work. Also, the above example assumes all recurring bookings are weekly. Some of them may be daily or yearly or monthly, and may have conditions such as "first Wednesday of every month".

I will admit that I haven't tried to build a system with recurring bookings like this, so this design should be considered a suggestion for a starting point. It will probably need some work. To get an idea of one way this is done, try creating and then manipulating recurring appointments in Outlook (if you have access to it).


Is there risk importing data with auto increment id #'s that are higher than the current numbers?

LOAD the new data into a separate table (staging), without including the AUTO_INCREMENT field.
Decide whether there are any duplicates that need to be avoided.
INSERT ... SELECT (without id) to copy the "new" data into the real table.
DROP TABLE staging;


how to view content of xbase procedures?

in Sybase ASE:

sp_helptext sp_name in your database can give you the code of the stored procedure
select * from sysobjects where type='P' in your database can list the stored procedures


The server variable lower_case_table_names is described in the relevant documentation page:

Identifier Case Sensitivity, where it also mentions:

  lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

Some more details appear in the linked page about server variables:

  It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.


Could someone help me identify my syntax error, or identify whether this is possible at all?

There is nothing wrong with your syntax but:

  A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE
  statement that references some or all the CTE columns. A CTE can also
  be specified in a CREATE VIEW statement as part of the defining SELECT
  statement of the view.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

As a best practice I will start with a semicolon (;).

As an example following should work.

;WITH lastfullbackup
     AS (SELECT bs.database_name,
                Max(bs.backup_finish_date) AS BackupDate
         FROM   msdb..backupset bs
         WHERE  bs.type = 'D'
         GROUP  BY bs.database_name),
     lastdiffbackup
     AS (SELECT bs.database_name,
                Max(bs.backup_finish_date) AS BackupDate
         FROM   msdb..backupset bs
         WHERE  bs.type = 'I'
         GROUP  BY bs.database_name)
SELECT *
FROM   lastfullbackup
UNION ALL
SELECT *
FROM   lastdiffbackup


Postgresql: Refreshing materialized view fails with "No space left on device" and a huge spike in disk usage

Find where postgres writes temp files for you and follow it during the refresh. As you are processing alot of data inside the database, postgres has to write a huge temp file and as this fills your disk, you get an error.
You probably have to find a more efficent way to write your query.


innodb_page_size is not present in [mysqld] section in my.ini file in MySQL Server 8.0?

If it is not explicitly set it defaults to 16k. You would set it to a new value in the [mysqld] section but it cannot be changed once the instance has been created.


How to verify the restored MS SQL DB from Backup matches with the source?

Once the backup is restored to target DB, it will be reflecting the state of source DB at the time of backup. From my understanding, restore is almost a drop and recreate of DB in target server. Normally, if Prod DB is restored to a non-prod - logins/Users accounts may be different, or if any alerts are configured to make any data changes thru triggers/SP's once a DB is restored. Otherwise, there is no possibility of difference. Even if you restore DB on a server with a different collation, it works unless you create some tempdb tables and try joining them to the restored DB. Hope this helps.


Group by with showing full rows in Postgresql

You just want this

SELECT a,b,sum(b)
FROM foo
GROUP BY a,b;

 a | b | sum
---+---+-----
 1 | 2 |   4
 1 | 1 |   2

If you don't want to reduce the row set, you can do this..

SELECT a,b,sum(b) OVER (PARTITION BY a,b)
FROM foo;

 a | b | sum
---+---+-----
 1 | 1 |   2
 1 | 1 |   2
 1 | 2 |   4
 1 | 2 |   4


How to profile a particularly slow or inefficient query

You didn't provide CREATE TABLE scripts; thus, it's hard to give you a specific advice; I'll try to describe general approach....

If you want to speed up this query, first of all, make sure you have indexes on the columns that define join condition, used as a filter (WHERE), or column[s] you have in GROUP BY or ORDER BY. In general, a good sign that some indexes are missed is when EXPLAIN shows "NULL" in key,key_len and "ALL" in type column (not always though; for instance if you select all rows from the table, or table is small enough, so full scan is faster than index seek + lookup).  Then you may want to tweak some indexes to make them covering for this query.

Side note. The query seems to me like a BI query which normally is not executed against OLTP database. When dealing with a big data, it makes sense to build a few cubes based on data from operational db and query them instead of original data. The nature of OLTP implies high level of normalization and optimization for INSERT/UPDATE/DELETE, not for SELECTs (still possible, but queries can be very long, not clear, and quite slow).


Will this SQL snippet REINDEX my GIN index? Why is it renaming an old index onto another?

Look at the names more carefully.  pgcompact_temp_index_17791 and pgcompact_index_17791 are not the same thing.

The reason to rename the old index is that you can't do "drop index concurrently" inside of a transaction.


Cannot start SQL Server replication as already running

You can do this via several methods. (from memory as don't have a replication environment in front of me!)

1- My Preferred intial method -  Open the Replication Monitor. You can do this by right clicking the 'Replication' folder in Management Studio and selecting Replication Monitor.

You will then have to register your publisher if this is the first time and add your Server into the list with the correct credentials (This can be local to the Publisher or can be setup on a remote Distribution server).

When you have selected your publisher you should now be able to select go to the agents tab on the right hand side. You can then right click and select 'view details' This should hopefully give you further insight into why the current replication is not working and its status. You can also stop and start agents from the monitor.

2-  The other method is to go to the SQL agents job on the server that is running your distribution DB and open the SQL Agent. you will see several jobs relating to replication.

Expand the jobs that are marked failing and look at the job steps (you will often have to go to the step before the retry to discover the real reason why the agent is failing). Obviously you can stop and start any agents here, but nearly always you will experience the same result. you should try to remedy the underlying issue first.  This will all depend upon what step is failing but permissions is a fair bet with Replication.


double (3,3) > sending 1.3 > stored 0.999

(3,3) indicates a total length of 3 digits, with 3 decimal places.  Perhaps you want (6,3).

See the MySQL Docs for details.


Need Help Executing a Remote Task from SQL Server Agent

I have only done this a couple of times and quite a while ago.  At that time I used PSEXEC.EXE from the Windows Internals.  You can read the instructions here:

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

For Powershell remote execution there was a discussion in StackOverflow that satisfied the questioner.  Powershell is probably the way to go these days.

https://stackoverflow.com/questions/9535515/powershell-execute-remote-exe-with-command-line-arguments-on-remote-computer

However you run the remote job, you can schedule it in the SQL Server Agent process that runs the other jobs.   If you think it would work for you, you could also set the job to run on a defined period of 'idleness'.  (I don't think that is what you want, but you can look into it under the SQL Server Agent discussion on Agent schedules at http://technet.microsoft.com/en-us/library/ms189065.aspx.)


Missing Transaction log in chain, Possible to skip

Is it possible to skip a transaction log and continue recovering the database to the most recent date with the remaining logs?
  EDIT: there were no changes to the database done at the time we are missing, so the transaction log would have nothing in it

Yes, only if there is NO gap in the Log Sequence Numbers of the log backups.

Below will explain you in more detail.

create database logbackup_test
go

-- take a full backup, else the database will be in pseudo-full recovery
backup database logbackup_test to disk = 'C:\sandbox\logbackup_test_full.bak'
with compression, stats =10

use logbackup_test
go
CREATE TABLE dbo.Table_1
       (
       Name varchar(50) NULL
       )  ON [PRIMARY]
GO

--Insert a 1st Row in TEST table
Insert into dbo.Table_1 values ('Kin-1')
GO
--Take 1st T-LOG backup.
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog1.trn' with stats =10
go

--Insert a 2nd Row in TEST table
Insert into dbo.Table_1 values ('Kin-2')
GO

--Take 2nd T-LOG backup.
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog2.trn' with stats =10
go

--Insert a 3rd Row in TEST table
Insert into dbo.Table_1 values ('Kin-3')
GO

--Take 3rd T-LOG backup
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog3.trn' with stats =10
go

--

--Take 4th T-LOG backup -- NOTE That there is no activity .. no transactions (NO INSERTS DONE) !!!
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog4.trn' with stats =10
go
/*Processed 0 pages for database 'logbackup_test', file 'logbackup_test_log' on file 1.
100 percent processed.
BACKUP LOG successfully processed 0 pages in 0.072 seconds (0.000 MB/sec).
*/

--

--Insert a 4th Row in TEST table
Insert into dbo.Table_1 values ('Kin-4')
GO
--Take 5th T-LOG backup
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog5.trn' with stats =10
go

Check the data now :

Below will explain you why you can skip the log backup. It relies on LSN (Log Sequence Number). It is beyond the scope of this answer to go into detail about the LSN, but the link will give you a good idea of what it is.

RESTORE HEADERONLY will tell you the first LSN and Last LSN as below :

--- now start the restore

-- restore a full backup
restore database logbackup_test
from disk = N'C:\sandbox\logbackup_test_full.bak'
with norecovery, replace, stats =10
go

-- restore consequent log backups !! - 1,2,3 and 5. WE ARE SKIPPING TLog4 !!
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog1.trn'
with norecovery, stats =10
go
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog2.trn'
with norecovery, stats =10
go
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog3.trn'
with norecovery, stats =10
go
-- NOTE -- I am skipping Log4 as it did not have any transaction .....
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog5.trn'
with recovery, stats =10

Below is the result .. we are able to restore the log backup 1,2,3 and 5. We skipped Tlog4 as the LastLSN of Tlog3 was the firstLSN of Tlog5.


Error: 18456, Severity: 14, State: 5; SSMS > Network sever from PC

My team lead, pointed out the issue.  As I could not find the issue/solution here, posting it for others.

I knew I could not use windows authentication, so I was trying to use SQL Server Authentication, this was the problem.

To access SSMS as a different user - When opening SSMS from your PC; Shift + right click > ‘Run as Different User’ enter ID ‘Admin1’ + password, proceed as normal.


Why can't temporary tables reference regular (non-temp) tables in PostgreSQL?

There are two sides to a foreign key constraint:

When inserting a child record (or updating its foreign key), the parent record must exist
When deleting a parent record (or updating its primary key), a child record must not exist

Postgres implements this by installing triggers on both tables to perform the existence checks. But the parent trigger simply won't work if the child is a temp table: the trigger needs to fire for all database users in order to guarantee referential integrity, but the temp table data is only visible within the session which created it, so the constraint cannot be enforced.

Even if it were possible, it's probably a bit too intrusive; it's rarely desirable to have a DELETE on the parent fail just because some other user is referencing it from a temporary dataset. But if you really need to maintain integrity in this case, you can get much the same effect by locking the parent records with a SELECT ... FOR KEY SHARE.


Delete from table where multiple fields match select subquery from other table

Perhaps the DELETE JOIN would be preferable

DELETE A.* FROM table1 A INNER JOIN table2 b
ON  A.id = B.id
AND a.field1 = b.field1
AND a.field2 = b.field2
AND a.field3 = b.field3
AND b.id = ?;

I wrote about why DELETE with WHERE involving a subquery is sort of unhealthy to deal with in a past post of mine back on Feb 22, 2011: Problem with MySQL subquery

Even though there have been great strides in improving the Query Optimizer, evaluation of subqueries could make keys unavailable for key comparisons downstream.

ALTERNATIVE

Try gathering the keys you know need to be deleted and do the DELETE JOIN with it:

CREATE TABLE DeleteIDs SELECT id FROM table1 WHERE 1=2;
INSERT INTO table1
    SELECT A.id FROM table1 A INNER JOIN table2 b
    ON  A.id = B.id
    AND a.field1 = b.field1
    AND a.field2 = b.field2
    AND a.field3 = b.field3
    AND b.id = ?
;
ALTER TABLE DeleteIDs ADD PRIMARY KEY (id);
DELETE B.* FROM DeleteIDs A INNER JOIN table1 B;
DROP TABLE DeleteIDs;

SUGGESTION

Maybe an index would help

ALTER TABLE table1 ADD INDEX id123 (id,field1,field2,field3);
ALTER TABLE table2 ADD INDEX id123 (id,field1,field2,field3);

Give it a Try !!!


Sleeping sessions with an open transaction but empty input buffer

This can sometimes happen when applications are using implicit transactions

It doesn't help that sp_who2 is really confusing -- you're better off with sp_WhoIsActive or sp_BlitzWho to find running queries.

One dead giveaway of implicit transactions is seeing the query text like begin tran or IF @@TRANCOUNT > 0.


What is a storage need for MySQL index on Varchar(100) in InnoDB?

I have discussed having large PRIMARY KEYs for InnoDB before : What storage engine should I use for this MySQL table?. The effect would be bloated keys experiencing linear growth of all Secondary Indexes because the PRIMARY KEY reference from a Secondary Key Index Entry would also start getting bloated as well.

Looking at your question, you yourself are saying you will be expanding an email address from 20 to 64 characters. You will be bloating every non-unique index for the table in question that has an email address column. If the email address is itself the primary key, then all indexes for the table in question whether an index has an email address column or not. There are other viewpoints discussed in Mysql int vs varchar as primary key (InnoDB Storage Engine?

It is bad enough that a CHAR field is fast to read than a VARCHAR field but at the expense of having larger indexes (See my post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?). Doing this  will certainly introduce configuration challenges including

Having a Large Enough InnoDB Buffer Pool (innodb_buffer_pool_size)
Having Large Enough Transaction Logs (innodb_log_size_size)
Having Sufficient Read and Write IO Threads (innodb_read_io_threads,innodb_write_io_threads)
Having a Large Enough Log buffer (innodb_log_buffer_size)

Even if email address is not indexed, data pages for that InnoDB will still have bloating and possibly fragmentation nonetheless. Since you are hashing, I can presume you must be indexing it.


I don't know much about graph database system and their capabilities but several traditional (SQL) database systems have options to store and manipulate XML documents:

PostgreSQL: there is a XML data type and quite a lot of XML functions since version 8.3.
SQL Server: has support for XML Data since version 2012 (and as announced this year, the DBMS will be soon available in Linux and not only Windows).
Oracle: XML DB Developer's Guide, I think since version 9.
DB2: offical docs: pureXML overview -- DB2 as an XML database and a nice article that explains its XML capabilities: DB2 Native XML.


Adding standard set of constraints (rules) to PostGIS `raster` type column

OK, so you:

Dropped a bunch of constraints
Did some work
Attempted to add a completely unrelated random constraint and that failed

You only showed two of the constraints in the output (why?) but the two you showed were CHECK constraints, not UNIQUE constraints. So it makes absolutely no sense to attempt to replace them with a UNIQUE constraint.

You must:

take a note of each constraint name and expression;
Drop them
Do the work
Re-create them with matching name and expression

So, say you have the constraint "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21])) on table chp05.us_tmin_new. To recreate it, you must use the same constraint expression and name. So that'd be:

ALTER TABLE chp05.us_tmin_new
ADD CONSTRAINT "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21]));

See how it conveniently matches the psql output for the constraint? You can just copy and paste it.

I have no idea why you were attempting to create a unique constraint instead, when it could not possibly have the effects you described above.

(BTW, your descriptions are slightly inaccurate. Those check constraints don't assert that all the rasters have the same number of bands, etc. They assert that all constraints have number-of-bands equal to some particular value. In other words, the constraint isn't relative to other values in the table, it's checked independently for each row.)


How to query results where a column with 2 classifications has same value in another column in the same table?

Something like this:

select a.columna, a.email
from (select * from tablename where columna='A') a
join (select * from tablename where columna='B') b
on (a.columnB=b.ColumnB)


MyISAM didn't change between 5.5 and 5.6. It's not being developed, and it's virtually stagnant.

Use FLUSH TABLES WITH READ LOCK and then it's safe to copy the MyISAM tables. This is mentioned in https://dev.mysql.com/doc/refman/5.6/en/backup-methods.html

Other solutions:

mysqldump --tab outputs tab-delimited data files instead of SQL, so it's much faster to import with mysqlimport.
mysqlhotcopy does a physical copy of MyISAM tables (it doesn't work for InnoDB tables). But be careful because mysqlhotcopy is on the chopping block for deprecation.
mydumper, which dumps to SQL, but it supports compression and multi-threaded execution for both dump and restore. Unfortunately it does not support an option like --tab.


You need FKs in category_product table, not in categories and products:

CREATE TABLE IF NOT EXISTS categories (
    id serial,
    name text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    CONSTRAINT PK_CATEGORIES_ID PRIMARY KEY (id)

);

CREATE TABLE IF NOT EXISTS products (
    id serial,
    name text NOT NULL,
    description text NULL,
    photo text NULL,
    price money NOT NULL,
    is_in_menu boolean NOT NULL DEFAULT true,
    is_from_kitchen boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT PK_PRODUCTS_ID PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS category_product (
    cat_id integer NOT NULL,
    product_id integer NOT NULL,
    CONSTRAINT PK_CATEGORY_PRODUCT_ID PRIMARY KEY (cat_id, product_id),
    CONSTRAINT FK_CATEGORY_PRODUCT_CATEGORY FOREIGN KEY(cat_id) REFERENCES categories(id)
    ON DELETE CASCADE,
    CONSTRAINT FK_CATEGORY_PRODUCT_PRODUCT FOREIGN KEY(product_id) REFERENCES products(id)
);


SSDT - Exclude certain schema along with unnamed constraint

You can use utility DacpacTool to remove the schema completely from a dacpac file - I wrote it to do exactly this (among other things). It is quite rough on the edges so far, but it works.

Extract dacpac from target database to MSSQLDatabase.dacpac
Create a file blacklist.txt with one line: <References Name="\[SchemaThatYouWantRemoved]
DacpacTool.exe filter -f MSSQLDatabase.dacpac -b blacklist.txt
DacpacTool.exe filter -f DBProject.dacpac -b blacklist.txt
Script the diff between the two dacpacs. DeploymentFilterContributor is no longer needed: sqlpackage.exe /action:script /sourcefile:DBProject.dacpac /TargetFile:"MSSQLDatabase.dacpac" /outputpath:"c:\temp\script_AfterDLL.sql"


Principle of least privilege for sql server agent jobs

Maybe the wording is unclear, but least privilege applies to all activities and should never be ignored.

The least privilege you need to perform database maintenance is sysadmin. Lower privileged users could perform those activities too (I'm thinking of db_owners), but the least privilege needed to perform any activity on a SQL Server instance is sysadmin, so if you have a job that performs administration tasks, it makes sense to have it running as a sysadmin.

For tasks other than database maintenance (tasks that implement some business logic), you want to impersonate users that have permissions to perform specific activities on the database.


The B, C and E columns will all have constant values as they are included in the WHERE clause, so there is no need to be in the GROUP BY and ORDER BY clauses. Therefore, the ORDER BY can be written as ORDER BY G,H,I
Since you can always rearrange columns in the GROUP BY clause without altering the results, that can be written as GROUP BY G,H,I,F to be aligned with the ORDER BY clause.
No reason to have all the columns in the SELECT list - especially those that are limited by the WHERE clause. Keep only those needed.

The query now becomes:

SELECT F,G,H,I     -- with possibly other columns, as needed
FROM TABLE
WHERE A = 1
  AND B = '5427dbfac0a8280d74bc672c72636a4f'
  AND C = 'BIL'
  AND D = 'B'
  and E = '01'
GROUP BY G,H,I,F
ORDER BY G,H,I ;

and possible indexes that could help:

(A) or (B) or (C) or (D) or (E) to limit the rows. This could have tiny or huge effect on efficiency, depending on the selectivity of the column.
(A,B) or (C,A) or .... or (A,C,D,E,B) i.e. whatever combination of the 5 columns in WHERE or all them (order doesn't matter). This should be better than the previous single-column indexes as the rows need to be returned will be found via the index. The result will still have to be grouped and ordered though.
(A,B,C,D,E, G,H,I,F) Even better as the whole query (selecting, grouping and ordering) can be done with an index scan. If you don't have any more columns in the SELECT list, this is the best index you can have.
(A,B,C,D,E, G,H,I,F, <moreColumns>) If you do have more columns in the SELECT list, you can include them in the index.

Now, which index to use really depends on lot of factors like:

Is this query crucial? How often is it run and how fast does it need to run?
How many rows does the WHERE clause limit the query to? If they are not many, you may just be ok with the (A,B,C,D,E) index or even narrower.
How many rows does the GROUP BY limit the query to and are there calculations in the SELECT list (like MAX(K))?
Can any of these indexes help other queries as well? Indexes with many columns are useful for very few queries and add overhead for the write operations on the table so you wouldn't want to create too many of them.


The password file is an option for authenticating remote users with special privileges. A database can operate without a password file. So no, it is not mandatory.

Typically all databases have their own seperate password file. If you create the new database with DBCA, that automatically creates the password file for the new database.

Some companies simply do not create password files for their databases and disable password file authentication in order to lower security risks. Think of it as disabling root login through SSH.

Features like Data Guard rely on password file authentication. You can create the password time now or later, it is up to you.


Postgres HAVING ilike any wildcard in array_agg

... but it is the other way around. The wild card would need to be in the inputted value.

Yes, it's backwards, but ILIKE (internally operator ~~*) nor the alternative regexp operator ~* have commutators. I.e., you can't turn the expression around, can't switch left and right operand. See:

SELECT * FROM pg_operator WHERE oprname  IN ('~~*', '~*');

oprcom = 0 means no commutator. Details in the manual.

The right operand of (I)LIKE is a pattern, so switching is impossible. And the ANY construct only goes one way. Dead end. See:

Can PostgreSQL index array columns?

You can still turn it around using a different tool: the aggregate function bool_or():

SELECT p.id, p.name, array_agg(vo.name)
FROM   product           p
JOIN   product_variation pv ON p.id = pv.product_id
JOIN   variation_option  vo ON pv.variation_option_id = vo.id
GROUP  BY p.id
HAVING bool_or(vo.name ILIKE ('%' || $1 || '%'));

Also using plain JOIN instead of LEFT JOIN, which would be pointless in view of the later filter.

  ideally, I'd be able to input an array of strings

Now that's also possible for an input array:

...
HAVING bool_or(vo.name ~* ANY($1));

$1 being of type text[] or an array literal like '{foo, bar}'. I chose the regexp operator as we don't need leading and trailing % with it.

Since $1 is a pattern (or an array of patterns), as mentioned above, you may want to escape characters with special meaning (per array element). See:

Escape function for regular expression or LIKE patterns

If your input is selective, meaning only very few products actually qualify, then an alternative formulation with EXISTS might be faster - in combination with a trigram index on vo.name. See:

How is LIKE implemented?

Index:

CREATE INDEX variation_option_name_gin_trgm_idx ON variation_option USING gin (name gin_trgm_ops);

Query:

SELECT p.id, p.name, array_agg(vo.name)
FROM   product           p
JOIN   product_variation pv ON p.id = pv.product_id
JOIN   variation_option  vo ON pv.variation_option_id = vo.id
WHERE  EXISTS (
   SELECT -- list can be empty
   FROM   variation_option vo1
   WHERE  vo1.id = pv.variation_option_id
   AND    vo1.name ILIKE ('%' || $1 || '%')
-- AND    vo1.name ~* ANY($1)  -- for array input
   )
GROUP  BY p.id;

Related:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


how to ignore the SSMS (sql server management studio) sql server ansi settings?

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level.

In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and so on, to control the way the following statements are interpreted.

With stored procedures it is a little more complicated:
When a procedure is created it remembers the current values for the ANSI_NULLS and QUOTED_IDENTIFIER options and uses them within any call to that procedure, but the same is not the case for other settings like ARITHABORT and ANSI_PADDINGS - for those you need to use SET <setting> ON/OFF in the procedure to fix the behaviour. See the Interoperability section of CREATE PROCEDURE (Transact-SQL) in the product documentation for more description of this situation.


It can be as complicated as you want, no issues with that. My own recommendation, for better manageability, if the tsql you are planning to use on the job step is too long, better create a stored procedure. This way you just call the sp from the job step and you do all your modifications there. And on the job itself just take care of the scheduling.


Oracle Audit: Why Logons are less than 0.1% of logoffs?

It may depend on whether you are using connection pooling or not.

Also, do you realize LOGON and LOGOFF for a session are recorded in the same audit row? So here the LOGON is showing how many are still logged in right now.

Consider the following scenario.

SQL> select ACTION_NAME, username, count(*) from dba_audit_session group by action_name, username;

When you run this first time it shows LOGON 1.

Without logging off, try to connect as the same user as you have connected before.

SQL> select ACTION_NAME, username, count(*) from dba_audit_session group by action_name, username;

Now, it shows LOGON 1 and LOGOFF 1.

SQL> select ACTION_NAME, username, count(*) from dba_audit_session group by action_name, username;

Now, it shows LOGON 1 and LOGOFF 2.

Each time you are seeing the 1 currently logged in session as 'LOGON', and the (increasing) number of completed sessions as 'LOGOFF'.


Based on a_horse_with_no_name's comment, I started searching around psql and found the solution:

\set VERBOSITY verbose
SELECT * FROM tgvbn();

ERROR:  42883: function vfjkb() does not exist
...

Now that goes into .psqlrc.  Details and further options can be found in the psql documentation.


Dynamically REVOKE CONNECT FROM guest in SQL Server 2005

I ran your script with no problems so my guess is that something is wrong with your conformation script.  I don't see anything specifically wrong with it but here are a couple of links with additional information about disabling guest.

http://blog.sqlauthority.com/2012/02/24/sql-server-guest-user-and-msdb-database-enable-guest-user-on-msdb-database/

http://blog.sqlauthority.com/2012/02/20/sql-server-disable-guest-account-serious-security-issue/

Here is a a modified version of Pinal Dave's script that should help you confirm.

EXEC sp_msforeachdb 'USE [?];
    SELECT ''?'' DBName, name, permission_name, state_desc
    FROM sys.database_principals prin
    INNER JOIN sys.database_permissions perm
        ON prin.principal_id = perm.grantee_principal_id
    WHERE name = ''guest'' AND permission_name = ''CONNECT''';


You can schedule an agent job to run at startup (there's an agent schedule for that), but you would have to add logic to the job to see if it "missed" the previous schedule. for instance, you can check the backup history for the relevant database and see how old the most recent backup is, and then conditionally perform the backup.


How can Restore MS SQL 2000 database To Ms SQL 2012

You must first restore the 2000 database to a SQL Server 2005 instance, then take a backup from the 2005 instance to restore on the 2012 instance.


Replicating into a Percona XtraDB Cluster

I suspect you are 'out of luck'.

Was there some reason not to have 'primary' part of the cluster?

Look into pt-table-checksum and pt-table-sync.  These may be a way to incrementally 'fix' the Slave to get it synced with Primary.


In the referenced link How to force the use of row locks? Paul White demonstrates that index reorganization cannot happen if that index has page locks disabled.

You can, of course, reset Page locking back on before doing a reorganization and then turn it off again afterward:

-- Allow page locks on Index
ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = ON);

For what it is worth, in response to this question https://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server Remus Rusanu gives the statement on disabling page locking on an index.  But he also comments: "I would never turn this OFF on my database. The solution is always to properly design the schema and the queries so that scans (which are the culprits for escalation) don't occur to start with."

This does not mean that it is wrong to disable page locking, but it points out that there are other ways to work through the problem.

You also might be interested in reading about Lock Escalation (SQL Server 2008 R2 is the most recent document I can find) at: http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx


Postgres Text Search supports prefix search

select 'housewarming'::tsvector @@ 'hou:*'::tsquery; --true

I looked at amazon's search, and guess what? ta doesn't return entertainment


How to make sure change_tracking statistics stays updated

Since you are on SQL Server 2016 with DB compat mode, the behavior of TF 2371 is the default behavior.

  You no longer have to enable trace flag 2371 in SQL Server 2016 or later because the corresponding behavior is enabled by default.

You should enable auto update async database option so that when sql server updates the stats, it does not affect your workload. Changing this db option is an online operation.

What you can do is to leverage sys.dm_db_stats_properties - new DMV to decide if you must manually update statistics for a table.  You can have a sql agent job configured to check and update stats every x min or hour for certain key tables.


Postgres copy data with \xYY as plain string instead of interpreting as encoded string

The backslash \ is the default escape character in the (default) text format of COPY. The format you describe is recognized as (quoting the manual here)

  backslash sequences are recognized by COPY FROM
  ...
  \xdigits | Backslash x followed by one or two hex digits specifies the character with that numeric code

.. which seems to be just as intended.
To avoid this effect you could use the CSV format instead, where the backslash has no special meaning:

COPY tbl FROM '/path/to/file/log.sql' (FORMAT csv);

Be sure to read the manual to learn about additional differences.

Alternatively you could replace every \ with \\ in your file with a sed script or something.

If the above doesn't solve your problem, please supply:

PostgreSQL version
Database encoding
Demo data
Definition of the table you COPY to
The exact COPY invocation you used


How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds

Create a new index that is based on movido,status, id_empresa, referencia, archivo

To convert the query to a covering index


Impact of table compression on HA Synchronous-commit mode scenarios

I've been using AGs for 3 years now, with table compression, and have not seen any material impact.

The data shunted between the servers is all log transport, and that itself is compressed, the compression of the pages themselves doesn't really factor in. Your only concern would be if you were under CPU pressure already and then added on that little extra for the table compression.


Give something like the below a try...

You'll obviously need to plug in your variables for your environment, check the data types (may need to add logic to keep leading zeros?), change from the final temp tables to your regular table(s), etc.

Works fine for me for import from XML files to temp tables without deleting the files afterwards but adding logic to delete files from the UNC path shouldn't be too difficult with another xp_cmdshell command.

DECLARE @folder AS VARCHAR(1000) = '\\servername\sharename\folder\subfolder1\'
DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)

-- create global temp table
IF OBJECT_ID('tempdb..##XMLImport') IS NOT NULL
    DROP TABLE ##XMLImport

CREATE TABLE ##XMLImport (
    R00000010 VARCHAR(7)
    ,R00000020 VARCHAR(1)
    ,R00000030 INT
    ,R00000040 INT
    ,R00000050 INT
    ,R00000060 INT
    )

INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command

-- create cursor
DECLARE filecurs CURSOR
FOR
SELECT REPLACE(filenameswithfolder, @folder, '') AS filenames
FROM @filesinafolder
WHERE filenameswithfolder IS NOT NULL

OPEN filecurs

FETCH NEXT
FROM filecurs
INTO @file

IF @file = 'FILE NOT FOUND'
    GOTO exitprocessing

WHILE @@fetch_status != - 1
BEGIN
    SET @sql = 'DECLARE @X XML

                    SELECT @X = P
                    FROM OPENROWSET(BULK ''' + @folder + '' + @file + ''', SINGLE_BLOB) AS Products(P)

                    DECLARE @iX INT

                    EXEC sp_xml_preparedocument @iX OUTPUT
                        ,@X

                    SELECT *
                    INTO #XMLResults
                    FROM OPENXML(@iX, ''/*/*'', 2) WITH (
                            R00000010 VARCHAR(7)
                            ,R00000020 VARCHAR(1)
                            ,R00000030 INT
                            ,R00000040 INT
                            ,R00000050 INT
                            ,R00000060 INT
                            )
                    EXEC sp_xml_removedocument @iX

                    INSERT INTO ##XMLImport
                    SELECT R00000010
                        ,R00000020
                        ,R00000030
                        ,R00000040
                        ,R00000050
                        ,R00000060
                    FROM #XMLResults'

    --PRINT @sql

    EXEC sp_executesql @sql

    -- process next file
    FETCH NEXT
    FROM filecurs
    INTO @file
END

exitprocessing:

-- clean up
CLOSE filecurs

DEALLOCATE filecurs

SELECT *
FROM ##XMLImport


It will automatically reset AFTER a successful login is made.


That page you linked, besides being quite old, talks about accesses from the same process through the same database connection (or through multipe connections in shared cache mode, which you should not use).

When not in WAL mode, multiple connections can read from the same database, but a writing transaction is exclusive, i.e., no other readers or writers are allowed.

In WAL mode, a writer and readers do not block each other, but there is still only one writer allowed.


In any case, when one thing hasMany of another how/where does it show up in application and database logic?

The only way I can think to answer this question is with another example of hasMany that may or may not answer your question.

err, except I should have called the table "comment" but I think the point stands.
I don't know that this really helps you understand anymore than what you already had.
So I'll also share a query that might demonstrate how this works:
declare @var char(5)
set @var = 'query'

select
   c.id
  ,c.text
  ,c.author
from comment c
inner join post p
   on p.id = c.parentid

which we expect to return many rows, because one post generally has many comments (in theory, of course not all posts will have comments)

Update:
So here's the thing about relations: They are EASY to model via diagram (see the very first tool I pulled out to answer this question) but they are HARD to diagram via text. So instead of trying to define whether we mean 1:M or 0:N, we just write it in "near English" and say what we intend. Here's some alternatives:
one to one           hasOne               post hasOne author (not true for CW ;])
one to many          hasMany              The one in question
many to one          belongsTo            parent child, comments belongTo post
many to many         hasAndBelongsToMany  This describes a complex relationship
many to many to many hasManyAndBelongsToMany

As you see they can get quite complicated. This would ... once again! ... indicate a need to refactor your business logic into simpler and smaller chunks.
These can also be called "association types" and may be indicated by verbage like "WrittenBy" (a many to one) or "IsOwnedBy" (one to one? - context is key)
Before I walk away from the "hasMany" concept for the last time (well this is an update, more will likely be written) I want to mention that while it's useful for "plain English descriptions", it's also used in ActiveRecord styled installations. Take, for instance, Ruby. http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html The rest of this post (so far) is taken from that page:
class Project < ActiveRecord::Base
  belongs_to              :portfolio
  has_one                 :project_manager
  has_many                :milestones
  has_and_belongs_to_many :categories
end

This allows the ActiveRecord ORM to create the data model and to maintain the relationships as specified by the business logic within the program. But I don't write Ruby or Rails so that's as far as I can tell you, I just know that it exists there.


How to query the mongoDB admin database?

As per your log statement you are getting the error message such as

Error: error: {
"$err" : "not authorized for query on admin.system.version", "code" : 13
}

As github mongodb documentation here error_code("Unauthorized", 13).

As per MongoDB documentation here MongoDB grants access to data and commands through role-based authorization and provides built-in roles that provide the different levels of access commonly needed in a database system. You can additionally create user-defined roles.

A role grants privileges to perform sets of actions on defined resources. A given role applies to the database on which it is defined and can grant access down to a collection level of granularity.

You need to assign the read role or readWrite to the user.

For example :

Create Administrative User with Roles

use admin
db.createUser(
   {
     user: "appAdmin",
     pwd: "password",
     roles:
       [
         { role: "readWrite", db: "config" }

       ]
   }
)

For further your ref here, here and here


SQL AlwaysON. keeping Agent Job in sync, but with same JobID?

I'm fairly certain this is a Very Bad Idea™, but... yes.

Using the UI as normal, go ahead and create the job you want. Before you hit Okay in the bottom right, stop; instead click  Script in the top left hand corner of the dialogue window.

One of the batches that you will see in the resulting session will have sp_add_job, one of the parameters of which will be @job_id = @jobId OUTPUT (depending on your SQL Server version & SSMS user scripting preferences).

  Awesome! Problem solved! You can just manually supply @job_id, super duper!

Actually...

  Hey, jerk! I got an error!

  Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

Yea, if you take a look at ~line 130 of object_definition(object_id('sp_add_job')), you will see something like...

IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
BEGIN
  RAISERROR(14274, -1, -1)
  RETURN(1) -- Failure
END

If only there were some way to pretend to be the SQL Agent...

Or if you're feeling less fancy, you can always...

delete dbo.sysjobactivity
where job_id = '11111111-1111-1111-1111-111111111111';

update dbo.sysjobs set
    job_id = '22222222-2222-2222-2222-222222222222'
where job_id = '11111111-1111-1111-1111-111111111111';

...since sysjobs is a user table.

Obviously... it seems like the MS devs who wrote this may have been trying to protect you from yourself... So... maybe keep an eye out for weird, exotic breakage if you try this approach.


how to decrease column values by 20 percent using mysql

You need the reciprocal value of 20%, because you're new value isn't 120% it is the new 100% value.

round(listprice * 1.2,2) -- increase by 20%

The reciprocal for above +20% would be:

round(listprice / 1.2,2) -- decrease by 17.777777%

Or put basically: If you want to counteract an increase of 20% then you need to reduce by 17.7777777%.

You can verify this on Windows with the calculator. Beware of the round() function!

Mathematics Explanation

Multiplying a value with a number is another way of adding. If you want to add 100% to a given value, then you can multiply the value with 2:

listprice * 2

If we replace listprice with 100 then we have:

100 * 2 = 200

This is an increase of 100% compared to the starting value of 100.

If you want to increase a value by 20% then you want to add a fifth of the base value. This is the equivalent of multiplying by 1.2:

100 * 1.2 = 120

Now multiplying by 1.2 can counteracted by dividing the result by 1.2 again:

120 / 1.2 = 100

This is the equivalent of what you are looking for. You want to counteract the addition of listprice +(listprice *(20/100)).

Working Example

A working example can be found at db<>fiddle (MySQL 8.0)

Using the Original Addition

After poring over your equation last night and this morning I found a way of using your original "addition".

                20
x = y + ( y * ----- )
               100

This can be converted to:

                20
x = y * ( 1 + ----- )
               100

Which as you may note is a multiplication again. So if you were to want the original value of y, then all you need to do is to divide by original multiplicand:

                20
y = x / ( 1 + ----- )
               100

Or written as :

          x
     ------------
y =         20
     (1 + ----- )
           100

In your MySQL equation this would be:

listprice = round(listprice / (1 + (20/100)),2)

Working Example Using Addition

A working example can be found at db<>fiddle (MySQL 8.0)


How can I monitor the import progress of a large CSV File in mysql?

Are you looking for something like this?
https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

    create table watcher_tbl(time datetime, num_rows int);

    create event watch_event
    on schedule every 2 second
    do
    insert into watcher_tbl(time, num_rows) values (current_timestamp,
                                                    (select count(*) from watched_table));

    -- to see the progress.
    select * from watcher_tbl;

    -- to stop the event after loading is done.
    drop event if exists watch_event;


Adding second SQL Server to create 2 Node cluster

Get us HA is often spoken by management but the clarity isn't there.  You'll want to first cover your butt to make sure you're both on the same page as what "HA" is.  First set acceptable data loss policies and acceptable downtime in case of an emergency.  Answer 1 will be 0 data loss and 0 seconds down time.  Great, 6 9's uptime will require their entire budget and then some.  Once they realize that they will decide that they can probably be down x minutes or hours per year.  They will also help you define 'maintenance hours' where you can have extended outages if possible.

Once you know if you can have maint. hours and how much downtime is acceptable, you can pick an HA solution.  Remember that clustering shares storage, so you have 1 single point of failure.  It's probably not what you're looking for in this case.  You will probably want to do async mirroring which is sadly only available in enterprise.  If you don't have enterprise you will have to go with Sync. mirroring.  This requires your write to finish on the 2nd server before it calls it complete on the first server.  Unless you're monitoring the full stack and have a witness server for auto fail over, this will probably cause you more outage time where users can't write, than it'll save.

The easiest and cheapest option is log shipping.  This doesn't allow you to auto failover like mirroring with a witness or clustering (albeit not with storage failure as the whole cluster goes down).  It is however easy to setup, maintain, and can ensure you're only 1 or 2 transaction log backups behind.  Then you can practice 'tail of log' backups and automate it if you still have access to the primary to get ALL the data out and bring up the secondary.

Come back with uptime/data loss reqs and we'll help you dig deeper if you need it.


Mongo Atlas API - Get Databases and Collections in a Cluster

The MongoDB Atlas API is for programmatic access to Atlas' management, monitoring, and backup  features. API connections are to the Atlas service, not to the underlying MongoDB clusters.

To query data in your clusters (for example, listing database and collections) you need to connect to each cluster using an authenticated MongoDB driver or client.

  I want to avoid having to connect to each cluster and collecting this information with a query - this is for a service discovery tool I am building for inventorying purposes.

This is the currently the only supported approach.


Cannot set innodb_buffer_pool_size above 1023M in my.ini

"Persisted options" is new in 8.0.  See if this works:

SET PERSIST innodb_buffer_pool_size=10737418240;

For more info:

https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html


How do I go from a PostgreSQL native point type to a PostGIS point?

There is no implicit conversion or casting from a PostgreSQL native point type to a PostGIS geometry, and a PostGIS POINT has a Spatial Reference System attached to it. ST_MakePoint will create the point geometry, but it'll be apart from any system that maps it to Earth,

SELECT ST_MakePoint(p[0],p[1])
FROM ( VALUES (point(-71.1043443253471,42.3150676015829)) ) AS t(p);

We're accessing the point type with the array syntax,

  It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1. For example, if t.p is a point column then SELECT p[0] FROM t retrieves the X coordinate and UPDATE t SET p[1] = ... changes the Y coordinate. In the same way, a value of type box or lseg can be treated as an array of two point values.


This answer presumes there is no many to many table with BidId related to ContractorId which should probably exist.  Examples of the tables from which you are querying are as helpful as seeing the example of expected result.

DECLARE @BidId int = 1

    SELECT BidID, ContractorName, ContractorEmail
    FROM tblBids B
    JOIN tblContractors C
    ON B.ContractorId = C.ContractorId
    WHERE B.BidId = @BidId


How to recover the data from Redo Log Group which has not been archived yet

If you have access to the logfile in redo group C, and you can copy it to the new server, then you will be able to use it for recovering the database on the new server, so you will not lose the data in it.

If you no longer have access to the redo logfile (because not only the database, but the server or storage also crashed, and you are unable to start/repair them), and all you have is your backup, then yes, you will lose the data created after the last backup.


How do I get the size of a Vertica database?

There are several ways to get the size of a database, each suited to a slightly different use case. It's important to note that Vertica uses raw and compressed data in different ways, and that you should be conscious of which size you require. For example, licensing is based on the raw data size.

Raw Size

The raw size is useful for capacity planning or monitoring license utilization (it's the space the data would take up if it wasn't compressed). To get the raw data size of the entire database, you can either use the GET_COMPLIANCE_STATUS() function, or query the system table v_internal.license_audits.

The GET_COMPLIANCE_STATUS() function retrieves information about the most recent audit. An audit estimates the raw data size of the database and stores the information in v_internal.license_audits. By default, audits take place daily at 23:59 and can be configured or manually run.

Here's some example output:

dbadmin=> SELECT GET_COMPLIANCE_STATUS();
                            GET_COMPLIANCE_STATUS
---------------------------------------------------------------------------------

Raw Data Size: 4.83TB +/- 0.24TB
License Size : 30.00TB
Utilization  : 16%
Audit Time   : 2014-05-11 23:59:49.763799+00
Compliance Status : The database is in compliance with respect to raw data size.

License End Date: 10/30/2014
Days Remaining: 171.18

If you have permissions, you can directly query the license_audits table:

SELECT /*+ label(license_utilization)*/
       audit_start_timestamp,
       database_size_bytes / ( 1024^3 ) AS database_size_gb,
       license_size_bytes / ( 1024^3 )  AS license_size_gb,
       usage_percent
FROM   v_catalog.license_audits
ORDER  BY audit_start_timestamp DESC
LIMIT  30;

Further, if you wish to get raw data size at the schema level, you can use this (from vertica.tips):

SELECT /*+ label(estimated_raw_size)*/
       pj.anchor_table_schema,
       pj.used_compressed_gb,
       pj.used_compressed_gb * la.ratio AS raw_estimate_gb
FROM   (SELECT ps.anchor_table_schema,
               SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb
        FROM   v_catalog.projections p
               JOIN v_monitor.projection_storage ps
                 ON ps.projection_id = p.projection_id
        WHERE  p.is_super_projection = 't'
        GROUP  BY ps.anchor_table_schema) pj
       CROSS JOIN (SELECT (SELECT database_size_bytes
                           FROM   v_catalog.license_audits
                           ORDER  BY audit_start_timestamp DESC
                           LIMIT  1) / (SELECT SUM(used_bytes)
                                        FROM   V_MONITOR.projection_storage) AS ratio) la
ORDER  BY pj.used_compressed_gb DESC;

Compressed Size

The compressed size is the actual size of the data on the disk. This is useful for estimating disk space usage as Vertica recommends that at least 40% of space is available at all times. You can get the compressed size from column_storage or projection_storage system tables.

Using projection_storage will also return any empty tables (from vertica.tips):

SELECT /*+ label(compressed_table_size)*/
       anchor_table_schema,
       anchor_table_name,
       SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb
FROM   v_monitor.projection_storage
GROUP  BY anchor_table_schema,
          anchor_table_name
ORDER  BY SUM(used_bytes) DESC;


Sql server/azure connection string parameter for readonly access?

No, there is unfortunately no such connection string parameter.  The "ApplicationIntent=ReadOnly" option is just used by the SQL driver to attempt to route your connection to a read only replica.

I'd suggest that, if there are users or processes that should only have read access to this database, you accomplish this using the built-in SQL Server permissions.  In other words, only add those users / processes to the db_datareader database role if they are accessing tables and view directly.

If they are accessing data through stored procedures, be careful to only grant them EXECUTE permissions on the read procedures.


Beware!  You have an extra space in some table and column names.  (I removed them when wrapping the queries.)

This is the optimal index for all those queries, and there is no benefit in saying USE INDEX:

INDEX(EventType, event_log_domain_id)  -- in this order
INDEX(event_log_domain_id, EventType)  -- in this order for #3

This index will handle the WHERE and be "covering".  Include both indexes.

More on indexing:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql  It explains how the composite index should start with all columns tested with =, then move on to IN, and finally one "range".  (Your examples have no ranges).


Why would an application use one central metadata table to hold metadata for all other tables?

I believe this is called a "Big Ball of Mud".  The not so hidden disadvantage is the system appears to have done away with normalization of the data model for the metadata.  This is likely lead likely lead to inconsistencies over time.  However, it is possible that the script you described, just rebuilds it from the database's metadata.

All the databases I have worked with, have a nice normalized data structure for the meta data in place of the single metadata table you are describing.  This involves the appropriate tables for the appropriate metadata.  Most cache the query and resulting optimized execution plan for recently used queries.

If the code is figuring out the required to query on the fly, by querying the metadata table, it is likely slower than it needs to be.

A query like select a,b,c from T, where d = ? can be supported by a single query with a parameter. This also prevents SQL injection when someone enters a value like 1 or 1 == 1.  On a desktop application it may not be that serious, but many companies have learned how serious it can be.

There is a reason many developers use hibernate, Spring, and other data access layers.  Some of these can build the database required to support the application, and also work with databases that have already been built.


Be careful with the term Always ON, This refers to Always On Failover Cluster Instances and Availability Groups (Also database mirroring but this is deprecated). FCIs and AGs are very different technologies.

To answer your question AGs can be installed on stand alone and clustered instances. It all depends on your HA/DR requirements.

2nd question:
Yes AGs can automatically failover their databases to other SQL instances (refered to as nodes). The servers running the SQL instances all need to be in the same windows failover cluster.

This white paper from Microsoft might be a good place for you to start reading on AGs.


You can still use SQL Log Shipping in Web Edition, this will provide the functionality you need.
It will basically make log backups on schedule and restore to your secondary location.


Mysql Connector C++ statement values question

While both methods are perfectly legitimate, there is one major difference.

When you prepare a SQL statement, you precompile it and substitute values using setInt().
When you hardcode the actually values, you have to precompile the SQL every time.

Please note when it comes to Com_stmt_prepare and Com_stmt_execute

only Com_stmt_execute would increment
both increment in the MySQL Server Status when you hardcode

Prepared statements are only to your advantage if you maintain persistent or pooled connections. This is the case because certain data structures must be allocated on the server-side. If you connect and disconnect all the time, you have to construct and destroy the prepared statement along with a host of other connection-based resources (See my old post How costly is opening and closing of a DB connection?)


Enable autocomplete in an sqlite3 interactive shell

Question was ultimately answered on superuser.

The solution is to compile sqlite3 with readline support.


Create 7 day rolling average in PostGIS for each feature

This produces the desired result - after adjusting off-by-1 errors ②:

SELECT geom, date, count
     , CASE WHEN rn < 7 THEN NULL  -- ②
            ELSE round(rolling_avg_count, 2) END AS rolling_avg_count
FROM  (
   SELECT geom, date, count
        , AVG(count) OVER (PARTITION BY geom
                           ORDER BY date -- ①
                           ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING -- ②
                          ) AS rolling_avg_count
        , row_number() OVER (PARTITION BY geom ORDER BY date DESC) AS rn
   FROM   features
   ) sub;

db<>fiddle here

① With PARTITION BY geom, you don't need geom in ORDER BY.

② You had off-by-one error(s):

  I would like to calculate the 7 day rolling average

But you show a calculation for the 8 day rolling average (1 + 7).

  I'd like the calculation to stop when there a fewer than 7 following rows.

But you show a result for only 6 following rows (displayed values 11.57 and 13.43).

I made it an actual 7 day average, stopping when there are fewer than 6 following rows.

Performance optimization

The above adds an additional sort to the query plan, due to the descending sort order in the second window function.

This alternative query avoids that with an additional count():

SELECT geom, date, count
     , CASE WHEN rn < 6 THEN NULL -- ③
            ELSE round(rolling_avg_count, 2) END AS rolling_avg_count
FROM  (
   SELECT geom, date, count
        , avg(count)   OVER (PARTITION BY geom
                             ORDER BY date
                             ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING
                            ) AS rolling_avg_count
        , count(*)     OVER (PARTITION BY geom)
        - row_number() OVER (PARTITION BY geom ORDER BY date) AS rn
   FROM   features
   ) sub;

db<>fiddle here

A bit more verbose, adding an additional window function. But I expect it to perform better.

③ Adjusted to 0-based row number. (Cheaper than adding + 1.)


Setting Blank lines to ON before executing a SQLFILE in shell script

$SQLPLUS -S username/password@ip:port/servicename<< EOF
SQL-STATEMENTS
set sqlbl on
commit
EOF


Replication with fail over cluster in SQL Server 2014

Yes you can upgrade without affecting current features .

To upgrade a SQL Server failover cluster to SQL Server 2014, you must run setup with upgrade action on each failover cluster node, one at a time, starting with the passive nodes. As you upgrade each node, it is left out of the possible owners of the failover cluster. If there is an unexpected failover, the upgraded nodes do not participate in the failover until cluster resource group ownership is moved to an upgraded node by SQL Server Setup.

MSDN


Indexes getting highly fragmented during normal usage of system

If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to keep up with the number of page splits, you might look at gently adjusting the fill factor on the most critical indexes. This will improve write performance, at a cost to reads and the number of pages that can be cached (in the limited buffer pool available with Standard Edition).

Don't go crazy with low fill factors to start with. Pick a manageable number of the most problematic indexes, make an estimate of the amount of free space needed to avoid too many splits during the normal workload between maintenance windows, and test the effects of setting the lower fill factor (normally no lower than about 90% for the first test). Do not just blindly set a lower fill factor for all indexes.

Bear in mind that nine times out of ten, the system has other, bigger, problems than those directly attributable to sub-optimally set fill factors. You should be monitoring the important performance aspects of the system, and be able to make the case for any changes based on a sound technical analysis of real historical data.

Reading material:

Specify Fill Factor for an Index in the product documentation
5 Things About Fillfactor by Kendra Little
Does index fill factor affect fragmentation? by Erik Darling
Stop Worrying About SQL Server Fragmentation by Brent Ozar
What is the Best Value for Fill Factor in SQL Server by Eric Blinn
SQL Server Index and Statistics Maintenance by Ola Hallengren


This might not be the answer but a general tip for designing is to remember that for your initial logical design. A logical relational DB schema is only made up of one-to-many relations. e.g. One Park has many Photos and One User has many photos. That allows for a many-to-many such as; A user may log photos for many parks and a park may have photos logged by many users.  These are called business rules. You want to always go for just one-to-many relations in your model of the business rules.  Later you might add a few one-to-one entities but rarely.  As for many-to-many , just exclude them from your model and represent them as one-to-many-to-one ( i.e. as two one-to-many relations). Which you have done.

Your schema design looks fairly close to reasonable but you have included some one-to-one relations. They are normally not part of a new schema design. They are basically extension tables/entities and done as a way to optimise performance in cases where some fields are rarely used.  For now, I would exclude modelling them.  But actually in your particular model, I think you just made a mistake with the notation.  So you probably want to keep those entities but correct the relation types.  e.g. Park-to-Favorite Park should be one-to-many ( 1:M ) .  The Park Type to Park should be either 1:M or M:M ( via two 1:M ) , depending on your business rule.  Can a Park be of more than one type; I expect a ParkType can be used by several Parks. If so then the relation is 1:M ( ParkType:Park ). If a Park can also be of several types then you'll need another entity and create the relation 1:M:1 via ParkType:ParkTypePark:Park .
Now, location is tricky.  Example data would help, as you list Address, long, lat as fields I assume that a location record is only usable by exactly one Park.  If location is like a region where many Parks can be associated with one Regional Location then the entity makes sense to me (as a 1 Location: M Parks relation ) . But if the Location is very precise so only one Park can ever be there, then you don't need the Location entity, store that data in the Park entity.  However, for optimisation you may put it back in if you think that searching for Parks in a Location area would be faster using a separate table with only that in it.  But I doubt you need to do that.

Not sure what ParkSubmission is, I'm guessing it is suggestions for new parks. If so, possibly you don't need it and can instead have a status flag on the Park record to show that it is a Suggested, Draft, Approved, Rejected, etc.


Managing ibtmp1 File Growth on MySQL 5.7

Unfortunately, all you can do is what said in my earlier post

SET GLOBAL innodb_fast_shutdown = 0;
Shutdown mysqld
delete ibtmp1
Start MySQL DB

ALTERNATIVE
Attach another volume to the VM and map ibtmp1 to that other volume using

innodb_temp_data_file_path (As of MySQL 5.7.1)
innodb_tmpdir (As of MySQL 5.7.11)

Please be sure to read these docs carefully. Also, try to optimize your queries to avoid make large tmp tables.


Options for having same tables in 2 different data bases

What you're looking for is Transactional Replication.

It allows you to replicate specific objects from one database to another.


What is the safe+fast way to move all Postgresql data to another machine?

No, the pg_xlog directory can't be omitted even if the server has been properly shut down before copying the data directory.

If you try, you should see that the target server will fail to start, complaining that it can't find a checkpoint record.


Migrating a database from SQL Server 2012 to the cloud

Have a look at Importing Data Into SQLServer on Amazon RDS.

Also, from codeplex SQL Database Migration Wizard with a reference video as well.


How to migrate between schemas in bulk in SQL Server?

The basic concept is actually quite simple: you generate a script from sys.objects and sys.schemas that builds ALTER SCHEMA TRANSFER statements. So for example, you have three objects in the dbo schema, and you want to move all of them to the blat schema:

Table: dbo.foo
Table: dbo.bar
View:  dbo.vFooBar

The following code:

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

SELECT @sql += N'
  ALTER SCHEMA blat TRANSFER dbo.' + QUOTENAME(o.name) + ';'
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo';

PRINT @sql;
-- EXEC sp_executesql @sql;

Will yield this script (but perhaps not in this order):

ALTER SCHEMA blat TRANSFER dbo.bar;
ALTER SCHEMA blat TRANSFER dbo.foo;
ALTER SCHEMA blat TRANSFER dbo.vFooBar;

(You may want to add additional filters to leave out objects in the dbo schema that you don't want to move, leave out certain object types (e.g. maybe all of your functions are utility functions and don't need to move), generate the script ordered by object type, etc.)

But there are a couple of problems with moving all of your objects to a new schema:

Probably a lot your code will still reference these objects as dbo.object - there is no easy way to fix this except brute force. You can probably find all of the occurrences of dbo. pretty easily, but these can also return false positives, such as EXEC dbo.sp_executesql, dbo. in comments, true references to objects that remain in the dbo. schema, etc.
Your dependencies will probably be completely out of whack, but I haven't thoroughly tested this. I do know that in this scenario:

CREATE SCHEMA blat AUTHORIZATION dbo;
GO

CREATE TABLE dbo.foo(a INT PRIMARY KEY);
CREATE TABLE dbo.bar(a INT FOREIGN KEY REFERENCES dbo.foo(a));
GO

CREATE PROCEDURE dbo.pX AS
BEGIN
  SET NOCOUNT ON;
  SELECT a FROM dbo.bar;
END
GO

CREATE VIEW dbo.vFooBar
AS
  SELECT foo.a, bar.a AS barA
    FROM dbo.foo
    INNER JOIN dbo.bar
    ON foo.a = bar.a;
GO

ALTER SCHEMA blat TRANSFER dbo.foo;
ALTER SCHEMA blat TRANSFER dbo.bar;
ALTER SCHEMA blat TRANSFER dbo.pX;
ALTER SCHEMA blat TRANSFER dbo.vFooBar;

The foreign keys actually migrate more smoothly than I expected (with the caveat that I'm testing on a much more recent version than you). But because the code in blat.pX still references dbo.bar, obviously executing the procedure:

EXEC blat.pX;

Is going to yield this error:

  Msg 208, Level 16, State 1, Procedure pX
  Invalid object name 'dbo.bar'.

And dependency queries, such as:

SELECT * FROM sys.dm_sql_referenced_entities('blat.pX', N'OBJECT');

Will yield this error:

  Msg 2020, Level 16, State 1
  The dependencies reported for entity "blat.pX" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity.  Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

And querying the view:

SELECT a, barA FROM blat.vFooBar;

Yields these errors:

  Msg 208, Level 16, State 1, Procedure vFooBar
  Invalid object name 'dbo.foo'.
  Msg 4413, Level 16, State 1
  Could not use view or function 'blat.vFoobar' because of binding errors.

So, this could involve a lot of cleanup. And after you've fixed all the object references, you'll probably want to recompile all of the modules and refresh all of the views in the new schema. You can generate a script for that quite similar to the example above.


Generating time series based on existing table

You may be interested in learning an efficient way of doing this using a set-based-methodology instead of a procedural (loop-based) methodology.

SQL Server typically performs set-based operations far more quickly than procedural operations.

The code below shows how to use a select statement to generate 366 rows containing dates from 2016-01-01 to 2016-12-31 (2016 is a leap-year).  We then combine each of those rows with the "Zip" and "Population" columns of your source table, using a CROSS JOIN.

First, we setup a test-bed with your table, and a new destination table:

USE tempdb;

IF OBJECT_ID('dbo.Zips') IS NOT NULL
DROP TABLE dbo.Zips;
CREATE TABLE dbo.Zips
(
    Zip VARCHAR(5) NOT NULL
    , Pop INT NOT NULL
);

INSERT INTO dbo.Zips (Zip, Pop)
VALUES ('00001', 100)
    , ('00002', 200)
    , ('00003', 250);

IF OBJECT_ID('dbo.ZipsWithDates') IS NOT NULL
DROP TABLE dbo.ZipsWithDates;
CREATE TABLE dbo.ZipsWithDates
(
    Zip VARCHAR(5) NOT NULL
    , Pop INT NOT NULL
    , ZipDate DATE NOT NULL
);

This inserts rows, one for each day in the year, for each row in Zips:

;WITH dates AS (
SELECT TOP(366) /* 366 days in a leap-year, which 2016 is */
    TheDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id), CONVERT(DATE, N'2015-12-31'))
FROM sys.objects o1
    CROSS JOIN sys.objects o2
)
INSERT INTO dbo.ZipsWithDates (Zip, Pop, ZipDate)
SELECT z.Zip
    , z.Pop
    , d.TheDate
FROM dates d
    CROSS JOIN dbo.Zips z

This shows the contents of the new table:

SELECT *
FROM dbo.ZipsWithDates;

And this is the output from the first few rows shows:

Note, you should always specify the schema, and please don't use reserved words for column names


ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes

ORU-10027 will appear if you are using a lot of dbms_output.put_line in your PL/SQL with a small buffer amount. If you are just debugging then you should set this to a large amount (dbms_output.enable(10000000) is traditional though from 10g onwards it can be unlimted: dbms_output.enable(null)) but try to remove them when going to production.


The cardinality of 2  (male,female) is way too low.

Although you are using InnoDB and could have foreign keys, the parent table would have only two rows. It's not worth the processing power. Therefore, do not add another table. Indexing it is not worth it because a cardinality of 2 simply screams at the Query Optimizer "DO NOT USE THE INDEXES". Foreign Keys to a table with two values is not worth it.

Do not add a VARCHAR(1) because that is really two bytes.

There are four(4) options to use one byte

You could use CHAR(1) with 'M' and 'F' as the only values
You could use TINYINT with 0 for Male and 1 for Female
You could use TINYINT with 1 for Male and 0 for Female
You could use ENUM('M','F') (this might be two bytes)


Restoring a differential backup in SQL server 2005

You haven't shown us your RESTORE DATABASE command, but you appear to have omitted a recovery option, or specified WITH RECOVERY (which is the default). In either case, if you've done that, you cannot apply any further backups.

If you want to apply a series of backups, you need to specify WITH NORECOVERY on all (or all but the last) of the RESTOREs.


Select entries based on a combination of columns

To create sample schema and data

 IF OBJECT_ID('tempdb..#Contact', 'U') IS NOT NULL
        DROP TABLE #Contact ;

    CREATE
    TABLE   #Contact
            (
            ClientNumber [numeric] (20, 0) NULL,
            ContactDate [Date] NULL,
            Name [text] NULL,
            AdditionalInformation [text] NULL
            );

    INSERT
    INTO    #Contact
            (
            ClientNumber
            ,ContactDate
            ,Name
            ,AdditionalInformation
            )

    VALUES  (111,'2016-03-11','A','A'),
            (111,'2016-03-11','B','B'),
            (112,'2016-03-11','C','C'),
            (112,'2016-03-12','A','A'),
            (112,'2016-03-13','A','A'),
            (112,'2016-03-13','B','B'),
            (112,'2016-03-14','A','A'),
            (113,'2016-03-14','B','B'),
            (113,'2016-03-14','C','C');

Actual query

;WITH   CTERowNumber
AS      (
        SELECT  ROW_NUMBER() OVER (PARTITION BY ClientNumber, ContactDate ORDER BY ClientNumber DESC) AS RowNumber
               ,ClientNumber
               ,ContactDate
               ,Name
               ,AdditionalInformation
        FROM    #Contact
        )

SELECT  c.RowNumber
       ,c.ClientNumber
       ,c.ContactDate
       ,c.Name
       ,c.AdditionalInformation
FROM    CTERowNumber c
WHERE   c.RowNumber = 1 ;

To return

RowNumber   ClientNumber    ContactDate Name    AdditionalInformation
1           111             2016-03-11  A       A
1           112             2016-03-11  C       C
1           112             2016-03-12  A       A
1           112             2016-03-13  A       A
1           112             2016-03-14  A       A
1           113             2016-03-14  B       B


Monitor if sqlserver is up from distant server using sqlcmd

The best way (aside from buying a proper product) is to do it with PowerShell, with Invoke-SqlCmd, because it will give you more flexibility to check the response and do something with it.

I probably wouldn't confirm version because it's meaningless and will change when you upgrade or patch; I would confirm @@Servername matches what I expect in case the IP has somehow become mixed.

As for alternatives they're not really "better" but you can expand it as far as you want to:

You can check the Event Log for failed connection attempts. Just because you can connect doesn't mean the critical application which uses an AD account which just expired when it wasn't meant to, can connect. This is actually pretty useful. If you're not tracking failed logins then you absolutely should start, whether it's the event log, an audit, extended event, or standard trace.
Are all the databases online / sync'd? Just because you can connect to master doesn't mean one of the disks hasn't disconnected and caused the database to go into suspect mode.
You can use WMI/CIM to determine whether the service is running. Is your Agent running? It better be because otherwise your backups and other processes are probably going to stop. You're probably going to want to use WMI/CIM to check that.
Technically you can write a WMI handler which gets injected onto the server which can trigger an email notification when the SQL service stops/starts unexpectedly. But this is tricky and I wouldn't bother.
You could query performance counters to pull back logins/sec to see whether it has dropped radically from the norm. But it would be so tricky to identify outliers it's probably not worth the effort. On the other hand if it increases to > 1000 per second (4 million per hour, thanks to a broken Oracle software reading from SQL!) it can indicate a problem, so maybe you do want to catch it.


How can MySQL return an empty row matching a given table's schema, given the table name as an input string?

From a pure database perspective this isn't really possible, unless you made a View that had a single row with the default values you needed that matched the schema of your table.
This question might be better suited on StackOverflow for how to construct a new instance of an entity object in JavaScript or whichever other object-oriented languages you're using.


Gaining visibility into long running INSERT INTO ... SELECT

Postgres doesn't allow dirty reads so we need some other means of checking if rows are being inserted from a second transaction, such as:

select pg_relation_size('summary_show_unique_personas');

The value returned by this query will be increasing if inserts are currently happening (unless, I think, they are filling up space freed up by previous delete statements).


SQL Server - Basic Availability Groups used for Publisher, Distributor and Subscriber databases?

The short answer is Yes.

If you can do it in an AG, and you can do it in SQL Server standard - you should be able to do it in a Basic Availability Group. The only gotcha was the distributor which wasn't able to be on an AG - the Replication couldn't work with the listener right - until the releases described in your links.

So you would basically have three AGs. Each with one DB. The distributor would be in one. The Subscriber in another. The Publisher in another.

You have to have your distributor on a separate SQL Server instance. So this would be mean two instances participating in a Basic Availability Group for the distributor, and then two for the Publisher and, most likely, two for the subscriber. Because of the limitations in the Basic Availability Group.

You could have more than on AG on an instance and have one DB in each and that would be good. but your limit here is 1.) You are doing replication, likely for a reason so your publisher and subscriber would likely be on different instances. 2.) The distributor has to be separate from publisher and subscriber.


What is the standard time delay taken for the data to get reflected in standby database when a new data is inserted in primary database

I think what you want is real time apply, but for that, you'll have to create standby redo logs on your standby database first.  As a side note, to make role transitions easier, it is considered best practice to create standby redo logs on your primary database as well:

The standby redo logs should be the same size as your redo logs and you need at least 1 more group of standby logs than redo logs:

I use the following script on the primary database, which builds that create statements for the standby redo logs that I then run on the standby database:

select 'alter database add standby logfile group ' ||
to_number((select count(*) from v$log) + rownum) || '(''T:\ORACLE\STANDBYREDO\REDO0' ||
to_number((select count(*) from v$log) + rownum) || '.LOG'') SIZE ' ||
(select max( bytes/1024/1024) from v$log) || 'M;' as standbyredo
from v$log
union all
select 'alter database add standby logfile group ' ||
((select max(group#) from v$log) + (select count(*) from v$log) + 1) || '(''T:\ORACLE\STANDBYREDO\REDO0' ||
((select max(group#) from v$log) + (select count(*) from v$log) + 1) || '.LOG'') SIZE ' ||
(select max( bytes/1024/1024) from v$log) || 'M;'
from dual;

After the standby logs are present on the standby database, to start real time apply, you need to include the using current logfile phrase in your recover command:

SQL> alter database recover managed standby database using current logfile disconnect from session;

During shipping and apply, you can check to verify that the standby logs are being used by querying v$standby_log on your standby database:

select * from v$standby_log;

I found Tim Hall's guide on Standby Databases invaluable when I was learning how to setup a physical standby.


Test something like:

Open_Stk Calc = SUM(COALESCE(Open_Stk + Replen, 0)) OVER (PARTITION BY sku ORDER BY ShopPrioirty)

Or divide to two SUMs to avoid COALESCE() usage. Edit PARTITION expression if needed. - akina


How can I create a MySQL database based on a SQLite schema?

The three main things to change are:

Change AUTOINCREMENT to AUTO_INCREMENT
Replace double-quotes around database, table and variable names with backticks
Delete any lines with “sqlite_sequence” in them

You should then be able to run the SQL file in MySQL. If you encounter any errors, the fix should be apparent from the error message shown.


Not having a listener IP means that you will get the functionality provided by a FCI (Failover Cluster Instance) rather than a fully fledged AG + FCI solution.  In effect you are asking what are the effects of using a FCI as a data source as opposed to an AG and how can I expand on it.

The FCI will require you to stop a service on one node with shared storage and wait for it to start on a different node after the resources are started on it.  This takes time.  By using this IP you will have to deal with this downtime every time you patch or fail over.

An AG has the service running at all times on all the nodes and you get to set much better priority, along with assigning read only node options to your clients connection strings.  This means faster failover, dynamic rerouting of applications with the READ_INTENT connection setting, and better management of your entire AG solution and cluster as a whole.

It is very simple to add or remove a listener so do not worry if you don't have one yet.

Edit to question:
Having a listener or not in SQL Server doesn't affect a FCI failover unless you do something with the dependencies with each other.  Outside of that a FCI will remain independent and operate on the instance level. The FCI will failover if there is a issue with the heartbeat or it's initiated, the AG solution with a listener will do so at the database level.  Thus you can have your application working off an AG listener which can work independently of the FCI depending on how the solution is designed.  We have some FCI/AG MCM's and experts here who can give you more in depth answers too.


Answer to question 1:

From what you posted you can drop the first two indexes as the third will cover all of the queries you mention and the query optimizer see that as well when it builds the query plan (based on the plan you posted.)

Answer to question 2:

It's always using the third index because it has more data already in the index with the two additional index keys (InstanceId and AddressType). This keeps SQL from needing to pull InstanceId and AddressType from the primary key (the key lookup part of the execution plan) to satisfy the query.

What I would suggest is drop the first two indexes and rebuild the third with include columns to cover the other columns being requested in the query

Create index IX_Address_profile_instance_addresstype
on dbo.address  (ProfileId, InstanceId, AddressType)
include(<put in the remaining columns comma delimited>)
with (drop_existing=on,sort_in_tempdb=on)

This should help with the queries and should remove the key lookup from the query plan.

See if the locks drop off after these changes and if they don't we can dig in a bit deeper.


What is the use of ibdata1 file in mysql?

In My Opinion you should use your solution1.

I am not very clear with what you will accomplish with the solution2 and solution3. with innodb_file_per_table OFF because they will never shrink the ibdata size whenever there delete,drop or truncate on an InnoDB table so using these option your ibdata will grow endlessly.

For your information have a look at this way to reduce the size of ibdata in mysql.?

without using  innodb_file_per_table, the ibdata file never compresses or shrinks, Not when you delete a row, drop a table, or a database. In no time, 3GB of data can become a 20GB file if you have an active queuing system.

Have a look why to use innodb_file_per_table : Reasons to use innodb_file_per_table.


Cannot connect to database remotely (not even locally when using ip address)

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

You have the localhost ip in the listener.ora (127.0.0.1), change this or add another line with the NIC ip.