pt-table-checksum not finding differences

When you stop a slave the master has no idea what its slaves are.
pt-table-checksum doesn't see your slaves.

pt-table-checksum is a mature tool and the most popular in the Percona toolkit. The data inconsistency is easy to reproduce. Just write something on a slave. By the way, there is a vagrant master->slave configuration in https://github.com/twindb/twindb_table_compare, you can test pt-table-checksum on it.

By default pt-table-checksum connects to localhost. Do you run it on the master? Can you connect to the slave with mysql client with same user/password?

There were two issues preventing the check from working correctly:

When replication is stopped on a slave, the master will not see it as a slave (and therefore not check it).
Permissions allowed pt-table-checksum to connect to the master, but not to the slaves (since that account was restricted to localhost). However, the tool will not show permission denied. Instead it just ignores the slave.


How do I enable READ-only (passive) mode in a particular RDBMS?

I'm going to attempt to answer this question for Oracle.

Historically Oracle has never supported read-only databases.

It's possible to make a tablespace read-only: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces006.htm

Individual tables can be made read-only: http://www.oracle-base.com/articles/11g/read-only-tables-11gr1.php

Or it's possible to make a select-only role and assign that to the user who logs in rather than the normal readwrite-role.


SQL Server High Availability and Sharepoint - log file grows when no database activity

The cleanup is normal behind the curtain SQL server functionality.  Never ever ever disable this.  I would increase the transaction log backup frequency.  I like mine set to every 15 minutes to keep the backups small and fast.


Using bulk insert is the simplest solution.

Using SSIS will require a development environment with visual studio, installing additional components like SSDT, designing the DTS package and configuring the execution of the DTS (on a SSIS server or directly via dtexec). Although this takes time, it will give you a lot more of flexibility to work later on if your requirements change.

If your task is as simple as loading a CSV or text file to a table in SQL just use bulk insert, you can do any additional transformation with SQL on a staging table and being only 200 rows (even if it increases a little), you shouldn't have any performance issues.


Setting parameters like PCTUSED when creating new tables in ORACLE 12 C

"Or will ORACLE automatically set these values efficiently"

Well, Oracle will set them to something by default ... probably not very "efficiently" though ;) For that, you're going to want to do a bit of research and groundwork on what YOU need.

As mentioned by Wernfried in comments, LOCALLY managed tablespaces are the way to go, and once you have those defined, specifying those options per table is wasted - Oracle will ignore them.

It's generally seen as better to setup certain types of Tablespaces for different types of tables used.

Small, mostly read-only tables: 1 tablespace: set to small Extent sizes, and small/0 pctincrease.

Larger, highly volatile tables: another tablespace: larger extent sizes, larger pctincrease ..

etc.
You need to work out what your needs are, and what kinds of tables you have (and then what tablespaces/storage options you'll want).


data driven subscriptions in SSRS - pulling a password value from the database

I just thought of an issue.  A user could take the column that contains the password and use it as the subject of an email.  Silly me!  I am not sure why I didn't think of this before.

Oh, well - documented here for others' future reference!


Execution plan showing missing index but query is fast

You don't need to add the index right now.  All the "missing index" message means is this might be helpful.  It might not be honestly, or there might be an index that helps even more.

As to when you need to add the new index?  Well let's say you have 100 rows in the account table but it's not really in use yet.  In a couple of months you are up to 10,000 rows and the query is now taking half an hour or more.  At that point you might consider adding the index.  And when I say consider, I mean test it in a test environment and see if it actually improves things.

Other considerations include:

How many indexes do I already have on this table? If you have 10+ indexes then I'd be more concerned than if you had say 2.
How important is this query?  You say you are running it every night.  At 1 second that's fine (assuming you only run it once).  If on the other hand it's part of a process and being run 1000's of times each night then it might be more of an issue.  If it get's to a point where it's taking long enough to cause you issues (causing problems with another process, or running into times when users are in the system etc) then you need to look at performance and adding indexes.


you need to find the corresponding error in event viewer in windows log\application find the error, one that the source is MSSQLServer and has happened, at the time you started the service.
detail of this error will help you resolve the issue.
(in the image below there is no error in it, it's just an information message, but in your case you may notice an error)

you may also want to check in system logs in event viewer, in the service Control manager to find a related error.


Sort all monthly incomes and group by year

Quick solution:

SELECT MONTH(date) AS monthno
     , SUM(amount) AS monthsum
  FROM incomes
 GROUP BY monthno
 ORDER BY monthno
;

More date functions are available for getting a text version of the date.


Functioning of Oracle's Server result Cache

cached results are invalidated and regenerated when transactions occur
  against the underlying data

I am fairly confident the same would be true for DDL changes but did you mean DML? The long and short is that Oracle is not going to allow an inconsistent result.


Will large transaction log file slow db recovery?

Not directly, but if there was a large transaction running at the time of failover that could cause both a large log file and a slow failover.


The short answer is yes.

As usual, after full backup follows differential backups, that completely depend on the last full backup. It means that if the database failure occurs there will be no chance to apply scheduled differential backups because they will be with another LSN.

The solution is to use COPY_ONLY option.


Yes backing up a database will affect performance if you run queries against the server / database.

Your server no matter how amazing it is has a limited amount of resources; backing up the database will add load to the disks and cpu; the amount depending on the size and the options that were chosen for the backup (e.g. compression or verification will add more load.)

What you can do is use a backup strategy that does a full backup at a low utilisation time and smaller incremental transaction log backups during the day to minimise the backup time window if you are backing up in operational hours. Or you could investigate alternatives like database mirroring which may help you remove the need to backup the active database; at the cost in terms of extra server resource and a small increase in load on the server for all transactions.


Relationship between two multi-valued child tables

Before I attempt to answer your questions, just a couple of hints that may help
you to create the perfect model :-) Bear with me.

When you construct an ERD, try and avoid "database" terminology such as:
primary key, foreign key, table, row.  Instead, use the terms "entity", "relationship", "unique identifier", "instance".
Use singular nouns as entity names.  When creating an entity, think about a SINGLE instance (of an entity).  This may make it easier to find the correct cardinalities.
You can draw as many relationships as you like.  However, only keep the relationships that make sense in your system (eg if there are 5 entities, and the relationships in your diagram make it look a bit like a pentagram inscribed into a 5-sided polygon, then there are - in all probability - too many relationships).  The MANY side of a relationship will eventually be transformed into a foreign key (which we should not worry about when working at the ERD level).

Now to your questions:

Q0) However, is it possible to create a Many:Many relationship between ElibibleEquity and EligibleIndices if they are children to the TradeBasket?

Answer 0)  Yes.  Just make sure that this really makes sense (see answer 1).

Q1) To facilitate a relationship between EligibleEquity and EligibleIndices, what should the cardinalities should i adopt (red or purple - in relation to the ERD)?

Answer 1)  Find out, and use, whatever is correct!
   Does EACH EligibleEquity (potentially) use/contain ONE OR MORE EquitableIndices?
   AND: Is EACH EquitableIndex used/contained within ONE OR MORE EquitableEquities?
   -> If the answer to both of these questions is: YES, then model a many-to-many relationship. (This will require an "intersection" or "junction" later on -> see the HYPOTHETICAL... entity in the attached ERD.)

Q2) If the EligibleEquity and EligibleIndices should be mapped as multivalued entities of the TradingBasket, what would the primary key of the junction table between EligibleEquity and EligibleIndices be?

Answer 2) -> The key for this could be: the combination of the UNIQUE IDENTIFIERs (UIDs) of TradingBasked and EligibleEquity (or EligibleIndex, respectively).

Eg - situation as discussed in "Answer 1": if the UIDs are the attributes(EligibleEquity)TYPE, and (EligibleIndex)INDEX_NAME, respectively, then a combination of these (unique) values can serve as a UID of the intersection/junction. (-> See the keys of the HYPOTHETICAL... table in the attached Relational model.)

There is more to be considered, and I'm sure that there will be more questions.  Let us know how you get on!


What have you tried?  Are both of these queries running slowly?  Are there other operations which have become a problem?  Since you have a live system, rather than speculating about what might be causing a problem, you can add some profiling to be sure.

Your first query can be answered by an index on ( Hour, PuzzleScore ), in that order.  Your second would require adding Iip; since it should have excellent cardinality, I'd put it first: ( Iip, Hour, PuzzleScore ).  Check with EXPLAIN to confirm whether the indices are actually used; depending on row counts and cardinality, there may be no benefit.

Since you're querying on Hour either way, I'd consider clustering on that field (in conjunction with a unique field or combination of fields to ensure uniqueness, of course).

Caveat: I mostly work in SQL-Server, I'm no expert on MySQL.


How to control reindexing that results in high log send queue

Insert the following code into the IndexOptimize procedure to implement a wait that will allow the log send queues to flush before continuing.  As written it will continue once there is less than 1 GB in the log send queues.

/* Pause until the log send queue is less than 1 GB */
WHILE ( (SELECT Sum(log_send_queue_size)
         FROM   sys.dm_hadr_database_replica_states
         WHERE  is_local = 0) > 1000000 )
  BEGIN
      WAITFOR delay '00:01:00'
  END

This code needs to be placed immediately after the IF @Delay > 0 block in the procedure.  With the current version of IndexOptimize, it would look like this:

IF @Delay > 0
BEGIN
    SET @CurrentDelay = DATEADD(ss,@Delay,'1900-01-01')
    WAITFOR DELAY @CurrentDelay
END

/* Pause until the log send queue is less than 1 GB */
WHILE ( (SELECT Sum(log_send_queue_size)
         FROM   sys.dm_hadr_database_replica_states
         WHERE  is_local = 0) > 1000000 )
  BEGIN
      WAITFOR delay '00:01:00'
  END


Oracle categorizing alert log error messages

From the Database Reference I see that the view V$DIAG_ALERT_EXT contains a MESSAGE_GROUP column. Maybe this column contains such kind of information.
I am not aware of a view that contains such information. I searched for *us.msg files in the $ORACLE_HOME directories  and subdirectories of a 12.2 installation by executing find $ORACLE_HOME -name '*us.msg' and found the file $ORACLE_HOME/rdbms/mesg/oraus.msg that contains error messages in increasing order of error number and often containing a comment lines that describes the type of the error numbers in the following range. I extracted these comment lines. The output is shown in the following code block. There are more captions in comments that have a format thast will not ber recognized by this grep command.

 grep '^/ [0-9]'  $ORACLE_HOME/rdbms/mesg/oraus.msg
