Multi-Tenant Design with Single DB & Single Schema approach using Sql Server

I would go with including Tenant_ID in all tables. Yes, this is denormalized, but with good reason.

In cases like this, it can be critical to ensure that one tenant cannot see another's data. If every query includes the Tenant_ID, that helps ensure this.

Your note on future partitions is another reason (though not critical at this point).

I can think of one other. If one of your tenants required some sort of direct SQL access to the database, you could create views for them for all tables, with WHERE Tenant_ID = 12 (or whatever the appropriate ID is, of course). By granting access to those views, and denying access to the actual tables, you would be able to provide the same security as you do with your application.


Yes, there are tools, like the database engine tuning advisor. It ships with SQL Server and does some pretty nasty things:

It allows you to consider only a very small workload (even a single query). Which will then suggest indexes that may help that small workload only, with no regard whatsoever to the rest of the workload (indexes help speed up some queries, but they can slow down others, especially insert/update/delete), never mind the rest of the business cycle.
It will often recommend redundant indexes that only differ by a trailing column or by an included column.
It really, really, really likes included columns - I think it may be a fetish.

The proper approach to index tuning is to consider your entire workload over a complete business cycle. Sometimes it is better to turn to 3rd party tools than reinvent the wheel. DBSophic makes a free product called Qure Workload Analyzer that I think does a much better job than DTA - but their licensed tool (Qure Workload Optimizer) is absolutely phenomenal at what it does. Last year I blogged about how the tool works when integrated with SQL Sentry).

There are some other options, too, for example missing indexes are reported in the DMVs, and you can also discover which indexes are being written to (so they are taking up space and affecting insert/update/delete operations) but never (or rarely) used by user queries:

Find missing indexes using the DMVs
Find unused indexes using Policy-Based Management
Discovering unused indexes
Deeper insight into unused indexes

But these also need the caveat that the DMVs are only valid since the last SQL Server restart, and that they won't necessarily cover a proper business cycle (and hence may be missing vital information


Access denied when trying to run an agent job

The 'Service\SQLSERVERAGENT' service account needs access to the file system to run that package.

You can also setup a proxy for the SSIS package and run the job using that proxy account.


How does Sybase store datetime information?

The answer below was taken from Sybase official technical documentation available at http://www.sybase.com/detail?id=1048699 (SAP has removed support for many links at sybase.com; however The Way Back Machine seems to have a copy of the page even if it is a bit old).

Sybase Servers support the use of date and time data through the datetime and smalldatetime datatypes (and, with newer server, the date and time datatypes), as well as the getdate(), dateadd(), datediff(), and datepart() functions. The getutcdate() function was also added to some servers to provide the current datetime value in Coordinated Universal Time regardless of the time zone the server is otherwise running under.
The datetime and smalldatetime datatypes, however, do not store time zone information and the products are entirely ignorant of the concepts of time zones and daylight saving time. Sybase Servers only recognize and store the date and time portions of the values provided by the operating system, which are based on the time zone configured at the operating system level (typically though the TZ environment variable setting in Unix or the Date/Time function of the Windows Control Panel) for the user who started the product. The calculations behind the dateadd and datediff functions are aware of leap years (using the rule of every 4th year, except for every 100th year, except for every 400th year), but do not include any adjustments for leap seconds or transitions from daylight saving time to regular time.

I would therefore expect that changing the underlying OS timezone will not have any appreciable effect on the data stored in the datetime fields in your database.


SQL Server Import/Export: cannot copy geo columns

It appears Microsoft forgot to include the geometry and geography datatypes in the SQL Server Import and Export Wizard.

There is an excellent explanation and fix here which I will summarize below:

Open the SQL mapping file. In my case I was doing am Export from SQL
Express 2014 to SQL Express 2008R2 using the SQL Client method. So
the file I had to edit is: C:\Program Files\Microsoft SQL
Server\120\DTS\MappingFiles\SqlClientToMSSql10.xml (In some cases
you might have to run Notepad first as administrator and then select
the file otherwise it doesn't allow you to save the changes)
Copy the following to the file, just before the line that reads </dtm:DatatypeMappings>:

    <!-- geography -->
      <dtm:DataTypeMapping >
        <dtm:SourceDataType>
          <dtm:DataTypeName>geography</dtm:DataTypeName>
         </dtm:SourceDataType>
        <dtm:DestinationDataType>
          <dtm:SimpleType>
            <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
          </dtm:SimpleType>
        </dtm:DestinationDataType>
      </dtm:DataTypeMapping>

      <!-- geometry -->
      <dtm:DataTypeMapping >
        <dtm:SourceDataType>
          <dtm:DataTypeName>geometry</dtm:DataTypeName>
        </dtm:SourceDataType>
        <dtm:DestinationDataType>
          <dtm:SimpleType>
            <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
          </dtm:SimpleType>
        </dtm:DestinationDataType>
      </dtm:DataTypeMapping>


TRY .. CATCH sometimes fails to catch PRIMARY KEY VIOLATION

Not sure if this is the issue, but you should use ERROR_NUMBER() not @@error in your catch block.

In any case something like this should work reliably, without PK voliations:

SET nocount ON;
begin transaction
IF NOT EXISTS(SELECT * FROM MyTable with (updlock, holdlock) WHERE ID = @ID)
begin
  INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE())
END
else
begin
  UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged < GETDATE()
end
commit transaction


Optimal way to save Social media posts from multiple platforms

You are asking how to implement inheritance on a relational database, and the 3 methods you mention are basically the 3 most common/basic ways to do that. See https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database for some discussion about them.

There is no "best" way to do it- it will all depend on how you intend to use your schema so it facilites your queries afterwards.


SQL Job to be run earlier on last work day of month

You can write this more tersely (e.g. without any variables at all), but I thought logically breaking it up might be more helpful.

DECLARE @Today DATE = SYSDATETIME();

DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1,
 DATEADD(DAY, 1-DAY(@Today), @Today));

DECLARE @LastWorkDayThisMonth DATE;

SELECT @LastWorkDayThisMonth = MAX(CalendarDate)
  FROM dbo.CalendarView -- guessing on name here
  WHERE WorkDay = 1
  AND CalendarDate >= @Today
  AND CalendarDate < @FirstDayNextMonth;

IF @LastWorkDayThisMonth = @Today
BEGIN
  -- do your last work day of the month stuff
END

(Also, I suggest changing your calendar view to expose DATE and not DATETIME.)


Cube Designer Aggregation Wizard Optimization Zero Percent Every Time

Alrighty. So, I had inherited this cube from the original architect. I ended up adding a couple data dimensions, adding to the several that were already there. The is data with many different kind of dates so the Date and Time dimensions play a lot of roles. Apparently, i reach a tipping point. Today, after a co-worker created a smaller version of the cube using the measure group I was having issues with and was able to create aggregations without difficulty, i tried something i had not before. In the aggregation design wizard, i set the all but the most commonly used dimensions to None. Thus, I was asking the wizard to create fewer aggregations. I was able to create aggregations with solid optimization without any difficulty.


Can you try using subquery? I can better design it if I have the table and sample data created on my DB server. But below is just a conceptual model which you can try on your server first.

SELECT
 UM.UserID, ULT.LogInTime, ULT.LogoutTime,
 (SELECT COUNT(CC.PickedUserKey)
    FROM dbo.CallCenter CC
    WHERE
     CC.PickedUserKey = (SELECT UserKey FROM dbo.UserMaster WHERE UserID = UM.UserID) AND
     CC.PickedTime > ULT.LogInTime AND
     CC.PickedTime < LOGOUTTIME) NoOfCallsAttended
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
 ON ULT.UserID = UM.UserID

Subsequently you can create subqueries for other columns as well.
NoOfJobCardsCreated, AverageCallDuration


The subject is quite broad and this could (actually does) fill a full book even. If you're interested in more details I recommend reading some. I'll try to summarise the baseline.

