SQL Server 2012 Standard Edition using more than 64GB of memory, why and how?

The 64GB limit (and 128GB in 2014) applies only to buffer pool. The KB article "Memory configuration and sizing considerations in SQL Server 2012" says:

  Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool).

And as we all know, max server memory also does not control all of SQL Server's memory. The official documentation is in the process of being updated to provide the following guidance on this (this exact wording was provided by Bob Ward):

  The server-level max server memory setting controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (basically any “clerk” as found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

So, it is quite feasible that you will see sqlservr.exe use more than 64GB of memory if there is significant allocation to anything other than the buffer pool, even in Standard Edition (and more than 1GB even in Express Edition).

If you want to stop SQL Server from using more than 64GB of memory, you'll need to set max server memory to something less than 64GB (you may need to experiment here to find the right mix, but understand that the non-buffer pool memory can fluctuate, so you may still see things that venture beyond both the licensing limit and the configured limit).

Or, you can just leave it as is, and be thankful that you can use up to 64GB for buffer pool and plenty else for other things that might otherwise cut into buffer pool usage if the 64GB limit were an enforced hard cap.

You can see how memory is distributed in various ways. Among the memory clerks, you can use:

SELECT [type], mem_MB = pages_kb/1024
 FROM sys.dm_os_memory_clerks
 ORDER BY pages_kb DESC;

In most cases, the top consumer is going to be MEMORYCLERK_SQLBUFFERPOOL, but you should see significant volume in CACHESTORE_* entries as well. You can group it at a higher level using something like:

SELECT [type] = COALESCE(SUBSTRING([type],1,CHARINDEX('_',[type])-1),'-- total'),
  SUM(pages_kb/1024)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((),SUBSTRING([type],1,CHARINDEX('_',[type])-1));

To see how cache/user store memory is distributed:

SELECT [type], name,
 mem_MB = CONVERT(DECIMAL(19,2),pages_kb/1024.0)
 FROM sys.dm_os_memory_cache_counters
 ORDER BY pages_kb DESC;

Currently, on my lowly VM, the query in your question (provided by @Shanky) says that SQL Server is using 2,085 MB of memory (with 2,004 MB locked in memory). However my query above against the memory clerks DMV says that the total memory being used there is only 1,793 MB.

The query in Chris' answer says that the total is 2,054 MB, with a target of 3,072 MB (this is because I have explicitly set max server memory to 3,072 MB).

So where is the extra ~200 MB? Who knows. (I can also see this difference if I compare total server memory to Process > sqlservr > Private Bytes in Performance Monitor.) I could start parsing the output of DBCC MEMORYSTATUS to try and piece it together (and Tim Chapman wrote some PowerShell to make that easier), but is it worth it? Is all memory even reported there? I'm not 100% certain that it is (or that you should use the output at all in a NUMA system), or that any DMV can give a fully accurate picture of all of the memory being used by SQL Server. I also don't believe all the memory clerk types are even documented anywhere - so even if that gave a complete picture, you might still be scratching your head over what the results really mean.

Task Manager, meanwhile, says sqlservr.exe is only using 42 MB. Whatever you do, please stop using Task Manager. For anything, really. I know you have removed this detail from your question in your most recent edit, but I can't stress enough how useless it is for showing SQL Server memory usage.


How can I enable my-huge file on windows server?

You need to stop mysqld, swap in my-huge.ini, and start up mysqld

cd C:\"Program Files\MySQL\MySQL 5.5"
net stop mysql
rename my.ini my.oldini
copy my-huge.ini my.ini
net start mysql

This will only work if you do not have to resize the log files.

If you have to resize ib_logfle0 and ib_logfile1, rename them before startup

cd C:\"Program Files\MySQL\MySQL 5.5"
net stop mysql
rename my.ini my.oldini
copy my-huge.ini my.ini
cd data
rename ib_logfile0 ib_logfile0.bak
rename ib_logfile1 ib_logfile1.bak
net start mysql

Give it a Try !!!


Shrink Transaction Log While Using AlwaysOn Availability Group

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info.

Do not shrink to 160MB. Determine why did the log grow to 121Gb so it does not repeat (you have a suspicion, would be nice to confirm if possible). Size the log to a size appropriate for your operational needs. Log growth is a serious problem, it cannot use instant file initialization and all your database activity will freeze while the log grows and is being 0-initialized. Users and apps hate it when it occurs. If you understand the impact and your users are OK, you can shrink once to a small amount (160MB is probably too small though) and let it grow until it stabilizes.


installing mysql 5.1 alongside mysql 5.5

I'm no expert on Mac OS X, but on Linux, it's relatively easy to have more than one install running side by side.

I follow a procedure very much based on the one here.

Download the source code. Gunzip and untar it. As a sister directory to the unzipped and untarred one, create a directory (I call it "sandbox"). From within sandbox, run "cmake ../", then "make" and then "make package". Then I copy the package up a level, gunzip and untar it and run the ./scripts/mysql_install_db --defaults-file=.

It's the --defaults-file (i.e. my.cnf) that's critical. In there you override the normal defaults - otherwise multiple MySQLs on the same machine will just overwrite each other's settings. I've appended a sample of my own file below. I just put all the extra stuff into the --basedir directory and that normally works a treat.
Things like the socket, pid-file and, of course, the port need to be changed.

You may be able to just run the binary install script with the --defaults-file and get your system up and running without having to compile, but as I said, I'm not that familiar with OS X.

[EDIT]

Forgot to mention that you have to kick off the daemon with --defaults-file (my.cnf) also.

From your base-dir issue the command

./bin/mysqld --defaults-file=<full_path_to_my.cnf>

============== sample --defaults-file =====================

pol@pol-laptop02:~/Downloads/mysql/src/mysql-5.6.19-linux-x86_64$ more my.cnf
[mysqld]
#
# * Basic Settings
#
user        = pol
pid-file    = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysqld.pid
socket      = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysql.sock
port        = 3306
basedir     = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64
datadir     = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/data
tmpdir      = /tmp
pid-file    = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysql.pid
lc-messages-dir = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/share
lc-messages = en_US

general_log     = on
general_log_file= /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/logfile.txt

log_error   = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log
log-error   = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log

slow_query_log_file = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/slow_query.log
slow_query_log  = 0

explicit-defaults-for-timestamp = TRUE

#language   = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/share/english

# bind-address  = 0.0.0.0 - may not need c.f. host...

[client]
port        = 3306
socket      = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysqld.sock
host            = 127.0.0.1
# or maybe localhost... cf. bind-address above...

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
#loose-local-infile =   1

local-infile    = 1

[mysqld_safe]
#user       = linehanp
#socket     = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysql.sock
#err-log        = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log
#pid-file   = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/mysql.pid
#log_error  = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log
#log-error  = /home/pol/Downloads/mysql/src/mysql-5.6.19-linux-x86_64/error.log

pol@pol-laptop02:~/Downloads/mysql/src/mysql-5.6.19-linux-x86_64$


Why BULK INSERT insert data in random order?

The rows from the file are read in order, and added to the table in the same order.

The issue occurs when you read rows from the table. Without an ORDER BY clause on your SELECT, SQL Server is free to return rows from the table in whatever order is convenient.

Details

The question doesn't provide a definition for the table ##AllRows, but it seems certain the table is a heap (a table without a clustered index). SQL Server reads pages from a heap using Index Allocation Map (IAM) structures. This means data tends to be returned in file and page id order within each IAM chain, which will generally not reflect the order in which data was inserted. This is the underlying cause of the behaviour you are seeing.

Solutions

You need a column to indicate the order of rows in the file, then order by that column when you write your query. Unfortunately, SQL Server does not provide a built-in way to add this 'sequence' column during import.

There are a couple of common workarounds:

Pre-process the source file outside SQL Server to add a sequence number to each row. This is the most reliable method.
Assign a sequence number during the import.

The second method carries some risk because there is no documented guarantee that this will work reliably in all circumstances. Nevertheless, people have been using this idea successfully for a long time. The general idea is:

Add an IDENTITY column to the import table.
Create a view over the import table, omitting the IDENTITY column.
BULK INSERT into the view.

This will not work with a global temporary table, because a view cannot reference that type of table. You would need to use a regular table (perhaps in tempdb) instead.

Example 1

I used the following script to successfully import a csv file containing the Complete Works of Shakespeare:

The first step is to create a table in tempdb with an extra IDENTITY column:

USE tempdb;
GO
CREATE TABLE dbo.Test
(
    id integer IDENTITY PRIMARY KEY,
    line nvarchar(4000) NOT NULL
);

Now we create a view over that table, omitting the IDENTITY column:

CREATE VIEW dbo.ImportTest
WITH SCHEMABINDING
AS
SELECT
    T.line
FROM dbo.Test AS T;

Finally, we bulk insert into the view:

BULK INSERT dbo.ImportTest
FROM 'C:\Temp\shakespeare.txt'
WITH
(
    CODEPAGE = '65001',
    DATAFILETYPE = 'char',
    ROWTERMINATOR = '\n'
);

We can now see the first few lines in file order using a SELECT with ORDER BY:

SELECT TOP (20)
    T.id,
    T.line
FROM dbo.Test AS T
ORDER BY
    T.id ASC;

The result shows the text in the right order:

Example 2

It is also possible to use OPENROWSET with a format file. Using the same example csv file, I was able to import the data using the following format file (saved as shakespeare.xml):

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r\n"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="line" xsi:type="SQLNVARCHAR" NULLABLE="NO"/>
 </ROW>
</BCPFORMAT>

and:

INSERT dbo.Test
    WITH (TABLOCK)
    (
        line
    )
SELECT
    ORO.line
FROM OPENROWSET
(
    BULK 'C:\Temp\shakespeare.txt',
    FORMATFILE = 'C:\Temp\shakespeare.xml',
    CODEPAGE = '65001'
) AS ORO;

Notice that this method does not require a view, so you could target a global temporary table. The target table still needs the extra IDENTITY column.


Long running READ queries stuck in "idle in transaction"

idle in transaction means the connection is not doing anything - it's "idle".

The query has finished, if the query was still running the connection would be shown as active.

The code that initiated the query, forgot to end the transaction by calling commit or rollback. If a connections stays in "idle in transaction" for a long time, this is typically a bug in the application.

Make sure your application properly ends every transaction - even "read-only" ones that only run SELECT statements. Or switch your connection to auto-commit in your code.


Reboot of affected shards fixed it.

That's kinda mongo in a nutshell - I ended up submitting a bug and that was their engineer's advice, and it happened to work.


How to back up a small database in SQL Server 2008 R2 Express Edition

To backup up logins, you'd backup the master database. For any jobs etc, it's be msdb

I assume you want to avoid some complexity by not having 96 log backups per day every 15 minutes.

Your approach to of hourly full backups will work, but I'd consider doing hourly or 30 minute differentials with a daily full backups. The combination of full/differential:

+consumes less space
+will give quicker backups
+can have more frequent backups (less average data loss)
-slightly increased restore time
-slightly increased complexity


What steps should I perform to prepare a MDF for distribution as a (mostly) read-only database?

If you want to distribute a copy of your database, why don't you use LocalDB?

Shrinking the data file causes very high fragmentation. Running index maintenance grows the file back out. Why are you shrinking it?

Furthermore, if you're only going to ship the mdf file, why are you using DBCC SHRINKDATABASE? SHRINKDATABASE calls DBCC SHRINKFILE for every file in the database. If you're not shipping the log file, why are you shrinking it at all? Seems like a waste of time.

Delivering just an mdf file is not a supported method for sharing a database. There's always a risk that the file cannot be reattached. Shipping a backup or at the very least the full set of files is the best way to ship a database.


I would change the setting in properties to None and set up a security audit where you have better control on what you want to audit, for how long you want to retain the result and exclude account you do not want to audit.

Create a Server Audit Specification for Failed login only.  You can add other audit action type as you need.

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Audit_Spec_FaileLogin]
FOR SERVER AUDIT [FailedLogin]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