/ 10 - 49 user session and session switching errors
/ 70 .. 90 generic debugger errors
/ 91 .. 95 ksm large_pool_size and large_pool_min_alloc size errors
/ 101 .. 149 shared server errors
/ 150 .. 159 Oracle*XA errors
/ 160 .. 199 Distributed transaction errors (continued)
/ 200 .. 249 control file management errors
/ 250 .. 299 archival & media recovery errors
/ 300..369 - Redo File errors
/ 370-389 kcb external errors
/ 390-399: more Redo log errors
/ 400-420: KCK errors
/ 436-439: Oracle licensing ercs
/ 440-460 Detached process startup errors
/ 469-525 Detached process death errors (signaled in foreground)
/ 00526-00565
/ 00570-00599 SQL*Connect Opening & Reading Files
/ 00710 - 00719 reserved for tablespace rename feature
/ 1070 - 1099: V6 program interface errors
/ 1100 to 1279: db file errors (continue at 63000)
/ 1384 - 1396 Reserved for 4K block size log file errors
/ 1490 - 1499 Analyze Parse and Execution Errors
/ 1500 - 1699 V6 Execution errors
/ 2040 .. 2099 - Reserved for distributed update
/ 2100 .. 2139 - Reserved for precompiler runtime (SQLLIB)
/ 2140 .. 2499 - V6 parser errors continued
/ 6571-6579 Reserved for Plsql functions called from Sql
/ 6581-6591 Reserved for European OEM Ports (contact: egroenen.nl)
/ 6600-6699 Reserved for more SQL*Net errors
/ 6700-6899 Reserved for TLI SQL*NET driver errors
/ 6900-6999 Reserved for more SQL*Net errors
/ 06609 is not used by the LU6.2 Driver.
/ 6700-6899 Reserved for TLI SQL*NET driver errors
/ 6900-6999 Reserved for more SQL*Net errors
/ 7000-7099 Reserved for SQL*CONNECT/DB2 errors
/ 7100-7199 Reserved for SQL*CONNECT/IMS errors
/ 7200 - 7499 Reserved for UNIX OSD errors
/ 7500-7999 VMS OSD ERRORS
/ 7700 - 7720: sksarch errors
/ 7721-7729 scg overflow errors
/ 7740 - 7760 error message file errors
/ 7820-7839  SSP error messages
/ 7840-7859 SLLF error messages
/ 7880 - 7889 Error message for sd
/ 8000 - 8174  Data layer error messages
/ 8175 - 8190 Discrete transaction error messages
/ 8200 - 8399 Reserved for NCUBE OSD errors
/ 8270 - 8273 Reserved nCUBE archival
/ 8273 - reserved nCUBE archival
/ 8320 - 8329 Reserved for scn.c
/ 8340-8359 Reserved for nCUBE redo server errors
/ 8362 - 8399 Reserved for nCUBE sosd errors (unassigned)
/ 8400 UTL_RAW and UTL_PG error and warning messages
/ 8401 - 8499 Reserved for APPC Gateway
/ 8500 - 8599 Reserved for Protocol Procedural Gateway for
/ 8600 - 8699 Reserved for ??? sql*connnect errors
/ 8700 - 8799 Reserved for ??? sql*connnect errors
/ 8800 - 8999 Reserved for Tandem sql*connnect errors
/ 9000 - 9099 Reserved for TERADATA sql*connnect errors
/ 9100 - 9199 Reserved for file-based sql*connnect errors
/ 9200 - 9499 Reserved for OS/2 and MSDOS OSD errors
/ 9500 - 9699 Reserved for MPE/XL OSD errors
/ 9700 - 9999 Reserved for UNIX OSD errors
/ 09720-09739 reserved for Philips MPX P9x00
/ 09786-09825 reserved for Loader V2.0
/ 10630-10639 Reserved for Online Segment Shrink
/ 09858-09859 Reserved For NeXT port
/ 09860-09869 Reserved For Oracle Europe
/ 10302 - 10318 Reserved for internal triggers
/ 10325 - 10335 reserved for recovery testing
/ 10530 -10540 reserved for lightweight threads
/ 10552 -10589 reserved for recoverable media/standby recovery
/ 10710 - 10729 are Reserved for BITMAP row sources
/ 10730 - 10800 general debugging events
/ 10780 - 10785  Reserved for LogMiner
/ 10786 - 10788  Reserved for ks*
/ 10789 - 10790 Reserved for LogMiner
/ 10791 - 10799  Reserved for Logical Standby
/ 10810 - 10825 Reserved for OLTP
/ 10826 - 10829 Reserved for upgrade/downgrade
/ 10830 - 10838 Reserved for sort and aggregation row sources
/ 10841 - 10849 Reserved for OCI
/ 10850 - 10869 Reserved for AQ
/ 10870 - 10879  Reserved for recovery layer
/ 10880 - 10882  Reserved for Java Virtual Machine
/ 10900 - 10924  Reserved for SPACE management
/ 10901 - used in 7.3.4 for disabling extent trimming for merge
/ 10970 - 10974 Reserved for Lob Access
/ 10975 - 10995 Reserved for Replication
/ 10996 - 10998 Reserved for ks*
/ 10999 End Pseudo-error debugging events
/ 11000 - 11999 Reserved for mvs sql*net errors
/ 12000 - 12099 Reserved for table materialized view errors
/ 12100 - 12299 Reserved for Net8 errors
/ 12150 - 12195   Reserved for OSN (Not to be used by TNS)
/ 12196 - 12285   NR errors reportable by OSN (returned by nricall())
/ 12286 - 12499   ---(Reserved)---
/ 12500 - 12699   See net12500.msg
/ 12196 - 12285 NR errors reportable to OSN.
/ 12300 - 12399 Reserved for Multiple Mounts
/ 12150 - 12285   See net12150.msg
/ 12286 - 12499   ---(Reserved)---
/ 12500 - 12530   NSG errors reportable by OSN (returned in cndo).
/ 12531 - 12629   NS (and NT errors reported as NS errors) reportable by OSN
/ 12630 - 12699   NA errors (returned by native services)
/ 12500 - 12530 Reserved for NSG errors reportable to OSN.
/ 12531 - 12629 ns errors reportable to osn:
/ 12606 - 12629 ns secondary errors
/ 12630 - 12699 Reserved for NA errors.
/ 12700 - 12999: Reserved.
/ 12700 - 12750 Reserved for NLS
/ 12751 - 12799 reserved for VOS
/ 12800 - 12893 Reserved for Parallel Query/Index Creation
/ 12857 - 12893 unused (parallel query/index creation messages)
/ 12874 - 12893 unused currently for PQ
/ 12894 - 12898 are reserved for asmcmd setsparseparent error messages.
/ 12899 - Stole this one for new "value too large" message
/ 12920 - 12939 reserved for database/tablespace force logging mode messages
/ 12930 - 12939 Reserved for 12c Nologging Standby Project.
/ 12940 - 12949 Reserved for server side DBVerify
/ 12950 - 12960 Reserved for Space Management
/ 12980 - 12999 Reserved for Drop Column
/ 13000 - 13499   (Reserved for Oracle Spatial)
/ 13000 - 13199   Spatial Data Option and Spatial Cartridge 8.0
/ 13200 - 13499   Spatial Cartridge 8.1.3
/ 13391 - 13499 for all GeoRaster errors
/ 13500 - 13999 reserved for Server Manageability Layer
/ 13500 - 13599 reserved for AWR
/ 13600 - 13699 reserved for the adv fmwk.
/ 13700 - 13749 reserved for the ADDM, ASH
/ 13800 - 13809 reserved for the SQL Tuning Base
/ 13810 - 13824 reserved for DBOP Monitor
/ 13825 - 13849 reserved for SQL profiles
/ 13850 - 13899 reserved for end-to-end tracing
/ 13900 - 13949 reserved for threshold-based alert
/ 13950 - 13969 reserved for MMON infrastructure
/ 13970-13998 reserved for common reporting framework
/ 14000 - 14999 reserved for Partitioned object project
/ 14000 - 14099 reserved for parsing new syntax introduced by the Partitioned
/ 14150-14249 reserved for parsing errors which may be generated
/ 14201-14239 reserved for errors which may be generated during
/ 14250-14349 reserved for errors which may be generated during
/ 14400 - 14499 reserved for execution errors introduced by the Partitioned
/ 14450 - 14499 reserved for execution errors introduced by the
/ 14601 - 14700 reserved for 9.0.2 partitioning project
/ 14650 - 14689 reserved for reference-partitioning project
/ 14690 - 14699 reserved for 32k varchar project
/ 14750 - 14799 reserved for interval-partitioning project
/ 14800 - 14849 Reserved for composite list/* partitioning
/ 14850 - 14869 Reserved for autolist partitioning
/ 14870 - 14889 Reserved for interval subpartitioning
/ 14900 - 14949 Reserved for KJAC error messages
/ 14950 - 14999 Reserved for KJAC PL/SQL error messages
/ 15000 - 15499 Reserved for Automatic Storage Management
/ 59000 - 59999 Reserved for Automatic Storage Management (e57000.msg)
/ 15000 - 15099 Used for ASM run-time errors
/ 15100 - 15179 Used for ASM SQL usage errors
/ 15180 - 15188 Used for ASMLIB related error messages
/ 15189 - 15199 Used for ASM tracing and testing errors
/ 15200 - 15206 ASM messages added in 10.2
/ 15210 - 15227 ASM SQL parsing messages added in 11.1
/ 15228 - 15229 Used for ASM testing errors
/ 15230 - 15250 ASM runtime error messages added in 11.1
/ 15252 - 15280 Reserved for TrailBlazer
/ 15380 - 15399 ASM File Group error messages
/ 15400 - 15439 ASM appliance error messages
/ 15430 - 15439 ASM Flex Disk Group Quota Group Messages
/ 15440 - 15499 ASM Volume Messages
/ 59000 - 59999 Reserved for Automatic Storage Management (e57000.msg)
/ 15500 - 15999 Reserved for Server Manageability Layer
/ 15500 - 15599 reserved for Workload Capture and Replay (KEC)
/ 15600 - 15649 reserved for Automated Maintenance Tasks (KET)
/ 15650 - 15699 reserved for Direct Access to Fixed Tables
/ 16000 - 16499 Reserved for ORACLE HOT STANDBY
/ 16100 - 16130 Reserved for Logical Standby
/ 16200 - 16374 Reserved for Logical Standby
/ 16375 - 16499 Reserved for Physical Standby
/ 16500 - 16699 Reserved for Data Guard Broker
/ 16700 - 16899 reserved for Data Guard Broker RSM errors
/ 16900 - 16949 Reserved for Data Guard Broker
/ 17000 - 17499 Reserved for JDBC
/ 17500 - 18000 Reserved for I/O subsystem
/ 17675-18000 Reserved for OFS
/ 18000 - 18099 Reserved for PLAN STABILITY
/ 19391 - 19399 are reserved for autonfs.
/ 19400 - 19499 are reserved for KOT.
/ 19500 - 19998 Reserved for Server Managed Recovery
/ 19830 - 19835: reserved for SQL Engine Errors:
/ 19926 - 19929: reserved for transport database errors
/ 21100 - 21299 Reserved for CORE
/ 21300 - 22799 Reserved for OI/KO/KG (object Interface and Subsystem)
/ 22800 - 22999 Reserved for object SQL:
/ 23000 - 23299 Reserved for object Management Subsystem (OMS)
/ 23300 - 24299 Reserved for errors from DBMS* pl/sql packages
/ 20000 - 20999 Reserved for callers of the
/ 21100 - 21299 Reserved for CORE
/ 21300 - 22799 Reserved for OI/KO/KG (object Interface and Subsystem)
/ 22275 to 22279 reserved for KOLL - LOBs
/ 22280 to 22299 reserved for LOBs - LBS(KOLB), DBMS_LOB(KKXL) and BFILE(KOLF)
/ 22799 IS THE MAXIMUM ERROR NUMBER for ORI/KO (object Interface and Subsystem)
/ 22800 - 22999 Reserved for object SQL:
/ 22999 IS THE MAXIMUM ERROR NUMBER for object SQL
/ 23000 - 23299 Reserved for object Management Subsystem (OMS)
/ 23300 - 24299 Reserved for errors from DBMS* pl/sql pakages
/ 24280 -24290 Reserved for DBMS_LOB (FILE) package
/ 24291 - 24299 Reserved for Sorted Hash Clusters
/ 24300 - 24999 Reserved for OCI/UPI/TTC/OPI/RPI/PSDs etc
/ 24500 - 24549 Reserved for OCI codepoint length semantics and UTF16
/ 24550 - 24574 Reserved for OCI et al Diagnosability
/ 24750 - 24800 Reserved for Transaction OCI
/ 24800 - 24849 Reserved for OCI Lob/File related Calls
/ 24850 - 24874 Reserved for Shared OCI
/ 24900 - 24939 Reserved for OCI Notification
/ 24940 - 24949 Reserved for Generic Notification
/ 24950 - 24959 Reserved for PL/SQL Notification
/ 25474 is reserved for dumping info
/ 25475 is reserved for debug mode
/ 26500 - 26649 Reserved for Replication (KN)
/ 26650 - 26999 Reserved for Log Based Replication Replication (KN)
/ 27000-27299 Reserved for Oracle8 Unix OSDs
/ 27000 - 27030 keep for SKGFQ (Also 27190-27229)
/ 27031 - 27099 keep for SKGFR (if more needed, allocate another range later)
/ 27100 - 27139 keep for SKGM
/ 27140-27189 RESERVED FOR SKGP
/ 27190 - 27229 reserved skgfq
/ 27231 - 27249 reserved for sskgof
/ 27250 - 27269 reserved for sm
/ 27270 - 27274 reserved for sz
/ 27300 - 27350 reserved for slos Error Reporting
/ 27351 - 27499 RESERVED FOR NEW DATABASE SCHEDULER
/ 27401 - 27410 Reserved for scheduler events
/ 27411 - 27420 Reserved for scheduler repeat interval errors
/ 27431 - 27500 Scheduler API errors continuation
/ 27500-27599 RESERVED FOR IPC ERRORS
/ 27600-27699 RESERVED FOR EXADATA (SAGE) and HOST/CELL ERRORS
/ 27700-27800 RESERVED FOR MGA
/ 27800-27900 RESERVED FOR EVENT NOTIFICATION (KSEVN)
/ 28000-28499 RESERVED FOR SECURITY RELATED ERRORS
/ 28060-28099 RESERVED FOR RADM (Real-time Application-controlled Data Masking)
/ 28065 is reserved for RADM internal error.
/ 28100-28149 RESERVED FOR FINE-GRAINED SECURITY
/ 28150-28199 RESERVED FOR N-TIER SECURITY
/ 28200-28230 RESERVED FOR THE APPLICATION ROLE
/ 28221-28230 RESERVED FOR PASSWORD/ACCOUNT ENHANCEMENT
/ 28230-28260 RESERVED FOR THE OBFUSCATION TOOLKIT
/ 28261-28270 RESERVED FOR APPLICATION CONTEXT
/ 28270-28290 RESERVED FOR PASSWORD AUTHENTICATED GLOBAL USER(KZAL)
/ 28290 - 28300 RESERVED FOR THE Kerberos Enterprise User Security
/ 28300 - 28329 RESERVED FOR THE ENTERPRISE USER SECURITY
/ 28330-28499 RESERVED FOR TRANSPARENT DATA ENCRYPTION
/ 28500-28749 RESERVED FOR THE HETEROGENEOUS SERVICES
/ 28575 - 28600 reserved for untrusted and distributed external procedures
/ 28700-28799 RESERVED FOR CODE-BASED ACCESS CONTROL
/ 29250-29299 RESERVED FOR MORE PL/SQL AND UTILITY PACKAGE ERRORS
/ 29300 - 29399 Reserved for errors from DBMS* pl/sql pakages.
/ 29300 - 29354 RESERVED FOR THE DBMS_SQL PACKAGE
/ 29355 - 29399 RESERVED FOR DBMS RESOURCE MANAGER PACKAGES
/ 29400 - 29469 Reserved for Oracle8 Data Cartridges
/ 29470 - 29489 RESERVED FOR THE DBMS_SQL PACKAGE
/ 29490 - 29499 RESERVED FOR THE DBMS_PARALLEL_EXECUTE PACKAGE
/ 29500 - 29699 Reserved for DDL and other integration for
/ 29700-29799 reserved for Global Enqueue Service
/ 29780 -29789 Reserved for Gpnp Generic modules
/ 29800 - 29999 Reserved for Extensible Indexing project
/ 30000 - 30010 Reserved 8.1 miscellaneous features
/ 30051 - 30059 Reserved for row versions query feature
/ 30060 - 30069 Reserved for Transaction layer internal tracing
/ 30070 - 30099 Reserved for datetime/interval feature
/ 30100-30134 Reserved for OCIExtract Cartridge Services
/ 30300 - 30330 reserved for transaction recovery
/ 30665 - 30675 are reserved for 12g SQL enhancements projects
/ 32791 - 32799 are reserved for 12g SQL enhancements projects
/ 32800 - 32999 are reserved for Messaging Gateway (MGW)
/ 65535 -  (MAX ERROR NUMBER; NONE CAN BE LARGER)
/ 32800 - 32999 reserved for Oracle Messaging Gateway (MGW)
/ 40001 - 40020 RESERVED FOR BLAST
/ 40021 - 40040 RESERVED FOR GLM
/ 40101 - 40180 Generic Errors Across All Data Mining Algorithms
/ 40181 - 40200 RESERVED FOR DBMS_DATA_MINING_TRANSFORM
/ 40201 - 40250 RESERVED FOR DBMS_DATA_MINING
/ 40251 - 40260 RESERVED FOR SVM
/ 40261 - 40270 RESERVED FOR NMF
/ 40271 - 40280 RESERVED FOR ABN
/ 40281 - 40300 RESERVED FOR DMF
/ 40301 - 40320 RESERVED FOR DECISION TREE CLASSIFICATION AND REGRESSION
/ 40321 - 40340 RESERVED FOR CLUSTERING MODELS
/ 40341 - 40360 RESERVED FOR MODEL MAINTENANCE UPGRADE/DOWNGRADE MESSAGES
/ 40361 - 40380 RESERVED FOR SECURITY (PRIVILEGE/AUDIT) MESSAGES
/ 40381 - 40500 RESERVED FOR PMML IMPORT
/ 40401 - 40420 RESERVED FOR SVD
/ 40421 - 40440 RESERVED FOR UNSTRUCTURED TEXT
/ 40441 - 40500 RESERVED FOR JSON
/ 40501 - 40550 Reserved for Recovery
/ 40551 - 40600 Reserved for JSON
/ 40581-40619 JSON data guide related
/ 40620-40700 JSON related
/ 40651 - 40700 JSON related
/ 41095 is reserved for tuning
/ 41096 is reserved for tuning component state handling
/ 41097 is reserved for tuning migratability checks
/ 41098 is reserved for tracing
/ 41099 is reserved for debug mode
/ 48000 - 48099 are reserved for diagnosability framework errors
/ 48100 - 48179 are reserved for ADS portable layer
/ 48180 - 48199 are reserved for ADS OSD layer
/ 48200 - 48299 are reserved for AMS layer
/ 48300 - 48389 are reserved for AMI layer
/ 48390 - 48389 are reserved for ADM layer
/ 48400 - 48499 are reserved for ADV layer
/ 48500- 48599 are reserved for AME layer
/ 48600 - 48699 for AMH layer
/ 48800 - 48899 are reserved for ADV layer
/ 48900- 48999 are reserved for UTS layer
/ 51000 - 51999 are reserved for HM, IR diagnosability framework errors
/ 51000 - 51099 for HM infrastructure related
/ 51100 - 51189 for IR infrastructure related
/ 51190 - 51199 for DBMS_IR package errors
/ 51200 - 51399 for DATA corruption checks (physical, logical)
/ 51400 - 51499 for ASM checks
/ 51500 - 51699 for Check Name, Descriptions and Parameter Descriptions
/ 51700 - 51749 for Report Framework (dbgx)
/ 51257 is available to be used
/ 51700 - 51749 for Report Framework (dbgx)
/ 53000 - 53199 : Reserved for repository errors
/ 53200 - 53209 General Errors
/ 53210 - 53229 Source Checking Errors
/ 53230 - 53249 Destination Checking Errors
/ 53250 - 53269 Other Parameter Checking Errors
/ 57000 - 58999 are reserved for TimesTen IMDB errors
/ 15000 - 15499 Reserved for Automatic Storage Management [continued from]
/ 59000 - 59999 Reserved for Automatic Storage Management
/ 59000 - 59299 ASM run-time error messages
/ 59300 - 59399 ASM SQL usage error messages
/ 59400 - 59499 ASM tracing and testing error messages
/ 59500 - 59599 Flex ASM and Member Cluster error messages
/ 59600 - 59699 ASM Flex Disk Group (File Group, Quota Group) error messages
/ 59700 - 59749 ASM Extended Disk Group error messages
/ 59750 - 59799 ASM appliance (ODA, EXADATA) error messages
/ 59800 - 59819 ASMLIB related error messages
/ 59820 - 59849 ASM Volume Messages
/ 60000 - 60999 are reserved for SPACE MANAGEMENT
/ 61001 - 62000 are reserved for ADE Virtual Folder (RNFS)
/ 62001 - 62005 are reserved for parameter properties
/ 62006 - 62009 are reserved for XML and object replication
/ 62010 - 62029 are reserved for DB Unicode Migration Assistant Kernel-Side
/ 62031 - 62080 are reserved for Vector Processing (IMA)
/ 62081 - 62130 are reserved for Vector Encoding (DSBs)
/ 62500 - 62549 are reserved for PATTERN MATCHING
/ 62550 - 62554 are reserved for the SQL Row Limiting clause
/ 62555 - 62599 are reserved for Polymorphic Table Function
/ 63000 - 63999 are reserved for DATABASE FILE ERRORS (cont. from 1100-1279)
/ 64201 - 64299 are reserved for LOB errors
/ 64300 - 64349 are reserved for ARCHIVE COMPRESSION
/ 64350 - 64399 are reserved for In-Memory Columnar (IMC)
/ 64400 - 64599 are reserved for XDB errors
/ 64610 - 64619 are reserved for PL/SQL package UTL_CALL_STACK
/ 65000 - 65399 are reserved for CONSOLIDATED DATABASES ERRORS
/ 65400 - 64450 are reserved for CLUSTERING clause
/ 65455 - 65466 reserved for family errors
/ 65470- 65499 reserved for LOST WRITE PROTECTION
/ 65501- 65529 reserved for LOB layer errors
/ 65530 - 65534 reserved for INDEX layer errors


How to remove a database server from Utility Explorer

I've recently had to drop a defunct instance from a UCP.
Here's how:

DECLARE @instance_id int;

SELECT @instance_id = mi.instance_id
FROM msdb.dbo.sysutility_ucp_managed_instances AS mi
WHERE mi.instance_name = 'defunctservrname\instancename';

EXEC msdb.dbo.sp_sysutility_ucp_remove_mi @instance_id;


Does PostgreSQL handle case-insensitive database as good as others?

When I have a, A, b and the non-English char, the result is a, A, b. And when I have a, A, B and the non-English char, the result is A, a,
B.

It's an effect of the deterministic collation property being false. There is no tie-breaker between equivalent strings, so a may come out before A or the opposite. Also sorts in database are generally not stable, because in relational theory, rows have no inherent order so sticking to the "original order" makes no sense.
If you don't like that, you may add a second order with a deterministic collation, for instance
ORDER BY field COLLATE "non-deterministic-collation", field COLLATE "C"

I use Full-Text indices for the search in the parts that users can input English words. I don't know how my collation will affect
Full-Text.

It doesn't. The full text search parser is mostly influenced by the lc_ctype property of the database. There is even a specific error associated to forcing a collation when extracting a tsvector from a string:
=> select to_tsvector('abc') collate "C";
ERROR:  collations are not supported by type tsvector

I need Persian collation (Not sure if I do, the collation in the docs handles the sorting fine but I did not test storing it - in CentOs).

When a database is created, it does inherit a collation from the template database, or get it from the lc_collate or locale clause if specified explicitly. That's the default collation for all sorts and string comparisons in this database.
If you use Linux in an UTF-8 encoding with fa_IR locale, the lc_collate of your database is probably fa_IR.UTF-8, so it does use a Persian collation implicitly. There is no clear indication in your question that your application really needs to create custom collations beyond that.
The details for creating collation can be fine here and here and to create Persian collation the following can be used.
CREATE COLLATION the_name (provider = icu, locale = 'fa-IR@colStrength=secondary', deterministic = false);


Deciding which indexes you should use and or remove is a very involved process and is not something that can be simply automated.  There are plenty of ways to identify under used or missing indexes, but adding or removing indexes can have a wide variety of impacts and these impacts need to be reviewed.

In order to properly understand how indexes can affect query performance, I strongly recommend you read Grant Fritchey's SQL Server 2012 Query Performance Tuning book.  This is a valuable reference book for many DBAs and, while it is written for SQL 2012, much of the information is applicable to other versions of SQL Server.

Listed below are some tools you can use to identify indexes you can either add or remove.  Do not apply these recommendations without reviewing them first.

Microsoft provides the Database Engine Tuning Adviser to review your database and provide recommendations on actions that can improve your performance.  There are many factors the Tuning Adviser can not evaluate, so make sure you consider the suggestions individually before applying them.

You can also review missing index information from two places within SQL Server.  The first is within the query plans.  If you view a query plan, the green text at the top will typically recommend an index that can help your query.  This data is typically found in the second place, sys.dm_db_missing_index_details.  SQL Server tracks index usage stats and compiles recommendations that can be reviewed for possible addition to improve your performance.  The Tuning Adviser referenced above also makes use of this information.

Glenn Berry has a very helpful query as part of his DMV script that shows you indexes that aren't very helpful:

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name]
  , i.name AS [Index Name]
  , i.index_id
  , user_updates AS [Total Writes]
  , user_seeks + user_scans + user_lookups AS [Total Reads]
  , user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
  ON s.[object_id] = i.[object_id]
  AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
  AND s.database_id = DB_ID()
  AND user_updates > (user_seeks + user_scans + user_lookups)
  AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

Please note!

Query tuning and index management is a very deep and involved topic.  It's not something you can simply turn on with a switch and allow an automated process to handle because of all the factors involved.  Many companies have DBAs dedicated solely to the task of managing this, using the tools listed above.  If you have no DBAs at your place of employment, I recommend either taking the time to train yourself in this OR seek a professional who has experience with this to aid you.


Is there any database modeling software which provides a real-time (and editable) ERD, which I can manipulate alongside a traditional GUI?

The two worlds differ because of "state". A web page has none; a database is nothing but.

A web page is rendered and it is over with. Yes, a user can click here and there, type values and widgets may appear and go again, but nothing permanent has changed as far as the enterprise is concerned. Should the rendering computer fail no inconsistency can be introduced. The page can be completely re-created as it was with a single call to the server. A change to, say, some CSS does not have to account for every other piece of styling that has been applied to this page throughout history.

A database, however, must maintain and protect state. If the DB server fails it must (should!) guarantee the integrity of the data it has acknowleded as successfully written. Rebuilding the current data from all past input would be time-consuming, assuming the previous files (key strokes?) exist and can be processed in the identical sequence. A change to a table's schema must take account of existing data's structure and meaning. Declared referential integrity (DRI) constraints, which further guarantee's data quality, must be honoured.

NoSQL tries to side step this issue by allowing dynamic schema i.e. no to rows need have the same columns, data types or meaning. This allows very rapid evolution of the applicaiton without hinderance from database upgrade tasks. The responsibility for ensuring the completeness, correctness and consistency of the data is now in the hands of the application developer with little or no help from the persistence tool. Being able to Deal with all the existing "state" in the previous format(s) is now her responsibility, too.

The difference between logical and physical models is a valid one, too. In my experience, however, they tend to be very similar. The mapping from logical to physical must be explicit within the modelling tools (if it is to generate DDL) so could be made transpartent to code written against the logical model. I don't think this is a great impediment to the world you hope for.

I have used CASE tools where the logical data model can be entered into the tool, procedural logic written against that model immediately, and a functioning system generated at the touch of a button. It was a great tool to work with. It was, however, expensive and seems to have declined.

There are tools which will take a "desired state" model, compare it to an "existing state" database and generate the delta DDL. This can be integrated into a continuous build & deploy workflow. Having seen a generated change script which was so awful it would take in excess of three days to complete, however, I'd recommend reviewing manually before submitting.


Analysis of MySQL database in real time Amazon RDS similar to phpMyAdmin

This depends on your particular setup, but you just modify your phpmyadmin config file to connect to the RDS database, and use phpmyadmin as usual.

Go to the phpmyadmin's root folder (usually /var/phpmyadmin) and copy config.sample.inc.php to config.inc.php .

Inside the file set up yout RDS connection:

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = '<RDS host>';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysql if your server does not have mysqli */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Remember that the server where you are running phpmyadmin must have access to the RDS.