InnoDB is using pages to store data. Normally it is 16k but is configurable on some MySQL forks (Percona for example). This is the smallest item which is read from or written to disk (except the innodb_logs but that's a different story). 64 consecutive page make an extent (1MB) and extents are grouped in segments. The tablespace contains multiple segments.

Row are stored in B+Tree structure based on the primary key. Secondary keys are also stored in B+Tree having the primary key at the end.

Primary key lookup

InnoDB is doing a tree traversal on the PRIMARY KEY and return the row.

Secondary key lookup

either traversing the secondary key finding the primary key(s) fulfilling the requirements and then the PK traversal.
or using the adaptive_hash_buffer to get the primary key from the conditions and then do the PK traversal (this can be quite efficient: benchmarking secondary key updates in MySQL)

In case of any read if the page is not present in the buffer pool InnoDB fetch it from the disk (and sometimes other to for read ahead) and put it in the buffer pool. It may need to evict some other to have space for it. In case of innodb_flush_neighbors it can flush more.

Then the row or rows will be fetched and returned.

I hope this gave you something to start with.

Innodb_ruby is very useful tool to look into the physical structure of ibd files. Can give you a lot of insight into the internal behaviour.

5.5 doesn't have a feature to show the contents of the buffer pool unfortunately. In Percona Server 5.5 (not in Oracle MySQL) you can dump the contents of the buffer pool, which can be a way to look what's inside.

The best book I think is High Performance MySQL, 3rd Edition

A couple of years ago I wrote a blog post about how to learn to become a DBA. That might contain some useful information for you also.


MySQL: Error while reverse engineering database from a sql file

There are a few things wrong with the DDL Open Office has generated for you:

MySQL doesn't have a VARCHAR_IGNORECASE datatype
The CREATE CACHED TABLE statement isn't valid
TIMESTAMP(0) should be TIMESTAMP

The following should work:

CREATE TABLE Users(
  UserID NUMERIC(15) NOT NULL PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  MobileNumber VARCHAR(12) NOT NULL,
  EmailAddress VARCHAR(50) NOT NULL,
  CompanyName VARCHAR(50),
  City VARCHAR(50),
  StateOrProvince VARCHAR(50),
  CountryOrRegion VARCHAR(50),
  PostalCode INTEGER,
  PassPhraseID NUMERIC(10) NOT NULL,
  Gender CHAR(1) NOT NULL,
  Age NUMERIC(3) NOT NULL,
  MaritalStatus CHAR(10),
  Height NUMERIC(4,1),
  Weight NUMERIC(3),
  Complexion INTEGER,
  PreferredMessageMode VARCHAR(10) NOT NULL,
  isPaid BOOLEAN NOT NULL,
  FBID VARCHAR(50),
  BBM VARCHAR(8),
  PreferredVerticals VARCHAR(100),
  EmergencyNos CHAR(60) NOT NULL,
  DOB DATE,
  PhotoID INTEGER,
  CategoryID NUMERIC(2) NOT NULL,
  isActive BOOLEAN NOT NULL,
  ActivationDate TIMESTAMP
);

CREATE TABLE UserLocation(
  UserID NUMERIC(15) NOT NULL PRIMARY KEY,
  Location NUMERIC(22) NOT NULL,
  Timestamp TIMESTAMP NOT NULL
);

ALTER TABLE UserLocation
ADD CONSTRAINT FK_UserLocation
FOREIGN KEY (UserID) REFERENCES Users(UserID);


Only backup drive and differential backups

No, differential backups cannot be applied on their own and cannot be applied to a database that has been fully brought online, which one from the entire disk backup would be.  You need to restore a full backup first, with norecovery, then the differential.

You would also only apply one differential backup, not several, the last diff backup will contain all of the data changed since the last full backup.


The server has 32 GB Memory and we assigned 22 GB to SQL Server but its using only 1.5 GB

This is because when 32 bit SQL Server is installed on 32 bit Windows Servers it can see maximum Virtual Address Space(VAS) of 2 GB so its memory consumption under default behavior cannot be more than 2 GB no matter how much RAM is present on windows server.

  Do we have to enable\configure any options on Windows before enabling the option on SQL Server?

As I can see you want SQL Server to use more memory and for that you are enabling AWE which is correct but first you have to make sure windows server can see more than 4 G of RAM. Again maximum VAS for 32 bit windows system is 2^32 which is 4 GB so any OS process cannot see more than 4 G of RAM. The good thing is you are having Window server 2008 enterprise and you can enable PAE so that windows server can see upto 64 G memory.

To explicitly enable PAE, use the following BCDEdit /set command to set the pae boot entry option: Take windows team help to make the changes.

bcdedit /set [{ID}] pae ForceEnable

IF DEP is enabled, PAE cannot be disabled. Use the following BCDEdit /set commands to disable both DEP and PAE:

bcdedit /set [{ID}] nx AlwaysOff
bcdedit /set [{ID}] pae ForceDisable

Once PAE is enabled windows server can see more than 4 G and then you can enable AWE so the SQL Server data and index pages can see memory more than 2 GB.

  How much memory can be assigned to SQL Server on the Enterprise Windows (Max)?

For 32 bit, maximum memory like I said is 2GB. But when you enable AWE on windows server which can see more than 4 G, ONLY SQL Server data and index pages can use this extra memory not other caches like proc, plan etc.

  Is there any other thing we should take care of before applying since its a production server?

Just test enabling PAE on UAT, enabling AWE is pretty much straight forward and easy.

I suggest you read

SQL Server memory and troubleshooting
What is difference between VAS and Buffer Pool
Understanding VAS reservation in SQL Server

PS:  Please upgrade to 64 bit ASAP. This will save you lot of hassle moving forward. SQL Server 2016 is now only for 64 bit system so you can see MS is moving away from 32 bit

PPS: SQL Server 2008 is patched to SP2 please patch it to SP4 ASAP.


Actually, it is pretty easy to configure a delayed member of a replica set via the shell:

cfg = rs.conf()
cfg.members[0].priority = 0
cfg.members[0].hidden = true
cfg.members[0].slaveDelay = 3600
rs.reconfig(cfg)

In this example taken from the docs, the first member of replica set will be reconfigured to have a delay of one hour. Note that this delayed member can never become primary (and should not). By setting the hidden attribute to false, you may end up reading from this member by setting the read preference to secondary, but there is no guarantee for it. So you have to connect directly to the delayed member (remember to set the slaveOk option), which obviously lacks failover capacity.

Waiting for the data to be acknowledged by one secondary and then send it to the next would be very inefficient, and therefor I doubt that it is possible. Think of how long it would take when you had a replication factor of 15, multiple data centers involved and a write concern of majority...


SQL SERVER ERRORLOG FILE UTILIZING THE FREE SPACE

You can close the existing log file by running
sp_cycle_errorlog
Which will create a new empty file
After that just delete the old log files
You should probably look what errors are being logged & try and fix those so the new error log doesn’t fill up so fast
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-errorlog-transact-sql?view=sql-server-ver15


Unknown _TransactionIndex table in Azure SQL Database

It's an internal table that is used during the import of a bacpac. If all is well it should be gone when the import is done. If it remains in the database the import wasn't completed successfully.


Grouping count by interval of 15 minutes

If you can deal with not counting zeros, you can avoid a calendar table

SELECT
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', TransactionDate) / 15)*15, '20000101'),
    count(*)
FROM
    Transactions
GROUP BY
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', TransactionDate) / 15)*15, '20000101')

gives

2017-07-10 11:30:00.000 2
2017-07-10 11:45:00.000 1
2017-07-10 12:00:00.000 5
2017-07-10 12:15:00.000 1


Reason: Failed to open the explicitly specified database.
...
The Database which the login is trying to access is a temporary Database
and is in Single user mode for last 15 days failing with above error.

The error clearly says that the database specified in the connection string cannot be accessed.
So you have these possibilities:

change the connection string with master database
make your database accessible, put it in multi_user


Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?

Download and install an Oracle 11 database client on your SQL Server 2008 machine.

Set up a linked server using an Oracle OLE DB provider (OraOLEDB.Oracle).

Make sure "Allow InProcess" is enabled in Provider options.

The Oracle 11 OLE DB client supports CLOBs.


Index Hoarder is telling you that historically this index is not being used. Therefore, the maintenance of the index is a cost in CPU, I/O, and storage, but you are not getting any performance benefit from those indexes.

A general policy of considering Foreign Keys for indexes is good general plan, but an index that is not used should most likely be dropped.

Here is a link on examining the indexes yourself:
http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/

A snippet of code from that post shows some query details:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       USER_SEEKS,
       USER_SCANS,
       USER_LOOKUPS,
       USER_UPDATES
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()

Consider dropping the unused indexes. If you have a yearly job that would really use the index, consider creating it just for that job.

Feature-Phobic Indexes is suggesting that a filtered index may be more valuable to you than a standard index.  A filtered index would only cover the rows that meet your search criteria.

This is a little more speculative, but the advice is to try some filtered indexes.  If they do  not work for you, you can drop them again.


Change Storage Engine on Referenced Tables

You need to disable foreign key checks and unique checks

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
for D in $DATABASES
do
   TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"')
   for T in $TABLES
   do
      echo "ALTERING TABLE $T"
      SQL="SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;"
      SQL="${SQL} ALTER TABLE ${T} ENGINE=MYISAM"
      mysql -u XXXXX -pXXXXX -D${D} -e "${SQL}"
   done
done

Here is a more concise way: Reload data with InnoDB Disabled

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
mysqldump -u XXXXX -pXXXXX --single-transaction --add-drop-database -B ${DATABASES} > AllMyData.sql
mysql -u XXXXX -pXXXXX -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql restart --skip-innodb
mysql -u XXXXX -pXXXXX < AllMyData.sql


Getting slaves of a master-master setup stopped in sync

All of these approaches show that you gave these things a lot of thought.

You are worried about any pending changes when running FLUSH TABLES WITH READ LOCK;.

Think about this: When you issue FLUSH TABLES WITH READ LOCK;, how is replication affected? Recall that replication has two threads

IO Thread
SQL Thread

The IO Thread is responsible for communication between Master and Slave. It downloads binary log entries from the Master and stores them in the Slave's relay logs.

The SQL Thread is responsible for

reading the next SQL statement from the Slave's relay logs and processing them
maintain are temp tables created within the session of the SQL Thread

When you run FLUSH TABLES WITH READ LOCK;, only the SQL Thread gets affected because it needs to connect to tables. The IO Thread can still collect binary log entries from the Master and store them in the Slave's relay logs. Any replication lag will simply be caught off guard as is. In light of this, STOP SLAVE; should be faster than FLUSH TABLES WITH READ LOCK;. If you are concerned about pending changes, then use STOP SLAVE SQL_THREAD; instead of STOP SLAVE;. That way, whatever is last executed on each Master should be checked.

When you do SHOW SLAVE STATUS\G look for two lines

Relay_Master_Log_File (line 10)
Exec_Master_Log_Pos (line 22)

This tells you what was the SQL statement downloaded to the Slave that was last executed.

Knowing this, you could try the following

Step 01 : On M1 and M2, STOP SLAVE SQL_THREAD;
Step 02 : Run SHOW MASTER STATUS; on M1 and M2
Step 03 : Run SHOW SLAVE STATUS\G on M1 and M2
Step 04 : Evaluate this condition

Does M1's File = M2's Relay_Master_Log_File ?
Does M2's File = M1's Relay_Master_Log_File ?
Does M1's Position = M2's Exec_Master_Log_Pos ?
Does M2's Position = M1's Exec_Master_Log_Pos ?

Step 05 : If any one of the four conditions in Step 04 is not met

On M1 and M2, START SLAVE SQL_THREAD;
SELECT SLEEP(30);
Go Back to Step 01

If you get past Step 05 with all four conditions in Step 04, M1 and M2 are in sync.

Once M1 and M2 are frozen simultaneously

S1 should match M1

Wait until S1's Seconds_Behind_Master = 0
M1's File = S1's Relay_Master_Log_File
M1's Position = S1's Exec_Master_Log_Pos

S2 should match M2

Wait until S2's Seconds_Behind_Master = 0
M2's File = S2's Relay_Master_Log_File
M2's Position = S2's Exec_Master_Log_Pos

No need to run STOP SLAVE; on S1 or S2

I hope this helps

UPDATE 2012-05-11 17:30 EDT

Once S1 and S2 match up with their respective Master, you could STOP SLAVE; if you want to. Since M1 and M2 are frozen, no other changes can reach S1 or S2. Thus, STOP SLAVE; is not a requirement but you do so anyway.

UPDATE 2012-05-11 21:29 EDT

Your Comment

  M1/M2 are frozen from receiving updates from one another but not from receiving a legit update from an external client/application, no?

Are you still accepting incoming feeds? You did say in the original question

  As I try thinking this out I keep running into gotchas that won't quite work out.

That would certainly be one gotcha. Therefore, discontinue incoming feeds.

Since you want to do FLUSH TABLES WITH READ LOCK; to M1 and M2, I have one recommendation. Please set this one hour before syncing everything:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This will clear all dirty pages from the InnoDB Buffer Pool. That way, the time for FLUSH TABLES WITH READ LOCK; is as fast as possible. When all syncing is done, set it back to 90 (if running MySQL 5.5) or 75 (otherwise).

Your Comment

  I could see how M1/M2 were locked if they flushed w/ read lock but it seemed your steps were not including such a step

I was not including such a step because I was under the impression you would disable outside feeds.


Problems with a job that rebuilds indexes

You should rebuild index which it deadlocked on, and rebuild any other indexes which not did not occur afterwards- if whole job failed and stopped.
Hopefully you are rebuilding indexes, during less busy times, say nighttime or after hours. If you have no choice, and have a continuously busy system,
Please Rebuild with Online option,

ALTER INDEX ALL ON [dbo].[Test] REBUILD WITH (ONLINE = ON);

Additionally, I would only rebuild indexes which require it.  Hallengren website has an option to reorganize indexes say with 5% Fragmentation, and rebuild for 30% Fragmentation. I don't think Windows SQL Server Maintenance has this option yet.

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Check when the deadlock occurred, and how the long the total job duration lasted. If those points meet, then the job stopped at the deadlock. If the job duration lasted longer than deadlock, more indexes were rebuilt.


High Level SAN Migration Strategy. Is this a good idea?

If you are using vnx you should be able to use replication manager or mirrorview both are emc products.
If your new SAN is also from emc you should have emc support and use them to get recommendations for this type of operation.


Why does this simple select statement require quotes?

You created your column with double quotes, so now Oracle requires you to always refer to this column using identifiers and respecting the case. If you remove the double quotes, Oracle always transforms the name to upper case, so without them it is case insensitive.

This will work if you created the column without identifiers:

where rulE_id
where RULE_ID

Also when creating a table, quotes are not required, unless you use reserved words.


How to execute multiple postgres script from command prompt

If you don't need to provide a password, it's easy:

for FILE in `ls -1 *.sql`
do
  psql -d DBName -p 5444 -U schemaname -f $FILE  2>$FILE.err.log
done

If you need to provide a password, concatenate all of the files together, then run psql:

for FILE in `ls -1 *.sql`
do
  cat $FILE >> /tmp/allfiles.sql
done

psql -d DBName -p 5444 -U schemaname -f /tmp/allfiles.sql 2>allfiles.err.log

rm /tmp/allfiles.sql


How do I choose primary keys for star schema BI data warehouse?

Almost always surrogate keys (your "Auto Incrementing primary key") are best in data warehouses. I have seen very few exceptions where this is not the case (but some do exist) - yours does not seem to be exceptional. To answer why would be repeating stuff you can find all around the web, by the giants in the field, for instance: http://www.kimballgroup.com/1998/05/surrogate-keys/


How can I have SQL Server email me the error details when a job fails?

Something you might do that is just a thought, throwing ideas out...

Create a single job that periodically checks the job table in msdb to see if any jobs show as failed, that can be done with a good T-SQL query. Then you could go into the sysjobsteps table and see if an output log is set for the job. Have a stored procedure send an email attaching that file to it. You would be able to see exactly what the job did from beginning to failure without having to touch the server.

Then could also have PowerShell script check the event log for errors. It allows you to filter down a pretty good bit to get exactly what message types you are looking for. You could set that up as a SQL Agent job to run periodically. Then in the PowerShell script use the email cmdlet to send the message if it finds one.

Far fetched ideas here, just some I thought about.


existing foreign key that references fields with different datatype (char(8) and varchar(8))

Digging into long forgotten boxes I found both nt4 and SQL-Server 6.5 installation media and I can confirm that a foreign key referencing fields with different data types was allowed.

Here is the statement I successfully run on SQL-Server 6.5:

create table tbl1 (code char(8) primary key (code))
create table tbl2 (code varchar(8) primary key (code))
alter table tbl1 add constraint FK foreign key (code) references tbl2 (code)

no trailing semicolon or you'll get a syntax error.

I can't get a grasp on sql 7 media so I can't tell if it was working on that version too.


Query to get reposts from people the user is following

SELECT p.*,r.repost_userr.repost_date,IF(r.post_id>1,'true','false') FROM posts p
left join repost_user r on p.post_id = r.post_id
left join user_follow u on p.post_user = u.follower
where following=$loggedin_user;

Regarding the performance implications of using union all in place of joins, please see this answer on Stack Overflow:

Inner Join versus Union All


MySql column without aggregates and group by clause

For the question about aggregates:  See only_full_group_by, which was added in MySQL 5.7.5.  Before that, you got random values for the columns not based on the GROUP BY column(s).  After that, the statement is in error (unless you turn off that flag).

As for whether there is a performance gain?  Who cares, if you get garbage?  SELECT * is probably never faster (other than to type) than SELECT ... with specific columns.

As for FIRST, LAST, and other "Windowing functions", they are available in MariaDB 10.2 and MySQL 8.0 (but not yet ready for prime time).  There are workarounds, often using LIMIT 1.

Another thing to learn about if you are moving to MySQL:  Learn about AUTO_INCREMENT as a non-drop-in replacement SEQUENCEs.


Query Store - What are the Security permissions needed to view reports?

Just use

GRANT VIEW DATABASE STATE TO [USER];


MySQL Replication - 1 Master - 2 Slaves. Inserting works for One, not the other

Have you set your second slave to server_id = 3 (or depends on you, should be different from 1st slave)

and in mysql of second slave:

CHANGE MASTER TO MASTER_HOST='IP Address of Master Server',
MASTER_USER='slave_user2',
MASTER_PASSWORD='slave_user_password',
Below lines will need the values from ‘SHOW MASTER STATUS’ in Master Database
MASTER_LOG_FILE='',
MASTER_LOG_POS=;


Sybase ASE will not allow you to directly delete rows from multiple tables using a wild card, but it's pretty simple to create a script to find the table names, and loop through them.

You can find the tables names by querying sysobjects within the database.

To find all the tables with the prefix Table_ you would do:

select name from myDatabase..sysobjects
where type = "U"
and name like "Table\_%" escape "\"

In the above query "U" is the flag for User defined tables.  Also because _ is a single character wild card in T-SQL, we have to escape it to find the literal _.  T-SQL allows almost any character to act as the escape, but it must be defined after the like clause.  Finally % is the multi-character wild card, the T-SQL equivalent to * in *nix and DOS.

It would also be possible to use the above query to build a sql script.  We start by telling the server not to print out the number of rows affected by the query using set nocount.  This allows us to create a script that will run error-free, without needing additional editing.

set nocount on    \\supressess rows affected printout
select "delete from " +name+ " where type = 1" + char(10) + "go"
from myDatabase..sysobjects
where type = "U"
and name like "Table\_%" escape "\"
go

If you execute this from isql and using the -b flag to supress headers, and -o flag to redirect output to a file, it will build a script that can then be executed directly.

isql -Uusername -Ppassword -Sservername -b -omyScriptToDeleteData.sql -iSQLToFindTables.sql


Why is CDC capture failing due to a missing dbo principal

dbo is mapped to an invalid login. Re-map it to a valid one:

ALTER AUTHORIZATION ON DATABASE::[<dbname>] TO [sa];


How can I define and declare a good PRIMARY KEY for my dataset?

I've asked some questions in the comments, but in general:

If you have a combination of columns that would make a row unique according to the business rules of the data, you can add those columns to a unique index/constraint and the DBMS will take care of no duplicates.  Concatenating the columns and putting them in a PK would not add any more uniqueness and it would impact performance in a negative way by taking up more space and potentially being a wider than needed composite key.


Can any database table name support double quotes "?

According to the SQL standard, this should be possible by doubling the double quotes (similar to escaping single quotes in string constants).

At least Postgres does support this (and Oracle does not, resulting in "ORA-03001: unimplemented feature")

create table "really""stupid_name"(id integer);

Online example: https://rextester.com/JYCW13692

However, using quoted identifiers is a bad idea to begin with. Let alone identifiers that in turn contain double quotes.

I would strongly suggest you do not do this.


While by no means a dead cert, PAGEIOLATCH_SH and CXPACKET in equal weight can be indicative of parallel table scans. Without a baseline to compare with I'd suggest looking at the top CPU and IO consumers and looking for those which appear in both.


MySQL Data Synchronisation from different Databases in C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace DBsync2
{
      //table2 is the Master(with duplicates) Table and table1 is the one without duplicates

<pre>
public partial class Form1 : Form
{
    private MySqlConnection conn1 = null;
    private MySqlConnection conn2 = null;
    string cs1,cs2;

    class IdTsEntry
    {
        public Int64 fid { get; set; }
        public String fdate { get; set; }
    }

    void Init()
    {
        //initialize cs1, cs2 to suit the database connection details.

        try
        {
            string servername1 = server1.ToString();
            string uname1 = uid1.ToString();
            string pass1 = pwd1.ToString();
            string dbName1 = dbname1.ToString();

            string tbl1 = table1.ToString();
            string tbl2 = table2.ToString();

            conn1 = new MySqlConnection(cs1);
            conn1.Open();
            conn2 = new MySqlConnection(cs2);
            conn2.Open();

            tbl1 = "abc";
            tbl2 = "def";

            // Load target list's ids and timestamps
            string stmt1 = "SELECT seriennummer, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl1;
            MySqlCommand cmd1 = new MySqlCommand(stmt1, conn1);
            List&lt;IdTsEntry&gt; A = new List&lt;IdTsEntry&gt;();
            using (MySqlDataReader reader = cmd1.ExecuteReader())
            {
                //while (reader.Read())
                //{ // I assume the fields are set to NOT NULL
                //    A.Add(new IdTsEntry()
                //    {
                //        fid = reader.GetInt64(0),
                //        fdate = reader.GetString(1)
                //    });
                //}

                while (reader.Read())
                { // I assume the fields are set to NOT NULL
                    A.Add(new IdTsEntry()
                    {
                        fid = reader.IsDBNull(0) ? -1L : reader.GetInt64(0),
                        fdate = reader.IsDBNull(1) ? "" : reader.GetString(1)
                    });
                }
            }
            // Load source list's ids and timestamps
            string stmt2 = "SELECT fid, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2;
            MySqlCommand cmd2 = new MySqlCommand(stmt2, conn2);

            List&lt;IdTsEntry&gt; B = new List&lt;IdTsEntry&gt;();
            using (MySqlDataReader reader = cmd2.ExecuteReader())
            {
                while (reader.Read())
                { // I assume the fields are set to NOT NULL
                    B.Add(new IdTsEntry()
                    {
                        fid = reader.GetInt64(0),
                        fdate = reader.GetString(1)
                    });
                }

            }

            // Filter lists
            List&lt;Int64&gt; List1 = new List&lt;Int64&gt;();
            List&lt;Int64&gt; List2 = new List&lt;Int64&gt;();
            foreach (IdTsEntry b in B)
            {
                var a = A.FirstOrDefault(e =&gt; e.fid.Equals(b.fid));
                if (a == null)
                    List2.Add(b.fid); // b.id not in A -&amp;gt; new row
                else if (!a.fdate.Equals(b.fdate))
                    List1.Add(b.fid); // b.id in A but other timestamp -&amp;gt; altered row
            }

                        // Update altered rows
            //string ct1 = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = {0}";
            //string ct2 = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = {0}";
            foreach (Int64 id in List1)
            {
                // Read all entry values into parameters
               // cmd1.CommandText = String.Format(ct1, id);
                cmd1.CommandText = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = " + id.ToString();

               // MessageBox.Show(cmd1.CommandText);
                cmd2.Parameters.Clear();
                int i = 0;
                using (MySqlDataReader reader = cmd1.ExecuteReader())
                {
                    if (!reader.Read()) continue;
                    for (int n = 0; n &lt; reader.FieldCount; n++)
                    {
                        cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
                            reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
                    }

                }
                // Update row
                //cmd2.CommandText = String.Format(ct2, id);
                cmd2.CommandText = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = " + id.ToString();
                cmd2.ExecuteNonQuery();
            }

                            // Insert new rows
            //ct1 = "SELECT fid, variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = {0}";
            //ct2 = "INSERT INTO " + tbl1 + " (seriennummer, variante,charge,fdate) " +
            //    "VALUES (@val1, @val2, @val3, @val4)";
            //    cmd2.CommandText = ct2;
                cmd2.CommandText = "INSERT INTO " + tbl1 + " (seriennummer,variante,charge,fdate) " +
                    "VALUES (@val1, @val2, @val3, @val4)";
                foreach (Int64 id in List2)
                {
                    // Read all values into parameters
                    //cmd1.CommandText = String.Format(ct1, id);

                    cmd1.CommandText = "SELECT fid,variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = " + id.ToString();
                    cmd2.Parameters.Clear();
                    int i = 0;
                    using (MySqlDataReader reader = cmd1.ExecuteReader())
                    {
                        if (!reader.Read()) continue;
                        for (int n = 0; n &lt; reader.FieldCount; n++)
                        {
                            cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
                                reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
                        }
                    }
                    // Insert row
                    cmd2.ExecuteNonQuery();
                }

        }

        catch (MySqlException ex)
        {
            MessageBox.Show("Error: " + ex.ToString());

        }

    }
    public Form1()
    {
        InitializeComponent();

        Init();

    }

}

}


Update the other columns which are for to have minimum of other 2 columns , difference between 2 columns , and others

update t set
 min_count = least(count_s, count_t),
 diff = count_s - count_t,
 negative_diff = decode(sign(count_s - count_t), -1, count_s - count_t, 0),
 positive_diff = decode(sign(count_s - count_t),  1, count_s - count_t, 0)
;

Or just use virtual columns since you are on 11g:

create table t(
  subscriber_id number,
  count_s number,
  count_t number,
  min_count as (least(count_s, count_t)),
  diff as (count_s - count_t),
  negative_diff as (decode(sign(count_s - count_t), -1, count_s - count_t, 0)),
  positive_diff as (decode(sign(count_s - count_t),  1, count_s - count_t, 0))
);


Get last two IDs from rows for last two days

You can use the rank function to get the last two terminalid's:

select terminalid, date, toolid, toolname, workcenterid
from (
    select terminalid, date, toolid, toolname, workcenterid
         , dense_rank() over ( order by terminalid desc ) as rnk
    from T
    where date <= now() -- this will be dependent of your DBMS
) as X
where rnk <= 2;

Note that date is a reserved word so it is wise to name the column otherwise. Beside that it is a date of some kind (hard to tell from the question), say transaction_date in lack of a better understanding of your domain.


MySQL General log not working on Mac OS X

The problem is indeed as @Phil hinted at. To fix it, set the permissions for the _mysql user:

> cd ~
> touch mysql.log
> sudo chown _mysql:wheel mysql.log

Restart MySQL after that and the log file will be used.


SQL Server DATEDIFF: How's dayofyear different from day?

Yes it is the same effect for DATEDIFF().  For an arithmatetic function like DATEDIFF(), it will have the same result, as the math is still taking into account the same unit: whether it's for day or dayofyear they are both still in days.


Is this an error in SQL Server's documentation for ROLLBACK?

The ROLLBACK TRANSACTION documentation is correct, albeit not clear without additional context.
ROLLBACK is shorthand for the similar ROLLBACK WORK statement rather than ROLLBACK TRANSACTION. Below is an excerpt from the ROLLBACK WORK doc page:

ROLLBACK [ WORK ]   [ ; ]
This statement functions identically to ROLLBACK TRANSACTION except
that ROLLBACK TRANSACTION accepts a user-defined transaction name.
With or without specifying the optional WORK keyword, this ROLLBACK
syntax is ISO-compatible.
When nesting transactions, ROLLBACK WORK always rolls back to the
outermost BEGIN TRANSACTION statement and decrements the @@TRANCOUNT
system function to 0.

Note the same applies to the similar COMMIT TRANSACTION and COMMIT WORK statements.


ALTER AUTHORIZATION has a more general use for changing ownership of databases, types, keys etc. There is an ENDPOINT example n this MSDN link

  ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner

ALTER SCHEMA does exactly one thing

  Transfers a securable between schemas

In this special case you've highlighted, they do the same thing, There is no difference.

I'll guess that ALTER SCHEMA was added for completeness (eg there is CREATE and DROP why not ALTER) or in preparation for other features (such as renaming a SCHEMA, say)


I think you may be making this more complex then need be, two alternative methods of storing trajectories.

Suggested method: create a LINESTRING M with each POINT M's M dimension representing the offset since t0. This is the method that PostGIS standardizes around with ST_IsValidTrajectory, ST_DistanceCPA, and ST_AddMeasure
Normalize to a time interval with a LINESTRING: every POINT on the LINESTRING represents t0 + numpoints-1

Both of those scheme can be indexed simply and easily.


Postgres: compare the dates in rows and find the difference


Can someone explain why select with nolock will query a potion of updated data?

It is not quite true that NOLOCK means placing no locks at all. Queries under this hint will still take Sch-S locks and (possibly HOBT locks).

Under read committed isolation level SQL Server will (usually) take row level S locks and release them as soon as the data is read. These are incompatible with the X locks held on uncommited updates and thus prevent dirty reads.

In the example in the linked answer the SELECT query is not blocked when it encounters a modified row so reading partial updates is quite likely.

It can also happen at default read committed isolation level too though that a SELECT reads some rows with the "before" value and others with the "after" value. It is just needed to engineer a situation where

Select query reads value of row R1 and releases its S lock
Update query updates R2 and takes an X lock
Select query tries to read R2 and is blocked.
Update query updates R1 and takes an X lock.
Update transaction commits thus releasing its locks and allowing the Select to read R2

This type of situation might arise for example if the SELECT and UPDATE are using different indexes to locate the rows of interest.

Example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y AS -X UNIQUE,
Name varchar(10),
Filler char(4000) DEFAULT 'X'
)

