SQL Server 2014 - UPSERT XML Multiple rows We have an input like this

    <?xml version="1.0" encoding="UTF-8"?>
    <Orders xmlns:jjsales:api:sales>
        <Order>
            <OrderID>334</OrderID>
            <AmountPaid currencyID="EUR">17.10</AmountPaid>
            <UserID>marc58</UserID>
            <ShippingAddress>
                <Name>Marc Juppé</Name>
                <Address>Rue</Address>
                <City>Paris</City>
                <StateOrProvince></StateOrProvince>
                <Country>FR</Country>
                <Phone>333333333</Phone>
                <PostalCode>22222</PostalCode>
             </ShippingAddress>
             <ShippingCosts>4.50</ShippingCosts>
             <Lines>
                <Line>
                   <Details>
                        <ItemID>3664</ItemID>
                        <Store>47</Store>
                        <Title>MCPU DDA010</Title>
                        <SKU>mmx</SKU>
                    </Details>
                    <LineID>1111</LineID>
                    <Quantity>1</Quantity>
                    <Price currencyID="EUR">6.2</Price>
                </Line>
                <Line>
                   <Details>
                        <ItemID>3665</ItemID>
                        <Store>45</Store>
                        <Title>MCPU DFZ42</Title>
                        <SKU>mmy</SKU>
                    </Details>
                    <LineID>1112</LineID>
                    <Quantity>2</Quantity>
                    <Price currencyID="EUR">3.2</Price>
                </Line>
            </Lines>
        </Order>
    </Orders>

and we are currently importing data into Sales Table with a query like this:

    INSERT INTO Sales (ItemID,Store,Title,SKU,Quantity,Price,OrderID,AmountPaid,UserID,ShippingCosts)

    SELECT I.X.value('(Details/ItemID/text())[1]', 'int') as ItemID,
           I.X.value('(Details/Store/text())[1]', 'int') as Store,
           I.X.value('(Details/Title/text())[1]', 'nvarchar(100)') as Title,
           I.X.value('(Details/SKU/text())[1]', 'nvarchar(100)') as SKU,
           I.X.value('(Quantity/text())[1]', 'int') as Quantity,
           I.X.value('(Price/text())[1]', 'decimal(11,2)') as Quantity,
           O.X.value('(OrderID/text())[1]', 'int') as OrderID,
           O.X.value('(AmountPaid/text())[1]', 'decimal(11,2)') as AmountPaid,
           O.X.value('(UserID/text())[1]', 'nvarchar(100)') as UserID,
           O.X.value('(ShippingCosts/text())[1]', 'decimal(11,2)') as ShippingCosts
    FROM @XML.nodes('/Orders/Order') as O(X)
    CROSS APPLY O.X.nodes('Lines/Line') as I(X)

but now we need to update records that already exists checking Lines.Line.LineID

Since do not want to use MERGE, we thought it was easy to solve adding the usual

    IF NOT EXISTS (SELECT 1 FROM Sales where LineID=@LineID)
        INSERT INTO Sales () SELECT ....
    ELSE
        UPDATE Sales SET ItemID=..., Store=..., ... WHERE LineID=@LineID

but the value of @LineID in inside the XML and has to be calculated while parsing the XML

And discovered have no idea how to get it

Can suggest how to get the value of LineID while parsing the XML?

Thanks

Can suggest how to get the value of LineID while parsing the XML?

You can take this output in a variable to check for existence.
DECLARE @input XML = '<Orders>
        <Order>
            <OrderID>334</OrderID>
            <AmountPaid currencyID="EUR">17.10</AmountPaid>
            <UserID>marc58</UserID>
            <ShippingAddress>
                <Name>Marc Juppé</Name>
                <Address>Rue</Address>
                <City>Paris</City>
                <StateOrProvince></StateOrProvince>
                <Country>FR</Country>
                <Phone>333333333</Phone>
                <PostalCode>22222</PostalCode>
             </ShippingAddress>
             <ShippingCosts>4.50</ShippingCosts>
             <Lines>
                <Line>
                   <Details>
                        <ItemID>3664</ItemID>
                        <Store>47</Store>
                        <Title>MCPU DDA010</Title>
                        <SKU>mmx</SKU>
                    </Details>
                    <LineID>1111</LineID>
                    <Quantity>1</Quantity>
                    <Price currencyID="EUR">6.2</Price>
                </Line>
                <Line>
                   <Details>
                        <ItemID>3665</ItemID>
                        <Store>45</Store>
                        <Title>MCPU DFZ42</Title>
                        <SKU>mmy</SKU>
                    </Details>
                    <LineID>1112</LineID>
                    <Quantity>2</Quantity>
                    <Price currencyID="EUR">3.2</Price>
                </Line>
            </Lines>
        </Order>
    </Orders>
'

   SELECT
      LineId = XCol.value('(LineID)[1]','int')
      FROM
      @input.nodes('/Orders/Order/Lines/Line') AS XTbl(XCol)

Here is the result set I get:

LineId
1111
1112


Concurrent update queries locking rows when `where` clause doesn't match My application sends messages to Slack.  On very rare occasions we may need to retract the message going out.  These retractions are handled by queueing a job for each messageId that has been created thus far.

I noticed during our last retraction about a week ago that we saw about 2k:

try restarting transaction (SQL: update `message` set `deleted_at` = 2018-12-04 04:47:44 where (`slack_channel_id` = xxxxxxxxxxxxxxxx and message_id = xxxxxxxxxxxxxxxx))"

It seems like the queue jobs (which are absolutely happening concurrently) are stepping on each other's toes.  My expectation is that this query would only put a lock on the individual row it needs (we have a compound unique index on slack_server_id, slack_channel_id and message_id), but it seems like more than one job is locking this record.

Why are these concurrent update queries locking rows that don't match the where clause?

CREATE TABLE `messages` (
  `slack_id` bigint(20) unsigned NOT NULL,
  `slack_channel_id` bigint(20) unsigned NOT NULL,
  `message_id` bigint(20) unsigned NOT NULL,
  `slack_message_id` bigint(20) unsigned DEFAULT NULL,
  `premium` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  UNIQUE KEY `slack_message_slack_id_channel_id_message_id_unique` (`slack_id`,`slack_channel_id `,`message_id`),
  KEY `slack_message_message_id_foreign` (`message_id`),
  CONSTRAINT `slack_message_slack_id_foreign` FOREIGN KEY (`slack_id`) REFERENCES `slacks` (`id`),
  CONSTRAINT `slack_message_message_id_foreign` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Update

I've added an id auto increment primary column and it did not resolve the transaction issues.

A lesson to learn about indexing.  INDEX(a,b,c) is not optimal for WHERE a=1 AND c=2.  This is because only the leftmost column(s) of an index will be used; there cannot be any skipping over columns (b, in this example).

Back to your question.

INDEX(slack_channel_id, message_id)

is needed, and

INDEX(`slack_id`,`slack_channel_id `,`message_id`)

is not useful at all.  slack_id is in the way.

KEY `slack_message_message_id_foreign` (`message_id`),

will be partially useful -- namely for narrowing the search down to those rows with the desired message_id.

A Cookbook on building optimal indexes.


Does the concept of candidate key exist only in theory? I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate key in any of the SQL database management systems, say SQL Server, Postgres, MySQL, Oracle etc.?

Is there any reserved keyword for designating column(s) as a candidate key like PRIMARY KEY or UNIQUE in case of primary key column and unique column?

I feel UNIQUE constraint itself provides implementation of the candidate key concept. I don't see any practical value of having a separate CANDIDATE KEY keyword. Is it so?

As far as I know, no SQL database management system (DBMS) supplies the CANDIDATE KEY keyword as such, but (as I consider that you are suggesting in the question) that does not mean that the notion (or the functionality) of candidate key cannot be configured in a SQL table.
How to represent a candidate key
For example, if

there is no primary declared for the table under consideration, and
a whole set of columns (i.e., one or more) that is configured with a UNIQUE constraint is also fixed with the corresponding NOT NULL constraint(s),

then the designer is, precisely, representing a candidate key.
For example, the following table shows three distinct candidate keys:
CREATE TABLE Foo (
    FooId  INT      NOT NULL,
    Bar    CHAR(30) NOT NULL,
    Baz    DATETIME NOT NULL,
    Qux    INT      NOT NULL,
    Corge  CHAR(25) NOT NULL,
    Grault INT      NOT NULL,
    Garply BIT      NOT NULL,
    Plugh  TEXT     NOT NULL,
    CONSTRAINT Foo_CK1 UNIQUE (FooId),          -- Single-column CANDIDATE KEY
    CONSTRAINT Foo_CK2 UNIQUE (Bar),            -- Single-column CANDIDATE KEY
    CONSTRAINT Foo_CK3 UNIQUE (Baz, Qux, Corge) -- Multi-column  CANDIDATE KEY
);

A candidate key set up in this manner is, as you know, susceptible of being the reference of one or more foreign key constraints.
It is worth to stress the fact that, since SQL and its dialects include the idea of NULL marks, a UNIQUE constraint alone is not sufficient to stand for a candidate key (as expounded in the DDL sample above). This point is particularly significant because the column(s) constrained as a candidate key cannot retain NULL marks, otherwise it could not be deemed a true candidate key (besides, there are reasons to argue that a table enclosing NULL marks in any of its columns cannot be considered a relational table but, yes, that is a different subject).
How does this differ from the CANDIDATE KEY keyword approach?
In this way, if the vendors/developers of a certain SQL DBMS wants to provide the CANDIDATE KEY keyword, then this kind of constraint, apart from the evident uniqueness enforcement, must also ensure the rejection of any attempt to insert NULL marks in relevant column(s), factor that would make it different from the approach combining the UNIQUE and NOT NULL constraint(s).
How to portray an alternate key
If, on the contrary,

a certain set of columns was chosen and defined as the PRIMARY KEY of a given table, and
other set of columns is constrained as UNIQUE, and each of the members of such set is also fixed with a NOT NULL constraint,

then the designer is representing an alternate key (if, a certain table has one or more candidate keys, and one of these is granted the status of primary, then the remaining ones become alternate keys).
For instance, the following table presents one PRIMARY KEY and three ALTERNATE KEYs:
CREATE TABLE Foo (
    FooId  INT,
    Bar    CHAR(30) NOT NULL,
    Baz    DATETIME NOT NULL,
    Qux    INT      NOT NULL,
    Corge  CHAR(25) NOT NULL,
    Grault INT      NOT NULL,
    Garply BIT      NOT NULL,
    Plugh  TEXT     NOT NULL,
    CONSTRAINT Foo_PK  PRIMARY KEY (FooId),           -- Single-column PRIMARY KEY
    CONSTRAINT Foo_AK1 UNIQUE      (Bar),             -- Single-column ALTERNATE KEY
    CONSTRAINT Foo_AK2 UNIQUE      (Baz, Qux, Corge), -- Multi-column  ALTERNATE KEY
    CONSTRAINT Foo_AK3 UNIQUE      (Grault)           -- Single-column ALTERNATE KEY
);

An alternate key put up as demonstrated above can be, evidently, referenced from one or more foreign key constraints.
Using the CANDIDATE KEY keyword when there is already a PRIMARY KEY?
Assuming that there is a DBMS that does provide the CANDIDATE KEY keyword, if a table has a primary key declared, then the creation of a candidate key should be rejected, and said DBMS should as well provide the ALTERNATE KEY keyword to represent one or more alternate keys when applicable.
Illustration of the same table (i) with one candidate key and (ii) with primary key
Yes, at the logical level of abstraction of a database, a candidate key that is established by means of a table-level UNIQUE constraint in conjunction with the applicable column-level NOT NULL counterpart(s), as exemplified as follows:
CREATE TABLE Foo (
    FooId  INT      NOT NULL,
    Bar    CHAR(30) NOT NULL,
    Baz    DATETIME NOT NULL,
    CONSTRAINT Foo_CK UNIQUE (FooId)
);

…would be equivalent to a primary key set up as shown below:
CREATE TABLE Foo (
    FooId  INT,
    Bar    CHAR(30) NOT NULL,
    Baz    DATETIME NOT NULL,
    CONSTRAINT Foo_PK PRIMARY KEY (FooId) -- Single-column PRIMARY KEY, constraint that implies the rejection of NULL marks.
);

This is so because, if a given table has only one candidate key (which, as illustrated before, can be composite, i.e., made up of two or more columns), then it can be considered, automatically, the primary key.
Physical-level support
And, yes, in order to support a UNIQUE constraint, some DBMSs may employ an index whose type is different from the one of the index utilized to sustain a PRIMARY KEY counterpart (e.g., non-clustered vs clustered), but this is a factor that is part of the physical (or internal) level of abstraction (which, by the way, might or might not apply depending of the DBMS of use), therefore it is entirely outside of the scope of the logical level constraints configured for a table (as long as the DBMS guarantees the uniqueness of the values contained in the column[s] involved).


Transaction log autogrowth duration I am using event notifications to capture data and log file autogrowth events for all databases on my servers.  I'm using the data to for analysis of database storage configuration.

In looking at the data I've noticed that the average duration for transaction log growth is well above anything I would expect which leads me to think that I'm either misinterpreting the data or overlooking something related to how transaction log autogrowth works.

This is an example of a log file growth event that was captured today:

<EVENT_INSTANCE>
  <EventType>LOG_FILE_AUTO_GROW</EventType>
  <PostTime>2013-08-29T11:14:26.447</PostTime>
  <SPID>97</SPID>
  <DatabaseID>32</DatabaseID>
  <NTDomainName />
  <HostName>[cleansed]</HostName>
  <ClientProcessID>4884</ClientProcessID>
  <ApplicationName>.Net SqlClient Data Provider</ApplicationName>
  <LoginName>[cleansed]</LoginName>
  <Duration>4173000</Duration>
  <StartTime>2013-08-29T11:14:22.263</StartTime>
  <EndTime>2013-08-29T11:14:26.437</EndTime>
  <IntegerData>64000</IntegerData>
  <ServerName>[cleansed]</ServerName>
  <DatabaseName>MyDB</DatabaseName>
  <FileName>MyDB_log</FileName>
  <LoginSid>[cleansed]</LoginSid>
  <EventSequence>14637017</EventSequence>
  <IsSystem />
  <SessionLoginName>[cleansed]</SessionLoginName>
</EVENT_INSTANCE>

Since duration is reported in miliseconds I'm reading this as it taking 69.54 minutes to grow the file.  Autogrowth for this log file is set to 512MB (limited to 2TB)

SELECT
    growth AS GrowthPages,
    growth*8/1000 AS GrowthMB,
    max_size,
    is_percent_growth
FROM MyDB.sys.database_files
WHERE type=1

GrowthPages GrowthMB        max_size        is_percent_growth
64000       512             268435456       0

All of the databases log to the same volume which is connected to a SAN via fiberchannel.  (I'd have to get with our SAN admin for more details on the storage config if needed).

The instance is SQL 2012 Enterprise, server is Windows 2008 R2 Enterprise.

Why would it take over an hour to grow the log by 512MB?  We're not noticing a delay in operations on any of these databases (unless we're just overlooking it).  There are a handful of other databases with similar duration; their autogrowth settings are the same.  Other databases with smaller autogrowth settings have proportionally smaller durations.

You're looking at SQL Trace. Extended Events records duration in microseconds (though some data is reported in milliseconds, duration isn't affected). You can see that the event took less than 5 seconds:

<StartTime>2013-08-29T11:14:22.263</StartTime>
<EndTime>2013-08-29T11:14:26.437</EndTime>

And also just to confirm (though it's off a bit due to millisecond rounding in the datetime data type):

SELECT DATEDIFF(MICROSECOND,'2013-08-29T11:14:22.263','2013-08-29T11:14:26.437');

----------
4174000


Speeding up group by query in PostgreSQL 9.6.5 I have this table along with the respective indexes:

CREATE TABLE shares
(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    poolid TEXT NOT NULL,
    blockheight BIGINT NOT NULL,
    difficulty DOUBLE PRECISION NOT NULL,
    networkdifficulty DOUBLE PRECISION NOT NULL,
    payoutinfo TEXT NULL,
    miner TEXT NOT NULL,
    worker TEXT NULL,
    useragent TEXT NULL,
    ipaddress TEXT NOT NULL,
    created TIMESTAMP NOT NULL
);

CREATE INDEX IDX_SHARES_POOL_BLOCK on shares(poolid, blockheight);
CREATE INDEX IDX_SHARES_POOL_MINER on shares(poolid, miner);
CREATE INDEX IDX_SHARES_POOL_CREATED ON shares(poolid, created);

... and this relatively simple query:

EXPLAIN (BUFFERS, ANALYZE) SELECT SUM(difficulty), miner, worker FROM shares
WHERE poolid = 'xmr1' AND created <= '12/18/2017' GROUP BY miner, worker;

... resulting in this execution plan:

 GroupAggregate  (cost=3276833.38..3354436.07 rows=2387929 width=60) (actual time=9241.633..11041.639 rows=342 loops=1)
   Group Key: miner, worker
   Buffers: shared hit=960 read=560857 dirtied=5837 written=7378, temp read=114684 written=114684
   ->  Sort  (cost=3276833.38..3290264.23 rows=5372340 width=60) (actual time=9234.860..10468.246 rows=2940014 loops=1)
         Sort Key: miner, worker
         Sort Method: external merge  Disk: 352784kB
         Buffers: shared hit=960 read=560857 dirtied=5837 written=7378, temp read=114684 written=114684
         ->  Bitmap Heap Scan on shares  (cost=411031.05..2272306.15 rows=5372340 width=60) (actual time=462.250..4411.353 rows=2940014 loops=1)
               Recheck Cond: ((poolid = 'xmr1'::text) AND (created <= '2017-12-18 00:00:00'::timestamp without time zone))
               Rows Removed by Index Recheck: 9129527
               Heap Blocks: exact=40162 lossy=500188
               Buffers: shared hit=960 read=560857 dirtied=5837 written=7378
               ->  Bitmap Index Scan on idx_shares_pool_created  (cost=0.00..409687.97 rows=5372340 width=0) (actual time=454.259..454.259 rows=2940014 loops=1)
                     Index Cond: ((poolid = 'xmr1'::text) AND (created <= '2017-12-18 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=4 read=21463 written=356
 Planning time: 0.171 ms
 Execution time: 11081.661 ms
(17 rows)

Two questions:

Why did the planner chose to sort first and apply the filtering afterwards, causing the costly outer part of the plan?
What indexes could be created to speed up the query? I've experimented with a lot of multiple column indexes but none yielded any significant speed improvement

Why did the planner chose to sort first and apply the filtering afterwards, causing the costly outer part of the plan?

It didn't. You read the query from the inside out. Not from the top down.

  What indexes could be created to speed up the query? I've experimented with a lot of multiple column indexes but none yielded any significant speed improvement

Well, you have two in addition to indexing.

Your estimates are off. Pg expects 5.3 M rows, you return 2.9 M.
Your sort of 2.9 M rows is going to disk. If you can't eliminate the sort, you may want to look at adding more work_mem.

Try these in this order.

VACUUM FULL ANALYZE shares; and try again. See if you get a better estimate.
Drop all of the index you've got. Try this

CREATE INDEX zzz1 ON shares (poolid, created, miner, worker, difficulty);
ANALYZE shares;
CLUSTER shares ON zzz1;

See how that performs. You may want to go BRIN, your table is getting kind of big. Check into it anyway.

If you can normalize xml1, I would do that too. (Example 1::int and keep the xml elsewhere.) You should also check into moving to timestamp with time zone. You almost never want timestmap without time zone.


How to determine the read / write ratio of your server? When looking at the read / write ratio of your server would you look at the NUMBER of reads and writes or the number of BYTES read and written? For example, consider the following numbers from sys.dm_io_virtual_file_stats:

93384 reads, 1080818 writes, = 8% reads, 92% writes

30630654464 bytes read, 60955863552 bytes written, = 33% bytes read, 67% bytes written

For your specific question - Yes. I think looking at sys.dm_io_virtual_file_stats, as you are doing is a good way to see which tables have the most reads or writes or the highest throughput of reads/writes. And I would say that looking at the returned data is good to look at in three angles. Look at the number of operations (reads or writes), look at the stalls and stall time so you can see who is waiting on IO stalls the most and look at the total bytes transferred. I would probably look at the total size last and focus on IO Operations/second IOPs number first for what you are looking for here.

I think you should also look at sys.dm_db_index_usage_stats and look at the amount of times your tables and indexes are written to (user_updates) or read from (user_lookups, user_scans, and user_seeks) to get an idea of the distribution.

Steven brings up a good idea of looking to see the buffers as well.

For The WRITELOG Waits

I think you are heading in a good direction here to see which databases are your busiest in terms of writes. That said I would consider taking a look at a script like sp_whoisactive that Adam Machanic wrote. You can periodically run this and collect the results to a table and you can actually see which statements of yours are the ones most waiting on WRITELOG.

I would argue that you may save some time in tracking down the read/write ratios of your databases (though this is great information to know and track) in this case and you can dive into a couple of other places to start. Presuming you are seeing significant WRITELOG waits and suffering issues from it (You can see these waits happening and not have them be a problem. You should ask what kind of averages you are seeing, what frequency you see the counter and if there are symptoms - are queries taking longer than expected in an app? Are users complaining? Or are you just being proactive), you could, for instance:

Analyze your IO Subsystem. Ask questions like:

Are you logs and data files on the same drive?
What kind of latency (avg sec/write and avg sec/read) are you seeing to these drives? If you are seeing consistent averages of 20ms and up you could have an issue.. The higher the average and the longer the spikes, the more likely this is.
What do the disks look like for your log drive(s)?
And some other questions. Lie - Do you need to take two active log files and split them onto separate storage? Are you write cache optimized? SQL Server typically doesn't take much advantage of a read cache in IO but it loves some write cache.

Analyze your activity. Run sp_whoisactive. Get an idea of which queries are waiting for these waits, look at your insert activity in these databases.
Ensure transactions are being managed appropriately. Are you committing too frequently? Waiting too long to commit? Both could have an impact on time to write the log. Too frequently - especially in bulk operations means more commits, means more log records.. Too infrequently can mean you wait to accumulate too much.


Alias Names and Two Table Query Problems I'm quite new to querying databases, I've only been doing it for about 2 weeks in the current job that I'm in and have never touched SQL prior to this.

I'm working on trying to use alias names to pull two different sets of tables and display the fields in each table, all while ordering them by a specific field.  Here is the code I'm currently trying to use when to do that:

  SELECT ars.facility_id, ars.service_code, ars.coding_system,
  ars.result_label_seq, ars.display_seq, ats.facility_id,
  ats.service_code, ats.coding_system, ats.display_name, ats.test_name

  FROM anc_test_codes AS ats,
  anc_results_seqs AS ars

  WHERE ats.service_code = ars.service_code
  AND ars.coding_system = ats.coding_system
  AND ars.facility_id = ats.facility_id
  AND ats.facility_id = 'C' AND ats.service_code = 'CBC'
  ORDER BY ats.display_name

I'm sure my methods and style is totally hideous, so excuse this.  The error I'm getting is a generic Oracle SQL error: SQL Command Not Properly Ended - ORA-00933

Can anyone tell me what I'm doing wrong? Any help on this would be greatly appreciated.

Thanks!

Oracle does not support using the AS keyword for defining a table alias, only for column aliases. Just rewrite the query by placing a space between the table name and the alias and see if it runs that way.

In particular rewrite the FROM clause like this:

 FROM anc_test_codes ats,
   anc_results_seqs ars


Nice query to get my friends my table has this structure:

 User_ID | Followed_ID

Example row:

 33 | 36

Means that user 33 follows user 36.

If exists a record like

 36 | 33

then the two users are considered friends.
What's the cleanest way to get all friends of(for example) user 36? Thanks.

It can be done with a simple join (assuming the (user_id, follower_id) combination has a unique constraint):

SELECT
    f1.followed_id AS friend_id
FROM
    follow AS f1
  JOIN
    follow AS f2
      ON  f1.user_id = f2.followed_id
      AND f1.followed_id = f2.user_id
WHERE
    f1.user_id = 36 ;


How to reproduce "resource busy and acquire with NOWAIT specified or timeout expired " error on local machine in oracle I am running oracle database and I am getting error as "resource busy and acquire with NOWAIT specified or timeout expired " and because of this error i am getting some other exceptions. on my local machine I am not getting this "resource busy ..." error. basically I want to reproduce this error on my local machine . is there any way to reproduce this error manually on my local machine?

Not quite clear what you want, this is a generic example of ORA-00054.
Session 1:
SQL> create table t1 (c1 number);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL>

Session 2:
SQL> truncate table t1;
truncate table t1
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>


SQL Server 2014 Standard Ram and CPU I've got a VMWare VM that runs Windows Server 2012 R2, SQL Server 2014 Standard Edition, IIS (hosting custom web application), and SentryOne Monitoring. The VM is configured with 4 cores and 128GB of RAM, which is the maximum for the entry license for Standard Edition.

If I give the VM more cores and more RAM, will SQL Server try to use them, or will it ignore them?

This is my demo box for my company, I want to give the VM extra resources so the Web Application (IIS), and SentryOne can function quickly and reliably.  But I don't want to violate licensing.

I know I can restrict SQL's Memory usage, but what about CPU usage?

Per Brent Ozar Unlimited (linked below), the recommendation for SQL Server Standard 2014 is 192GB RAM. SQL Server will only use up to 128GB of it and the rest is to accommodate the overhead of OS and other processes.

This being said, 64GB may be far more than necessary to allow for the of SentryOne and the IIS application overhead.

How Much Memory Does SQL Server Need?

I believe that, for the cores, you must license each core that is allocated to the VM. You can limit the CPU's using the CPU page on the Server Properties.


Create job to send alert using sp_send_dbmail when table value greater than x PROBLEM

The SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached.

TABLE CREATE

CREATE TABLE [dbo].[SMSCounter](
  [Counter] [int] NOT NULL,
  [CounterDate] [datetime] NOT NULL )

EXAMPLE DATA

380, 2012-09-10 00:00:00.000
177, 2012-10-14 00:00:00.000
999, 2012-11-16 00:00:00.000

JOB SCRIPT

The script below is my attempt to create a scheduled job that will check every hour if the SMScounter.Counter is greater than 950.

USE DATABASEname
select (Counter) as CHECKVALUE from SMSCounter where smscounter.CounterDate  = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)
IF CHECKVALUE > 950

BEGIN

EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near',
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =
    'USE DATABASEname
     (select * from SMSCounter where CounterDate = GETDATE())'

END

QUESTIONS

How can I format GETDATE to examine dd/mm/yy and ignore the time part when reading SMScounter.CounterDate? (script above edited , works now).
How can I get the IF statement to work to compare CHECKVALUE to 950?

I'm using SQL Server 2008 standard edition.

You can use convert (date, GETDATE())  to get just the 'date' part:

select GETDATE(), convert (date, GETDATE())
----------------------- ----------
2012-11-16 08:58:20.750 2012-11-16

And if you're only ever going to be comparing 'date' parts, it might be worth changing the datatype in your table to DATE, to save having to convert each time you query it.

For an IF statement you need to pass in a value to be checked, and then wrap the code to be run within BEGIN and END statements. Here's an example using the sysobjects table:

declare @cnt integer
select @cnt=COUNT(1) from sysobjects

if @cnt>50
begin
  select 'hello'
end

which will say hello if you've more than 50 objects in your DB

So in your case:

USE DATABASEname
go
declare @counter integer

select @counter=MAX([Counter]) from SMSCounter where convert(date,CounterDate) = convert(date,GETDATE())

IF @counter > 950
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near',
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =  '(select * from DATABASEname.dbo.SMSCounter where convert(date,CounterDate) = convert)(Date,GETDATE()))'
END

I've added a MAX function to your check query, as this will only return a single row containing the maximum value for that day, otherwise it'll return a random value from all the matching rows. If you're overwriting the day's value each time you write to the table, then it's superfluous.


Any potential cons associated with enabling backup checksums when performing backups? I noticed that Ola Hallengren's backup sp has backup checksums disabled by default.  This made me curious to know if there are any potential cons (i.e. increased CPU, IO, long backup times, etc) associated with enabling backup checksums for sql server backups?

Yes, and you can verify that on the Backup Checksums doc:

Due to the overhead verifying and generating backup checksums, using
backup checksums poses a potential performance impact. Both the
workload and the backup throughput may be affected. Therefore, using
backup checksums is optional. When deciding to generate checksums
during a backup, carefully monitor the CPU overhead incurred as well
as the impact on any concurrent workload on the system.

But I advise you not to discard the checksum benefits just because of the cost it implies. Check the documentation to better understand the checksum and then decide whether to use it or not.
That same doc states that

after encountering a page checksum error, a BACKUP or RESTORE
operation fails and a RESTORE VERIFYONLY operation continues.

As you can see, when using checksum it's possible to find out that your backup is not reliable while taking it and not when you need to restore it (you don't wanna find yourself in this situation).

The reason checksums are not enabled by default in the backup stored
procedure DatabaseBackup is just to follow the defaults for the backup
command. I strongly recommend you to use checksums. @CheckSum = 'Y' is
also added in the jobs that comes with the installation script
MaintenanceSolution.sql. – Ola
Hallengren


How to migrate SQL Server credentials from 2005 to 2008 R2 I want to migrate SQL Server credentials used for proxy accounts from SQL Server 2005 to 2008 R2. Is there a way to decrypt credential passwords or a script to copy credential with the same password on new instance. Unfortunately I don't have the passwords for the accounts used for SQL credentials. So looking for a workaround.

I'd suggest you take a look at the Copy-DbaCredential Powershell cmdlet from dbatools.

From their web page on this cmdlet:

  By using password decryption techniques provided by Antti Rantasaari
  (NetSPI, 2014), this script migrates SQL Server Credentials from one
  server to another, while maintaining login names and passwords.

Here is an example of how you'd invoke this cmdlet

Copy-DbaCredential -Source sqlserver2014a -Destination sqlcluster


Using a GIN index on an integer array to model sets of tags In PostgreSQL, what are the pros and cons of using a GIN index on a column of type integer[] to model sets of tags, instead of doing a relation table?

I am trying to design a simple data model with a table called post where each post can have a set of tags, similarly to Instagram or StackExchange.
It seems that I would be able to search for a post that contains a set of tags without having to join two tables, but I won't be able to easily get the list of tag strings for one specific post.

I think you already more or less answered your own question.

While a GIN index can speed up searching inside an array quite significantly, you have to be aware that relational database engines are designed to do joins very efficiently.  Also, when you need the actual tags, you will have to join the tag table regardless of you use the array approach or not.

Furthermore, storing the tags as an array of integer makes enforcing referential integrity (that it is impossible to insert a nonexistent tag ID into the array) quite complicated.  The only way I can imagine this is creating a trigger, which never makes changing the data faster.

The best way to choose among these alternatives is, though, to create a sufficiently big amount of test data and measure the performance yourself.


Does SQL Server have a Date_Format function? All I found is a list of predefined DateFormat that I can choose from, like this

As I remember in MySQL (and PostgeSQL too?) you can define your Date Format:

DATE_FORMAT(now(),'&m_%Y') --for 02_2012 etc.

Does SQL Server have the same thing? I see people have to write a function to do such thing, does it have a built-in one?

EDIT:

I just found the DatePart function. It can take Month as number, but always returns 1 digit, even I use  datePart(MM, getdate())

Not yet.

You need to use CONVERT with a style parameter or hack something together with DATEPART or DATENAME.

SQL Server 2012 will have the FORMAT function though that accepts a .NET Framework format string

Syntax:

FORMAT ( value, format [, culture ] )

Example Usage

SELECT FORMAT(getdate(), 'dd/MM/yyyy', 'en-US' )

You could always use CLR integration and create your own UDF that does the same thing for 2005 or 2008.


Designing an extensible authentication system I need to design an authentication system, where different type of authentication schemes may exist. At the end, I should assign a passphrase (access token) to the user.

I have several authentication schemes right now, and design should be extensible with new authentication schemes. What I've currently is:

Insecure (direct)
One time code over SMS
Time-based code via external authenticators (i.e. Google Authenticator)

To clarify how does those schemes work, I will give some examples:

Insecure authentication is performed by only user credentials. Those credentials are username and password. All users have those two credentials.

One time code over SMS is another authentication scheme, having taken users credentials, a one time code is generated by the server, and sent to the user's phone via SMS. Afterwards, user supplies that one time code to the server in order to complete the authentication. In practice, time-based code is no different than this authentication scheme.

Here are the DDTs of my current design (please note that they're canonical):

CREATE TABLE users(
    id serial PRIMARY KEY, -- surrogate key
    username character varying (25) NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL
);

-- Those tables are middleware artifacts
CREATE TABLE one_time_codes(
    id serial PRIMARY KEY, -- surrogate key
    code integer NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

CREATE TABLE insecure_authentication_handles(
    id serial PRIMARY KEY, -- surrogate key
    passkey character varying (255) NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

-- Final product of successful authentication
CREATE TABLE passphrases(
    id serial PRIMARY KEY, -- surrogate key
    passkey character (5) UNIQUE NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

As you may understand, I used Martin Fowler's "Concrete Table Inheritance" in order to represent the concrete OneTimeCode and InsecureAuthenticationHandle subclasses of AuthenticationScheme abstract class in the database. Here, each passphrase could be matched with either one_time_code or insecure_authentication_handle depending on users preference of authentication. one_time_codes and insecure_authentication_handles are the middleware artifacts of the authentication, hence I created a view to list all of those artifacts.

CREATE VIEW concrete_authentication_middleware_artifacts AS
    SELECT user_id, NULL insecure_authentication_handle_passkey, code one_time_code, inserted_at
    FROM one_time_codes

    UNION

    SELECT user_id, passkey insecure_authentication_handle_passkey, NULL one_time_code, inserted_at
    FROM insecure_authentication_handles;

Hence, if I want to pair each middleware artifact with successful authentication artifact (passphrase), I would execute the query (assume all artifacts have a valid duration of 3 minutes):

SELECT cama.*, p.passkey passphrase
FROM concrete_authentication_middleware_artifacts cama
INNER JOIN passphrases p ON cama.user_id = p.user_id AND (GREATEST(p.inserted_at, cama.inserted_at) - LEAST(p.inserted_at, cama.inserted_at) < '3 minutes'::interval);

Everything works great, however:

I could use foreign keys instead of comparing timestamps in order to join passphrases table and the view, however that would lead to creating n foreign keys in passphrases table, whereas only one may have non-NULL value. I'm also unaware of creating external predicates in order to ensure database integrity on such conditions.
The design does not favor the latter query with huge amount of data. This may not be a problem since it's executed rarely.

What kind of suggestions do you have? I'm not sure I reached to the most elegant design.

First, get the idea of inheritance out of your head and try to think relationally. Keep integrity as a first priority, and ease of use as a second. Saint So-and-so isn't that hot. And, I doubt anyone has heard of the buzz term Concrete table inheritance. PostgreSQL has native inheritance built it and the side effects of it are simply not worth the gain.. Even though PostgreSQL does have a concept of Multiple Inheritance.

You provide three tables...

-- Those tables are middleware artifacts
CREATE TABLE one_time_codes(
    id serial PRIMARY KEY, -- surrogate key
    code integer NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

CREATE TABLE insecure_authentication_handles(
    id serial PRIMARY KEY, -- surrogate key
    passkey character varying (255) NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

-- Final product of successful authentication
CREATE TABLE passphrases(
    id serial PRIMARY KEY, -- surrogate key
    passkey character (5) UNIQUE NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

All of this raises the question of how data relates to other data. In this case, the users table, that we agree must exist.

Do you have 1-to-1 relationship?
Do you have 1-to-many relationship?
Do you have many-to-many relationship?

That should be the principal factor in determining your data schema and not some external theory applied by an organic buzzword generator. That's how databases work.

Aside from how you related to your data, consider the other downsides.

Ease of use. You've got a UNION now and a VIEW other complexities you wouldn't otherwise need.
The alternative is faster. JOINs ain't free they cost index scans and merges -- even the ones on ints. PostgreSQL has a large-row size. The fewer rows you have in memory and have to fetch the better. Same for UNION, without UNION ALL you have to sort and filter distinct. With regular UNION you may not know how many duplicates you're getting back.
You're not using the tools available to you: PostgreSQL provides hstore, (sql-standard) array, jsonb types. You can use these for schema-less design on your user. I suggest it if you're not needing to select on that criteria.

So you could just as easily,

ALTER TABLE users ADD COLUMN (auth jsonb);

And, then put one_time_codes, passkeys, and passphrases in that.

As a side note,

you should consider reviewing pgcrypto for authentication needs.
Don't specify character varying unless you truly need a character limit by specification. Otherwise, use text. It's faster. And, that's the PostgreSQL convention. They're both implemented in the same way, but varchar has a length-check on it.

Testing

200 passcodes, for 2000 users using JSON and PostgreSQL array.

JSON Schema

CREATE TABLE users AS
SELECT t1.userid, md5(t1.userid::text), x AS passcodes
FROM generate_series(1,2000) AS t1(userid)
CROSS JOIN LATERAL (
  SELECT t1.userid, jsonb_agg(floor(random()*1000)::int)
  FROM generate_series(1,200) AS t(x)
  GROUP BY true
) AS t2(userid,x);
CREATE UNIQUE INDEX ON users (userid);
ANALYZE users;

JSON Query

This is 35ms. You should never have to do this.

EXPLAIN ANALYZE SELECT * FROM users
WHERE passcodes @>'5' ;

This is 0.060 ms

EXPLAIN ANALYZE SELECT * FROM users
WHERE passcodes @>'5'
AND userid = 42;

That's likely faster than the index lookup and merge for the other table.

SQL Array Testing

CREATE TABLE users AS
SELECT t1.userid, md5(t1.userid::text), x AS passcodes
FROM generate_series(1,2000) AS t1(userid)
CROSS JOIN LATERAL (
  SELECT t1.userid, array_agg(floor(random()*1000)::int)
  FROM generate_series(1,200) AS t(x)
  GROUP BY true
) AS t2(userid,x);
CREATE UNIQUE INDEX ON users (userid);
ANALYZE users;

SQL Array Query

This is 7ms. You should never have to do this.

EXPLAIN ANALYZE SELECT * FROM users
WHERE passcodes @> ARRAY[5];

This is 0.076 ms

EXPLAIN ANALYZE SELECT * FROM users
WHERE passcodes @> ARRAY[5]
AND userid = 42;

That's likely faster than the index lookup and merge for the other table.


Full text query slow on first run I have SQL Server database which has size of 65GB and 6 million rows. We have essays in tables and the average size of an essay is 450 words. When I run a query for the first time, it takes 3-10 seconds to show me the results. On running same query again, it is in less than 1 second (very fast).

Please let me know how to optimize it so I can get queries in less than 1 sec.

Query example:

SELECT top 1 * FROM tblContent WHERE CONTAINS(pagecontent,'"increasing the likelihood"')

Here is detail of server(VPS):

OS: Windows Server 2012
SQL Server 2012 Enterprise (Trial)
Processor: set to 0
Memory: set to 0
RAM: 4GB
Processor: QUAD 4Ghz

When I run a query for the first time, it takes 3-10 seconds to show me the results. On running same query again, it is in less than 1 second (very fast)

The second time you run the query, it is likely that data and index pages for the relational tables are coming from SQL Server's cache rather than being read in from disk. Likewise, the full-text portion of the processing will also benefit from caching.

As the comments on the question indicate, 4GB does not seem like very much memory for a SQL Server managing a 65GB database. More physical memory will allow SQL Server to cache a greater proportion of your relational tables and full-text indexed data.

The exact amount you need depends on the nature of your workload, and the response times your users expect. I would imagine that expanding the memory even modestly (say to 8 or 16GB) would result in a very noticeable performance improvement, and more predictable response times overall.

You would also want to configure the maximum memory for SQL Server to ensure there is enough for Windows as well. See the following links for more information on memory (and other important details):

How much memory does my SQL Server actually need?

Troubleshooting SQL Server: A Guide for the Accidental DBA (free eBook)


Grant permission to execute stored procedure without granting access to underlying table - Oracle I am new to Oracle (10g), my background is SQL Server. I have a service account that needs permission to execute a stored procedure, but nothing else.

I see from here how to add execute permissions for a single stored procedure:
How do you grant execute permission for a single stored procedure?

Say I have a stored procedure GET_A(), which returns the results of a SELECT from Table A. I don't want to grant this user permission to query Table A directly. Will granting permission to execute GET_A() be sufficient?

I know in SQL Server I can grant a role access to specific Views and Stored Procedures, without granting permission to the underlying Tables. However, my in-house DBAs tell me this is not possible with Oracle.

This is indeed possible with stored procedures running with AUTHID DEFINER. Such SPs run with the authority of whoever created them. Please note that the creator must have explicit grants to the queried table; authorities acquired via roles will have no effect in a stored procedure.


How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)? The below is a valid TSQL statement. But I want to understand how SQL Server processes it.
DELETE A
FROM table1 WITH (NOLOCK)
WHERE
    EXISTS
    (
    SELECT 1
    FROM
        table2 B WITH (NOLOCK)
    WHERE
        B.id = A.id
    )

Because the output of the subquery will be a list of 1s. How does SQL Server know which rows to delete?

How does SQL Server know which rows to delete?
To understand how it's processed, it's probably more helpful to look at the execution plan for the query. Setup scripts are at the end.
First, let's change the query a little bit to something that should throw an error, but doesn't.
DELETE A
FROM table1 AS A
WHERE EXISTS
(
    SELECT 1/0
    FROM table2 B
    WHERE B.id = A.id
);

If you were to just run SELECT 1/0 you'd get a divide by zero error. But there are places where expressions are present in the query for parsing, but aren't actually projected by the optimizer.
The "list of ones" you're referring to doesn't ever really appear. All the work to identify rows to delete is done via the where clause in the exists.
Reading the plan from right to left is the correct method when you need to understand the flow of data. Left to right is the flow of logic.

We have:

A full scan of both tables
A hash join to match rows
A delete from table1

The hash join figures out which rows need to go, and passes qualifying bookmark values on to the delete operator. In this case, bookmarks are used to uniquely identify rows because there's no clustered index available to use keys from.
With a unique clustered index, key values alone can be used to identify rows. With a non-unique clustered index, an additional unique-ifier will accompany any duplicate values, though this won't be visible to you.
Setup scripts
DROP TABLE IF EXISTS table1, table2;

CREATE TABLE dbo.table1 (id int NOT NULL);
CREATE TABLE dbo.table2 (id int NOT NULL);

INSERT
    dbo.table1 WITH(TABLOCK) (id)
SELECT
    x.*
FROM
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY 1/0) AS n
    FROM sys.messages AS m
) AS x;

INSERT
    dbo.table2 WITH(TABLOCK) (id)
SELECT
    t.*
FROM dbo.table1 AS t
WHERE id % 2 = 0;

SELECT TOP (100) t1.* FROM dbo.table1 AS t1;
SELECT TOP (100) t2.* FROM dbo.table2 AS t2;


dbcreator role member not able to access database it just restored I have added a new windows account [PROD\apdf] and given it the dbcreator role.

The user initiates a restore from an app server.
The database restores correctly.  Under database properties it shows the prod\apdf user as the database owner.  However, that user is not granted access to the database. The sysadmins and dbcreators are added to security on the new database, but not the one it was restored by.  When pulling up the dbcreator role list, the [PROD\apdf] user is on that list.

I have made sure that the account is not orphaned and it shows it is a member of the dbcreator role using:

EXEC sp_helpsrvrolemember 'dbcreator';

I have seen an article that indicates this may be by design, but I wanted to see if anyone else had an answer about this.

After the database is restored, a member of the sysadmin or securityadmin server roles will need to alter the authorization (ownership) of the database to the login:

ALTER AUTHORIZATION ON DATABASE::<name of database> TO [PROD\apdf];

I can verify the restore operation does not seem to correctly grant ownership through the below MCVE.

create a login and make it a member of the dbcreator server role:

CREATE LOGIN backup_restore_test
WITH PASSWORD = 'asdfousd^f89aw09e8r90qawer$udfjoia'
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF;

ALTER SERVER ROLE dbcreator ADD MEMBER backup_restore_test;

Take a backup of the model database, to restore later:

BACKUP DATABASE model
TO DISK = 'C:\temp\model.bak'
WITH COPY_ONLY;

Login to the server using the [backup_restore_test] user, and run the following:

RESTORE DATABASE newmodel
FROM DISK = 'C:\temp\model.bak'
WITH MOVE 'modeldev' TO 'C:\temp\modeldev.mdf'
    , MOVE 'modellog' TO 'C:\temp\modellog.ldf'
    , RECOVERY;

This shows the backup_restore_test login is the owner of the database:

SELECT d.name
    , sp.name
FROM sys.databases d
    INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid
WHERE d.name = 'newmodel';

However, this fails:

SELECT *
FROM newmodel.sys.tables

The error:

  Msg 916, Level 14, State 1, Line 14
  The server principal "backup_restore_test" is not able to access the database "newmodel" under the current security context.

Switch back to a sysadmin login, and run this:

ALTER AUTHORIZATION ON DATABASE::[newmodel] TO [backup_restore_test];

Now, this works, when logged in as the [backup_restore_test] login:

SELECT *
FROM newmodel.sys.tables

Unless I'm overlooking something, the backup_restore_test login should have access to the database as soon as it is restored since it owns the database at that point.  I've verified this behavior on SQL Server 2012, and SQL Server vNext


Isn't contradictory that MongoDB may lose updates with journaling in WiredTiger? This is what MongoDB says about journaling:

  To provide durability in the event of a failure, MongoDB uses write
  ahead logging to on-disk journal files.

But with WiredTiger:

  Important

  In between write operations, while the journal records remain in the
  WiredTiger buffers, updates can be lost following a hard shutdown of
  mongod.

If journaling in WiredTiger can't fully guarantee that there will not be lost updates we have to write our application to be aware that some documents may just "disappear", so why enable journaling at all, considering that it will slow down writes?

It seems that if you can't lose updates* you have to choose MMAPv1 with journaling and if you can you may prefer to choose WiredTiger without journaling.

(*) You still may lose updates in a MongoDB cluster due to rollbacks altought you can avoid rollbacks with w: majority write concern or do something with the files in the rollback/ folder.

It doesn't matter if it's a wiredTiger or MMAPv1, they are just storage engines. Whether you lose updates or not depends on write concerns.

Your app communicates with MongoDB server that writes mostly to memory that has a cache of pages which is periodically written and read from the persistent disk. There is also a journal that logs everything.

When your write to the database it writes both to pages and journal simultaneously. Pages will write to the disk depending on memory pressure. By default you don't wait for acknowledgement from the journal because the journal may not write to the disk for a while, which is represented by j=false. And by default w=1 which means it only acknowledges write to the cache pages.

Default: w = 1, j = false - this is fast, but if something happens before the journal has a chance to write to the disk then the data is lost. What's worse is that it will show as if the data has been written/persisted if you rely on w = 1.

w = 1, j = true - in this case you wait for the acknowledgement from the journal that it has written to the disk. This is a lot slower, but ensures the data is persisted.


I am trying to loop through my counter to create a dynamic sql query which should finally look like I am trying to loop through my counter to create a dynamic sql query which should finally look like

I am trying to use this approach to get the final query but does not seems to work

declare @CurrentRow int
set @CurrentRow =0;
declare @RowsToProcess int
declare @FinalHistoricalQuery varchar(5000)
WHILE @CurrentRow<3
BEGIN
   SET @FinalHistoricalQuery =' select 11'+convert(varchar(20),@CurrentRow) + ' union '
   SET @CurrentRow=@CurrentRow+1
END

SET @FinalHistoricalQuery = left(@FinalHistoricalQuery,len(@FinalHistoricalQuery)-6)
exec (@FinalHistoricalQuery)

The final output that i am looking for is 110 111 112 but it comes as 112 Any suggestion would be helpful.

You need to initialize @FinalHistoricalQuery before the loop and you need to use it in on the left side if the assignment in the loop.

declare @CurrentRow int
set @CurrentRow =0;
declare @RowsToProcess int
declare @FinalHistoricalQuery varchar(5000)

-- Set a start value
set @FinalHistoricalQuery = ''

WHILE @CurrentRow<3
BEGIN
                              -- Use variable here
   SET @FinalHistoricalQuery = @FinalHistoricalQuery + ' select 11'+convert(varchar(20),@CurrentRow) + ' union '
   SET @CurrentRow=@CurrentRow+1
END

SET @FinalHistoricalQuery = left(@FinalHistoricalQuery,len(@FinalHistoricalQuery)-6)

exec (@FinalHistoricalQuery)

Result:

-----------
110
111
112

(3 row(s) affected)


Database Design (how to handle subclass & superclass) I'm wondering what is the best approach to this type of problem (I'm a highschooler so don't have practical knowledge on the subject):

Let's say I want to store information about employees at a zoo. Some employees are zookeepers and take care of animals. Other employees are in charge of cleaning up, while others are in charge of delivering goods from one place to another.

Let's say each type of employee shares some attributes, like first_name, last_name, date_of_birth, but other attributes are dependent on the type of employee, like for delivery people, I would want to know if they a commercial drivers' license. For zookeepers, I would want to know what certifications they have on taking care of animals.

Am I better off creating separate entities for each type of employee, like "zookeeper", "driver" , "cleanup_dude", and having appropriate columns for each?

Or am I better off creating a single "employee" entity, and adding a column to indicate the type of employee, and other column flags for things like "has_CDL", "has_AnimalHandlingCert", etc. These would be left null for employees that do not match the criteria.

Also certain types of employees could have relationships with certain tables, which may or may not be shared with other employees. Like zookeepers could be handling different sections of the zoo, which I would want to store, but the same would not be true for a cleanup guy.

Not sure what would be the best approach to this. Any advice?
Thanks

I've just recently answered a similar question here. What you want is a People table containing data common to all people contained in your DB with other subsidiary tables providing the additional data unique to different roles some of those people play in the operation of your service. The linked question and answer should provide the details you need. If not, a couple of my answers to related questions can be found here and here. However, perform a search to get a variety of answers from other contributers. I have found many times that the best answer for a particular situation may be a combination of techniques from several sources.


How to apply patch on postgresql 9.2 Hi all I would like to apply one patch(pg_dump_grouplock.patch) in my PostgreSQL 9.2 database
regarding lock table issue advised on below link

http://www.postgresql-archive.org/pg-dump-and-thousands-of-schemas-td5709766i40.html

I have never applied the patch, can anybody let me know the step how to apply the patch.

Don't. Instead update to PostgreSQL 9.2.23, which includes a better fix.

If you really must patch it: clone PostgreSQL's source code from git, check out 9.2, apply patch, compile.

git clone git://git.postgresql.org/git/postgresql.git
cd postgresql
git checkout REL9_2_STABLE
patch -p1 < ~/Downloads/pg_dump_grouplock.patch
./configure ...

See the PostgreSQL documentation for compile instructions.


Auto logging / tracking records changes at MS SQL how can I set automatic recording all changes that were done at tables records (Updates) and to get some info / logging summery?
thanks

There's a few different options available to do this, and also depend on which version of SQL Server you're running. Here they are in order of least complicated to implement and most standard, in my opinion:

Temporal Tables - These generate a log table for each table you want to track changes on, with who, when, and what the change was.

Change Data Capture - Similar to Temporal Tables, but on a more global level. System tables of the same database are used to log the changes of your tables within that database.

Database DML Triggers - You can create an AFTER TRIGGER that manually logs the changes into a separate table. This is more of a homebrew solution and a little more work than using either of the first two features.

Audit - This globally tracks multiple events, a subset of which would be DML changes such as UPDATES and INSERTS.


is it possible to create a form in the intranet that can be stored in a database? I am trying to create a form for my companies intraNet site. The idea is as follows:

An employee wants to make a purchase, so they will go to the appropriate page in the intraNet
They will fill out the form on the intraNet page
They click the email button
The data in the form is saved in a database, and an email is sent to me stating that there is purchase order request form filled out

I am not exactly sure how to go about this. Part of me wants to create it in a Data Access Page but I am not sure that's correct.

Can someone help me?
Is there anyone who can direct me to someone/something that can help me?

You could look into APEX which is available even on the free Oracle Express Edition.

  Oracle Application Express (APEX) is a Web application development
  tool for Oracle Database. The tool uses built-in features such as user
  interface themes, navigational controls, form handlers, and flexible
  reports to accelerate application development.

  Oracle Application Express installs with the database and consists of
  data in tables and PL/SQL code. When you run an application, your
  browser sends a URL request that is translated into an Oracle
  Application Express PL/SQL call. After the database processes the
  PL/SQL, the results are relayed back to the browser as HTML. This
  cycle happens each time you request or submit a page.

  You can use Oracle Application Express with the embedded PL/SQL
  gateway. The gateway runs in the XML DB HTTP server in the database
  and provides the necessary infrastructure to create dynamic
  applications. As shown in Figure 19-1, the embedded PL/SQL gateway
  simplifies the application architecture by eliminating the middle
  tier.

You will need to evaluate this option with respect to your existing infrastructure and requirements.


Oracle 11gR2 - How To Audit SYS.USER$ Table I have a Oracle 11gR2 database and I want to audit all select operations on SYS.USER$ table for which users select on this table. When I try this command (I'm trying to run this command with SYS user):
audit select on SYS.USER$;

It gives error below:

ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be
altered

How can I audit this table?
Best regards,

In Oracle 11g this cannot be audited. There are ways of doing it in 12c and up. That said, only users with the "select any dictionary" privilege should be able to select from that table, and anyone with that privilege should be trusted.


High PAGELATCH_* and WRITELOG waits. Are they related? We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion rate into a busy clustered primary key defined with an IDENTITY value. I understand that this phenomenon is known as last page insert latch contention.

However my question is when a new record is inserted, does SQL Server take an exclusive PAGELATCH_EX on a buffer page, insert the record to the buffer page, write the record to the transaction log and then release the exclusive PAGELATCH_EX as detailed https://www.microsoft.com/en-ie/download/details.aspx?id=26665 Page 24. Or does it write the record to the transaction log first before taking the PAGELATCH_EX as detailed "Resolving PAGELATCH Contention on Highly Concurrent "INSERT Workloads - Background information SQLCAT's Guide to: Relational Engine

If the record is written to log outside of the latching mechanism then I can rule out slow writes to disk as a cause of high PAGELATCH waits. But if the latch is held until the record is hardened to log then I should probably take WRITELOG into consideration.

Also would having multiple non-clustered indexes cause the PAGELATCH_* latch to be held for longer i.e if a table has a clustered and multiple non clustered indexes are latches added and released to each of the index buffer pages concurrently?

Update 1
After reading confio-sql-server-writelog-wait slide two and general WAL architecture. I am now of the understanding that the "Record a log entry that the row has been modified" step detailed in both white papers is referring to SQL Server logging a change in the transaction log cache, not disk. Once the transaction is complete or buffer full all records are immediately flushed to disk.

However my question is when a new record is inserted, does SQL Server take an exclusive PAGELATCH_EX on a buffer page, insert the record to the buffer page, write the record to the transaction log and then release the exclusive PAGELATCH_EX

You must note that latch only protects physical integrity of page while it is in memory so latch would be taken when page is in memory. Suppose a record is being inserted and for that page needs to be fetched. First, page would be locked and brought into memory then it would be latched and information would be written. The process after this would be

Generate log record
Update page LSN to match that of the log record
Change Data (dirty the page)
Release Latch
Commit Transaction Starting
FlushToLSN of Commit
Release locks
Commit Transaction Complete

For more details and explanation about above steps please read Bob Dorr's I/O presentation blog

Pagelatch* waits are non I/O waits and I have seen most of the time these wait are prominent because of allocation contention. My hunch is that it has to do something with how tempdb is configured. So how is your tempdb configured ?, how much tempdb data files are present ? Make sure they have same autogrowth and same size. When new page is created system pages like GAM,SGAM and PFS pages needs to be updated or are accessed and when SQL Server finds contention in accessing these pages such wait come into picture.


Find if Column Belongs to User Table I want to compute the number of columns with not null constraint in my database. So I thought about querying the table sys.all_columns:
select * from sys.all_columns

The problem is that it provides me with extra information I dont need (columns in system defined tables).

So I have to add some SQL code to determine whether some column belongs to User Defined Table or not. What is the best way to proceed ?

Do an inner join between sys.columns and sys.tables.  The latter table is the catalog view for user tables, so joining on object_id should give you the corresponding columns:

select c.*
from sys.columns c
inner join sys.tables t
on c.object_id = t.object_id;

Taking this a step further on what seems to be your end result, if you want to get all nullable columns in user tables, this quick query should work:

select
    object_name =
        quotename(s.name) +
        '.' +
        quotename(t.name),
    column_name = quotename(c.name),
    c.is_nullable
from sys.columns c
inner join sys.tables t
on c.object_id = t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
where c.is_nullable = 1;


Can't create a server role using GUI in SQL Server 2014 Express I am still new to DBA and learning. I am having an issue when creating a Server Role using GUI. The option to create the server role does not seem to be visible in the Menu

But when I create a role using the following script it is successful

CREATE SERVER ROLE TestRole AUTHORIZATION MyUser

Also after creating a role I can't update it using the GUI options

How do I enable the Role Administration GUI options for SQLServer express?

edit
Microsoft SQL Server Management Studio                      12.0.2000.8

(This was asked recently and I'm going to close this as a duplicate.)

This is a known bug in Management Studio, and has existed since at least SQL Server 2012. Unlike other missing things in Management Studio (like SQL Server Agent), this has nothing to do with the version or edition of Management Studio you're using, but rather the edition of SQL Server that Object Explorer has connected to. Here's confirmation from one of the lead SSMS devs:

You will need to use T-SQL for this functionality until they fix it (which I think after 2016 SP1 they'll need to do a full audit of any instance-specific filtering or restrictions).


What would be the maximum number of records that can be indexed with a three levels B-tree ? B+ tree? I am learning dynamic tree-structure organizations and how to design databases.

Consider a DBMS with the following characteristics :

file pages with size 2048 bytes
pointers of 12 bytes
page header of 56 bytes

A secondary index is defined on a page of 8 bytes. What would be the maximum number of records that can be indexed with a three levels B-tree? And with a three levels B+tree ?

Here are two examples of these trees :

My attempt

B+trees

I have read that

  B+ trees are shallower than a B tree. because only the set of the highest key denoted as k in each leaf node except the last one, is stored in the non-leaf nodes, organized as a B-tree. Relational DBMS Internals, chapter 5: Dynamic Tree-Structure Organizations, p.46

Therefore there is a difference, something we store in the nodes in a B tree is stored in the leaves in a B+ tree. Thus, to my mind it was (m-1)h (m being the order and h being the height) as far as each nodes contains at most (m-1) keys to another node. But this is not linked with the number of bytes.

Yet I found in the book mentioned above the following table :

Therefore would it be 203.7 number of records ?

B trees

For them, as far as some values are stored in the node, I have to do a division by the number of nodes. And I'm stuck there.

There are many implementation options available to developers of BTree and B+Tree algorithms that will affect the answer here. In a simplistic BTree, all nodes are the same size, and when a node overflows it is split into two half-full nodes with no other key redistribution occurring. Since there will on average be a uniform distribution nodes between half-full and full, the average fill factor will be 75%. You can calculate everything else from that.

Real implementations may however redistribute keys into one or two additional adjacent nodes, which increases the average fill factor. In addition, an implementation may detect (or be notified) that a bulk insertion of pre-sorted keys is happening, and will modify the split algorithm to leave behind a trail of full nodes with only the final node being incomplete; the advantages of this behaviour should be obvious.

In a B+Tree, all key values are present in leaf nodes - so a B+Tree will have as many leaf nodes as the equivalent BTree has nodes overall. The B+Tree will also have internal nodes which contain the keys used as splitters, and the same repetition of values occurs up the tree. Actual implementations however might truncate these keys to fit more in (which changes the fan-out radically, especially at the root level), and of course key redistribution can also be done.

Many implementations use enlarged root nodes, and some allow other nodes also to expand into additional pages, to reduce the hassle of splitting and key redistribution, and to handle very large key values.

Finally many implementations cut short the process of merging nodes on deletion, to the point of only deleting nodes that become empty. There is a number of nasty edge cases with B+Trees regarding merging (consider where you delete a small key from a leaf, where that key was used as a splitter; now you need to replace that splitter with the next value that may be large, and causes the internal node to split!), so it can be easier to just drop it, and any performance impact is not often a concern. So the actual fill factor depends not only on the keys, but also on the history.

The upshot is that the question you're trying to answer is only ever asked for academic interest. It's almost never relevant to real implementations.


How to use BCP Utility queryout in a network path with crededential (login required) for SQL Server I'm having a hard time figuring out on how can I export my selected table in a network path that requires login credentials.
'bcp "select ''StudentName'' UNION ALL SELECT StudentName AS StudentName from [School].[dbo].[ClassRoom]" "queryout \\IPAddress\d$\Log\StudentLog_'+@fileTimeStamp+'_01'+'.'+@fileExtension+'" -c -t, -T -S' + @@servername

Running this gives me an error of

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file
NULL

Where should i put in the script the authentication for my network path?

The BCP utility does not have option to provide credentials for network path authentication where output file will reside. One alternative is to map the network path as local drive with "net use" providing User/password to authenticate. Then use local drive in BCP command.
e.g.
net use e: \IPAddress\d$\  /user:<domain\login>  /savecred


MySQL slave only views We are kind of struggling while creating the infrastructure for our BI sector.

I had read on this link that it is possible to create views on a read-only slave. Still, when I run the query I get

  "The MySQL server is running with the --read-only option so it cannot
  execute this statement."

Is there any way to acomplish this?

Or if it's not, is it possible to be able to have 2 MySQL masters one way sync, without loosing our views? (It's kind of a fake slave).

Our problem is that we won't nor want write access to the master server in order to create our views, and just in case both options aren't possible, is any way to achive something similar?

It should be possible to make the slave read/write by disabling read_only. SET GLOBAL read_only=0, and changing it in the my.cnf configuration file.

Using GRANT you can create a user with SELECT, CREATE VIEW at

GRANT SELECT, CREATE VIEW ON *.* TO bob@localhost;

This should leave the user with enough access to do BI work on a slave without modifying data.


What does "collation" mean? I am learning sqlite from a book which has mentioned collation and collating  sequence multiple times. What does it mean exactly in the database world?

Basically, how to sort and compare strings.

Examples:

Comparing:

Accent: à vs ä vs a
Case: A vs a

Sorting:

Swedish: z -> å -> ä -> ö
Danish: z -> æ -> ø -> å

Special character equivalence

German ß = ss

Wikipedia

Some SQL (SQL Server 2008+)

DECLARE @foo TABLE (bar varchar(2))
INSERT @foo VALUES ('z'), ('æ'), ('ø'), ('å'), ('ss'), ('ß'), ('a'), ('ä')

SELECT * FROM @foo ORDER BY bar COLLATE Finnish_Swedish_100_CI_AS
SELECT * FROM @foo ORDER BY bar COLLATE Norwegian_100_CI_AS
SELECT * FROM @foo ORDER BY bar COLLATE Latin1_General_100_CS_AS --german (western europe) and default
SELECT * FROM @foo ORDER BY bar COLLATE German_PhoneBook_100_CS_AS


Identify root cause for why database is stuck in RESTORING state I know there are questions which solve the issue of a database being stuck in RESTORING state and have used those solutions to manually bring the database back online, but my scenario is somewhat different.

I have an automated restore using Powershell scripts which restores a copy of production to a DEV instance. The scripts are unchanged for about a year and occasionally the restore process finishes but the restored database is stuck in RESTORING state (sometimes the script works fine, sometimes it fails like this).

Each time if I manually re-run the process it works or if I manually restore the database from SSMS's user interface or through T-SQL it completes without a problem.

I've found answers which recommended to run CHECKDB on the restored DB but nothing came out as a cause for this problem.

Since the restore scripts restore a FULL backup of the database and uses a "WITH RECOVERY" option I am trying to find out what might be stopping the restore process, although I'm actually restoring it using "WITH RECOVERY".

Any suggestions are really appreciated since I'm stuck at trying to understand why this is happening from time to time.

I'd very much like to solve the root cause of the issue rather than treat the symptoms, which is to manually restore the DB once again.

Update:

Github Gist as @Brent recommended - here.

Occam's razor suggests starting with the obvious:

If your script sometimes leaves a database in restoring state,
then debug the script.

Start by logging what you're doing in a table or a file. Then, when you end up with the database in restoring state, step back through your logs to see what went wrong. (If you want a second set of eyes from the community, try uploading your script as a Github Gist, but keep in mind that the bigger it is, the harder it might be for folks to spot bugs.)

If you don't wanna do that, then try running a Profiler or Extended Events trace to track the restore events, but be warned - it's way harder than it looks. (Read the comments on that post for even more reader ideas that they've tried and failed.)


Massive update to Clob fields oracle 10g I need to remove a string of characters from 2000+ clobs fields in oracle 10g. How would I do this in PL/SQL?

For example, in table t1 there is a clob field called VBase that needs to have the string ".magic()" replaced with " ".

Something like this for each field.

--Setup.
DROP TABLE t1;
CREATE TABLE t1 (ID Number(4), VBase Clob);
INSERT INTO t1 (SELECT level, '*' || '.magic()' || '*' FROM dual
   connect by level <=2001);
COMMIT;
SELECT * FROM t1;

--Replace.
DECLARE
   cBatchSize CONSTANT Number(3) := 100;
   Cursor vQuery Is SELECT ID, VBase FROM t1 FOR UPDATE;
   Type tTable Is Table Of t1%rowtype;
   vData tTable;
   vNew Clob;
   vLocation Number(10);
   vLobLength Number(10);

   Procedure LobReplace(ioLob In Out Clob, iOld In Varchar2, iNew In Varchar2 )
   As
       vPosition number;
   Begin
       vPosition := dbms_lob.instr(ioLob, iOld);
       If (nvl(vPosition,0) > 0) Then
           DBMS_Lob.Copy( ioLob, ioLob, dbms_lob.getlength(ioLob)
              , vPosition+length(iNew), vPosition+length(iOld) );
           DBMS_Lob.Write( ioLob, length(iNew), vPosition, iNew );
           If (Length(iOld) > Length(iNew)) Then
               DBMS_Lob.Trim(ioLob
                  , dbms_lob.getlength(ioLob) - (length(iOld)-length(iNew)));
           End If;
       End If;
   End;

BEGIN
   Open vQuery;
   Loop
      Fetch vQuery Bulk Collect Into vData Limit cBatchSize;
      For i In 1..vData.Count() Loop
         LobReplace(vData(i).VBase,'.magic()',' ');
      End Loop;
      ForAll i In 1..vData.Count
         UPDATE t1 SET vBase = vData(i).VBase WHERE ID = vData(i).Id;
      Exit When vData.Count() < cBatchsize;
   End Loop;
   Close vQuery;

   COMMIT;
END;
/

--Verify.
SELECT * FROM t1;


IO Error: Got minus one from a read call I am very new with Oracle, I have installed 11g 64 bit on my Windows 10. And only started the oracle database using emctl start dbconsole. I have the following code to be connected to my database, which results in the shown exception. I have investigated a lot about this issue on web/stackoverflow/youtube etc, and could not find a straightforward working procedure, would you What is the problem? Please note that the exception happens at the following line:

con = DriverManager.getConnection(url, props);

The code looks like:

    String url = "jdbc:oracle:thin:@localhost:1158:searchengine";

    //properties for creating connection to Oracle database
    Properties props = new Properties();
    props.setProperty("user", "sys");
    props.setProperty("password", "1234");

    //creating connection to Oracle database using JDBC
    try
        {
            //Class.forName("oracle.jdbc.OracleDriver");
            con = DriverManager.getConnection(url, props);
            //con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1158:searchengine", "system", "1234");
            String query = "alter session set nls_calendar='Persian'";
            PreparedStatement pr = con.prepareStatement(query);
            pr.execute();
            query = "select sysdate from dual";
            pr = con.prepareStatement(query);
            ResultSet res = pr.executeQuery();
            while (res.next())
                {
                    System.out.println(res.toString());
                }
        }
    catch (Exception e)
        {
            e.printStackTrace();
            return false;
        }

    java.sql.SQLRecoverableException: IO Error: Got minus one from a read call
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at Controllers.DBController.connect(DBController.java:24)
    at Wrapper.TestMain.start(TestMain.java:21)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:745)
Caused by: oracle.net.ns.NetException: Got minus one from a read call
    at oracle.net.ns.Packet.receive(Packet.java:311)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:300)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)

"emctl start dbconsole" is a command to start Enterprise Manager (EM), which is tool comes bundled with Oracle Database, and used for monitoring and managing the Oracle Database.

I believe localhost:1158/em  is the URL for Enterprise Manager(EM), so is the port.

The EM and DB port won't be same, in your case the DB port should be 1521 and SID should be ORCL.

So, your connection URL should look like "jdbc:oracle:thin:@localhost:1521:orcl".


Getting Records with Min Max Value on Grouped in Single Table in postgresql The table looks like this:

Existing Table:

table_1

Desired Result:

table_1

I have been able to get result like the following using this code:

table_1

But I would like to get minimum and maximum value on 'trans' row and their corresponding value on 'rate' row, "Grouped By date" on Single Table as following.

table_1

I've reduced a bit your sample data by entering only the first 10 rows.

create table tbl (id int, dt date, code text, trans text, rate int);

insert into tbl values
(1, '20181201', 'AAA', '2018120101', 399),
(2, '20181201', 'BBB', '2018120102', 244),
(3, '20181201', 'AAA', '2018120103', 400),
(4, '20181201', 'AAA', '2018120104', 397),
(5, '20181202', 'AAA', '2018120201', 395),
(6, '20181202', 'AAA', '2018120202', 405),
(7, '20181202', 'AAA', '2018120203', 399),
(8, '20181202', 'DDD', '2018120204', 355),
(9, '20181202', 'AAA', '2018120205', 402),
(10, '20181202', 'AAA', '2018120206', 403);

select
    row_number() over (order by t0.code, t0.dt) as id,
    t0.code,
    t0.dt,
    min_trans,
    t1.rate as min_rate,
    max_trans,
    t2.rate as max_rate
from
    (select   code, dt, min(trans) min_trans, max(trans) max_trans
     from     tbl
     group by code, dt) t0
join
    tbl t1
    on t1.code = t0.code
    and t1.trans = t0.min_trans
join
    tbl t2
    on t2.code = t0.code
    and t2.trans = t0.max_trans;

id | code | dt         | min_trans  | min_rate | max_trans  | max_rate
-: | :--- | :--------- | :--------- | -------: | :--------- | -------:
 1 | AAA  | 2018-12-01 | 2018120101 |      399 | 2018120104 |      397
 2 | AAA  | 2018-12-02 | 2018120201 |      395 | 2018120206 |      403
 3 | BBB  | 2018-12-01 | 2018120102 |      244 | 2018120102 |      244
 4 | DDD  | 2018-12-02 | 2018120204 |      355 | 2018120204 |      355

db<>fiddle here

The first sub-query calculates MIN and MAX trans value grouped by code and date.

from
    (select   code, dt, min(trans) min_trans, max(trans) max_trans
     from     tbl
     group by code, dt) t0

Then you can join twice your original table using code and max/min trans value fetched by the first sub-query.

join
    tbl t1
    on t1.code = t0.code
    and t1.trans = t0.min_trans
join
    tbl t2
    on t2.code = t0.code
    and t2.trans = t0.max_trans;

Adding a WHERE clause you can filter the final result by code.


Does Oracle server installation include client components? I installed Oracle personal edition on a Windows Server 2008 virtual machine for testing purposes. My intention is to test Linked server functionality with SQL Server. Does Oracle Server installation include also client components?

Yes, it does, as @Phil said in the comment.


When I do transactional replication does SQL truncate T-Logs on the Publisher? I would like to do Transactional  replication 10 tables on a Prod server to a Secondary server.  The prod server is in Full mode and has t-logs.  Secondary is in Simple.  Can I still capture T-Logs from Production and restore them (with a full backup of course)  if I have a catastrophic failure on Prod?  Do the T-Logs get truncated each time the subscriber pulls the transaction from the Publisher (Prod) and applies it to the Subscriber?

Can I still capture T-Logs from Production and restore them (with a full backup of course) if I have a catastrophic failure on Prod?

Yes. Just make sure that the log chain is not broken by adhoc log backups. You can use sp_restoregene (tsql based) or ps_restoregene (PowerShell based) to automate when disaster happens.

  Do the T-Logs get truncated each time the subscriber pulls the transaction from the Publisher (Prod) and applies it to the Subscriber?

Once the transaction is replicated to subscriber, the log file can be truncated.

You can review the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view to determine why the transaction log space is not reused and why the transaction log cannot be truncated.  See this KB 317375 for more details.

Check my answer for - Replicated Database Log File Maintenance. Also, Kendra Little wrote an excellent blog post on : You Can’t Kill Transactional Replication


Sorting by a field in child table There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract).

contractupdate also has DATE field startdate and TINYINT field day.

I want the list of contract IDs ordered by the value of field day in the latest (having maximal startdate) contract update which refers to this contract.

MySQL.

SELECT B.contractid,B.startdate,B.day
FROM
    (SELECT MAX(startdate) startdate,contractid
    FROM contractupdate GROUP BY contractid) A
    INNER JOIN
    contractupdate B USING (contractid,startdate)
;


Implement Database Security? I want to implement a database security on the database i created so no one can access it from SQL Server Management Studio, and that it will be only accessible through code.

Is that possible? and if it's not is there any other ways to prevent user from accessing database?

Well at least DBAs should be able to connect to such database using SQL Management Studio, or nothing would be possible like backup/restore/checks...

just do not give anybody the sa password and create a user for your application and configure it accordingly and also, do not give anybody the password of this application user.


How to resolve unmodifiable replica set state We are using mongoDB 3.0, and our setup uses six instances with one PRIMARY and the rest of them as SECONDARY.

We were trying to remove three of these using the following command sequence -

rs.remove("host1:port") //same for host2, host3

Now that we wanted to add those back, there is a weird state that the rs.conf() results in. i.e

The replica set before removal was at the _id:1,2 and 3 Now that we have added them back, they are at the _id:7,8, and 9

Why does this happen so, and how do I correct the rs.conf()?

Also if I again try and remove host1, host2 or host3, they are not getting removed.

rs.remove("host1:port")

displays the conf() value back again, so we are unable to remove them now. Is there a way to overcome from such state?

rs.conf()

{
    "_id" : "shard1",
    "version" : 15,
    "members" : [
        ...
        {
            "_id" : 4,
            "host" : "host4:port",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 1,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 5,
            "host" : "host5:port",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 1,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 8,
            "host" : "host1:port",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 1,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        }
    ],
    "settings" : {
        "chainingAllowed" : true,
        "heartbeatTimeoutSecs" : 10,
        "getLastErrorModes" : {

        },
        "getLastErrorDefaults" : {
            "w" : 1,
            "wtimeout" : 0
        }
    }
}

When you add hosts, _id is set always one bigger than what is current max _id. If you really want to "fix" that, you can always do:

var cfg=rs.conf()
cfg.member[3]._id=0
cfg.member[4]._id=1
rs.reconfig(cfg)

Same way, you can change anything or everything in your rs configuration. Remove or add new hosts, change an order of hosts in the list.


Getting multiplication of column values on a joined table based on condition The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables:

 CREATE TABLE YEARLY_RATES
 (
   RATE_YEAR NUMBER(4,0),
   RATE NUMBER(6,4)
 );

 CREATE TABLE PERSON_ACCOUNT
(
   PAID_YEAR NUMBER(4,0),
   PAID_AMOUNT(10,2)
);

I need to select PERSON_ACCOUNT.PAID_YEAR, PERSON_ACCOUNT.PAID_AMOUNT,PERSON_ACCOUNT.PAID_AMOUNT * multiplication of the YEARLY_RATES.RATE WHERE YEARLY_RATES.RATE_YEAR >= PERSON_ACCOUNT.PAID_YEAR. So for instance if I have the following data in PERSON_ACCOUNT

PAID_YEAR|PAID_AMOUNT
---------------------
   2006    2150.69
   2007    3290.40
   2008    3000.35

and YEARLY_RATES tables

RATE_YEAR|RATE
---------------------
   2006    1.067
   2007    1.208
   2008    1.057

then I should be able to have the following output:

PAID_YEAR|PAID_AMOUNT|INDEXED_AMOUNT
----------------------------------------------------
   2006    2150.69      2150.69*1.067*1.208*1.057
   2007    3290.40      3290.40*1.208*1.057
   2008    3000.35      3000.35*1.057

Here's how I solved it:

   SELECT PA.PAID_YEAR,PA.PAID_AMOUNT,EXP(SUM(LN(YR.RATE)))*PA.PAID_AMOUNT
     FROM YEARLY_RATES YR, PERSON_ACCOUNT PA
     WHERE YR.RATE_YEAR>=PA.PAID_YEAR
     GROUP BY PA.PAID_YEAR,PA.PAID_AMOUNT

EXP(SUM(LN(YR.RATE)))  is just a trick to get the multiplication of the column values as there's no ready function available in Oracle.


Restoring SQL Server databases - tips and tricks? Anyone have any tips and tricks when it comes to restoring SQL Server databases?

I recently restored a .bak from one server/instance to another, using SQL Server 2005 on both, and have run into the following issues (so far):

All of the identity insert values were reset to their original values (1 in my case). A pain in the butt until I found the following command to go through all tables, and reseed to the current value + 1:

 exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'

Permissions were gone. The database level accounts came over of course (with their role assignments), but most of the DB permissions for each Role were gone...meaning read,insert,delete,execute etc permissions were missing on each table,view, and stored procedure. I wrote some scripts like the below to rectify, but still a pain in the ass.

 select 'GRANT SELECT ON ' + TABLE_NAME + ' TO rolename' from Information_schema.tables

Instance level permissions obviously aren't present if restoring to a new instance...but that was kind of a pain since it seems you can't recreate an instance level account if the account already exists at the database level. What's the best approach here? In my case I deleted the database account, the created the instance, then granted permissions using scripts like the above.

Generally I'm just curious about some best practices, when it comes to restoring SQL Server databases... and/or tips on somehow avoiding the issues I encountered. I'm just trying to pave the way for the most error free restores I can.

It looks like you did not restore a backup but you just transferred the tables with some kind of bcp tool.

Common issues with backup/restore are, in my experience:
a) Logins missing or mismatched, because they are in the master database
b) Collation problems when the collation of the server on which you restore the backup is not the same as the one from which you made the backup.

I had exactly the problems you had on sqlserver 7 when I had a primary / slave setup based on replication. The day I switched the slave as a master I had to reseed all the identity columns and process all the grants manually. This was not a surprise this was because replication does not replicate system tables and because the identity columns were set as "NOT FOR REPLICATION".


How to transpose rows to column in datatabse I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below

 Col_ID   Col_Group   Col_Price
 1        AA           0
 1        BB           2000
 1        CC           1000
 2        AA           0
 2        RR           3000
 2        DD           4000
 2        EE           0

and I want to make transpose or rows to column like as below

col_ID  group1 Price1 group2 Price2 Group3  Price3  Group4 Price4
 1       AA     0       BB    2000   CC      1000
 2       AA     0       RR    3000    DD     4000      EE     0

I use PIVOT And trued below query

SELECT * FROM tbl1 PIVOT ( SUM(Col_Price) FOR Col_Group IN ('AA','BB','CC','DD','EE','RR'))

I got table But I Want as above mention. please Help me

I can't do hard PIVOTs, it's just one of those things that breaks my brain. You need @bluefeet for that.

Anyway, you can do it the oldskool way with case ... when and max() if the number of groups per col_id is finite (I'm assuming the Col_Group names are unknown, and there's a finite number:

select Col_Id, max(Group1),max(Price1),max(Group2),max(Price2),max(Group3),max(Price3),max(Group4),max(Price4),max(Group5),max(Price5)
from (
  select Col_Id, case when Col_NewGroup=1 then Col_Group else null end as Group1,
                 case when Col_NewGroup=1 then Col_Price else null end  as Price1,
                 case when Col_NewGroup=2 then Col_Group else null end as Group2,
                 case when Col_NewGroup=2 then Col_Price else null end as Price2,
                 case when Col_NewGroup=3 then Col_Group else null end as Group3,
                 case when Col_NewGroup=3 then Col_Price else null end as Price3,
                 case when Col_NewGroup=4 then Col_Group else null end as Group4,
                 case when Col_NewGroup=4 then Col_Price else null end as Price4,
                 case when Col_NewGroup=5 then Col_Group else null end as Group5,
                 case when Col_NewGroup=5 then Col_Price else null end as Price5
  from ( select Col_Id, Col_Price, Col_Group, row_number() over
       (
           partition by Col_Id order by Col_Group
       ) as Col_NewGroup
       from tbl1
     )
)
group by Col_Id;

Note that there's no way of ordering which groups end up where, as there's no ORDER given in your question.


Can I not add VIEW SERVER STATE to a role? My server admin asked me to grant a user SA privileges for the purpose of upgrading our vCenter environment from 5.5 to 6.x and I refused. So I found a VMWare article that outlined all of the required permissions and set the up in a role so that I could assign the vCenter database user to the role for the purpose of upgrading and then remove the user from the role once complete. That part worked correctly with some cosmetic name changes to match our current environment. However the upgrade still failed with the following error:

 Error : The user associated with the DSN has insufficient privileges.
 Resolution: Provide sufficient privileges for the user by granting the user the following permissions:
 * VIEW SERVER STATE
 * VIEW ANY DEFINITION

OK so these are server level permissions. That is fine. So I wanted to add these privileges to the role I created for this "vc_admin".

USE master
GRANT VIEW SERVER STATE to vc_admin
Grant VIEW ANY definition to vc_admin
GO

Which netted me this set of errors.

Msg 15151, Level 16, State 1, Line 3
Cannot find the login 'vc_admin', because it does not exist or you do not eave permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'vc_admin', because it does not exist or you do not have permission.

So I add the role to the master database CREATE ROLE vc_admin successfully. The code above is run again and I get the same errors again.

If I change the code snippet above to target the user instead of the role it completes successfully. I suspect my issue is a simple misconception.

I read that I might have to create a server role for this to work which would explain the error. I tried the following which SSMS thinks is incomplete / syntactically incorrect.

create server role vc_admin

Now according to msdn the authorization is optional and adding that didn't change the error. Expecting ID, Quoted ID or TO

I read now that ...

  Server-scoped roles were introduced on SQL Server 2012, they are not supported in SQL Server 2008R2.

So I that train of thought might be a goose chase for me.

How can I get these privileges assigned to my vc_admin role instead?

Just because you create a role in the master database doesn't make it a server level role. It's still a database role - it just lives in a really important database.

SQL Server 2008R2 simply didn't have user defined server level roles. For that feature, you'll need to upgrade to SQL Server 2012.

You asked for something authoritative, and the best answer there is good ol' Books Online, where they list what's new in SQ Server 2012 security features:

https://technet.microsoft.com/en-us/library/42919d6c-1fd4-466e-ac75-d579248c07df(v=sql.110)


MySQL: fetch multiple global status and system variable in a single query When we execute SHOW global status; or SHOW variables; we get list of 291 and 278 records in the resultset. For performance perspective, only few of them are much important. I have to fetch these variables many times during performance hit.
For example, can we write below multiple statement into one?

show global status like 'Threads_cached';
show global status like 'Threads_connected';
show global status like 'Threads_created';
show global status like 'Threads_running';
show global status like 'Select_scan';

If we fetch these variables in a single statement, it will be really a great help...

select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME like 'Threads_cached' or VARIABLE_NAME like 'Threads_connected' or VARIABLE_NAME like 'Threads_created' or VARIABLE_NAME like 'Threads_running' or VARIABLE_NAME like 'Select_scan';