How to get different metrics for same table with one query?

Another way of writing the query (without using the  vw_EzProviderUser provider table because it is unused in the example  would be:

;WITH CTE AS
(
SELECT COUNT(DWB.CaseId) as CountingCaseId, DWB.CaseId,DWB.statusId
FROM
dbo.DashboardWorkbench DWB
GROUP BY DWB.CaseId,DWB.statusId
),
CTE2 AS
(
SELECT  COUNT(DISTINCT DWB.CaseId) as CountingCaseIdDistinct,
        DWB.statusId
FROM dbo.DashboardWorkbench DWB
LEFT JOIN dbo.DashboardCaseStatusHistory DCSH on DWB.CaseId = DCSH.CaseId
GROUP BY DCSH.AssignedByUser,DWB.statusId
)
SELECT  SUM(CASE WHEN CTE.statusId = 9 THEN CountingCaseId ELSE 0 END) as metric1,
        SUM(CASE WHEN CTE.statusId IN (10, 1088) THEN CountingCaseId ELSE 0 END) as metric2,
        SUM(CASE WHEN CTE.statusId = 11 THEN CountingCaseId ELSE 0 END) as metric3,
        SUM(CASE WHEN CTE.statusId = 20 THEN CountingCaseId ELSE 0 END) as metric4,
        (SELECT MAX(CountingCaseIdDistinct) FROM CTE2
        WHERE CTE2.statusId = 12) as metric5,
        (SELECT MAX(CountingCaseIdDistinct) FROM CTE2
        WHERE CTE2.statusId = 10) as metric6
FROM CTE;

Performance

As of performance this could help depending on your data due to the early grouping on DWB.CaseId,DWB.statusId.

YMMV

There should be better solutions as to make it more performant such as columnstore indexes / better rewrites / indexing / ....

Temp tables

I would advise storing the result of CTE2 (or your subquery) in a temporary table if you are calling it multiple times.

This way, you are only reading from the resultset, not evaluating the same query each time.

(Metric5 and Metric6 in the example).

Testing

This was tested on SQL Server 2017

When adding some indexes to cover all options:

CREATE INDEX IX_statusId_CaseId
ON dbo.DashboardWorkbench(statusId,CaseId);
CREATE INDEX IX_CaseId_statusId
ON dbo.DashboardWorkbench(CaseId,statusId);

CREATE INDEX IX_AssignedByUser_CaseId
ON dbo.DashboardCaseStatusHistory(AssignedByUser,CaseId);
CREATE INDEX IX_CaseId_AssignedByUser
ON dbo.DashboardCaseStatusHistory(CaseId,AssignedByUser);

The only real 'benefit' compared to your plan is not having the compute scalar operator
or the multiple SUM(CASE WHEN StatusId = 9 THEN 1 ELSE 0), statusid = 10, ... in your query plan on all data.

In my rewrite, it is only doing that on the grouped count of DWB.CaseId & DWB.statusId.

Part of the plan of your query on my test YMMV

The compute scalar from the part above (second from the right)

Wheras there is earlier grouping in my plan + count(DWB.CaseId)

This part of the plan represents the T-SQL statements in CTE (1)

SELECT COUNT(DWB.CaseId) as CountingCaseId, DWB.CaseId,DWB.statusId
FROM
dbo.DashboardWorkbench DWB
GROUP BY DWB.CaseId,DWB.statusId

the computes are done afterwards, on the result of the above query:

The rest of the plan is virtually the same.

Extra info

There will definetely be better solutions, but somebody might be able to use the DB<>Fiddle below and test for something better.

DB<>Fiddle with test data, execution plans etc.

Temp tables can help if CTE2 or your subquery is evaluated multiple times, indexes matter. And as mentioned in the comments there are other options to explore, but we would need more information.


I checked around.  The bug seems to be in both MyISAM and InnoDB, MySQL 5.5 and 5.6.  It seems to be fixed in 5.7 and 8.0, plus MariaDB 10.

I did not find the bug report in order to identify what, exactly, was the cause, but here are some recommendations:

Don't use DEFAULT '0000-00-00'; "zero dates" are, by default disallowed in later versions.  (There is a switch to allow them.)
Don't use dates looking like 20200518, use instead '2020-05-18'
It may be that 20200518 works, but '20200518' fails (in 5.5 and 5.6).  That is, the quotes caused trouble.
Upgrade the version.
Reconsider the need for an AUTO_INCREMENT since you have a "natural" PRIMARY KEY (jma).


Why does SQL Server require non-clustered index to use unique keys?

Probably it simplifies the engine a lot because you can now address individual rows.

Imagine, you are indexing on a IsMale bit column and there are 1 million "male" values. Now you want to address exactly one of those rows to delete it. The index has 1 million rows that just say true and nothing more. I'm sure it's possible but uniqueness makes the design much more elegant.

Locking requires index rows to be uniquely identifiable.


Oralce: Why Do Triggers on Editioning Views in My Custom Schema Fail?

I think I figured it out. I believe this is by design. I tested this and it works.

INSERT INTO my_table#
  (field2,
   field3)
VALUES
  ('TEST', 1234)

I believe the intention is that source code be modified to either directly reference the # view. Or reference a synonym that points to that view. I know, people are saying "reference an apps synonym" all over the web, but I didn't connect why until just now. Hope this is useful to the rest of you all.


MYSQL - number of rows returned equals number of connections?

A connection survives until one of these:

You explicitly call disconnect(), or
Your program terminates, or
A network glitch causes the disconnect.

Normally, you should code your app to connect once near the beginning and disconnect when finished.  (There are rare use cases for having multiple connections open at once.)

Most APIs have an obscure setting that determines whether you get all the rows (of a SELECT) in one "round trip" to the server, or whether they are fed to you individually (maybe in small chunks??) in many round trips.  There are advantages and disadvantages of either.  99.9% of the time, I am happy getting the entire resultset on one round trip, even if it is 100K rows.


At first

DO NOT detach your database
DO NOT restart your server or services
DO NOT use DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS parameter.

Second, you need to know the cause of suspection. There is the chance that there is no more space on the database disk. In this case you need to free some space and then try to return the database to normal state using sp_resetstatus.

But if database seems to be corrupted, you need to try to fix the database using DBCC CHECKDB with appropriate parameters. But the database needs to be set in EMERGENCY mode first:

ALTER DATABASE [yourdatabase] SET EMERGENCY;

Now, you can execute the DBCC CHECKDB command.
Be careful with result of DBCC execution: if you see the errors on the pages with data, you need to restore your database from backup, because you can lost the integrity of your data; But obviously (in my expirience) DBCC returns the errors on the pages with indexes, and in most cases you can rebuild your indexes.

Finally, you need to fix the cause of the problem. If your problem concerned with disk space, move the data to larger disks. If your database is corrupted because windows is unexpectelly shutted down - move your database to another server, and so on.

I think you need to read this article and the forum discussion below to get some basic information to start the recovery.


If you want a standby copy of your data for HA/DR then is much better to use log shipping or database mirroring. Since these technologies create a physical database copy, you avoid exactly the problem(s) you mention. Also, schema changes 'replicate' much easier.

If you insist on transactional replication, seed your replicas with a backup instead of a snapshot. See How to: Initialize a Transactional Subscription from a Backup for a step-by-step guide.


When you grant permissions to a user using the WITH GRANT OPTION clause, then subsequently revoke that permission, SQL Server will prevent the revoke unless you explicitly add the CASCADE option to the REVOKE ... statement.
This prevents a security risk whereby you grant WITH GRANT OPTION to a user, who subsequently grants rights to others unbeknownst to you.  You then revoke the rights to that user, but the damage has already been done.  This error message is thrown when you attempt to revoke permissions that have been assigned with the grant option, unless you use the CASCADE option:

Msg 4611, Level 16, State 1, Line 44
To revoke or deny grantable privileges, specify the CASCADE option.

In order to revoke permissions that have been granted using the WITH GRANT OPTION clause, you must revoke the permission using the CASCADE option. This also removes the revoked rights from all users that have been granted the rights by the user with the GRANT OPTION.  If you want those other users to retain the rights granted by the user with the GRANT OPTION, you must then manually assign those rights explicitly to those other users.
This code below is a simple repro showing the symptoms, and how to deal with it.
First, create an object we can assign rights to:
IF OBJECT_ID('dbo.TestSelect') IS NULL
CREATE TABLE dbo.TestSelect
(
    t INT
);

Create three users:
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u1')
CREATE USER u1 WITHOUT LOGIN;

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u2')
CREATE USER u2 WITHOUT LOGIN;

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u3')
CREATE USER u3 WITHOUT LOGIN;

Grant SELECT WITH GRANT OPTION to user u1:
GRANT SELECT ON dbo.TestSelect TO u1 WITH GRANT OPTION;

Grant SELECT WITH GRANT OPTION to user u2 as user 'u1' :
EXECUTE AS USER = 'u1';

GRANT SELECT ON dbo.TestSelect TO u2 WITH GRANT OPTION;

REVERT

Grant SELECT WITH GRANT OPTION to user u3 as user 'u2' :
EXECUTE AS USER = 'u2';

GRANT SELECT ON dbo.TestSelect TO u3 WITH GRANT OPTION;

REVERT

Ensure we are running as the initial user (in my case dbo)
SELECT USER_NAME();

Attempt to remove the SELECT rights from user u1 without using the CASCADE option, which will fail:
REVOKE SELECT ON dbo.TestSelect FROM u1;
--Msg 4611, Level 16, State 1, Line 44
--To revoke or deny grantable privileges, specify the CASCADE option.

Add the CASCADE option, which works:
REVOKE SELECT ON dbo.TestSelect FROM u1 CASCADE;

See if the other users have access to the test table (they don't since we've revoked access using the CASCADE option):
EXECUTE AS USER = 'u2';

SELECT *
FROM dbo.TestSelect;
--Msg 229, Level 14, State 5, Line 51
--The SELECT permission was denied on the object 'TestSelect', database 'Test', schema 'dbo'.

Revert back to the 'dbo' user, so we can grant SELECT on the test object to the remaining users:
REVERT -- back to 'dbo'

GRANT SELECT ON dbo.TestSelect TO U2;
GRANT SELECT ON dbo.TestSelect TO U3;

Confirm the other users have access to the test object:
EXECUTE AS USER = 'u2';

SELECT *
FROM dbo.TestSelect;

REVERT -- back to 'dbo'

EXECUTE AS USER = 'u3';

SELECT *
FROM dbo.TestSelect;

REVERT -- back to 'dbo'

Clean up the test object and users:
DROP TABLE dbo.TestSelect
DROP USER u3;
DROP USER u2;
DROP USER u1;


SQL Server: insert french characters without using them in a query

in the most portable way possible, using only ASCII in my query

Requiring ASCII-only will make it harder to achieve this goal in a truly reliable manner. If you only have one database, or if you have multiple databases that all have the same default Collation, then using CHAR(asciiValue) as mentioned in @Nelson's answer would work. Just keep in mind that the character you get from using CHAR(someValue) depends on the default Collation of the "current" or "active" database that the query is running in. The Collation determines the code page, and that can be different per each DB. The following simulates what would happen if the same ASCII value were used in different DBs that had different default Collations that used different code pages:

DECLARE @Test TABLE
(
  [Latin1] VARCHAR(10) COLLATE Latin1_General_100_CI_AS_SC,
  [Hebrew] VARCHAR(10) COLLATE Hebrew_100_CI_AS_SC,
  [Greek] VARCHAR(10) COLLATE Greek_100_CI_AS_SC
);

SELECT CONVERT(BINARY(1), 233);

INSERT INTO @Test ([Latin1], [Hebrew], [Greek]) VALUES (0xE9, 0xE9, 0xE9);

SELECT * FROM @Test;
-- é      י       ι

  when I send queries to a counterpart to perform database changes, the text file is opened and edited on different machines, and the special characters are damaged.

ASCII, is inherently difficult to make portable. ASCII text files have no ability to indicate what encoding was used to create them. The problem you are seeing is that the file is being written with one encoding / code page and then being read with another. If you open the file on your computer, does it look exactly as it should? Or has it already been altered? Because é exists in many code pages, I suspect that it still looks fine on your computer, which means it is being read with the wrong encoding.

One way to avoid this is to ensure that the person opening the file opens it with the correct encoding. You can use the File Open dialog in SSMS to select a file, but don't click the Open button. Instead, click the down arrow on the right side of the Open button and select Open With.... Next, select SQL Query Editor with Encoding and click OK. A new dialog will pop up and will be a drop-down of encodings. The drop-down will default to (Auto-Detect) but that won't work here. Just select the encoding that you are using when saving the file and then click OK.

An easier and more reliable way is to use a Unicode encoding (UTF-8 or UTF-16) with a Byte Order Mark. The Byte Order Mark (BOM) is 2 to 4 bytes at the very beginning of a file that indicates the encoding used to create the file. The BOM is used to auto-detect the encoding, which means that you don't need to tell the person you are sending the file to what encoding to use. And, it doesn't require that you use Unicode data in the script; you current INSERT statement with the VARCHAR data should be unaffected.

You can do this by going to File | Save As... Then, just like with the Open dialog, click on the down arrow on the Save button and select Save with Encoding. Then select either Unicode (UTF-8 with signature) - Codepage 65001 or Unicode - Codepage 1200 and then click OK.


SQL Server Stolen Server Memory - what it consists of?

What "Stolen Server Memory" consists of ?

I will quote from my answer given on This Thread.

  Stolen memory describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.

Plan cache is not part of stolen memory.

  How can I query rest of things that are included into "Stolen Server Memory" ?

AFAIK their is no query to tell what "actually" stolen memory consists and how much. You can run DBCC MEMORYSTATUS() command to see the value of stolen memory.


Name for table with metric_name and metric_value columns

Unpivot Table

Being the product of a transformation, your desired form here is called an "Unpivot table". The form you're starting from here is either normalized data, or the result of a transformation itself called a "pivot."

You can pivot and unpivot without losing data. Pivoting and operations like this emerge from OLAP functionality, where they're rotations. In PostgreSQL we pivot with the tablefunc module. We can easily unpivot with a CROSS JOIN LATERAL on a VALUES list,

SELECT id, cat, value
FROM ( VALUES (1,1,2,3), (2,10,20,30) ) AS t1(id,x,y,z)
CROSS JOIN LATERAL ( VALUES ('X',x), ('Y',y), ('Z',z) )
  AS t2(cat, value);

Or, though slower, we use the unnest(ARRAY[]) idiom and parallel unnest

SELECT id, cat, value
FROM ( VALUES (1,1,2,3), (2,10,20,30) ) AS t1(id,x,y,z)
CROSS JOIN LATERAL unnest(ARRAY['X','Y','Z'], ARRAY[x,y,z])
  AS t2(cat, value);

Either way you'll produce,

 id | cat | value
----+-----+-------
  1 | X   |     1
  1 | Y   |     2
  1 | Z   |     3
  2 | X   |    10
  2 | Y   |    20
  2 | Z   |    30
(6 rows)

See also,

Unpivoting data in PostgreSQL (older method than used above)
Microsoft Technet docs
Oracle Pivot and Unpivot article


We are doing something very similar at my place and this very question was in my head for a while a few weeks back.

After noticing that logs were consuming 98% of our DBs in SQL Server I did some research on how we should handle this problem.

It all depends on the structure of your logs but our logs are not really relational so storing them in a RDBMS and consuming all the resources built for the RDBMS does not make sense.

I started looking into NoSQL stores and found DB Engines Ranking to be very useful to compare the feature sets of each DB engine.

Personally I found that an eventual consistency solution worked for me as it has good sharding, availability, and fast reads on huge data sets.

Check the CAP Theorem wiki entry to learn about the trade offs on different types of NoSQL solution.

We are currently testing a Cassandra deployment for our logs with MapReduce jobs.


PostgreSQL splitting one table into three and setting up references between them

Let's assume this is your tables (I've changed naming for clarity and ease of use):