INSERT INTO T (Name)
SELECT TOP 2500 'A'
FROM master..spt_values

Now in one query window run

DECLARE @Sum int

SELECT 'SET @@ROWCOUNT' WHERE 1=0

WHILE (@@ROWCOUNT = 0)
SELECT @Sum = SUM(LEN(Name))
FROM T
WHERE Y IN (-1, -2500)
HAVING SUM(LEN(Name)) = 3

This will run in an infinite loop. In another run

UPDATE T
SET Name=CASE WHEN Name = 'A' THEN 'AA' ELSE 'A' END

This will likely stop the loop in the other query (try again if not) meaning that it must have read either A,AA or AA,A


How to prevent message »Pager won't be used for less than 500 lines.«

The message comes from the execution of the \pset command, which you asked psql to execute upon start.
Change your .psqlrc file and tell psql to be quiet:
\set QUIET on
\pset pager_min_lines 500
\set QUIET off


If you want to remove all line feed characters (\n), then what you have is ok. You could also remove the WHERE, to update all rows:

UPDATE Players
SET Name = REPLACE(Name, CHAR(10), '')
-- WHERE PlayerID <= 500
;

Similarly, if you want to remove all spaces, do the same:

UPDATE Players
SET Name = REPLACE(Name, ' ', '')
-- WHERE PlayerID <= 500
;

