SQL Backup/Restore and Disaster Recovery solutions with VMware Is vSphere Replication safe for SQL databases?

We are a virtual environment (Cisco hardware + VMware hosts + HP san).
We have a primary and disaster recovery datacenter, with SAN-level replication already setup for every 4-hours.

There are several HA/DR solutions for SQL - alwayson, logshipping, clustering, replication etc. But we want the easiest administrative solution that can deliver good RPO & RTO. So we're looking to replicate at the VM-level instead
of SQL-level strategies.

Apparently, any VM-level snapshot/backup solution needs to be VSS (Microsoft's volume shadow service) aware to ensure highly transactional applications like Exchange or SQL Server comes back up in a consistent state after restore.

VMWare's SRM(Site Recovery Manager) gives you the choice of array based replication or vSphere Replication. I read here that vSphere Replication is VSS aware and can offer utmost 15 minute RPO, which is very acceptable for most of our applications.

Does anyone else use this for enterprise oltp applications with 15-minute DR RPO (not high-availability) ? Does it freeze IO on SQL server for unusable period of time ? Does it definitely ensure consistency when VM turns back on at the DR site ?

VMware HA is not database server high availability...

Read Brent Ozar's article on the subject.


Data dictionary best practices in SQL Server 2008 r2 We are interested in sharing the meta data and data dictionary among the team. I know that we can use the Extended Properties for this purpose, but based on my experience I've seen it gets out of date easily, because team members tend to forget to update them or skip this step.

I'm wondering if there is a more convenient way to create the data dictionary which can be maintained with the least amount of effort and time.

Thank you.

In our data warehouse, we built an application (data warehouse tracker) which maintains versions of data warehouse objects over time.  When employees in our data warehouse (project manager, data modeler, DBA, ETL developer, Quality Assurance, data warehouse specialist, report writer, etc...) work on a project to build a new data model, we attach new objects and existing objects to those projects.  Every change to a database object requires an explanation of what the object is being used for or what they are fixing in that object (new/change to data model, script being run to fix a data quality issue, ETL source code migration within informatica, defect being logged to a batch--or ETL staging or atomic run for data migration, report bug, etc...).  When there is a report request using that column (or table), we require a comment about why they are using that object.  If an defect is logged to that object, we attach a comment for the defect.  Someone reading about that object can see all changes made to that object, so they can get a good understanding of what it's used for.

We started using Meta Data Manager in Informatica, but nobody ever updated the descriptions within the data dictionary.  Because there may be different definitions of that object, we store the definition of all changes over time.  Our new approach works well, because anyone can go and correct someone else's mistake, and that keeps everyone honest.  Everyone using the object can comment on it's definition.  We still track source lineage within Informatica, but we just link to that lineage from our data warehouse tracker.


Database structure for hierarchical quiz? I'm doing a personal project where I have a quiz with a number of boolean questions. Each question will have a two answer choices "true" and "false". Based on the selected answer, a new question will be asked with the similar pattern and based on that an another question. Any question may have any or no number of sub-questions.

The last questions in the tree (E, D) and questions with no sub-questions (F) will also store the correct answer from  either "true" or "false".

The alphabets A,B,C,D,E,F are questions. A quiz can have any number of master questions (like A), sub-questions like (like B,C,D,E) and individual questions (like F).

Kindly help me design a simple database schema for for this kind of quiz. I'm open to both relational (mysql) and document (mongodb) databases.

In relational database, you should create a table called Questions which should contain following fields:

ID
QuestionString
QuestionType (Master, Individual, sub-question)
TrueCase (here you should store the ID of the next question. Since
we'll store all questions in this table, therefore each questions
shall have unique ID)
FalseCase (ID of the question to ask if answer is not correct. If ID
is null, then you should exit)

Hope it helps.


Migrating SQL Server 2008 R2 to new server I need to migrate an instance of SQL Server 2008 R2 (20+ databases) to a new server this weekend. The new server is exactly the same as the old server in all respects, including SQL Server version numbers. The only difference is that the new server has a lot more CPU power, RAM and harddrive space.

I've already successfully performed a test migration using my automated backups, but I only restored the user databases. This meant that I had to do a lot of work afterwords to recreate logins, jobs, maintenance plans, settings etc. I have all of this scripted and backed up, but its a lot of extra work that I'd like to skip in order to save time during the actual live migration. I only have a couple of hours to get it done. I need more time for testing and configuring the systems using the databases.

One of the guys here pointed out that as all the SQL Server version numbers are the same, I could just shutdown both instances of SQL Server and then copy all the *.mdf (data) and *.ldf (log) files to the new server, including those for the system databases, and then just start SQL Server again.

My questions:
- Is this wise / recommended?
- Will it work?
- Any foreseeable problems?

I'm going to go do a test of this theory now, but I'd like to know what you guys think.

This will work

Some gotchas:

@@SERVERNAME will be wrong. Fix as per this MSDN sp_addserver example
SQL Agent jobs (in msdb) may be "owned" by the old server. You'll have to script and recreate. However, this doesn't apply to SQL Server 2012 from what I can see: it used to for SQL Server 2000 which is the last time I did it....

Note that using DNS to "hide" the physical server is a useful trick. See these

https://stackoverflow.com/a/1335228/27535
https://stackoverflow.com/a/5237769/27535


Adding Secondary Data file in SQL Always on How to add the Secondary Data file to the Database which is a part of Always on availability Group in SQL Server 2012.

Just add it as you would normally do on the primary node. Make sure that all the nodes have the same disk configuration and enough disk space.


Sqlcmd command strips $ out of query variable What I am trying to do

Restore a database from backup using sqlcmd. Sqlcmd is being called by PowerShell.

What is the issue

The server folder which contains the database backups has a $ inside of the name. ie Hostname$InstanceName. When I try to restore any databases I get an error and notice it is trying to restore from \\networkshare\Hostname\DatabaseName\Database.bak

You'll notice that everything after the $ got stripped out. I believe this is due to sqlcmd's support of variables (-v parameter). I have tried to use the -x parameter (disable variable substitution) with no success.

This process works fine with folders that do not have $ inside of their names.

Does anyone know what I can try from here? Or see if I'm doing something wrong.

Thanks

Sample code

$Source_Server = "Hostname`$InstanceName"
$FQFile_DatabaseBackup = $Network_Share + "\" + $Source_Server + "\" + $Source_Database + "\Database.bak"
$Query_RestoreDatabase = "RESTORE DATABASE [$Source_Database] FROM DISK = '$FQFile_DatabaseBackup' WITH RECOVERY, REPLACE"
Invoke-Expression -Command "sqlcmd -x -S `"$Destination_Server`" -d `"master`" -E -Q `"$Query_RestoreDatabase`""

Have you tried defining $InstanceName as a string literal with single quotations?

PS C:\> $GoodInstanceName = '$myInstanceName'
PS C:\> "Hostname$GoodInstanceName"
Hostname$myInstanceName

PS C:\> $BadInstanceName = "$myInstanceName"
PS C:\> "Hostname$BadInstanceName"
Hostname

In Powershell, string literals that are defined with double-quotes will resolve variables.  However, if they are defined with single-quotes, they will not.  Since $myInstanceName resolved in the $BadInstanceName example but has never been defined, it will simply resolve to an empty string.

I suspect this (or something similar) is the problem since the error seems to do with your completed path string.


Copy FileGroup to Another FileGroup we run  Microsoft SQL Server 2008 R2 on production server.
We need create new tables and load a big amount of data on them (roughly 1 TB!)

I am wondering if it is possible to insert this dataset on a second test database server to avoid overloading the production server.

Once completed i would like to move the newly create tables in the production db in a "clever" way, avoiding to perform again the inserts.

What do you suggest?
For instance, Is it possible to load the new dataset on separate filegroup and then move the files to the production db?

Thanks

You can accomplish this a couple of different ways, but probably the best way would be with a table partition.  First you create your filegroups in the database and add at least one file to each.  Make your existing table a partitioned table by following the wizard (there's more here than meets the eye.  You will need to read up on table partitioning).  Create a staging table that matches the structure of your existing table, but without any indexes or constraints or anything. Load your 1TB of data into the staging table. Once it is all loaded, then add your indexes and contraints and stuff.  Then, you ALTER TABLE stagingtable SWITCH TO existingtable.

Since you didn't even know that table partitioning existed, you'll want to educate yourself on what a table partition is before you up, jump and implement in production:
http://www.brentozar.com/sql/table-partitioning-resources/

Not only should you read up on best practices, but you will want to try it in your test environment first:  http://msdn.microsoft.com/en-us/library/cc966380.aspx


mysql multiple unique values per primary key I have a table that stores multiple phone numbers. While the same number can exist in the table I want to ensure that a user can't have the same number twice.

**Valid**

id0001 : 5555550000
id0001 : 5555550001
id0002 : 5555550000

**Invalid**

id0001 : 5555550000
id0001 : 5555550000
id0002 : 5555550001

Not sure the best approach.  Should I make a function and apply it as a constraint?

Should I change the structure of the table?

CREATE TABLE `callback` (
    `f_unti_id` INT(10) NOT NULL,
    `phone_number` varchar(10) NOT NULL
);

ALTER TABLE `callback` ADD CONSTRAINT `callback_fk0` FOREIGN KEY (`f_unti_id`) REFERENCES `unit`(`unit_id`);

You just need a composite (a.k.a. compound or multi-column) PRIMARY KEY:

CREATE TABLE callback
(
    f_unti_id varchar(10) NOT NULL,
    phone_number varchar(10) NOT NULL,

    -- Compound PK
    PRIMARY KEY(f_unti_id, phone_number)
);

(Actually, a UNIQUE constraint on multiple columns would suffice. Check How do I specify unique constraint for multiple columns in MySQL?).

Try it a http://sqlfiddle.com/#!9/9882a/1/0


Why do I get error 1054 using trigger on INSERT saying that column isn't on the field list? I am trying to write a Trigger to register every new inserted genre into a news table.

Here are the tables definitions. They are only two, GENEROS & NOVEDADES.

This is the trigger:

CREATE TRIGGER insertarNovedades AFTER INSERT ON GENEROS
FOR EACH ROW
    INSERT INTO NOVEDADES (novedad) VALUES (CONCAT('Nuevo genero: ', genero));
$$

But MySQL don't let me use it:

mysql> INSERT INTO GENEROS (genero)VALUES('f');
ERROR 1054 (42S22): Unknown column 'genero' in 'field list'

What is wrong on the trigger code?

This works:

CREATE TRIGGER insertarNovedades AFTER INSERT ON GENEROS
FOR EACH ROW
    INSERT INTO NOVEDADES (novedad) VALUES (CONCAT('Nuevo genero: ', new.genero));

Manual has this example and the following explanation:

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
     -> FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.06 sec)

  The statement following FOR EACH ROW defines the trigger body; that is, the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering event. In the example, the trigger body is a simple SET that accumulates into a user variable the values inserted into the amount column. The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.”


Getting “SQL Server Alert System: 'Alert Severity 20' occurred” error occasionally We occasionally get following email alerts.

DESCRIPTION:    The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.

DESCRIPTION:    Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.

I tried to search online. But I haven’t got any good direction about how exactly I should proceed to troubleshoot these alerts. Should I contact application team to check buggy code or security team to check any malicious activity in this case? Appreciate your guidance.

Check the SQL Server error log and you will find a corresponding log message that includes the IP address of the originating system.  This will be the most important clue to investigating what is causing this.

It is most likely that this is being caused by security software that is scanning/probing your server for vulnerabilities.  If you find that is not the case, then it is either something that is accidentally or maliciously attempting to make a connection to whatever port SQL Server is listening on.


MySQL, RAM disk for tmpdir - how to figure out what size it should be? We regularly run a few large SQL queries to generate reports.
It seems that the bottleneck is the amount of time that is spent writing to the tmpdir, which is currently on disk.

We are upgrading to a larger server and I would like to setup tmpdir to use a RAM disk, but am not sure what a reasonable size would be to accommodate these reports.

Is there somewhere in MySQL that might log the most amount of space tmpdir it uses, or somewhere else I should be looking to calculate this.

Thanks in advance for your help.

This is a really interesting question.

You could check the

SHOW GLOBAL STATUS like 'Created_tmp_disk_tables';

But that only gives you the overall number of tables created (or the delta, if you subtract that value between two points of time), but not the actual size needed.

You could go the "logical" wayin MySQL 5.6, configuring performance_schema in order to log a certain amount of queries, enough to check how many temporary tables are being created. You can find those on

performance_schema.events_statements_history/events_statements_history_long

On the CREATED_TMP_DISK_TABLES and CREATED_TMP_TABLES. That will give you more information about the type of temporary tables created, but still no information on the actual number or rows written or the size at a moment in time.

I would probably check it the physical way (assuming a UNIX-like OS), by something like this:

sudo lsof -s | awk '{if ($1 == "mysqld" && $9 ~ /\/tmp/) size+=$7} END {print size}'

Checking the total size of files that the mysqld process has open on /tmp along time may give you a better indication, assuming you monitor that at your peak time, or a significant amount of time.


What is the name of the default schema in Oracle? When you create an object in Oracle, and do not specify a schema (object prefix), is there a name for that default schema?  I see in Oracle SQL Developer, that a newly created object shows up in the root object nodes when no prefix is specified, rather than the schema object nodes.

Creating table under schema X:

X.table_1

Creating table under default schema:

table_1

By default, it is the user you used to log in, and you can query it as below:

select sys_context('USERENV', 'CURRENT_SCHEMA') from dual;

You can change your current schema in your session however:

alter session set current_schema=USER2;

If you create a table after this, without specifying the schema, that table will be created in the USER2 schema regardless of what user you logged on with.


Monetary value in integer over decimal datatype I have came across several articles that mentioned that advise on not to store monetary value as decimal, but use integer instead.
The reasoning that it does not store actual value and will cause some rounding difference/errors.
But i have yet ever participate in any project that use an integer datatype for monetary value in database storage.

My question is:
1) Is there any solid business use case/scenario/example of using decimal datatype (19, 4) on monetary value that will result in rounding issue?
As so far i had yet to experience the above mentioned issue.