CREATE TABLE runner
(
    runner_id serial PRIMARY KEY,
    runner_name varchar UNIQUE
);

CREATE TABLE race
(
    race_id serial PRIMARY KEY,
    race_name varchar UNIQUE
) ;

CREATE TABLE race_result
(
    race_id integer REFERENCES race(race_id),
    runner_id integer REFERENCES runner(runner_id),
    finish_position integer NOT NULL,
    UNIQUE (race_id,finish_position),
    PRIMARY KEY (runner_id, race_id)
);

CREATE /* TEMP */ TABLE tmp_x
(
    runner_name varchar,
    race_name varchar,
    finish_position integer
);

And this is your data

-- COPY tmp_x FROM 'input.csv' (FORMAT csv);
INSERT INTO tmp_x
VALUES
  ('runner1', 'race1', 1),
  ('runner2', 'race1', 2),
  ('runner3', 'race1', 3),
  ('runner4', 'race1', 4),
  ('runner5', 'race1', 5),
  ('runner1', 'race2', 5),
  ('runner2', 'race2', 4),
  ('runner3', 'race2', 3),
  ('runner4', 'race2', 2),
  ('runner5', 'race2', 1),
  ('runner6', 'race3', 1),
  ('runner2', 'race3', 2),
  ('runner7', 'race3', 3),
  ('runner4', 'race3', 4),
  ('runner9', 'race3', 5) ;