Beware though that the above will change 'Mark Jenkins' to 'MarkJenkins'. Are you sure you want to do that?

You could also do both actions in a single statement, with:

SET Name = REPLACE(REPLACE(Name, CHAR(10), ''), ' ', '')

Before running the actual update, you could examine the changes with:

SELECT Name,
       REPLACE(Name, CHAR(10), '') AS NewName_NoLinefeed,
       REPLACE(Name, ' ', '') AS NewName_NoSpace,
       REPLACE(REPLACE(Name, CHAR(10), ''), ' ', '')
           AS NewName_NoLinefeed_NoSpace
FROM Players
-- WHERE PlayerID <= 500
;


Cleaning up cut and pasted outlook email addresses

You can use a recursive common table expression to iterate through the string variable:

DECLARE @outlook nvarchar(max)='Something.something@email.com;Anne Hathaway < Anne.Hathaway@Email.com >; Albert Einstein < Albert.Einstein@email.com >; Ash Ketchum < Ash.Ketchum@mail.com >';

WITH semi AS (
    SELECT CAST(NULL AS nvarchar(255)) AS part, @outlook AS remain
    UNION ALL
    SELECT CAST(LEFT(remain, CHARINDEX(';', remain+';')-1) AS nvarchar(255)),
           CAST(SUBSTRING(remain, CHARINDEX(';', remain+';')+1, LEN(remain)) AS nvarchar(max))
    FROM semi
    WHERE remain!='')

SELECT +LEFT(addr, CHARINDEX('>', addr+'>')-1)
FROM (
    SELECT LTRIM(SUBSTRING(part, CHARINDEX('<', part)+1, LEN(part))) AS addr
    FROM semi
    WHERE part IS NOT NULL
    ) AS sub;

Ok, so what's going on here is that the "semi" common table expression splits the string using the semicolon character (and if one of your names has a semicolon in it, you're out of luck).

Once we've split every name/address into its own row, the bottom SELECT gets only the string between the < and > characters.

If you want to go a little crazy with it, you could use XML parsing to concatenate all those lines back on a single row:

WITH semi AS (
    SELECT CAST(NULL AS nvarchar(255)) AS part, @outlook AS remain
    UNION ALL
    SELECT CAST(LEFT(remain, CHARINDEX(';', remain+';')-1) AS nvarchar(255)),
           CAST(SUBSTRING(remain, CHARINDEX(';', remain+';')+1, LEN(remain)) AS nvarchar(max))
    FROM semi
    WHERE remain!='')

