Sql Server fails to use index on simple bijection This is another query optimizer conundrum.

Maybe I'm just over-estimating query optimizers, or maybe I'm missing something - so I'm putting it out there.

I have a simple table

CREATE TABLE [dbo].[MyEntities](
  [Id] [uniqueidentifier] NOT NULL,
  [Number] [int] NOT NULL,
  CONSTRAINT [PK_dbo.MyEntities] PRIMARY KEY CLUSTERED ([Id])
)

CREATE NONCLUSTERED INDEX [IX_Number] ON [dbo].[MyEntities] ([Number])

with an index and some thousand rows in there, Number being evenly distributed in the values 0, 1 and 2.

Now this query:

SELECT * FROM
    (SELECT
        [Extent1].[Number] AS [Number],
        CASE
        WHEN (0 = [Extent1].[Number]) THEN 'one'
        WHEN (1 = [Extent1].[Number]) THEN 'two'
        WHEN (2 = [Extent1].[Number]) THEN 'three'
        ELSE '?'
        END AS [Name]
        FROM [dbo].[MyEntities] AS [Extent1]
        ) P
WHERE P.Number = 0;

does an index seek on IX_Number as one would expect.

If the where clause is

WHERE P.Name = 'one';

however, it becomes a scan.

The case-clause is obviously a bijection, so in theory an optimization should be possible to deduct the first query plan from the second query.

It's also not purely academic: The query is inspired by translating enum values to their respective friendly names.

I'd like to hear from someone who know what can be expected from query optimizers (and specifically the one in Sql Server): Am I simply expecting too much?

I'm asking as I had cases before where some slight variation of a query would make an optimization suddenly come to light.

I'm using Sql Server 2016 Developer Edition.

Am I simply expecting too much?

Yes. At least in current versions of the product.

SQL Server will not pick apart the CASE statement and reverse engineer it to discover that if the result of the computed column is 'one' then [Extent1].[Number] must be 0.

You need to make sure that you write your predicates to be sargable. Which almost always involves it being in the form. basetable_column_name comparison_operator expression.

Even minor deviations break sargability.

WHERE P.Number + 0 = 0;

would not use an index seek either even though it is even more straightforward to simplify than the CASE expression.

If you want to search on a string name and get a seek on number you would need a mapping table with the names and numbers and join onto it in the query, then the plan might have a seek on the mapping table followed by a correlated seek on [dbo].[MyEntities] with the number returned from the first seek.


SQL Server how to detect unused views and/or get usage stats Is there a way to determine if a view is no longer being used (without removing them)?

Ideally I would like to know the views' usage for SQL Server 2000 and 2012.

I am upgrading some databases and suspect that many of the views are no longer being used. Also, some of the views will be difficult to compile on the new server as they access multiple databases, some of which are not being moved to the new server.

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc.
Since you want a method that works across 2000-2012, your only real option to guarantee you catch the usage is to use a server-side trace with the Audit Database Object Access event:
http://msdn.microsoft.com/en-us/library/ms175013.aspx
You will want to filter on the ObjectType = 8278 so that you only catch Views being accessed:
http://msdn.microsoft.com/en-us/library/ms180953.aspx
Then setup a job that pulls the file data in and aggregates the counts up every couple of hours (or even days depending on the data generation rate), and you'll be able to accurately trace out the accesses that are occurring.
Yeah I hate Trace as much as the next guy, but this is one of those scenarios where it is the right tool for the task at hand.
FWIW, on 2012 you could use Server Audit or Extended Events for this to track the object access as well, but once you create the Trace definition for 2000, it should be portable for the most part to 2012, and you can use Profiler to generate the scripts to make it much easier to work with.


Append to an existing backup setup when doing a Transaction Log backup? When doing transaction log backups, is it best practice to just keep appending to the same file or creating a new .trn file for each backup?

I personally create a whole new NTFS file for each backup. Why? Because there is no chance of accidentally overwriting a backup (with init). A lost transaction log backup is crippling. In my opinion, the maintenance overhead of multiple backup devices/files is negligible.


Is there any way to concatenate a value in a column with different strings? As you can see on the column 'Race Position' I have a set of values corresponding to the participants results. 1 being 1st Position and so on.

What I wanted was to be able to concat the values and give the first 3 strings as so; 1'st', 2'nd', 3'rd'. The position after will all finish with 'th' at the end for example 4th, 5th, 6th ,7th and so on.

Is there any way to implement this? I have used a cross join in order to make my positions work.

I have the generic expression you need:

SELECT CONCAT(@num,IF(MOD(@num,100) IN (11,12,13),'th',
IF(MOD(@num,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(@num,10)-1,2),'th')));

What this does is the following

If the last digit of a number is 1 (other than last two digits being 11), st is appended
If the last digit of a number is 2 (other than last two digits being 12), nd is appended
If the last digit of a number is 3 (other than last two digits being 13), rd is appended
All other two-digit combinations have th appended

To test this out, run the following