I would insert the data in 3 steps. First, you INSERT in your runners table  the ones you don't yet have. Then you do exactly the same for races.

-- Create new runners
INSERT INTO
   runner (runner_name)
SELECT DISTINCT
   runner_name
FROM
   tmp_x
WHERE
   NOT EXISTS (SELECT *
                 FROM runner r0
                WHERE r0.runner_name = tmp_x.runner_name)
ORDER BY
    runner_name ;

-- Create new races
INSERT INTO
   race (race_name)
SELECT DISTINCT
   race_name
FROM
   tmp_x
WHERE
   NOT EXISTS (SELECT *
                 FROM race r0
                WHERE r0.race_name = tmp_x.race_name)
ORDER BY
    race_name;

NOTE: Using "NOT EXISTS..." works with versions of PostgreSQL older than 9.6, and is (AFAIK) pure standard SQL; so, it should work on other DB as well.

And now that your master tables are filled in, you JOIN your temporary table to them, and add your finish positions. [I assume the combinations are new data, and we do not need to check for already existing data.]

INSERT INTO
    race_result (race_id, runner_id, finish_position)
SELECT
    race_id, runner_id, finish_position
FROM
    tmp_x
    JOIN runner USING(runner_name)
    JOIN race USING(race_name)
ORDER BY
    race_id, runner_id, finish_position ;

At this point, you can query:

SELECT
  race_name, finish_position, runner_name
FROM
  race_result
  JOIN runner USING(runner_id)
  JOIN race USING(race_id)
ORDER BY
  race_name, finish_position, runner_name;

... and get

| race_name | finish_position | runner_name |
|-----------|-----------------|-------------|
|     race1 |               1 |     runner1 |
|     race1 |               2 |     runner2 |
|     race1 |               3 |     runner3 |
|     race1 |               4 |     runner4 |
|     race1 |               5 |     runner5 |
|     race2 |               1 |     runner5 |
|     race2 |               2 |     runner4 |
|     race2 |               3 |     runner3 |
|     race2 |               4 |     runner1 |
|     race2 |               5 |     runner2 |
|     race3 |               1 |     runner6 |
|     race3 |               2 |     runner2 |
|     race3 |               3 |     runner7 |
|     race3 |               4 |     runner4 |
|     race3 |               5 |     runner9 |

Check it all at SQLFiddle

NOTE: Your race_result  table doesn't need a surrogate primary key. There's a natural key (race_id, runner_id) or (race_id, finish_position).


Can we protect data form Database Administrator by encrypting the data?

I have an old post where I discussed an actual product I was evaluating : How to properly secure MySQL database?. The product Gazzang (now Cloudera) would basically encrypt the data and the framework requires using specific keys. Even if you copied the files, the keys not being present in the target system made everything inaccessible just from an OS standpoint.

If you do not trust a DBA with the key, you may have issue specific MySQL grants to SysAdmin/DBAs: SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, REPLICATION CLIENT, and REPLICATION SLAVE. All other GRANTs would need to be given to Project Leaders and Lead Developers.

As you can tell from this answer, your question makes the leap from technology to office politics. We all wish there was some 100% automated HIPAA compilance framework around a database. Until then, you would need to scrupulously define what an operational DBA can and cannot do.


For TDE:

Do transportable tablespaces work with TDE tablespace encryption?

  Yes, but it requires that the wallet containing the master key is
  copied to the secondary database. If the tablespace is moved and the
  master key is not available, the secondary database will return an
  error when the data in the tablespace is accessed.

How to use TTS With TDE (Doc ID 1674166.1)

  ... Only difference when transporting tablespace using TDE is the
  wallet needs to copied to the target database. ...


how to encrypt one column of a table without using always encrypted?

SQL Server has had column encryption with server-managed keys since SQL 2005.  See Encrypt a Column of Data.

AlwaysEncrypted uses client-side keys.


What is the best way to determine, without an Oracle support contract, the release date of an Oracle Database 12c patchset?

Patchsets? That is easy.

12.1.0.1 is a base release
12.2.0.1 is a base release
12.1.0.2 is the one and only 12c patchset, it was released in July 2014: https://blogs.oracle.com/imc/oracle-database-12c-release-1-12102-generally-available

For PSU/RU/RUR/one-off patches, opatch lists the date they were packaged, e.g.:

$ /u01/app/oracle/product/12.2.0/dbhome_1/OPatch/opatch lsinv | grep Created -B 1

Patch description:  "Database Jul 2018 Release Update : 12.2.0.1.180717 (28163133)"
   Created on 6 Jul 2018, 08:05:03 hrs PST8PDT

Note that this is not the release date, they can be packaged weeks before the actual release.

Another thing to note, Oracle includes the release date in PSU/RU/RUR versions since 2016. So the version 12.2.0.1.180717 above means it is the RU for 12.2.0.1 that was released on 2018-07-17.

The list of most recent or the one released prior PSU/RUs:

12.1.0.2.180417
12.1.0.2.180717
12.2.0.1.180417
12.2.0.1.180717

The patching for 12.1.0.1 ended 2 years ago (2016-08-31).


Isn't a tab (char(9) ) whitespace in the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3)?

The same code above on MySQL 5.5 gives the same result

Tab isn't "space", it is a character.
The same applies to hard/non-breaking space (char(160) in "normal" collations)

The ANSI-92 standard states in 8.2. item 3.a

  If the length in characters of X is not equal to the length
                in characters of Y, then the shorter string is effectively
                replaced, for the purposes of comparison, with a copy of
                itself that has been extended to the length of the longer
                string by concatenation on the right of one or more pad char-
                acters, where the pad character is chosen based on CS. If
                CS has the NO PAD attribute, then the pad character is an
                implementation-dependent character different from any char-
                acter in the character set of X and Y that collates less
                than any string under CS. Otherwise, the pad character is a <space>.

CS is "collating sequence". I'll assume no collation uses tab or non-breaking spaces as padding characters. So space it is...