SELECT SUBSTRING(CAST((
    SELECT '; '+LEFT(addr, CHARINDEX('>', addr+'>')-1)
    FROM (
        SELECT LTRIM(SUBSTRING(part, CHARINDEX('<', part)+1, LEN(part))) AS addr
        FROM semi
        WHERE part IS NOT NULL
        ) AS sub
    FOR XML PATH(''), TYPE) AS nvarchar(max)), 3, 10000);

To round off the answer, if you have access to a SQL Server 2017 instance, this is just a few lines of code:

SELECT STRING_AGG(RTRIM(LTRIM(REPLACE(REPLACE(b.[value], '<', ''), '>', ''))), '; ')
FROM STRING_SPLIT(@outlook, ';') AS a
CROSS APPLY STRING_SPLIT(a.[value], ' ') AS b
WHERE b.[value] LIKE '%@%';


PostgreSQL filtering on array length inside JSON

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much).

One trick to overcome this might be changing the query slightly and creating a covering index.  For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this column in the index:

CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id)
  FROM your_table
 WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

This will hopefully turn into an index-only scan (I tested this omitting the jsonb part, but you will  be able to tell if it works).


SQL Server 2016 Cluster - mail works in alerts; sp_send_dbmail dosen't send in jobs

AMtwo Got me to the solution.  In this new installation we separated the service accounts that run the server/agent and the agent didn't have access to run the database mail or run scripts in the databases.

I added the agent user to the server security then gave them access to the [DatabaseMailUserRole] role in [msdb].  In addition to that it needed access to the database it was pulling information from.

Thanks for your help.


Migrate heroku database to Amazon RDS with minimum downtime

Pretty sure you are going to have to perform a good old-fashioned dump-and-restore here. Amazon doesn't seem to offer any other import options than loading in a dump:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

and even if they did, AFAICT the only export options offered by Heroku are pg_dumps (i.e. not basebackups or the ability to set up an external hot standby node):

https://devcenter.heroku.com/articles/heroku-postgres-import-export

If you're lucky, you're using a Postgres version new enough to support both parallelized pg_dump (9.3+) and parallelized pg_restore (8.4+), which will make your job a bit quicker. And hopefully you can afford to take a maintenance window during which you lock out writes to your site while the switchover is underway...


Let's start with the basics:

Should you store 16 trillion raw data points per year in a relational database? Probably not. Think about what you're going to do with it afterwards, and what those queries will look like. Relational databases are great for data that has relationships to other tables and needs to be inserted/updated/deleted frequently, or read by common reporting tools, but it's not quite as good of a fit here.

If you don't do it in an RDBMS, where do you do it? Meet the time series database, something designed for this exact purpose. Examples include Graphite and InfluxDB.