This script will create a new login fail audit. Replace filepath, maxsize, max_files.  To exclude service account replace the value where I have ServiceAccount in the script.

USE [master]
GO

CREATE SERVER AUDIT [FailedLogin]
TO FILE
(   FILEPATH = N'C:\'
    ,MAXSIZE = 64 MB
    ,MAX_FILES = 50
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE

)
WHERE ([server_principal_name]<>'ServiceAccount')
ALTER SERVER AUDIT [FailedLogin] WITH (STATE = ON)
GO

Use this process as documented in below link to read the audit file.

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

Two more resource to learn about security audit.

SQL Server Security Audit Basics by Feodor Georgiev
SQL Server Audit (Database Engine)


SQL Server 2012 Create index with Sort In TempDb On - Gains False?

Option 'Sort in tempdb' valid only while creating an index. And after index has been created it (an option, not index) becomes senseless. Any other operations like inserts, updates and deletes does not apply sorting in tempdb, or even apply, but this has no any relevance to this option value used while creating index


How to restore symmetric key on the new database?

There is no built-in solution in SQL Server. A symmetric key that was created without specifying the source and identity can never be scripted or copied.

That means, in your case you have to decrypt and re-encrypt on the fly while copying the data.

You could also create a new key in the current database (specifying source and identity) and decrypt and new-key-re-encrypt locally. Then you can move the encrypted data and recreate the key in the new location, as you have all the important seed data available for that new key.


How to create a trigger in oracle to prevent overlapping date ranges

Here's a sketch. I get an error from the trigger on db<>fiddle, but you shpould be able to use the idea. First I'll add some constraints on the table, this will simplify things a lot:

CREATE TABLE training
( id_user          NUMBER NOT NULL
, start_training   DATE NOT NULL
, end_training     DATE
,     constraint ak1_training unique (id_user, start_training)
,     constraint c1_training check (start_training <= end_training)
);

I assume id_training is some sort of auto increment number so I left that out since it is not relevant for the idea. Comments:

All attributes except end_training is not null
is_user and start_training is unique
start_training is before or equal to end_training. The constraint is satisfied if end_training is null.

.

CREATE OR REPLACE TRIGGER training_validation
BEFORE INSERT ON training
FOR EACH ROW
BEGIN
    IF EXISTS (
           select 1 from training t
                 where t.id_user = new.user
             and (t.start_training between new.start_training and coalesce(new.end_training, current_date))
                or
                  coalesce(t.start_end, current_date) between new.start_training and coalesce(new.end_training, current_date))
     )

  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Some msg ...' );
  END IF;
END training_validation;

I get the error:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue.

FWIW, I get the same error with the trivial examples I found googling around

Anyhow, the idea is to throw an error if there exists a previous start_training between the new.start_training and new.end_training. Since the new.end_training is typically null, we map that to current_date via coalesce. In the same manner, we check for the previous start_training.

Note that if start_training is not null, we won't have to take possible nulls in consideration. Also since we know that:

start_training <= end_training

we can rely on that as well.


How to find the size, name and other details of Analysis server database?

You could use powershell to connect to the database and list a lot of properties.

Use this script to find out what properties you can get from a database:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
$Server.Databases | Get-Member

Which outputs:

   TypeName: Microsoft.AnalysisServices.Database