Note that LIKE is section 8.5 (I mention this because of your previous question)


Efficiency for contact-contact relationships schema

If you can guarantee that each person can only belong to a single organisation there is no reason why you could not add an extra column to store the organisation_id to your table. Then you could use a self join in the query to get the organisation per person.

If each person can belong to multiple organisations then you would need to maintain the list of relationships in a separate table which has person_id and organisation_id columns and then join off this to produce the information you require.

EDIT

Based on the additional information provided it looks like you will need to implement the second method where relationships are stored in a separate table. This table should contain at least two columns which should both be foreign keys to the primary key in your contacts table. This way a contact can be related in anyway to another contact whether that be an organisation, a person, or any other object you class as a contact. Using this design you can link the two keys by joining the contact table from each column to get the information for both sides of the relationship.

I hope this helps you.


How do you minimize chance of collision between system-named constraints on temp tables?

The easiest solution is to use a unique clustered index which does not have to be unique in the system (unlike a primary key constraint). Otherwise, they mostly function the same - a unique index is still a constraint that prevents duplicates and can also be used as the target of foreign keys.

I forget if this specific inline index syntax was possible in 2016 (I only have 2019 locally), but this works fine on newer versions:

CREATE TABLE #fooA(id int, INDEX PK1 UNIQUE clustered(id));
CREATE TABLE #fooB(id int, INDEX PK1 UNIQUE clustered(id));

If that syntax was not yet valid on 2016, you could create a unique clustered index in a second step, which you'd have to name yourself (but you wouldn't have to find some convoluted way to make that name unique).

Also, instead of over-using generic names like #TempTable, use names more specific to the table contents / procedure functionality (like #Orders, #Customers, etc). In cases where you need an explicit PK, this would at least reduce the chance of collisions if all of your PKs will no longer be system-named PK__#TempTab_....

As an aside, I don't think I've ever seen so many temp tables in a system that there are collisions on these long, tried-to-be-unique names. Are you trying to overwhelm the metadata?


I have written a little function in Powershell that applies a driver table to a template in order to produce a simple repetitive result.  It was really a learning exercise to help me become proficient in Powershell.
Here is the function:
 <#
.NOTES
    Script: Expand-Csv    Rev:  3.2
    By:     DGC           Date: 2-21-19
.SYNOPSIS
    Generates multiple expansions of a template,
    driven by data in a CSV file.
.DESCRIPTION
    This function is a table driven template tool.

    It generates output from a template and
    a driver table.  The template file contains plain
    text and embedded variables.  The driver table
    (in a csv file) has one column for each variable,
    and one row for each expansion to be generated.
#>
function Expand-csv {
    [CmdletBinding()]
    Param (
       [Parameter(Mandatory=$true)]
       [string] $driver,
       [Parameter(Mandatory=$true)]
       [string] $template
    )
    Process {
       Import-Csv $driver | % {
           $_.psobject.properties | % {Set-variable -name $_.name -value $_.value}
           Get-Content $template | % {$ExecutionContext.InvokeCommand.ExpandString($_)}
       }
    }
}

Expand-Csv takes two inputs.  The first is a CSV file that represents the driver table.  In this  example,  the csv file might look like this.
privs,table,user
ALL,Employees,DBA
READ,Employees,Analyst
"READ, WRITE", Employees, Application
ALL,Departments,DBA
READ,Departments,"Analyst, Application"

The template might look like this:
grant $privs
   on $table
   to $user;

The result of running it through Expand-Csv might look like this:
grant ALL
   on Employees
   to DBA;

grant READ
   on Employees
   to Analyst;

grant READ, WRITE
   on Employees
   to Application;

grant ALL
   on Departments
   to DBA;

grant READ
   on Departments
   to Analyst, Application;

This result is fed down the pipeline,  but it could easily be written to a file for later execution by SQL.
A superior tool might be written that takes as input a table or view in a database instead of a .CSV file.  I wanted to do the easy stuff first.
I apologize for the fact that the code is not easy to read.  I was interested in minimizing the use of variables inside it,  so I used $_ in more than one context.  At least it's small.
You may be able to use this tool as a starting point to develop something that will make your workload easier.  I hope so.


Shoud I uninstall mongodb 2.6 before upgrade to 3.0?

No its not necessary to uninstall.
I don't really like yum or apt for installing mongoDB. I suggest you to download the binaries from https://www.mongodb.org/dl/linux/x86_64 , linux/mongodb-linux-x86_64-ubuntu1404-3.1.0.tgz (or the v12) and use it to start mongodb.


Is it possible to have more than one replica on a PostgreSQL server?

You understood correctly. Since streaming replication keeps the replica a physical copy of the primary database cluster, you can only replicate between one primary and one replica.

There are two options:

Keep three clusters on the replica machine, one for each primary cluster.
Use logical replication.

With logical replication, you have to make sure that each subscription replicates to different tables, ideally in different databases.


Query Store Force Plan feature doesn't work

From sys.query_store_plan (Transact-SQL) (emphasis added)

  Plan forcing limitations

  Query Store has a mechanism to enforce Query Optimizer to use certain execution plan. However, there are some limitations that can prevent a plan to be enforced.

  First, if the plan contains following constructions:

  Insert bulk statement.
  Reference to an external table
  Distributed query or full-text operations
  Use of Global queries
  Cursors
  Invalid star join specification

You are using a cursor.

You can often force a cursor plan with a plan guide instead. See Using the USE PLAN Query Hint on Queries with Cursors.

And yes, I agree that the current situation does not make for the best user experience. I would expect that attempting to force a plan for something that cannot (currently) be forced would raise an error or warning, or log something. You could log a Feedback item to request an improvement in this area.

As of SQL 2019 CTP 2.3 forcing query execution plan for Fast Forward and Static cursors are supported

  Query Store now supports the ability to force query execution plans for fast forward and static T-SQL and API cursors. Forcing is now supported via sp_query_store_force_plan or through SQL Server Management Studio Query Store reports.


Update column with value of another column or another column

You can use COALESCE() function to do the update:

UPDATE ...
SET columnA = COALESCE(columnB, columnC, now());

COALESCE will return the first non-null value in the list that you provide.


Can sharding speed up insertion performance in postgresql

As has been clarified in the comments, accessing a sequence will not be a bottleneck in PostgreSQL.
If you are faster inserting into a sharded database depends on where the bottleneck is.
If it is disk I/O or CPU on the database, you will probably be faster spreading the workload across several databases (sharding), as long as they have independent storage and network bandwidth is not a bottleneck.
If the bottleneck is on the client side, you probably won't gain much.


Display single record for attributes values joining 3 tables in SQL Server?

Here is a very simple way to solve this.

--Setup demo data
Declare @Table1 table
    (ID int, Name varchar(5), AttrTypeID int, AttrName varchar(10), AttributeValue varchar(24))
;

INSERT INTO @Table1
    (ID, Name, AttrTypeID, AttrName, AttributeValue)
VALUES
    (123456, 'Bob', 201, 'Hair Color', 'Blonde'),
    (123456, 'Bob', 202, 'Eye Color', 'Blue'),
    (123456, 'Bob', 203, 'Height CM', '180'),
    (123456, 'Bob', 204, 'Weight KG', '77.1'),
    (123456, 'Bob', 205, 'Email', 'bob@domain.com'),
    (123456, 'Bob', 206, 'Profile', 'Text...2000characters...'),
    (789000, 'Sarah', 201, 'Hair Color', 'Brown'),
    (789000, 'Sarah', 202, 'Eye Color', 'Brown'),
    (789000, 'Sarah', 203, 'Height CM', '155'),
    (789000, 'Sarah', 204, 'Weight KG', '50.5'),
    (789000, 'Sarah', 206, 'Profile', 'Text...2000characters...')
;

--The actual query
SELECT ID, max(name) as Name,
  MAX(CASE WHEN AttrName = 'Hair Color' THEN AttributeValue END) AS [Hair Color],
  MAX(CASE WHEN AttrName = 'Eye Color' THEN AttributeValue END) AS [Eye Color],
  MAX(CASE WHEN AttrName = 'Height CM' THEN AttributeValue END) AS [Height CM],
  MAX(CASE WHEN AttrName = 'Weight KG' THEN AttributeValue END) AS [Weight KG],
  MAX(CASE WHEN AttrName = 'Email' THEN AttributeValue END) AS [Email],
  MAX(CASE WHEN AttrName = 'Profile' THEN AttributeValue END) AS [Profile]
FROM @Table1
GROUP BY id, name;

| ID     | Name  | Hair Color | Eye Color | Height CM | Weight KG | Email          | Profile                  |
|--------|-------|------------|-----------|-----------|-----------|----------------|--------------------------|
| 123456 | Bob   | Blonde     | Blue      | 180       | 77.1      | bob@domain.com | Text...2000characters... |
| 789000 | Sarah | Brown      | Brown     | 155       | 50.5      | NULL           | Text...2000characters... |

You just need to wrap your original query inside a common table expression and use that in place of my @Table1.  Something like:

;With Table1 as
(
SELECT DISTINCT Table1.ID Table1.NAME Table2.AttrTypeID Table2.AttrName Table1.AttributeValue
FROM Table1
RIGHT JOIN Table2 ON Table1.AttributeTypeID = Table2.AttrTypeID
WHERE Table1.ID IN (
        SELECT DISTINCT EntryID
        FROM Table3
        WHERE EntryDateTime BETWEEN '2018-05-01 00:00:00.000'
                AND '2018-05-07 00:00:00.000'
        )
)

--The actual query
SELECT ID, max(name) as Name,
  MAX(CASE WHEN AttrName = 'Hair Color' THEN AttributeValue END) AS [Hair Color],
  MAX(CASE WHEN AttrName = 'Eye Color' THEN AttributeValue END) AS [Eye Color],
  MAX(CASE WHEN AttrName = 'Height CM' THEN AttributeValue END) AS [Height CM],
  MAX(CASE WHEN AttrName = 'Weight KG' THEN AttributeValue END) AS [Weight KG],
  MAX(CASE WHEN AttrName = 'Email' THEN AttributeValue END) AS [Email],
  MAX(CASE WHEN AttrName = 'Profile' THEN AttributeValue END) AS [Profile]
FROM Table1
GROUP BY id, name;


You do not have permission to add users to the db_ddladmin role in the master database.
Since
The Server admin and Azure AD admin accounts have the following characteristics:

Do not enter the master database as the dbo user, and have limited
permissions in master.
Are not members of the standard SQL Server sysadmin fixed server role,
which is not available in SQL database.
Can create, alter, and drop databases, logins, users in master, and server-level IP firewall rules.

Source
When querying the sys.database_role_members system view in the master database, you will also see that only dbo is part of the db_owner role. For most or all other roles, no members show up.
DatabaseRoleName         DatabaseUserName
db_accessadmin           No members
db_backupoperator        No members
db_datareader            No members
db_datawriter            No members
db_ddladmin              No members
db_denydatareader        No members
db_denydatawriter        No members
db_owner                 dbo
db_securityadmin         No members
dbmanager                No members
loginmanager             No members
public                   No members

This means that specific permissions are granted to the user connecting to the master database, adding users to the db_ddladmin role in master is not one of them.
As documented, you can add users to the dbmanager & loginmanager db roles in master.
alter role [dbmanager] add member sqlloginname;
alter role [loginmanager] add member sqlloginname;

You can add users to the db_ddladmin role in user databases when connecting with the server admin since you will have dbo permissions there.
Remember that these are database roles, and that you will have to add the user per database.


Could not add Replica Set to MongoDB Shard

So i have found my mistake. I had to add the Servernames and IP Addresses to the hosts file of the docker container in which the config Servers runs. After that i could add my Replica Set as a Shard. I will edit this answer this or next week and provide a link to a Blogpost in which i will describe how to make a MongoDB Sharding with Docker on Virtual Machines.


ORA-00604: error occurred at recursive SQL level 1

Even if you use the same credentials, your colleague may have a different environment with PL/Scope disabled.

SQL> create procedure p1 as
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

Compile the procedure without PL/Scope:

SQL> show parameter plscope

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plscope_settings                     string      IDENTIFIERS:NONE
SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

No change. Now compile with PL/Scope enabled:

SQL> alter session set plscope_settings='IDENTIFIERS:ALL';

Session altered.

SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2058

This created some extra metadata in the above table which happens to be in the SYSAUX tablespace. Now disable and compile again:

SQL> alter session set plscope_settings='IDENTIFIERS:NONE';

Session altered.

SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

Back to the original state.