SELECT CONCAT(num,IF(MOD(num,100) IN (11,12,13),'th',
IF(MOD(num,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(num,10)-1,2),'th'))) n FROM
(SELECT th*1000+h*100+t*10+u num FROM
(SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TH,
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U) A
ORDER BY num;

UPDATE 2017-04-13 14:29 EDT

You asked me

  Hey Rolando, I understand roughly how you have implemented this. But How would I work this around my code as so; select id, duration, @position := @position + 1 as position from (select @position := 0) x, (select id, duration from course order by duration asc) y;

SELECT id,CONCAT(position,IF(MOD(position,100) IN (11,12,13),'th',
IF(MOD(position,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(position,10)-1,2),'th')))
FROM
(
    select id, duration, @position := @position + 1 as position
    from (select @position := 0) x,
    (select id, duration from course order by duration asc) y
) a;

Basically, I placed your query inside a subquery and applied the generic expression.


Optimizing one-way syncing of large (wide) tree I deal with several trees that can vary in size (they all have depths of ~8-10) but it's not unusual for them to consist of ~50-100k nodes.

At the moment, the trees are stored using simple parent_id FKs.

A tree is created/generated in a program, and then it needs to be saved in the database. The catch is that we cannot just wipe the previous tree (of the same type, if one exists), we need to update it to reflect the new (changed) tree. Occasionally this means swapping the entire tree (if the root changed), but a lot of the time it just means minor changes here and there (e.g. add some children/sub-trees, retire others). We also do not delete old children (and their children), they just get marked as old.

The way updates/verifications are done is by traversing through the tree (depth-first) and checking each node one-by-one.

One of the performance issues is that it can take almost 2 minutes to just verify that a tree (~50k nodes) in the db is the same as the one in the program (since it involves n selects where n is the number of total nodes).

While utilizing an ltree structure could be beneficial in terms of reads, I don't think it would solve this issue (inserting/updating/verifying a tree).

I'm not sure what the best way to optimize this is. One idea I had was to calculate and store a hash of every sub-tree, and that way it could potentially speed things up in most cases since it would know what parts of the tree that doesn't need touching. The hash calculation should be pretty fast and can be parallelized in the program, where the workload is easier to manage. However, perhaps there is some other way that I'm missing?

Bulk inserts etc. are impossible since the tree relies on recursive relationships.

Furthermore, the database is naturally in the cloud, which means that these thousands of queries being executed one-by-one probably incur quite a bit of time just in terms of latency (both the program and the db are on AWS so the latency is small, but still exists of course). A part of me wants to bulk insert the newly generated tree into some temporary table and then write a DB function that syncs it into the actual table.

General structure/schema of a tree:

Each node has an id and a parent_id column (nullable FK to id)
The root node has parent_id=NULL
What makes each node unique is (parent_id, attrs...)

Meaning that duplicate nodes can exist in multiple places in the tree

Nodes are only added or marked as obsolete

Any advice is much appreciated!

It's look like that is more efficient to dump the new state of the tree into the base along with the old state and then compare them with a single CTE.

Here is an approach I've used in the MariaDB but I hope PostgreSQL allows the same with minor changes:

WITH RECURSIVE
   old_tree (dpth, id, parent_id, attr1, attr2 ...) AS (
      SELECT 1, t.*, a.*                          -- initial portion
        FROM trees  AS t                          -- the table containing all the trees
        JOIN attrs  AS a ON a.node_id = t.id      -- attributes for node
       WHERE t.id = 1234                          -- root node for the old tree
      UNION ALL
      SELECT o.dpth+1, t.*, a.*                   -- recurrent portion
        FROM old_tree AS o                        -- this is recursive CTE
        JOIN trees    AS t ON t.parent_id = o.id  -- childs of the nodes already fetched
        JOIN attrs    AS a ON a.node_id = t.id    -- attributes for node(s)
      ),

   new_tree (dpth, id, parent_id, attr1, attr2 ...) AS (
      SELECT 1, t.*, a.*                          -- all the same for the new_tree
        FROM trees  AS t
        JOIN attrs  AS a ON a.node_id = t.id
       WHERE t.id = 4567                          -- root node for the new tree
      UNION ALL
      SELECT n.dpth+1, t.*, a.*
        FROM new_tree AS n                        -- this is recursive CTE too
        JOIN trees    AS t ON t.parent_id = n.id
        JOIN attrs    AS a ON a.node_id = t.id
      )
SELECT ot.*, nt.*                     -- should be aliased to avoid ambiguity
  FROM            old_tree AS ot
  /* there is no FOJ in mysql/mariadb but I've used it to be more clear */
  FULL OUTER JOIN new_tree AS nt  ON nt.attr1 = ot.attr1 -- or any other node identifier(s)
                                 AND nt.dpth = ot.dpth   -- and by same depth of recursion
;

As a result you'll get the table like that:

+------+------+------+------+------+------+------+------+
|  oid | opid | oat1 | oat2 |  nid | npid | nat1 | nat2 |
+------+------+------+------+------+------+------+------+
| 1234 | NULL |    X |    x | 4567 | NULL |    X |    x | -- unchanged
| 1235 | 1234 |    Y |    y | NULL | NULL | NULL | NULL | -- deleted
| NULL | NULL | NULL | NULL | 4568 | 4567 |    A |    B | -- added
| 1357 | 1234 |    Q |    a | 9876 | 4567 |    Q |    z | -- modified
| .... | .... | .... | .... | .... | .... | .... | .... |

The final SELECT of the WITH can be used to filter out only changed/added/deleted nodes. Then you can modify the old tree structure or log the changes or else.


SQL Server recovery with LVM snapshot as base Is it possible to fully recover a database from a snapshot plus transaction log backups? By snapshot I mean a crash-consistent clone of the Server - in this case an LVM snapshot that is shipped offsite while the live server continues to operate and generate logs.

--edit

To clarify, the LVM snapshots are of the entire Virtual Machine including OS, SQL Server and everything else. This snapshot is then transported to a remote site and successfully started on the host. I know I can at this point restore a full backup and recover with the logs, but I wondered if it was possible to skip the full backup/restore step and use the snapshot as a base?

Going from this: "I can at this point restore a full backup and recover with the logs, but I wondered if it was possible to skip the full backup/restore step and use the snapshot as a base?".

In order to restore the log backups the database needs to be put in a 'restoring' state. Now I'm not actually sure if you can put the online database in that state without actually making a real restore of a full backup. The normal way would be to restore the db from full backup using the NORECOVERY option (expects log backups) and then restore the following log backups.

I'm not sure if there's a way to circumvent it.

PS: Jack, you don't need to mark this as the answer yet, let's let the wiser SQL Server admins here speak their own experience, maybe I'm just plain dumb and missing something fairly obvious :-).


How can I check index fragmentation in the quickest way possible? Checking index fragmentation in my database seems unruly slow. Regardless if I use the DMV sys.dm_db_index_physical_stats (for a specific database, table, or even index) or if I use the SSMS Index Properties window to look at Fragmentation on a specific index, it takes a really long time.

For example, using the Index Properties window will take upwards of 5 minutes to open up for a single index on my largest (~20 billion rows) table.

I do want to push to implement partitioning but until then I have to support an existing index maintenance job and I'm not sure how we can even check index fragmentation when one index on our heaviest table takes about 5 minutes to analyze. (Each of our tables has at least a few indexes.)

Here's a case where it took so long in the Index Properties window that I think it timed out and returned nothing in window:

It looks like the other suggestions so far all lead back to sys.dm_db_index_physical_stats

This is where you will have to look to get the index stats, but you could look to use the LIMITED option and restrict it to specific indexes at a time.

With the size of your table, I would want to know how quickly fragmentation occurs to help me confirm that the indexing (particularly the clustered index) is appropriate.

How big is your maintenance window?  Is it all consumed by the scanning part?

One other aspect to investigate (you didn't mention which version you were on) is RESUMABLE ONLINE REBUILDS.  If you have the space then this might be an option to run as much as you can over multiple maintenance windows.


What is better in IN()? Sub-query or list of values If search or update rows and use where statement IN() - is better to select values with sub-query or define values or there is no efficiency difference between this? For example:

SELECT a FROM b WHERE c IN( SELECT c FROM x )

OR

SELECT a FROM b WHERE c IN(1,2,3,4,5,6,...hundreds values...)

If you have the values in a table and the column is indexed, I would expect it to be more efficient than using a list of values. Note that there are several ways to so this - especially because this type of queries (WHERE c IN (SELECT c FROM x)) is not fully optimized in some, not so old, MySQL versions:

SELECT a FROM b WHERE c IN ( SELECT c FROM x ) ;

You could rewrite (if x (c) has no NULL values) as:

SELECT a FROM b WHERE EXISTS ( SELECT 1 FROM x WHERE x.c = b.c ) ;

or (if x (c) has unique values) as:

SELECT b.a FROM b JOIN x ON x.c = b.c ;

So, now you have 4 versions to test. Actual behaviour of course depends on several more factors (besides query, indexing, versions) like table sizes, values distributions, mysql settings, memory available, other queries running, moon phase, disk efficiency, etc.

In all, the best thing to do is test in your environment, all the available options and choose what works best for you.


Replication on same physical server I am new to DB.
We have galera cluster on Test environment where mariaDB is replicated in 3 servers.
Now customer want us to work with only 1 server. they cant provide 3.

Can we do replication on single physical server?
Is it good idea? How can we achieve that?

Thanks in advance.

I'm hoping you're talking about MS-SQL Server.
In that case is the answer: Yes you can run it on the same server.
Is it a good idea, that depends on what you want to achieve with it and how many resources that you have on the server.

I would prefer, if one server is only possible, that you install 3 different MS-SQL Server instances on this server each with their own mry and own storage.
But understand, that in case the server goes off-line, all 3 instances are also off-line.

Proper evaluation is needed.


Why Whole Table Scan (Clustered Index) I have a big table with 315M rows. with these columns & Index :

[key_] (int & IDENTITY(1,1)), [time_] (datetime), [value_] (float), [quality_] (int)

PK & Clustered-Index: key_ , Non-Clustered-Index on: time_ + quality_

Actually the main select that we use here is as bellow. This select gives good result, in 2Secs, but when I change the range & make it wider (like 36 hours), it responds in 6Minutes!
SELECT TOP (1000)
     ,[time_]
     ,[value_]
     ,[quality_]
    FROM [DataHubRev2].[dbo].[tb_HPGR_120MI01ME01_Power]
    WHERE quality_=192 AND time_ >' 2020-03-27 19:00:01'AND time_ <' 2020-03-28 07:00:00'
                           --- 12 hours: Good Result
--- WHERE quality_=192 AND time_ >' 2020-03-27 07:00:01'AND time_ <' 2020-03-28 19:00:00'
--- 36 hours: Bad Result

It is clear that by using this select & where, SQL-Engine should go for NonClusteredIndex (at least I think so). When I look at the 'Actual Execution Plan' the first one goes directly to the NonClustIndex Seek, but the second one goes for ClusteredIndex Scan & the readed rows tells 315M (the entire table)!!
Interesting part is that when I remove  ',[value_]' from the select or add the Hint to force NonClustIndex use (With (INDEX = [IX_TimeQuality_Index])) response time & exec-plan is solved. But I need a more deep solution & actually why this is happening.
Also tell me that: making my table as Heap & not having any Clustered Index on ID which I don't use on my selects is a good practice or not?
Edit:
These are the Actual Execution Plans, you could see that the second one scans all the table (Number of rows read).

Good Response Time : https://ibb.co/JFjXXwJ
Bad Response Time:    https://ibb.co/WGgrDh2

Wild Guess only: Use quality_ + time_ instead time_ + quality_ and include (value_) ?


Oracle - clean LOB files - recovering disk space I have a friend who has a website and asked me for help.
I often use MySQL databases but never Oracle databases.
And unfortunately he has an Oracle database, so I can't find a solution.

The available disk space is slowly decreasing... I delete a lot of lines from the table but that doesn't solve his problem.
The database continues to take up disk space slowly.

I read that LOB files do not return disk space, even if you delete data.

How can I reorganize LOB files easily with a simple request?
(or/and) How can I recover disk space on Oracle?

SELECT DISTINCT VERSION FROM PRODUCT_COMPONENT_VERSION

12.1.0.1.0

Only deleting lines from tables will not gain the disk space.
You need to reclaim the table space after deleting data.

I would suggest go through the Oracle documents on reclaiming space.


Running SSIS package from SQL Agent job owned by a non-sysadmin domain user I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job is owned by a sysadmin (well, me) and run as the SQL Server Agent Service Account.

So, the data essentially goes source system ~> transit db ~> staging ~> NDS overnight.

The two SSIS packages I care about, handle the transit db ~> staging and staging ~> NDS parts, respectively, for a specific set of data.

A domain user (non-sysadmin) does something in the source system and that pushes the interesting data into the transit db, so I need a way to fetch this updated data during work hours to update the NDS: it was decided that the simplest way for this person to trigger that ETL, was by clicking a button in a macro-enabled Excel workbook, that connects to SQL Server via ODBC (using Windows Authentication) and executes a stored procedure.

The stored procedure looks like this:

create procedure dbo.UpdateMaterialInventory
as
begin
    execute msdb.dbo.UpdateMaterialInventory;
end

The "sister" stored procedure in [msdb] looks like this:

create procedure dbo.UpdateMaterialInventory
with execute as 'SqlAgentProxy'
as
begin
    execute msdb.dbo.sp_start_job N'NDS-ManualMaterialInventory';
end

This [SqlAgentProxy] user is a Windows user I created in [msdb] off the domain user's login, to which I granted execute permission to this UpdateMaterialInventory procedure. This avoids having to grant the domain user execute permission to msdb.dbo.sp_start_job, which would be excessive.

The SQL Agent job NDS-ManualMaterialInventory is owned by the domain user and has 2 steps, each of type [SQL Server Integration Services Package], set up to Run as SSISProxy.

SSISProxy is an SQL Server Agent proxy that's mapped to the [SQL Server Integration Services Package] subsystem, using credential name SSISProxyCredentials. The domain user's login was added to the Proxy account principals.

The SSISProxyCredentials were created with the Identity of the same domain user that's running the entire SSIS ETL overnight, and its password was quadruple-checked.

Now, if I run this:

execute as login=N'DOMAIN\thatperson'
exec NDS.dbo.UpdateMaterialInventory;
go

I get this output:

Job 'NDS-ManualMaterialInventory' started successfully.

However the job history is telling a much less encouraging story:

The job failed.  The Job was invoked by User DOMAIN\thatperson.
The last step to run was step 1 (Extract).

And step 1 details:

Executed as user: {domain user that runs SSIS ETL overnight}.
Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started:  2:18:50 PM  Failed to execute IS server package because of error 0x80131904.
Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.
Source: .Net SqlClient Data Provider
Started:  2:18:50 PM  Finished: 2:18:51 PM  Elapsed:  0.094 seconds.
The package execution failed.
The step failed.

The job fails and nothing gets logged anywhere.

If I change the job owner to be myself, and change the steps' run as to be the SQL Server Agent Service Account, the job runs, succeeds and logs 1,067 rows to [Metadata].[dbo].[sysssislog].

Looks like there's something not right about how the proxy/credentials are set up. Which part am I doing wrong?

The problem looks more complex than it is. Since you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012.

The only thing that actually matters is:

Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.

Your Proxy user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server).
You need to map the login to an SSISDB user and configure access to the SSISDB folders/projects in Integration Services.

Please have a look at this MSDN blog post SSIS Catalog Access Control Tips and SQL 2012 SSIS Catalog Permissions

Once you have the package actually loading you might run into other security context issues but you should get better logging from integration services itself.


Database design for web mail client I am implementing a web-mail application.Only registered users can send, receive and view E-mails. When I start to design the database,I came up with following issue.
Lets say this application has pottentially 100 000 users (maybe more than this).
each user can have thousands ( n*1000 ) of emails in there inboxes.What my question is...
Is it okay to create inbox table for each user dynamically
OR create only one inbox table to all users?

If I use only one inbox table it will
contain 100000*(n*1000) records.If I do other way only particular user's
e-mails will stored in their respective inbox table. But there will be
thousands of inbox tables in the database.

Can any one suggest a design clue...

I think it has to do with your security requirements / permissions on the backend.  With the single table approach, DB permissions will be for the table, whereas you can permission the multiple tables per user.

You can partition your single inbox table by user. This will allow you to manage it in much the same way as you would the multiple tables for performance.

If you decide on separate inbox tables per user, I would recommend putting them all in their own dynamically created filegroup.

What will happen to the tables or rows when the user leaves?  In two scenarios, 1 where they are dropped and 2 where there are archived, both would be more easily and cleanly accomplished by the table scenario IMHO.


Trigger if certain data matches - Mysql I have 2 tables load_data and logs.

I have written a trigger on the load_data table (mysql).
The trigger inserts the data into the logs table on Updates

I would like to insert the data in "load_data" only when the 3 columns has following records

If diIndex=3 && diMode=0 && diStatus=1 then only execute insert statement.

I tried this but throws an error

SELECT 1
   INTO @id
   FROM load_data
   WHERE diIndex=3 && diMode=0 && diStatus=1;

   IF @id != 1
   THEN
       INSERT INTO logs(user_id,button_name,flag,box_name)
values("83","Plant","1","Trigger")
    END IF;

SELECT @a := EXISTS ((SELECT 1 FROM load_data WHERE diIndex=3 and diMode=0 and diStatus=1));
IF @a = 1 THEN

       INSERT INTO logs(user_id,button_name,flag,box_name)
       VALUES("83","Plant","1","Trigger");

END IF;

or

IF EXISTS ((SELECT 1 FROM load_data WHERE diIndex=3 and diMode=0 and diStatus=1)) THEN
BEGIN
       INSERT INTO logs(user_id,button_name,flag,box_name)
       VALUES("83","Plant","1","Dark Red");
END;

ELSEIF EXISTS((SELECT 1 FROM load_data WHERE diIndex=1 and diMode=0 and diStatus=1)) THEN
BEGIN
       INSERT INTO logs(user_id,button_name,flag,box_name)
       VALUES("83","Plant","1","Yellow");
END;

ELSEIF EXISTS((SELECT 1 FROM load_data WHERE diIndex=3 and diMode=0 and diStatus=1)) THEN
BEGIN
       INSERT INTO logs(user_id,button_name,flag,box_name)
       VALUES("83","Plant","1","RED");
END;

END IF;


Migrate data from multiple disparate databases into 1 I have a SQL Server instance with 5 different databases.  For a few reasons, we would like to combine these into just 1 database, separating out each database into it's own schema.  I've built the schema/tables into the 'master' database already, but i'm having trouble building a good way to import the data from the old databases into the new master one.  something like:

insert into [masterDB].[oldDBSchema].[tableName]
select * from [oldDB].dbo.[tableName]

does not work, because of this error:

  An explicit value for the identity column in table
  'cobraclientdata.cconfigd.adjudicationbutton' can only be specified
  when a column list is used and IDENTITY_INSERT is ON.

now i can set IDENTITY_INSERT to ON easily enough, but i'd rather not specify the columns if i don't have to because it would be a PITA to do that for every table.  These tables have exactly the same structure, is there a quick way to do this (I'm trying to build a script that i can run this 'update' on several servers), or am i stuck writing out the columns for each table, or doing an import/export data explicitly?

You can generate a list of T-SQL statements using dynamic SQL, something like this:

DECLARE @srcDB sysname;
DECLARE @newDB sysname;
DECLARE @oldSchema sysname;
DECLARE @newSchema sysname;

SET @srcDB = 'oldDB';
SET @newDB = 'masterDB';
SET @oldSchema = 'dbo';
SET @newSchema = 'newDBSchema';

SELECT N'INSERT INTO ' + QUOTENAME(@newDB) + N'.' + QUOTENAME(@newSchema) + N'.' + QUOTENAME(t.name) + N'(' + STUFF((SELECT N', ' + QUOTENAME(c.name) FROM sys.columns c WHERE c.object_id = t.object_id ORDER BY c.column_id FOR XML PATH(N'')), 1, 2, N'') + N'
) SELECT ' + STUFF((SELECT N', ' + QUOTENAME(c.name) FROM sys.columns c WHERE c.object_id = t.object_id ORDER BY c.column_id FOR XML PATH(N'')), 1, 2, N'') + N'
FROM ' + QUOTENAME(@srcDB) + N'.' + QUOTENAME(@oldSchema) + N'.' + QUOTENAME(t.name)
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = @oldSchema
ORDER BY t.name;


MySQL performance problem I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server:

+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| tweetid     | varchar(255)   | NO   | PRI | NULL    |       |
| user_id     | varchar(255)   | NO   | PRI | NULL    |       |
| screen_name | varchar(255)   | NO   | PRI | NULL    |       |
| date        | datetime       | NO   | PRI | NULL    |       |
| json_src    | varchar(15000) | YES  |     | NULL    |       |
+-------------+----------------+------+-----+---------+-------+

When I select rows by user_id, query time ranges from a few seconds to more than 2 minutes, which is completely unacceptable. Initially I thought it was a memory problem, since I do not have this problem when indexes take up less space. So I made sure innodb_buffer_pool_size is higher than the total index size but that did not solve it. As total index size becomes large, some indexes are fast to get, others very slow. It does not make sense to me because all indexes should be fitting into memory.

Many posts seem to be about the same problem, and I tried all suggestions I could find, but nothing worked.

Any idea ?

Edit:

Here is the output of 'show create table name;'. As you can see, user_id is a primary key so it should be indexed.

CREATE TABLE `test` (
  `tweetid` varchar(255) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  `screen_name` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `json_src` varchar(15000) DEFAULT NULL,
  PRIMARY KEY (`tweetid`,`user_id`,`screen_name`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Edit 2:

The exact command is:

  select * from twittertweets where user_id=3678084920 limit 2;

With a select in front I get:

  | id | select_type | table         | type | possible_keys
  | key  | key_len | ref  | rows    | Extra       |
  |  1 | SIMPLE      | twittertweets | ALL  | ix_twittertweets_user_id
  | NULL | NULL    | NULL | 2231775 | Using where |

Since I created a new table, let me should you the output of 'show create table':

  | twittertweets | CREATE TABLE `twittertweets` (
  `tweetid` varchar(50) NOT NULL,
  `user_id` varchar(25) DEFAULT NULL,
  `screen_name` varchar(25) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `json_src` varchar(15000) DEFAULT NULL,
  PRIMARY KEY (`tweetid`),
  KEY `ix_twittertweets_user_id` (`user_id`),
  KEY `ix_twittertweets_screen_name` (`screen_name`),
  KEY `ix_twittertweets_date` (`date`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Using a varchar(255) as a 'one-size fits all' type is a bad idea, but that is another story, and not what you asked.

As @ypercube said, you have 2 options:

a) Create an additional index on user_id

ALTER TABLE `test` ADD INDEX `user_id` (`user_id`);

b) Reorder your PRIMARY key so it starts by user_id

ALTER TABLE `test` DROP PRIMARY KEY,
ADD PRIMARY KEY (`user_id`, `tweetid`,`screen_name`,`date`);

This second option will take half the disk and memory space, but it may affect negatively queries filtering, for example, by tweetid only. You can read more about left-most prefix indexes and why your query was doing a full table scan here.


Oracle Database Enterprise Users & Roles with Microsoft Active Directory? We have a legacy application and we have full access to source code and platform running it, so virtually any change can be implemented if required.

The application is a Java Swing heavy-weight GUI (desktop) accessing directly the database through JDBC in a 2-tier fashion. At application start-up, the user enters his/her credentials consisting on user&password. The current security schema is:

Oracle Database 10g Enterprise (migrating to 11g) with Oracle Internet Directory 10g (OID).
Oracle uses Enterprise Users so when connecting to DB, user provided in connection comes from GUI and Oracle validates against OID.
DB permissions are managed through Enterprise Roles. Each ER defined in DB maps a Group defined in OID, so user DB access to objects (tables, procedures...) are controlled by the Groups he/she belongs in OID.
Several GUI users can create others users, so create they in OID, assigning to the corresponding OID Groups in order to grant DB permissions. This is done using special Oracle permissions in OID to this "super-user". This is done from GUI using standard LDAP API, not OID API. The GUI user connects to OID with its credentials and performs LDAP operations.

The idea is to replace OID with Microsoft Active Directory, so the database would validate user against AD. DB permissions defined in ER would be retrieved also from AD, based on the Groups the users belongs to.

Assuming Oracle DB 11g and MS Windows Server 2008 (or upper versions if necessary), is that possible?

Restrictions/notes:

Oracle is installed on Linux, RH typically.
Oracle Virtual Directory can not be used nor any Identity Management Oracle product, just Oracle DB Enterprise.
GUI application executes on Windows workstations that are already in the Windows domain with the AD, son can use their OS credentials instead custom users used by GUI, but this must be done in Java (any version supported).
SSL, Certificates are not the preferred way, as they required to be provisioned.
Kerberos with MS KDC is also not the preferred way.
The new security schema with AD could lead to a less secure environment, but this is acceptable.
Prefer not to add third-party products to the security schema.
Oracle DB - MS AD integration should be supported by Oracle and Microsoft.

We need some advice from people with previous experience in similar installations about if it is possible the Oracle - AD solution. Some guidance and steps are welcome!

The application I maintain does something similar that is database OS independent for around 100 users but in a much simpler fashion. Users are authenticated by the application and authorized from the database. Here are the details:

a table for users including their email and enabled/disabled status
a table of all AD groups that have a specific prefix to indicate that they are application groups  (eg:  APP_ADMINS, APP_USERS)
a table with group/user links

The next part is synchronizing the information.  In our organization if new users are added or permissions for existing users are changed IT does the job in the morning.  The application typically has a window of low usage overnight (ie: not 24 hours a day/usage).

On the application web server we have a windows service which syncs the groups and users.
On the database at night I run a scheduled job which truncates the group-user links and recreates them by a direct query to Active Directory using DBMS_LDAP.

For your purposes you would want to write a more sophisticated package that has a database package running three LDAP queries to synchronize groups, users and group users instead of my truncate solution.

I found DBMS_LDAP to be powerful but poorly documented.  If the IT department should happen to change the OU that users are in you don't get a lot of useful information from the Oracle end.

Tim's site was very useful with this article and here has the code I reused.

Edit: There are two parts to using an application:

who are you?  (authentication) if you do not supply a name and password listed in Active Directory you are not allowed past the log in page.
what are you allowed to do? (authorization) Here is where the granularity of permissions has a wide range. We have limited ourselves to a basic set of operations on a portion of a unit of work:  Read, Update, Assign other users, Unassign other users.  Our permissions are not so granular so they can be worded as "User X has select on this table".  They can be better said as "User X
can update this portion of the file if they are assigned".

In part this is driven by the application.  It creates  a pooled connection to the database as required.  Users log onto the application but connect to the database as a shared user who has all the permissions required.

To go into a more detailed example:
User X logs in.  They have a username and password that matches what is in Active Directory.
After authentication they are taken to the main page.  During that time the application queries the database for what permissions this user has and caches them in memory.
The main page loads each sub work area. Each area asks the cached permissions:
-  "Do you have authority to see this area?". If so show the work area.
- " Do you have permission to edit this area?" If so show an edit/create link.

Edit2:
The original poster comments that

  We are trying to replicate the user permissions onto application to DB
  permissions onto database objects.

This sounds like a square peg/round hole problem to me.  Your business logic may be defined as User X needs select on this table but if you ask the user they are working with the application.  They need to be able to work with whatever the application is displaying. It is common to group tables and views and custom filters to show what the user needs in the application.  This is why some applications find it easier to enforce authorization at the application level.
What is it about your application that requires database permissions to be replicated in Active Directory?


Is it possible to store and query JSON in SQLite? I need to store JSON objects in a SQLite database,
and then do complex queries on it.

I did a table like this:

+--------------------------------------+
|document |  property | string | number|
+--------------------------------------+
|foo      |  "title"  | "test" |       |
+--------------------------------------+
|foo      |  "id"     |        |  42   |
+--------------------------------------+
|bar      |  "id"     |        |  43   |
+--------------------------------------+

for the two objects

foo {"title": "test", "id": 42}
bar {id: 43}

But I can't do "AND" queries, like:

SELECT DISTINCT id  FROM table WHERE title = "test" AND id = 42

as you see, the part after the "WHERE" is total nonsense,
but I have no idea of how to create a query that would do what I want.

So do you think there is a better way to store my data,
or a workaround  to do an "AND" query?

And of course, the JSON can contain any property,
so I can't create a table with columns for each properties.

I am using WebSQL, which is SQLite without extensions.

I know my question is pretty specific, but could you help me?

SQLite 3.9 introduced a new extension (JSON1) that allows you to easily work with JSON data .

Also, it introduced support for indexes on expressions, which (in my understanding) should allow you to define indexes on your JSON data as well.


SHOW tables returns empty set, but MYI.Crypt files are there Something really weird happened to my VPS last week. Suddenly, MySQL stopped working. My VPS has CentOS 6 and uses Parallels Plesk. After this, Parallels Plesk stopped working, and trying to start MySQL via SSH just gave me an error
Mysql daemon failed to start.

I added this skip-grant-tables to my.cnf and I was able to start MySQL and access it... in MySQL I can see the database names. I selected the database using use [database-name] and I can select it, but when I use show tables it shows an empty set.

At this point I thought all the info is gone and that's it. But, outside MySQL, when I go to /var/lib/mysql, I see folders with the database names, and inside those folders, files with the table names MYD.crypt or MYI.Crypt.

At this point, all I want is to be able to create a dump file of a certain database. Is there a way to make MySQL use these files so I can recover that data and be able to export it?

Thank you.

There is more information need to troubleshooting.

What is the error logs for Mysql daemon failed to start for normal
start?

[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
What is the engine for MYD/MYI?
Do you have previous backups or replication site?


LIMIT of rows but with complete "sets" I guess this is a pretty tough one. I want to get results from the database, per 5 rows max (so a limit of 5 each time), but each time I don't want to return incomplete "sets" either. Look at my table of forms:

+----+--------+------------+
| id | formID |  created   |
+----+--------+------------+
|  1 | 111111 | 2014-01-01 |
+----+--------+------------+
|  2 | 111111 | 2014-01-01 |
+----+--------+------------+
|  3 | 111111 | 2014-01-01 |
+----+--------+------------+
|  4 | 222222 | 2014-01-01 |
+----+--------+------------+
|  5 | 222222 | 2014-01-01 |
+----+--------+------------+
|  6 | 222222 | 2014-01-01 |
+----+--------+------------+
|  7 | 333333 | 2014-01-02 |
+----+--------+------------+

So I want to return my results in sets of 5 database rows BUT with complete forms. In this case I will have incomplete results, cause I don't have all rows of form with ID 222222 returned. So either I need to get 3 results (less than 5 but complete forms) or 6 (a bit more than 5 but also complete forms).

Anyone who has any idea how to accomplish this?

At the moment my query looks, more or less, like this:

SELECT `id`, `formId`, `created`
FROM `forms`
WHERE created >= $lastCreated
      AND id != $lastId
ORDER BY created
LIMIT 5;

(this is just an example so no worries about the sql injection vulnerability ;)). As you can see I check for the last returned row id and created column every time I run a new query, so I know where I ended. But like I said, this will return a complete form 111111 but an incomplete form 222222.

What you want is called TOP WITH TIES in SQL Server and FETCH FIRST .. WITH TIES in standard SQL. The MySQL LIMIT clause is equivalent to TOP (and FETCH FIRST) but it doesn't have WITH TIES functionality.

Here is one way to do this. The details will differ, depending on what you want the ORDER BY to be based on. I used formID:

SELECT f.*
FROM forms AS f
  INNER JOIN
    ( SELECT formID
      FROM forms
      ORDER BY formID
      LIMIT 1 OFFSET 4        -- offset 5 minus 1
    ) AS fm
  ON  f.formID <= fm.formID
ORDER BY f.formID ;

This will return 6 rows in your example case. If you replace <= with < it will return 3 rows.

In order to try and adapt that to your specific case more closely, you can add the created column to the "equation":

SELECT f.*
FROM forms AS f
  INNER JOIN
    ( SELECT created, formID
      FROM forms
      WHERE created >= '2014-01-01'
      ORDER BY created, formID
      LIMIT 1 OFFSET 4
    ) AS fm
  ON  (f.created, f.formID) <= (fm.created, fm.formID)
WHERE f.created >= '2014-01-01' ;

This Rextester demo shows how it works both with <= and with <.

I am not sure about the id != $lastId predicate in your query, but if it is an actual part of the filter rather than just your attempt at resolving the issue in question, then you will probably need to add it to the above query as well, both to the main WHERE and to the nested WHERE.


Oracle 12c connection configuration errors I've installed Oracle 12c, and I'm trying to create a connexion using SQL Developer but I've got errors.

Here is the parameters after installation and users parameters:

Like in the photo, the port is 5500 but in tnsnames.ora I have this:

ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

  PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

The port in here is 1521, so I'm confused which parameters use to create a connexion. It's my first installation of oracle.

I've changed the port to 1521. Now I've got this error:

  ORA-01017:invalid username/password; logon denied

I changed username to "system". It works on SQL Developer, but on the browser I don't know how to access.

You can use the following link to access Database Express through the browser:

  https://yourhostname:5500/em

Or

  https://youripaddress:5500/em


Way to obtain full list of column dependencies for stored procedure? I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a stored procedure of the user's choice. This stored procedure will have its name passed as an argument to sproc_deps.

The problem is that I'm getting columns that the stored procedure doesn't actually use when I combine sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. In order to get the information I want, I've JOINed a few other things on as well:

sys.objects(for object IDs, and type_desc)
sys.tables(to match against tables contained in sys.sql_expression_dependencies)
sys.views (because I'm interested in both views and tables)
sys.columns (to pull columns for each table or view involved)

Here is the actual JOIN:

sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name
LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name
LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id)
INNER JOIN sys.dm_sql_referenced_entities (N'dbo.DummySprocName', 'OBJECT') s
ON s.referenced_entity_name = sed.referenced_entity_name

Using just sys.sql_expression_dependencies nets a small list of tables that I can't really decipher, and using sys.dm_sql_referenced_entities yields a partial list of tables and columns used by the procedure.

Is it possible for sproc_deps to return the correct list of tables and columns used by a procedure using just T-SQL? If so, how?

Here is my full code as requested (still working on reproducible example for missing dependencies):

CREATE PROCEDURE [dbo].[usp_v9_SprocDocInfo_FullDependency_SingleSproc]
@SprocName NVARCHAR(150) = ''

AS
BEGIN

DECLARE @ObjName NVARCHAR(128) = NULL
DECLARE @rowCount INT = 0
DECLARE @HasNulls BIT = 0
DECLARE @DepExists BIT = 0

--temp table to hold output
--match this against view!!!
CREATE TABLE #TempData
(
FullName NVARCHAR(300) not null,
ShortName NVARCHAR(128) not null,
TableName NVARCHAR(128),
ObjectName NVARCHAR(128),
column_name NVARCHAR(128),
[definition] NVARCHAR(MAX),
LastUpdated DATETIME,
[Type] NVARCHAR(60),
[object_id] INT,
SprocNo BIGINT
)

--temp table to hold pure dependencies
CREATE TABLE #Sproc_FullTableCols
(
RefEntity NVARCHAR(256),
TableName NVARCHAR(256),
ColName NVARCHAR(256),
TypeDesc NVARCHAR(256)
)

--first, grab known dependency data for this particular sproc from the correct view
INSERT INTO #TempData
SELECT FullName,
ShortName,
TableName,
ObjectName,
column_name,
[definition],
LastUpdated,
[Type],
[OBJECT_ID],
SprocNo
FROM v9_Sproc_DocInfo
WHERE ShortName = @SprocName

--next grab any data not covered in the previous query
--this will be tables/views and ALL columns for objects found in sproc
INSERT INTO #Sproc_FullTableCols
--https://www.sqlrx.com/using-sys-sql_expression_dependencies-as-a-single-source-to-find-referenced-and-referencing-objects/
SELECT DISTINCT OBJECT_NAME(referencing_id) AS referencing_entity_name,
    CASE WHEN t.name is null then V.name when V.name is null THEN t.name ELSE NULL END,
    c.name,
    case when t.name is not null then t.type_desc
        when v.name is not null then v.type_desc
        else o.type_desc end AS referencing_desciption
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name
LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name
LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id)
INNER JOIN sys.dm_sql_referenced_entities (N'dbo.' + @SprocName, 'OBJECT') s
ON s.referenced_entity_name = sed.referenced_entity_name
WHERE referencing_id = OBJECT_ID(N'dbo.' + @SprocName)

--clean up
DELETE FROM #Sproc_FullTableCols
WHERE (TableName IS NULL OR ColName IS NULL)-- OR MinorRef IS NULL)

WHILE @HasNulls = 0
BEGIN

    --pull the first row of junk data from the v9 results
    SET @ObjName = (SELECT TOP 1 ObjectName FROM #TempData WHERE (TableName IS NULL AND column_name IS NULL))

    SET @DepExists = CASE WHEN @ObjName IN (SELECT TableName FROM #TempData) THEN 1 ELSE 0 END

    --see if pull was successful; if so update flag accordingly
    IF (@ObjName IS NOT NULL) SET @HasNulls = 1
    ELSE BREAK

    IF @HasNulls = 1 AND @DepExists = 0
    BEGIN

        INSERT INTO #TempData
        SELECT DISTINCT (N'dbo.' + @SprocName) AS FullName,
            @SprocName AS ShortName,
            @ObjName AS TableName,
            '-' AS ObjectName,
            z.ColumnName AS column_name,
            z.Description AS [definition],
            GETDATE() AS LastUpdated,
            '*' + (N'' + c.TypeDesc) AS [Type],
            OBJECT_ID(N'dbo.' + @SprocName) AS [object_id],
            0 AS SprocNo
        FROM z9_BaseTables_Columns z
        INNER JOIN #Sproc_FullTableCols c
            ON (z.[Table] = c.TableName collate Latin1_General_CI_AI
            AND z.ColumnName = c.ColName collate Latin1_General_CI_AI)
        WHERE z.[Table] = @ObjName collate Latin1_General_CI_AI

    END

    --clean out row used for input
    DELETE TOP (1) FROM #TempData
    WHERE ObjectName = @ObjName
    AND TableName IS NULL
    AND column_name IS NULL

    SET @HasNulls = 0
    SET @ObjName = ''

END

--finally, print results then discard the temp table
SELECT * FROM #TempData

order by [Type] asc, FullName asc, ObjectName asc, TableName asc, column_name asc, LastUpdated asc

DROP TABLE #TempData

DROP TABLE #Sproc_FullTableCols

END

The code posted will create a procedure that grabs known dependency data for a procedure from a view (v9_Sproc_DocInfo) that uses only sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. Then I pull the full list of columns for any table used by the procedure, filter out anything already contained in v9_Sproc_DocInfo, and combine this data with the view data as output.


How to find queries that timed out in MongoDB? Is it possible to find queries in the system.profile-Collection of a MongoDB-database that did not finish due to a timeout?

I found some queries that ran longer than the time specified in $maxTimeMS.

db.coll.find(
  { millis: {$gt: 1300}, "command.maxTimeMS": NumberLong(1300) },
  {millis: 1}
).sort(
  {millis: -1}
)

But they only ran a couple of milliseconds longer, and did not have a field error or timeout. Are there fields that indicate a timeout?

On a pretty large dataset I tested a query that times out after 10 seconds:

db.collection.find({"foo": "bar"}).maxTimeMS(10000)

(In my case the collection has > 100 GB of data and no index on the field foo). I searched for the query using:

db.system.profile.find({"query.query" : { "foo" : "bar" }}).limit(5).sort({ts: -1}).pretty()

and found that the fields exception and exceptionCode are filled:

"exception" : "operation exceeded time limit",
"exceptionCode" : 50,

So searching for the field exception should give you the desired result:

db.system.profile.find({ "exception" : { "$exists" : 1 } }).limit(5).sort({ts: -1}).pretty()

And if you explicitly look for queries that timed out, try:

db.system.profile.find( {"exceptionCode": 50} ).sort( {ts: -1} ).pretty()


Always on Failover Cluster This is the error message I get on my replica when there is a connection timeout for few seconds with the Primary. Can anyone tell me whats going on and why are the available storage being moved as this is an AAG where storage is not supposed to be shared and also here there is no failover its just the secondary replica missed heartbeats. Thanks

The failover error logs are mentioned below starting from the bottom

Cluster network 'Cluster Network 2' is operational (up). All available failover cluster nodes attached to the network can communicate using it.
Joiner tried to Create Security Context using Package='Kerberos/NTLM' with Context Requirement ='0' and Timeout ='40000' for the target = 'xxxxxx01'
The Cluster service successfully brought the clustered role 'Available Storage' offline.

Clustered role 'Available Storage' is moving to cluster node 'xxxx'.
Cluster has lost the UDP connection from local endpoint xxx.xx.xx.21:~xx43~ connected to remote endpoint xxx.xx.xx.20:~xx43~.
Cluster has missed two consecutive heartbeats for the local endpoint xxx.xx.xx.21:~xx43~ connected to remote endpoint xxx.xx.xx.20:~xx43~.

The only group that failed over was the Available Storage group, which you aren't using, so it is something you can just ignore.  If you want to increase the number of missed heartbeats that will cause it to fail over, change the SameSubnetThreshold accordingly.

(get-cluster).SameSubnetThreshold = 15

See Clustering and High-Availability for details.


Why $in is much faster then $all I am well aware of what those 2 do. $in take .3 seconds. And $all take 26 seconds. Huge different. If anything $in should take longer because it returns more data.

Also if the limit is removed, $in actually is much faster.

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^soto/, /^nasi/] } }).limit(200);

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$in" : [/^soto/, /^nasi/] } }).limit(200);