2) As we all know that fiat currency have range of decimal place of 0 to 3 depending on countries.

Imagine now that we have a system that accepts fiat currencies transfer based on configuration configured in currencies config table.

Is it common to implement a decimal constraint for each currency during insert or we leave it to application level to deal with that?

TransactionLog
--------------
CurrencyID
UserIDFrom
UserIDTo
TransactionDateTime
CurrencyAmount (decimal(19, 4)

The reasoning that it does not store actual value and will cause some rounding difference/errors

This is only an issue if you are storing decimals as floating point decimals. A float should always be considered to be an estimate which is subject to rounding errors and variability.

In most RDBMSs, using a decimal () data type is not a floating point decimal, and is not subject to the same variability.

When performing math & other computation, you will need to be conscious of rounding or truncation to ensure stored values meet accepted practices-- but you'll need to do that regardless of whether storing as an int or a decimal.

For example, take the following example on SQL Server (You didn't specify an RDBMS, but I'm most familiar with SQL Server). This is a simple example that mimics a calculation like "increase by 6.5%":

DECLARE @intmoney int;
DECLARE @decmoney decimal(19, 4);

SET @intmoney = 1234500  * 1.065;
SET @decmoney = 123.4500 * 1.065;

SELECT IntegerMoney = @intmoney,
       DecimalMoney = @decmoney;

The integer money value will be rounded to 1314742.
The decimal money value will be rounded to 131.4743.

Doing the math "by hand" will give a value of 131.47425.
* The integer value has been truncated.
* The decimal value has been rounded .

This is expected behavior based on the different data types. Depending on your application's rounding rules, one of these behaviors may be more or less desired. If you are storing important financial information, you may want to explicitly specify rounding using the ROUND, CEILING, and FLOOR functions (or equivalent, depending on platform & language). This can ensure you always have the expected behavior and do not suffer from unexpected rounding/truncating issues.


Altering column data SQL I have a set of car data, one of the columns represents the registration year. I want to change this column to age. As the data is from 2010 I assume that the following statement should calculate the age but there is an error?

Select Model, Variant, EngineSize, RegYear, Mileage, Price, Sector from   sampledata
update sampledata
set RegYear = 2010 - RegYear
where sector = 'Van Derived Cars'
and mileage > 200
and regyear != -1
and price != 999999

Can someone please help. I feel this should be very easy to do but everything I try seems to fail!

Error code 1064  :

This means you have an error in your SQL syntax.( ; missed)

 Select Model, Variant, EngineSize, RegYear, Mileage, Price, Sector
 from sampledata ;
 update sampledata
 set RegYear = 2010 - RegYear
 where sector = 'Van Derived Cars'
 and mileage > 200
 and regyear != -1
 and price != 999999;

  Mysql error  code 1175 :

If you are in a safe mode, you need to provide a key (ex. primary key) in the where clause or you can SET SQL_SAFE_UPDATES = 0;


Incremental sync for Sql Server The Change Tracking feature of Sql Server allows to identify the modified rows of a modified database copy.

This is obviously helpful for sync jobs, as the database can then be synced incrementally.

My question is: Is there a tool that uses this feature to actually do incremental syncs?

I know about

SSDT: can sync, but isn't interested in change tracking data as far as I know.
MS Sync Framework: is a framework, I'm looking for an application.

Do I really have to write something myself?

Is there perhaps some cool SQL script that does that if the source is brought in as a linked server?

What are you trying to "sync" exactly?  A subset of tables, the entire database?  Replication was designed for syncing data between databases and it offers a number of different flavors and features.  It's pretty verbose and should likely tackle just about any "sync" scenario you're looking to accomplish.  My main issue with Replication is that people often take it too far and try to use it as a fully fledged HA/DR solution when there are better approaches instead.

If you don't want to setup Replication (as it does have it's fair share of caveats and problems) and want to tap into Change Tracking or even CDC, then you'll likely be creating your own sync jobs/routines.  I'm not aware of any 3rd party tools that prefer Change Tracking or CDC over replication in this domain.


Denied updating a field that is modified within a BEFORE UPDATE TRIGGER I have a table with 4 columns:

ID
FirstName
LastName
Synced

If row contains synced information, then Synced is 1, otherwise 0.

I have a BEFORE UPDATE TRIGGER to catch when FirstName or LastName is modified to change Synced from 1 to 0:

BEGIN

IF
    NEW.FirstName <=> OLD.FirstName OR
    NEW.LastName <=> OLD.LastName
    THEN
        SET NEW.`Synced` = 0;
END IF;

END

The trigger works as expected, if row modified, then Synced = 0, but I cannot update Synced value to 1.

If I run:

UPDATE `x` SET `Synced` = 1 WHERE `ID` = 4367

I got 0 affected rows, 0 errors.

Your logic is inverted.  You are setting synced to 0 if either column is not changed.  The correct expression:

IF
    NOT(NEW.FirstName <=> OLD.FirstName) OR
    NOT(NEW.LastName <=> OLD.LastName)
    THEN

The <=> operator is an equality comparison that considers nulls to be equivalent.


SSRS appears to be ignoring Permissions set using Report Manager I have setup SSRS on SQL Server 2008 in native mode.

As an administrator I can login to report manager, upload reports and run them, and also use the Web Service URL to generate reports.

I have also created a local user on the machine, I went into Report Manager as Admin, and at the top level set permissions that should assign the local user to all roles.

When I login to the machine as that user, and then navigate to Report Manager I just get the heading for the page, but do not see any of the folders that are configured.

I've checked and the folders are set to inherit parent permissions and they are showing the newly created local user in there too.

It seems odd that I have set the permissions, yet SSRS is still not showing what I should be able to see. Is there another step I need to take other than configuring the permissions in Report Manager?

When logged in as the newly created local user:

Report Manager - Shows the heading for the page, but no folders/items