This is the problem:

ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_IDENTIFIER$ by 8 in tablespace SYSAUX, which simply means the SYSAUX tablespace is full.

Being unable to compile the PL/SQL code with PL/Scope enabled is just a symptom of the above error. Ask the DBA to make/increase space in the SYSAUX tablespace. PL/Scope is a configurable option in SQL Developer (Tools - Preferences - Database - PL/SQL Compiler - PLScope identifiers).


How to get only month from MySQL date/time?

SUGGESTION #1
You could use the MONTH() function
mysql> select MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            3 |
+--------------+
1 row in set (0.03 sec)

mysql> select MONTH('2013-03-17 02:53:47');
+------------------------------+
| MONTH('2013-03-17 02:53:47') |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.04 sec)

mysql>

You would have to put the MONTH function into the SQL command you are running.
SUGGESTION #2
You could also try the PHP Date Function
$month = date("m",$date)

Also DATE_FORMAT(date_column, '%c') to get '3' as result and DATE_FORMAT(date_column, '%m') to get '03' as result.


First, if you're getting corruption issues in TempDB, I'd start by making sure I've got DBCC CHECKDB reporting clean results across my user databases. I'd also restore those backups on another server and run DBCC CHECKDB there just to make sure the problems are only confined to TempDB.

Then I'd remove any filter drivers on the servers - antivirus, disk defragmenting, storage mirroring, etc - and try to run multiple load-intensive queries against TempDB to see if I can reproduce the issue. Do simple select-intos, dumping Cartesian joins of your user databases into TempDB.

Finally, if I can get an outage window, I'd try SQLIO - not SQLIOSIM - hammering the TempDB drives. SQLIOSIM is interesting if you want to reproduce a vague similarity of a benchmark, but SQLIO is better if you just want to repeatedly punch your storage in the junk. Plus, it's really good about bubbling failures up quickly.

You also asked:

  how much more likely is drive corruption than, say, I/O path driver
  issues (storport, HBA driver or firmware, filter drivers).

In my experience, the most common corruption issues are connectivity problems, followed by storage processor/controller firmware bugs, followed by filter drivers (I see less only because they were so unreliable that most folks have uninstalled them), finally followed by drive corruption. After all, in modern storage, the RAID controllers are pretty good about catching problems with a single failing drive.


What happens when we add an index to an existing table with a large amount of data?

With plain CREATE INDEX, the table will be locked for writes but not reads.

Use CREATE INDEX CONCURRENTLY to avoid write locks as well.

From the PostgreSQL docs on CREATE INDEX:

  When this option is used, PostgreSQL will build the index without
  taking any locks that prevent concurrent inserts, updates, or deletes
  on the table; whereas a standard index build locks out writes (but not
  reads) on the table until it's done. There are several caveats to be
  aware of when using this option — see Building Indexes Concurrently.

And more specifically (Like @ypercube commented):

  PostgreSQL supports building indexes without locking out writes. This
  method is invoked by specifying the CONCURRENTLY option of CREATE INDEX.
   When this option is used, PostgreSQL must perform two scans of
  the table, and in addition it must wait for all existing transactions
  that could potentially use the index to terminate. Thus this method
  requires more total work than a standard index build and takes
  significantly longer to complete. However, since it allows normal
  operations to continue while the index is built, this method is useful
  for adding new indexes in a production environment.

Bold emphasis mine.


compare/sync databases from different computers

You can use Percona's pt-table-sync tool, even if the two MySQL instances are not setup for replication, with this caveat:

  pt-table-sync does one-way and bidirectional synchronization of table data. It does not synchronize table structures, indexes, or any other schema objects

So something like this command (if they're not set up in replication..there's other options for the tool if they are):

pt-table-sync --dry-run host1 host2

After you're comfortable with the report, run it:

pt-table-sync --dry-run host1 host2

When using this tool, I would first make a backup of both instances. Always backup in case an issue arises!


Performance counter MSSQLInstance\SQL Errors\Errors/sec\Info Errors whats in it?

I dug a little deeper myself and it seems that the definition of information errors is errors with severity 10 according to the documentation of Severity and found my specific "Info errors" by profiling on the User Error Message Event.  In my case it was database context switches with the message:

  Changed database context to 'DBNAME'

and language changes with the message

  Changed language setting to us_english

And a few of the ones that @aaron-bertrand mentions


A question on MySQL SELECT Algorithm/Function

To see how MySQL is doing something you want to look at the execution plan. More information on MySQL Execution plans at http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html . You should think of SQL as a declarative language, you tell it to do something , and it writes a program to do that. The execution plan shows you the program it wrote.

SQL will be able to look up specific entries when there is an index on the column ( like an index from a book). However, a binary field with a split distribution is a classic example of a column that doesn't have good selectivity, in other words, it isn't going to narrow down the search very much.


Functional Indexes in Oracle, compared to MySQL

MySQL does not have functional indexes but it does have (at least in version 5.7 and in MariaDB 5.5+) generated columns which can be indexed.

For details see the official documentation: Generated Columns.

Example of usage:

ALTER TABLE schema1.table1
    ADD lower_lastname VARCHAR(100) AS (LOWER(lastname)) ;

CREATE INDEX idx_table1_lastname
    ON schema1.table1 (lower_lastname) ;

And test at dbfiddle.uk where you can see that the index is used.

Now, the above construction is not needed if you never need to distinguish 'Jack' from 'jack' and you have defined the column with a case insensitive collation. You can just the case insensitive column and index it.

The construction is useful if you want to have case sensitive and case insensitive comparisons and also in other areas where the Oracle database has functional indexes (i.e. with numeric or date columns).


8Kb row size limits ignored - but only sometimes!

SQL Version:
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
Response:
The pointer to the LOB_DATA page (your 24 byte pointer) is only used when the row containing your VARBINARY(MAX) column (s) no longer can fit on the page.
I am still working on matching the exact scenario you mentioned above.
Reference(s):
Slot Array and Total Page Size

USE [master];
GO

IF DATABASEPROPERTYEX (N'RowSize', N'Version') > 0
BEGIN
    ALTER DATABASE [RowSize] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [RowSize];
END;
GO

CREATE DATABASE [RowSize];
GO

USE [RowSize];
GO

IF OBJECT_ID('test' , 'U') IS NOT NULL
BEGIN
     DROP TABLE dbo.test;
END;