This is result

/* 88 */
{
  "ts" : ISODate("2012-09-11T06:57:26.801Z"),
  "op" : "query",
  "ns" : "newisikota.tablebusiness",
  "query" : {
    "LongitudeLatitude" : {
      "$nearSphere" : [106.772835, -6.186753],
      "$maxDistance" : 0.053980478460939611
    },
    "Prominent" : {
      "$gte" : 15.0
    },
    "indexContents" : {
      "$all" : [/^soto/, /^nasi/]
    }
  },
  "ntoreturn" : 200,
  "nscanned" : 48,
  "nreturned" : 48,
  "responseLength" : 60002,
  "millis" : 3821,
  "client" : "127.0.0.1",
  "user" : ""
}

/* 89 */
{
  "ts" : ISODate("2012-09-11T06:57:43.147Z"),
  "op" : "query",
  "ns" : "newisikota.tablebusiness",
  "query" : {
    "LongitudeLatitude" : {
      "$nearSphere" : [106.772835, -6.186753],
      "$maxDistance" : 0.053980478460939611
    },
    "Prominent" : {
      "$gte" : 15.0
    },
    "indexContents" : {
      "$in" : [/^soto/, /^nasi/]
    }
  },
  "ntoreturn" : 200,
  "nscanned" : 200,
  "nreturned" : 200,
  "responseLength" : 249598,
  "millis" : 320,
  "client" : "127.0.0.1",
  "user" : ""
}