Web Service URL (http://machine/ReportServer) - rsAccessDeniedError

To give a user rights to work with SSRS they need to have permissions at the root folder (which is what it sounds like you have done), but you also have to go into the "site settings" link into  the "security" tab and add the user as (at minimum) a system user.
It is easy to do one, but not the other. Both are required to allow full access to the system.


mysqld startup/initialize permission demied - can't connect The MariaDB server (5.5.35?) which is preinstalled on my linux system came broken, and instead of trying to fix it (#1 it's out of date, #2 I might break the system in the process since it's pre-installed), I downloaded MySQL 5.7.16 (the .tar.gz version, see #2 for reason). Then I changed the data folder, logs folder, etc. to get away from MariaDB and put them in my own my.cnf file. Also made a symbolic link called '/usr/local/mysql' to my actual MySQL installation in my home folder.

I'm having trouble configuring/running mysqld as mysql user (which came with MariaDB), but it can run well as root (yes, I know the security risks but the server is only for testing).

It seems to have trouble creating some files for writing.
The following is from running mysqld with mysql user & personal my.conf:

    2016-12-05T17:22:26.420468Z 0 [ERROR] InnoDB: Cannot open '/home/me/Downloads/mysql-5.7.16-linux-glibc2.5-x86_64/data//ib_buffer_pool' for reading: Permission denied

    miscellaneous failures/server shutdown as a result

    ...

    2016-12-05T17:22:26.729869Z 0 [ERROR] InnoDB: Cannot open '/home/me/Downloads/mysql-5.7.16-linux-glibc2.5-x86_64/data//ib_buffer_pool.incomplete' for writing: Permission denied

Initializing the database gives the same error  but woth'data/mysql/db.MYI' instead of data/ib_pool.

Here are the file ownerships for mysql (I didn't provide file permissions in case that's irrelevant):

All folders except data and mysql-files: My home user
data and mysql-files: mysql

Groups are the same as the owners.

Update: changing /usr/local/mysql permissions let me initialize/start the server, but I still can't connect to it on any user (even root@localhost gets Access Denied), when  I removed /usr/local/mysql link and redirected all paths to my home dir, problem came back again.

Can someone help me use another user besides root?

It looks like permissions for your actual data/* is correct: mysql:mysql, but if you're having it access that through a symlink in your own user's $HOME, then the permissions leading up to the symlink are probably what is blocking mysql from accessing the symlink path.

What about giving mysql its own $HOME, like /home/mysql/{etc,data} (chown mysql:mysql) and place its configuration and data files within that. You could always add your user to the mysql group if you need to access mysql's config or data as your own user.


Oracle limit the number of files of audit I would like to know how to limit the number of files of audit generated in the audit directory, I have an Oracle 12c database. The AUDIT_TRAIL parameter is equal to NONE, however it continues to generate .aud files, I would like to know how to limit the number of files or if I can prevent them from being generated completely. Thank you

Connections as sysdba or as sysoper, also startup and shutdown events are always audited, whether AUDIT_TRAIL is set or not.


Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter? If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the indexed view) is there any extra contention from the indexed view against the table as those records are being added to it?

Example:

CREATE VIEW FilteredIndexedView WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2, DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 < '1/1/2019';

CREATE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexView (DateColumn1);

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view

Most of the information you will need will be found in this post by Erik Darling.

  ...SQL Server is sort of smart about some modifications.
  Why it isn’t smart about all of them is beyond me

Inserts are fine when we adapt and run your queries:

CREATE TABLE dbo.BoringTable(ID INT IDENTITY(1,1),DateColumn1 DATE,TextColumn1 varchar(50),TextColumn2 varchar (50))

INSERT INTO dbo.BoringTable(DateColumn1,TextColumn1,TextColumn2)
SELECT TOP(1000) CONVERT(date,DATEADD(day,(-ROW_NUMBER() OVER(ORDER BY (SELECT NULL))),GETDATE())),'val1','val2'
FROM master..spt_values s
CROSS APPLY master..spt_values

GO
CREATE VIEW FilteredIndexedView
WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2,DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 < CONVERT(DATETIME, '20190101', 101)
GO

CREATE UNIQUE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexedView (DateColumn1);

And perform the insert that does not match the filter:

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view

The execution plan shows that the indexed view was not added to the plan, and that the optimizer knows that it does not need to access it. There is no additional contention found.

If we do insert a date that matches the indexed view, we get this plan:

And then, when performing an update on rows that do not match the indexed view (before or after):

UPDATE dbo.BoringTable
SET DateColumn1=  '12/13/2019'
WHERE DateColumn1 = '12/11/2019';

Nothing gets updated in the indexed view, but it does bring some extra overhead as additional operators are added to the plan, to be able to update the indexed view if necessary:

The delete does not add the indexed view operators

DELETE FROM dbo.BoringTable
WHERE DateColumn1=  '12/13/2019';


Oracle CPU vulnerabilities and Database Proactive Bundle Patch I've applied the latest Oracle 12c Database Proactive Bundle Patch (DBBP).

My security team runs scans and April 2015 CPU (amongst other CPUs) continues to show up as vulnerabilities.

I need to prove that DBBP is a cumulative patch and all those old vulnerabilities are fixed.

sys.registry$history doesn't help

select ACTION,NAMESPACE,COMMENTS from registry$history;
ACTION                    NAMESPACE    VERSION     COMMENTS
------------------------- ------------ ------------------------
UPGRADE                   SERVER        12.2.0.1.0 Upgraded from 12.1.0.2.0

opatch lsinventory -bugs_fixed   or   opatch lsinventory -details
    isn't giving me what I want.

How can I find out what vulnerabilities are in April 2015 CPU and prove that DBBP has mitigated them???

The key word you're looking for is "cumulative".

Note 1962125.1 (Oracle Database - Overview of Database Patch Delivery Methods), includes this text about Bundle Patches:

Bundle Patch (BP)
- a cumulative collection of fixes to address bugs in a given feature, product, or configuration
     For example: Windows Database Bundle Patch, Database Patch for Exadata, Database Proactive Bundle Patch
- a superset of PSU

So that's all you should need to provide!  BPs include all patches previously included in earlier BPs/PSUs.


executing shell (bash) commands from within the mongo client How can I execute shell (bash) commands from within the MongoDB client?

With MySQL client:

  system    (!) Execute a system shell command.

Is there something similar for MongoDB?

There are a number of system command equivalents available:

ls()
mkdir()
hostname()
pwd()
sleep() (not a system command, but similar functionality)

It should be noted that these are all executed on the client (local) machine, not the server.  There is no general construct for executing arbitrary system commands that I know of in the shell.  To be honest, I suspect that is intentional because it would be a security nightmare even on the client side.

Allowing arbitrary commands to be executed from your product (database or not) is a massive potential security hole because instead of being vulnerable to just your own issues, you are now liable for any security bugs in any other system utility people choose to install.

Hence the potential for the nightmare - I can't say for sure, but I'll bet the MySQL devs would love to get rid of that command. Here's an example of someone patching the client to get rid of it:

MariaDB/MySQL Security Essentials – Disable System Commands


Why would adding an index on a MySQL table slow it down significantly but ok on SQL Server and PostgreSQL Version MySQL 5.7.2

I'm working with dummy data with int columns.

time: 1-10mm

products: 70,000 random ints with another 30,000 that are dupes from the 70,000

volume:  ranges from 500 - 1000

price:  ranges from 10 - 50 though the price stays within bounds of 1-5 diff for each product row data.

from the above, 10mm rows are created by randomly selecting a product and generating the required  row data

running a range query like...

select * from productdata where product >= 1500 and product <= 2000

takes about 4 seconds.

When I add an index on product using...

create index productindex on productdata(product)

The query now takes about 30 seconds.  Time is the only unique column in the table but setting that as the primary key does not help either.

On SQL Server and PostgreSQL I don't see the same issues with the same data and query using a non clustered index in each.  I only really have experience with writing queries for SQL Server so a bit perplexed by this.   I tried PostgreSQL as well to have another db to compare to.

All databases are the latest stable versions available.

Action Output (I had to decrease the range as the original query was taking too long)...

Without Index..

With Index..

Table status...

Buffer...

Explain Select...

Show create table...

CREATE TABLE `products` (
  `time` int(11) DEFAULT NULL,
  `product` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  KEY `productindex` (`product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Trying with time and product as key...

MyISAM?  InnoDB?  What is the cache size?  (SHOW VARIABLES LIKE '%buffer%';)  Did you run the query a second time (to cancel out the affect of caching)?  Do you have TEXT or BLOB columns?  (Please provide SHOW CREATE TABLE.)  How much RAM?  How big (GB) is the table (SHOW TABLE STATUS).

Probably...  Definitely... The cache for the data was not large enough to hold the products in question.  And, since the products are mostly "random", there is a lot of I/O.

Here's what happens in any(?) database vendor for that query:

Locate the 'row' for 1500 in the index.
Scan forward through the index until 2000.  (Due to BTree organization in MySQL, this is quite efficient.)
For each row, reach over into the "data" to get all the columns (SELECT *).  (This is where vendors vary.  And "Engines" in MySQL will vary.)  Since the data rows are in one order, but the index rows are in a different order, MySQL will be effectively doing 'random I/O' to get the rows.  (MySQL fetches the rows as needed.  Other vendors may sort the row-addresses first -- perhaps a benefit, perhaps a cost.)
For MySQL with InnoDB and large TEXT/BLOB columns, those columns may be stored elsewhere, thereby necessitating an extra I/O.  (Hence the admonition not to use *, but to spell out only the necessary columns.)

But...

If "too much" of the table needs to be fetched -- in particular more than about 20% of the table has product in the desired range -- MySQL will do a table scan instead of using the index.  (I don't know about non-MySQL vendors.)  This optimization is usually beneficial, but sometimes is a mistake.  The EXPLAIN SELECT .. would tell us which it did.

InnoDB "reaches into the data" via the PRIMARY KEY which is "clustered" with the data.  So it is a BTree-probe to find each row.  MyISAM has a byte address into the data file, so it is a fseek.  Other vendors work differently.

So... Is the comparison "fair"?  Do you have the PK "clustered" with the data on all vendor tests?  (Etc)

And I did not get into the caching details.  This is probably a major component of the slowdown.

I'll fill in more details after you provide more details.

After details

You are using InnoDB, not MyISAM (good).  innodb_buffer_pool_size = 8M is much  too small.  For 16GB of RAM, recommend 11G.  But even 1G would show a significant speedup since the table is smaller than that.

You say you are running MySQL 5.7, yet the 8M default contradicts that.  Did you override the default?  Please provide the version (SELECT @@version;).

It seems that there are no big TEXT or BLOB columns, so my comments on such do not apply.

Bottom line:  To get a 'fair' comparison, increase the buffer_pool setting for MySQL.  This is the most important tunable for performance.  It is not automatically set because it depends on the amount of available RAM.


Need a sum on my current column On an Oracle database, I need to calculate something like this - on column my

id yr    m    v1  v2 v3 st my
1  2015  10   1   0  0  0  0
2  2015  10   0   0  2  1  4
3  2015  10   2   0  0  1  0
4  2015  10   0   1  0  1  0

When st=1, calculate 10 (total from m row) minus total in rows v1+v2+v3 (6) = 4. So, in the second row I will put 4 and in the third row, the total of 10 is reached and I will put 0. The same in the 4th row. So I need to calculate a sum on rows until the current row. Can I do this?

The sum on v1+v2+v3 is something like this:

(sum(v1+v2+v3) over (partition by yr))

How can I find out the sum for the row I calculate my so I won't get over my maximum sum from m?

If I understand this correctly, what you want to do is successively subtract the value of SUM(v1 + v2 + v3) OVER (PARTITION BY year) from m for each row where st is 1, until m is exhausted; once the result drops to zero or below, it should show just 0.

Since the subtracted value is the same, you could just enumerate all st=1 rows and subtract sum * row_number for each row, like this:

WITH calculated AS (
  SELECT
    mytable.*,
    m - SUM(v1 + v2 + v3) OVER (PARTITION BY yr)
      * ROW_NUMBER()      OVER (PARTITION BY yr, st ORDER BY id ASC) AS res
  FROM
    mytable
)
SELECT
  id,
  yr,
  m,
  v1,
  v2,
  v3,
  st,
  CASE WHEN st = 1 AND res > 0 THEN res ELSE 0 END AS my
FROM
  calculated
;

The above assumes that m is the same across all rows of a partition.


What is the best approach to parsing a large amount of strings? I have a table with over 400,000,000 records and I am looking for advice on how to parse it quickly.

TheNameTable
(
  NameID  int primary key,
  TheName varchar(500)
)

The names are stored like this:  "FirstName, LastName"  (not my table, just what I have to work with)

I need to extract a unique list of last names.  My initial thoughts are to process the table in a series of batches (say 50,000 records at a time), using the NameID to control the batch ranges.  I would then use SQL's built in string functions to break the string at the "," and keep the right half of the string.

right(TheName,charindex('.',reverse(TheName))-1)

I have a feeling this will still take a LONG time.

Anyone out there have any other ideas?

Would it be worthwhile to simply export the data and process the file outside the database?

The Solution I went with:

As suggested, I created two computed columns.  One for first name, one for last name.  They are not persisted, as I do have limited space.

alter table TheNameTable
add LastName as substring(TheName, charindex(',',TheName)+1,1000)

alter table TheNameTable
add FirstName as left(TheName,charindex(',',TheName)-1)

I asked the admins for a temporary increase in RAM, they bumped the VM up to 32GB.

I created a new table, with columns for both FirstName and LastName.  I put a unique compound index on the columns, but specified IGNORE_DUP_KEY = ON.

I just inserted the first 1,000,000 records.  It filtered out 125,000 duplicates.  The whole statement took 9 seconds to run.

That is the speed I was looking for!

400 million names is a lot. Am I in there? ;-)

My gut level feeling says that using substring isn't going to be terribly much slower than coding up something via the CLR. I'm a SQL guy, I've done a fair amount of simple parsing in the past (2000 or 2005), and I was involved in what was going to be a very complicated parsing scheme (addresses, world-wide) written in c and called via an xproc until we found that out prototype "native" code wasn't any faster than the same thing written with tsql functions.

If you want to use a language other than tsql, I'd suggest writting a CLR in c# or vb.net. For simple things, it's not hard to code in CLR. I went from newb to having a couple of working directory and file utilities in less than one morning. There are plenty of examples of simple clr procedures on the net. and you wont have to learn anything (or install visual studio) to write it in tsql

No matter what, you will have to go through the table at least once. If you export, then parse and then put back what is not a small amount of data, that's a lot of time. Can you guarantee that your source isn't going to change in the mean time?

Here's the thing that always seems to sneak up on everyone:
What happens with the parsed data? Where does it wind up? Do you intend to update the row, perhaps there are lastname and firstname columns that you don't show in your example?

If you do, and those columns are currently null or have zero length stings in them, you may find that the update statement performance is very bad because sql may have to split pages to store the lastname.

In other words, your performance problem isn't parsing, it is storing the parsed data. Often, this is worse than inserting the data into another table. Also, all of those page splits will fragment your table and cause query performance to drop, which may enrage your dba because s/he will have to run a defrag procedure on the (large) table.

Here's one last thought:
Do you really need to store the parsed data? Can you get away with a computed column that calculates the last name on the fly? Those are indexable, with certain conditions, if you need that. Another approach would be a view that exposes the columns of the table as well as your "parsed lastname column".


Exclude certain categories from a flag column I have a table P that has a Categories column.
The Categories column is a bitwise flag; I have a Categories Table and if a row from P belongs in 2 different categories(say with Id 1 and 3), its column Category(or IntValue) would be 5, a sum of the first and third bit.
I also have a Exclusion table by Ids in P, for example, I can say that I want to exclude from a search on P with Id=1, every category of Id=2
I would like to select All P, excluding categories in the exclusion table.
For the moment I have this condition :
p.Categories & @SUM_OF_EVERY_CATEGORY_INT_VALUE
  - (SELECT COALESCE(SUM(IntValue), 0)
FROM CategoryExceptionByUser
  LEFT JOIN PCategories
    ON CategoryExceptionByUser.PCategoryId = PCategories.PCategoryId
WHERE UserId = @CURRENT_USER) > 0

Which works only if you add every category from a product in the exclusion table. I would like it to work if I add only one of the categories to the exclusion table
I am sorry if it's not clear, english is not my first language. If you need any more detail, please ask away
Here is the link for the example: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1d01677893041ecfd6054e63e5241bad
The query on table P returns Ids 0, 2 and 4, I would like it to return Ids 1, 2 and 3

For anyone wondering, the solution was :
p.Categories & (SELECT COALESCE(SUM(IntValue), 0)
FROM CategoryExceptionByUser
  LEFT JOIN PCategories
    ON CategoryExceptionByUser.PCategoryId = PCategories.PCategoryId
WHERE UserId = @CURRENT_USER) <= 0


Debug why MySQL/mariadb table is crashing I have a MyISAM table that keeps crashing. How would I figure out why it is crashing? The error messages say nothing other than that it crashed. Is there a log setting I could activate?

This is mariadb 10.0.17 on Arch Linux.

Turns out I ran into this 5-yr-old bug: http://bugs.mysql.com/bug.php?id=54828 in combination with a leftover (no idea from where) zero-byte file.

How I debugged it: I set up the query log:

set global general_log 'mysql.log';
set global general_log = 'ON';

which turned out to write to a file far below /tmp/systemd-private... because I'm running systemd. tail -f that file showed the crash happened when the application I'm running executed "OPTIMIZE TABLE".

Invoking that manually showed error message:

Can't create new tempfile: '/var/lib/mysql/zxnifltlapzlcumb/items.TMM'
Operation failed

and that happened because a month-old zero-byte file already was in that place. I deleted it, and the problem went away.


Why do user defined scalar functions require the schema? When creating a user defined table function, or a user defined procedure, you can call the function or procedure as named.

However, a user defined scalar function requires that you call it with the schema:

select dbo.doit(data);

I have not come across that behaviour in other DBMS. Why is this the case for scalar functions but not for the rest?

This isn’t just morbid curiosity, and I know asking why a Microsoft product is the way it is is unproductive.

If there is a technical reason why a schema is required for scalar functions but not for others, it is worth knowing. This is not the behaviour of other DBMS, but there may be a technical reason why it is the case here.

Because that's what Sybase/Microsoft decided when they implemented it. - a-horse-with-no-name

I think when you call any function without schema it only looks for system function or in-built functions, and if you do a built-in function with schema SELECT dbo.getdate() it only looks in user-defined functions. - biju-jose

  The question is why with scalar functions and not the rest?

Probably because when scalar functions were introduced to the product, they could enforce that from the beginning. They couldn't go back and enforce it for other objects because of the amount of existing code that would break. The same reason they've announced deprecation for statements without terminators but in reality that will probably never happen. Remember, too, that the schema prefix wasn't always the schema, it used to indicate the owner.

I was going to offer that it was necessary for the parser to differentiate your intentions in case you create a scalar function that shares a name with a (future) built-in function. Consider SELECT doit() in SQL Server 2017 and then in SQL Server 2021 they add an internal function called doit().

In other cases (table-valued functions), the parser gets more hints about what it is because it is after FROM. But this doesn't hold true anymore, because in the case of STRING_SPLIT() - added in 2016 - if I had a function called string_split() I might be surprised about any behavior change.

You could ask all sorts of "why" questions. Why do some ALTER DATABASE commands have SET <feature> ON and others are SET <feature> = ON? Why do we have both NOWAIT and NO_WAIT? BACKUP TO NUL instead of NULL? All kinds of historical things exist, check-ins with no review, left hand/right hand. The why is usually going to be irrelevant, unknown, or unsatisfactory.

In any case, does "why" really matter? What technical problem does knowing solve? We can't tell you why, you have to ask Microsoft. - aaron-bertrand

Side note: Even though you could omit the schema name for most objects, it is a better practice (when using SQL Server) to add the schema prefix to all objects used. You might see some unexpected or unwanted behaviour when you don't. A blog post about this: Bad habits to kick : avoiding the schema prefix by Aaron Bertrand - randi-vertongen


How to disable "Completion time:..." in SQL Server Messages window Every query I run in SSMS append the annoying message: "Completion time:...".

How can I disable that text?

In SSMS 18.3, they added the option to turn off the completion time.

Tools > Options > Query Execution > SQL Server > Advanced.

Uncheck the Show Completion Time checkbox.

Start a new query window and execute something...
No completion time after query finishes. Yay!


How to isolate general Oracle 10g issues through SQL Background:

I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in mind. The issue still feels too wide and general to only be caused by bad SQL. It's not really a huge database (the largest couple of tables contains like 8million rows, with less than half a k of data per row) and the server specifications are not that bad either (Ultrasparc, access(they say) to 96 hyperthreads @ 1.2ghz, 2gb sga). We've tried increasing the sga to near the size of the whole db (20gb), seeing only a few percentage is speedgains, which made us willing to rule out I/O issues. The maintenance dept cant see anything wrong HW or OS wise, but arn't too helpful either.

Question:

There are several possible bottlenecks in a DB like this. I list the ones I can think of below. Is there any perticular actions(preferably SQLs) I, with only "a few" DBA permissions, can perform to get measurable values that can help in deciding where my issues are?

Disk I/O speed. How do I get a good measurement right now?
CPU througput. How much data can 1.2ghz shovel?
Number of CPUs, How many are accessible really?
Memory / SGA size. Index swapping etc.
Sorting area size
Temp tablespace (for materializations etc)

Please suggest more categories if you think of something I miss.

first thing you should do is get AWR(10g on)/statspack(if your on an older version of the DB) running on your database so that it generates a snap every few minutes (few being say 15, 30 ..if you need more resolution, make the snaps more frequent). this will give you a system overview on IO, CPU etc and will show you the TOP SQL (in terms of IO, CPU, runtime, executions and the like). it will advise on SGA / buffer cache usage too.

From that point onwards you should start looking at the worst sql/areas and run tools like explain plans/tkprof/sql trace on sql statements and plsql profiler on any slow running pl/sql code.


Is this relation in third normal form (3NF)? I created a relation called Customer and defined it as:

I just wanted to know if it is in 3NF?

My concern is regarding address, since multiple customers may have the same address.

I thought, since there are duplicate values for address, it is not in 2NF. How can I solve this?

Should I separate name as firstName, lastName, and address as (Country, City,...)?

In the end whether the table is 1NF and 3NF depends on what the domain value of a city is.   This answers whether or not you have atomicity.

In essence what normalization means in the abstract regarding address information is quite controversial and it is not an easy area to answer and so I personally believe that this must be addressed on a case by case basis, based on the semantic use of the data.

Otherwise consider the following addresses:

418 N Bradley St
Chelan, WA 98816

How many atomic fields would this require with maximum decomposition?  So we consider this to be:

CREATE TABLE address (
    id int not null unique,
    street_segment char(1),
    street_name varchar not null,
    city_name varchar,
    state_province varchar,
    mail_code
);

That seems straight forward enough until you get an address that requires a cross-street as is common in South America, then we start to add fields. But now you have to track an address in Managua like:

Bo Altagracia km 3 1/2 carretera sur | Montoya 3c al Oe

Note that this is an ordinal address based on landmarks, distance and direction, and it does not fit into this data model at all and a normalized representation would require at least one additional table and some additional fields, and we can no longer enforce the same not null constraints.

But then this doesn't necessarily mean there is anything breaking 1NF if we just store the address as a text string up through and including the country.

If you have no reason to track countries, then one may see "London, England" as a valid representation of a city designation and then yes, we could see this as 1NF.  In essence such a table has a city domain (where "London, England" is distinct from "London, UK" and "Paris, France" is distinct from "Paris, North Carolina").  This gives you a number of possible issues, but it does not pose classic normalization problems unless you need to track countries (here the country is just an incidental part of the city's domain value, not a domain in itself).

So in this case it may or may not be 1NF depending on how you define the domain of the address to be and what you have to track it relative to.  If it is 1NF then it is also 3NF.

However if you break city and country into separate fields, then you have a normalization problem because city is dependent on country, and so to be truly 1NF you'd probably need to break out your regional hierarchies quite a bit more (a cities table, a state/province table, a country table).

A typical approach to this is exactly what you have done, which is to treat the address as a text string atomic domain, and not break things out any further.

Edit:  Another example of 1NF problems using arrays

Suppose on PostgreSQL I am storing IP addresses and will be querying on octets.  I might represent an IP address as a smallint[] array like:  array[192,168,1,101,24] instead of a cidr representation like '192.168.1.101/24' and this does not break 1NF.  Each array of smallints is distinct in its domain and each represents a single value of its domain.  This does not break 1NF because in the domain of an IP address, each array represents a single value in its domain (and this is ensured by the fact that ordinality is important).  This is a good example of why it is wrong to assume, for example, that the inclusion of complex data structures or arrays necessarily violates 1NF.

Finally if "this datatype can be decomposed and therefore it isn't atomic" breaks 1NF then so does every use of a datetime datatype....

TL;DR

1NF's atomicity requirement is violated only when the column stores two or more values within a given domain.  That is not the case in the initial question so 1NF is not violated given the information shown.  Given that 1NF is not violated and both name and address are functions of customerid, and not functions of eachother, the requirements of 3NF are met.


Oracle 12.1 optimize_adative_features = FALSE ; using new statistics In Oracle 12.1, does setting optimize_adaptive_features to FALSE mean that the optimizer will no longer use new statistics generated by dbms_stats.gather_schema_stats?

No, it just means that the Oracle optimizer won't use

  adaptive features, including adaptive plan (adaptive join methods and bitmap pruning), automatic re-optimization, SQL plan directives, and adaptive distribution methods

(quoting from the URL @JSapkota mentioned in the comments)

Oracle will still use all the information gathered via dbms_stats.gather_schema_stats -- but unless you are experiencing problems with the default value of true -- why not take advantage of those additional features?  As a side note, Oracle will, with a default installation, automatically gather statistics daily on tables that have changed enough via inserts, updates, and deletes, where "enough" is roughly 20% of the data; so you'll seldom need (or want to) manually gather statistics for an entire schema.

You may want to gather statistics manually on a particular table or set of tables after a bulk load, etc., but then just on the affected tables, and if you don't want to wait for the daily statistics gathering to do its thing.

In short, any of the Oracle dbms_stats.gather_XXX functions will provide the needed information for the optimizer.  The only time you may want to gather statistics for an entire schema is perhaps after importing a schema into a database, after a mass update of many tables in the schema, etc.


How to connect to a remote Oracle server from Windows machine using PL/SQL Developer? I understand that I need to configure database address in the TNSNAMES.ORA file but where should I put this file?

UPDATE

I defined the $TNS_ADMIN variable, created the TNSNAMES.ORA file, added new connection and restarted my machine. Unfortunately, the Database list in Oracle Logon remains empty.

However, the following error occurred when I was trying out various options in PL/SQL Developer:

  Initialization error
  SQL*Net not properly installed

  OracleHomeKey:
  OracleHomeDir:

What is SQL*Net?

You need to have SQL*Net installed in order to connect PL/SQL Dev to Oracle. SQL*Net is installed by default with most Oracle DB products (Oracle client for example). Since PL/SQL dev is a Windows app, you can see what Oracle products you have installed by looking into the registry (HKEY_LOCAL_MACHINE\SOFTWARE\Oracle). Also in PL/SQL dev settings (Tools/Preferences/Connection) you will have a list of all Oracle Homes (which allows you to specify which one you want to use if you have multiple homes).

If you already have an Oracle Home, the TNSNAMES.ora file is by default in the directory %ORACLE_HOME%\network\admin. You can set the registry key TNS_ADMIN if you want to specify another directory.


Good practices for patching SQL nodes in High Availability solution Our database structure utilizes two roles in the Window Failover Cluster Manager.

One is for FCI - switching between the active/passive node in the Primary Data center.
One is for SQL Availability Group setup to copy data from the primary data center to the DR data center.  This also use for manual failover in the case of disaster scenario occurs.

Our IT team wants to perform OS patching on these servers regularly on a scheduled down time, the application will be offline when this happens.  This helps us out a bit because we don't have to worry about failover to a node while patching another, but instead we can assume we can bring the database offline completely during patching period.
My question is what is the best way to do this since IT team probably does not know much about FCI or Database.  I don't want our databases to failover unexpectedly because during this process one of the active SQL node may needs reboot.  I'm thinking of giving them the following instruction.

RDP into the node active Cluster Group, you can find this by running
the powershell command: Get-ClusterGroup and find the OwnerNodeValue
of the "Cluster Group".

Open up Failover Cluster Manager Expands to Roles, there are two    roles one for AG and one for FCI.
Right click on each and select "Stop Role"
Perform patch, restart ect..
Once done, RDP back in and "Start Role" on the two roles to resume.

Would this work and is it considered good practice?  I am hoping to force the failover service to stop so that the SQL nodes don't fail-over to another node during a server reboot.

Approaching this by thinking about each SQL instance separately.
Patching SQLDR\Inst_B
This is a secondary replica, so it can be patched/rebooted independently.

Switch SQLDR\Inst_B to be an ASYNC replica (if it's not already)
Patch SQLDB\Inst_B and reboot (windows patches, SQL patches, etc.)
Verify SQLDR comes back up and the AG is healthy.
Switch back to SYNC if required.

Notes:  While Inst_B is offline, the T-Logs on Inst_A will be kept around to replay when Inst_B returns.  I wouldn't recommend patching Inst_B while doing index maintenance or large data loads on Inst_A.  If Inst_B is down for a long period of time (think days) then you'll probably want to break the AG replica.
Patching SQLFCIPrimary\Inst_A

Patch passive node and reboot.
Failover to Passive node.  This will cause your (hopefully brief, e.g. < 1 min) outage.
Confirm AG is healthy
Patch new passive node and reboot.
Failback if required.

Notes:  Since you are using MANUAL failover, there is no risk that you'll inadvertently failover to Inst_B.
This is the simplest way to accomplish regular patching, and just needs one or two FCI failovers.  There is no reason to stop WSFC roles.
Whether you do Inst_A or Inst_B first doesn't matter, but I'm risk adverse, so I'd start with Inst_B.


General ETL principle - ETL from unknown number of tables? AFAIK, ETL integrates data from a fixed number of sources/tables. Is it possible that ETL can integrate data from an unknown number of tables which might be added in the future, given that these tables might be of different types (customer, shipping, employee, accounting etc.) ?

EDIT -

Let A,B,C be tables. We can have the following ETL scenarios - A + B + C = 1 row or A + B + C = 3 rows

I have the first one. Later, we can add tables D,E,F,G...etc all of which might be different from each other in terms of the data they contain.

You're asking about an ETL process which accepts data from new tables without having to be adjusted?  In principal, sure, all things are possible.  In practice, writing this would be a challenge.

There are two main approaches to combining data from two or more tables: joins and unions.  These correspond to your two examples, where A, B, and C become one row (they're being joined) or three rows (they're being unioned).

Either way, your ETL process would need to have some kind of pattern with which to identify the sources.  For example, it could look for tables named "Orders" and a number.  You could write code to create dynamic SQL based on such a pattern, but having multiple nearly-identical tables differing only by suffix is generally a bad design.  There are some similar concepts, such as partitioning.

Alternatively, if your tables are substantially different, you're essentially talking about writing an artificial intelligence to recognize and combine related data.  This would be a huge project; suitable for a thesis, or DARPA maybe, but probably not a business with deadlines to meet.  Any solution it created would need to be vetted and tweaked by hand to be accurate, so why not just update the code yourself?

Is there a business goal you're trying to meet, or is this a matter of curiosity?


How can i tune below proc either by changing query or adding index I am having issues with one of the proc running very slow approx an hour:
Please pardon as I have to write entire code out of system to paste here. I will be editng the question with more details on the table schema and indexes as i get to copy that info:
SP is something like below
CREATE PROCEDURE [dbo].[testsp1]
@parameter1 VARCHAR(MAX),
@parameter2 DATETIME

AS

DECLARE @Today DATETIME =GETDATE()
IF @parameter2 IS NULL
 SET @parameter2 = Getdate()

SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

DECLARE @table1 TABLE ( tabletypeID BIGINT)

Insert into @table1
SELECT Value from stringsplit(@parameter1, '|')

UPDATE bigtable
    SET value1 = 33, statusdate = @today
from
    bigtable t
    INNER JOIN anotherbigtable t1 on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2

bigtable  is approx 800 M rows and has clustered as PK on id and NC on userid( 1st key), tabletypeid (2nd key)
anotherbigtable  is approx. 300 M rows
@parameter2 is most of times received as NULL while @parameter1 varies
cached plan looks something like below

The first thing I would do is avoid using table variables in heavy SQL operations. You should use a temp table instead. Either initially or select your table variable @table1 into a temp table then use that temp table in your update statement.
Table variables are historically known to be bottlenecks because statistics aren't maintained on them, and even with Microsoft's improvements to them in SQL 2014 they can still perform poorly. Here's a good Brent Ozar article about them: https://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/
You might also benefit from using the force seek hint on "anotherbg table". But start with the above change first.
For reference: http://nisalbi.blogspot.com/2015/11/what-is-forceseek-and-forcescan-table.html?m=1
And MS Docs on Table Hints: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
Typically query hints should only be used in very situational cases by people with experience, but I have found that with larger tables, sometimes the SQL Engine attempts to do an index scan when an index seek truly is a better choice. (Updating the table statistics on "anotherbg table" might more naturally fix your issue too.)
So it's situational and you'll need to test but it's possible a force seek index hint will help.
This is what your update query would look like with the index hint:
UPDATE bigtable
    SET value1 = 33, statusdate = @today
from
    bigtable t
    INNER JOIN anotherbigtable t1 WITH (FORCESEEK) on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2


How to generate create table script without primary and foreign key in Oracle I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL

But because this statement also generates all primary and foreign key scripts that belong to table when I try to insert my tables to another location I am getting "ORA-00942: table or view does not exist" error because I didn't create key's reference tables yet.

You can say that, previousl

So, is there any way to not include primary and foreign keys in create table scripts

This will do the trick :

EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
EXEC dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL


Is there a way to prevent special characters on schema objects? I would like to prevent the creation of objects with spaces or special characters.
Is there a way to allow only letters, numbers, and the underscore?

Thanks!

MySQL was designed to include special characters in Schema Object Names such as

database names
table names
column names

Certain characters (such as ' '(blank), hyphens (-), and other strange characters) can still be used within Schema Object Names by enclosing the identifier in backquotes (`).

According to the MySQL Documentation on Schema Object Names, there are a variety of characters that are permitted within Schema Object Names:

  Identifiers are converted to Unicode internally. They may contain
  these characters:

  Permitted characters in unquoted identifiers:

  ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar,
  underscore)

  Extended: U+0080 .. U+FFFF

  Permitted characters in quoted identifiers include the full Unicode
  Basic Multilingual Plane (BMP), except U+0000:

  ASCII: U+0001 .. U+007F

  Extended: U+0080 .. U+FFFF

  ASCII NUL (U+0000) and supplementary characters (U+10000 and higher)
  are not permitted in quoted or unquoted identifiers.

  Identifiers may begin with a digit but unless quoted may not consist
  solely of digits.

  Database, table, and column names cannot end with space characters.

You are welcome to get the source code, tweek it to check all characters in any potential Schema Object Identifier, and either allow or deny.

The only thing I can suggest is never to use backquotes when creating databases and tables.


what is the difference between first and second execution of a query? I notice that when I am executing queries the first time (i.e., just after mysqld_safe --user=mysql &), the queries are slower than when they are executed second time. When the query (source) is:
select
    s_name, s_address
from
    SUPPLIER, NATION
where
    s_suppkey in (
        select
            ps_suppkey
        from
            PARTSUPP
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    PART
                where
                    p_name
                like
                    'green%'
            ) and
            ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    LINEITEM
                where
                    l_partkey = ps_partkey and
                    l_suppkey = ps_suppkey and
                    l_shipdate >= date '1993-01-01' and
                    l_shipdate < date '1993-01-01' + interval '1' year
            )
    ) and
    s_nationkey = n_nationkey and
    n_name = 'ALGERIA'
order by
    s_name;

And I got:
> SELECT .....;
>   41.255s  -------- first time after startup
> SELECT .....;
>   6.242s   -------- second time after startup
> SELECT .....;
>   6.104s   -------- third time after startup

However, in some other times, the difference does not exist. For example, this query(source):
SELECT
    s_name, count(*) as numwait
FROM
    SUPPLIER, LINEITEM l1, ORDERS, NATION
WHERE
    s_suppkey = l1.l_suppkey and
    o_orderkey = l1.l_orderkey and
    o_orderstatus = 'F' and
    l1.l_receiptdate > l1.l_commitdate and
    exists (
        SELECT *
        FROM LINEITEM l2
        WHERE
            l2.l_orderkey = l1.l_orderkey and
            l2.l_suppkey <> l1.l_suppkey
    ) and
    not exists (
        SELECT *
        FROM LINEITEM l3
        WHERE
            l3.l_orderkey = l1.l_orderkey and
            l3.l_suppkey <> l1.l_suppkey and
            l3.l_receiptdate > l3.l_commitdate
    ) and
    s_nationkey = n_nationkey and
    n_name = 'EGYPT'
GROUP BY
    s_name
ORDER BY
    numwait desc, s_name
LIMIT 100;

I got:
> SELECT .....;
>   3m9.264s -------- first time after startup
> SELECT .....;
>   3m9.377s -------- second time after startup
> SELECT .....;
>   3m7.287s -------- third time after startup
>
> ------ This time, the difference do not exist.

MySQL version is 8.0.22
All configurations are default.
Why it is the case?
Thanks.

As others have mentioned in the comments, likely the performance improvement on subsequent runs that you saw in your first example is due to the data being cached in memory after your first run. There are multiple types of caches in MySQL, so even if you're not using the Query Cache, your MySQL instance uses the Table Cache and the buffer_pool which means the data from your query is actually cached in memory so that subsequent runs of that query don't need to fetch the data from disk again. This is beneficial if the size of the tables in your query are large.
The reason your second query didn't see much of a difference in performance on subsequent runs (though I would assume the raw table data was still cached) was because most of the runtime you're experiencing is probably due to the complexity of the query itself. Some of those complexities are from the EXISTS & NOT EXISTS that can be written in a more relationally efficient way, multiple inequality predicates, and the fact you're doing a COUNT() which may result in scans instead of seek operations, for example. The EXPLAIN ANALYZE / query plan for your second query would probably give more insight on specifically which part of it is the complexity that is causing most of the runtime.


Using table variable instead of temp table makes query execution slow I have a table with historical data about cars AutoData with combined clustered key Cas (DateTime) + GCom (Car ID). One record contains various indicators, like fuel level, vehicle state etc.

Intervals between individual records for one car in AutoData table are irregular, sometimes it is 120 seconds, sometimes few seconds, sometimes hours etc. I need to normalize the records for viewing, so that it shows one record per every 30 seconds.

I have the following script:

DECLARE @GCom int = 2563,
    @Od DateTime2(0) = '20170210',
    @Do DateTime2(0) = '20170224'

--Create a table with intervals by 30 seconds
declare @temp Table ([cas] datetime2(0))
INSERT @temp([cas])
SELECT d
FROM
(
  SELECT
      d = DATEADD(SECOND, (rn - 1)*30, @Od)
  FROM
  (
      SELECT TOP (DATEDIFF(MINUTE, @Od, @Do)*2)
          rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM
          sys.all_objects AS s1
      CROSS JOIN
          sys.all_objects AS s2
      ORDER BY
          s1.[object_id]
  ) AS x
) AS y;

--Create temp table
CREATE TABLE #AutoData (
    [Cas] [datetime2](0) NOT NULL PRIMARY KEY,
    [IDProvozniRezim] [tinyint] NOT NULL,
    [IDRidic] [smallint] NULL,
    [Stav] [tinyint] NOT NULL,
    [Klicek] [bit] NOT NULL,
    [Alarm] [bit] NOT NULL,
    [MAlarm] [tinyint] NOT NULL,
    [DAlarm] [bit] NOT NULL,
    [Bypass] [bit] NOT NULL,
    [Lat] [real] NULL,
    [Lon] [real] NULL,
    [ObjemAktualni] [real] NOT NULL,
    [RychlostMaxV1] [real] NOT NULL,
    [RychlostV2] [real] NOT NULL,
    [Otacky] [smallint] NOT NULL,
    [Nadspotreba] [real] NOT NULL,
    [Vzdalenost] [real] NOT NULL,
    [Motor] [smallint] NOT NULL
)

--Populate the temp table selecting only relevant AutoData records
INSERT INTO #AutoData
SELECT [Cas]
      ,[IDProvozniRezim]
      ,[IDRidic]
      ,[Stav]
      ,[Klicek]
      ,[Alarm]
      ,[MAlarm]
      ,[DAlarm]
      ,[Bypass]
      ,[Lat]
      ,[Lon]
      ,[ObjemAktualni]
      ,[RychlostMaxV1]
      ,[RychlostV2]
      ,[Otacky]
      ,[Nadspotreba]
      ,[Vzdalenost]
      ,[Motor]
FROM AutoData a
WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do

--Select final data
SELECT t.cas, ad.malarm, ad.IDProvoznirezim, ad.Otacky, ad.motor, ad.objemAktualni, ad.Nadspotreba
FROM @temp t
OUTER APPLY (
SELECT TOP 1 stav, malarm, otacky,motor, objemAktualni, Nadspotreba, IDProvoznirezim  FROM #AutoData a
                     WHERE DATEDIFF(SECOND, a.cas, t.cas)<=CASE WHEN Motor>120 THEN Motor ELSE 120 END
                     AND DATEDIFF(SECOND,  a.cas, t.cas)>-30
                     ORDER BY CASE WHEN DATEDIFF(SECOND, a.cas, t.cas)>0 THEN DATEDIFF(SECOND, a.cas, t.cas) ELSE (DATEDIFF(SECOND, a.cas, t.cas)*-1) +120 END
) ad

DROP TABLE #AutoData

At the first I have tried to write the script with only one table variable @temp placing the condition WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do in the last select. The script took 39 seconds to execute.

When I have used #AutoData temp table to preload data subset in a temp table like it is shown in the script above, it dropped to 5 seconds.

Then I have tried to use a table variable @AutoData instead of #AutoData - but it took again much longer - 22 seconds.

@temp table has 40320 records and #AutoData table has 1904 records for this example. But suprisingly just using #temp table instead of @temp variable made the execution slow again.

I was suprised to see such differences using or not-using temp table/variable. Appearently SQL Server could not by itself optimize the insides of the OUTER APPLY clause.

But why there is such a big difference using table variables vs. temp tables?
Is there any other way to know, what to use and not just trying it?

Execution plan with temp table #AutoData:

https://www.brentozar.com/pastetheplan/?id=B1y2x2Zcg

Execution plan with variable @AutoData:

https://www.brentozar.com/pastetheplan/?id=r1rAZnbqx

The key is in this part of your question:

  @temp table has 40320 records

In the execution plan, hover your mouse over the @temp table's scan. Compare the estimated number of rows versus the actual number of rows. (If you'd like to post the plan at http://PasteThePlan.com, we can give you more specific details. Disclaimer: that's my company's site.)

You're going to see that the estimated number of rows is really low.

SQL Server estimates that 1-3 rows will come back from a table variable (depending on your version of SQL Server, cardinality estimator, trace flags, etc.) This in turn gives you a really bad execution plan because SQL Server underestimates how much work it'll need from other tables, how much memory to set aside, etc.

Here are the two most popular ways to get a more accurate estimate:

Try a temp table instead (and look at estimated vs actual rows in the plan)
Use OPTION (RECOMPILE) on your query - which will get you a much more precise estimate, but with some very big drawbacks around plan cache visibility and CPU usage

To see me doing it live, watch the 1-hour Watch Brent Tune Queries (disclaimer: that's me, linking to a video of me) where I take a Stack Overflow query that uses a table variable, and tune it live in front of an audience at SQL Rally Norway.


log_reuse_wait_desc says ACTIVE TRANSACTION but DBCC OPENTRAN reports nothing I'm using AdventureWorks database in Simple Recovery Model on SQL Server 2016 Ent, and I'm examining the active VLFs.

Running select * from sys.databases shows ACTIVE_TRANSACTION under log_reuse_wait_desc

but when I run dbcc opentran to figure out which session, i get "No Active Open Transactions"

There does seem to be a background task running, which prevents me from taking the database offline

  Status           DBName           Command
BACKGROUND    AdventureWorks     XTP_OFFLINE_CKPT

There is also a full-text catalog (which gets crawled regularly I guess?)

How do I find out what transaction is holding up my log VLFs ?

Because this is AdventureWorks and obviously not a production server, you're probably not going to get great answers here. I'd just restart the server. (Is that the right answer? No, but we're obviously talking about a development playground box here, and I wanna get you across the finish line quickly.)

If you really wanted to troubleshoot it, try sp_WhoIsActive:

sp_WhoIsActive @show_sleeping_spids = 1, @show_system_spids = 1, @show_own_spid = 1

Just because if you're getting started with transaction troubleshooting, I'd rather give you something easier to use than DBCC commands.


Redshift DROP and TRUNCATE hang with no locks I followed this procedure to check to see if a table lock is preventing a table from being deleted, but finding none.  However, I am still unable to drop a table in Redshift.  Are there other system tables I can check to see if something is locking a table and preventing the drop?

Please try this code to identify locks over past 7 days.

This is from the link here lock_wait.sql

WITH locks AS (
SELECT svv.xid
,      l.pid
,      svv.txn_owner as username
,      TRIM(d.datname) as dbname
,      svv.relation
,      TRIM(nsp.nspname) as schemaname
,      TRIM(c.relname) as objectname
,      l.mode
,      l.granted
,      svv.lockable_object_type as obj_type
,      svv.txn_start
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start)),2) as block_sec
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start))/60,2) as block_min
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start))/60/60,2) as block_hr
,      CASE WHEN l.granted is false THEN ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM rct.starttime)),2) ELSE NULL END as waiting
FROM   pg_catalog.pg_locks l
INNER JOIN pg_catalog.svv_transactions svv ON l.pid = svv.pid
AND   l.relation = svv.relation
AND   svv.lockable_object_type is not null
LEFT JOIN pg_catalog.pg_class c on c.oid = svv.relation
LEFT JOIN pg_namespace nsp ON nsp.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_database d on d.oid = l.database
LEFT OUTER JOIN stv_recents rct ON rct.pid = l.pid
WHERE  l.pid <> pg_backend_pid()
)
select distinct * from (
SELECT l.xid
,     l.pid
,      l.username
,      l.dbname
,      l.relation
,      l.schemaname
,      l.objectname
,      l.mode
,      DECODE(l.granted, true, 'True', false, 'False') granted
,      l.obj_type
,      l.txn_start
,      DECODE(l.granted, true, l.block_sec, NULL) as block_sec
,      DECODE(l.granted, true, l.block_min, NULL) as block_min
,      DECODE(l.granted, true, l.block_hr, NULL) as block_hr
,      waiting
,      b.max_sec_blocking
,      b.num_blocking
,      b.pidlist
FROM   locks l
LEFT OUTER JOIN
      (
       SELECT relation
       ,      mode
       ,      listagg(b.pid, ',') as pidlist
       ,      MIN(block_sec) as min_sec_blocking
       ,      MAX(waiting) as max_sec_blocking
       ,      COUNT(*) as num_blocking
       FROM   locks b
       WHERE  granted is false
       GROUP BY relation
       ,      mode
      ) b
  ON  l.relation = b.relation
AND  l.granted is true
AND (l.mode like '%Exclusive%'
OR (l.mode like '%Share%' AND b.mode like '%ExclusiveLock' and b.mode not like '%Share%'))
)
-- where objectname like 'pg_%'
ORDER BY granted DESC
,       max_sec_blocking desc nulls last
,      block_sec DESC,
waiting desc nulls last
;


Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable? Expressions like NOT IN (1,2,3) are valid in a where clause:

SELECT foo FROM bar WHERE
  ((record_type NOT IN (2, 3, 7, 18, 19, 20, 21,12,13,22))

However if I want to move an elaborate expression composed of checks like the above from the WHERE clause to a SET @Var= statement, is there a way to do that without rewriting it like this:

declare @record_type int
set  @record_type  = 1

declare @ignoretype bit
IF    (@record_type <> 2)and(@record_type <> 3)and(@record_type <> 7)and
      (@record_type <> 12)and(@record_type <> 13)and
      ( (@record_type < 18) or (@record_type > 22))
     set @ignoretype=1
else
     set @ignoretype=0

I feel like it's really gross that what used to fit in one readable line is now a
gigantic gross mess.  Am I missing some syntax in SQL that is "NOT IN (1,2,3)" equivalent that works in set @var = or IF expressions? I would have written the above as:

select @ignoretype = (@record_type <> 2) ....

But you can't even use "<>" inequalities in a select statement in Microsoft SQL Server, which might be normal situation for DBAs and SQL experts, but as a relatively new SQL Server guy, I'm still finding the various syntax limits in various contexts confusing.

There is no boolean datatype in SQL Server which is why select @ignoretype = (@record_type <> 2) doesn't work.

IF @record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 )
  SET @ignoretype=1
ELSE
  SET @ignoretype=0

works fine. Or you could also do

SET @ignoretype = CASE
                    WHEN @record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 )
                    THEN 1
                    ELSE 0
                  END

Or (if you are on 2012+)

SET @ignoretype = IIF(@record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 ) ,1,0)


Execute Queries Against CMS within SSRS Reports? Is it possible to execute queries against a Central Management Server (CMS) using SSRS?

For example, I know of no way to tell a stored procedure that it is inherently supposed to call the CMS, so it would seem some sort of qualifier to indicate that you are querying CMS (and which CMS) would need to be specified.

This is not possible using SSRS, but you can use powershell to read from CMS and then use the SQLCMD cmdlet to execute queries.


Lifespan of Launchpad-Launched Exterrnal Runtime Processes When SQL Server uses SQL Launchpad to launch an external runtime (e.g. R or Python) when sp_execute_external_script is used, what's the lifespan of the external runtime process?

Does each sp_execute_external_script script execution get its own external process instance or does the external process stay around until it's been inactive for a certain amount of time (e.g. the existing process might be reused if I execute another script written in the same language using the same SQL session)?

For security reasons, Python processes are not reused. Each call to sp_execute_external_script uses its own process instance(s), at least as far as Python goes.

Since Microsoft SQL Server's Python and R integrations are very similar, I'd suspect the same holds true with R, as well, but I can't say that with 100% certainty.

Reference: reddit comment made by SQL Server engineering team program manager Tobias Ternström


msaccess split db feature linked tables We have an Access db that has around 300 linked tables (to SQL Server tables). To switch environments, we run some VBA code that re-links all these tables to the relevant server. But this seems to be very slow. Approximately 0.25 seconds for each table, so the whole process can take almost 2 minutes.

I thought that I could use the Split Database feature to create a backend db that has linked tables that link to the dev environment, and another backend db that has linked tables to the live environement. Then the process of changing environments would be to programmatically tell the front-end db which back-end db to use.

However, when I tried the Split Database wizard, the back-end db it created has no linked tables, only the local tables.

Has anyone got some suggestion as to achieve that I'm after?

I have solved the problem. Within the loop through the 300 tables, the code was creating a new ADOX.Catalog object, appending a new linked table, then setting the catalog object to nothing.

I made one simple change: create the catalog object before the loop, and set it to nothing after the loop. After this change, the relinking takes only about 10 seconds.


Is it possible to use two different drivers for sql server on the same machine. Say 2000 and 2008 I am trying to use two versions of sql server on the same machine. I have sql server 2000 driver installed on the system. Can I also install sql server 2008 driver on the same machine and use both side by side?

Yes you can use the SQL 2000 and SQL 2008 driver on the same machine at the same time.


In MS SQL how do I find out what tables have not been queried or updated in a long time? I'm maintaining a database that is being shared by a few systems. Over the years a few of those systems have been deprecated and lots of the tables are actually orphaned. Is there a query I can run to find out which tables have not been used since a certain date?

Thanks.

The sys.objects table keeps track of the last time a table's structure was modified but the closest you are going to get for finding out the last time a table's data was updated or used is by querying sys.dm_db_index_usage_stats.  This keeps track of index updates (including heap's, or tables without an index).  The catch is, it only keeps track of this data since the last time you're server was restarted.

select db_name(database_id) dbname,
object_name(object_id,database_id) oname,
MAX(CASE WHEN last_user_update < last_system_update THEN last_system_update ELSE last_user_update END) as LastUpdated
from sys.dm_db_index_usage_stats
group by database_id,object_id
order by db_name(database_id),object_name(object_id,database_id)

This sample query was borrowed from the following link:
http://www.sqlservercentral.com/Forums/Topic852747-146-1.aspx#bm852757


How can i fix the Oracle Enerprise Manager error - Agent is blocked? I have installed OEM 12c and after restarting my oms the agents got the following error:

Heartbeat Status : Agent is blocked
Blocked Reason : Plugin mismatch found between agent and repository.

I have restarted the agent but sill no success.
Any ideas there ?

I suggest you re-sync your agent using the EM GUI !
Here is a article showing how this is done !

In short :

Check your agent status :

./emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.3.0
OMS Version : (unknown)
Protocol Version : 12.1.0.1.0
Agent Home : /em_agent12.3/agent_inst
Agent Binaries : /em_agent12.3/core/12.1.0.3.0
Agent Process ID : 64164
Parent Process ID : 64110
Agent URL : https://bih002:3872/emd/main/
Repository URL : https://dcg023:4900/empbs/upload
Started at : 2013-11-13 15:26:08
Started by user : em_user
Last Reload : (none)
Last successful upload : (none)
Last attempted upload : 2013-11-13 15:26:14
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload : 72
Size of XML files pending upload(MB) : 0.11
Available disk space on upload filesystem : 91.16%
Collection Status : Collections enabled
Heartbeat Status : **Agent is blocked**

Next re-sync your agent using the GUI:

Now check you agent status again!


Basically it set a reset point each time there is a difference greater than 3 seconds between startDate and previous endDate.

Then set blocks (groups) by getting the sum of each reset point, and finally returns MIN/MAX dates and an array with the aggregated id's of every group.

WITH x AS
(
SELECT
    id,
    startDate,
    endDate,
    machine,
    case when
         date_part('second',
                   startDate -
                   coalesce(lag(endDate) over (partition by machine order by machine, startDate),
                            startDate - interval '10' second)) > 3
         then 1 else 0 end as reset

FROM
    tbl
WHERE
    machine = 6
    AND planned = false
ORDER BY
    machine,
    startDate
), y AS
 (
 SELECT
     id, startDate, endDate,
     sum(reset) over (partition by machine order by machine, startDate) grp
 FROM
     x
 )
 SELECT
     MIN(startDate) blockStartDate,
     MAX(endDate) blockEndDate,
     array_agg(id) ids
 FROM
     y
 GROUP BY
     grp
 ORDER BY
     grp;

blockstartdate             | blockenddate               | ids
:------------------------- | :------------------------- | :-------------------------
2019-01-16 07:10:46.984236 | 2019-01-16 07:39:23.786911 | {1989,1990,1991,1992,1993}
2019-01-16 07:50:06.887349 | 2019-01-16 08:00:03.221309 | {1996,1997,1998,1999}

db<>fiddle here


Understanding MS SQL Server Date Types Consider the following:

declare @dt datetime, @dt2 datetime2, @d date
set @dt  = '2013-01-01'
set @dt2 = '2013-01-01'
set @d   = '2013-01-01'

select convert(varbinary, @dt) as dt,
       convert(varbinary, @dt2) as dt2,
       convert(varbinary, @d) as d

Output:

dt                    dt2                     d
------------------    --------------------    --------
0x0000A13900000000    0x07000000000094360B    0x94360B

Now, I already understand from the documentation that datetime has a smaller range, and starts from 1753-01-01, while datetime2 and date use 0001-01-01 as their start date.

What I don't understand though, is that datetime appears to be little-endian while datetime2 and date are big-endian.  If that's the case, how can they even be properly sortable?

Consider if I want to know how many integer days are represented by a date type.  You would think you could do this:

declare @d date
set @d = '0001-01-31'
select cast(convert(varbinary, @d) as int)

But due to the endianness, you get 1966080 days!

To get the correct result of 30 days, you have to reverse it:

select cast(convert(varbinary,reverse(convert(varbinary, @d))) as int)

Or, of course you can do this:

select datediff(d,'0001-01-01', @d)

But that means internally somewhere it is reversing the bytes anyway.

So why did they switch endianness?

I only care because I'm working on a custom UDT in SQLCLR and the binary order of the bytes does seem to matter there, but these built-in types seem much more flexible.  Does SQL Server have something internal where each type gets to provide it's own sorting algorithm?  And if so, is there a way I can tap into that for my custom UDT?

See also, a related (but different) question on StackOverflow.

SQL Server does not rely on the binary order for its "own" data types. For CLR datatypes you could use the iComparable interface, but as @MattJohnson mentioned, SQL Server ignores it:

http://connect.microsoft.com/SQLServer/feedback/details/252230/sqlclr-provide-the-ability-to-use-icomparable-or-a-similar-mechanism-for-udts

Microsoft does not publish the details about how the different datatypes are stored and worked with. However Books Online explicitly states that you cannot rely on a specific binary format for a specific datatype and that the format they use might change any time. So it is a good idea to store an INT as just that and not as VARBINARY, because you might not be able to read your data anymore after the next SP.

As for the sorting: Most of the SQL Server core is written in C++. I assume internally a method similar to an iComparable is used. But again, there is no publicly accessible documentation about this available. Even if it were, you probably would not be able to exploit it because of the inherent differences between .NET and C++.


Checking for multiple identical values in a Posgresql array I have a simple table in Postgresql:

CREATE TABLE data (id integer, values integer[]);
INSERT INTO data VALUES (1, '{1,2,3,4,5}');
INSERT INTO data VALUES (2, '{1,1,2,3,4,5}');
INSERT INTO data VALUES (3, '{1,1,2,1,3,4,5}');

My basic query for values is something like:

SELECT id FROM data WHERE values >@ ARRAY[1,2];

I am trying to select rows with multiple copies of the same value, e.g.

SELECT id FROM data WHERE values >@ ARRAY[1,1,3];

Since values are compared one by one, the query above will match all 3 rows, while I would like to match only IDs 2 and 3, so ones where there are at least two copies of 1 in the 'values' array. Similarly

SELECT id FROM data WHERE values >@ ARRAY[1,1,1,2];

would match only ID 3.

Any pointers on how to proceed, or which functions to look into?

Thanks.

Note: this will only work if you make data.id a PRIMARY KEY

SELECT
    data.id
FROM
    data,
    LATERAL (SELECT DISTINCT unnest(values)) no_duplicates
GROUP BY
    data.id
HAVING
    array_length(values, 1) > COUNT(no_duplicates)

Here's an SQL Fiddle.

This works by converting your array into a recordset/table (which I've called "no_duplicates") using unnest(), and removing duplicates using DISTINCT:

LATERAL (SELECT DISTINCT unnest(values)) no_duplicates

Then I GROUP BY the original data table's ID, and compare the length of the new, filtered recordset with the old, unfiltered table. If the original, unfiltered array is bigger, then we removed duplicates so we should select that row:

array_length(values, 1) > COUNT(no_duplicates)


bulk update vs row wise update I have to update multiple rows (> 100k) satisfying a particular criterion in production environment. Since this query takes much time, it comes in slow query.  someone told me that instead of making a bulk update, we should send update query row wise.

Example-
old query:

update table_1
set column_20 = 'abc'
where updatedOn > date_sub(now(), interval 1day) and column_19 = 'xyz'

New query/method:

select auto_increment_id_column
from table_1
where updatedOn > date_sub(now(), interval 1day) and column_19 = 'xyz'

Now use a "for  loop"  for all id's extracted from above, then send below query for each id:

update table_1
set column_20 = 'abc'
where auto_increment_id_column = id_from_for_loop;

This method seems to be counter intutive, so, please confirm, if it should be done. Also how it will affect master slave replication.

dbinfo -  mysql and using statement based replication

Major Update: Our DBA recently updated the replication to row based

The Update would benefit from the composite INDEX(column_19, updatedOn).

It is almost never faster to use a client loop to do something that can be done in a single SQL statement.

Since you are talking about updating upwards of a million rows, there are other things to discuss...

One should rarely need to update more than a few rows of a table in a batch.  Perhaps column_20 does not belong in that table; maybe it belongs in a small, maybe one-row, table to avoid this?  Maybe something else.  If you care to describe the semantics of the columns, maybe I can give you more concrete advice.

The reason for the big updating seeming to take "too long" is that it is saving things in case you need to ROLLBACK.

But, if you do need to do a massive UPDATE or DELETE, I recommend doing it in chunks.  There are notes on the impact on Replication in that document.


Impact of Redgate SQL Search on Server I'm a huge fan of a lot of the Redgate tools, however at my current workplace they are very paranoid over anything interacting with live servers (in particular affecting performance).

So my question is specifically on their SQL Search tool, what impact does it have on a server? I'm assuming that there will be:

Initial indexing (what does it interact with, idea on impact on server, can it be customised to not index live servers)
Subsequent connections/refreshes (how is it determining if there is a change to be re-indexed and what performance/load impact that will have on a server)?

Answer received from Redgate support:

  Following are answers to your questions:

  When you load a database in SQL Search, it indexes and stores the
  info on cache for giving search results. To not index specific
  servers, just do not select 'All database' nor pick the database you
  do not want to index in the database filter.
  Whenever you have mode modifications or want the index to be
  refreshed, manually refresh the index from the menu in SQL Search.

  Hope that helps.

  Thanks,   Anuradha Deshpande