Name                        MemberType Definition
----                        ---------- ----------
Disposed                    Event      System.EventHandler Disposed(System.Object, System.EventArgs)
Backup                      Method     void Backup(string file), void Backup(string file, bool allowOverwrite), void Backup(string file, bool allowOverwrite, bool bac...
CanProcess                  Method     bool CanProcess(Microsoft.AnalysisServices.ProcessType processType), bool IProcessable.CanProcess(Microsoft.AnalysisServices.Pr...
Clone                       Method     Microsoft.AnalysisServices.Database Clone(), System.Object ICloneable.Clone()
CopyTo                      Method     Microsoft.AnalysisServices.Database CopyTo(Microsoft.AnalysisServices.Database obj)
CreateBody                  Method     void IMajorObject.CreateBody()
CreateObjRef                Method     System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)
DependsOn                   Method     bool IMajorObject.DependsOn(Microsoft.AnalysisServices.IMajorObject obj)
Detach                      Method     void Detach(), void Detach(string password)
Dispose                     Method     void Dispose(), void IDisposable.Dispose()
Drop                        Method     void Drop(), void Drop(Microsoft.AnalysisServices.DropOptions options), void Drop(Microsoft.AnalysisServices.DropOptions option...
Equals                      Method     bool Equals(System.Object obj)
GetDependents               Method     hashtable GetDependents(hashtable dependents), hashtable IMajorObject.GetDependents(hashtable dependents)
GetHashCode                 Method     int GetHashCode()
GetLifetimeService          Method     System.Object GetLifetimeService()
GetReferences               Method     hashtable GetReferences(hashtable references, bool forMajorChildrenAlso)
GetType                     Method     type GetType()
GetUpdateOverwrites         Method     hashtable GetUpdateOverwrites(bool fullExpansion)
InitializeLifetimeService   Method     System.Object InitializeLifetimeService()
LinkDimension               Method     Microsoft.AnalysisServices.Dimension LinkDimension(Microsoft.AnalysisServices.Dimension source, string dataSourceId), Microsoft...
OnDeserialization           Method     void IDeserializationCallback.OnDeserialization(System.Object sender)
OnDeserializationBegin      Method     void IDeserializationStartCallback.OnDeserializationBegin(System.Object sender)
Process                     Method     void Process(), void Process(Microsoft.AnalysisServices.ProcessType processType), void Process(Microsoft.AnalysisServices.Proce...
Refresh                     Method     void Refresh(), void Refresh(bool full), void Refresh(bool full, Microsoft.AnalysisServices.RefreshType type), void IMajorObjec...
Submit                      Method     void Submit(bool submitPermanently), void Submit()
ToString                    Method     string ToString(), string IFormattable.ToString(string format, System.IFormatProvider formatProvider)
Update                      Method     void Update(), void Update(Microsoft.AnalysisServices.UpdateOptions options), void Update(Microsoft.AnalysisServices.UpdateOpti...
Validate                    Method     bool Validate(Microsoft.AnalysisServices.ValidationErrorCollection errors, bool includeDetailedErrors, Microsoft.AnalysisServic...
WriteRef                    Method     void IMajorObject.WriteRef(System.Xml.XmlWriter writer)
Accounts                    Property   Microsoft.AnalysisServices.AccountCollection Accounts {get;}
AggregationPrefix           Property   string AggregationPrefix {get;set;}
Annotations                 Property   Microsoft.AnalysisServices.AnnotationCollection Annotations {get;}
Assemblies                  Property   Microsoft.AnalysisServices.AssemblyCollection Assemblies {get;}
BaseType                    Property   type BaseType {get;}
Collation                   Property   string Collation {get;set;}
CompatibilityLevel          Property   int CompatibilityLevel {get;set;}
Container                   Property   System.ComponentModel.IContainer Container {get;}
CreatedTimestamp            Property   datetime CreatedTimestamp {get;set;}
Cubes                       Property   Microsoft.AnalysisServices.CubeCollection Cubes {get;}
DatabasePermissions         Property   Microsoft.AnalysisServices.DatabasePermissionCollection DatabasePermissions {get;}
DataSourceImpersonationInfo Property   Microsoft.AnalysisServices.ImpersonationInfo DataSourceImpersonationInfo {get;set;}
DataSources                 Property   Microsoft.AnalysisServices.DataSourceCollection DataSources {get;}
DataSourceViews             Property   Microsoft.AnalysisServices.DataSourceViewCollection DataSourceViews {get;}
DbStorageLocation           Property   string DbStorageLocation {get;set;}
Description                 Property   string Description {get;set;}
Dimensions                  Property   Microsoft.AnalysisServices.DimensionCollection Dimensions {get;}
DirectQueryMode             Property   Microsoft.AnalysisServices.DirectQueryMode DirectQueryMode {get;set;}
DismissedValidationResults  Property   Microsoft.AnalysisServices.DismissedValidationResultCollection DismissedValidationResults {get;}
DismissedValidationRules    Property   Microsoft.AnalysisServices.DismissedValidationRuleCollection DismissedValidationRules {get;}
EstimatedSize               Property   long EstimatedSize {get;set;}
FriendlyPath                Property   string FriendlyPath {get;}
Host                        Property   System.IServiceProvider Host {get;set;}
ID                          Property   string ID {get;set;}
ImagePath                   Property   string ImagePath {get;set;}
ImageUniqueID               Property   string ImageUniqueID {get;set;}
ImageUrl                    Property   string ImageUrl {get;set;}
ImageVersion                Property   string ImageVersion {get;set;}
IsLoaded                    Property   bool IsLoaded {get;}
Language                    Property   int Language {get;set;}
LastProcessed               Property   datetime LastProcessed {get;set;}
LastSchemaUpdate            Property   datetime LastSchemaUpdate {get;set;}
LastUpdate                  Property   datetime LastUpdate {get;set;}
MasterDataSourceID          Property   string MasterDataSourceID {get;set;}
MiningStructures            Property   Microsoft.AnalysisServices.MiningStructureCollection MiningStructures {get;}
ModelType                   Property   Microsoft.AnalysisServices.ModelType ModelType {get;set;}
Name                        Property   string Name {get;set;}
ObjectReference             Property   Microsoft.AnalysisServices.ObjectReference ObjectReference {get;}
OwningCollection            Property   Microsoft.AnalysisServices.ModelComponentCollection OwningCollection {get;set;}
Parent                      Property   Microsoft.AnalysisServices.Server Parent {get;}
ParentDatabase              Property   Microsoft.AnalysisServices.Database ParentDatabase {get;}
ParentServer                Property   Microsoft.AnalysisServices.Server ParentServer {get;}
Path                        Property   string Path {get;}
ProcessingPriority          Property   int ProcessingPriority {get;set;}
ReadWriteMode               Property   Microsoft.AnalysisServices.ReadWriteMode ReadWriteMode {get;set;}
ReturnPassword              Property   bool ReturnPassword {get;set;}
Roles                       Property   Microsoft.AnalysisServices.RoleCollection Roles {get;}
Site                        Property   System.ComponentModel.ISite Site {get;set;}
SiteID                      Property   string SiteID {get;set;}
State                       Property   Microsoft.AnalysisServices.AnalysisState State {get;set;}
StorageEngineUsed           Property   Microsoft.AnalysisServices.StorageEngineUsed StorageEngineUsed {get;set;}
Translations                Property   Microsoft.AnalysisServices.TranslationCollection Translations {get;}
Visible                     Property   bool Visible {get;set;}

You can combine or loop over these properties as much as you want, for example this script should give you the size of a known database:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
$database = $server.Databases.FindByName("AdventureWorksDW2012Multidimensional-EE")
Write-Output("database size Size : {0}MB" -f ($database.EstimatedSize/1024/1024).ToString("#,##0"))

outputs:

database size Size : 24MB

If you wanted the size of all databases on the server you can loop over them too:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
foreach ($database in $server.Databases)
{
Write-Output("{0} size Size : {1}MB" -f $Database.Name, ($database.EstimatedSize/1024/1024).ToString("#,##0"))
}

Outputs:

AdventureWorksDW2012Multidimensional-EE size Size : 24MB
MultidimensionalProject1 size Size : 0MB

Mix and match to your liking to get information on all databases, a single database, cubes inside a database, dimensions inside a cube etc.


I have a client with that has 11000 databases with each database containing 90-100 databases each.

This causes several problems:

It bloats the INFORMATION_SCHEMA badly. Memory takes a hit.

Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?
Apr 21, 2014 : Adding new tables -- memory usage increases
Jun 15, 2011 : How is INFORMATION_SCHEMA implemented in MySQL?

If you ever have to backup a database, it takes 3+ hours for Xtrabackup to read the entire instance's table and column info before it actually starts copying data.
Poor performance in one tenant database can hurt performance for the entire instance
Maintenance for one tenant database can hurt availability for the entire instance

Please see my old post : One Big Database vs. Several Smaller Ones. It compares a multitenant database to a multitenant building. Ask any landlord of a multitenant building and you will get the same sob story.

BACK TO REALITY

Managing a building with three tenants is better that managing a complex with 100+ units (NYCHA).

In terms of databases, you should shard the tenants into several independent clusters so that each DB instance holds a decent amount of data without gorging on RAM. Just keep in mind that the more tenant database you have, the more memory will be consumed. (BUYER BEWARE)


Essentially no.

Briefly looking at the script you have referenced shows that it is making use of DBCC CHECKDB in order to perform the database integrity checks.

This will not lock the database to modifications. CHECKDB works on a consistent view of your database by creating a Database Snapshot.

Locks are of course acquired throughout the integrity checking process but these will not be long term blocking unless you explicity request them to be, for example by using the TABLOCK option for CHECKDB (which can be requested via a parameter in Ola's script).

The best practice advice would be for you to perform the desired maintenance operation in a controlled test environment first. This will give you an idea also of at least how long you can expect CHECKDB to take in Production.

Additional Thoughts: Dependant on your requirements, say for example you only manage a few databases, deploying an automated solution such as Ola's may in fact be overkill when a simple SQL Server Agent Job that calls the CHECKDB command may suffice. If you just need to check a single database then see my blog post Simplify Your DBCC CHECKDB Output for guidance.


Oracle - how to know what internal executable or program performs DML (SELECT,UPDATE,INSERT,DELETE)?

No, it is effectively impossible. The RDBMS is simply too complicated. The block structure has been reversed engineered, but to even begin to do what the RDBMS itself does with those blocks is a gigantic task.

If your goal is to speed up INSERTs, then you can use an /*+APPEND*/ hint in some situations to speed things up, or use Data Pump to do the imports.

Query parsing itself shouldn't really be a major overhead if your application is written properly and uses bind variables.

If you can be more specific about the performance problems you are seeing, and whether you're CPU or IO limited, we'll be able to help you.


You run a server-side trace or Extended Event session to capture login activity.

I also would not have just renamed it in production. If you are referring to the sa account itself, disable it first to verify if anything breaks; and leave that disabled for a day or up to a week. How long you leave it is based on knowledge by the application owner. If they are not confident leave it disabled for at least a month.

If you are renaming just any old login to something else for a purpose, you obviously cannot go with the disable route. In that case you just have to let your trace run for a period of time to capture the activity.

One other option you might try is to just put in a server trigger that just writes an audit of login activity. There are a few examples of this on the web.


If an account has REQUIRE SUBJECT, does it still need a password?

When a user account has REQUIRE SUBJECT, it simply places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject subject. If the client presents a certificate that is valid but has a different subject, the server rejects the connection.

Note that, in the MySQL Documentation, a sample user was created with both:

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/emailAddress=tonu@example.com';

PCI and HIPAA would shriek "INSECURE" if a root user had no password, SSL or no SSL. This would be the case even more so with a Replication User.

Therefore, what is the practical value? : Having a password would provide another level security.


pg_stat_activity shows lots of easy queries running for minutes

Your problem are the "idle in transaction" sessions.  You should fix your application to avoid that.  The queries in pg_stat_activity in those cases are the last query run, not the current one (since there isn't a current one).


Remember indices with less that 1000 pages are ignored by default, this can be changed via PageCountLevel parameter but it's based on Microsoft's recommendation according to ola hallegreen's reference. Check this other answer for further details.


How can I move a MySQL database from 5.1 to 5.6?

It's all here. What's wrong with

mysqldump options | mysql localhost_name -u me -pmy_password

(with valid options for your particular setup)? Check here for a sample. I don't know for sure, but Navicat is, in all likelihood doing something similar in the background. This is possibly better.


When does creating a .sql backup with mysqldump become too large?

I can only think of three(3) things that can cause a mysqldump to be too large
PROBLEM #1 : Disabling Extended Inserts
Extended Inserts (--extended-insert) is on by default via the --opt option. If you issue --skip-opt or --skip-extended-insert, every INSERT command will quickly become hundreds or every thousands. Such a mysqldump can still be loaded but takes longer because of the absence of batch inserts.
PROBLEM #2 : BLOB Data
When dumping a table with BLOB data, there is a small possibility that the dump may not be portable. To make such data portable, some resort to the --hex-blob option. This write the BLOB as text-represented hexadecimal. When using this, you should expect a more bloated mysqldump.
CAVEAT : Don't use --hex-blob and --skip-extended-insert together
PROBLEM #3 : MySQL Packet
Most people take the MySQL Packet for granted. Some mysqldump could time out if you not paying attention. mysqldump's default for max-allowed-packet in 24M. Extended inserts can benefit from a larger packet. It could possibly increase the number of rows per extended INSERT command. The savings in size might be nominal at best. Notwithstanding, the larger the dump, the slightly better the reduction of size might be.
UPDATE 2015-02-13 12:37 EST
If your main concern is just sheer size vs. hardware constraints, you dump like this:
mysqldump -uroot -ppasswword ... | gzip > mybackup.sql.gz

and restore like this
gzip -d < mybackup.sql.gz | mysql -uroot -ppasswword

You could use bzip2 instead of gzip (Pros and cons of bzip vs gzip?)


I've run into this kind of performance issue before, and breaking the delete up into discrete steps of a fixed size is what ended up doing the trick.  It allows SQL Server to commit changes more frequently, which is generally easier on the transaction log.

SELECT 'Begin Delete'; --gives @@ROWCOUNT a value
WHILE @@ROWCOUNT <> 0
    delete top (100000) from [dbo].[tbl1]
    where exists (
        select *
        from [dbo].[tbl2] t
        where [dbo].[tbl1].[col1] = t.[col1]
        and [dbo].[tbl1].[col2] = t.[col2]
        and [dbo].[tbl1].[col3] = t.[col3]
    );

The optimal batch size just depends on your server and what it can handle.  I've found 100,000 to be a safe size for me, and we've not got anything particularly powerful, so you may be able to get away with more.


Slope is a numeric concept.  It does not make sense to have a slope with time unless you already have a unit for it.  Fortunately we have date arithmetic in Oracle that can help you out.  To do this you need to convert to a numeric quantity as you have noted.

Date arithmetic works with addition and subtraction and this can create scoped numeric representations of this for your purposes.  Slope is easy enough (no modifications there since you are subtracting dates first).  The intercept is more interesting.  The question is at what date x = 0 is at.  If you subtract that date from x1, then you will get a numeric result that you can use for your multiplication factor.


The "Database Size" on the properties page is showing the size of the data file.  This will be the size of your data, with some empty space for new data.  You can configure the rate at which this empty space is allocated in the database properties.

It says space available 0.03Mb because you've shrunk the data file down - it will grow by itself to allow for new records - this is normal and intended behaviour.  This "Available Space" is what's left in the current data file, NOT the difference between your current database size and the licensed limit of 10GB.

You can verify the edition that is installed by checking the General page in the Server properties under Product.  In the management studio header it only shows the general version, not the product edition.

When you delete a lot of records, you will end up with free space left in the file.  This will not be automatically shrunk unless you set up a maintenance task for it.


32 bit vs 64 bit system - Informix Client SDK

If a package is using 32-bit components (e.g. OLEDB libraries), you will need to choose the "32-bit runtime" option on the Advanced tab when running the package on a 64-bit server.

(I know this is an old question; just adding an answer for posterity.)


PostgreSQL DISTINCT ON forces a specific order

Why DISTINCT ON is sorting the rows?

Distinct removes "duplicate" rows from the result set.  That's a whole lot easier to do if the items being compared arrive neatly sorted into order.
Check the Explain Plan for this query - it will probably contain extra "Sort" and "Unique" steps.  Guess what those are doing.
"select distinct" is always a Red Flag for me.
It is almost always used poorly, a band-aid to get around a bigger problem and introduces problems of its own.  Remember, the performance of ...
select distinct a, b, c
. . .

... is roughly comparable to that of ...
select a, b, c
. . .
group by a, b, c
order by a, b, c

Ask yourself why [you think] you need to use "distinct".


SQL Server 2008 Express backup and restore to test

Full Backup always constitutes of (.mdf) and (.ldf) files as a whole DB inside Backup.If 200GB (.ldf) is the problem, then as Raz Said, Switching the recovery model to 'SIMPLE'. Follow this; Take full backup if possible before switching [if free disk space is present].
Change the recovery model to 'SIMPLE' and click OK. then again revert to 'FULL' model. Shrink the (.LDF) file now it should be able to see the (.LDF) size reduced to few MBs. Now take Full Backup and move it to 2012 server.


Search for authors whose records are separated by at least X days

A check in the HAVING clause would do the trick:

SELECT author_id, count(*) AS i_count
FROM   images
GROUP  BY author_id
HAVING max(date_added) > min(date_added) + interval '30 days'
ORDER  BY i_count DESC;


Export and import a table without a specific column

Another solution is to use LOAD DATA [LOCAL] INFILE, but override the columns list so the auto-increment value is inserted into a dummy variable:
Here's a demo:
I have a text file I call c.csv:
1       foo     bar
2       baz     bam
3       blee    bloo

I want to insert into mytable, but id's 1,2,3 are already occupied. I want the csv data to be inserted, but given new id's.
mysql> select * from mytable;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | text | text |
|  2 | text | text |
|  3 | text | text |
+----+------+------+

Here's a solution:
mysql> load data local infile 'c.csv' into table mytable (@dummy, col1, col2);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from mytable;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | text | text |
|  2 | text | text |
|  3 | text | text |
|  4 | foo  | bar  |
|  5 | baz  | bam  |
|  6 | blee | bloo |
+----+------+------+

It added the three rows from my csv file, but the id column on each row of input was detoured into the dummy variable, so the rows had to use the default, allocating new auto-increment id's.
P.S.: You need to let go of your desire to have no gaps in the sequence if id's. Gaps are normal. You may delete some rows in the future. You may attempt an insert but it gets rolled back or gets an error. There are also cases where InnoDB generates non-consecutive id values by design (read https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html for details).


Difference between character_maximum_length and character_octet_length

As described in the MSDN documentation CHARACTER_OCTET_LENGTH is the length in bytes, and CHARACTER_MAXIMUM_LENGTH is the length in characters.

For parameters of type char or varchar they will be the same, but for parameters of type nchar or nvarchar they will be different, with OCTET-LENGTH being twice (usually if not always) the CHARACTER_LENGTH.


Find rows in which the column doesn't contain a "space"

I suggest you provide explicitly the characters you want to be considered as "white space" and excluded to a regex:

where name !~ '[ \t\v\b\r\n\u00a0]'

Characters:

\s         white space (space, \r, \n, \t, \v, \f)
' '        space
\t         (horizontal) tab
\v         vertical tab
\b         backspace
\r         carriage return
\n         newline
\f         form feed
\u00a0     non-breaking space
---

See the docs for pattern matching.

In your example, note that \xC2A0 is the UTF-8 representation of Non breaking space (00A0).


for archive_command use an script rather than a command

You can put it wherever you like, so long as the postgres user has permission to execute it, and permission to cd into the directory it's in.

I usually put it in /usr/local/bin unless I'm working on a system that has a versioned repository of scripts, in which case it's in there - something like /opt/mycompany-scripts/postgresql/myarchivescript.sh.

I recommend using a fully qualified path to make its location obvious, e.g.

archive_command = '/usr/local/bin/pg_archive_wal.sh "%p" "%f"'

(calling it "backup script" is misleading; it isn't doing backups at all, it's just archiving WAL, which may be part of a backup or replication process).


Bucardo is log/trigger based as I understand it.  This means it doesn't require that other tables are not accessed.

The simple solution seems to me to create a trigger which inserts rows in these tables to rows in archive tables when they are inserted or, in the alternative, deleted.  This way everything works properly on Bucardo's side but you retain a copy of the full data elsewhere.

Actually a better option might be to move to an unreplicated partition on delete.  That way you still get a consistent view of the data as if the delete never took place.

Additionally if you have trouble making Bucardo work, Slony would be an option.


Having researched and tested out a few ideas I have now found a solution.

1) Access or Excel and utilise VBA to Save the CSV files to the FTP of where your website is.
2) After the SaveAs VBA statement I then had another line of code that fires open a web browser which loads a PHP page and executes a INSERT with LOAD DATA LOCAL INFILE...

Even with a 50mb CSV file the saving process over BT Infinity only takes a minute or so and the PHP runs in seconds.

I'm no natural born coder, I find solutions to what I need to achieve and here's what I have working...

Here's the PHP (ensure MyTable.csv is in the same folder as the PHP file else modify below):

<?php

$sql1 = "TRUNCATE TABLE MyTable";

$sql2 = "LOAD DATA LOCAL INFILE 'MyTable.csv'
INTO TABLE MyTable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'
(@date_time_variable, field1, field2, field3, field4)
SET datestamp = STR_TO_DATE(@date_time_variable, '%m/%d/%Y %H:%i');";

$con=mysqli_connect("mysql_server_address","mysql_username","mysql_password","mysql_table");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result1 = mysqli_query($con, $sql1);
$result2 = mysqli_query($con, $sql2);

if (mysqli_affected_rows($con) == 1) {
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysqli_error($con);
};

echo $message;

mysqli_close($con);

?>

The VBA I used to save to FTP:

ThisWorkbook.SaveAs "ftp://ftp_username:ftp_password@ftp_site_address/MyTable.csv"

Thanks for the responses to the original question to those that tried to help but as you can see I needed a solution that required no extra input from the user, one button does all.

The only thing you will need to ensure is that the MySQL server you are scripting to allows the use of LOAD DATA LOCAL INFILE as some restrict this on shared servers. Also take note that you'll have to constantly have FTP access enabled or enabled from your IP Address as many hosts like this locked down these days.

Hope this helps someone given the fact it wasn't easily answered.


Unless a NOEXPAND hint is used, SQL Server always expands the view reference to the underlying stored query before optimization begins.

It may later chose to match part(s) of, or the whole plan, back to one or more indexed views later on in the optimization process.

There are two common reasons that the original expansion isn't reversed later:

The plan tree has been changed by prior optimizer activity in such a way that the indexed view no longer matches (or at least, can no longer be matched by the tools available to the optimizer).
The optimizer finds a good enough (low estimated cost) plan before it goes looking for indexed view substitutes.

There are other reasons to prefer NOEXPAND hints. For example, statistics are only automatically created on indexed views when that hint is present. See my article Another Reason to Use NOEXPAND hints in Enterprise Edition for more.


How to implement database schema for tracking the status of a purchase?

I had a document approval and publishing module that tracked reports through their status from writing, approval, revisions, and finally publish. The way we tracked it was to have a table with the document id, current status, and a timestamp. Any time the status would change it would update that document's record with the datetime it happened. The only thing to be aware of was that we did not store historical information, so when a report was published we couldn't look back to see when it was originally written and approved by the supervisor.


T-SQL Optimizing a Join on TOP Value from another table

You may want to consider partitioning instead of a scalar query.

So something like

insert into New_Table
    select
        [fst].Account_ID,
        [sst].Code
    from
        First_Source_Table as [fst]
            inner join (select
                            row_number()    over(
                                partition by Account_ID
                                order by Account_ID ) as [topN],
                            Account_ID,
                            Code
                        from
                            Second_Source_Table) as [sst]
            on     ( [sst].Account_ID = [fst].Account_ID )
    where
        ( [topN] = 1 ) --This is your topN query


Should I create seperate referenced table or go with serialized data in a column?

No.  InnoDB does something like that for you.  If a table has any bulky TEXT or BLOB columns, those columns are stored "off-record".  This means that they go in a separate block(s) on disk.  Meanwhile, if you read only other columns, you are not 'wasting' time fetching the bulky columns.

Corollary:  Do not use SELECT * ...; instead specify the columns you really need.

(History:  Back when ENGINE=MyISAM was the typical table type, your idea was a good one.)

A tip:  Consider COMPRESSing and UNCOMPRESSing the JSON column in the client.  This may shrink your 7GB to 2-3GB, and speed up queries that do need to look at the JSON (because I/O costs more that CPU).


Renaming a column in a PostgreSQL database table

You could try it yourself as well, but no: renaming a column won't break your foreign keys.

Try this:

CREATE TABLE a (a_id serial PRIMARY KEY);

CREATE TABLE b (a_id integer REFERENCES a (a_id));

ALTER TABLE a RENAME COLUMN a_id TO id;

and have a look at b's definition.

Imagine the world if it weren't so: every single column renaming (in my experience happening a lot in the first phases of development) would involve modifying constraints here and there.  Wouldn't make much sense, would it?

Doing this in a dump is rather error-prone, I would not do that.


Getting PAGELATCH_UP:tempdb:1(PFS) with so many waiting queue

Kendra Little writes in How to Tell if You Need More Tempdb Files:

First of all, if you just have one tempdb file, consider adding more files as preventive medicine. Don’t go crazy with this. The formula recommended by Microsoft in KB 2154845 to use one tempdb data file for each logical processor up to 8 processors is a good place to start.

and:

Remember: this only applies to tempdb data files. You only need one tempdb log file.
Also remember that the tempdb data files will be used proportionally with their size. This means that you should keep the files equally sized.

Read the full article to learn more.


Do I add read committed after SET TRANSACTION ISOLATION LEVEL SERIALIZABLE?

The command is TRANSACTION ISOLATION LEVEL, not SERVER ISOLATION LEVEL. It only changes the isolation level for the transaction scope.


How to export dump of some schemas exclude some tables

Afaik, that syntax never worked like that, I am surprised EM generated this (well, not really...)

grant dba to bp1 identified by bp1;
grant dba to bp2 identified by bp2;
create table bp1.t1 as select * from dba_users;
create table bp1.t2 as select * from dba_users;
create table bp2.t1 as select * from dba_users;
create table bp2.t2 as select * from dba_users;

$ cat exp1.par
schemas=BP1,BP2
exclude=table:" in ('"BP1"."T1"', '"BP2"."T2"')"

This does not work as expected, because the database compares the given strings to table_name and that does not include the owner:

$ expdp \'/ as sysdba\' parfile=exp1.par
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP1"."T1"                                  19.07 KB      27 rows
. . exported "BP1"."T2"                                  19.07 KB      27 rows
. . exported "BP2"."T1"                                  19.07 KB      27 rows
. . exported "BP2"."T2"                                  19.07 KB      27 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************

If you do this:

$ cat exp2.par
schemas=BP1,BP2
exclude=table:" in ('T1', 'T2')"

This excludes the tables, but from all schemas:

$ expdp \'/ as sysdba\' parfile=exp2.par
...
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************

What you can do is, specify the QUERY parameter with a condition that evaluates to false for the tables to be skipped. This will still export the table, but without any data (0 rows).

$ cat exp3.par
schemas=BP1,BP2
query=BP1.T1:"WHERE 1=0"
query=BP2.T2:"WHERE 1=0"

$ expdp \'/ as sysdba\' parfile=exp3.par
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP1"."T1"                                  14.99 KB       0 rows
. . exported "BP1"."T2"                                  19.07 KB      27 rows
. . exported "BP2"."T1"                                  19.07 KB      27 rows
. . exported "BP2"."T2"                                  14.99 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************


We have all been there. It sounds like you don't have a specific question, but just need some help getting started. You need to learn about the sys tables available in SQL Server and the DMV's. They are insanely useful.

Ones that will be helpful in your case:

sys.procedures - gets you a list of your stored procedures
sys.object - gets a list of all objects
sys.dm_exec_procedure_stats - gives stats of your stored procedures, how many times they executed, when the last execute was, etc.
sys.dm_sql_referenced_entities - returns entities that are referenced by the object you provide (pass a stored procedure name and it will return all the tables and views it hits)
sys.dm_sql_referencing_entities - returns entities that refer to the object you pass (pass a table name and it will return all the stored procedures that hit that table)


You can use RMAN for this.
There is some documentation I suggest you read in order to accomplish this easy way:

http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm


Retrieve data from two tables where table 2 has multiple entries for one value

Provided customer_users has ID and the order needed is customers.customerid , customer_users.ID you may ask sql server to show NULL in all but first row in a partition. Adjust ordering as needed.

SELECT '' AS campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, *,
  [show CustomerId]=CASE ROW_NUMBER() OVER (PARTITION BY C.customerid ORDER BY CU.ID) WHEN 1 THEN C.customerid END
FROM customers C
LEFT OUTER JOIN customer_users CU ON C.customerid = CU.customerid
WHERE C.customerid > 0
ORDER BY C.customerid , CU.ID

Getting only one user is a bit different task. First option is OUTER APPLY to get this only user

SELECT '' as campaigndaysremaining, *
FROM customers C
OUTER APPLY (
  SELECT TOP(1) cu.userid, cu.request, cu.regionid, cu.primaryrep, cu.dateadded ,
     COUNT(*) OVER () AS totalrecordcount
  FROM customer_users CU
  WHERE C.customerid = CU.customerid
  ORDER BY cu.userid -- change ordering as needed
) CU
WHERE C.customerid > 0 AND (C.company like '%blue%' OR c.altcompanyname like '%blue%') AND status = 0
ORDER BY C.company ASC


Create a trigger that compares a field, with a field in other table

You can't create triggers on system objects. So going down the path you're thinking isn't possible.

Your best bet to increase fault tolerance is to move your Witness database to it's own server hardware, (not just another instance on the same box even though SQL Server allows you to set it up that way), separate from both the principal and the mirror.

That way you're highly unlikely to have a failure that takes out both your principal AND your mirror database at the same time.


Impoving performance of query by using greater than on a primary key

Assuming that valueStr has a Unique constraint on the second table, so the following query will always yield at most one row:

SELECT last_sno
FROM tableB
WHERE valueStr ='ABC'

You can use it as a subquery (or a derived table) to run an INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO tableB
  (valueStr, total_mins, last_sno)
SELECT
  'ABC', SUM(a.mins), MAX(a.sno)
FROM tableA AS a
WHERE a.sno > COALESCE(
          ( SELECT last_sno
            FROM tableB
            WHERE valueStr ='ABC'
          ), 0)
  AND a.description LIKE 'ABC%'
ON DUPLICATE KEY UPDATE
  total_mins = total_mins + VALUES(total_mins),
  last_sno = VALUES(last_sno) ;

Regarding efficiency, the condition you have

WHERE a.sno > @last_sno
  AND a.description LIKE 'ABC%'

is basically two range conditions, one on sno and one on description (think of description LIKE 'ABC%' the same as description >= 'ABC' AND description < 'ABD') which is rather hard to optimize. The optimizer will have to choose one index to use, it can't use both indexes for this query.

If you could split the description column or add one more with the first (three or more) characters of description that uniquely identify the value_str, making tableA something like this:

tableA

sno   | Project |name     | description      | value_str | mins |
1     | prjA    |nameA    |ABC -10% task done| ABC       | 30   |
...
3000  | prjA    |nameB    |ABC -70% task done| ABC       | 70   |

then you could write the conditions as:

WHERE a.sno > @last_sno
  AND a.value_str = 'ABC'

and a composite index on (value_str, sno) would make this and similar queries much more efficient than what you have now.


Unable to change root user unix_socket to mysql_native_password

As the manual says:

  The ALTER USER statement was introduced in MariaDB 10.2.0.

Since you're at 10.1, you'll need to reset the password using the old, dangerous method:

update mysql.user
 set authentication_string=password('mypass'),
 plugin='mysql_native_password'
where user='root';


Recreate MSDB for SQL Server Express 2008

You either recreate as above or restore it.

The info above is similar to MSDN, see "Creating a New msdb Database".

Note: if this came from Paul Randal's blog or the SQL Server Storage Engine then you can trust it


Postgres \copy command into single column without delimiter

You cannot use COPY or \copy if there is no character which is certain not to appear in the data. You will either need to pick a character to act as the delimiter, then escape or quote it and the escape/quote character in the input file (perhaps using PROGRAM).  Or, not use COPY or \copy, but rather use INSERT instead.

It isn't clear from your question if your files have newlines as record separators or if every file is one record and internal newlines are literal.  If the latter you do use COPY or \copy, you will have to escape/quote that as well.


Lot to go through here, so I'll take it piece by piece, first off splitting:

  I thought this meant that when a chunk hits 64mb, it splits into two
  equal chunks both of size 32mb. That's what is demonstrated here. Is
  that not correct?

That's not quite how it works.  If you have a 64MB chunk and you manually run a splitFind command, you will get (by default) 2 chunks split at the mid-point.  Auto splitting is done differently though - the details are actually quite involved, but use what I explain as a rule of thumb and you will be close enough.

Each mongos tracks how much data it has seen inserted/updated for each chunk (approximately).  When it sees that ~20% of the maximum chunk size (so 12-13MiB by default) has been written to a particular chunk it will attempt an automatic split of that chunk.  It sends a splitVector command to the primary that owns the chunk asking for it to evaluate the chunk range and return any potential split points.  If the primary replies with valid points, then the mongos will attempt to split on those points.  If there are no valid split points, then the mongos will retry this process when it the updates/writes get to 40%, 60% of the max chunk size.

As you can see, this does not wait for a chunk to reach the max size before splitting, in fact it should happen long before that and with a normally operating cluster you should not see such large chunks in general.

  What's up with this? How can the first 3 shards/replica sets have an
  average size greater than 64mb when that is set to be chunkSize? Rs_2
  is 119mb!

The only thing preventing large chunks from occurring is the auto-split functionality described above.  Your average chunk sizes suggest that something is preventing the chunks from being split.  There are a couple of possible reasons for this, but the most common is that the shard key is not granular enough.

If your chunk ranges get down to a single key value then no further splits are possible and you get "jumbo" chunks.  I would need to see the ranges to be sure, but you can probably manually inspect them easily enough from sh.status(true) but for a more easily digestible version take a look at this Q&A I posted about determining the chunk distribution.

If that is the issue you only really have 2 choices - either live with the jumbo chunks (and possibly increase the max chunk size to allow them to move around - anything over the max will be aborted and tagged as "jumbo" by the mongos), or re-shard the data with a more granular shard key that prevents the creation of single key chunks.

  Rs_2 has 27.53% of the data when it should have 16.6%.

This is a fairly common misconception about the balancer - it does not balance based on the data size, it just balances the number of chunks (which you can see are nicely distributed) - from that perspective a chunk with 0 documents in it counts just the same as one with 250k documents.  Hence, the reason for the imbalance in terms of the data is because of the imbalance in the chunks themselves (some contain a lot more data than others).

  What should I do here? I can manually find chunks that are large and
  split them, but that is a pain. Do I lower my chunkSize?

Lowering the chunk size would cause the mongos to check for split points more frequently, but it won't help if the splits are failing (which your chunk size averages suggest is the case), it will just fail more often.  As a first step I would find the largest chunks (see the Q&A link above) and split those as a priority first.

If you are going to do any manual splitting or moving, I recommend turning off the balancer so that it is not holding the meta data lock and does not kick in as soon as you start splitting.  It's also generally a good idea to do it at a low traffic time because otherwise the auto-splitting I described above could interfere also.

After a quick search I don't have anything generic immediately to hand, but I have in the past seen scripts used to automate this process.  It tends to need to be customized to fit the particular issue (imagine an imbalance due to a monotonic shard key versus an issue with chunk data density for example).


In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

This is not a new problem

Feb 07, 2007 : Upgrading from MySQL 3.23 to 5
Oct 26, 2010 : Upgrading from MySQL 4.1 to 5

On May 11, 2007. someone had "fun" with this upgrading MySQL 4.1 to 5.0.41. Notice his comment (Look for Posted by Phil Collett on May 11 2007 7:11pm in the Post):

  I had some fun upgrading from mysql-4.1.15-win32 (mysql 4.1) to
  mysql-5.0.41-win32. I figured I would write down what I did to save
  people time. The permissions table thing is still tricky. I used the
  no-installer to get the version 5 mysql. Then I just swapped the
  installs as I had done in the past. when I relaunched mysql
  (mysql5/bin/mysqld-nt --console) I kept getting Error 1130 [HYO 000x]
  cannot logon locally errors. So this was the mysql permission tables
  upgrade problem. After attacking the upgrade from a few differnt
  angles I found the way that worked for me. This is detailed below.

  The upgrade took me 15 minutes after I had my plan.

  download mysql 5 no install.
  unzip it.
  export you mysql.db and mysql.user tables using whatever tool you use (make import of old users permissions and table rights) so you get
  something like (I used EMS extract)...

  use mysql; INSERT INTO db (Host, Db, User, Select_priv,
  Insert_priv, Update_priv, Delete_priv, Create_priv,
  Drop_priv, Grant_priv, References_priv, Index_priv,
  Alter_priv, Create_tmp_table_priv, Lock_tables_priv) VALUES
  ('%','Database','Table','Y','Y','Y','Y','N','N','N','N','N','N','N','N'),
  next rec.. next rec ....); COMMIT;
  #

  Data for the db table (LIMIT 0,500)

  INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv,

  Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
  File_priv, Grant_priv, References_priv, Index_priv,
  Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv,
  Lock_tables_priv, Execute_priv, Repl_slave_priv,
  Repl_client_priv, ssl_type, ssl_cipher, x509_issuer,
  x509_subject, max_questions, max_updates, max_connections)
  VALUES
  ('%','parts','old-encrypted-password','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0),
  ...next rec.....; commit;

  save as a text file somewhere.
  4. open a command window and end the mysql service, if you run as service.
  5. go to mysql install folder/bin and unregister whatever mysql daemon you are using c:/mysql/bin/mysqld-nt --remove

  zip up all your old mysql (basedir and DataDir) for  backup and later use.
  find all your old my.cnf and my.ini files that were left over from previous installs and put them in you backup folder with the other
  ziped folders so the new install can't see them.
  place mysql 5's unzipped stuff where ever you want it to run from (mine is c:/mysql5.
  just let iot use the mysql and test databases at first to get mysql5 running.
  Set up your my.ini file for mysql 5 and just leave it in the mysql directory (none in %sysroot% any more I guess.
  start mysql5 in console c:/mysql5/bin/mysqld-nt --console.
  Try to log into the console with a blank root password (since this is a fresh install).
  assuming you got this far, type "SHOW DATABASES;" in mysql. Should only be Mysql and Test.
  Copy you old databases except of course the 4.1 mysql and 4.1 test dbs (if it is still there) to you c:/mysql5/data/ directory.
  check in mysql console if they can be seen by running "Show Databases;" again.
  Hopefully that worked, no import your old db and user permission from the sql file you made earlier.  in mysql console: SOURCE
  "c:/your-file-path.txt"; that should bring in your old user and
  password in the old format.
  check if you import worked by selecting from users (use mysql; select * from user;)
  I was fine at this point besides the old password format, so I ran the Mysql_upgrade.exe file that is mentioned in here a million times,
  but omly worked for me this way.
  I ran mysql_upgrade from a batch file: C:\mysql5\bin\mysql_upgrade.exe --datadir=c:/mysql5/data/
  --basedir=c:/mysql5/ -u root > @pause
  a whole bunch of output about bad table struct, 4.1 style passwords and duplicate columns. when it finsihed I ran it again, and
  only got the dup column warnings which can be ignored. Then I ran it
  again just for fun.
  at this point all the db's are in the right structure, my logins all worked.
  closed my console session running mysql, closed my console window mysql was running in (--mysqld-nt --console) and moved my data
  directory back to which it came (personal choice).
  Checked my cofig file (in mysql base dir ...c:/mysql5/my.ini) using "c/mysql5/mysqld-nt --print-defaults" and they looked fine,
  changed my datadir="x:/path", and re-ran the --print-defaults.
  I then reinstalled the msyql as a service with the my.ini file still only in my basedir (c:/mysql5/bin/mysqld-nt --install), and
  started mysql (net start mysql) or services gui.
  Last thing, make the root user a password, easily crackable of course like a bad word (don't really do this, but do make a password).

  Hope that saves somebody else some time.
  -Phil Citrus Motors

Right now, you say that mysql.user is empty. Ugh !!! This means you will have to run mysql with skip-grant-tables in my.ini for now.

Here is the next things you can do: Find out how many columns are in mysql.user

Run this query

SELECT COUNT(1) FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

If you get 43, the mysql.user table is MySQL 5.6 ready

If you get 42, the mysql.user table is MySQL 5.5 ready

I wrote about this before : Cannot GRANT privileges as root

Try This #1

Simply copy back the mysql.user table from the MySQL 5.5. dataset. That would mean copying

user.frm
user.MYD
user.MYI

from the mysql subfolder of MySQL 5.5 datadir into the mysql subfolder of MySQL 5.5 datadir.

Then, rerun the upgrade and go from there.

Try This #2

If you do not want to touch the mysql_upgrade, then add the user

REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = PASSWORD('whateverpasswordiwant'),
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;

Logout of mysql, remove skip-grant-tables from my.ini. Restart

CAVEAT

Personally, I hate mysql_upgrade. I actually dump the grants as SQL

Here is how to do it for Windows

set MYSQL_CONN=-uroot -prootpassword
mysql %MYSQL_CONN% -ANe"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" > GetGrants.sql
mysql %MYSQL_CONN% -AN < GetGrants.sql > MySQLUserGrants.sql

then in a newer installation. I execute the MySQLUserGrants.sql and all necessary columns are populated using SQL GRANTs only.

I actually wrote about this technique before

Jul 26, 2011 : Restoring an old backup to latest MySQL release
Sep 23, 2011 : Anyone have issues with a MySQL 5.1 to 5.5 in-place Upgrade in Production?
Dec 24, 2012 : Backup and restore "mysql" database
Mar 24, 2013 : MySQL export user with semi-colon ";" on the end


Partial SQL Server database synchronization

If you want a real-time sync you can use replication and select only that table as an article. If you don't need real-time sync and let's say you only need to sync the table once a day, you can create a job scheduled as you need and this job will execute an SSIS package that will move the data from one table to the other.


Why is my log file so massive? 22gb. I am running log backups

You are confusing allocated space with used space.  After running the backup use this query to see the difference between allocated and used space.

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

You can use the GUI to shrink the log file by changing the 'Initial size'

If you are having troubles shrinking the log, even when it looks mostly empty see my post here


Is it safe to copy the binlogs from a running mysql server

As long as you have done flush logs and you have taken the logfilename just before flush logs say it should be fine.

Example current binlog file is bin-log00333 and after flush logs it is bin-log00334. You may scp the logs from bin-log00001 to bin-log00333 and don't take bin-log00334.


Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

The clustered index "is" the table.  Rebuilding the index will require enough space to duplicate each existing non-deleted row, page-by-page.  Since you don't have Enterprise Edition, you do not have the luxury of rebuilding the index online.  This means the table will not be accessible during the rebuild operation.

You'd likely be better off rebuilding by creating a duplicate empty table on a different drive that has plenty of free space with room for foreseeable growth, moving data row-by-row in key order, then renaming the old and new tables.  For instance if the original table is Table1 and the new copy is Table2, rename Table1 as Table1-old then rename Table2 as Table1.


Duplicate Same Table for Multiple locations

You are talking about less than 5 queries per second.  A well maintained database can handle 100 qps or more.

A poorly handled db will melt down even with 5 qps.

It is rarely a good idea to split a table artificially.

It is usually a good idea to normalize repeated data.  But don't normalize 'continuous' data, such as numbers, dates, floats, etc.

What will the SELECTs look like?  And SHOW CREATE TABLE.  Then we can discuss what indexes to have, and whether you have under/over-normalized, etc.


A slight variation of Vérace very detailed answer. The idea is to enumerate the rows per id, and per id plus, if the condition holds. I named the difference grp. If grp changes it means that there is a change to whether the condition holds. I.e. min and max grouped by grp will apply to intervals where condition either holds or fails.

I used a having clause for the duration condition.

select id, min(the_time), max(the_time) -- min and max per interval
from (
    select id, the_value, the_time
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time)
         - row_number() over (partition by id
                                 , case when the_value < 10
                                        then 1
                                        else 0
                                   end
                              order by the_time) as grp
    from tab
) as x
where the_value < 10 -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4; -- duration

The added sample data results in:

1   3   9
1   11  15

It is possible to determine the condition in one place by nesting the query, but I'm not sure whether that is useful or not:

select id, MIN(the_time) AS min, MAX(the_time) AS max -- min and max per interval
from (
    select id, the_value, the_time, condition
         -- grp is an id for each interval
         , row_number() over (partition by id order by the_time)
         - row_number() over (partition by id, condition
                              order by the_time) as grp
   from (
       select id, the_value, the_time, the_value < 10 as condition
       from tab
   ) as a
) as x
where condition -- intervals where condition holds
group by id, grp
having max(the_time) - min(the_time) >= 4 -- duration
ORDER BY id, min;


Copying MySQL table to another table with no missing new changes

The way you provide is a very simple and basic level's solution to create a backup copy of the table. Once copy done and someone made a change into the main table backup won't be updated with original tables so you have to do it again.

As far I understand you want to keep backup of your whole database which should be up to date with all changes being done till now.

To make this you can create a master-slave replica of the database, which will work as a master-slave database. Changes being done in the master will automatically reflect in the slave database.

Thank you.


You should modify the registerallprovidersip setting in conjunction with changing the time to live (TTL) for the records.

Without modifying the default TTL, the registration lives too long. Sadly there is still a delay but dramatically reducing the TTL helps.

Ideally you should see what you can do to get the client connections to specify multi subnet failover and not have to do this workaround. But with the TTL being lowered, you should be fine, I've done that with several clients and it all works out alright. It still won't instantaneous.

You can see more about these settings working in conjunction with each other here.


SQL Server CLR method failing due to TLS 1.0 shutdown

If you are still getting that error then it is possible that the requirement for the connection is actually TLS 1.2 and not 1.1, and 1.2 does not become the default until .NET Framework version 4.6. So, you can either try to compile with a target framework version of 4.6, or you can force the TLS version to use 1.2 (or support both 1.1 and 1.2) by setting the SecurityProtocol property of the ServicePointManager class. There are enum values for:

SecurityProtocolType.Tls12
SecurityProtocolType.Tls11

However, depending on which version of the .NET Framework you are using, those values might not yet have been added to the enum. But that won't matter since you can just specify the numerical value that the enum value translates to:

(SecurityProtocolType)3072 for TLS 1.2
(SecurityProtocolType)768 for TLS 1.1

It appears that there might also be a registry setting to force the TLS version if your code is not overriding via setting ServicePointManager.SecurityProtocol.

I think for my SQL# project I used a static class constructor for the main class that contains the SQLCLR methods that sets ServicePointManager.SecurityProtocol once upon being loaded. And the enum is a "HasFlags" enum, so you can specify multiple versions, such as:

ServicePointManager.SecurityProtocol =
    SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

or possibly:

ServicePointManager.SecurityProtocol =
    SecurityProtocolType.Tls11 | (SecurityProtocolType)3072;

Please also see:

TLS 1.2 and .NET Support: How to Avoid Connection Errors
Support for TLS System Default Versions included in the .NET Framework 3.5 on Windows Server 2012


Foreign keys

Foreign keys are good to keep the database consistent. The common practice is to name the foreign key starting with the name that it refers to then the name of the column: instead of id_contact use contact_id.

ID column

There are a couple of benefits of serial primary key.

Faster insert speed
Easier to refer to the row if necessary
Some frameworks requires single column PK (for example Django)

Also it's possible to use natural primary key like email address or personal identification number or composite keys. The benefits are:

Lookup on these are faster

Usually insert speed are lower because insert happens out of order and results in more fragmented table.

This really depends on many things and without context it's hard to give recommendation. You need to take into consideration the application using this database, the relationships, insert/update/select rate, etc. Try and see what works best for your case.

Schema

I would start with something like this:

Since it's unlikely that a contact will have more than one birthday splitting it is unnecessary. You can though have a different address table if it's possible to have two or more address for a single contact. The simplest solution would be:

create table contact (
    id serial NOT NULL,
    name text not null,
    birth date,
    address text,
    PRIMARY KEY (id)
);

create table sales (
    id serial NOT NULL,
    contact_id integer NOT NULL REFERENCES "contact" ("id") DEFERRABLE INITIALLY DEFERRED,
    amount numeric NOT NULL,
    date_paid date NOT NULL,
    PRIMARY KEY (id)
);

Bare in mind that postgresql doesn't create an index automatically for foreign keys (more info: https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys). If you need it you have to create it explicitly:

CREATE INDEX sales_contact_fk_idx ON sales (contact_id);


I am not able to use the sqlplus after intalling 11g on redhat 6.7

This should sort you out. Remember to login & out again once edited, or do a . .bash_profile to source it. Also, make sure it's executable (chmod u+x ~/.bash_profile ).

PATH=$PATH:$HOME/bin
TMP=/tmp
ORACLE_HOSTNAME=prashant.localdomain
ORACLE_UNQNAME=prod
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
ORACLE_SID=prod
PATH=$ORACLE_HOME/bin:$PATH:/usr/sbin:/sbin

export TMP ORACLE_HOSTNAME ORACLE_UNQNAME ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

The most glaring error was that you were nuking PATH by doing PATH=$ORACLE_HOME/bin:PATH; - there was a $ missing.


In Oracle, how to get a list with the names of schemas that a user has read permission?

you can use the following command if you want to see the users that has X privilege for example SELECT ANY TABLE privileges

SELECT * FROM USER_ROLE_PRIVS WHERE GRANTED_ROLE like '%SELECT ANY TABLE%';


Matching dates with conditions and filtering in a table

I think what you want to do is rank the data based upon the smallest difference between the dates, per year - but you only need to consider any rows where date1 is greater that date2. For this, I would personally use a CTE to filter and rank the results before returning it like so:

WITH results
AS(
  SELECT id, date1, date2, value
       , RANK() OVER (PARTITION BY id, date1
                ORDER BY DATEDIFF(DAY, date1, date2) DESC) AS rnk
    FROM tblA
   WHERE date1 >= date2
)
SELECT DISTINCT id, date1, date2, value
  FROM results
 WHERE rnk = 1;

You can find the results on dbfiddle.

For more information see the official documentation of RANK and CTE.


Postgres : prevent altering join condition in plan

Workarounds by changing the query

By changing one part to be a sub query and using OFFSET 0

SELECT *
FROM   company_type AS ct,
       movie_companies AS mc, (
           SELECT t_id
           FROM   info_type AS it,
                  movie_info_idx AS mi_idx,
                  title AS t
           WHERE  it.it_info = 'top 250 rank'
           AND    t.t_id = mi_idx.mii_movie_id
           AND    it.it_id = mi_idx.mii_info_type_id
           AND    t.t_production_year >2010 OFFSET 0
           ) AS var
WHERE  var.t_id = mc.mc_movie_id
AND    ct.ct_kind = 'production companies'
AND    ct.ct_id = mc.mc_company_type_id;

OFFSET 0 won't allow optimizer to pull subquery up in plan tree.
One more workaround is to change query such that joins in query are in
order which we want and setting join_collapse_limit = 1 in PostgreSQL
configuration file which essentially tells optimizer not to change join
order. More about join_collapse_limit here
Note : Explicit JOIN syntax should be used

So new query with explicit join order will look like this

SELECT *
FROM   title AS t
JOIN   movie_info_idx AS mi_idx ON t.t_id = mi_idx.mii_movie_id
JOIN   info_type AS it ON it.it_id = mi_idx.mii_info_type_id
JOIN   movie_companies AS mc ON t.t_id = mc.mc_movie_id
JOIN   company_type AS ct ON ct.ct_id = mc.mc_company_type_id
WHERE  ct.ct_kind = 'production companies'
AND    it.it_info = 'top 250 rank'
AND    mc.mc_note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
AND    (
     mc.mc_note LIKE '%(co-production)%'
  OR mc.mc_note LIKE '%(presents)%');

Corresponding plan

Hash Join  (cost=331522.33..438739.98 rows=1 width=281)
  Hash Cond: (mc.mc_company_type_id = ct.ct_id)
  ->  Hash Join  (cost=331503.40..438720.17 rows=234 width=195)
        Hash Cond: (mc.mc_movie_id = t.t_id)
        ->  Seq Scan on movie_companies mc  (cost=0.00..107038.63 rows=46879 width=34)
              Filter: ((mc_note !~~ '%(as Metro-Goldwyn-Mayer Pictures)%'::text) AND ((mc_note ~~ '%(co-production)%'::text) OR (mc_note ~~ '%(presents)%'::text)))
        ->  Hash  (cost=331290.67..331290.67 rows=17019 width=161)
              ->  Hash Join  (cost=174945.39..331290.67 rows=17019 width=161)
                    Hash Cond: (mi_idx.mii_info_type_id = it.it_id)
                    ->  Hash Join  (cost=174942.96..323906.04 rows=1923203 width=143)
                          Hash Cond: (mi_idx.mii_movie_id = t.t_id)
                          ->  Seq Scan on movie_info_idx mi_idx  (cost=0.00..30292.03 rows=1923203 width=49)
                          ->  Hash  (cost=82545.76..82545.76 rows=3403376 width=94)
                                ->  Seq Scan on title t  (cost=0.00..82545.76 rows=3403376 width=94)
                    ->  Hash  (cost=2.41..2.41 rows=1 width=18)
                          ->  Seq Scan on info_type it  (cost=0.00..2.41 rows=1 width=18)
                                Filter: ((it_info)::text = 'top 250 rank'::text)
  ->  Hash  (cost=18.88..18.88 rows=4 width=86)
        ->  Seq Scan on company_type ct  (cost=0.00..18.88 rows=4 width=86)
              Filter: ((ct_kind)::text = 'production companies'::text)


How to select records from two tables transactionally

You can't have the cake and leave it whole; on one hand, you want to allow maximal concurrency, and on the other hand, you want each process to be 'isolated' and not visible to other processes.
SQL Server offers Service Broker exactly for these queuing / asynchronous processing challenges. Check it out, it is an awesome infrastructure that will most likely work better than any custom user application solution.

Before we had Service Broker, what we used to do for similar challenges was create a 3rd table, call it "Email Send Queue" or something like that.
Let it hold just the IDs of the messages that need to be sent, so every time you enter it into 'messages', also enter the ID to the queue.

When a process needs to pick a message to send, you open an explicit transaction in the REPEATABLE READ isolation level, and hold it open until the sending process completes.
Every new process that needs to pick a job, uses the

SELECT TOP 1 ID FROM EmailSendQueue WITH (READPAST) ORDER BY ID ASC

This way, the reading process will simply skip locked rows, and take the next one that is not yet locked, i.e. - not yet being processed.
Even if you were able (and you can BTW...) hold explicit locks on the original tables, without the READPAST trick, it would be a recipe for blocking hell.
Here is a sample, simplified, untested code to do the trick.

CREATE TABLE [EmailSendQueue]
(
[MessageID] BIGINT NOT NULL
-- Better use a heap here to prevent page level resrouce contention
PRIMARY KEY NONCLUSTERED
REFERENCES [Messages] (ID)
-- In case someone deletes a message that has not been sent yet,
-- and to prevent deleting a message which is in process
ON DELETE CASCADE
ON UPDATE NO ACTION
);

-- New messages into queue when ready to send - allows delayed send logic as well...
INSERT INTO [EmailSendQueue]([MessageID])
VALUES (1), (2), (3);

-- First Process picks up #1
-- Second process will pick up #2, if #1 is still locked, or is completed...
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

DECLARE @MessageID BIGINT;

SELECT TOP (1) @MessageID = [MessageID]
FROM [EmailSendQueue] WITH (READPAST, XLOCK)
ORDER BY [MessageID] ASC;

DECLARE @StartTime DATETIME = GETDATE();

-- Process and send message
EXECUTE [SendMessageProcedure] @MessageID;

-- Send successful
IF @@Error = 0
BEGIN
-- Now the message has been sent, and can go into SentMessages
    INSERT INTO [SentMessages] ([ID], [SendOn], [DeliveredOn])
    VALUES (@MessageID, @StartTime, GETDATE());
    DELETE FROM [EmailSendQueue] WHERE [MessageID] = @MessageID
END;

-- If send unsuccesful, you can do nothing, or introduce some kind of retry logic
-- You can add a 'Retry' column to the queue, and increment it on every attempt
-- A scheduled backgrond process can delete all messages that  exceed a retry threshold
-- This will prevent 'poisoning' the queue with error messages

COMMIT TRANSACTION;


oracle 12c grid standalone Installation failed with crsconfig_params file not found error in windows

Make sure of unzip the grid installer in just one directory. For example, if the files you need to unzip are the following: winx64_1202_grid1of2.zip and winx64_1202_grid2of2.zip, then unzip both of them on the Grid directory and install from there.


Issue migrating oracle package to postgres using ora2pg

Since sms_package.LOG_MESSAGE is a function in PostgreSQL, you have to call it using PERFORM in PL/pgSQL:

PERFORM sms_package.LOG_MESSAGE(...);

But there are other problems in your code. At least the COMMIT in the finction definition will cause an error.

You may have to redesign and rewrite your code.


MYSQL Workbench Column Default/Expression

You are kind of mixing 2 different topics.

DEFAULT clause only accepts constants in MySQL. For example 0, -1, '', or '2000-01-01'. Workbench cannot override this limitation.
Virtual columns may be what you really need (or not). They have an automatic value determined by an SQL expression (no queries), but you cannot change that value. Check this article for more details.


Meaning of precision 1, scale 0 in error message?

That's because to_number() drops anything but data characters from the input string. Data characters are: digits, sign, decimal point, and comma. Noise characters are not insignificant though, as they still count against the length of the format pattern. So:
SELECT to_number('1d4','9D9');

is effectively the same as:
SELECT to_number('14','9D9');

Both fail because the pattern '9D9' only allows a single digit before the comma.
But this valid expression:
SELECT to_number('1X4','99D9');  -- added digit to pattern

is not the same as:
SELECT to_number('1XXX4','99D9');

To much noise truncates the input.
db<>fiddle here
And, yes, D is a format specifier for a decimal point (independent of locale) in the pattern string (2nd function argument), but not in the input string (1st function argument). You found that yourself already.

To address the question in the title - Postgres has a definition in the manual:

The precision of a numeric is the total count of significant
digits in the whole number, that is, the number of digits to both
sides of the decimal point. The scale of a numeric is the count of
decimal digits in the fractional part, to the right of the decimal
point. So the number 23.5141 has a precision of 6 and a scale of 4.
Integers can be considered to have a scale of zero.

That said, I see two Postgres issues here:
1. Missing documentation
It's not documented in the manual that to_number() ignores non-data characters in the input. Nor that those still count against the total length of the pattern.
2. Bug in error message
There seems to be an error in the DETAIL of the error message. The observed:

DETAIL:  A field with precision 1, scale 0 must round to an absolute value less than 10^1.

Should really be:

DETAIL:  A field with precision 2, scale 1 must round to an absolute value less than 10^1.

The effect is the same, so the behavior is coherent. But the information is misleading.


SQL Server 2017 and SSRS on Same Server?

No it's not. You can have both of them running on the same server. Here is a screenshot of SQL Server 2017 Database Engine and Power BI Report Server (newest version of SSRS):


Upgrading SQL Server 2008 failover cluster node to SQL Server 2012 failover cluster node

It's really not THAT different to Service Pack patching whereby you'll need to do passive nodes first, then give cluster resource group ownership to one of the upgraded nodes, in order to finally upgrade the active node.

Map out your upgrade sequence first and test it if you have the luxury of a dev environment.

In place upgrades are supported and the information is already out there from Microsoft.

See here for example.

You'll probably want to perform what's called a rolling update, which can be best explained by the following paragraph from the article:

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

Honestly, the biggest headache I've had with this is with application compatibility and agreeing downtime. The process was simple for me and my nodes...


Error "In a grant or revoke statement, the user attempted to grant a tablespace quota above the upper limit." when trying to increase user quota

It turns out that the error message is misleading. The problem is the quoting around the size specification.

Both of these work:

ALTER USER MY_SCHEMA QUOTA 100M ON "USERS";

and

ALTER USER MY_SCHEMA QUOTA 100 M ON "USERS";


MongoDB doesn't have Data Definition Language. In place of "rows" and "columns" it has "objects" and "attributes". An object is typically not defined or constrained by the storage engine as it would be in a relational database.
The data is not defined by the thing that keeps it but rather by the thing that reads and writes it. For this reason, you can store data about an "Employee" object in the same collection where you also store data about a "Transaction" object. The storage engine won't stop you unless you tell it to.
For MongoDB the Data is Defined by the application, not the database. So you need to go to the application to understand what the equivalent of "DDL" is for the application. Anything you infer from the database is anecdotal and not rigorously constrained.


Don't join tables like that. Using a comma means cross joining tables (cartesian product).

select j.location_id
from companies c
   join jobs j on j.company_id = c.id
and c.name = 'name'

These two queries should produce the same execution plan and therefore the same results but this one is much clearer.