Note: the $all query can run for 26 seconds once in a while.

The explain result is the following:

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^soto/, /^nasi/] } }).limit(200).explain();

{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 48,
        **"nscannedObjects" : 48,**
        "n" : 48,
        "millis" : 8563,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
        }
}
>

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$in" : [/^soto/, /^nasi/] } }).limit(200).explain();
{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 200,
        **"nscannedObjects" : 200,**
        "n" : 200,
        "millis" : 516,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
        }
}

Notice that the $in search scan more object.

$in can abort and return success as soon as the first match is found, while $all has to go through all the data.

The runtime is dominated by the time needed to check for matches, not by the time needed to return data, because any data that does get returned has just been checked for a match and is already in the cache.


postgres fdw create foreign table without specifiy columns need to create couple of foreign tables with a lot of columns from oracle.
I dont want to specify all cloumns by myself.
Is there a way to do something like this:

create foreign table ALFSTK **(*)** SERVER server1 options (schema 'test', table 'ALFSTK');

Or is this not possible with fdw?

Thanks for your advices.

This is what IMPORT FOREIGN SCHEMA is for.  You can run it for a single table at a time if you want to.

oracle_fdw says it supports IMPORT FOREIGN SCHEMA, but be sure to read that section of the docs for more info.


Is it Possible to have Multiple Named Instances with No Default Instance? Using SQL Server 2008 R2, is it possible to install multiple named instances, and no default instance?

