Overhead of decomposing complex query into multiple simple queries? What kind of overhead should I expect when replacing a single complex query with multiple simple queries?

My goal is to improve the readability and portability of all SQL code so I will be favoring simple constructs and replacing database-specific extensions with ANSI SQL whenever possible.

For example:

Imagine the client is invoking Dynamic SQL (as opposed to stored procedures)
Scenario 1: Client invokes: INSERT INTO employee SELECT name FROM user
Scenario 2: Client invokes:

Statement getNames = connection.createStatement();
try (ResultSet rs = getNames.executeQuery("SELECT name FROM user"))
{
  while (rs.next())
  {
    String name = result.getString(1);
    PreparedStatement prepared = connection.prepareStatement("INSERT INTO employee SET name = ?");
    prepared.setString(1, name);
    prepared.executeUpdate();
  }
}

Scenario 1 is not a complex query, but for the sake of argument let's pretend it is. Scenario 2 achieves the same result using multiple (simpler) queries. What kind of overhead can I expect for Scenario 2 versus Scenario 1? Is it something I should worry about or is it negligible?

UPDATE: https://stackoverflow.com/a/14408631/14731 makes a good point. Decomposing queries by hand hard-codes an execution plan instead of allowing the database's optimizer to choose. That being said, it's still not clear whether the overhead is meaningful.

As a general rule of thumb, you should give the database as much information as possible about the task that you're implementing. How does this apply to your scenarios?

Scenario 1 (INSERT .. SELECT)

The database knows that you're about to bulk-move a whole set of data from one table or from one derived table to another. It can optimise the execution given:

The cardinality of the bulk operation
The various constraints / triggers that will be invoked
The amount of storage that needs to be reserved in the new table
etc etc.

Scenario 2 (SELECT, and the N x INSERT)

The database has no clue about the various prepared statements that you are going to send after the SELECT. Even if it were smart enough to collect long-term statistics and heuristics about what's going to come after your SELECT, it would be unwise to assume anything about the subsequent load. So, effectively, this scenario is most often much much worse than the other.

There are some remarks to place, though:

Even if you have a good application-design reason to partition INSERTs like that, you should then probably batch-send them to the database
One good reason to partition INSERTs is the fact that you can have more fine-grained control over transaction lengths. It is generally not good to insert millions of records in a long-running transaction with logging turned on. So, either turn off logging, or commit after N inserts

Conclusion

The above are remarks regarding your two specific scenarios. Real-world scenarios aren't that simple, but you've also stated that in the question. The point I'm trying to make is that in many cases, you should let the database perform bulk data operations, because that's what it's very good at.


Selecting from an excel spreadsheet into SQL Server table This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express).

So for the following code snippet:

Declare @strSQL as varchar(200)
declare @file as varchar(200)

SET @file='C:\temp\file.xlsx'
SET @strSQL=N'SELECT * INTO #mytemptable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database='+@file+';HDR=YES'', ''SELECT * FROM [Sheet1$]'');'
SET @strSQL=@strSQL+N'SELECT * FROM ##mytemptable'
PRINT @strSQL
Exec (@strSQL)

EXPANDED SQL STATEMENT

SELECT *
INTO #mytemptable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\file.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]');

SELECT * FROM ##mytemptable

I get this error:

  Msg 7399, Level 16, State 1, Line 1
  The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
  Msg 7303, Level 16, State 1, Line 1
  Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Excel is 32-bit, if you are using 64-bit version of Windows it could be causing this problem. I have 32 bit drivers installed and get the same error as you. In order to install the office 64 bit drivers would have to uninstall the 32 bit drivers first.


NTFS Filesystem Fragmentation issue occur after migrating mysql 5.6 database to mariadb 10.2 I am currently experience a novel issue with a previously fine running database server. For a second time within 8 weeks, the current running MariaDB 10.2.7 has been corrupted due to Operating System Error 665 with underlying cause being the NTFS Filesystem.The Sysinternals Utility contig should a heavily fragmented .ibd file (>1Million fragments) which leads me to believe the above is the cause of the problem.

3 months ago I switched from MySQL Server 5.6 to MariaDB 10.2.6 due to better backup features. The database runs on top of a virtual Windows 2012 R2 Machine. Previously, this setup was able to handle a much larger database without so much as a sweat and was running 24/7. The underyling hardware does not post any errors, nor do the physical harddrives show any errors.

As this has happened twice in the past 8 weeks, I am wondering if something is going wrong in MariaDB? Has anybody else experienced such heavy fragmentation of the database file? And does it have to do with the SQL Server switch? Are there any settings which I have overlooked in the MariaDB my.cnf which make it play nice with NTFS?

Thank you very much for your answers!

Edit:

The exact chronology is as follows

31st of July Write the existing 500GB Database into empty MariaDB. This was done via Parsing the old database and writing into the new via INSERT Statement. This was done as a new column for legacy "flagging" was added.
During the writing process of a minor table( one .ibd file), the error occurred for the first time. This resulted in a corrupt new database. Unfortunately, at the time I thought the operating system error 665 was due to a faulty MariaDB install.

To test this hypothesis, reinstalled MariaDB on 4th of August, recreated an empty database to write in TestData and see if the error occurred again. The database ran since then till the 22nd of September. Then the operating system error 665 was logged again and the database was corrupt.

This prompted me to look more seriously at error 665 and see if the database files had the fragmentation issue. This was the case with 1M+ fragments for now only 200GB. Unfortunately, I never checked the fragmentation issue before as it had been a non-issue. The write operations involve uuid of incoming packages from multiple sources to verify origin. Additionally, the data is mainly Blob and timestamp information.

Now I am sitting on two corrupted MariaDBs. In recovery mode, the SHOW TABLE Status shows errors whereas SHOW CREATE TABLE gives an error on execution.

The my.cnf is below

[mysqld]
datadir=D:\Database\Data\
port=3306
max_allowed_packet=64M
max_connections=251
innodb_buffer_pool_size=6G

[client]
port=3306
plugin-dir=C:/Program Files/MAriaDB 10.2/lib/plugin

The machine has 8 Vcores and rund on 16GB RAM. I hope this provides you with the wanted information.

The main issue is there anything that I can set in mariadb or on the NTFS Filepartition to stop this error from occurring?

Thanks for your time!

This is a bug, which I filed and fixed, based on this question. BTW, we do have a bug system too, do not hesitate to use it directly.

Here is the bug report. https://jira.mariadb.org/browse/MDEV-13941

Basically, fragments are created because sparse file is being extended. The fix is not to create sparse files, unless user wants a table with page compression (which is an exotic feature that not many people would use). The fix will appear in the next 10.2 (i.e 10.2.10), mid-end October.

However, just the upgrade to 10.2.10 won't automatically fix  the problem for existing tables. There is something else you need to do prior to installation of 10.2.10

Stop the server
Unset the "sparse flag" on .idb files. in elevated command prompt, type

fsutil sparse setflag C:\path\to\table.ibd 0

Defragment file with contig (or other tools)


ORACLE ASM present one or more LUNs? I have the following question regarding the allocation of disks or LUNs to ASM in Oracle, specifically, I have an Oracle 18c DB in Cluster and I have created several diskgroups, now, I need to increase the size of the data diskgroup to which I only present a LUN of say 300 GB, it turns out that I have realized that it is not enough because the data volume will increase considerably, and I need at least 2 tb at the moment, I would like to know that it is recommended in these cases, to present a LUN of 2 TB, several of 300 GB ...???
My storage is completely integrated by NVMe SSD disks.
Thank you very much

First, have your storage administrator increase the size of your LUN dynamically to the desired size up to 2TB, which is as much as ASM will recognize in a single disk.
When this is completed, you will need to get your OS to recognize the change. If you are using udev rules to present your LUNs, then use the udevadm command to update the device metadata:
udevadm trigger --action=change --verbose [device path]

If desired you can tailor the udevadm command to focus specifically on your LUN device using the device path or the --subsystem-match options. Use the --dry-run option along with --verbose to confirm which devices will be checked for changes before running it for real on a production system.
Once the changes are recognized by the operating system, you must propagate them them in ASM. Login to your ASM instance with SYSASM privileges and execute the following command:
ALTER DISKGROUP [DG NAME] RESIZE;

This will resize your diskgroup to consume the entire LUN in its new capacity.
I have performed these commands on live, running production systems without issue many, many times. It is much more efficient when using an external SAN for storage to resize LUNs this way than to worry about adding separate disks. Avoid that until you hit the 2TB limit, and then make every LUN in the group the same size (2TB).


Scalar function value not being stored I created a scalar valued function that pauses for as many seconds as passed in. This function won't be used in production. I am trying to test how SQL server evaluates and creates plans if I add a UDF to a query. I am very puzzled by the difference in how it evaluates the last of my 3 queries. First here is the code to create the test function:

CREATE FUNCTION dbo.[sleep](@seconds int)
RETURNS datetime
as
BEGIN

DECLARE @sleepUntil datetime
DECLARE @dummy int
SET @sleepUntil = DATEADD(s, @seconds, getdate())
WHILE getdate() < @sleepUntil
    SET @dummy = 0
RETURN getdate()
END

Now to create a table to test queries

CREATE TABLE #table (num int)

and to insert values

INSERT INTO #table
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)

The first query takes 2 seconds to run - which makes sense assuming that SQL server stores the value returned from the scalar function.

   SELECT num
   FROM #table t
   WHERE dbo.sleep(2)=0