-- Create the wide table
CREATE TABLE dbo.test
(
      c1 UNIQUEIDENTIFIER NULL
    , c2 UNIQUEIDENTIFIER NULL
    , c3 DATETIME NULL
    , c4 DATETIME NULL
    , c5 VARBINARY(MAX) NULL
    , c6 VARBINARY(MAX) NULL
    , c7 VARBINARY(MAX) NULL
    , c8 VARBINARY(MAX) NULL
    , c9 VARBINARY(MAX) NULL
    , c10 VARBINARY(MAX) NULL
    , c11 VARBINARY(MAX) NULL
    , c12 VARBINARY(MAX) NULL
    , c13 VARBINARY(MAX) NULL
    , c14 VARBINARY(MAX) NULL
    , c15 VARBINARY(MAX) NULL
    , c16 VARBINARY(MAX) NULL
    , c17 VARBINARY(MAX) NULL
    , c18 VARBINARY(MAX) NULL
    , c19 VARBINARY(MAX) NULL
    , c20 VARBINARY(MAX) NULL
    , c21 VARBINARY(MAX) NULL
    , c22 VARBINARY(MAX) NULL
    , c23 VARBINARY(MAX) NULL
    , c24 VARBINARY(MAX) NULL
    , c25 VARBINARY(MAX) NULL
    , c26 VARBINARY(MAX) NULL
    , c27 VARBINARY(MAX) NULL
    , c28 VARBINARY(MAX) NULL
    , c29 VARBINARY(MAX) NULL
    , c30 VARBINARY(MAX) NULL
    , c31 VARBINARY(MAX) NULL
    , c32 VARBINARY(MAX) NULL
    , c33 VARBINARY(MAX) NULL
    , c34 VARBINARY(MAX) NULL
    , c35 VARBINARY(MAX) NULL
    , c36 VARBINARY(MAX) NULL
    , c37 VARBINARY(MAX) NULL
    , c38 VARBINARY(MAX) NULL
    , c39 VARBINARY(MAX) NULL
    , c40 VARBINARY(MAX) NULL
    , c41 VARBINARY(MAX) NULL
    , c42 VARBINARY(MAX) NULL
    , c43 VARBINARY(MAX) NULL
    , c44 VARBINARY(MAX) NULL
    , c45 VARBINARY(MAX) NULL
    , c46 VARBINARY(MAX) NULL
    , c47 VARBINARY(MAX) NULL
    , c48 VARBINARY(MAX) NULL
    , c49 VARBINARY(MAX) NULL
    , c50 VARBINARY(MAX) NULL
    , c51 VARBINARY(MAX) NULL
    , c52 VARBINARY(MAX) NULL
    , c53 VARBINARY(MAX) NULL
    , c54 VARBINARY(MAX) NULL
    , c55 VARBINARY(MAX) NULL
    , c56 VARBINARY(MAX) NULL
    , c57 VARBINARY(MAX) NULL
    , c58 VARBINARY(MAX) NULL
    , c59 VARBINARY(MAX) NULL
    , c60 VARBINARY(MAX) NULL
    , c61 VARBINARY(MAX) NULL
    , c62 VARBINARY(MAX) NULL
    , c63 VARBINARY(MAX) NULL
    , c64 VARBINARY(MAX) NULL
    , c65 VARBINARY(MAX) NULL
    , c66 VARBINARY(MAX) NULL
    , c67 VARBINARY(MAX) NULL
    , c68 VARBINARY(MAX) NULL
    , c69 VARBINARY(MAX) NULL
    , c70 VARBINARY(MAX) NULL
    , c71 VARBINARY(MAX) NULL
    , c72 VARBINARY(MAX) NULL
    , c73 VARBINARY(MAX) NULL
    , c74 VARBINARY(MAX) NULL
    , c75 VARBINARY(MAX) NULL
    , c76 VARBINARY(MAX) NULL
    , c77 VARBINARY(MAX) NULL
    , c78 VARBINARY(MAX) NULL
    , c79 VARBINARY(MAX) NULL
    , c80 VARBINARY(MAX) NULL
    , c81 VARBINARY(MAX) NULL
    , c82 VARBINARY(MAX) NULL
    , c83 VARBINARY(MAX) NULL
    , c84 VARBINARY(MAX) NULL
    , c85 VARBINARY(MAX) NULL
    , c86 VARBINARY(MAX) NULL
    , c87 VARBINARY(MAX) NULL
    , c88 VARBINARY(MAX) NULL
    , c89 VARBINARY(MAX) NULL
    , c90 VARBINARY(MAX) NULL
    , c91 VARBINARY(MAX) NULL
    , c92 VARBINARY(MAX) NULL
    , c93 VARBINARY(MAX) NULL
    , c94 VARBINARY(MAX) NULL
    , c95 VARBINARY(MAX) NULL
    , c96 VARBINARY(MAX) NULL
    , c97 VARBINARY(MAX) NULL
    , c98 VARBINARY(MAX) NULL
    , c99 VARBINARY(MAX) NULL
    , c100 VARBINARY(MAX) NULL
    , c101 VARBINARY(MAX) NULL
    , c102 VARBINARY(MAX) NULL
    , c103 VARBINARY(MAX) NULL
    , c104 VARBINARY(MAX) NULL
    , c105 VARBINARY(MAX) NULL
    , c106 VARBINARY(MAX) NULL
    , c107 VARBINARY(MAX) NULL
    , c108 VARBINARY(MAX) NULL
    , c109 VARBINARY(MAX) NULL
    , c110 VARBINARY(MAX) NULL
    , c111 VARBINARY(MAX) NULL
    , c112 VARBINARY(MAX) NULL
    , c113 VARBINARY(MAX) NULL
    , c114 VARBINARY(MAX) NULL
    , c115 VARBINARY(MAX) NULL
    , c116 VARBINARY(MAX) NULL
    , c117 VARBINARY(MAX) NULL
    , c118 VARBINARY(MAX) NULL
    , c119 VARBINARY(MAX) NULL
    , c120 VARBINARY(MAX) NULL
    , c121 VARBINARY(MAX) NULL
    , c122 VARBINARY(MAX) NULL
    , c123 VARBINARY(MAX) NULL
    , c124 VARBINARY(MAX) NULL
    , c125 VARBINARY(MAX) NULL
    , c126 VARBINARY(MAX) NULL
    , c127 VARBINARY(MAX) NULL
    , c128 VARBINARY(MAX) NULL
    , c129 VARBINARY(MAX) NULL
    , c130 VARBINARY(MAX) NULL
    , c131 VARBINARY(MAX) NULL
    , c132 VARBINARY(MAX) NULL
    , c133 VARBINARY(MAX) NULL
    , c134 VARBINARY(MAX) NULL
    , c135 VARBINARY(MAX) NULL
    , c136 VARBINARY(MAX) NULL
    , c137 VARBINARY(MAX) NULL
    , c138 VARBINARY(MAX) NULL
    , c139 VARBINARY(MAX) NULL
    , c140 VARBINARY(MAX) NULL
    , c141 VARBINARY(MAX) NULL
    , c142 VARBINARY(MAX) NULL
    , c143 VARBINARY(MAX) NULL
    , c144 VARBINARY(MAX) NULL
    , c145 VARBINARY(MAX) NULL
    , c146 VARBINARY(MAX) NULL
    , c147 VARBINARY(MAX) NULL
    , c148 VARBINARY(MAX) NULL
    , c149 VARBINARY(MAX) NULL
    , c150 VARBINARY(MAX) NULL
    , c151 VARBINARY(MAX) NULL
    , c152 VARBINARY(MAX) NULL
    , c153 VARBINARY(MAX) NULL
    , c154 VARBINARY(MAX) NULL
    , c155 VARBINARY(MAX) NULL
    , c156 VARBINARY(MAX) NULL
    , c157 VARBINARY(MAX) NULL
    , c158 VARBINARY(MAX) NULL
    , c159 VARBINARY(MAX) NULL
    , c160 VARBINARY(MAX) NULL
    , c161 VARBINARY(MAX) NULL
    , c162 VARBINARY(MAX) NULL
    , c163 VARBINARY(MAX) NULL
    , c164 VARBINARY(MAX) NULL
    , c165 VARBINARY(MAX) NULL
    , c166 VARBINARY(MAX) NULL
    , c167 VARBINARY(MAX) NULL
    , c168 VARBINARY(MAX) NULL
    , c169 VARBINARY(MAX) NULL
    , c170 VARBINARY(MAX) NULL
    , c171 VARBINARY(MAX) NULL
    , c172 VARBINARY(MAX) NULL
    , c173 VARBINARY(MAX) NULL
    , c174 VARBINARY(MAX) NULL
    , c175 VARBINARY(MAX) NULL
    , c176 VARBINARY(MAX) NULL
    , c177 VARBINARY(MAX) NULL
    , c178 VARBINARY(MAX) NULL
    , c179 VARBINARY(MAX) NULL
    , c180 VARBINARY(MAX) NULL
    , c181 VARBINARY(MAX) NULL
    , c182 VARBINARY(MAX) NULL
    , c183 VARBINARY(MAX) NULL
    , c184 VARBINARY(MAX) NULL
    , c185 VARBINARY(MAX) NULL
    , c186 VARBINARY(MAX) NULL
    , c187 VARBINARY(MAX) NULL
    , c188 VARBINARY(MAX) NULL
    , c189 VARBINARY(MAX) NULL
    , c190 VARBINARY(MAX) NULL
    , c191 VARBINARY(MAX) NULL
    , c192 VARBINARY(MAX) NULL
    , c193 VARBINARY(MAX) NULL
    , c194 VARBINARY(MAX) NULL
    , c195 VARBINARY(MAX) NULL
    , c196 VARBINARY(MAX) NULL
    , c197 VARBINARY(MAX) NULL
    , c198 VARBINARY(MAX) NULL
    , c199 VARBINARY(MAX) NULL
    , c200 VARBINARY(MAX) NULL
    , c201 VARBINARY(MAX) NULL
    , c202 VARBINARY(MAX) NULL
    , c203 VARBINARY(MAX) NULL
    , c204 VARBINARY(MAX) NULL
    , c205 VARBINARY(MAX) NULL
    , c206 VARBINARY(MAX) NULL
    , c207 VARBINARY(MAX) NULL
    , c208 VARBINARY(MAX) NULL
    , c209 VARBINARY(MAX) NULL
    , c210 VARBINARY(MAX) NULL
    , c211 VARBINARY(MAX) NULL
    , c212 VARBINARY(MAX) NULL
    , c213 VARBINARY(MAX) NULL
    , c214 VARBINARY(MAX) NULL
    , c215 VARBINARY(MAX) NULL
    , c216 VARBINARY(MAX) NULL
    , c217 VARBINARY(MAX) NULL
    , c218 VARBINARY(MAX) NULL
    , c219 VARBINARY(MAX) NULL
    , c220 VARBINARY(MAX) NULL
    , c221 VARBINARY(MAX) NULL
    , c222 VARBINARY(MAX) NULL
    , c223 VARBINARY(MAX) NULL
    , c224 VARBINARY(MAX) NULL
    , c225 VARBINARY(MAX) NULL
    , c226 VARBINARY(MAX) NULL
    , c227 VARBINARY(MAX) NULL
    , c228 VARBINARY(MAX) NULL
    , c229 VARBINARY(MAX) NULL
    , c230 VARBINARY(MAX) NULL
    , c231 VARBINARY(MAX) NULL
    , c232 VARBINARY(MAX) NULL
    , c233 VARBINARY(MAX) NULL
    , c234 VARBINARY(MAX) NULL
    , c235 VARBINARY(MAX) NULL
    , c236 VARBINARY(MAX) NULL
    , c237 VARBINARY(MAX) NULL
    , c238 VARBINARY(MAX) NULL
    , c239 VARBINARY(MAX) NULL
    , c240 VARBINARY(MAX) NULL
    , c241 VARBINARY(MAX) NULL
    , c242 VARBINARY(MAX) NULL
    , c243 VARBINARY(MAX) NULL
    , c244 VARBINARY(MAX) NULL
    , c245 VARBINARY(MAX) NULL
    , c246 VARBINARY(MAX) NULL
    , c247 VARBINARY(MAX) NULL
    , c248 VARBINARY(MAX) NULL
    , c249 VARBINARY(MAX) NULL
    , c250 VARBINARY(MAX) NULL
    , c251 VARBINARY(MAX) NULL
    , c252 VARBINARY(MAX) NULL
    , c253 VARBINARY(MAX) NULL
    , c254 VARBINARY(MAX) NULL
    , c255 VARBINARY(MAX) NULL
    , c256 VARBINARY(MAX) NULL
    , c257 VARBINARY(MAX) NULL
    , c258 VARBINARY(MAX) NULL
    , c259 VARBINARY(MAX) NULL
    , c260 VARBINARY(MAX) NULL
    , c261 VARBINARY(MAX) NULL
    , c262 VARBINARY(MAX) NULL
    , c263 VARBINARY(MAX) NULL
    , c264 VARBINARY(MAX) NULL
    , c265 VARBINARY(MAX) NULL
    , c266 VARBINARY(MAX) NULL
    , c267 VARBINARY(MAX) NULL
    , c268 VARBINARY(MAX) NULL
    , c269 VARBINARY(MAX) NULL
    , c270 VARBINARY(MAX) NULL
    , c271 VARBINARY(MAX) NULL
    , c272 VARBINARY(MAX) NULL
    , c273 VARBINARY(MAX) NULL
    , c274 VARBINARY(MAX) NULL
    , c275 VARBINARY(MAX) NULL
    , c276 VARBINARY(MAX) NULL
    , c277 VARBINARY(MAX) NULL
    , c278 VARBINARY(MAX) NULL
    , c279 VARBINARY(MAX) NULL
    , c280 VARBINARY(MAX) NULL
    , c281 VARBINARY(MAX) NULL
    , c282 VARBINARY(MAX) NULL
    , c283 VARBINARY(MAX) NULL
    , c284 VARBINARY(MAX) NULL
    , c285 VARBINARY(MAX) NULL
    , c286 VARBINARY(MAX) NULL
    , c287 VARBINARY(MAX) NULL
    , c288 VARBINARY(MAX) NULL
    , c289 VARBINARY(MAX) NULL
    , c290 VARBINARY(MAX) NULL
    , c291 VARBINARY(MAX) NULL
    , c292 VARBINARY(MAX) NULL
    , c293 VARBINARY(MAX) NULL
    , c294 VARBINARY(MAX) NULL
    , c295 VARBINARY(MAX) NULL
    , c296 VARBINARY(MAX) NULL
    , c297 VARBINARY(MAX) NULL
    , c298 VARBINARY(MAX) NULL
    , c299 VARBINARY(MAX) NULL
    , c300 VARBINARY(MAX) NULL
    , c301 VARBINARY(MAX) NULL
    , c302 VARBINARY(MAX) NULL
    , c303 VARBINARY(MAX) NULL
    , c304 VARBINARY(MAX) NULL
    , c305 VARBINARY(MAX) NULL
    , c306 VARBINARY(MAX) NULL
    , c307 VARBINARY(MAX) NULL
    , c308 VARBINARY(MAX) NULL
    , c309 VARBINARY(MAX) NULL
    , c310 VARBINARY(MAX) NULL
    , c311 VARBINARY(MAX) NULL
    , c312 VARBINARY(MAX) NULL
    , c313 VARBINARY(MAX) NULL
    , c314 VARBINARY(MAX) NULL
    , c315 VARBINARY(MAX) NULL
    , c316 VARBINARY(MAX) NULL
    , c317 VARBINARY(MAX) NULL
    , c318 VARBINARY(MAX) NULL
    , c319 VARBINARY(MAX) NULL
    , c320 VARBINARY(MAX) NULL
    , c321 VARBINARY(MAX) NULL
    , c322 VARBINARY(MAX) NULL
    , c323 VARBINARY(MAX) NULL
);
GO
/* I GET THIS WARNING...

Warning: The table "test" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

*/

-- Create one row
INSERT INTO dbo.test (c1 , c2 , c3 , c4)
    VALUES (NEWID() , NEWID() , GETDATE() , GETDATE());

-- Is it there?
SELECT * FROM dbo.test;
GO

-- How many pages?
DBCC IND (RowSize,test,-1); -- We have the IAM page and a single data page
GO                          -- Note that the row is stored as IN_ROW_DATA at this point.
DBCC TRACEON(3604);
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*
m_freeCnt = 7999
10003400 c203754c 9b36f44b 8dafee3b b32bd7c6
3106ac3a 8ec6854e b68291e6 4ccf7872 b321e100
f2a40000 b321e100 f2a40000 4301f0ff ffffffff
ffffffff ffffffff ffffffff ffffffff ffffffff
ffffffff ffffffff ffffffff ffff07

Tag A:              0x10
Tag B:              0x00
Null Bitmap Offset: 0x0034 = 52 dec.
GUID 1:             c203754c 9b36f44b 8dafee3b b32bd7c6
GUID 2:             3106ac3a 8ec6854e b68291e6 4ccf7872
DATETIME 1:         b321e100 f2a40000
DATETIME 2:         b321e100 f2a40000
Column Count:       0x0143 = 323 dec.
NULL Bitmap:        0xc0fff...

Variable Length Column Count
+ Variable Length Column Offset Array
+ Variable Length Columns: Do not exist at the moment

Total Overhead:
    Tag A:              1B
    Tag B:              1B
    Null Bitmap Offset: 2B
    Column Count:       2B
    NULL Bitmap:        41B
    Page Header:        96B
    Slot Array:         2B  = 145B

Fixed Size of Row:
    (16 + 16 + 8 + 8)B = 48B

Total Used Bytes: 145B + 48B = 193B = 8192B [8K page] - 7999B [m_freeCnt]

*****************************
Note: There are no pointers stored. The row is stored as IN_ROW_DATA at this point.
*****************************
*/

-- Update one row
UPDATE dbo.test
SET  c5  = CONVERT(VARBINARY(MAX), REPLICATE(0x11, 7961));
GO

-- What is m_freeCnt now?
--      m_freeCnt = 34

-- How many pages?
DBCC IND (RowSize,test,-1); -- We have the IAM page and a single data page
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*

m_freeCnt can be made to equal 20 when snapshot isolation is turned on and used,
but it cannot go lower than that, since the remaining bytes are
reserved for future use by the SQL Server team

Variable Length Column Count:         0x0001 (2B)
Variable Length Column Offset Array:  0x157c (2B)
Variable Length Columns:              0x11111111...

*/

-- Update one row
UPDATE dbo.test
SET  c5  = CONVERT(VARBINARY(MAX), REPLICATE(0x11, 7962));
GO

-- How many pages?
DBCC IND (RowSize,test,-1); -- We have 2 IAM pages, one IN_ROW_DATA page, and one LOB_DATA page
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*

Your 24 byte pointer is on page 283 ( the IN_ROW_DATA page)

*/