Yes, it is.  I have a dev server with multiple named instances (with multiple Sql Server versions) and no default instances.


Explanation of the query Can somebody explain me the below query?

select SUBSTR(SUBSTR(PART_SEQ_NBR,
                     POSITION('_' in PART_SEQ_NBR )+1,
                     char_length(PART_SEQ_NBR)
                    ),
              1,
              POSITION('_' in SUBSTR(PART_SEQ_NBR,
                                     POSITION('_' in PART_SEQ_NBR )+1,
                                     char_length(PART_SEQ_NBR)
                                    )
                      ) -1
             ) AS PRD_NAM
from PRD_MWE_GPRSDATPART_PERM_S

Thank you in advance

After formatting the query and especially the expression, it's pretty obvious that the expression can be split/rewqitten in 2 parts:

select SUBSTR( string,
               1,
               POSITION('_' in string) - 1
             )   AS PRD_NAM
from PRD_MWE_GPRSDATPART_PERM_S

where string is:

       SUBSTR( PART_SEQ_NBR,
               POSITION('_' in PART_SEQ_NBR ) + 1,
               CHAR_LEGTH(PART_SEQ_NBR)
             )

The 2 expressions - the main and the (string) sub-expression - are very similar as well. If it's still not obvious what they do, one has only to try the query, putting a few underscore _ characters in the data of the column.


DB2 CASE statement faster with parentheses around entire WHEN Trying to optimize slow queries, someone on my team come across this. Having this in our stored proc:

CASE
  WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM
    AND RECTYPE = 'RO'
    THEN 1
    ELSE 0
END AS MYRESULT

runs ~10 seconds slower than having this:

CASE
  WHEN (MYCOL BETWEEN @STARTNUM AND @ENDNUM
    AND RECTYPE = 'RO')
    THEN 1
    ELSE 0
END AS MYRESULT

The only difference is the parentheses around the entire WHEN clause. We're at a loss as to what is going on. This with DB2 on an iSeries.

What could be causing this performance difference, and how can we take advantage of it to speed things up? As a programmer, those extra parentheses mean nothing. As a DbA, what could the optimizer be doing here to have such a drastic effect on performance?

Edit: Here's the query; it's pretty nasty, but that's the data we have to work with. I've run just a single sub-select (before the union) and still had a significant performance difference, so the union and final select aren't causing the bulk of the performance hit.
I realize some of the date manipulation can be fixed to remove the function calls; someone will get to that eventually.

SELECT
ID ,
ABBREVIATION ,
METRICS.NAME ,
METRICS.TYPE AS TYPE_ID ,
JOBTYPE.NAME AS TYPE_NAME ,
SUM ( CREATEDORDUE ) AS CREATEDORDUE ,
SUM ( COMPLETED ) AS COMPLETED ,
SUM ( COMPLETEDINSIDE ) AS COMPLETEDINSIDE ,
SUM ( CASE WHEN DAYS <= 7 THEN 1 ELSE 0 END ) AS ONTIME ,
SUM ( CASE WHEN DAYS > 7 AND DAYS <= 14 THEN 1 ELSE 0 END ) AS ONEWEEKLATE ,
SUM ( CASE WHEN DAYS > 14 AND DAYS <= 21 THEN 1 ELSE 0 END ) AS TWOWEEKSLATE ,
SUM ( CASE WHEN DAYS > 21 THEN 1 ELSE 0 END ) AS MORETHANTWOWEEKSLATE