If you had to do it, what should you look out for? Any kind of maintenance will be extremely challenging - index rebuilds, stats updates, backups, CHECKDB, etc. Instead, consider a dynamic sharding design where data is split into smaller volumes. In the data warehousing realm, for example, large tables are typically split by date range (2017Q3, 2017Q2, 2017Q1, etc) so that if you need to add new columns for new incoming data, you only have to modify the current table. For reporting purposes, you can union all the tables together into a view - but just be warned that any kind of ad-hoc queries on a trillion-row table can prove challenging. (Heaven help you if someone wants an order-by and there's no supporting index - goodbye, tempdb drive space.)


In where clause, does MySQL continue checking conditions if first condition before AND falsy

I did some experimenting.  Here's my guess:

WHERE (constant-expression-evaluating-to-FALSE)
  AND (SELECT ...)

It will evaluate the constant and stop.

WHERE (col ... )  -- some test that can't use an index (but always fails)
  AND (SELECT ...)

It will evaluate the SELECT.  Here the Optimizer is being smart -- it realizes that reevaluating the SELECT is likely to be costly, so better to do it once.  Actually, the EXPLAIN FORMAT=JSON says "optimized_away_subquery", which probably means that, in the process of deciding what to do with the subquery, it evaluated it.

Note that I am assuming that the subquery is not correlated.  That is, nothing from the original table is referenced in it.  If it were, then the Optimizer would be obligated to evaluate it for each row in the outer query.  And it would probably go back to the original case -- always FALSE would avoid evaluating the subquery.  However, multiple TRUEs would lead to multiple evaluations.

(I arrived at this by (1) somewhat understanding the Optimizer, and (2) testing.  Caveat:  My test was with 5.6.22; older versions, and MariaDB may work differently.  However, I believe that 5.7 and 8.0 will work the same.)


Mostly just for my own amusement, you can do this with a materialized view which has a unique index:

CREATE MATERIALIZED VIEW LOG ON COMPANY
WITH PRIMARY KEY, ROWID
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON DEPARTMENT
WITH PRIMARY KEY, ROWID (company_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE
WITH PRIMARY KEY, ROWID (department_id, username)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW xcheck_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT c.id as company_id, lower(e.username) as username,
    c.rowid as c_rowid, d.rowid as d_rowid, e.rowid as e_rowid
from company c, department d, employee e
where d.company_id = c.id
and e.department_id = d.id;

CREATE UNIQUE INDEX xcheck_ind ON xcheck_mv(company_id, username);

Then attempting to insert an employee record for a different department with the same username gives a unique constraint violation, although not until you commit:

insert into company (id) values(1);

1 row created.

SQL> insert into department (id, company_id) values(1, 1);

1 row created.

SQL> insert into department (id, company_id) values(2, 1);

1 row created.

SQL> insert into employee (id, department_id, username) values(1,1,'Joe Bloggs');

1 row created.

SQL> insert into employee (id, department_id, username) values(2,2,'Joe Bloggs');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (STACKOVERFLOW.XCHECK_IND) violated

Just for fun I put lower() in the MV definition to catch the most basic workaround - so trying to insert 'joe bloggs' also fails - but this model is never going to be very robust.

I'm not saying this is a good idea, just that it's possible...


Getting Identity values to use as FK in an INSTEAD OF trigger

Tables:

CREATE TABLE dbo.Claim
(
    DataRowID   bigint IDENTITY NOT NULL,
    ClaimColumn integer NOT NULL,

    CONSTRAINT PK_Claim
        PRIMARY KEY CLUSTERED (DataRowID)
);
GO
CREATE TABLE dbo.ClaimExtended
(
    ClaimDataRowID  bigint NOT NULL,
    ExtendedColumn  integer NOT NULL,

    CONSTRAINT PK_ClaimExtended
        PRIMARY KEY CLUSTERED (ClaimDataRowID),

    CONSTRAINT FK_ClaimExtended_Claim
        FOREIGN KEY (ClaimDataRowID)
        REFERENCES dbo.Claim (DataRowID)
);

View:

CREATE VIEW dbo.MyView
WITH SCHEMABINDING
AS
    SELECT
        c.DataRowID,
        c.ClaimColumn,
        ce.ExtendedColumn
    FROM dbo.Claim AS c
    JOIN dbo.ClaimExtended AS ce ON
        ce.ClaimDataRowID = c.DataRowID;

Instead-of trigger:

CREATE TRIGGER trgMyView_IOI
ON dbo.MyView
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET ROWCOUNT 0;

    DECLARE @ExtendedRows AS TABLE
    (
        ClaimDataRowID  bigint PRIMARY KEY,
        ExtendedColumn  integer NOT NULL
    );

    MERGE dbo.Claim AS c
    USING INSERTED AS ins ON 1 = 0
    WHEN NOT MATCHED THEN
        INSERT (ClaimColumn)
        VALUES (ins.ClaimColumn)
    OUTPUT
        INSERTED.DataRowID,
        ins.ExtendedColumn
    INTO @ExtendedRows
        (
            ClaimDataRowID,
            ExtendedColumn
        );

    INSERT dbo.ClaimExtended
        (ClaimDataRowID, ExtendedColumn)
    SELECT
        er.ClaimDataRowID,
        er.ExtendedColumn
    FROM @ExtendedRows AS er;
END;

Example usage:

INSERT dbo.MyView
    (ClaimColumn, ExtendedColumn)
VALUES
    (1000, 2000),
    (1001, 2001);
GO
SELECT
    mv.DataRowID,
    mv.ClaimColumn,
    mv.ExtendedColumn
FROM dbo.MyView AS mv;

Output:


postgresql random function returns duplicate results when order by random used in query

random() is volatile, and the doc says about volatility:

  A query using a volatile function will re-evaluate the function at
  every row where its value is needed.

Based on this definition, the engine is correct in evaluating the function only once per row, not once per column for the same row.

To turn off this optimization, you might tweak the query for the optimizer to miss the fact that the two columns carry the same expression. For instance:

postgres=> SELECT random()+0 as sortOrder, random() as col2
               FROM generate_series(0,1)
               ORDER BY sortOrder;
     sortorder      |        col2
--------------------+---------------------
 0.0412265718914568 | 0.00992022315040231
  0.841067179106176 |   0.818967831321061
(2 rows)

Now, that simple solution may seem a bit fragile. In some future version, the optimizer might theorically become smart enough to recognize that these two expressions have a common sub-expression that it can reuse.

A really robust solution would be to generate the columns in different rows to start with, in order to force the optimizer to use distinct function calls no matter how hard it tries to avoid the multiple evaluations. For instance:

SELECT v1,v2 FROM
(select random() as v1, row_number() over() as r1
 FROM generate_series(0,1)) as s1
JOIN
(select random() as v2, row_number() over() as r2
 FROM generate_series(0,1)) as s2
ON s1.r1=s2.r2
ORDER BY 1;
        v1         |        v2
-------------------+-------------------
 0.145532250870019 | 0.963712519966066
 0.277611976023763 | 0.252654927782714
(2 rows)


Using SQL Profiler on a database that's in production

I use SQL Profiler against production all the time.  When done correctly (filtering so that you get back a very small amount of data) against a server the risk is minimal.  Tracing everything down would be useless.


How does MOLAP SSAS default processing detect changes in the relational source?

I contacted Chris Webb (SQL Server MVP) about this, who got back to me.

The answer is: the premise of the question was wrong. Default processing does not detect changes in the relational source.

In fact, if you have done a full process on your cube, and the data changes in the relational source, Default process will not load the new data into the MOLAP storage.

The only time default processing loads data from the relational source, is when a partition is unprocessed, or one of the indexes is out, or for some other reason certain parts of the cube have been left in an 'unprocessed' state.

So if you're scheduling a SQL Agent job to load data into the cube, and you want to use Default, you need to do something like a Process Update or a Process Add first, followed by a Process Default.

Here is the MS article: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/processing-options-and-settings-analysis-services


Is there a better design to store the data?

Probably. A measurement seems to be an entity in itself, and should be modelled like one.  You will then need a one-to-many (or many-to-many, if a particular measurement applies to more than one scenario) relationship between scenarios and measurements.


SQL Native Client and network disconnects

If you have connection pooling enabled in the connection string for the SQL Server Native Client, then connections are maintained to the SQL Server.

Check this MSDN blog for some technical details about how you can confirm if connections are being maintained.

Also, take a look at this MSDN page for Connection String properties, and how to ensure connection pooling is enabled.


How safe is to use BACKUP LOG WITH TRUNCATE_ONLY

What actually doing this code in the SQL-Job? Does it really help?

SQL is backing up the log to nowhere and then trying to shrink the data and log files of your database. All of these are bad ideas. Backing up the log to nowhere means you lose point-in-time recovery of your database because you cannot restore these portions of the log. See this link for more info.

  Is there anything related performance of the DB-Server?

Constantly shrinking your log and data files can cause performance issues because SQL Server has to regrow these files as needed, which causes waits on your queries that require the additional space.

  If I remove first line of code (BACKUP LOG), how it can get effect?

Before you can reclaim space in the log, you need to backup the data in the log. Removing this would mean your shrinking wouldn't likely free much space, however, as stated before shrinking the log is a bad idea anyway.

  Currently I am not able see SQL-Job history of same SQL-Job, is this the reason that it clears all logs?

No, this is unrelated. Most likely your SQL Agent history is limited and you have a high-frequency job filling the job history tables. See this link.


SQL Developer the client application is free of charge.  Of course, the Oracle database would need to be licensed in a way that allowed you to connect with any tool.

PL/SQL Developer is a completely different tool produced by a different company that you would need to license.

Define "secure".  Assuming a default database install, using any tool to connect to the database is secure in the sense that, say, the password isn't sent in clear text over the network.  But all the data is, by default, sent in clear text unless you configure Advanced Security.  But there are many, many ways that a database could be relatively secure or relatively insecure because there are many, many ways to set up security and many types of things that people may want to prevent.


How can I script administrator permissions on a SSAS server?

I highly recommend you check out Analysis Management Objects (AMO) - I use it to perform all maintenance tasks in SSAS such as creating Roles, Permissions, Databases and Models.

I prefer to use C# but there are plenty of examples online of using the same classes in PowerShell. You need to make sure you use the correct classes based on the type of install that you have (multidimensional or tabular). It looks like you have Tabular in your example above so please refer to the Microsoft.AnalysisServices.Tabular Namespace primarily.

This is how I would perform some tasks in c# using AMO:

// Copy Roles

var sourceServer = new Server();
var targetServer = new Server();

sourceServer.Connect("DEV1"); // replace with IP address, DNS etc...
targetServer.Connect("DEV2");

foreach (Role sourceRole in sourceServer.Roles)
{
    var targetRole = new Role();
    sourceRole.CopyTo(targetRole);
    targetServer.Roles.Add(targetRole);
}

targetServer.Update();

// Backup Database
foreach (Databse db in sourceServer.Databases)
{
    db.Backup($"{db.Name}.abf"); // or whatever file you want to back it up to.
}

You can use c# in maintenance plan script tasks or you can create standalone PowerShell scripts using the same classes. Once you get your head around the object model its pretty easy to do anything that you need.


Backup Functions in PgAdmin 4 Version 1.6

This is not backing up your functions, it is just selecting the text of them.  You could copy the result from pgAdmin and save it to a text file manually if you want.

If you take a full backup of your database the functions will be backed up along with the data.


SQL server create temporary function as a CTE

To replicate this kind of code, without creating a function:

CREATE FUNCTION dbo.add1(@x int)
RETURNS int
AS
BEGIN
  RETURN (SELECT @x + 1);
END
GO

SELECT dbo.add1(v.Value1), dbo.add1(v.Value2)
FROM (VALUES(1,2),(3,4)) AS v(Value1, Value2);

You can use CROSS APPLY:

SELECT z.v1, z.v2
FROM (VALUES(1,2),(3,4)) AS v(Value1, Value2)
CROSS APPLY
(
  SELECT v.Value1 + 1, v.Value2 + 1
) AS z(v1,v2);

If the function is extremely complicated and you don't want to repeat it, and that is the actual problem, you could try this solution. It is complex but allows you to only write the function once. If you need to expand this to more than two columns it gets more complicated.

CREATE TABLE #vals
(
  id int identity(1,1) primary key,
  a  int,
  b  int
);

INSERT #vals(a,b) VALUES(1,2),(15,16);

;WITH vals AS
(
  SELECT * FROM #vals AS v
  UNPIVOT (Result FOR [Value] IN (a,b)) unp
  CROSS APPLY
  (

     /******** COMPLEX FUNCTION HERE ********/
     /**** Applies to output of unpivot *****/
     SELECT unp.Result + 10

  ) AS new(orig)
  PIVOT (MAX(orig) FOR Value IN ([a],[b])) AS p2
)
SELECT v1.id,
  OriginalValue1 = v1.Result,
  Value1 = v1.a,
  OriginalValue2 = v2.Result,
  Value2 = v2.b
FROM vals AS v1
JOIN vals AS v2 ON v1.id = v2.id
AND v1.a IS NOT NULL
AND v2.b IS NOT NULL;

GO
DROP TABLE #vals;

Or you could ask for the permissions to create your function somewhere.


Is there any other way apart from sa to login to a migrated instance where the target environment is in a different domain?

To avoid this, I usually prefer to set up a fresh installation of an instance in the new domain. Then I restore the databases. Then I script proper instance configurations/agents/other things, adapting them to the new domain.
As suggested by @Ronaldo, you can enter in this emergency situation using:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-2017
Please, take a look at this guide in case of domain change:
https://social.technet.microsoft.com/wiki/contents/articles/24960.migrating-sql-server-to-new-domain.aspx
It's quite old but the general overview is fine.
This guide to regaining access is also fine:
https://www.sqlservercentral.com/articles/regaining-access-to-sql-server-after-changing-the-domain


redis newbie - how to create hash within a hash?

Redis doesn't support nested data structures, and specifically it doesn't support a Hash inside a Hash :) You basically have a choice between two options: either serialize the internal Hash and store it in a Hash field or use another Hash key and just keep a reference to it in a field of the outer Hash.


Why does my statement result in a syntax error?

You use DECLARE in compound-statement syntax to declare a local variable, cursor, condition or handler. However, your variable @batchsize is a session global variable due to the presence of the @ prefix, and it is an error to attempt to redeclare a global variable.

Change the name of your variable from @batchsize to batchsize, and then read up on MySQL variables.


Is it possible to gain the precise position of a live mysql database when performing full backup

When you are using mysqldump, you can get the binary logs coordinates of the backup at the point-in-time of the the mysqldump's start.

Just add the --master-data option

mysqldump --master-data=2 --flush-logs --single-transaction...

Using --master-data=2 will record the binary log filename and position in the form of a  CHANGE MASTER TO command. It is recorded as a comment.

Using --master-data=1 will also record the binary log filename and position in the form of a  CHANGE MASTER TO command. It is not recorded as a comment, but as a command that will be executed when you reload the mysqldump.

In both cases, you can always find that CHANGE MASTER TO command on line 23 of the dump. I mentioned this before in my old post MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos under the heading "BUT WAIT..."

To retrieve that line from a mysqldump file named myfullbackup.sql, simply run

head -23 myfullbackup.sql | tail -1 > MasterLogFilePos.sql

and the CHANGE MASTER TO command will be in it

If you run the mysqldump with

mysqldump --master-data=2 --flush-logs --single-transaction...

the Master will continue allowing reads and writes to all InnoDB tables.


How to find out the maximum number of datafiles per tablespace in Oracle?

db block size is a database parameter but maximum number of tablespace or datafile is a physical limit and you can not find them in v$parameter view. You can use documentation for that: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915


Postgres - Encoding, Collation and CType

I know that encoding affects how the information is actually
    stored (IE whether 'A' requires one byte or multiple bytes,
    and what value those bytes have, depend on the encoding).

Yes. Encoding is the character-to-byte conversion algorithm.
For mono-byte encoding such as LATIN1, it's trivially
byte value = character number, but for UTF-8 it's more
complicated.

  I've been told that collate specifies rules for comparing
   characters. If you were to sort a bunch of strings, the collate
   type would dictate the order.

Yes, collate specifies how strings are compared. The collation
service is provided by the operating system or optionally
by the ICU library for PostgreSQL 10 or newer.
Sorting arbitrary strings with linguistic rules is a
complicated business.
The Unicode standard and ISO provide rules but they're heavily
customizable and not all C libraries implement them fully anyway.
See for instance https://en.wikipedia.org/wiki/Unicode_collation_algorithm
for more.

  I've struggled to find what Ctype is; possibly related to concepts
  like uppercase and lowercase (given 'a' knowing that 'A' is the
  uppercase form?).

LC_CTYPE in POSIX is related to the functions in ctype.h
Postgres is strongly influenced by POSIX in that matter and uses a
per-database lc_ctype more or less like POSIX uses LC_CTYPE.

Aside from upper() and lower(), it's also relevant for
regular expressions and full text search (case-folding of tokens).

I think that Postgres could do without an independant lc_ctype
(using the same value as lc_collate) except for one thing:
it's interesting to have lc_ctype to something.UTF-8 for instance, to
have the full range of characters properly supported, and
lc_collate to C because C as a collation is much faster than any linguistic sort implied by lc_collate=lang_country.UTF-8.

Robert Haas (postgres committer) wrote an interesting post about essentially that subject when the COLLATE support was enhanced in Postgres: "The Perils of Collation-Aware Comparisons"

  I don't understand how (as in my example) I can have a UTF8 encoded
   database and use a collate value of English 1252. UTF8 has many
   characters that win1252 does not

This is a Windows-specific thing, due to Windows not following the
POSIX model with locales and collations. On Windows and with non-ICU
collations, Postgres will convert the strings from the db encoding to
UTF-16 (wchar_t) and call wcscoll_l(). This is why the encoding is decorrelated
from the collations.

With ICU collations, either Postgres passes directly UTF-8
contents if it can, or it converts the strings to UTF-16,
so again collations are not tied to a particular database encoding,
contrary to the POSIX model and its family of strcoll functions.


Is restoring a mysql database backup faster at removing white space than running OPTIMIZE?

Both will achieve essentially the same thing. If your exact db version doesn't offer online OPTIMIZE then the dump and restore could be useful if you restore into a new table and then DROP the old one and RENAME the new one, allowing concurrent selects during the import. Regarding the speed, the dump and restore might be a bit faster but it's probably not a huge gain. I prefer it if I'm working with a version that doesn't support online OPTIMIZE.

As Rick James mentioned this sort of thing isn't often necessary, but I have seen it help to bulk re-balance indices on tables with columns struggling to keep up with a large number of concurrent updates.


Only one administrator can connect at this time (MS SQL Server error 18461)

If you start SQL Server in single user mode only one administrator can connect at the same time.

What's probably happening here is that some service is using a sysadmin login to connect such as Reporting services or SQL Server agent.

When starting SQL Server in single user mode you have the option to specify a client application so only that application can connect.

Have a look at the command line options where it lists:

  -m "Client Application Name"

  When you use the -m option with SQLCMD or SQL Server Management
  Studio, you can limit the connections to a specified client
  application. For example, -m"SQLCMD" limits connections to a single
  connection and that connection must identify itself as the SQLCMD
  client program. Use this option when you are starting SQL Server in
  single-user mode and an unknown client application is taking the only
  available connection. To connect through the Query Editor in
  Management Studio, use -m"Microsoft SQL Server Management Studio -
  Query".

  Client Application Name is case sensitive.

If you are able to connect this way, change the maximum server memory to something reasonable. I suppose you will be able to connect because otherwise you would probably receive something as "there is no process on the other end of the pipe" so I would assume your server is running.

If however you can't log in to SQL Server because your memory configuration doesn't allow you, you can start SQL Server in minimal configuration using the -f option.

  -f

  Starts an instance of SQL Server with minimal configuration. This is
  useful if the setting of a configuration value (for example,
  over-committing memory) has prevented the server from starting.
  Starting SQL Server in minimal configuration mode places SQL Server in
  single-user mode. For more information, see the description for -m
  that follows.

For reference: SQL Server can’t start after accidently set the "max server memory" to 0


How large is "too large" for SQL 2008 Web Edition?

This is a very tough question to answer and the only correct thing to say is "it depends". Yes, it's trite but that doesn't make it untrue. That being said I'll try my best to give you an answer (use Standard) and explain the thought process behind it.

I'm not familiar with the "Web" Edition of SQL but I can certainly rattle off the differences between Standard and Enterprise. At the risk of a massive generalization I'd say that if you're looking at between 10 and 100GB for a single DB and you're not sure you need Enterprise features, the upfront cost difference combined with the ease of upgrading the SKU (Edition) if/when you need to mean that choosing Standard over Enterprise probably makes the most sense. I'm guessing that "Web" edition is some semi-stripped down Standard version and personally I'd avoid it based on the "Web Workloads Only" disclaimer. What does that even mean?!

So how does one truly determine if they'll need Enterprise features? Well, the best way I can think of is to download the Standard and Enterprise trials (or get an MSDN license) and do performance testing with both editions. If you find you can't hit/exceed your performance targets with the lower edition identify the bottlenecks and then see if the Enterprise features would assist. If so, upgrade & make the needed changes to use the features then re-test. In my experience the main features one makes use of in Enterprise are (in order of most common to least):

online reindexing
row/page compression
backup compression
filtered indexes
partitioning & partitioned indexes
indexed views
parallel index operations

And finally... When moving up from something that's running well on SQL Express I don't think there would be many cases that justify the jump to Enterprise. Alone, the additional system resources that the full version of SQL can use should deliver big gains.


Backup to the null device on SQL Server for Linux

The "typical" SQL Server syntax does actually work in SQL Server for Linux; so try:

BACKUP DATABASE [xyz] TO DISK = 'NUL';


SQL Server Restore w/o "Closing Existing Connections"

No, the option is there for you so that you can decide to either:

Kick all users out right now and restore immediately; or,
let the restore operation wait until it can get exclusive access to the
database before it begins.

You can't restore and keep connections active, sorry. This is like changing the keg without interrupting someone actively pouring a beer...


Why does xp_logininfo truncates AD user account names? How to overcome this?

the way I get this done is:

            SELECT @@SERVERNAME
            ----myserver

            DECLARE @NTLogin nvarchar(128) ;
            SET @NTLogin = 'mydomain\myuser'

            DECLARE @UserList TABLE (
                     [Account Name] nvarchar(128)collate Latin1_General_CI_AS,
                     [Type] nvarchar(128) collate Latin1_General_CI_AS,
                     [Privilege] nvarchar(128) collate Latin1_General_CI_AS,
                     [Mapped Login Name] nvarchar(128)collate Latin1_General_CI_AS,
                     [Permission Path] nvarchar(128) )

            INSERT INTO @UserList EXEC master.dbo.xp_logininfo @NTLogin, 'all' --insert group information

            IF EXISTS (SELECT * FROM @UserList WHERE [Type] = 'group') --only if it's a group
               INSERT INTO @UserList EXEC master.dbo.xp_logininfo @NTLogin, 'members' --insert member information

            SELECT  [Server Name] = @@SERVERNAME,
                    [Account Name],
                     [Type],
                     [Privilege],
                     [Mapped Login Name],
                     [Permission Path]

            FROM @UserList

you can wrap the code inside a stored procedure with @ntLogin as a parameter, that is how I did it here, including the error handling stuff, which is important too.

Another thing is to install the active directory module for windows powershell

also for testing from inside sql server you can run the following script:

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        ,USER
        ,SESSION_USER

this will show your credentials

and if you execute as the login you are testing you can see how it looks like.

EXECUTE AS LOGIN='yourdomain\the_long_login'

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

REVERT


Why does information_schema have "YES" and "NO" character strings rather than booleans?

INFORMATION_SCHEMA

Using psql you can see the schema with \d information_schema.columns, or you can look it up in the docs.

                               View "information_schema.columns"
          Column          |                Type                | Collation | Nullable | Default
--------------------------+------------------------------------+-----------+----------+---------
... stuff cropped..
 is_nullable              | information_schema.yes_or_no       |           |          |

SQL Spec

Looking up information_schema.columns.is_nullable in the spec we can see it's the spec's fault -- as is often the case with stuff. You can't just erase 9,000 years of legacy. Especially with something that is supposed to provide a dependable  interface. From the SQL 2011 spec,

IS_NULLABLE INFORMATION_SCHEMA.YES_OR_NO
  CONSTRAINT COLUMNS_IS_NULLABLE_NOT_NULL NOT NULL,

And you can see the definition of INFORMATION_SCHEMA.YES_OR_NO defined in the spec too,

CREATE DOMAIN YES_OR_NO AS
  CHARACTER VARYING (3)
  CHARACTER SET SQL_IDENTIFIER
    CONSTRAINT YES_OR_NO_CHECK
    CHECK (VALUE IN ( 'YES', 'NO' ) );

GRANT USAGE ON DOMAIN YES_OR_NO
  TO PUBLIC WITH GRANT OPTION;

PostgreSQL's RDBMs-specific catalog

If you don't need the generic standardized interface though, you can usually find something more sane in pg_catalog. In this case you want pg_catalog.pg_attribute. Let's check it out \d pg_catalog.pg_attribute,

              Table "pg_catalog.pg_attribute"
    Column     |   Type    | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
 attnotnull    | boolean   |           | not null |

There is your boolean.

PostgreSQL Docs

The PostgreSQL docs on Information Schema: Data Types actually address this too,

  A character string domain that contains either YES or NO. This is used to represent Boolean (true/false) data in the information schema. (The information schema was invented before the type boolean was added to the SQL standard, so this convention is necessary to keep the information schema backward compatible.)


What is a good way to parameterize a materialized view on Postgres?

There are three ways,

Use foreign data in a table that holds the inputs.
Make sell_date a function of the current time. Such as AND sell_date > current_time - '5 days'.
If nothing depends on it, drop and reload a new ad-hoc MATERIALIZED VIEW by the same name in a transaction.


MySQL: update instead of delete if foreign key constraint?

You can't directly do it since there's no way (that I can think of) to alternately UPDATE or DELETE from the same query... generally a query can only do one type of CRUD operation (ON DUPLICATE KEY UPDATE being an exception to this).

You could, however, make it "feel like" a single query, with a stored procedure, where you'd set up a CONTINUE HANDLER for the foreign key error you anticipate.  The handler will trap the error and can be used to set a variable which you can test to see whether you hit the error and therefore need to also try the update query.

DELIMITER $$

DROP PROCEDURE IF EXISTS delete_or_update $$
CREATE PROCEDURE delete_or_update (IN input_value INT)
BEGIN

  DECLARE hit_fk_error TINYINT DEFAULT 0;
  DECLARE deleted_row_count INT DEFAULT NULL;
  DECLARE updated_row_count INT DEFAULT NULL;

  -- 1451 is probably the error code that will be generated
  -- Cannot delete or update a parent row: a foreign key constraint fails (%s)
  -- otherwise, you may need to substitute the correct error code in the next line

  DECLARE CONTINUE HANDLER FOR 1451 SET hit_fk_error = TRUE;

  DELETE FROM parent_table WHERE tested_column = input_value; /* arg to the stored proc */
  SET deleted_row_count = ROW_COUNT();

  IF (hit_fk_error = TRUE) THEN
    UPDATE parent_table SET some_other_value = 'something_else'
     WHERE tested_column = input_value;
    SET updated_row_count = ROW_COUNT();
  END If;

  SELECT deleted_row_count, updated_row_count, hit_fk_error;

END $$

DELIMITER ;

This will try the delete; if it hits a foreign key error it will then try the update (and the foreign key error will be suppressed).  The procedure will return a record set with the number of rows affected by each query and whether the foreign key error occurred.  If the "input_value" isn't an INT or the WHERE clause is more complex, you'll need to modify the structure, accordingly.

The potential problem I see here is that if the where clause is too broad, and matches some rows that would cause a foreign key error and other rows that wouldn't, then the query will not treat the rows individually.  That could be accomplished by a more sophisticated procedure that used a cursor to find the rows in the where clause and tried to delete or update them individually by primary key after identifying them.   That approach would be less efficient but more precise.


Here's how I would model the tables for that requirement (note the table and column names can be changed for whatever is appropriate for your application):

In this model, the idea of a "Profile" and "User" is contained in the same entity, "User". The idea of a single vote for one of two user candidates is contained in the entity "Vote". If you want to restrict a User to one Vote per set of two users, then you can add a unique index in Vote for idUser, idUserCandidate1, and idUserCandidate2.


Just remove pg_size_pretty from the query:

SELECT
    relid::regclass AS table,
    indexrelid::regclass AS index,
    pg_relation_size(indexrelid::regclass) AS index_size,


Why SMO's Transfer tries to login into linked server when scripting?

You can only get data from a SQL Server that you are logged into.  The metadata of table definitions, stored procedures, and so forth is also data.

So, your login through the linked server must be successful.  This can be done by granting your login rights on the other server.  Or, the security of the linked server could be through a specific login that has the needed rights.

However, getting rights to the metadata of a database does not imply that you need rights to the data in the database.  For example:

USE DBName;
GRANT VIEW DEFINITION ON DATABASE::DBName TO [YourDomain\YourLogin]

This will allow you to script the definitions from the database without having access to the user data.  (Although that can also be granted separately if needed.)

EDIT: SQL Server does not support deferred name resolution across a linked server.  Therefore, I am fairly sure that since the reference to the remote table cannot be resolved that is why the scripting is failing.

Of course, if the stored procedure is not encrypted then the text is stored in sys.sql_modules of your local server.  You could extract the text from there.

 SELECT definition FROM DBName.sys.sql_modules
 WHERE object_id = object_id(N'DBName.Schema.ObjectName');


What's the differences between sys.dm_exec_query_stats and sys.dm_exec_cached_plans?

In terms of getting the plan, using sys.dm_exec_query_stats you can use statement_start_offset and End offset to get the plan for individual statement in a stored procedure.

Eg

select top 100 object_name(object_id),ps.execution_count as proc_exec_count, qs.plan_generation_num, qs.execution_count as Stmt_exec_count,qs.total_elapsed_time/qs.execution_count as AVG_Run_Time,   qs.total_worker_time/qs.execution_count AS Avg_CPU_Time,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,qs.min_rows,qs.max_rows,
SUBSTRING ([st].[text],
        ([qs].[statement_start_offset] / 2) + 1,
        ((CASE [statement_end_offset]
            WHEN -1 THEN DATALENGTH ([st].[text])
            ELSE [qs].[statement_end_offset] END
            - [qs].[statement_start_offset]) / 2) + 1)
            AS [StatementText],cast(s3.query_plan as xml) query_plan
from sys.dm_exec_procedure_stats ps
JOIN [sys].[dm_exec_query_stats] AS [qs]
        ON [ps].[plan_handle] = [qs].[plan_handle]
cross apply sys.dm_exec_text_query_plan ([qs].[plan_handle], statement_start_offset, statement_end_offset) s3
CROSS APPLY [sys].[dm_exec_sql_text]    ([qs].[sql_handle]) AS [st]
where database_id=db_id('DB') --and qs.execution_count >100
and object_id in (object_id('SP Name'))
ORDER BY avg_run_time DESC

And obviously it has different information regarding query execution stats and plans.


Audit everything coming from an ip adress in an Oracle database

With Unified Auditing.
Oracle Database Auditing

Oracle Database Unified Auditing enables selective and effective
auditing inside the Oracle database using policies and conditions. The
new policy based syntax simplifies management of auditing within the
database and provides the ability to accelerate auditing based on
conditions. For example, audit policies can be configured to audit
based on specific IP addresses, programs, time periods, or connection
types such as proxy authentication.

Example:
CREATE AUDIT POLICY mypolicy
ACTIONS ...
WHEN 'SYS_CONTEXT(''USERENV'', ''IP_ADDRESS'') = ''1.2.3.4'''
EVALUATE PER SESSION;


Tuning query to remove joins for reporting

No it wouldn't (that is, it's not logically equivalent. Performance would probably be better though). Two reasons:

Both of the subqueries are aggregating to get the minimum event_date.
The second subquery (join_223) is an OUTER JOIN, not an INNER JOIN.

Incidentally, you have a repeated ON ON in the last line of the original query.


Yes there is. You could do this with SQL PL to create a stored procedure to do this. Or if you prefer other script languages you could use KSH, BSH, or even Perl.

The point being to catch the errors or exceptions (a little easier to do with SQL PL IMHO) and then you can react to them the way you wish.


Merge replication issue : conflicts with new data

That view is created by replication and used internally. If it is missing, either it got deleted accidentally, or sql servers replication metadata got corrupted.

Depending on how big you databases are and if you can afford some downtime, the easiest way to fix this is to drop and recreate the merge replication setup.


How do long string field names affect MongoDB database size?

This is covered in the developer FAQ, some relevant excerpts:

  MongoDB stores all field names in every document. For most documents,
  this represents a small fraction of the space used by a document;
  however, for small documents the field names may represent a
  proportionally large amount of space

And, just a note on indexes:

  Shorter field names do not reduce the size of indexes, because indexes
  have a predefined structure

So, yes, reducing your field name size will make storage more efficient, though it will have no impact on index sizes.  Whether the saving you will make is worth it (versus loss of descriptiveness) will be up to you.  As an approximation, you will probably save something like 16 bytes per field if you drop to 2 character field names from 20 (for example) and that should mean that the document size will be reduced by more than 40% (~400k).