The second one still takes 2 seconds - so my assumption is that it does the same.

 SELECT num
   FROM #table t
   WHERE EXISTS (select TOP 1 1
                FROM  #table t1 )
   OR dbo.sleep(2)=0

The third one is puzzling - it takes 20 seconds to run, why would sql server not store the value here instead it runs it for each row? An interesting point I noticed was that when I clicked to see live query statistics it took 20 seconds to start showing the plan.

SELECT num
   FROM #table t
   WHERE EXISTS (select TOP 1 1
                FROM  #table t1
                WHERE t.num = t1.num) dbo.sleep(2)=0

To answer a question like this you need to carefully inspect the plans. I asked and answered a similar question here. It's probably worth reading through, but the most important parts are that compute scalars can be deferred and SQL Server 2016 gives us the sys.dm_exec_function_stats DMV so that it's easier to see how many times a UDF executes in a query.

Your example UDF is nondeterministic which can change how many times it's executed in a query. The use of getdate() and the lack of SCHEMABINDING makes it nondeterministic. You can verify this within SQL Server as well:

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.[sleep]'), 'IsDeterministic')

Let's go through your queries. For the first query the UDF call is in a startup expression predicate:

The filter operator is executed once over 9 rows. The startup expression predicate means that the UDF is only executed once instead of one time for every row processed by the filter. That's why this query only takes 2 seconds.

If I change the query to the following:

SELECT num
FROM #table t
WHERE dbo.sleep(2)=DATEADD(DAY, num, 0);

Now the query takes 18 seconds. In the plan we can see that the filter is no longer a startup expression predicate:

Now the UDF is executed once per row. If I create a completely new function that's deterministic:

CREATE FUNCTION dbo.[no_sleep] (@seconds int)
RETURNS datetime
WITH SCHEMABINDING
as
BEGIN
    RETURN DATEADD(DAY, 0, 0);
END;

Now the query plan changes again:

For this query, the UDF is only executed a single time. You can verify that through the UDF, by using extended events, or by making the function execution take a long time (but it needs to remain deterministic).

For the second query in the question:

SELECT num
   FROM #table t
   WHERE EXISTS (select TOP 1 1
                FROM  #table t1 )
   OR dbo.sleep(2)=0;

The UDF is only executed per query for a different reason. The results of the WHERE clause do not reference table t and SQL Server chooses to effectively cache them into a spool:

If you look at the actual plan you can see that the spool was executed 9 times but the filter was only executed once over a single row.

Your third query doesn't compile, but I assume that you meant this:

SELECT num
   FROM #table t
   WHERE EXISTS (select TOP 1 1
                FROM  #table t1
                WHERE t.num = t1.num) OR dbo.sleep(2)=0;

Now that you reference a column from t in the WHERE clause there's no longer a spool in the plan:

The UDF reference is a startup expression filter but it's still executed 9 times, once for each execution of the filter. If I change the query to use the deterministic no_sleep function then the UDF is only executed once, despite the query plan not changing at all.

To summarize, functions can be executed once per query or once per row depending on the function definition as well as the query definition. Sometimes it can be difficult to figure out how many times the UDF will execute just by looking at the plan. If you need to guarantee that a function will only be executed once you should save the value to a local variable and use that local variable in your query, along with a RECOMPILE hint if you need the parameter embedding optimization.


sql server 2016 sp1 to sp2 with latest cumulative I inherited an SQL Server 2016 SP1. I am not a DBA and work at a small company where we do not have a test environment. I know that I need to install the SP2 first.  Do I then install the last cumulative?  Are there any tools that would help me check what issues may come up after this update.

Do I then install the last cumulative?

Yes, you should install the latest cumulative update.  Some folks would advise to install the "next to latest" version.  Since the latest one just came out 2 days ago at the time of this writing, it might be prudent to wait and see if any issues arise before going there.

Are there any tools that would help me check what issues may come up after this update.

There's not a tool I'm aware of.  You basically need to go through the KB articles for each update if you want to see what's included.  And check the KB for the CU you're installing specifically to see if there are any "known issues" with that version.  Here are the KB articles for SQL Server 2016 releases: SQL Server 2016 build versions
You can also follow the SQL Server blog, which will notify you of new releases, and also if there are significant issues with a release or if one gets pulled back entirely.


CHECK constraint for array column to verify length > 0 I'm playing with postgres table validation rules and trying to set a CHECK constraint for an array column. An idea is to allow only arrays with length > 0.

Here is how I want to implement it:

create table words_table (
  id serial primary key,
  words varchar(20)[] CHECK (array_length(words, 1) > 0)
);

But looks like it doesn't work. O_o

insert into words_table (words) values ('{}');
//INSERT 0 1

How to implement such a constraint?

The crucial point you may not be aware of, quoting the manual:

  It should be noted that a check constraint is satisfied if the check
  expression evaluates to true or the null value.

Bold emphasis mine.

Using cardinality() fixes this case like a_horse explains.

Explicitly disallowing empty arrays would do the same, cheaper:

CHECK (words <> '{}')

Both solutions still allow that words itself is NULL. You might want to add a NOT NULL constraint.

Either way, be aware that NULL always passes CHECK constraints. The general solution to disallow NULL for expressions:

CHECK (<expression> IS TRUE)


Why is it faster to do four simple selects instead of one select with left joins? I need to find a single value from a table A containing three foreign keys to two other tables B and C.

For the sake of experiment, I tested two ways to query the value:

Multiple queries:

declare @start int = (select top 1 [Id] from [B] where [Day] = '2015-01-01')
declare @end int = (select top 1 [Id] from [B] where [Day] = '2017-06-14')
declare @category int = (select top 1 [Id] from [C] where [Title] = 'Hello, World!')

select top 1 [Name]
from [A]
where [StartId] = @start
    and [EndId] = @end
    and [CategoryId] = @category
    and [Day] = '2016-05-27'

Single query:

select top 1 [Name]
from [A]
left join [B] as [BStart] on [BStart].[Id] = [A].[StartId]
left join [B] as [BEnd] on [BEnd].[Id] = [A].[EndId]
left join [C] on [C].[Id] = [A].[CategoryId]
where [BStart].[Day] = '2015-01-01'
    and [BEnd].[Day] = '2017-06-14'
    and [C].[Title] = 'Hello, World!'
    and [A].[Day] = '2016-05-27'

I was surprised that the execution plan indicates that the single query is more expensive than multiple queries. When doing all five selects together, the one with left joins indicates 53%. The other four queries indicate 12% each.

Those are the execution plans:

declare @start int = (select top 1 [Id] from [B] where [Day] = '2015-01-01')`

declare @end int = (select top 1 [Id] from [B] where [Day] = '2017-06-14')

(Same as below)

declare @category int = (select top 1 [Id] from [C] where [Title] = 'Hello, World!')

select top 1 [Name]
from [A]
where [StartId] = @start
    and [EndId] = @end
    and [CategoryId] = @category
    and [Day] = '2016-05-27'

select top 1 [Name]
from [A]
left join [B] as [BStart] on [BStart].[Id] = [A].[StartId]
left join [B] as [BEnd] on [BEnd].[Id] = [A].[EndId]
left join [C] on [C].[Id] = [A].[CategoryId]
where [BStart].[Day] = '2015-01-01'
    and [BEnd].[Day] = '2017-06-14'
    and [C].[Title] = 'Hello, World!'
    and [A].[Day] = '2016-05-27'

Why is the single query with left joins slower than the first approach?

Your execution plan for the "individual queries" shows that pre-calculating the StartID, Category etc allows an index to be used efficiently on A, "seeking" straight to the record(s) you want (you have a Non-Clustered Index Seek in your query plan), having identified the given Category etc to search for.

The "single-query" with JOINs, on the other hand, needs to carry out the join between table A-B-C "on the fly" before returning the "top 1" result matching your criteria. This join will involve all the records of those tables where they match to the other related table (full clustered index scan).

If you are frequently querying by Day and Title like this, it looks like an index is missing (or statistics out of date) that allow table B and C to be searched by those criteria.

By the way, Top 1 will only give a deterministic result if you "order by" some criteria (or if your search statement is unique) - otherwise you will get the 1st row the query comes to, which may or may not be consistent between occasions when you run it.


Is it possible to have a unique key condition based on another value? I have a table with the following columns:

ID, STRING_VALUE, ENUM_VALUE, CATEGORY_IDENTIFIER

I'd like to create a condition that won't allow two identical ENUM_VALUEs, but only if their ID is also the same.

For example:

1  BLUE    4  6   --> Good
2  BLUE    4  6   --> Good
3  BLUE    4  6   --> BAD
3  GREEN   4  6   --> BAD

Is there any way to create such a constraint?

Create a unique constraint on the 2 columns.

ALTER TABLE schemaName.tableName ADD CONSTRAINT
        uniTableName UNIQUE NONCLUSTERED
(ID, ENUM_VALUES)


Validate three digits before decimal and after decimal point six digits I have columns called longitude and latitude with nvarchar data type. The user can enter something like (23.23424432),(432423.343), which should not be allowed.

Can anyone help me to validate this in SQL Server?

Data that can be entered by user: Ex: 0, 12, 101.0, 111.022222
Data that can’t be entered by user:  Ex: 1111, 111.2222222, 1234.1111

It should accept only this format: (232.343244)

Below code is accepting any numbers:

 + CASE WHEN (CAST(A.GPS_LATITUDE AS NVARCHAR) IS NOT NULL AND CAST(A.GPS_LATITUDE AS NVARCHAR) <>'' AND CAST(A.GPS_LATITUDE AS NVARCHAR) NOT LIKE'%*%')
AND ISNUMERIC(CAST(A.GPS_LATITUDE AS NVARCHAR)+'e0') = 0 OR (LEN(A.GPS_LATITUDE)>20)
 THEN 'Invalid Latitude. ' ELSE '' END
+ CASE WHEN (CAST(A.GPS_LONGITUDE AS NVARCHAR) IS NOT NULL AND CAST(A.GPS_LONGITUDE AS NVARCHAR) <>'' AND CAST(A.GPS_LONGITUDE AS NVARCHAR) NOT LIKE'%*%')
AND ISNUMERIC(CAST(A.GPS_LONGITUDE AS NVARCHAR)+'e0') = 0 OR (LEN(A.GPS_LONGITUDE)>20)
 THEN 'Invalid Longitude. ' ELSE '' END

In my application we had a download template button. When we press the download template button, it downloads a template with only a header - with columns like   LONGITUDE and LATITUDE. We need to enter manually like 234.234325 but user can enter 2342.234242 or 4353453.123213 and save the Excel file.

In the application UI, there will be an upload button. Pressing the upload button sends the data to the database and it validates in a stored procedure. In the stored procedure, the above lines of code validate the data. We need to add decimal number validation like (432.342342) for above code.

You're better off using a DECIMAL(9,6) data type in the database.

And then you've validate with a simple numeric BETWEEN

However, if you insist on a character type, use CHAR instead of NCHAR - you're only accepting periods and numbers, so there's never any "Unicode"; cut your space use in half.

And then you could validate with something like

a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9]' ESCAPE '~'

Note that doing this with character types is both annoying and not nearly as performant.

Note that the leftmost digit is limited to 0-3, since you'd never have a latitude or longitude starting with a 4 or higher (I'm assuming degrees).

Since you're on SQL Server 2008 R2, you may also want to read the Stackoverflow question Why use the SQL Server 2008 geography data type?.  A very brief summary is: DECIMAL(9,6) is significantly smaller, and for a simple SELECT Lat, Long is significantly faster.  However, to do accurate proximity searches (which is often why coordinates are stored), distance calculations (also often why you need them), or areas and the like, the geography type is much simpler and almost certainly more accurate.


Does Express store any statistics to indicate when performance is held up due to being Express? I am looking for business reasons to upgrade from Express to Standard. as per my question here

Does SQL Server store internally any information about when it is held up due to edition / licensing features such as number of available cores?

What queries could I run to find this out?

SQL Server isn't going to tell you specifically that there is a performance limitation due to it being Express Edition.  But in general, if you start collecting and monitoring wait statistics, you'll see whether there are any bottlenecks due to insufficient CPU or memory.  So if you've given SQL Server Express the max number of CPUs and memory, then bottlenecks in these areas generally indicate that it's being limited by the limitations of the edition.  Technically, you could give it faster processors to help overcome the CPU limiation, but I assume that's not an option.

See SQL Server Wait Statistics: Tell me where it hurts to get started understanding what you need to be monitoring.


SQL Server 2012 - login mapping to user I'm still struggling a bit in moving from Oracle to SQL Server, especially with the login-vs-user thing.  I've restored a database from a client site so we can have a development copy. I want to set up a login with that new database as the default db, and map it to a user of that database. But everything I've tried results in a "user or role already exists" error. I'd like for this login to only have access to this one particular database.  I've read about users and logins, but I'm obviously missing something here.  Can the login have the same name as the database user?  What I've read seems to imply it can, but I get an error.

Harry

It is pretty simple, you just do the following two steps

1st: in sql server, create a new login using the user name in that database, like

use master;

create login [MyUserName] with password='mypassowrd123$', check_policy=off;
go

2nd: map the login with the user name inside that particular database, let's call it [xyz]

use [xyz]
alter user [MyUserName] with login = [MyUserName];
go


Multiserver administration - passing maintenance plans to clustered instances SQL Server 2019 MSX/TSX environment. TSX servers are clustered instances. MSX is not. There is a SQL integration service on each of the nodes in the cluster. I create a maintenance plan on the MSX and when i try to push it to the TSX i get this error:

Message
[000] Failed to import Maintenance Plan 'MaintenancePlanMSA' from MSX: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Parameter 0 is incorrect for this statement.
The statement has been terminated.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DiskFile.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
--- End of inner

I tried to add all agent service users as sysadmin, didn't work.
The funny thing is that the job, under the maintenance plan, appears on the TSX.
When i create a standalone job and push it to the target, the job appears on the TSX(?!).
Hope someone can help.
Thank you in advance.

JohnnyP.
I know that a long time has passed - but maybe someone will need it.
This error is related to the sys.dm_os_enumerate_filesystem() function launched without the @Path parameter.
In my case, the error appeared due to the fact that I did not specify the folder for the job execution log.
If you create a maintenance plan by wizard, then you need to tick  the 'write and report to a text file' and select folder on the "Select Report Options" window.


Getting a "Request to run job refused because the job is already running" When no job is running? So have an ETL job that runs through integration services that I start with a SQL job name StartStageStore. The last step of this job is to start another job and report success. The job this calls to is referred to as LoopStageStore. All LoopStageStore does is execute a stored procedure that Restarts the StartStageStore job all over again this way we are constantly pulling source data into our system. This job has been running for sever weeks now without failure. Until the last two nights I started getting this error "Executed as user: AgentUser. SQLServerAgent Error: Request to run job Stage/Store AgentUser refused because the job is already running from a request by User AgentUser. [SQLSTATE 42000] (Error 22022).  The step failed."

This should not be possible as the run StartStageStore job finishes and is no longer running when LoopStageStore has started.

Here we can see the last step executed at 12:19:14.

And the Next Job Fails at 12:19:15 Saying that StartStageStore is currently running. I already have a workaround for this where I just throw a Delay on the Second job. I'm just wondering if anyone knows why this would happen.

BEGIN
WAITFOR DELAY '00:01';
    EXECUTE dbo.LoopStageStore
END; GO

Alright so the problem I was having was that the write time to the log file for job completion occasionally takes longer than it takes to run the stored procedure that restarts this loop.

The solution to this is my original workaround.

In case anyone wants to use it here is the code.

BEGIN
WAITFOR DELAY '00:01';
    EXECUTE dbo.LoopStageStore
END; GO

Just make it wait a little bit.


Logical read count varies In this post the writer runs a query several times.  I notice the logical reads vary a little across executions.  There is a difference of about 2 pages in a total of a few thousand pages read.  It seems clear to me from the context that there would be no write activity in between times.  If the plan had changed I would have expected a larger variation than a fraction of a percent.

Q: what factors will cause SQL Server to report different logical read counts for the same query in the absence of data writes?

What factors will cause SQL Server to report different logical read counts for the same query in the absence of data writes?

For a heap table, there's no mechanism I know of that would cause this.

I notice that the author's reported logical reads only vary when compression was used. This suggests that the table rebuild was included on each execution. The rebuild statement does not specify MAXDOP = 1, so a parallel plan would have been generated. The way rows are distributed across threads in the rebuild at runtime is not deterministic (it depends on timing) so the final structure of the heap would vary. This is the only way I have been able to reproduce the published results.

For a clustered table (or a seek/scan of an index structure in general), the logical reads can vary due to read-ahead activity. Read-ahead reads pages from the upper levels of the b-tree to identify pages to read in advance of the scan, resulting in a varying number of 'extra' logical reads, depending on timing and the characteristics of the storage system. Disabling read-ahead with global trace flag 652 can be used to demonstrate this.

I mention the read-ahead aspect only for completeness, since the table in question is a heap. Heap scans do use read-ahead, but this is driven from the IAM pages. IAM pages do not form part of the main table structure (unlike the upper levels of a b-tree) so these extra reads are not reported by STATISTICS IO.


How do I set a geography point to be the North Pole? I tried to execute this query on my SQL Server 2008 R2 instance:

UPDATE ITEMS
SET GEOG=geography::STPointFromText('POINT(0 90)', 4326);

SQL Server returned the following error:

System.FormatException: 24201: Latitude values must be between -90 and 90 degrees

I also tried POINT(0 89.99) and got the same error.

How do I set a point to be the North Pole?

Try this instead, with (or without) decimal points

DECLARE @a geography
SET @a=geography::STPointFromText('POINT(0.0 90.0)', 4326)

if this works - then something wrong with the ITEMS table


Database denormalization: multiple rows into single column. How bad is it? We have few join-heavy queries to our SQL Server 2008 database and in an attempt to optimize them I thought of having a view that would produce this

OrderID     Number    Traveller
1           102       1015;John Doe|1016;Jon Appleseed

out of

OrderID     Number
1           102

TravellerID    OrderID    FullName
1015           1          John Doe
1016           1          Jon Appleseed

My question is: how bad is it from a DBA's standpoint? On the one hand, it gets the job done, but this solution really bothers the purist side of me.

It depends on how the views are going to be queried.  If the purpose is to do something like

SELECT OrderID from dbo.vw_Orders WHERE Traveler like '%Jon Appleseed%'

then you should NOT do this.  If you are just using the view to return a list that will be parsed by your application, then it may not be so bad.


How to alter column from float(8) to numeric(64,8) I am working on a website that store lots of numbers in a very specific precisions (8 digits).
Most of the columns are using numeric(64,8), but I discovered that a handful are using float(8) instead, and it's causing a lot of computation issues.

How can I alter those float columns to numeric(64,8)? How do I cast the data?

Thanks!

ALTER TABLE your_table
   ALTER COLUMN the_float_column TYPE numeric(64,8);

More details in the manual: http://www.postgresql.org/docs/current/static/sql-altertable.html


Why is this query slow, it seems simple I have a very typical table of user information for my application, with an average set of columns (ints, varchars, etc).

In this table are two varchar(100) fields:  emailAddress and emailAddress2.

I'm extremely perplexed at why one of my queries is so slow (5+ seconds) and in testing some alternates they are so fast (<= 0.02 seconds).

This query is fast (matching on emailAddress field):

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress = 'user@domain.com'

This query is also fast (matching on emailAddress2 field):

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress2 = 'user@domain.com'

This query is NOT fast (matching on either emailAddress or emailAddress2 field):

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND (u.emailAddress = 'user@domain.com' OR u.emailAddress2 = 'user@domain.com')

I have 2 indexes, each on the emailAddress and emailAddress2 field.

(EDIT) Here is the EXPLAIN output:

id                   1
select_type          SIMPLE
table                u
type                 ref
possible_keys        active,emailAddress,emailAddress2
key                  active
key_len              1
ref                  const
rows                 166808
Extra                Using where

Any idea why the 3rd query above is so slow? Suggestions for doing it another way? I need to match on a handful of fields and then get all users EXCEPT ones where their email address is in emailAddress or emailAddress2 (the queries above are simplified for my post).

This is driving me crazy! (and negatively affecting my app's performance).

Thanks

The query is slow because first, this query has an OR condition which cannot be optimized with a single index (while conditions with only AND can) and second, because besides the emails, it has to check the isActive=1 condition. So it prefers to do a table scan. The other possible path would be to use both email indexes with the Index Merge Union Access Algorithm but for some reason the optimizer does not choose this plan.

One solution would be to alter the two indexes you have, appending the isActive column at the end:

ALTER TABLE user
  DROP INDEX the_name_of_the existing_index,
  DROP INDEX the_name_of_the_other_index,
  ADD INDEX emailAddress_isActive_IX
    (emailAddress, isActive),
  ADD INDEX emailAddress2_isActive_IX
    (emailAddress2, isActive) ;

If it still slow (and/or does not do an index merge but prefers the table scan), you could try to rewrite the query with UNION:

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress = 'user@domain.com'

UNION

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress2 = 'user@domain.com' ;

Another option - without necessarily adding/altering indexes - is to nest the query into 2 layers, keeping only the email conditions in the internal layer, essentially forcing MySQL to consider using index-merge with the two available indexes:

SELECT userId
FROM
    ( SELECT userId, isActive
      FROM user AS u
      WHERE emailAddress = 'user@domain.com'
         OR emailAddress2 = 'user@domain.com'
    ) x
WHERE isActive=1 ;


use different odbc conector to connect mysql server We found out Mysql odbc connector is released under gplv2 license. We have a commercial application (in c#) that connects to mysql (using Mysql.data) so we are supposed not to use mysql odbc connector because we would have to release our source code.

How can I connect to MySQL usign a method that does not involve using a gplv2 connector in order we dont have to publish our source code under the same license ?

My employer, CData Software makes an ODBC Driver for MySQL: http://www.cdata.com/drivers/mysql/odbc/

Since you're working in C#, you might also want to take a look at our ADO.NET Provider for MySQL: http://www.cdata.com/drivers/mysql/ado/


Potential risks of users connecting to SQL Server, through Excel, as sysadmin? I recently discovered that a large swath of the finance department is using Excel to connect to my SQL Server 2000 instance with an account in the sysadmin role. What are my current risks that I should immediately communicate to the powers that be?

Pretty much everything.

I'd start with their potential ability to use xp_cmdshell (and sp_configure if they can't, so then they can ... and whatever the account returned by xp_cmdshell 'whoami.exe' can do....), then move onto their ability to do drop database.

Further risks include not just finance users being able to do these things, but any program on a finance machine gaining access to your sysadmin connection credentials...

(Other potential risks include the risk of discovering that one of TPTB set it up this way)


Treating certain Arabic characters as identical In Arabic we have characters like ا (alef) and أ (alef with hamza).

Users write them interchangeably and we want to search them interchangeably.  SQL Server treats them as separate characters. How can I make SQL treat them as the same character?

I thought to replace any أ (alef with hamza) with ا (alef) at insertion but we have a lot of alternatives in Arabic language not just ا (alef) and أ (alef with hamza).

I tried Arabic_CI_AS and Arabic_CI_AI but that doesn't solve the problem.

Here is a script to regenerate the issue:

CREATE TABLE [dbo].[TestTable] (
    [ArabicChars] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
    (
       [ArabicChars] ASC
    )
) ON [PRIMARY];

INSERT INTO TestTable values (N'احمد');
INSERT INTO TestTable values (N'أحمد');

SELECT *
FROM TestTable
WHERE ArabicChars like N'ا%';

The result is:

ArabicChars

احمد

(1 row(s) affected)

The desired result would be both of the rows we inserted.

i did few tests and i guess it is a work around but can get your job done, since SQL it self isn't helping much.

if you notice that the unicodes of these characters are close to each other

select unicode(N'أ')
  = 1571

select unicode(N'ا')
  = 1575

select unicode(N'إ')
  = 1573

so between أ and ا , its from 1571 to 1575
or if you want to make sure you get every thing in between

make sure you include from 1569 to 1575

which are

Select NCHAR(1569) = ء
Select NCHAR(1570) = آ
Select NCHAR(1571) = أ
Select NCHAR(1572) = ؤ
Select NCHAR(1573) = إ
Select NCHAR(1574) = ئ
Select NCHAR(1575) = ا

So to make sure that you include every thing similar in your search you can use regular expressions

SELECT *
FROM TestTable
WHERE ArabicChars like '%[ء-ا]%'

so in this case you get all characters between ء and ا
which include all those between 1569 to 1575

so in this case if your table has

 CREATE TABLE [dbo].[TestTable]  (
    [ArabicChars] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,
)
INSERT INTO TestTable values (N'احمد');
INSERT INTO TestTable values (N'أحمد');
INSERT INTO TestTable values (N'إحمد');

the query above will get them all.

but you will notice something funny

if you have your column as a primary key

CREATE TABLE [dbo].[TestTable]  (
    [ArabicChars] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
    (
       [ArabicChars] ASC
    )
) ON [PRIMARY];

you wont be able to insert these 2 records

INSERT INTO TestTable values (N'أحمد');
INSERT INTO TestTable values (N'إحمد');
INSERT INTO TestTable values (N'ءحمد');

because the ء,أ,إ
are all to SQL are part of hamza which is ء

So if you run the query

SELECT *
FROM TestTable
WHERE ArabicChars like 'ء%'

it will show you

أحمد
إحمد

so to get the long story short

to SQL أ is not = to ا because its 2 different letters
hamza and alefp

but ء = آ = أ = ؤ = إ = ئ

they are all Hamza  ء


SQL Server : View names I'm coming from an Oracle background and came across code to create a SQL Server view which I've never seen before.

We have a view called viewA (for example)

Within the script to generate the view I see this code

select columnA, columnB, columnC
from tableA as vw_tableB

The question I have is, what is the view really called?

Is it viewA or vw_tableB.

The other question I have is what is the rationale behind this?

The view is called whatever is listed in the CREATE statement, which would look like this in the script.

CREATE VIEW viewA AS

The vw_tableB is an table alias
https://technet.microsoft.com/en-us/library/ms187455.aspx

  The readability of a SELECT statement can be improved by giving a table an alias, also known as a correlation name or range variable. A table alias can be assigned either with or without the AS keyword:


Getting 2 columns from sets of 2 rows in Postgresql So I have my data organized as:

id(bigint) | report_item (varchar) | report_value (numeric) | stock_id (uuid) | date

1 | netIncome | 100 | stockid1 | 2019-05-01
2 | totalRevenue |1000 | stockid1 | 2019-05-01
3 | salesExpense |20 | stockid1 | 2019-05-01
4 | totalAssets | 10000 | stockid1 | 2019-05-01
5 | netIncome | 30 | stockid2 | 2019-04-23
6 | totalRevenue |600 | stockid2 | 2019-04-23
7 | salesExpense |15 | stockid2 | 2019-04-23
8 | totalAssets | 900 | stockid3 | 2019-04-23

So for each stockid, date pair, there is only one of each different report_item (there's not 2 different totalAssets for X date, Y stock id). There is a totalAssets for each stockID for every quarter (which aren't always on the same date).

What I want to do is return stock_id, date, totalAssets, totalRevenue as columns, effectively merging the 2 rows (one for totalAssets, totalRevenue) for each date/stock_id pair into 1 while discarding any rows where report_item is neither totalAssets or totalRevenue.

So this is what I did to turn turn the totalAssets and totalRevenue rows into columns, which gets rid of rows with other report_items, although for each stock_id, date pair there are still 2 rows. What I want is to have them in one row.

select date,
       stock_id,
       (case when report_item = 'netIncome' then id end) as netIncome,
       (case when report_item = 'totalRevenue' then id end) as totalRevenue
from report_items where report_item IN('netIncome', 'totalRevenue') order by date desc limit 100

Returns:

date | stock_id | netIncome | totalRevenue
2019-05-01 | stockid1 | 100 | null
2019-05-01 | stockid1 | null | 1000
2019-04-23 | stockid2 | 30 | null
2019-04-23 | stockid2 | null | 600

You can use a self-join for that:

select ni.date,
       ni.stock_id,
       ni.report_value as net_income,
       nr.report_value as total_revenue
from report_items ni
  join report_items nr
    on ni.stock_id = nr.stock_id
   and ni."date" = nr."date"
   and nr.report_item = 'totalRevenue'
where ni.report_item = 'netIncome'
order by date desc limit 100


Single Drive vs Multiple Drives Generally bottleneck of RDBMS (I am a MySQL user) performance is disk access. SSD provides great performance compared with conventional spindle drives.

Question : Is it possible to improve performance by attaching multiple drives of reduced space, because this way more heads will be available to read data?

Like replacing 2TB 7.2k RPM drive with 4 500GB 7.2k RPM drives?

This could depend a great deal on the storage engine.

For MyISAM, I think it would be a great idea because you can make data as contiguous as a you want. That would benefit queries involving bulk operations and large range scans. The toolset for compressing and repairing MyISAM to improve the table format can also make access a little better. IMHO concurrent INSERTs could thrive in such a disk environment.

InnoDB is a completely different story.

If you have innodb_file_per_table disabled, data can be scattered throughout ibdata1 and thus scattered amongst the multiple disks. You could run OPTIMIZE TABLE against an InnoDB table and make the data and index pages contiguous, but that could cause two problems:

Makes the ibdata1 grow
Deteriorate performance over time

Don't forget the four types of data that reside in ibdata1:

Table MetaData
Table Data Pages
Index Data Pages
MVCC Data

There is constant writing of MVCC Data to protect data integrity. There is also constant interaction with ib_logfile0 and ib_logfile1 (redo logs). There is constant data flushing every 10 seconds from the InnoDB Buffer Pool back to the . Overall, it is safe to say that the spread of data across multiple disks and the I/O performance can show marginal-to-modest performance improvement until there is enough fragmentation within ibdata1.

Now, if innodb_file_per_table is enabled, things could be a little messier. Here is why:

Once you cleanup ibdata1 and separate all InnoDB tables from ibdata1, you will have a smaller ibdata1. However, each access to an InnoDB table requires navigating its use through ibdata1 ALL THE TIME via Table Metadata. You literally cannot semantically separate the table metadata (ibdata1) from the table (.ibd). Wherever ibdata1 lives, disk I/O must always pass through ibdata1.

Overall effect: Table fragmentation is limited to .ibd files and can be reduced and reorganized with OPTIMIZE TABLE, but no additional I/O performance enhancement is really achieved.

What about putting all .ibd files into a different set of disks apart from ibdata1 and using symlinks? According to Percona, that creates more mayhem.

CONCLUSION

When it comes to using multiple drives over a single drive, I highly favor MyISAM over InnoDB.


Using desktop computers to manage an SQLite database on a fileserver? Sometimes, it can take months to get a proper database server up and running in large companies. They have all kinds of policies, which means that this process takes months. An alternative in the meantime is to place an SQLite database directly on a file server. This works nicely for a limited number of users (I have had success with serving 100 users, using one such database), and it is only recommended for non-critical data. Now I have been thinking about ways to get a database, which is placed on a file server to be able to handle, say ten times as many users. The basic idea I have is to utilize the many, mostly idle desktop computers around the company. Again, the data would be non-critical, database access could be slow, and uptime of 99.0% is sufficient.

Do you see any way of utilizing these, mostly ideal desktop computers to help reduce the load on the file server? Do you have any idea of DBMS, which would be most suited, SQLite was just my first thought. It would be best with an open-source, one so I could hire someone to adapt it for this purpose. Alternatively, I would be interested in building a DBMS for this purpose from scratch.

I know that any experienced DBA will pull their hair out over this question, but would really have value due to the policies of these companies.

The issue with this is that as you scale SQLite, you run into the fundamental problem that it doesn't have row-level locks, only table locks. The "thing" you run out of first isn't a raw resource like CPU or IO or memory - it's that your app will get bogged down in lock management. You can have lots and lots of users sharing an SQLite database, but only your readers can tolerate waiting for your writers. If this database is going to be on a a shared drive, then that will only make your scalability problems worse as it introduces additional latency, making the lock acquire/release cycle longer!

Also, in a business, there is no such thing as "non-critical data" unless you are literally talking about people MP3 or recipe collections! If people use it to get their work done and for whatever reason it "goes away" then they will wonder why, and perhaps escalate it up their reporting line. I totally sympathize with how long it takes to get a "proper" database server (be aware that the DBA feels exactly the same way, when he's waiting for the procurement department to buy the servers!) but unless you are prepared to support something forever, don't deploy it. Or if you do, develop it with something like the free versions of Oracle or SQL Server, so that when the time comes, it can easily be migrated into the datacentre.


Overlapped I/O error installing SQL Server 2008 R2 SP2 Express A user of our software is attempting a normal installation on Windows V8.1 x64 (theoretically completely updated). As part of the install, SQL Server 2008 R2 SP2 is normally installed as the back-end database.

This one user is currently getting an error 997 - Overlapped I/O operations is in progress. The relevant portion of the log file:

MSI (s) (40:90) [09:36:49:634]: SECREPAIR: New Hash Database creation complete.
MSI (s) (40:90) [09:36:49:634]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:642]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:643]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:643]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:643]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:643]: SECREPAIR: Crypt Provider not initialized. Error:997
MSI (s) (40:90) [09:36:49:643]: SECUREREPAIR: Failed to CreateContentHash of the file: Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x86\Microsoft.SqlServer.Configuration.SqlBrowserExtension.dll: for computing its hash. Error: 997
MSI (s) (40:90) [09:36:49:645]: SECREPAIR: Failed to create hash for the install source files
MSI (s) (40:90) [09:36:49:645]: SECUREREPAIR: SecureRepair Failed. Error code: 3e554415638
Action start 9:36:49: ProcessComponents.
Error 997. Overlapped I/O operation is in progress.

I have looked into the problems with KB2918614, and this patch is apparently not even installed - at least it doesn't show up in the list of installed fixes.

I disabled the running antivirus, and the error still occurs.

I did a manual install after downloading a fresh file from Microsoft - error still occurs.

I am now at a bit of a loss. I will continue searching for an answer online, but with all of the experience here - does anyone have any specific suggestions: things to check, things to install, things to disable, random voodoo idols to pray to, whatever.

I eventually located an answer to this. There are multiple steps that you need to perform to ensure that this does not occur, and any one of the items on the list can cause the issue.

If any of these MS patches are installed, delete them. Any one can cause this issue

KB2918614 (The classic cause, but not the cause in this case)
KB3000988
KB3008627
KB3072630

Rename or delete the folder C:\ProgramData\Microsoft\Crypto\RSA\S-1-5-18
Rename or delete the folder C:\Users\[username]\AppData\Microsoft\Crypto\RSA\S-1-5-18

In this particular case, one patch (KB3072630), and the two files all needed to be dealt with to fix the issue - or, to be more precise, I dealt with these three items before the issue was fixed.


How do I load data from a single table to multiple tables & keep referential integrity Using SQL Server 2008R2.

I have a single table with a bunch of columns.  I have built a new database with multiple tables to copy the data too.  How do I copy the data, and still keep the relationships?

For example, I have a table with columns such as: StudentFirstName, StudentLastName, TeacherName, Class1, class2, class3, class4, AdvisorName, etc.

I want to copy that to multiple tables, such as:

Student - StudentId, FirstName, LastName

Teacher - TeacherId, FirstName, LastName

StudentTeacher - Id, StudentId, TeacherId

etc.

When I insert the data into the Student and Teacher tables, I need to grab the ID fields from them (StudentId, TeacherId) and load those into the the StudentTeacher table, in the correct order.

Is this best done with SSIS? If so, will I need a bunch of data flows because of the "one source, one destination" thing?

If I do it with a script, I'm thinking the OUTPUT clause might help, but I have not yet figured out how to make that work.

I have tried this:

INSERT INTO Student (Name)
OUTPUT Inserted.StudId INTO dbo.StudentTeacher (StudentId)
VALUES ('Jimmy'), ('Bobby'), ('Nanacy'), ('Suzie')

INSERT INTO Teacher(Name)
OUTPUT Inserted.TeachId INTO dbo.StudentTeacher (TeacherId)
VALUES ('Mr Jim'), ('Mr Bob'), ('Ms Nancy'), ('Ms Suzie')

But that doesn't load at the same time, so I get multiple rows, one with a studentid, one with a teacherid.  How do I get them to load at the same time?

Just looking for some direction

output alone is not going to work (easily) as you have two things going on

insert into student (name)
select distinct studentName
  from table;

insert into teacher (name)
select distinct teacherName
  from table;

insert into studentTeacher(studentID, teacherID)
select student.ID, teacher.ID
  from table
  join student
    on student.name = table.studentName
  join teacher
    on teacher.name = table.teacherName;


Best Way To Migrate Existing Production Database To New Physical Storage Structure I have recently taken a job as a Senior Database Administrator at a small but rapidly growing company. My first order of business was a clean up the logical design of the main database (adding primary and foreign keys to existing tables, removing database objects that were no longer in use, eliminating redundant indexes, adding missing indexes, etc) but now that the preliminary logical cleanup is complete I have turned myself to the physical design.

The current production setup is that we have a physical database server, 4 sockets, 16 cores, hyper-threaded to have 32 logical processors with 256 GB of memory. CPU utilization, even during the busiest times, is typically under 15%.

For storage we have four drives, C, D, E and T, with C for the OS, D for the data files, E for logs and T for TempDB. The size of the primary data file for the main database is currently 650 GB, but is growing since the company is growing. There are 700 GB free on the D: drive, which is an SSD in Raid 10, with 1.45 TB of capacity in total.

We are using SQL Server 2014, but the main database is in 2008 compatibility mode, due to a performance issue with one of the stored procedures which occurs in 2012/2014 compatibility mode (In 2014 compatibility the performance problems are consistent, in 2012 compatibility the performance issue only presents itself under load)

The intermittent change that I would like to make, is to take the one 650 GB data file for the main database and split it into 2 file groups (one for the main data, the other for non-clustered indexes) with 8 files per file group. So my questions are this:

1) Should I expect any gains in performance or reliability breaking up the main data file in the manner I described?

2) How do I make these changes on a production database server where I can schedule (at most) a 30 minute downtime window per day?

Also note that while I have access to a number of servers I could use to test these changes, prior to making them in production, the non-production hardware is under-powered compared to actual production. For example, the box I am planning to use for my tests hosts a production database copy which is refreshed daily. It is a virtual machine with a 8 cores, 35.6 GB of memory and the D: Drive is a virtual disk hosted on a SAN, which has 1.19 TB of total space. So while I can test my changes on this machine, I cannot be confident that any performance increases on this machine will be representative of actual production.

So far all attempts to move towards the state I have described have involved the session_id for my session getting stuck during a DbccFilesCompact command. When this happens, the percent_complete does not increment, and the SPID is suspended, and dm_exec_requests shows it cycling through PAGEIOLATCH_SH waits on various resources. I'm not sure how long it would need to complete (if it could complete), but I have let in run for hours at a time with no progress. I can kill the process and that kill resolves immediately, but when the process is killed no progress is made towards the desired end state.

Has anyone faced this problem in their own companies?
If so, what was the resolution?

I am not a SQL Server DBA, I use MySQL for my jobs. But some truths are universal. :-)

  1) Should I expect any gains in performance or reliability breaking up the main data file in the manner I described?

I have observed that the benefit of any scalability optimization depends on the queries you run.

It's practically the definition of optimization that any method of optimization will improve one type of query workload as it worsens some other types of workloads.

  2) How do I make these changes on a production database server where I can schedule (at most) a 30 minute downtime window per day?

I would set up a replica server (I gather in SQL Server land these are called Subscribers). Restructure the physical tablespaces of the replica. Then it doesn't matter how long it takes, as long as it can catch up to the master after the restructure is done.

I'm assuming SQL Server replication is asynchronous and also agnostic about physical layout, as MySQL replication is. But I don't know that SQL Server supports this.

Once you're done restructuring the replica, and it catches up to be in sync with its master, you can "cut over" probably by changing DNS entries for the respective server instances. Then the downtime is minimal, and brief, regardless of the size of the databases. The downtime should include a few moments to make sure all changes from the master have "drained" and been applied fully to the replica.

The tradeoff being that you need an extra set of DB server(s), at least temporarily. This is a case where using virtual servers or cloud servers can really save you!


Where am I going wrong while trying to install sample database in MySQL? I wanted to load the Sample Database Menagerie into MySQL. So, it's showing 'FAILED TO OPEN' in MySQL.
My text.file for the same is
Drop database if exists menagerie ;
CREATE DATABASE menagerie ;
USE menagerie ;
SOURCE C:/SampleDB/cr_pet_tbl.sql
LOAD DATA LOCAL INFILE 'C:/SampleDB/pet.txt' INTO TABLE pet ;
SOURCE C:/SampleDB/ins_puff_rec.sql
SOURCE C:/SampleDB/cr_event_tbl.sql
LOAD DATA LOCAL INFILE 'C:/SampleDB/event.txt' INTO TABLE event ;

And these are the details of Menagerie DB

I have also Uncompressed the file.

But still there's an error!

Where am I going wrong please can anyone tell? Don't ignore please, it takes a lot of efforts to type so much here.
I have downloaded Menagerie from MySQL site into a folder SampleDB on drive C.

The answer was, the file was not in the Folder C:/SampleDB
the screenshot is from the temporary unzipped file.
After creating the foldeer and copying  the files, all was in order.


Unable to start mongod daemon after upgrading OS from Ubuntu 16 to 18 Unable to start mongod daemon after upgrading OS from Ubuntu 16 to 18
root@dummy2:~# mongod --version
    db version v4.0.4
    git version: f288a3bdf201007f3693c58e140056adf8b04839
    OpenSSL version: OpenSSL 1.1.1  11 Sep 2018
    allocator: tcmalloc
    modules: none
    build environment:
        distmod: ubuntu1804
        distarch: x86_64
        target_arch: x86_64

root@dummy2:~#
cat /etc/*-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.4 LTS"
NAME="Ubuntu"
VERSION="18.04.4 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.4 LTS"
VERSION_ID="18.04"

root@dummy2:~# /usr/bin/mongod --config /etc/mongo/db8303/mongodb.conf &
/usr/lib/x86_64-linux-gnu/libcurl.so.4: version `CURL_OPENSSL_3' not found (required by /usr/bin/mongod)

The difference that I'm seeing before and after OS upgrade on libcurl version:
before:
root@dummy2:~# ls -lrt /usr/lib/x86_64-linux-gnu/libcurl*
/usr/lib/x86_64-linux-gnu/libcurl.so.4 -> libcurl.so.4.4.0
/usr/lib/x86_64-linux-gnu/libcurl.so.3 -> libcurl.so.4
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.4 -> libcurl-gnutls.so.4.4.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.3 -> libcurl-gnutls.so.4
/usr/lib/x86_64-linux-gnu/libcurl.so.4.4.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.4.4.0

After:
root@dummy2:~# ls -lrt /usr/lib/x86_64-linux-gnu/libcurl
/usr/lib/x86_64-linux-gnu/libcurl.so.4 -> libcurl.so.4.5.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.4 -> libcurl-gnutls.so.4.5.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.3 -> libcurl-gnutls.so.4
/usr/lib/x86_64-linux-gnu/libcurl.so.4.5.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.4.5.0

Did anyone face similar issues ?
How to fix this issue ?

Support for Ubuntu 18.04 was added in Mongo 4.0.6, see https://jira.mongodb.org/browse/SERVER-37778

You have version 4.0.4


How do you authenticate the database connection when using Reporting Services Forms authentication? I'm looking into using Forms authenticaiton for SSRS. I got it working using Microsoft's sample found here: http://msftrsprodsamples.codeplex.com/wikipage?title=SS2008!Security%20Extension%20Sample

However, I'm wondering what authentication to use to the SQL Server data source. When I use Windows Authentication I get this error:

An error has occurred during report processing. (rsProcessingAborted) The execution failed for the shared data set 'ActualYears'. (rsDataSetExecutionError) Cannot impersonate user for data source ' Data source for shared dataset'. (rsErrorImpersonatingUser) This data source is configured to use Windows integrated security. Windows integrated security is either disabled for this report server or your report server is using Trusted Account mode. (rsWindowsIntegratedSecurityDisabled)

When I use SQL Server Authentication it prompts me for the credentials when I run a report, even though my data source has the user and password specified in the connection string. So when I run the built-in Report Manager I have to log in for forms authentication. Then when I run a report I have to log in again for SQL authentication.

If using Forms authentication do you have to use SQL Server Authentication or can Windows Authentication somehow work? Also, is there a way to get SQL Server Authentication to not prompt you for the credentials, but to use those already supplied in the connection string?

I somehow missed seeing the Credentials section you can set for a data source. This way I can use SQL Server authentication and it works.


Datetime column vs Date + Time columns I am designing a table for a system that saves a number of logs. We are looking at ~200 entries per second.

We are using SQL-Server 2012 Enterprise Edition.

I have a question regarding breaking up a Datetime column into two columns, Date and Time.

My thinking behind the question. I would say once the data is stored in the database, most searches are going to be based on a daily basis, give me all results from today / 10th of Jan.

Now I do still need to store the time as well. So if I store it as a datetime, when executing this query sql will have to load the whole datetime fields and then only look at half of the data.

So by possibly storing date in its own field it can look at only what it needs.

But then on the other hand if you do specify a time in your query, it now has to check the value of two columns.

So I would like the input from you SQL Gurus on which option would have better performance for queries on a large database.

For all I know datetime could be highly optimised and a much better solution than breaking it up.

I would use DATETIME2 as opposed to DATETIME for any new development work at this point. If you don't need sub-second granularity, use DATETIME2(0) and you'll see some space savings as well.

From here I would start experimenting. If performance is poor just querying off the single column, I'd then try adding persisted computed columns to separate out the date and time components. This will ensure that the separated DATE and TIME columns will always be the same as the "master" DATETIME2(n) column.


Oracle writes whole file during database creation is it ok? Consider empty (!) database creation:

CREATE TABLESPACE reload DATAFILE 'D:\myDb.dbf'  SIZE 3000M  ONLINE;

This is leads to writring 3000 megabytes to the disk for empty database. Is it normal for oracle? I am new to oracle database, did I miss something important in CREATE TABLESPACE which leads to such behavior?

If you are able to create this tablespace(logical entity of Oracle database to hold user objects) then you already have  created the database.

Yes, this is expected. The statement defines a datafile of 300MB size in underlying storage.

You can also auto increment size. For example, initially you have defined its size  as 300M. When you create new objects and perform DML, they will use this space. Once this 300M is consumed by objects you need to either add new datafile or increase its size or let the Oracle increment its size as needed.

Example:

CREATE TABLESPACE tbs_02
   DATAFILE '/diskb/tbs_f5.dbf' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

It creates a tablespace called tbs_02with a datafile of 500K initial size. Once the 500K is occupied by objects it will be incremented by 500K at once and grows upto 100MB.

If you are new to Oracle database I strongly suggest you to follow Oracle Concepts Manual

Reference:

CREATE TABLESPACE

storage_clause


Database Mirroring Failure Reasons I was wondering if there is any information on what scenarios might cause a DB Mirror to have to be recreated?

I am attempting to research how sturdy the technology is and under what scenarios do we have to proceed with caution such as long running maintenance tasks or large bulk transactions.

Situations that come to mind are:

Having to Restore the Primary due to
a bad code release  / user error
Loss of Network Connectivity for extended periods of time or due to maintenance activities
DNS Issues / Misconfiguration of Aliases in SQL Server Configuration Manager

Microsoft also has quite a bit of information in an article appropriately named "Possible Failures During Database Mirroring"


How does this syntax work? {fn CurDate()} or {fn Now()} etc Recently I've been looking through some fairly old stored procedures that were written for SQL Server 2005, and I've noticed something that I don't understand.  It appears to be some type of function call.

A sample:

SELECT o.name, o.type_desc, o.create_date
FROM sys.objects o
WHERE o.create_date < {fn Now()} -1;

This will display all rows from sys.objects that have a create_date prior to 24 hours ago.

If I display the execution plan for this query, I see that {fn Now()} is replaced with getdate() by the Database Engine:

SELECT [o].[name],[o].[type_desc],[o].[create_date]
FROM [sys].[objects] [o]
WHERE [o].[create_date]<(getdate()-@1)

Clearly, using {fn Now()} is far more obtuse than GetDate().  I for one will avoid this syntax like the plague since it is undocumented.

It's ODBC escape syntax, and the engine knows what its own implementation is, and swaps it out, as you've seen in the execution plan. There are also other things, such as:

SELECT {fn curdate()},
       {ts '2016-05-24 15:19:36'}, -- not vulnerable to SET LANGUAGE!
       {guid 'D08891B4-BC25-4C7C-BAEF-3B756055AC6E'};

See the documentation here, here, here, and most importantly here. But please don't investigate and learn about this syntax; IMHO you should use the native syntax for the most part and pretend you've never heard of this stuff.

I also strongly recommend against the getdate()-1 shorthand, too, particularly if you're going back and updating old code. Be explicit and use DATEADD, since the implicit shorthand does not work with new types. For example, try:

DECLARE @d DATE = GETDATE();
SELECT @d - 1;

Result:

  Msg 206, Level 16, State 2, Line 2
  Operand type clash: date is incompatible with int

While you're in there, may as well add the semi-colons, too if you really want to protect your code 10 years into the future.


Does using a view over standard queries and table indexes improve performance on large databases? I recently had a discussion with someone who said that using a view on a database with a large number of tables and (>1,000000) rows would allow for much better performance.

I don't see how this would be the case as a view is just a stored query definition as far as I am aware.

This was in relation to a web based app which allows for the filtering of products based on type, brand, spec, etc... and needs to be very performant.

Given that queries are effectively hard coded into the app, its not like a person needs to create each query manually so I'm thinking that there should be no difference between using a view or running a standard query againts the needed tables that uses joins...

I had suggested that using indexes on the tables would allow for the performance needed but they said that when tables have more than around 200k rows, indexes are useless in terms of speeding things up...

Is this correct about table size removing the effectiveness of indexes and are there any performance increases to be had from using views over standard queries?

Any help will be much appreciated.

In general, I would say that you are right. Views are nothing more than a query stored in the catalogue of the DBMS.

Certain types of views (Materialized Query Tables, MQT) can materialize rows on disk but they have problems of there own (the cost of keeping them up to date). They are typically used for data warehouses and not so much for heavy OLTP due to the cost of maintaining them. This is a new concept in MySQL that I was not aware of. @Verace pointed me to the following links in his comment:

https://github.com/greenlion/swanhart-tools
http://www.fromdual.com/mysql-materialized-views .

Another thing that comes to mind is statistical views. At least Db2 have this possibility. In short, you enable a view to become a statistical view, and then you can update statistics for that view. It can be a great help for the optimizer to have statistics for a join instead of using a formula. I don't think this concept exists in MySQL.

With these minor remarks I would say that it's definitely wrong to claim that views in general "allow for much better performance.".

When it comes indexes, I must say that this claim is probably one of the weirdest ones I've heard. Using an index to access a row is O(log n) complexity compared to O(n) for accessing the row directly in the table. Therefore the benefit of an index grows as the table grows in size, it does not become useless when the table reaches some particular size.


Calculate Quantity Based on First in, First Out (FIFO) I'm trying to get result of quantity based on FIFO, with 2 tables below:

Table Purchase:

| PO    | Date         | Quantity | Item |
|-------|--------------|----------|------|
| PO001 | 01-Jan-2016  | 3        | AO21 |
| PO002 | 10-Jan-2016  | 7        | AO21 |
| PO003 | 01-Feb-2016  | 3        | AO21 |

Table Stock:

| SO    | Date        | Quantity | Item |
|-------|-------------|----------|------|
| SO001 | 02-Jan-2016 | 2        | AO21 |
| SO002 | 11-Feb-2016 | 8        | AO21 |
| SO003 | 12-Feb-2016 | 6        | AO23 |

I want output to be like this :

| SO    | PO    | Quantity |
|-------|-------|----------|
| SO001 | PO001 | 2        |
| SO002 | PO001 | 1        |
| SO002 | PO003 | 7        |

Do you have any idea of query for view this output? Result from calculated SO and PO in row. some more explanation:

  Where do the 2, 1, 7 come from in the wanted result?

From stock and purchase. The first (by date) stock value for item A021 has 2 and the first purchase (PO001) needs 3, so stock sold 2 and we get this row in the result:

| SO001 | PO001 | 2        |

We still need 1 more for the purchase and then next stock value is 8. So this purchase is completed and we get 1 (and 7 left in stock):

| SO002 | PO001 | 1        |

The next purchase (PO002) needs 7 and we have exactly 7 left, so the purchase is completed (and 0 stock left for that item). We get:

| SO002 | PO003 | 7        |

Purchase PO003 needs 3 but there is no stock left, so we get no rows in the result for that purchase.

This is not a trivial problem but it wouldn't be very hard with window functions (and CTEs for readability).

MySQL has not implemented either but lets see how it could be done:

WITH
  running_purchase AS
  ( SELECT po, date, quantity, item,
           SUM(quantity) OVER (PARTITION BY item
                               ORDER BY date, po
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW)
             AS running_total
    FROM purchase
  ),
  running_stock AS
  ( SELECT so, date, quantity, item,
           SUM(quantity) OVER (PARTITION BY item
                               ORDER BY date, so
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW)
             AS running_total
    FROM stock
  )
SELECT
    s.so, p.po, p.item,
    LEAST(p.running_total, s.running_total)
    - GREATEST(s.running_total - s.quantity, p.running_total - p.quantity)
        AS quantity
FROM running_purchase AS p
  JOIN running_stock AS s
    ON  p.item = s.item
    AND s.running_total - s.quantity < p.running_total
    AND p.running_total - p.quantity < s.running_total
ORDER BY
    p.item, p.date, p.po ;

Tested at SQLFiddle (in Postgres).

Note that MariaDB (which can replace MySQL) has announced that they are working on window functions and CTEs, probably on their next version (10.2). See MariaDB 10.2 Release notes.

For current MySQL versions, it has to be more complex but the logic is the same:

SELECT
    s.so, p.po, p.item,
    LEAST(p.running_total, s.running_total)
    - GREATEST(s.running_total - s.quantity, p.running_total - p.quantity)
        AS quantity
FROM
     ( SELECT p1.po, p1.date, p1.quantity, p1.item,
             SUM(p2.quantity) AS running_total
       FROM purchase AS p1
         JOIN purchase AS p2
           ON  p1.item = p2.item
           AND  ( p1.date > p2.date
               OR p1.date = p2.date AND p1.po >= p2.po)
       GROUP BY p1.item, p1.date, p1.po
     ) AS p
  JOIN
     ( SELECT s1.so, s1.date, s1.quantity, s1.item,
             SUM(s2.quantity) AS running_total
       FROM stock AS s1
         JOIN stock AS s2
           ON  s1.item = s2.item
           AND  ( s1.date > s2.date
               OR s1.date = s2.date AND s1.so >= s2.so)
       GROUP BY s1.item, s1.date, s1.so
     ) AS s
  ON  p.item = s.item
  AND s.running_total - s.quantity < p.running_total
  AND p.running_total - p.quantity < s.running_total
ORDER BY
    p.item, p.date, p.po ;

Tested at SQLFiddle-2 (in MySQL 5.6).


SQL Server Dynamic Group By I want to create a de-dupe (remove duplicates) records (actually Guest Profiles) from a table (TableA).
The user can opt for 5 criteria (Surname, Name, Email, PostCode,Phone).
This means it can create combinations or use all or leave some out etc

I am trying to create a Dynamic SQL (with sp_executesql to avoid injection) but the problem is that I do not know how many criteria will the user use in order for me to build a GROUP BY clause.

Name    Initial     Email   PostCode    Phone
Guest1  George      g@u.com 33333       777777
Guest1  George      l@i.com 22222       888888
Guest1  George      g@u.com 11111       222222
Guest2  George      g@u.com 22222       000000

You should be able to re-use the same variable that lets you pick your columns, like this:

    create procedure dynamic_group_by (@params nvarchar(max))

    as

    declare @sql nvarchar(max)

    set @sql = 'select ' + @params + ' from profiles p
    where email like ''%@%''
    group by ' + @params

    print sql

    exec sp_executesql @sql


PL/SQL date or to_char question begin
     if TO_CHAR('23-OCT-1994','DAY') = 'FRIDAY' THEN
        DBMS_OUTPUT.PUT_LINE('FRIDAY');
     else
        DBMS_OUTPUT.PUT_LINE('Not Friday');
     end if;
     end;
     /

I am trying to see if 10/23/1994 is Friday.
If it is Friday, it is going to return FRI, if not, NOT FRI

However I get
"PL/SQL: numeric or value error: character to number conversion error".

'23-OCT-1994' is a string, not a DATE, so TO_CHAR on a character is pointless.

First you have to convert your string into a DATE value. You should specify the date format plus the language which is used:

TO_DATE('23-OCT-1994', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American')

Then you can compare with your weekday. Note, 'DAY' returns the weekday but padded with spaces up to the longest name. Try '"'||TO_CHAR(DATE '2018-10-23', 'DAY')||'"' to see the effect. You should also specify the language.

You can use one of the following:

TRIM(TO_CHAR(..., 'DAY', 'NLS_DATE_LANGUAGE = American')) = 'FRIDAY'
TO_CHAR(..., 'fmDAY', 'NLS_DATE_LANGUAGE = American') = 'FRIDAY'
TO_CHAR(..., 'DY', 'NLS_DATE_LANGUAGE = American') = 'FRI'

for example

begin
   if TO_CHAR(TO_DATE('23-OCT-1994', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'),'fmDAY', 'NLS_DATE_LANGUAGE = American') = 'FRIDAY' THEN
      DBMS_OUTPUT.PUT_LINE('FRIDAY');
   else
      DBMS_OUTPUT.PUT_LINE('Not Friday');
   end if;
end;


Materialized View - Oracle I have materialized view for which i want to alter its refresh time:

REFRESH FORCE START WITH SYSDATE NEXT SYSDATE +1 DISABLE QUERY REWRITE

I want to know few things in this.

What does NEXT SYSDATE +1 depict?
How am I going to change it (e.g after every 6 hours)?
What is DISABLE QUERY REWRITE?

NEXT SYSDATE +1 means the materialized view will be automatically refreshed every day at the same time when the view is created.

In order to refresh the materialized view every 6 hours use NEXT SYSDATE +6/24

DISABLE QUERY REWRITE indicates that the materialized view is not eligible to be used by the query optimizer to rewrite sql queries on the base tables.


How can I get the list of tables in all the stored procedure I am trying to get the information about all the tables belongs to the stored procedure. I was able to get this information from below query. But, this query only gives information about the tables who are in the same database. What if my procedure is using the tables from other database separated by different schema? How can I modify my query to give information about Database name, schema name, table name of all the stored procedures in the given database. I would like to list the table who belongs to other database as well.

Query (SSMS - 2016)

  SELECT DISTINCT s.name,p.name AS proc_name, t.name AS table_name
  FROM sys.sql_dependencies d
  INNER JOIN sys.procedures p ON p.object_id = d.object_id
  INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
  INNER JOIN Sys.schemas s on s.schema_id=p.schema_id
  ORDER BY proc_name, table_name

Can anyone please help?

Tables don't "belong" to stored procedures, but I think you want sql_expression_dependencies:

SELECT DISTINCT s.name, p.name,
  db  = COALESCE(d.referenced_database_name, DB_NAME()),
  obj = COALESCE(d.referenced_schema_name, s.name) + N'.'
        + d.referenced_entity_name
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.procedures AS p
  ON p.[object_id] = d.referencing_id
INNER JOIN sys.schemas AS s
  ON p.[schema_id] = s.[schema_id]
ORDER BY p.name, obj;

Of course this won't find dependencies that are constructed using dynamic SQL, and the schema references might be wrong in cases where you've explicitly left out the schema name in the query so it can be determined by the user's default schema (if you're doing that, please don't do that).


What is the getdate() equivalent for MongoDB? I have a query to update and set some values in some fields.
Obut Now I need to update these field and in adition, I need to set DateOfModification to the current date and time.
What is the equivalent to GETDATE() from sql server to use in a $set?
I tried this:
db.col.updateMany({
     "field.field":"zzzzzzzz"
},{$set:{
        "field2" : {
        "field3" : ObjectId("5d4dc2c2b3e6de001b934ec6"),
        "field4" : "xxxxxxxxxxxxxxxxxxxxxxxxxx"
        },
        "DateOfModification: "$$NOW"
    }
}})

but It seems the $$NOW only works with mongoDB 4.2 and I'm still using 4.0
Thanks
Edit:
I tried $currentDate but still getting errors:
db.testeDBA.update(
        {},
          {$set: {"item":"anything",$currentDate: {"modifiedOn":true}}
        })

'The dollar ($) prefixed field '$currentDate' in
'$currentDate' is not valid for storage.+

You can try this.
db.testDBA.updateOne(
   {},
   {
     $currentDate: {
        modifiedOn:true
     },
     $set: {
        "item":"anything"
     }
   })

I inserted a new document and updated it. It worked.

If I run this code again, the document will be updated like this


Trigger to log Users Assigned to website Is it possible to create a trigger so that works with the following scenarios:

User types valid login info to the web application

A trigger is activated and login name is inserted to the audit table, along with timestamp

and

User types invalid login info

Trigger is activated and user name is also saved in the audit table along with timestamp

Your best option here is to create a Stored procedure to handle everything.

On user login, the stored procedure is called with the username and password(Encrypted) for example.

This stored procedure would do the following

Validate the login.
Update a Lastlogin timestamp
write the login audit row (irrespective of successful or unsuccessful login.

This would be the best way to handle the process. I would then wrap the above in a transaction, to ensure that the audit record is always created for any login. If the transaction fails, the user will have to login again.

Here is a very basic example for the sake of keeping it simple. It can definitely be improved on for your specific use case, but this is broken down for easy reading and logic, especially around the password validation as encryption should be used but is not for this example :)

CREATE PROCEDURE ApplicationLogin @Username nvarchar(255), @Password nvarchar(255), @IpAddress
AS
BEGIN
   DECLARE @UserID int;
   BEGIN TRANSACTION;

   SET @UserID = (SELECT UserID FROM Logins WHERE Username = @Username AND Passwword = @Password );

   IF @UserID > 0
   BEGIN
     UPDATE Logins SET LastLogin = GETDATE() WHERE UserID = @UserID;
   END

   INSERT INTO UserAudit (Username, UserID, Password, IpAddress, TimeStamp)
   Values(@Username, @UserID, @Password, @IpAddress, @Timestamp);

   COMMIT TRANSACTION;

   SELECT @UserID --On your client, check if the returned @UserID is >0. If it is, then successful login. If not, then unsuccessful.

END

The login check and LastLogin Update could probably be combined. You could also use a try catch instead of a transaction. In this case I believe the difference in negligible. Also, as mentioned, you would want to have your passwords encrypted. What i liked to do, was encrypt the password in the client, and only compare encrypted strings. That means you never need to decrypt the password. Its always encrypted and therefore i believe is more secure.


Query FROM Indexed View queries underlying table in Execution plan I have a largish table (>126 million rows), and a need to optimize a specialized set of queries, so I created an Indexed view on the table, and coded a procedure that would select from the Indexed View when the conditions were favorable for it.

Execution of the procedure took longer than I expected, so I looked at the execution plan, and I see that the query that takes the longest is the one that selects from my view, and when I hover over the element that takes the longest it's a Clustered Index Seek of the CI of the underlying table.  Not the Index on the view.

I understood that an Indexed View was a sort of virtual table that was stored on disk, and that when queried, did not pass the query through to the underlying table at all.   Is this not the case?

The optimizer might choose to use the underlying table (it "expands" the view) based on how it chooses to implement your query.  To force the query to use the indexed view, you could add a WITH (NOEXPAND) hint to the FROM clause of the query where the view is referenced.

This is useful for two reasons:

It gets the query to use the view you want
It might show you why the optimizer chose not to use the view (maybe the index definition needs to be tweaked to get better performance, etc)

By the way, it's recommended to use this hint regardless when referencing indexed views directly, in order to avoid other problematic behavior.  See this article from Paul White for details: Indexed Views and Statistics


How can I strip non-numeric characters out of a string? Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type.

One field (phone number) consists of all numbers, so when checking it strips out all non-numeric characters from the string using a .Net CLR function.

SELECT dbo.RegexReplace('(123)123-4567', '[^0-9]', '')

The problem is, this function abruptly stops working on occasion with the following error:

Msg 6533, Level 16, State 49, Line 2
AppDomain MyDBName.dbo[runtime].1575 was unloaded by escalation policy to ensure the
consistency of your application. Out of memory happened while accessing a critical resource.
System.Threading.ThreadAbortException: Exception of type
'System.Threading.ThreadAbortException' was thrown.
System.Threading.ThreadAbortException:

I've tried the suggestions posted on MSDN for this error, but am still getting the problem. At this time, switching to a 64-bit server is not an option for us.

I know restarting the server releases whatever memory it has held, but that is not a viable solution in a production environment.

Is there a way to strip non-numeric characters out of a string in SQL Server 2005 using T-SQL only?

I found this T-SQL function on SO that works to remove non-numeric characters from a string.

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END


How to get missing values in row order? In a table, many rows have been deleted, how to get the id of missing rows for next INSERT?

For example

id      col1
1       1
3       3
4       4
5       5
8       8
9       9

how can I get the value of first available id for next INSERT. Here I want to get the id = 2.

Something like

SELECT id+1 FROM table WHERE CLAUSE // pointing to the least available value
// or x = id +1 (after SELECT)
INSERT INTO table (id, ....) VALUES ('x', ....)

If the column is just a surrogate key and is not used for other purposes (display sorting and so forth) then I would just use your database's AUTOINCREMENT equivalent and ignore the fact that there will be gaps in the IDs, as there point is to be unique rather than serving other purposes. The much recommended "SQL Anti-Patterns" book has a chapter on this entitled "pseudokey neat-freak". This depends on what you are modelling and how of course, if the numbers do carry meaning beyond being an identifier then this point is irrelevant.

The general concept you want to read around (it is worth a little background reading as these problems crop up all over the place so knowing how to spot them and deal with them efficiently or design them out is very useful) is gaps and islands. There are many online references about this (this is the first one a quick search found, it is talking about MSSQL but the concepts are transferable).

As well as the WHERE NOT EXISTS method, which is usually suggested as the way the code reads makes your intentions more obvious, you can also do:

SELECT TOP 1 t1.id-1
FROM     yourtable t1
LEFT OUTER JOIN
         yourtable t2
ON       t2.id=t1.id-1 -- will match if there is a row with the next ID down from the row in t1
WHERE    t2.id IS NULL -- the next ID down not found
AND      t1.id > 0     -- assume 1 is the lowest valid ID
ORDER BY t1.id

(that is MSSQL syntax, you may need to tweak it)
Both variants should produce similar query plans so perform the same, but I've seen the above approach perform better as part of a more complex query so it may be worth trying both in your circumstances and verifying which performs best.

Also not that neither method as currently presented will return the first ID if the table is empty: you'll get no rows back instead (or NULL if you are using this as a sub-query). One method of getting around this is to have an "invalid" row with id=0 but that is quite dirty (you end up having to filter that out of many other queries) so dealing with the nothing/NULL in your logic instead instead is strongly recommended. For tables that will never be empty in production (a users table for instance, even on initial install there will be one record for the initial setup/admin user) this or course is not an issue.

edit: Corrected the query to return the first ID if not found, as ypercube noted the first version would not do

Newer mySQL Versions

Since this answer, mySQL and MariaDB have added support for window functions (in versions 8 and 10.2 respectively) such as LAG() and LEAD(). You can use these to inspect the next (or previous) row and compare the value that is intended to increment and see if there is a gap, meaning you do not need to join the table onto itself or use a sub-query with EXISTS for this comparison - with the right index(es) this can be a much more efficient way to implement some gap/island queries.

All the common SQL database engines (sqlite, postgres, SQL Server, Oracle, ...) now support window functions.


sp_BlitzIndex compilation I currently use SQL Server 2012 and have BlitzIndex 4.4
not infrequently the execution plan will say I have a missing index on a table.  BlitzIndex will often not say any indexes are missing on that table.
question:  How are both of them determining that an index is missing?  I assumed both were drawing info from the dmv's however I no longer assume that to be the case.

sp_BlitzIndex draws on the missing index DMVs, and shows you indexes with an estimated daily benefit >100k. This comes from uses * impact * average query cost / days uptime. If a missing index request doesn't show up here, it may not break that threshold.

Missing index requests in query plans draw from the index matching portion of the optimization process. They don't pull from DMVs at all, but rather will log to the DMVs that sp_BlitzIndex pulls from.

If you want to look more closely at just missing indexes, run sp_BlitzIndex in @Mode = 3 for that database. It will show you all of them.

Full disclosure: I am employed by Brent Ozar Unlimited, a limited liability corporation, and I contribute code to sp_BlitzIndex.

Thanks!


Oracle 12c and Solid State Disks I have to migrate my old Oracle 10g database system to a brand new 12c server. The vendor suggests to use two SSD disks in RAID1. Since I'm very happy with my current configuration (two SAS RAID10 arrays) I'm a little bit scared about this. Do you have suggestion/reccomendations/experiences to share?

There is an Oracle doc that you might want to look at, Doc ID 1681266.1. You want to make sure that you are using 4K block sizes for storage. If you are moving to a new server and you are running at least Redhat 6.5 or Windows 2012, than that should not be a problem. Using a 4K physical block size will give you better performance.


Select subquery CHECK constraint DB2 I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.

db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"

I keep running into, During SQL processing it returned: SQL0546N  The check constraint "check_actor" is invalid.

How can I fix this?

You cannot use a subselect in a check constraint; you can only reference columns from the same table.

However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.

ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)

Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it, or a unique constraint on that column.


sp_blitzCache against one stored procedure Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully.

I have a copy of the output of sp_BlitzCache taken during the week before we tuned the query, it is available here

https://drive.google.com/open?id=1oiAEl62ZT51qcCEhVzkg8oDA3HB-NCsv

The command run to generate this output was

exec sp_BlitzCache @StoredProcName = 'hs_UpdateShipmentPackagesAndWeightSp'

Few questions

1) I noticed that we have multiple rows for the same plan handle, why is that?

2) You'll notice that the highest cost query (1451) has a query text of the entire create procedure statement, what does that mean? Are the metrics for this row just for the creation of the stored procedure?

3) For the highest cost row, the one referenced above, it has a Min/Max grant of almost 2GB. Does that mean that when we run the create procedure statement it asked for 2gb of memory? Or is this plan being used when the proc is called ? I suppose this ties into the answer from question 2

4) Why in some cases we have Min/Max grant KB > 0 and Min/Max used grant kb > 0, but percent memory grant used is null ?

We are running SQL Server 2012.

Thanks,
Kevin

Rephrased your questions a little:

Q: Why multiple rows for the same plan handle?

A: Because a plan can include multiple statements.

Q: Why does a proc have multiple lines in sp_BlitzCache?

Similar to above: because a stored proc can have multiple statements in it. You'll see one line for the entire proc's metrics, and a line for each statement in it.

Q: When I see "CREATE PROC" does that mean the proc was created?

No, that's just how SQL Server stores the queries in a stored procedure. It shows you the contents of the proc.

Q: Why is the memory granted different from memory used?

Because the grant is calculated before the query starts using memory. It's kinda like you asking for money to go to the store - you may not use all of the memory if they don't have all the stuff you wanted at the store. You may end up only using some of your money.