FROM (

-- Active File --

SELECT EQMDOM AS ID ,
CT1TID AS ABBREVIATION ,
CT1NAM AS NAME ,
JBCTL ,
JBWKA AS TYPE ,
CASE
    WHEN JBCDT BETWEEN @STARTDATENUM AND @ENDDATENUM THEN 1
    ELSE 0
END AS COMPLETED ,
1 AS CREATEDORDUE ,
-- Stackexchange question
CASE
    WHEN ( JBCDT BETWEEN @STARTDATENUM AND @ENDDATENUM AND JBDTC = 'RO' ) THEN 1
    ELSE 0
END AS COMPLETEDINSIDE ,
-- /Stackexchange question
MASTER.USDATE_DATEDIFFERENCE (
    CASE WHEN JBCDT = 0 THEN @ENDDATENUM WHEN JBCDT > @ENDDATENUM THEN @ENDDATENUM ELSE JBCDT END ,
    CAST ( MASTER.DATETOCYMDDATE ( DATE ( JBCRT ) ) AS DEC ( 7 , 0 ) ) ,
    CAST ( 'DAYS  ' AS CHAR ( 6 ) )
    ) AS DAYS

FROM EMJOB
JOIN EMEQM ON JBUNT = EQMNUM
JOIN TABLE ( MASTER.CSVTABLE ( @LOCATIONS ) ) AS LOCATIONS ON VAL = EQMDOM --CSVTABLE function separates a csv list into a usable table
JOIN AAP030 ON CT1NUM = EQMDOM

WHERE JBSYS = 'PM'
AND ( JBCDT >= MASTER.DATETOCYMDDATE ( DATE ( @STARTDATE ) ) OR JBCDT = 0 )
AND JBCRT <= @ENDDATE
AND JBCRT > '2012-01-01-00.00.00.000000'  -- bad data before 2012
AND JBSTS <> 'D'

UNION

-- History File --

SELECT
EQMDOM AS ID ,
CT1TID AS ABBREVIATION ,
CT1NAM AS NAME ,
JHCTL ,
JHWKA AS TYPE ,
CASE WHEN JHCDT BETWEEN @STARTDATENUM AND @ENDDATENUM THEN 1 ELSE 0 END AS COMPLETED ,
1 AS CREATEDORDUE ,
-- Stackexchange question
CASE
    WHEN ( JHCDT BETWEEN @STARTDATENUM AND @ENDDATENUM AND JHDTC = 'RO' ) THEN 1
    ELSE 0
END AS COMPLETEDINSIDE ,
-- /Stackexchange question
MASTER.USDATE_DATEDIFFERENCE (
    CASE WHEN JHCDT = 0 THEN @ENDDATENUM WHEN JHCDT > @ENDDATENUM THEN @ENDDATENUM ELSE JHCDT END ,
    CAST ( MASTER.DATETOCYMDDATE ( DATE ( JHCRT ) ) AS DEC ( 7 , 0 ) ) ,
    CAST ( 'DAYS  ' AS CHAR ( 6 ) )
    ) AS DAYS

FROM EMJOBH
JOIN EMEQM ON JHUNT = EQMNUM
JOIN TABLE ( MASTER.CSVTABLE ( @LOCATIONS ) ) AS LOCATIONS ON VAL = EQMDOM
JOIN AAP030 ON CT1NUM = EQMDOM

WHERE
JHSYS = 'PM'
AND ( JHCDT >= MASTER.DATETOCYMDDATE ( DATE ( @STARTDATE ) ) OR JHCDT = 0 )
AND JHCRT <= @ENDDATE
AND JHCRT > '2012-01-01-00.00.00.000000'
AND JHSTS <> 'D'

)
AS METRICS
JOIN JOBTYPES AS JOBTYPE
    ON METRICS.TYPE = JOBTYPE.TYPE
GROUP BY METRICS.ID , METRICS.ABBREVIATION , METRICS.NAME , METRICS.TYPE , JOBTYPE.NAME ;

In situations where one static sql (procedure) executes better than an equivalent one, I would examine:

a) whether this depends on the order of execution, i.e. does the second one benefit from the first one reading data from disk into the bufferpool? If one runs better than the other regardless of the order they are run in this is not the case.

b) are the plans equal? If one procedure is compiled with one set of statistics, and the other compiled with another set of statistics this could result in different plans. Try rebinding (or recreating) both procedures with the same statistics and see if they still differ


Moving two databases from MySQL 5.1 [Prod] to MySQL 5.6 [Stage], not working [Windows] I'm in the process of upgrading our production MySQL from 5.1 to 5.6, on a Windows Server 2008 R2 server. What I'm trying to achieve is to move 2 databases [say abc and xyz] from Prod server to a newly created MySQL 5.6 stage environment.

At first, I tried -

C:\>mysqldump -uroot -p abc > abc.sql [from the 5.1 prod server]
C:\>mysql -uroot -p abc < abc.sql [on the 5.6 stage server]

Here, I got the error -

  ERROR 1049 <42000>: Unknown database 'abc'

When I opened the abc.sql file, I didn't find the two lines -

CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'abc' /*!40100 DEFAULT CHARACTER SET utf8 */;
USE 'abc';

I thought to export the db by specifying --database option, so I tried -

C:\>mysqldump -uroot -p --database abc > abc.sql [from the 5.1 prod server]
C:\>mysql -uroot -p abc < abc.sql [on the 5.6 stage server]

Now, the CREATE DATABASE and Use <dbname> are there, but still I'm getting the error -

  ERROR 1049 <42000>: Unknown database 'abc'

Anyone has any idea what I am doing wrong? Same is the case with the database 'xyz'.

Doing this

C:> mysqldump -uroot -p abc > abc.sql

does not include the two lines you mentioned.

On the Prod Server, do this

C:> mysqldump -uroot -p --databases abc > abc.sql

Using --databases abc makes mysqldump write those two lines

On the Staging Server, do this

C:> mysql -uroot -p < abc.sql

Don't specify abc upon reload. Those two lines will create abc (if it is not already there) and make abc the default database before loading the data.


TSQuery To Search Number and UoM in Text I'm beginner with full text search so I'm learning this feature using psql following a tutorial i found here. I modify the data sample to meet my own need:

INSERT INTO documents (document_text) VALUES
('Pack my box with five inches liquor jugs.'),
('Jackdaws love my big sphinx of quartz.'),
('The 5" wizards jump quickly.'),
('How vexingly quick daft zebras jump!'),
('Bright vixens jump; dozy fowl quack.'),
('Sphinx of black quartz, judge my vow.');

And tried to search using several combination of keyword hoping to get document #1 and #3, such as:

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('5"');

output: document #3 only

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('5&inch');

output: null

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('5&in');

output: document #3 only

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('five&in');

output: document #1 only

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('inch&five');

output: document #1 only

While naturally we can assume that 5" / 5 in / five inches all are same, why the fts give different result (can't output both document #1 and #3)? If I use the fts in wrong way, please show me the right implementation of it or if I need to use other search engine to solve my problem, which search engine should I use?

FTS is very clever in some ways, but not very clever in other ways.  It is not clever (by default) in the way you want it to be.

You can set up a thesaurus to map words to synonyms, but I don't know if there is any way to get your hands on the " symbol in order to map it. And of course it is unlikely to make sense to convert every ", or for that matter every 'in', into 'inches'.


Can someone decipher this deadlock for me? We don't have a real DBA in our shop at this time, I'm filling in the best I can.  It looks like the INSERT has taken place first and is holding a lock the SELECT FOR UPDATE needs.  But the INSERT is also waiting on another lock, is that other lock already held by the SELECT FOR UPDATE, or is there another transaction not being shown in INNODB STATUS.

INNODB STATUS:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-11-24 11:42:33 0x1470
*** (1) TRANSACTION:
TRANSACTION 191471, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1136, 9 row lock(s)
MySQL thread id 7, OS thread handle 7700, query id 57301 localhost 127.0.0.1 resonant Creating sort index
select this_.Id as Id80_0_, this_.Queue as Queue80_0_, this_.OriginalQueueName as Original3_80_0_, this_.Message as Message80_0_, this_.Retries as Retries80_0_, this_.Locked as Locked80_0_, this_.Failed as Failed80_0_, this_.LastTryDate as LastTryD8_80_0_, this_.Context as Context80_0_, this_.CreateDate as CreateDate80_0_ from sys_messages this_ where this_.Queue<>'QDead' and this_.Locked=0 and this_.Failed=0 and this_.CreateDate<='2015-11-24 11:42:31.24' order by this_.CreateDate asc for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 675 page no 5 n bits 296 index idx_sys_messages_Locked_Failed_Queue of table `hmuw`.`sys_messages` trx id 191471 lock_mode X waiting
Record lock, heap no 226 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 00; asc  ;;
1: len 1; hex 00; asc  ;;
2: len 9; hex 515365727669636573; asc QServices;;
3: len 30; hex 323235383235656633623034643933343061303130613261326465353733; asc 225825ef3b04d9340a010a2a2de573; (total 32 bytes);