Here's an easy way to estimate this using the MongoDB shell:

$ ./mongo --nodb
MongoDB shell version: 3.0.2
> testObject = {"12345678901234567890" : 1.23324}
{ "12345678901234567890" : 1.23324 }
> Object.bsonsize(testObject)
35
> testObject = {"1" : 1.23324}
{ "1" : 1.23324 }
> Object.bsonsize(testObject)
16
> testObject = {"12" : 1.23324}
{ "12" : 1.23324 }
> Object.bsonsize(testObject)
17

The Object.bsonsize method will give you an approximate size of any document in bytes but does not include padding, indexes etc. that would actually be used when storing a document in the database. Hence, these are all very approximate numbers - I would recommend testing with actual data to get a more definitive example.


SQL Server 2012 CPU usage spikes to 100%

What is the exact type of the user_id column?

It is very likely not a VARCHAR column therefore the rules of data type precedence dictate that the comparison must be done by casting the user_id to the type of the '10' literal, thus negating the possibility of an index seek and forcing a full scan. This is a very common mistake.

I recommend reading How Data Access Code Affects Database Performance (do a mental translation for Java - Hibernate) and How to analyse SQL Server performance.


I recall that the rule of thumb used to be "never shrink databases". Does it apply in this case?

Yes. Dont touch that shrink button - Its a general rule and a very very sound advice from many renowned SQL Server Gurus - Paul Randal, Brent Ozar, Mike Walsh, Gail Shaw and many more.

  The database is still in use, and still somewhat growing, but I do not think it will grow to use all 1.2Tb in the next 3-5 years.

You have to be 1000% sure about your assumption that its growing but wont grow to this size in your proposed 3-5 years (its a 2 year difference as well :-)).

still somewhat growing -->You really need to find out at what pace it is growing. Are you doing database growth tracking by collecting the data growth over your complete business cycle ?

Now a days, data is what keeps the companies in business and databases are meant to grow.

Think about it - what are you going to do with the disk space that you gain from shrinking the database ?

Concentrate on more important areas of optimization as @AaronBertrand suggests.

Even if you decide to shrink , I would suggest you to

Do a reorg/rebuild and update stats after the nasty shrink operation
Take a full backup to be safe with all the indexes intact.
Adjust your auto-growth settings to not allow the auto-growth events kick off more frequently.

Edit

After OP's comments ---

Then go for it ... since you are or will be moving away data from SQL Server to PostgreSql. But do the post steps once as I mentioned in my answer - once you do the shrink operation. Also, use DBCC SHRINKFILE as you have more control of what you are shrinking as opposed to DBCC SHRINKDATABASE.


For a single student, why would you have multiple selection for race? This for the situation you mentioned as example.

I'd see both options valid (lookup with multiple selection or radio buttons with true-false) depending on the situation you need to display. What you show in the front is not identical with what tables you have in the database. Do your tables to fit simple normalization rules and then just build queries and views to display in the Access UI. Read about normalization and normal forms and design your tables accordingly (I prefer by default 3rd normal form, but it's not mandatory, you could increase/decrease the form level as you see fit).

I think that this tutorial is done with Access users in mind.

You can find some good free data models here: Free data models. Some of them even have attached Access databases.

PS: more of a comment than an answer, but too verbose.


MySQL Cluster setup, receiving error "Specify node id"

(Disclaimer: I'm no expert on NDB Cluster)

A few options to try:

Have you tried ndb_mgmd with the --no-nodeid-checks option?

Description on the ndb_mgmd documentation page

As the error message suggests, maybe try ndb_mgmd with the --ndb-nodeid= option? I'm guessing any small number might be valid. (Note: guessing!)

Description on the Common NDB Options documentation page


Injecting connection context information using a persistent table

In terms of performance, you will incur the overhead of the DELETE and INSERT every time the connection is opened.  Alternatively, you could use the built-in connection CONTEXT_INFO for this purpose.  The example below stores the information in a fixed-length 48-byte structure.

EXEC sp_executesql N'
    DECLARE @ContextInfo binary(48);
    SET @ContextInfo = CAST(CAST(@UsernameAttribute AS nchar(8)) + CAST(@Username AS nchar(16)) AS binary(48));
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
GO

CREATE FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE
      @ContextInfo binary(48) = CONTEXT_INFO()
    , @Username nvarchar(16);
    SET @Username = RTRIM(CAST(SUBSTRING(@ContextInfo, 17, 32) AS nvarchar(16)));

    RETURN ISNULL(@Username, SUSER_NAME());

END
GO

Also, sp_set_session_context and SESSION_CONTEXT() are available in SQL Server 2016 and Azure SQL Database.  That's a much cleaner method if available to you.


How can I time SQL-queries using psql from command line?

You could use the shell command time

postgres@db:$ time psql db -c 'SELECT 1'
 ?column?
----------
        1
(1 row)

real    0m0.108s
user    0m0.040s
sys     0m0.032s

Or, to combine a meta command with an SQL command, you could pipe a string to psql:

postgres@db:~/script$ echo '\timing \\ SELECT 1;' | LANG=C psql
Timing is on.
 ?column?
----------
        1
(1 row)

Time: 0.000 ms

Per documentation:

  The special sequence \\ (two backslashes) marks the end of arguments
  and continues parsing SQL commands, if any. That way SQL and psql
  commands can be freely mixed on a line. But in any case, the arguments
  of a meta-command cannot continue beyond the end of the line.


DDL for PostgreSQL for DB2 tables (migration)

BLOB should be translated to bytea in Postgres and CLOB to text.

DECIMAL(15) is probably better done as bigint and char() should not be used at all. I would replace that with varchar(30)

I don't know what rowid is in DB2.

The with default option is invalid for Postgres. If you want a default value you need to specify it explicitly, e.g.:

field_6  INTEGER       NOT NULL DEFAULT 42,


Upgrading MySQL 5.1 to 5.5 - All tables gone

Turns out I had a mess with the my.ini files. Not sure why ibdata1 still changed, but that was the solution. The mysql workbench configuration was still pointing to the old my.ini file.