*** (2) TRANSACTION:
TRANSACTION 191469, ACTIVE 2 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 9
MySQL thread id 5, OS thread handle 5232, query id 57358 localhost 127.0.0.1 resonant update
insert into sys_messages (Queue, OriginalQueueName, Message, Retries, Locked, Failed, LastTryDate, Context, CreateDate, Id) values ('QEvents', 'QEvents', x'ACED000573720037636F6D2E7265736F6E616E742E6875622E6576656E74732E6E6F74696669636174696F6E732E4576656E744E6F74696669636174696F6E00000000000000010200024C0004646174617400104C6A6176612F7574696C2F4C6973743B4C0007737464446174617400454C636F6D2F7265736F6E616E742F6875622F6576656E74732F6E6F74696669636174696F6E732F4576656E745374616E646172644E6F74696669636174696F6E446174613B7870737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A657870000000157704000000157372003B636F6D2E7265736F6E616E742E6875622E6576656E74732E6E6F74696669636174696F6E732E4576656E744E6F74696669636174696F6E4461746100000000000000010200014C000673704461746171007E000178707371007E00040000000177040000000173720043636F6D2E7265736F6E616E742E6875622E6576656E74732E6E6F74696669636174696F6E732E4576
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 675 page no 5 n bits 296 index idx_sys_messages_Locked_Failed_Queue of table `hmuw`.`sys_messages` trx id 191469 lock_mode X locks rec but not gap
Record lock, heap no 226 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 00; asc  ;;
1: len 1; hex 00; asc  ;;
2: len 9; hex 515365727669636573; asc QServices;;
3: len 30; hex 323235383235656633623034643933343061303130613261326465353733; asc 225825ef3b04d9340a010a2a2de573; (total 32 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 675 page no 5 n bits 296 index idx_sys_messages_Locked_Failed_Queue of table `hmuw`.`sys_messages` trx id 191469 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 191 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 00; asc  ;;
1: len 1; hex 00; asc  ;;
2: len 7; hex 514576656e7473; asc QEvents;;
3: len 30; hex 396263626431636533623034636563383061303130613261326465353733; asc 9bcbd1ce3b04cec80a010a2a2de573; (total 32 bytes);

*** WE ROLL BACK TRANSACTION (1)
-

2 Types of deadlocks.
    1. true cycle in the waits-for graph
    2. waits-for graph that is too expensive to check for cycles.

If InnoDB has to check more than a million locks in a graph, or if it recurses
through more than 200 transactions while checking, it gives up and says there's a deadlock. These numbers are hardcoded constants and cannot be configured (although recompile is possible).

The last line suggests Transaction 1 was chosen as a deadlock victim and was rolled back.

Thus when you re-run the transaction that was rolled back, it might have to wait for other transactions to complete, but typically the deadlock does not recur. Please try...


SQL Server: Understanding log ship utility: sqllogship.exe This is my first time using the stock SQL log shipping setup. The copy operation is failing.

I think I see the issue. I need to change the unc:

 '\\Win-2ooc0jplr5k\sql\logbackup\', Backup Destination Directory:
 '\\10.37.155.187\sql\logs\',

I need to change that first unc to an IP, because this environment is not resolving names. (No domain, etc). But when I look at the job, it has this:

sqllogship.exe" -Copy 34FCC5A2-8A2C-4581-9A58-04BC60E42DEF -server WIN-2OOC0JPLR5K

How do I modify the from/to unc paths?

(I am on SQL 2014, but I believe this topic spans several versions)

In order to change the settings, you don't alter the job directly. Go to the primary database in the log shipping setup in SSMS, right click, choose tasks, then ship transaction logs.

Click on the button highlighted, which will take you to the following screen:-

This screen will allow you to change the UNC path to an IP address


Are there any guidelines for the "IBM Premier Level Business Partner"? Our company currently subcontracts a DB2 project to a company which have "IBM Premier Level Business Partner". I was just wondering, are there any guideline about what they should provide (say, SQAP, source code....) as an IBM partner?

I ask this because we are not too familiar with database development and the db contract terms. Therefore, we may want to make sure of what we should have after the contract is finished.

Thanks~

Sorry, it may not be a suitable place for not programming related question, please suggest me a suitable place if I'm posted wrong.

I do not know if what you are looking for is stated in some place. The closest thing could be PartnerWorld in IBM: https://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/pw_home_pub_index

I think a partner is just people close to IBM, with a lot of IBM product knowledge, but they can do almost whatever they want with the client, they just have to follow some criteria based of sells, courses, etc. This is the reason, there is a big competence between partners.


Reference column alias in same SELECT list I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this:

SELECT
    samples.id,
    samples.wet_weight / samples.dry_weight - 1 AS percent_water,
    100 * percent_water AS percent_water_100
FROM samples;

When I do this in PostgreSQL, Postgres throws an error:

  ERROR: column "percent_water" does not exist.

Here's how I can work around it, by selecting out of a sub-selection:

SELECT
    s1.id,
    s1.percent_water,
    100 * s1.percent_water AS percent_water_100
FROM (
    SELECT
        samples.id,
        samples.wet_weight / samples.dry_weight - 1 AS percent_water
    FROM samples
    ) s1;

Is there any kind of shortcut like in the first code block to get around complicated nesting? I could also just say 100 * (samples.wet_weight / samples.dry_weight - 1) AS percent_water_100, but this is just a small example out of what is a much larger system of math going on in my code, with dozens of more complex bits of math stacked on top of each other. I'd prefer to do as cleanly as possible without repeating myself.

It's inconvenient sometimes, but it's SQL standard behavior, and it prevents ambiguities. You cannot reference column aliases in the same SELECT list.

There are shorter syntax options:

SELECT s.*, s.percent_water * 100 AS percent_water_100
FROM  (
   SELECT id, wet_weight / NULLIF(dry_weight - 1, 0) AS percent_water
   FROM   samples
   ) s;

And you can use a LATERAL join in Postgres 9.3+:

SELECT s.id, s1.percent_water
     , s1.percent_water * 100 AS percent_water_100
FROM   samples s
     , LATERAL (SELECT s.wet_weight / NULLIF(s.dry_weight - 1, 0) AS percent_water) s1;

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#LATERAL_JOIN
How to retrieve closest value based on look-up table?

I added NULLIF() to defend against division-by-zero errors.


what is the best approach to send report by Email? I've been requested to create some sql server reports ( create a report and send them by email ) for some poeple. I thought it would be like 2 or 3 but now that they know I can do it, every week I need to create 1 or 2 more reports.
I use SQL Agent and some steps to create a table, populate with the desired results, then I send it inside a xls file. Thats really not a problem, But I would like to know what can be better used for this task, since now I have more then 20 jobs on my SQL Agent. its all well organized and etc but still, I dont like to be looking at it. I already have a lot of DBA jobs and I dont want to mix things.
Is reporting services a viable way to do this? can I normally create a query, them send it to an excel file and send it by email without using c# or visual studio?

Yes sure.. you can use the subscription and delivery of Reporting Services to send a report.
The available formats are:

XML file with report data
CSV (comma delimited)
PDF
MHTML (web archive)
Microsoft Excel
TIFF file
Microsoft Word

(source)
For delivery via email look here.


TempDB Configuration using 8 TempDB Data File On a production server with 512 GB RAM , 2 Sockets 24 Cores (Total Logical Processors 48), i have configured to have 8 TempDB Data Files ( on Primary) with each 4096 MB growth of 1024 MB and Log File set to 2048 MB with 1024 MB growth and Trace Flag 1117 configured in the startup.

the OLTP database of approx. 100 GB data file is been accessed by Dynamics AX application. the AX application server is on another server.

The concern is that the AX application is running very slow even after rebuilding/reorganizing indexes and setting the MAXDOP as per the AX Dynamics recommendation.

For which i tried to find the Avg Write performance of all database from
sys.dm_io_virtual_file_stats (io_stall_write_ms / num_of_writes < 20) it shows above 100 ms. though the read performance is well below 10 ms.

The TempDB is stored in the different Disk Drive (which is dedicate cluster storage H Drive - free space 66 GB ) and other  DB Files are stored  on different Disk Disk ( Cluster Storage Drive).

What needs to be done in this case to improvise the write performance.
Do i increase the TempDB Data File size more?

attaching the image of the output from sys.dm_io_virtual_file_stats.

?

Check these articles for some recommendation.

https://cloudblogs.microsoft.com/dynamics365/no-audience/2015/09/25/ax-performance-analyzing-key-sql-server-configuration-and-database-settings/

https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/configure-sql-server-and-storage-settings

Also, you could check for tempdb auto grow events, if there are, you could enable IFI, also check if Lock Pages in Memory is enabled.


Compatibility mode change does not reflect on secondaries I was of belief that changing compatibility mode on primary database in database mirroring would make the change for mirror database.

But it proved to be wrong. I queried using sys.databases on Primary where i changed compatibility mode to 120 , however on its mirrored database compatibility level was still 100. Why?

Am i using the incorrect fn here sys.databases?

Also if it does not change what does it mean, do i need to do failover/failback to reflect?

What if i had log shipping secondary database in read only as well where this change needs to reflect? Do i need to rebuild log shipping then?

Thanks

Below

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

is not propagated to secondaries in DB mirroring or AlwaysON since they ship log blocks vs transactions - (logshipping you take log backups and ship it to secondary server and restore them).

There is a reason why they should not change the DB compatibility level as you certify your application based on DB Compatibility level vs a particuliar version of sql server.

This is same as if you change the owner of database .. you have to change it on secondary when it becomes Primary.

  how can i really get this changed without breaking LS or mirroring if possible.

You dont have to break mirroring or LS. You have to just failover and failback.

For logshipping, since it is physical log backup-copy-restore, once you restore the database and bring it online or readonly it will reflect the change. For mirroring, you can just failover and failback.

The point is to bring the db to read/write state and do alter database in AG or mirroring configuration.


Unfamiliar syntax - Query with Parameters in Braces at the beggining I have run sp_WhoIsActive on one of our servers using the following syntax:

sp_whoisactive @get_plans = 1, @show_sleeping_spids = 0, @get_outer_command = 1, @get_locks = 1

and found a spid with the sql_command (the column shown when @get_outer_command is set to 1) as follows

(@p1 int,@p2 int)
Exec MyDatabase.MyProc @p1 @p2

When I try to run a query with this syntax on my test Adventureworks database:

(@be int)
SELECT  *
FROM    Person.Person
WHERE   BusinessEntityID = @be

I get the error

  Msg 1050, Level 15, State 1, Line 1
  This syntax is only allowed for parameterized queries.
  Msg 137, Level 15, State 2, Line 4
  Must declare the scalar variable "@FN".

so it seems to be something to do with parameterized queries. This makes sense as the variable @be is never set to a value

What is happening here?

You are right, the (@be int) shown applies to parameterized queries. Applications often parameterize the queries using sp_executesql, and then sent them to the sql server.

The query will be cached as (variables)QueryText
. And ofcourse, the values won't be cached in the text, since the query is parameterized.

Parameterized query example

Test Data

CREATE SCHEMA PERSON;

CREATE TABLE Person.Person( BusinessEntityID int );

INSERT INTO Person.Person(BusinessEntityID)
VALUES(1),(2),(3);

Query

exec sp_executesql N'SELECT  * FROM    Person.Person WHERE   BusinessEntityID = @be',N'@be int',@be=2

See the result in the cache with this query

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%Person%';

or

(@be int)SELECT  * FROM    Person.Person WHERE   BusinessEntityID = @be

With a procedure the caching will be different

Create the procedure

use test
go
create procedure dbo.myproc @dbname varchar(255)
as
select * from sys.databases where name = @dbname

Run the proc

exec dbo.myproc @dbname= 'master';

Result in cache

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%myproc%';

or

create procedure dbo.myproc @dbname varchar(255)  as  select * from sys.databases where name = @dbname

This can also happen when forced parameterization is enabled

Enable forced parameterization

ALTER DATABASE test SET PARAMETERIZATION FORCED

Same query, without parameters

SELECT  *
FROM    Person.Person
WHERE   BusinessEntityID = 5

Result in cache

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%Person%'

or

(@0 int)select * from Person . Person where BusinessEntityID = @0


How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character? I want to set a Unicode string variable to particular character based on its Unicode code point.

I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of SQL_Latin1_General_CP1_CI_AS.

According to Microsoft's NCHAR documentation, the NCHAR function takes an integer as follows:

  integer_expression

  When the collation of the database does not contain the supplementary
  character (SC) flag, this is a positive whole number from 0 through
  65535 (0 through 0xFFFF). If a value outside this range is specified,
  NULL is returned. For more information about supplementary characters,
  see Collation and Unicode Support.

  When the collation of the database supports the supplementary
  character (SC) flag, this is a positive whole number from 0 through
  1114111 (0 through 0x10FFFF). If a value outside this range is
  specified, NULL is returned.

So this code:

SELECT NCHAR(128512);

Returns NULL in this database.

I would like it to return the same as this:

SELECT N'';

How can I set a Unicode string variable (e.g. nvarchar) to an emoji using code (without using the actual emoji character) in a database where the collation "does not contain the supplementary character (SC) flag"?

Full list of emoji Unicode code points

(Ultimately I want any character to work. I just chose emoji for ease of reference.)

(Although the server is SQL Server 2008 R2, I'm also curious about any solutions for later versions.)

Assuming that there is no way, could I reference an inline user-defined function in another database which had an appropriate collation?

How do I find a collation which has the "supplementary character" flag?

This returns no records on our server:

SELECT * FROM sys.fn_helpcollations()
WHERE name LIKE 'SQL%[_]SC';

It seems like SQL Server 2012 introduced Latin1_General_100_CI_AS_SC which would work. Can you install collations on older instances?

Collation References:

Answer to What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
Microsoft's Supplementary Characters Collation Information
Microsoft's SQL Server 2008 R2 Collation List

Is there an explanation for why, regardless of collation, SQL Server can understand and deal with the extended characters except from the perspective of NCHAR?

The UCS-2 encoding is always 2 bytes per character and has a range of 0 - 65535 (0x0000 - 0xFFFF). UTF-16 (regardless of Big Endian or Little Endian) has a range of 0 - 1114111 (0x0000 - 0x10FFFF). The 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is 2 bytes per character while the range above 65536 / 0xFFFF is 4 bytes per character.

Windows and SQL Server started out using the UCS-2 encoding because it was available and UTF-16 hadn't been finalized yet. Fortunately, however, there was enough fore-thought put into the designs of UCS-2 and UTF-16 that the UCS-2 mappings are a complete subset of the UTF-16 mappings (meaning: the 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is UCS-2). AND, the 65536 - 1114111 (0x10000 - 0x10FFFF) range of UTF-16 is constructed from two Code Points in the UCS-2 range (ranges 0xD800 – 0xDBFF and 0xDC00 – 0xDFFF, specifically) that were reserved for this purpose and otherwise have no meaning. This combination of two Code Points is known as a Surrogate Pair, and Surrogate Pairs represent characters beyond the UCS-2 range which are known as Supplementary Characters.

All of that information explains two aspects of NVARCHAR / Unicode data in SQL Server:

Several built-in functions (not just NCHAR()) don't handle Surrogate Pairs / Supplementary Characters when not using a Supplementary Character-Aware Collation (SCA; i.e. one with _SC, or _140_ but not _BIN* in the name) because the non-SCA Collations (especially the SQL_ Collations) were originally implemented prior to UTF-16 being completed (sometime in 2000, I believe). The non-SQL_ Collations that have _90_ or _100_ in their names but not _SC have minimal support for Supplementary Characters in terms of comparison and sorting.
The full Unicode / UTF-16 character set can be stored, without any data loss, in the NVARCHAR / NCHAR / XML / NTEXT datatypes because UCS-2 and UTF-16 are the exact same byte sequences. The only difference is that UTF-16 makes use of the surrogate code points to construct Surrogate Pairs, and UCS-2 simply can't map them to any characters, hence they appear to the built-in functions as two unknown characters.

With that background info in mind, we can now go through the specific questions:

  I would like SELECT NCHAR(128512); to return the same as this: SELECT N'';

That can only happen if the current database — where the query is being executed — has a default Collation that is Supplementary Character-Aware, and those were introduced in SQL Server 2012. Built-in functions that have string input parameters can have the Collation provided inline via the COLLATE clause (i.e. LEN(N'string' COLLATE Some_Collation_SC) ) and don't need to be executed within a Database that has an SCA default Collation. However, built-in functions such as NCHAR() accept an INT input parameter and the COLLATE clause isn't valid in that context (which is why NCHAR() only supports Supplementary Characters when the current database has a default collation that is Supplementary Character-Aware; but this is an unnecessary inconvenience that can be changed, so please vote for my suggestion: NCHAR() function should always return Supplementary Character for values 0x10000 - 0x10FFFF regardless of active database's default collation) .

  Is there an explanation for why, regardless of collation, SQL Server can understand and deal with the extended characters except from the perspective of NCHAR?

How SQL Server can store and retrieve Supplementary Characters without data loss was explained in the top section of this answer. But, it is not true that NCHAR is the only built-in function that has issues with Supplementary Characters (when not using an SCA Collation). For example, LEN(N'' COLLATE SQL_Latin1_General_CP1_CI_AS) returns a value of 2 while LEN(N'' COLLATE Latin1_General_100_CI_AS_SC) returns a value of 1.

If you go to the second link posted in the Question (i.e. "Microsoft's Supplementary Characters Collation Information") and scroll down just a little, you will see a chart of the built-in functions and how they behave based on the effective Collation.

  How do I find a collation which has the "supplementary character" flag?

In a version of SQL Server prior to 2012 you can't. But, starting with SQL Server 2012, you can use the following query:

SELECT col.*
FROM   sys.fn_helpcollations() col
WHERE  col.[name] LIKE N'%[_]SC'
OR     col.[name] LIKE N'%[_]SC[_]%'
OR     (COLLATIONPROPERTY(col.[name], 'Version') = 3
      AND col.[name] NOT LIKE N'%[_]BIN%');

Your query was close, but the pattern started with SQL and the SQL Server Collations (i.e. those starting with SQL_ ) have been deprecated for a while in favor of the Windows Collations (those not starting with SQL_ ). So, the SQL_ Collations are not being updated and hence have no newer versions that would include the _SC option (and starting in SQL Server 2017, all new collations automatically support Supplementary Characters and do not need, or have, the _SC flag; and yes, the query shown immediately above accounts for that as well as picking up the _UTF8 collations added in SQL Server 2019).

  Can you install collations on older instances?

No, you cannot install Collations into a previous version of SQL Server.

  How can I set a Unicode string variable (e.g. nvarchar) to a Supplementary Character using code (without using the actual Supplementary Character) in a database where the collation "does not contain the supplementary character (SC) flag"?
  ...
  Although the server is SQL Server 2008 R2, I'm also curious about any solutions for later versions.

When not using an SCA Collation, you can inject Code Points above 65535 / U+FFFF in two ways:

Specify the Surrogate Pair in terms of two calls to the NCHAR() function, each with one part of the pair
Specify the Surrogate Pair in terms of converting the VARBINARY form of the Little Endian (i.e. reversed) byte sequence.

These two methods of inserting Supplementary Characters / Surrogate Pairs will work even if the effective Collation is Supplementary Character-Aware, and should work just the same across all versions of SQL Server, at least as far back as 2005 (though probably would also work in SQL Server 2000 as well).

Example:

Character:

Name:                Pile of Poo
Decimal:            128169
Code Point:       U+1F4A9
Surrogate Pair: U+D83D & U+DF21

SELECT N'', --
       UNICODE(N'' COLLATE Latin1_General_100_CI_AS), -- 55357
       UNICODE(N'' COLLATE Latin1_General_100_CI_AS_SC), -- 128169
       NCHAR(128169), --  in DB with _SC Collation, else NULL
       NCHAR(0x1F4A9), --  in DB with _SC Collation, else NULL
       CONVERT(VARBINARY(4), 128169), -- 0x0001F4A9
       CONVERT(VARBINARY(4), N''), -- 0x3DD8A9DC
       CONVERT(NVARCHAR(10), 0x3DD8A9DC), --  (regardless of DB Collation)
       NCHAR(0xD83D) + NCHAR(0xDCA9) --  (regardless of DB Collation)

UPDATE

You can use the following iTVF to get the Surrogate Pair values (in both INT and BINARY form) from any Code Point between 65536 - 1114111 (0x010000 - 0x10FFFF). And, while the input parameter is of type INT, you can pass in the binary / hex form of the Code Point and it will implicitly convert to the correct integer value.

CREATE FUNCTION dbo.GetSupplementaryCharacterInfo(@CodePoint INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

WITH calc AS
(
  SELECT 55232 + (@CodePoint / 1024) AS [HighSurrogateINT],
         56320 + (@CodePoint % 1024) AS [LowSurrogateINT]
  WHERE  @CodePoint BETWEEN  65536 AND 1114111
)
SELECT @CodePoint AS [CodePointINT],
       HighSurrogateINT,
       LowSurrogateINT,
       CONVERT(VARBINARY(3), @CodePoint) AS [CodePointBIN],
       CONVERT(BINARY(2), HighSurrogateINT) AS [HighSurrogateBIN],
       CONVERT(BINARY(2), LowSurrogateINT) AS [LowSurrogateBIN],
       CONVERT(binary(4), NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT)) AS [UTF-16LE],
       NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT) AS [Character]
FROM   calc;
GO

Using the above function, the following two queries:

SELECT * FROM dbo.GetSupplementaryCharacterInfo(128169);

SELECT * FROM dbo.GetSupplementaryCharacterInfo(0x01F4A9);

both return the following:

CodePoint  HighSurrogate  LowSurrgate  CodePoint  HighSurrgate  LowSurrgate  UTF-16LE   Char
INT        INT            INT          BIN        BIN           BIN                     actr
128169     55357          56489        0x01F4A9   0xD83D        0xDCA9       0x3DD8A9DC

UPDATE 2: An Even Better Update!

I have adapted the iTVF shown above to now return 188,657 code points so you don't need to fit it any particular value. Of course, being a TVF, you can add a WHERE clause to filter on a particular code point, or range of code points, or "similar characters", etc. And, it includes additional columns with pre-formatted escape sequences to construct each code point (both BMP and Supplementary Characters) in T-SQL (without requiring an "_SC" or "_140_" collation), HTML (and XML), the style common to many app languages ("\uHHHH" ; used for C++ / C# / F# / Java / JavaScript / Julia / etc), and finally the slightly newer, other common style that handles all code points, not just BMP ("\UHHHHHHHH" ; used for C / C++ / C# / F# / Julia / etc).

Read all about it here:

SSMS Tip #3: Easily Access/Research ALL Unicode Characters (Yes, Including Emojis 😸)


Migrating database from SQL Server 2005 Enterprise to SQL Server 2008 Standard I'm attempting to migrate a database from SQL Server 2005 Enterprise to SQL Server 2008 Standard. I've run into an issue because there is a partition function defined in the database of the Enterprise edition. I know Standard editions do not support partition functions. Upgrading the version of SQL Server I'm migrating to Enterprise isn't an option.

I'm looking for a way to remove the partition function without causing any negative effects to the database currently so I can migrate it to the 2008 Standard Edition.

Any help would be greatly appreciated. Thanks!

Well, if you're only concerned with how to get around this partitioning issue you have, and are sure you won't face performance issues after you move to a single partition, then you could try to collapse all of your partitions into 1, and there's lots of ways to do.

If you feel your system can handle it, you could dump all the data into another table and give it a max ID number to dump into.  Then when the bulk of the data is copied into there, you could note the ID, find any new records that came in.  If it's a huge amount of records, dump those new records in the new table as well.  After the 2nd dump you should be almost caught up.  At that point you do not allow new data to come in for a few seconds/minutes while you run pre-scripted out commands to:

-move the remaining data into the new table that doesn't have partitioning enabled after making sure the schemas match.

-rename the old table.  Make sure you don't have blocking issues.

-rename the new table to the old table.

You might want to avoid adding indexes at the beginning, and do those at the very end.

If doing this on the live system isn't an option, you could move the data to another system and do all these steps there.

Perhaps you could use this opportunity to archive your old data.  You could consider creating a view and have your old data in a different archive read only database and have it referenced with a view.

How much data is it anyways, with and without indexes?