Where can I get SQL Server Transaction Begin Time from? I am working with temporal tables in SQL Server and the generated time is the transaction begin time in UTC datetime2

Is there a function or table in SQL Server to get that same transaction begin time to use elsewhere that does not require me to write to and select from a system versioned table first?

I have some non-system versioned tables that are part of the same transaction and I would prefer if the datetime2 recorded against them matched instead of using SysUTCDateTime and having it vary.

I tried to pull it from sys.dm_tran_active_transactions but its datetime and local server time and also appears to be different ?

system versioned:   2017-04-11 14:00:59.4690673

active transations: 2017-04-11 15:00:59.467

You can get the id of the current transaction from sys.dm_tran_current_transaction.
Then you can get the transaction_begin_time column from sys.dm_tran_active_transactions.
So, use one of the following T-SQL statements:
SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_current_transaction dtct
    INNER JOIN sys.dm_tran_active_transactions dtat
        ON dtct.transaction_id = dtat.transaction_id;

or
SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_active_transactions dtat
WHERE dtat.transaction_id = CURRENT_TRANSACTION_ID();

CURRENT_TRANSACTION_ID() returns the value of the transaction_id column from the sys.dm_tran_current_transaction.

declare @TRANSACTION_BEGIN_UTC datetime2(7); set @TRANSACTION_BEGIN_UTC = SYSUTCDATETIME()

but MUST be saved in Variable at BEGINNING because SYSUTCDATETIME() changes during Transaction
Feb.1 2021: NOT WORKING! : found Example where SysStartTime is earlier:
SYSUTCDATETIME() = 2021-02-01 06:54:58.2040041
SysStartTime     = 2021-02-01 06:54:58.2030165


INSERT performance difference between temporary tables and table variable I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.

This is the code to insert into the table variable

DECLARE @Data TABLE(...)
INSERT INTO @DATA( ... )
SELECT ..
FROM ...

This is the code to insert into the temp table

CREATE #Data TABLE(...)
INSERT INTO #DATA( ... )
SELECT ..
FROM ...
DROP TABLE #Data

The temporary table doesn't have any keys or indexes, the select part is the same between the 2 queries, and the number of results returned by the select is ~10000 rows. The time needed to execute the select alone is ~10 seconds.

The temp table version takes up to 10 seconds to execute, I had to stop the table variable version after 5 minutes.

I have to use a table variable because the query is part of a table value function, that doesn't allow access to temporary table.

Execution plan for the table variable version

Execution plan for the temp table version

The obvious difference between the two plans is that the fast one is parallel and the slower one serial.

This is one of the limitations of plans that insert into table variables. As mentioned in the comments (and it seems as though it had the desired effect) you could try doing

INSERT INTO @DATA ( ... )
EXEC('SELECT .. FROM ...')

to see if that gets around the limitation.

Table variables are sometimes slower because there are no statistics on table variables, and thus the optimizer always assumes only one record.

However I cannot guarantee that this is the case here, you will have to take a look on the "estimated rows" info in the query plan for the table variable.


Not sure if this is the best solution, but this does the job. The only requirement is that each sensor has a least 2 values.

select *
from sensor_data as t8
inner join (
  select t6.sensor_id, min(t6.date) as date
  from sensor_data t6
  inner join (
     select t4.sensor_id, max(t4.date) as before_last_date
      from sensor_data t4
      left join (
          select t2.sensor_id, t2.val as last_val, t2.date as last_date
          from sensor_data t2
          inner join (
              select sensor_id, max(date) as last_change
              from sensor_data
              where 1
              group by sensor_id
          ) as t1
          ON t2.sensor_id = t1.sensor_id AND t2.date = t1.last_change
      ) as t3
      ON t3.sensor_id = t4.sensor_id
      WHERE t4.val != last_val
      GROUP BY t4.sensor_id
  ) as t5
  ON t5.sensor_id = t6.sensor_id
  WHERE t5.before_last_date < t6.date
  group by t6.sensor_id
) as t7
on t7.sensor_id = t8.sensor_id and t7.date = t8.date

Try this

; WITH cte AS(
SELECT  ROW_NUMBER() OVER (PARTITION BY sensorid ORDER BY date) AS rnm, *
FROM dbo._TestRanking
)

SELECT A.sensorid, A.Date, A.val
FROM (
SELECT c1.*, ROW_NUMBER() OVER (PARTITION BY c1.sensorid ORDER BY c1.rnm DESC) rn
FROM cte c1
LEFT JOIN cte c2 ON c2.rnm = c1.rnm - 1
AND c1.sensorid = c2.sensorid
WHERE  c1.val <> c2.val
) A
WHERE A.rn = 1


How much RAM is required for 5000 concurrent users MySQL Going by this :
When should I think about upgrading our RDS MySQL instance based on memory usage?
for max_connections to be set at 5000 concurrent users,how much should be the RAM capacity be ?

If I add per connection over head for 5000 users, as per what I set the values as shown below, I get :

[ read_buffer_size (0.125) MB + sort_buffer_size (2 MB)  +  join_buffer_size (2 MB)  +  read_rnd_buffer_size (1 MB) +   thread_stack (0.25 MB) +    binlog_cache_size (0.03125 MB) ] * 5000

= ~ 26 GB.

And as per my understanding, innodb_buffer_pool_size we normally give as 75% of RAM - for optimal performance.

So my question is, per connection over head (26 GB) + 75% of RAM = RAM, then RAM capacity should be ~ 104 GB ??

Can any one pls help me out on this.

Your problem is the innodb buffer size - that is a lot more dependant on database patterns than users. If your database is only 20gb there is no sense in a larger buffer.

And the sensible size there can not be determined from user count only.

So, given you need something for the OS and MySql as a server, then 26gb for the connections - and man, I don't do MySql, but in SQL Server world 5000 connections would be crazy and be for like 100.000 users with connection pooling at the same time.

Assuming that is not just crazy bad programming - I would go with 32 to 64gb memory and the best xeon chips I can get my hands on....  RAM is not your problem here, processing 5000 concurrent SQL statements is. As a start - 64gb memory are cheap in such a server. If the database is larger, then 128gb to 256gb memory would be good. But your problem is not memory, if you really have 5000 concurrent users doing things and not just keeping passive users connected, which is bad programming.

well i don't know very much about the memory usage but i have heard somewhere that 1GB RAM is capable of handling 20000 concurrent connections on a 2.5Ghz dual-core processor, i don't remember on what Operating system it is. so according to this 256MB of RAM is sufficient to handle 5000 concurrent users.

PS: I really don't remember the actual stats..


MySQL Sharding tables/servers or other methods to reduce table sizes? I'm currently working with a table exceeding 12 million rows (at about 3 GB when exported with mysqldump) and am curious how huge a table can realistically get without any serious performance impact. The table is growing about 100,000-200,000 rows a day or so.

Should I be starting to think a little about sharding this data across multiple tables or mysql instances now before the data gets much larger? Currently the server this is running on has 1 GB of ram (though it'll soon be moving to a machine with 3/4 GB).

Has anyone got any tips/recommended reading that would push me in the right direction, or is this something I don't need to worry about yet?

Thanks :)

A good RDBMS can grow to accommodate extremely large data. 3Gb databases are very manageable, and very probably, as long as you can get a server with enough RAM, most queries will run pretty fast with little effort.

Even when you exceed RAM, indexes, caching and partitioning let you still perform well. Very often, applications access most a relatively small working set- for instance, 90% queries might be limited to last month's data- while the 10% can be queries over older data. "Last month's" data tends to be somewhat stable- it grows when you have more users, but other than that, it doesn't tend to grow with time. This "working set" often fits in RAM, gets cached and you still get great performance.

But then, you can again get slowness. With proper monitoring and analysis, you can locate the queries which are running slow and take steps to solve them.

This is often simple:

Queries or incorrect code: often we write queries that retrieve information which is unneeded, or which execute several queries when one would suffice and be faster (the typical case is an operation which needs to display n rows of a table and performs n queries when 1 would be enough. Performing aggregates outside the database is also frequent). This is easily fixed by changing your code
Queries which do not run efficiently. EXPLAIN is your friend here. Often, creating indexes that the query can use is enough (roughly, you'll want to index on columns which appear in the WHERE clause). Also, sometimes tweaking the query itself will yield good results

Another approach which gives good results is throwing hardware at the problem:

Buying more RAM
Getting more and better disks (RAID10 setups get faster if you add more disks, SSDs often have significant benefits, etc.)
It is not frequent, but sometimes CPU is your bottleneck- you can get faster processors and more cores/processors

In some other cases, replication and sharding might be a problem. Replication is complicated, but stuff such as Oracle RAC let's you build monster clusters (at a price). Sharding is another option, but it's often one of the most complex to implement- even applications which shard easily require lots of work to be sharded, and some applications can be notoriously hard to shard.

I think the minute your DB hits disk - your performance degrades, so you need to make sure your machine has more RAM than the size of your DB.
Good solutions for you are partioning or sharding (for sharding, check out http://www.scalebase.com - they do transparent sharding, so it's effortless)


postgres removing user from role membership How do I drop a role member? My role name tester is member of role postgres (which I do not want). How do I remove this membership?

 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+------------
 tester    |                                                | {postgres}
 postgres  | Superuser, Create role, Create DB, Replication | {}

With REVOKE e.g.:

REVOKE postgres FROM tester;

Check the existence of a member role in another role.
SELECT r.rolname  as username, r1.rolname as "role", roleid, member
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolname = 'tester' AND r1.rolname = 'postgres';

Solution
REVOKE postgres FROM tester;

-- Or also
REVOKE "postgres" FROM "tester";

Notice that 'postgres' and 'tester' does not work.

WARNING:

Hack in the internals is extremely bad advice

But in case the code helps you to do something else that does not have a native implementation.
DELETE FROM pg_auth_members WHERE
member = (SELECT member
        FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
        ON (m.member = r.oid)
        JOIN pg_roles r1 ON (m.roleid=r1.oid)
        WHERE r.rolname = 'tester' AND r1.rolname = 'postgres') AND
roleid = (SELECT roleid
        FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
        ON (m.member = r.oid)
        JOIN pg_roles r1 ON (m.roleid=r1.oid)
        WHERE r.rolname = 'tester' AND r1.rolname = 'postgres');

GL
Source


How to avoid Recovery Catalog's metadata being purged after 60 days? I'm using Oracle's Recovery Catalog to track my db's backups metadata (RC ver. is 12.01.00.02 )

I need to collect historical backup sizes but then I've realized there's no metadata information about completed backups beyond 60 days. (however, there's for FAILED and incompleted backups)

e.g. I'm trying to collect this data from rc_rman_backup_job_details view. This RC's was used since Q1 2015, but only 60 days of completed backups are shown.

RMAN@SERESCATALOG> SELECT status, MIN(start_time)
      2  FROM rc_rman_backup_job_details
      3  GROUP BY status
      4  ORDER by status;

STATUS      MIN(START_TIME)
----------- --------------------
COMPLETED   03-SEP-2016 01:31:34
FAILED      18-SEP-2015 09:03:50
RUNNING     28-MAY-2015 02:39:36

What controls this retention? There's any way to change it? Any documentation?

Regards.

This is described in the note:

How to Clean up The information in EM Backup Report (Doc ID 430601.1)

Edit recover.bsq, find the procedure cleanupRSR, change the default value 60 as needed:

  DELETE FROM rsr
        WHERE rsr_end < nowTime-60
          AND rsr.dbinc_key IN
              (select dbinc_key from dbinc
               where dbinc.db_key = this_db_key);

When finished:

RMAN> upgrade catalog;

Connect to your database using RMAN and try the following command.
Where 90 is the number of days that you want to keep backup data
in your RMAN catalog.

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;

Also under sqlplus you can set the number of days for the control
files to track the backup information for RMAN.

ALTER SYSTEM SET control_file_record_keep_time=90;


Netezza: What schema contains all the system tables / views? Is there a schema I can look in or a query that I can run to see all the Netezza system or metadata tables?

I know about _v_table, _v_view, _v_relation_column, and _v_groupusers.
I know there are many others and I would like the definitive list.
Documentation on each would be nice too, but I will settle for just a list.

I looked at How list of netezza system tables/views? but I am looking for a list of system tables. The author of that question is looking to pull specific metadata about the tables on his system.

I don't know of a way to access such a list via a query but IBM provide a list of user views and system views in their Knowledge Centre. I assume those pages are public because their website tells me I'm not currently logged in (but it's not the most user-friendly of sites in terms of authentication so I may be wrong).

As CraigBoyd pointed out in a comment, SELECT * FROM _V_SYS_VIEW; will give you the list of system views. SELECT * FROM _V_VIEW; will also list some of the system views plus any user-defined views. I can't tell why some views are included in _V_SYS_VIEW but not in _V_VIEW.

ADMIN schema contains the metadata views.


mongodb service not running after changing path I have changed the dbpath of mongodb.conf.
And after that trying to start mongodb service,when I run

sudo service mongodb start

I get back

mongodb start/running, process 7320

After that I run:

sudo service mongodb status

And getting back

mongodb stop/waiting

When I want to access mongo shell by :

 mongo
MongoDB shell version: 2.0.4
connecting to: test
Sun Oct 28 13:01:36 Error: couldn't connect to server 127.0.0.1 shell/mongo.js:84
exception: connect failed

But when I start mongod as manually by mongod --dbpath=/mnt/data/db it runs fine.
And I can access the mongo shell by mongo command.

I have correct permission for my data directory.

Any clue where I am doing wrong?

It is possible that mongodb.conf is not getting loaded because of wrong location (typically this is /etc/mongodb.conf) or a typo when you launch through service start

Try launching manually

mongod --config /etc/mongodb.conf

to rule out any issues with the conf file.

Check the config file there will be bind-ip and port pass that value while connecting to client

mongo <ip.add.re.ss>:<port>/<dbname>

assume ip is 10.10.10.2 and port is 27017

mongo 10.10.10.2:27017/test


There is no export as SQL option As CMS I use Wordpress. I want to export a whole category (windows 8) so I run the SQL command :

SELECT * FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE (wp_term_taxonomy.term_id = 15
   AND wp_term_taxonomy.taxonomy = 'category'
   AND wp_posts.post_type = 'post'
   AND wp_posts.post_status = 'publish');

I get the tables but then I cannot export as SQL (there is no option) - strange. Is there a way to export only selected tables as SQL?

I get this effect only when applying a query.

A table, view, or other direct structure seems to export into SQL fine.

The SQL option seems is not available to a query result set, very sadly.

You should try with this:

C:/xampp/mysql/bin/mysqldump -u root -p databasename tablename > C:/filetext.sql

(I use XAMPP v3.2.2 and I had the same problem.)


How do I guarantee inventory numbers are unique across all tables? I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords to be inventoried and 'butters' for different butters. Obviously, these two objects have practically nothing in common as far as attributes other than the fact that they both need to be accounted for in the inventory. This is why they each will have their own table.

I need every inventoried object to have a unique, integer ID assigned to it. So, inventory number '5' might be a sword and '6' might be a butter.

I believe what I need is a separate table which just keeps track of inventory numbers, 'inv_nums'. Then I'd have a foreign key constraint referencing that table in each of the 'swords' and 'butters' tables. The relationship would be 1:1 between an object table and the 'inv_nums' table.

How do I guarantee that an inventory number doesn't get used, inadvertently or otherwise, by multiple object tables?

I'm thinking maybe I need to use a trigger on inserts which calls a function or procedure (not sure which) to either create and return the next sequential inventory number or ensure that the one being provided in the insert statement isn't already in use (it doesn't already exist in the 'inv_num' table).

Am I on the right track? I'm sure there must be a design pattern for this somewhere but my googling hasn't been very successful thus far. I could certainly do all this at the application layer but I'd rather have it enforced in the DB itself.

I'm implementing this in MySQL/MariaDB but I think the concept behind this should be the same no matter the (relational) DB.

First insert a row in a table of "objects" that includes an AUTO_INCREMENT PRIMARY KEY.  Get the id by using LAST_INSERT_ID().

Then insert whatever rows you need into other tables.  Use the id from the first step as the unique id.

This works for MySQL and MariaDB.  Other databases have the concept of a SEQUENCE.  Such can be simulated in MySQL/MariaDB, essentially by using the 'first' step I gave you.  So, if you really need to be somewhat db-independent, start with a SEQUENCE emulation.

(My opinion:  There are so many differences between RDBMS vendors that it is folly to try to have one code for all.)

The code below is an (simplified) example of how I met the requirement of having a separate table for each item type (e.g. guns, butters, etc) and that every item, regardless of type, has a unique inventory number assigned to it. Items must also never share the same inv number even if they're in separate tables.

This solution is following an inheritance model which in ER modeling parlance is called generalization/specialization. In OOP you'd call it a class/subclass or parent/child model. I'm specifically using "Class Table Inheritance" for this solution.

I start with an item_types table. The type_id in this table can also be thought of as a table id; it's essentially keeping track of which table an inventory number has been assigned to. The sample data I'm inserting will assign a type_id of 1 to all guns and 2 to all butters.

CREATE TABLE `item_types` (
  `type_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`type_id`)
) ENGINE = InnoDB;

INSERT INTO `item_types` (`name`) VALUES ('guns');
INSERT INTO `item_types` (`name`) VALUES ('butters');

The items table is the parent "class" which all items will be "children" of, to use OO terminology. Note the composite INDEX in this table which will come into play in the child tables.

CREATE TABLE `items` (
  `inv_num` INT NOT NULL AUTO_INCREMENT,
  `type_id` INT NOT NULL,
  PRIMARY KEY (`inv_num`),
  CONSTRAINT `fk_items_item_types`
  FOREIGN KEY (`type_id`)
  REFERENCES `item_types`.`type_id`,
  INDEX `idx_inv_num_type_id` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

The tables created for each item type are made children of the items table by having them share the inv_num primary key with items. This ensures that the inv_num value must be present in items before it can be assigned here. By creating a composite foreign key with inv_num and type_id, we can now control which table is allowed to use an inv_num value from the items table. This ensures that the same inv_num value cannot appear in both the guns table and the butters table. Be aware that InnoDB requires referenced columns in a composite foreign key to be indexed in the parent.

CREATE TABLE `guns` (
  `inv_num` INT NOT NULL,
  `type_id` INT NOT NULL DEFAULT 1,
  `name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`inv_num`),
  CONSTRAINT `fk_guns_items`
  FOREIGN KEY (`inv_num`, `type_id`)
  REFERENCES `items` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

CREATE TABLE `butters` (
  `inv_num` INT NOT NULL,
  `type_id` INT NOT NULL DEFAULT 2,
  `name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`inv_num`),
  CONSTRAINT `fk_butters_items`
  FOREIGN KEY (`inv_num`, `type_id`)
  REFERENCES `items` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

At this point, it would still be possible to enter a combination inv_num + type_id into the wrong table. For example, an inventory number of 3 and type id of 1 (which represents guns) into the butters table. To enforce only a specific type_id being allowed in a specific table, that table must perform a CHECK when the data is being inserted. Unfortunately, MySQL does not support the CHECK operation so you must use a TRIGGER instead.

DELIMITER //
CREATE TRIGGER trg_guns_ins
  BEFORE INSERT ON guns
  FOR EACH ROW
BEGIN
  DECLARE msg VARCHAR(128);
  IF NEW.type_id != 1 THEN
    SET msg = concat('trg_guns_ins error: Trying to insert wrong card_type'
                     'value in guns: ',
                     cast(NEW.type_id as char));
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = msg;
  END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_butters_ins
  BEFORE INSERT ON butters
  FOR EACH ROW
BEGIN
  DECLARE msg VARCHAR(128);
  IF NEW.type_id != 2 THEN
    SET msg = concat('trg_butters_ins error: Trying to insert wrong card_type'
                     'value in butters: ',
                     cast(NEW.type_id as char));
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = msg;
  END IF;
END //
DELIMITER ;


RMAN incremental restore Can you anyone tell me if it's possible to restore an 11g Oracle database using media management software like Net Backup or Commvault incrementally?

I need to perform a restore of a DB into a new target database (overwriting the DB) ... this of course is fine - connect to the RMAN catalog and then duplicate the DB. However I would then, perhaps 5 days later, like to apply all the incremental changes that occurred on the source to this new target. Is it possible to restore again but only apply the logs from the previous 5 days and roll forward? The new target can be left mounted during this time. It's not being used until all logs are applied. The idea is to save time restoring a massive database before switching over to it permanently. Many thanks.

Sure it is possible. I have done this dozens of times.
It is a simple RESTORE, then a RECOVER phase 5 days later. Or you can even run RECOVER periodically to keep the copy up to date.

Just make sure you do not mess up the catalog with the copy database. Usually I just restore the latest controlfile and use without a catalog connection to prevent RMAN registering restored copies of datafiles in the catalog.

Or use DUPLICATE if you want to use Data Guard as suggested.

With RMAN you would need to re-duplicate the entire database. To achieve the effect you're looking for you would need to set up Data Guard replication. See here: https://docs.oracle.com/en/solutions/reduce-database-migration-downtime/learn-migrating-oracle-data-guard1.html#GUID-4371514B-17CA-4FC9-94FF-CF8AA16F1C8A


Getting Ranked Position - SQL - Simple Query I have a very simple database structure and am struggling to write a command to find the ranked position of the user.

------------------
id     |  average
------------------
1      |  79
------------------
2      |  99
------------------
3      |  22

How would I be able to find the ranked position of the user with id 1? #2 would be ranked 1st. I want the rank to be descending with the highest number in 1st place.

If you are in version 8 (or above), you can use the RANK() function for this purpose:

SELECT
    id,
    average,
    RANK() OVER (ORDER BY average DESC) as rk
FROM
    t;

Note I've ordered the result by average DESC, that assigns rank=1 to the maximum average.

id | average | rk
-: | ------: | -:
 2 |      99 |  1
 1 |      79 |  2
 3 |      22 |  3

db<>fiddle here

assuming average is the column name -

add
order by Average Desc on the end of your query

have a look at the answers to this question for further information


MongoDB failover when master is stuck on IOWait? We host a MongoDB 3.4 replica set in AWS with three nodes:  a primary, secondary failover and an arbiter.  Normally, if the primary instance dies, the failover to the secondary is pretty quick (10-30 seconds).

Today we had a network issue where the MongoDB primary instance lost connectivity with the disk containing the database for about 3 minutes and CPU IOWait went to 100%.  During this time, queries to the primary just hung and went into timeout.  Probably because the primary was still up (though unresponsive), the replica set did not failover or even start a vote.

Is there a configuration which would produce a failover also in such cases?  Or is there some ready tools that could force a failover if simple queries to the primary node start taking too long?

A closely related question is discussed extensively in the comments on SERVER-14139, a bug report against filed against mongodb. To summarize, it is not feasible to build a fully general hang detection system inside a server process.

The commentary in discuss a monitoring approach that kills either the process or shuts down the operating system and can be done using a cron job or the watchdog daemon. Because a mongod process cannot win an election before it has read and written some data to its storage engine, it is safe to immediately attempt to restart mongod after you kill it. The restarted process should not accept connections and certainly will not be able to win an election for primary.

A ticket linked to SERVER-14139 covers an implementation in the enterprise (non-free) version of MongoDB of a storage watchdog timer. Organizations that can instead use the watchdog daemon or an external monitoring process should prefer that approach, because it can protect against more kinds of resource failure.

Normally at primary, you give command rs.stepDown(), but I think that this case it will not work because your secondary cannot read primary opLog.

The fastest thing to do in this situation is bringing firewall between those nodes. So, you give a command that port 27017 (or what ever you use) is blocked. Your secondary and arbiter cannot get the heart beat and they vote that primary must be moved.


Does anybody know what this tool is called? Productive work has crawled to a stop while we try and figure out what the red thing on the cover of Microsoft SQL Server 2012 Internals (2013, 0735658560) is. We think it's a surgical or electrical tool of some sort?

Thoughts?

It's a pair of canvas pliers. They're used to stretch canvas over braces, used in painting prep.

Answer source: Matt Velic, data professional and noted art graduate.


What permissions does db_owner not have? (In SQL Server 2008) Can you list the permissions that db_owner cannot do.

Permissions of Fixed Database Roles: db_owner: has CONTROL permission with GRANT option in the database and VIEW ANY DATABASE permission in the server. To understand what that entails, read all of the following:

Permissions
Principals
Securables
Permission Hierarchy

Any members of the db_owner fixed server role can do anything they want within that database (include drop the database).  They can't do anything outside the database without additional rights.


Error log - What does this mean and what can I do about it? I have the following repeated errors in my error.log and I don't know what it means or what I should/can do about it:

  Access denied for user 'root'@'[some IP]' (using password: YES)

There are many different IPs showing in the [some IP] field.

I've been reading some security posts that recommend changing the root username to something else, is this something I should do here?

I am using MySQL 5.5.22

I believe these are attempts to gain access to my database from hackers. I want to know what I can do to prevent the attempts. Most of the IPs are coming from China.

My first answer is: why is your database even accessible from outside through the Internet? That network traffic really ought to be blocked by router Internet gateway router or firewall.

If you really need to allow some connections from the Internet to your database, then limit it to the valid IP address who should be connecting.

At this point that's not really a dba question but a network admin question.

Try the command below. Please review your options for security.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'enter_password_here' WITH GRANT OPTION;

This will basically allow the root account to log in from any machine, which is a security risk.

Specify the IP to restrict the connecting hosts.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'[IP]' IDENTIFIED BY 'enter_password_here' WITH GRANT OPTION;


How to create and send mail in SQL Server 2008? I want to send E-mail from SQL Server 2008. What are the basic steps for that? Can you explain me if someone used this facility.

Any help will appreciated.

this link will helpful as commented by  marc_s (also I have accepted an answer below)
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

In SSMS object Explorer, click Management
Right-click Database Mail
Click Configure Database Mail

4.Select Set up Database Mail by performing the following tasks

5.Enter a profile name and click Add

6.Enter the settings for your email account. I could not set the account for my company's email account - it just didn't work. But I had no trouble with setting the Gmail account I use and these are the parameters that you should use for the Server name and Port number

It is not possible to send attachments from SSMS, you have to use T-SQL for that:

EXEC msdb.dbo.sp_send_dbmail
@profile_name='carol Gmail',
@recipients='john@john.com',
@subject='Test email',
@body='Attachment test',
@file_attachments='D:\123\pic.jpg'

Try the following SP.

CREATE procedure [dbo].[usp_send_cdosysmail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(max) ,
@smtpserver varchar(25),
@bodytype varchar(10)
as
declare @imsg int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)
exec @hr = sp_oacreate 'cdo.message', @imsg out
exec @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @smtpserver

exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
exec @hr = sp_oasetproperty @imsg, 'to', @to
exec @hr = sp_oasetproperty @imsg, 'from', @from
exec @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

exec @hr = sp_oasetproperty @imsg, @bodytype, @body
exec @hr = sp_oamethod @imsg, 'send', null

-- sample error handling.
if @hr <>0
select @hr
begin
exec @hr = sp_oageterrorinfo null, @source out, @description out
if @hr = 0
begin
select @output = ' source: ' + @source
print @output
select @output = ' description: ' + @description
print @output
end
else
begin
print ' sp_oageterrorinfo failed.'
return
end
end
exec @hr = sp_oadestroy @imsg


connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host Hello I am trying to run a website sent to me but after doing so this error appeared

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database "dbXXX", SSL off in C:\xampp\htdocs\xmastool\index.php on line 37

after Googling it it says that i just need to add an entry in the pg_hba.conf file for that particular user.
this is my pg_hba.conf file.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
local dbXXX userXXX md5
host    dbXXX  userXXX  XX.XXX.XXX.XXX           md5
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

but after doing so, the error still persist. I restarted my XAMPP server several times but no changes appears.
Thanks in advance

Add or edit the following line in your postgresql.conf :

listen_addresses = '*'

Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

Restart Postgresql after adding this with service postgresql restart or the equivalent command for your setup.

Just write the following sequence in the configuration file:

const
  pg                = require('pg'),
config          = require('./db-config'),
    dbOption    = {
      host    : ,
      port    : ,
      user    : ,
      password  :,
      database  :
    },

  pool          = new pg.Pool(dbOption)
  pg.defaults.ssl = true;

This is all.
The file db-config contains the specification data of the host, user, password and database. With this small change it works perfectly both locally and in a pass service (Heroku). Fully tested.


SELECT that is null if there is one more register in this date that is not null I'm trying to make a query that I can't produce...

This is my table capturainfo

captureid   int(11)
userid      int(11)
date        date
dateinit    datetime
datefin     datetime

I need to select all rows that have datefin=null and is not the last row of the day ( ie there is one more row with date is the same day but dateinit1<dateinit2.

I just can't do it.

Here are some attempts I've made:

The basic one:

SELECT * FROM capturainfo where date < "2015-02-27"
AND datefin is null

it gets me all datefin is null, but I can't know if there is one more row in the same day.

SELECT * FROM capturainfo a where date < "2015-02-27"
AND datefin is null
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.datefin<= b.datefin) AND a.userid = b.userid )

... Well this last one doesn't work.

here is the data sample

+-----------+--------+------------+---------------------+---------------------+
|     id    | userid |    date    |      dateinit       |     datefin         |
+-----------+--------+------------+---------------------+---------------------+
|    0001   | 2034   | 2015-02-20 | 2015-02-20 11:41:31 | 2015-02-20 20:16:07 |
|    0002   | 1789   | 2015-02-20 | 2015-02-20 12:41:31 | 2015-02-20 13:43:34 |
|    0003   | 1432   | 2015-02-20 | 2015-02-20 12:55:33 |         NULL        |
|    0004   | 1432   | 2015-02-20 | 2015-02-20 13:44:21 | 2015-02-20 13:45:31 |
|    0005   | 1444   | 2015-02-20 | 2015-02-20 14:41:31 |         NULL        |
+-----------+--------+------------+---------------------+---------------------+

In this case, query should return id 0003 because user 1432 have made a posterior register in the same day

Anybody knows how to do it?

You had one closing parentheses too many in the latter query. You also have columns date and datefin and the distinction between the two is not clear. If you want to do a comparison per day, you have to make sure both rows are from the same day in the inner query (a.date = b.date) and there has to exist b.dateinit where a.dateinit is smaller (<) not smaller or equal (<=). You also erroneously compare datefin columns in the subquery when you should compare dateinit columns. It seems you want to get rows from dates older than 2015-02-27:

SELECT * FROM capturainfo a where date < "2015-02-27"
AND datefin is null
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.date = b.date AND a.dateinit < b.dateinit AND a.userid = b.userid )

Try this,

select * from capturainfo where date <= 'your last date' AND datefin='null'

hope it will help.


Why is my database stuck in the Restoring state? I have some unit tests that manipulates data in a database. In order to guarantee that the database state is always the same throughout all the tests, we're trying restore a database snapshot at the beginning of these tests.

The restoration code looks like this:

USE Master
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '{1}' WITH RECOVERY
ALTER DATABASE {0} SET MULTI_USER

After this, the tests connect to the database and do whatever they need to do.

The problem is that, during the tests debug, if eventually I need to hit the stop button to cancel the tests, the database is being left in the Restoring state forever. It's strange because it only happens when I stop the debug session. If I have 20 tests and all of them restores the snapshot prior to the test, I'll get no error during these test executions.

Do you have any suggestions of what might be causing this?

EDIT

Complementing the @usr's response, to recover the database from the inconsistent state at the beginning of the tests, it's necessary to add the REPLACE option to the restore statement.

It will work if it's like this:

USE Master
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '{1}' WITH RECOVERY, REPLACE
ALTER DATABASE {0} SET MULTI_USER

If you abort a RESTORE mid-way the database is in an unusable state. This makes sense: Some pages are old, some are new.

Stopping the debugger kills the client process causing SQL Server to kill the connection and all associated sessions and requests.

To get it working, restart the last restore step that was interrupted. In your case, restore from snapshot again.

There might be another query still running against your original database that is blocking your restore command.

First, verify the status of your database:

-- check status of databases
select name, state_desc
from sys.databases
where name = 'DATABASENAME';
go

Run this to see if anyone is running a query against your database:

select r.session_id, s.host_name, s.program_name, s.login_name,
DB_NAME(r.database_id) as 'Database', r.status,
r.command, r.percent_complete, st.text
from sys.dm_exec_requests r
join sys.dm_exec_sessions s
on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where s.is_user_process = 1
and r.session_id <> @@SPID
order by r.session_id;
go

If so, kill 'em:

-- kill the offending session
kill SESSIONID;
go


MySQL commits taking time We are facing an issue, where at certain times there are number of commits that takes a more than a second. The time interval for this is not fixed. Our application sends thousands of commits but at some instance the commits with exact same timestamp gets slow.

This is extracted from MySQL slow query logs

There isn't anyother query in the slow query log at the same time. Any pointers on how it can be further investigated?

We are using MySQL 5.7.16 on Amazon RDS.

Since your commits are stalling, it's pretty safe to assume you're running with innodb_flush_log_at_trx_commit=1 (see #4 here), so one obvious change you could do is switch to innodb_flush_log_at_trx_commit=0, however that may not be the right choice for you - see the previous link.

In any case, COMMITs don't usualy stall due to other activity on the server (like queries), unless that other activity locks some kind of mutex for far too long.

If I had to guess without any further evidence, I would say that your writes are so intensive, that MySQL is unable to flush fast enough using adaptive checkpointing, so it exceeds 75% of the log capacity and starts sharp checkpointing (which blocks server activity for a short period).

There are few quick ways to make MySQL handle writes better (besides one already mentioned above):

Increase innodb log files (innodb_log_file_size) - you can go up to 4GB or so for your logs (esimating based on zero information given on the amount of writes),
Increase pIOPS capacity for the data drive
Increase innodb_io_capacity (up to ~70-80% of pIOPS capacity)

Otherwise it would be really useful to see disk activity from AWS, amount of data server currently writes (you can calculate it using Log sequence number delta between two consecutive show engine innodb status\G runs)

There are other ways too, but for that, I'd need a lot more details on the actual workload.

https://stackoverflow.com/questions/16035958/amazon-rds-mysql-instance-perform-very-slow
https://stackoverflow.com/questions/25692905/aws-rds-slow-insert
 Please refer the up links, do some basic tunes: enable query cache, etc.


Query runs in serial and parallel mode in two identical databases on the same instance I have two databases in the same SQL Server instance.
One is a test database with a smaller number of records and total size of 500GB.
Other one is production database with a size of 2.2 TB.
Both database are similar with same database objects.
The server is enabled for parallel execution with MAXDOP = 8 and Cost Threshold for Parallelism = 20. There are 40 logical CPUs on SQL Server 2016 Standard edition.

When I run a somewhat complex query that has multiple joins and views, on the production database the query executes fast in parallel mode.

When I run the same query in the test database (on the same instance) the query runs in serial mode. Data in test database is static too.

I do not understand why it is not running in parallel mode.

From my research, since the test database is small compared to the production database, maybe the cost of the query in the test db does not exceed 20 (cost threshold for parallelism). How can I calculate the cost of the query to confirm?

I made the query run in parallel by removing a scalar function and replacing it with an inline function, but the same query runs differently in two identical databases, that is why I am confused. I have gone through the post which explains why SQL Server does not engage parallelism.

Estimated Subtree Cost is different in both. The one running in serial has a low value of 44. The parallel one has a higher value of 312.

Since the parallel one has to deal with more data, its individual node subtree cost is higher and adds up to 312. So maybe cost is the real issue?

My cost threshold for parallelism is 20 and the one running in serial has an estimated subtree cost of 44 (> 20). So shouldn't it run in parallel?

The cost that is determined by the query optimizer is really, a best guess. The guess is based off of CPU/IO resources the optimizer believes that the query will use. If you want to really get some good information (including query costs) install sp_blitzcache - https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit


Restore a SQL Server bacpac file in Linux I'm working to move an Azure database to an open source DB under Linux.  I've received a .bacpac file and, to start with, I thought I'd try to install MS/SQL in Linux and restore the .bacpac file so I could peruse the database.  But a restore command gives me:

$ /opt/mssql/bin/sqlpackage /a:Import /tsn:tcp:localhost /tdn:dbName /tu:SA /tp:thePassword /sf:/home/user/Downloads/dbname.bacpac
An unexpected failure occurred: Data plan execution failed with message One or more errors occurred..

Unhandled Exception: Microsoft.SqlServer.Dac.Data.DataException: Data plan execution failed with message One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.UnauthorizedAccessException: Access to the path 'C:\Users\Client\Temp\tmp4D32.tmp' is denied.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at Microsoft.Data.Tools.Schema.Sql.Dac.Data.TemporaryStorageProvider.CreateTemporaryFile(Int64 maxSize)

Obviously the restore is assuming the existence of Windows paths.  Is there a way to override this that I've missed?  I'm using:

1> select @@version
2> go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64)
    May 10 2017 12:21:23
    Copyright (C) 2017 Microsoft Corporation. All rights reserved.
    Developer Edition (64-bit) on Linux (Linux Mint 18.1)

(1 rows affected)

Any pointers?  My MS/SQL experience dates to a time not too long after SQL server came from Sybase so right away I'm a bit behind.  Thanks for any help.

Edit

I'll not answer my own question as this solution is incomplete but I did figure out how to limp along with this as much as possible.  Part of what I got from the current maintainer was a schema create script.  Using sqlcmd I was able to create the schema.  The .bacpac file is just a zip file and, when you extract it, you have a "Data" directory with a separate directory per table.  There is also a "model.xml" file that is basically the schema - this must be how the sqlpackage import command can create the database tables but I used a .sql file for mine.

For each directory there is one or more BCP files - ah finally something I remember from my Sybase days.  Because I had very few tables I was able to manually use bcp to load each table in succession.

This isn't the best answer as it requires more than just the .bacpac file to work but it did work for me.  If MSFT really wants people to use MS/SQL server on Linux they've got some bugs to fix.

I'd recommend trying the restore process suggested in the Microsoft Docs - https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-sqlpackage

It looks like this may help:

RESTORE DATABASE AdventureWorks
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' TO '/var/opt/mssql/data/AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO '/var/opt/mssql/data/AdventureWorks2014_Log.ldf'
GO

Hope this helps with the path issue you're running into.


SQL*Plus default output format I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query.

Database 1:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))'

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:54:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;

'XXXX TO_CHAR(UNIQUES
----- ---------------
XXXX          300f483

Here the output is on one line, the headers are truncated.

Same thing on an other database:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))'

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:58:15 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;

'XXXX'
--------------------------------
TO_CHAR(UNIQUESEQUENCENUMBER,'XXXXXXXXXXXXXX')
--------------------------------------------------------------------------------
XXXX
        3a1393b

Here the output is split on two lines.

I am not looking for SQL*Plus formatting options like linesize, column format, etc. that just impact the current session.
What I need to understand is what is making the output different even when I don't specify any options.

Is there some default settings on the server side?
Is it depending on the Oracle server version (10.2.0.1 in one case, 10.2.0.5 in the other)?

Note: column UniqueSequenceNumber is of type NUMBER

Note 2: Tried on a Server version 10.2.0.3, same result as on 10.2.0.1 (one liner)

In reply to miracle173 :

C:\Oracle\instantclient_10_2>sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 26 09:32:02 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))
Connected.
SQL> select 'xxxx' from dual;

'XXX
----
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))
Connected.
SQL> select 'xxxx' from dual;

'XXXX'
--------------------------------
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))
Connected.
SQL> select 'xxxx' from dual;

'XXX
----
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))
Connected.
SQL> select 'xxxx' from dual;

'XXXX'
--------------------------------
xxxx

Tom Kyte posted something like this once and the issue was the CURSOR_SHARING parameter.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696883368520

If it is set to FORCE at the system/database level on host2, it could be mapping/forcing the 'XXXX' hard coded value to an arbitrary 32 character variable.

There are at least to files controlling sqlplus behavior - they are "login.sql" in current directory and "glogin.sql" in $ORACLE_HOME/sqlplus/admin/ Check those files on both servers.

Read docs for more info:
http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch2.htm (suitable for 10g AFAIK).


How long can this ALTER TABLE statement take? Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL

Suppose

there are 45 GB data space and 2GB index space;
about 3 million rows in this table;
column YYY is varchar(8000) now and can be updated (is writable).
the table has about 30 other columns.
There are about 3000G data from other database & tables on this machine.

Some other information:

99.99% of rows have NULL in this varchar(8000);
the web application may hit this table 5 times per minute;
the hardware is enterprise level (8 core CPU and 256GB RAM).
there are other tables and database on this machine, about 3000GB data.

Relevant @@VERSION details:

  Microsoft SQL Server 2014 - 12.0.4422.0 (X64) Enterprise Edition (64-bit)

Nobody can tell you with any degree of certainty or accuracy how long this operation will take on your system. However I suggest the change will likely have less impact than you think. I set up a quick test to compare changing a table from int -> bigint and from varchar(8000) -> varchar(max). First, two simple tables:

CREATE TABLE dbo.t0(a int primary key, b int);
GO
CREATE TABLE dbo.t1(a int primary key, b varchar(8000));
GO

Now, insert at least one non-NULL value, and then over 5 million rows (on my system; YMMV):

INSERT dbo.t0 VALUES(0,1);
INSERT dbo.t0 SELECT rn, NULL FROM
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

INSERT dbo.t1 VALUES(0,'what');
INSERT dbo.t1 SELECT rn, NULL FROM
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

Then I tested the ALTER change, with stats I/O enabled:

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t0 ALTER COLUMN b bigint NULL;
GO -- 9 seconds, 122,506 reads
SET STATISTICS IO OFF;

This finished in 9 seconds and required 122,506 reads.

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL;
GO -- 5 seconds, 8,562 logical reads
SET STATISTICS IO OFF;

This finished in 5 seconds and only required 8,562 reads.

So while neither operation happened online, and while your actual results may differ based on hardware and your table structure, this was on a lowly Windows VM on a MacBook, so I would expect your time to be better than this.

I also tested with a lot more data populated in the nullable column. Dropped the tables, re-created and re-populated with the above scripts, then ran this to put something in 100,000 rows:

;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = a;
GO
;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = REPLICATE(RTRIM(a), 1000);
GO

This took way longer than the subsequent ALTERs (over 3 minutes). The first ALTER still took 9 seconds, but the second one took 17, simply because the number of reads shot up (to 189,854). This is similar to what would happen if you had, say, 30 other columns in this table. I still think 17 seconds is pretty good for what is actually happening here, and again, this is far from an enterprise-level environment.

As Paul points out, starting with SQL Server 2016, you can get this to happen online, but there are restrictions and limitations. For more information see the docs.

ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL WITH (ONLINE = ON);

This option is not available in SQL Server 2014, though.

One way you could get this to happen more online is to simply move that column to a separate, related table. There are some intricacies here with how you cut over, and it might impact a bunch of code, but you can solve most issues with a view (at least temporarily).

About the time you need to type it, if the table isn't locked.

It's DDL, not DML, if you doubt my words.


Script out Oracle DDL in an automated fashion Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention.

I know of DBMS_METADATA.get_ddl(), but have found that the export isn't perfect.  I ran into issues where the exported DBMS_METADATA DDL wasn't usable without first fixing up problems like breaks in the middle of a keyword, and worse. However, if anyone knows a way of exporting DDL through DMBS_METADATA that can run without manual fixes, that'd be a great solution too.

Basically, I am looking for an automatic/scriptable way to export DDL identical to what is exported through the manual way.

How can I do that?

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size.  By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text.

It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL.

The script you need is:

-- Run this script in SQL*Plus.

-- don't print headers or other crap
set heading off;
set echo off;
set pagesize 0;

-- don't truncate the line output
-- trim the extra space from linesize when spooling
set long 99999;
set linesize 32767;
set trimspool on;

-- don't truncate this specific column's output
col object_ddl format A32000;

spool sys_ddl.sql;

SELECT dbms_metadata.get_ddl(object_type, object_name, owner) || ';' AS object_ddl
FROM DBA_OBJECTS
WHERE
      OWNER = 'SYS'
  AND OBJECT_TYPE IN (
      'TABLE'
    , 'INDEX'
    , 'SEQUENCE'
    , 'VIEW'
  )
ORDER BY
    OWNER
  , OBJECT_TYPE
  , OBJECT_NAME
;

spool off;

set heading off feedback off trimspool on escape off
set long 1000 linesize 1000 pagesize 0

col SEQDDL format A300

spool tts_create_seq.sql

prompt /* ========================= */

prompt /* Drop and create sequences */

prompt /* ========================= */

select regexp_replace(
 dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner),
 '^.*(CREATE SEQUENCE.*CYCLE).*$')||';' SEQDDL
 from dba_sequences
 where sequence_owner not in
 (select name
 from system.logstdby$skip_support
 where action=0);

spool off


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

Can anyone help me to validate this in SQL Server?

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

It should accept only this format: (232.343244)

Below code is accepting any numbers:

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

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

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

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

And then you've validate with a simple numeric BETWEEN

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

And then you could validate with something like

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

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

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

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

Take the provided value.  Cast it to FLOAT and to DECIMAL(9,6).  Take ABS() of the difference, and compare it to some epsilon acceptable difference, perhaps 0.0000005.  If it's less than that, the input can be represented as DECIMAL(9,6), which is probably the datatype you should use in your table.

abs( cast(@input as float) - cast(@input as decimal(9,6) ) < 0.0000005

The above rule can be used as a CHECK constraint in your table, too.

HTH.


How can we improve this PIVOT operation? ( SQL Server 2008 R2 ) We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query.

select top 100 *
from (
    select codCliente
        ,Resposta
        ,NrQuestao --,COUNT(1) as Qtde
        ,(
            select COUNT(1) as qtde
            from tblRespostaINT T2
            where T2.codCliente = T0.codCliente
                and T2.codNrQuestao between 9
                    and 29
            ) as QResp
    from tblRespostaINT T0
    inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
    where T0.CodQuestionario = 1
        and T1.codNrQuestao between 9
            and 29
                --and codCliente = 5156
                --Group by codCliente, Resposta,NrQuestao
    ) as tblx
Pivot(Sum(resposta) for NrQuestao in (
            [I_1_P14_1]
            ,[I_2_P14_1]
            ,[I_3_P14_1]
            ,[I_4_P14_1]
            ,[I_5_P14_1]
            ,[I_6_P14_1]
            ,[I_7_P14_1]
            ,[I_9_P14_1]
            ,[I_10_P14_1]
            ,[I_11_P14_1]
            ,[I_12_P14_1]
            ,[I_13_P14_1]
            ,[I_14_P14_1]
            ,[I_15_P14_1]
            ,[I_16_P14_1]
            ,[I_17_P14_1]
            ,[I_18_P14_1]
            ,[I_19_P14_1]
            ,[I_20_P14_1]
            ,[I_21_P14_1]
            ,[I_22_P14_1]
            )) tbl

I've added some indexes but none of them helped. This is the execution plan:

if I add loop in the inner join, we have low CPU usage on HASH MATCH, but a high percentage with the index seek:

It takes 30sec to run the insider query:

select codCliente
    ,Resposta
    ,NrQuestao --,COUNT(1) as Qtde
    ,(
        select COUNT(1) as qtde
        from tblRespostaINT T2
        where T2.codCliente = T0.codCliente
            and T2.codNrQuestao between 9
                and 29
        ) as QResp
from tblRespostaINT T0
inner join tblQuestao T1 on T0.codNrQuestao = T1.codNrQuestao
where T0.CodQuestionario = 1
    and T1.codNrQuestao between 9
        and 29
            --and codCliente = 5156
            --Group by codCliente, Resposta,NrQuestao

What can we do? is there some index tip I should know for problems like these?Should we use only joins?

Query Plan:

XML plan: http://pastebin.com/raw/anLifzBC

I would stay away from LOOP joins, or specifying any specific joins. The SQL Server query optimizer is very good at picking the best join method to use, and it will pick LOOP on its own if that is the right one. Also, if there is a missing non-clustered index that could improve the performance of the query, SQL Profiler will find it and suggest it to you.

One thing that I noticed missing from your query is the use of common table expressions. I haven't had a pivot yet where I haven't needed to use common table expressions to get the data ready to pivot. I would rewrite your query using common table expressions (particularly the correlated subquery) and see if that improves performance. If it doesn't another thing you could consider doing is changing the order of columns in the clustered index on the tblRespostaINT to better support that query.

Finally I believe that what you are doing in the inner select can be accomplished with a windowed function, and if it can that should be faster.


How to profile the PostgreSQL query planner diagnose what is causing it to take up large amounts of memory and time I'm fighting with a specific "generated" query (the SQL is generated from a proprietary system, and I won't be able to share it or the execution plan here), and running into simply massive memory usage and long query times. When running locally on my machine, i'm seeing 6.5gb of memory used during the query, and the query takes around 8 seconds to fully run. However the weird part is that almost all of the time is spent in query planning, and not much in the actual execution:

Planning time: 7453.148 ms
Execution time: 159.609 ms

Interestingly enough, PREPARE-ing the query and then EXECUTE-ing it later causes the memory usage to spike during planning, but not during execution.

I have never seen a query take that long to plan, and none of our other large queries get anywhere near that.

My question is how can I diagnose or debug what the query planner is doing here? What tools does PostgreSQL provide to allow me to profile and diagnose why this query in particular is taking so much time and memory in the query planning stage, and what we can do to reduce the memory usage from the query planner.

Things i've tried:

Disabling GEQO entirely - had no measurable effect on the memory used, but did marginally increase the planning time to ~9 seconds up from ~7.5 seconds.
Tweaking GEQO threshold - had no measurable effect whether set to 2 or 99999 or anything in between.
Tweaking from_collapse_limit and join_collapse_limit - these had a small impact on the query, taking the planning down from ~7 seconds to ~5 seconds but not changing the memory usage at all (and the memory usage is really my main concern here)

I want to dig into this more, but without knowing how to profile what the query planner is doing, I'm basically shooting in the dark...

Edit: this is in PostgreSQL 9.6. We should be able to upgrade to 10 fairly easily, but 11 and up will need more research into if it's feasible.

The first stop to investigating this be would the system tool "perf".  You can get the pid of the running backend (by using "top", for example--make sure you get the pid of the backend, not the client program) then do sudo perf top -p <pid>.  You might get more useful output by first installing the debug symbols. How you do that is going to depend on your OS and how you installed it.

But once you identify the bottleneck, I wouldn't hold out much hope that anyone will do anything about it, given the far-out nature of your query.  If you are willing to dig into the source code to propose our own solution, then I would suggest you start by compiling your own server with version 13dev.  You aren't going to get very far in your investigations using only packaged versions, and especially not older packaged versions.  (Also, the problem might already be fixed in 12 or 13dev).


MySQL5.7 (Windows) how to install from zip file without the Data Directory? This has probably been asked before, but I am struggling to find the answer.

Historically I have always installed MySQL (on Windows) from a template based on the mysql-xxx-win64.zip file.
I would basically

1) download the zip file,
2) copy the contents to the new location,
3) add in my template ini file,
5) add in my template mysql directory (with default users)
4) install a Windows Service,
5) start MySQL

This has worked fine for years (5.5 and earlier). But now I am starting to test 5.7, and I find that the zip file no longer contains a data directory?
If I try and create one by copying over from an existing database, I get an error.

Looking at the documentation I see that I am now supposed to initialize the data directory. But when I try this I get an error:

c:\MySQL\bin\mysqld.exe --initialize --user=mysql
mysqld: Can't change dir to 'C:\MySQL\DATA\' (Errcode: 2 - No such file or directory)

If I create the Directory it seems to work, but there is still no MySQL directory, so when I run mysql_upgrade or try and (start service then) login, it fails.

2016-05-16T09:03:16.222540Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist.

I found this post which says the password shouold be in the error log, but it isn't.

So, how am I supposed to install MySQL if they don't give us the data directory (and therefore mysql user etc. tables) anymore?

(and before someone suggests using the msi file - that's not an option as they are blocked on our network, and require me to jump through numerous hoops to even download it, let alone run it).

If I do: Copy MySQL Directory >> initialize >> install service >> start service, it seems to be ok, until I look in the error log and see that virtually every table has the wrong structure. But I can't run mysql_upgrade as I have no password, and mysql@localhost is access denied.

UPDATE: 2016-05-18

So I got hold of the MSI file, and (tried) to install using that. Eventually, I managed to get a copy of MySQL 5.7 installed and running. So now I need to install a second copy. The MSI installer fails at this point as I 'already have a copy installed', with no option that I can see to install another copy.

So I copied the MySQL Directory to a new folder, removed the DATA directory, updated the ini file, and ran c:\Mysql\...\mysqld.exe --initialize.

That re-created the DATA directory for me.
I then set it up as a Service c:\Mysql\....\mysqld.exe --install MySQL57-2 --defaults-file=c:\MySQL\...\my.ini

I got the temporary password from the error log, and tried to log in, only to be told ERROR 1130 (HY000): Host '::1' is not allowed to connect to this MySQL server.

So I stopped the service, started Mysqld using --skip-grant-tables, logged in, added a new user, logged out, restarted the service. But still can't log in.

If it's going to be this much hassle every time I need to install a new Database, I think I'll just stick to 5.5.

INSTALL MYSQL 5.7.25 ZIP ON WINDOWS 10
Install MySQL server 5.7.25 on Windows 10 using the zip/portable version. Using the zip version allows you to run multiple versions on the same development machine.
https://cloudwrk.com/mysql-server-5-7-x-zip-install/


bash: sqlplus: command not found I have installed oracle correctly and also set ORACLE_HOME correctly on server,still sqlplus command is not working in my PC while it is working in others PC. I think I missed out some important step can you tell me what should I do ?

 $ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1

This following is error message:

$ sqlplus
bash: sqlplus: command not found

Export the PATH variable. You have missed that.

PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ export ORACLE_HOME=/u1/db/db1800/product/18.0.0/dbhome_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=PDB
$ sqlplus sys/sys@PDB as sysdba

This should work


Reasonable way to predict query performance over time I have a SQL Server 2012 database that I am using to save data from processed files. We read data from a folder, process it with python and save the results into the database.

One of the first things we do in our ETL process is to check if the file has already been processed. We simply do a:

SELECT id FROM table1 WHERE basename = <basename>

If there is a result we skip the file, if there is no result we process the file. Right now this query takes ~250ms with ~5m records. We have a nonclustered index on the basename column already.

We will see about 100-200k records added per month. We get the files in batches. So we might see 2k files, and then 2 hours later another 2k files. Some days we will get 10k files, other days we might only get 4k files.

Keeping all other variables the same is there a rule of thumb for projecting when we might run into performance (queries taking longer than 1s) issues with this query other than inserting 15-20 million records into the table and seeing what happens?

Table DDL:

CREATE TABLE [dbo].[raw_records](
[id] [int] IDENTITY(1,1) NOT NULL,
[basename] [varchar](512) NULL,
[filename] [varchar](1024) NULL,
[file_size] [int] NULL,
[machine] [varchar](10) NULL,
[insert_timestamp] [datetime] NULL,
[raw_xml] [xml] NULL,
[process_status] [varchar](2048) NULL,

PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Index:

CREATE NONCLUSTERED INDEX [basename_index] ON [dbo].[raw_records]
(
    [basename] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This table was created long before I started working on it, so I'm assuming someone just made filename 1024 maximum length to hold "enough". Certainly changeable.

The files that are produced have a timestamp and unique information within the filename itself (e.g. system1_metadata_timestamp.xml) one "system" couldn't produce (or should never) produce a file with the same timestamp.

select max(len(basename)), max(len(filename)) from dbo.raw_records;

returns: basename - 143, filename - 168. Probably a good thing to change down to a max of 260.

process_status probably doesn't need to be that long either, but I feel that one is reasonable to guess at, because the column is there to hold error messages from the processing phase. I ran a query on it and had a max of 600 characters. We don't typically query on that column though. It is just more informational for debugging.

I'm going through the application cleaning up stuff such as that. In certain places I can't get away from it but in others can't do much about it unfortunately (e.g. need to actually retrieve the XML column to extract data from it). This question simply stemmed from seeing the performance of the query in question and not wanting it to get away from me. It is the first thing that gets done for every file so if that doesn't work, nothing else will either.

With a good index present, the time taken to locate a matching row should scale roughly logarithmically, as long as you have room for the index in memory.
I'd make the index UNIQUE since the basename must be unique otherwise your workflow is invalid, and it makes the index more efficient.
CREATE UNIQUE INDEX IX_raw_records_basename
ON dbo.raw_records (basename);

Check the execution plan for the query to ensure the index is being used.
Make sure you have enough room in memory for the index, and assuming concurrency won't be a massive problem, you should be good for a very large number of rows.
I'd reconsider the length of the basename and filename columns since the query optimizer will use the length when calculating how much memory it will need to allocate to run the query.  If, for instance, the basename column will never hold more than 20 characters, but you have it defined as 512 characters, the memory grant for SELECT basename FROM dbo.raw_records; would be 25.6 times larger than actually required.  Column lengths are actually much more important than most people realize.
You could also change the query to be SELECT 1 FROM table1 WHERE basename = <basename> that way you wouldn't even need the id since all you're trying to do is verify its existence. Only do what you really need.  It looks like the index you show in your question would work fine for that.
In addition, if space is an issue you may want to consider data compression of the indexes and table.  This should allow the index to fit in a smaller memory footprint.  Evaluate DATA_COMPRESSION = ROW versus DATA_COMPRESSION = PAGE to see which is the best compression method for your requirements.

How do you get  param that your are querying with. It will be more effective if you can do your queries based on int columns not varchar.
Also if you are interested only in the existence such a row in your table  you could change your query to

SELECT 1 FROM table1 WHERE id = <id>

In this case you don`t need to include more columns in the index, so it will use less memory and will perform better.


How do I create a bounded range type in the SQL Server? I want to create a bible verse table that has one field representing a range of bible verses.  Currently I represent this range as pair of values (VerseBegId and VerseEndId).  I want something like the Sql Server spatial data type

Instead of

CREATE TABLE BibleVerse AS TABLE
(
    VerseBegId int NOT NULL,
    VerseEndId int NOT NULL
)

I want

CREATE TABLE BibleVerse AS TABLE
(
  VerseRange ??? NOT NULL
)

Besides SQLCLR, you could also store both values in either XML or JSON (if you are using SQL Server 2016 or newer) and retrieve them as discrete values. Similarly you could simply convert them to VARCHAR and place them, separated by a comma, in a single column (I don't recommend this at all, but it is functionally similar to the other approaches I just mentioned). The SQLCLR approach makes it easy to get the discrete values back out, and in their native datatypes, but you could just as easily write a few helper UDFs to do the same with those values shoved into XML or JSON.

In all of these cases, while you could accomplish the goal of storing two values in a single column, I don't see what you gain by doing this given that each approach has some amount of overhead yet the usage doesn't seem to justify it. The main reason for creating your own type isn't to merely associated related data points, but to have the ability to act upon them. The internal CLR types not only store the discrete data points, but also offer quite a few methods to do interesting things with those data points. I am not sure what you can do operationally with just beginning and ending numbers that don't have commonly used algorithms for making use of them. It seems that you will be doing more work to get the values back out (and most likely decrease performance while doing so) than you will be gaining by putting them in such a structure.

Unless you can clearly see a guaranteed benefit from doing this, I suggest you keep them as discrete data points. And if my memory serves me correctly, you should be able to get away with using TINYINT, assuming no verse # goes above 255 (and they certainly wouldn't be negative or NULL). Looking here ( New Testament Statistics ) and here ( Old Testament Statistics - New American Bible ) it seems that the highest verse number is 80 for the New Testament (Luke chapter 1), and 176 for the Hebrew Bible — "Old Testament" in Christian terminology — (Pslam 119).

Consider checking out PostgreSQL where this is actually pretty simple because it has range types,

CREATE TABLE BibleVerse (
  VerseRange int4range NOT NULL
);

CREATE INDEX ON BibleVerse USING GIST (verserange);

INSERT INTO BibleVerse VALUES (int4range(10,20)), (int4range(20,30));

Finding intersections is also very easy,

SELECT * FROM BibleVerse WHERE verserange && int4range(5,15);
 verserange
------------
 [10,20)


Read CSV and perform SQL in SQL Server Management Studio Is it possible in SQL Server Management Studio to read a CSV and perform an SQL update per line?

Example

data.csv

"column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"

I want to run a SQL update based on the data in each line such as

UPDATE [data].[orders] set order_number = $column1, something_else = $column2 where order = $column3

Is this possible to do in SQL Server Management Studio?

You may use BULK INSERT for that:

CREATE TABLE #orders (
  Column1 int,
  Column2 nvarchar(max),
  Column3 datetimeoffset
)

BULK INSERT #orders
FROM 'X:\orders.csv'
WITH
(
  FIRSTROW = 1,
  DATAFILETYPE='widechar', -- UTF-16
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS -- Treat empty fields as NULLs.
)

If you exported the CSV from SQL Management Studio then some preparations are needed:

The file should be converted to UTF-16.
All NULLs should be replaced with empty strings.

Here is a Powershell script for that:

Get-Content .\orders_raw.csv -Encoding UTF8 | % { $_ -replace "NULL","" } | Out-File ".\orders.csv"

You can refer to below link for how to read csv file in SQL Server:

http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2015/02/09/t-sql-read-csv-files-using-openrowset/

Based on that you will be able to work with your csv data as a table and then you can create the update query as required.


How many bytes does character takes if I created database with utf8 in mysql Suppose if create a database with utf8 character set and created table and inserted data.
Here my doubt is if the character is 1 byte then  it will occupy only 1 byte or it will occupy 3 bytes because of utf8 character set

Depends on a character. Some need one byte, some - four. See http://www.fileformat.info/info/charset/UTF-8/list.htm .
MySQL has two UFT8 character sets:

mysql> SHOW CHARACTER SET like 'UTF8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.01 sec)

If a character is a one byte, it will use one byte in memory and on a disk. If four - then four. Plus the string will use one or two bytes to encode length of the string.
Note, that utf8 character set cannot encode all UTF8 characters.

From MySql documentation:

  Requires a maximum of four bytes per multibyte character.

Or, if your database character encoding is set as utf8mb3:

  Requires a maximum of three bytes per multibyte character.

So, yes, an utf8 character will occupy the same size in your database as it would have. No more, no less.


Index or View of all UUIDs in Postgres DB? I have a Postgres database with about 35 tables of varying sizes. Every table has an auto incrementing integer primary key, as well as a column of native Postgres uuid data type recording Version 4 UUID values.

The application code logs UUIDs, but...alas...doesn't always log the object type or table.

While we're working on cleaning that up, we sometimes just want to figure out what row a UUID refers to.

Is there a reasonable strategy to create a view, index, or even full on table to efficiently find a row given a UUID, if every row in the database has a UUID? Or am I chasing an antipattern and should solve it at the application level?

Disclaimer: I think the whole idea doesn't really make sense. But anyway here is one way to get a uniform "view" on all UUIDs in the database.

You can create a function that goes through all columns that are defined as UUID and then returns the values for those columns. You can optionally pass a UUID value that should be searched for:

Something like:

create or replace function find_uuids(p_id uuid default null)
  returns table(schema_name text, table_name text, column_name text, uid uuid)
as
$$
declare
  l_row record;
  l_sql text;
begin
  for l_row in select n.nspname, tb.relname, col.attname
               from pg_attribute col
                 join pg_type ty on ty.oid = col.atttypid
                 join pg_class tb on tb.oid = col.attrelid
                 join pg_namespace n on n.oid = tb.relnamespace
               where col.attnum > 0
                 and not col.attisdropped
                 and ty.typname = 'uuid'
                 and n.nspname not in ('pg_catalog', 'information_schema')
                 and n.nspname not like 'temp%'
  loop
    l_sql := format('select %L as schema_name,
                            %L as table_name,
                            %L as column_name,
                            %I as uid
                     from %I.%I', l_row.nspname, l_row.relname, l_row.attname, l_row.attname, l_row.nspname, l_row.relname);
    if p_id is not null then
       l_sql := l_sql || format(' where %I = %L', l_row.attname, p_id);
    end if;
    return query execute l_sql;
  end loop;
end;
$$
language plpgsql;

Then you can use:

select *
from find_uuids('19d2efcc-45c1-4371-addc-45028a054b85');

to find the tables where that UUID value appears, or

select *
from find_uuids();

to see everything.

The above returns something like this:

schema_name | table_name | column_name | uid
------------+------------+-------------+-------------------------------------
public      | uuid_test  | some_id     | 666ce0a8-11c9-11e0-b5e2-3b49c86ffc2c
public      | uuid_test  | some_id     | 7fffda0c-11c9-11e0-967c-a300aec7eb54
public      | some_table | id          | 3aa04664-863c-43e6-9890-1087db021fd8

But this is going to be really slow

I'm not sure the point of this question. If you need to find something yes it's a "reasonable strategy" to index it, UUID or not.

It is kind of weird to have both a UUID an "auto increment integer primary key". Usually, you go serial → bigserial → uuid. Or jump to UUID when you need sharding.


sp_executesql not accepting multiple types I have the following table:
CREATE TABLE [dbo].[Settings](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Key] [varchar](50) NOT NULL,
   [Value] [nvarchar](max) NULL,
   CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([Id] ASC)
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I'm trying to run this query
exec sp_executesql
   N'INSERT INTO Settings ([key], [value]) VALUES (@k6,@v6), (@k8,@v8)',
   N'@k6 nvarchar(4000), @v6 nvarchar(4000), @k8 nvarchar(4000), @v8 bit',
   @k6=N'DefaultLocale', @v6=N'eb', @k8=N'ReCaptchaIsEnabled', @v8=1

Unfortunately, I'm getting this error:

Conversion failed when converting the nvarchar value 'eb' to data type
bit.

When I remove the bit definition or remove Nvarchar definition it working well. From what I see, I cannot mix different value types in this query.
Can you tell me why?
Thank you

It's the same reason this doesn't work:
DECLARE @x nvarchar(4000) = N'foo', @y bit = 1;

SELECT x = @x
UNION ALL
SELECT y = @y;

Same error. If you're going to use the wrong data types (for example, key is varchar(50), so why is @k6 nvarchar(4000)?), you're going to need to massage them to prevent unexpected errors that come from how SQL Server might construct an execution plan. Try making your statement:
INSERT INTO dbo.Settings ([key], [value])
VALUES (@k6,CONVERT(nvarchar(max),@v6)),
       (@k8,CONVERT(nvarchar(max),@v8));

It's because that causes the SQL to do the an Implicit conversion and It cause failure , here is the way SQL server engine sees your query :

you have no way unless match the datatype before passing it
you can find out more about CONVERT_IMPLICIT warning in Pinal blog:
SQL SERVER – How to Fix CONVERT_IMPLICIT Warnings?


How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"? When I run the following command I get an error, however one of my scripts requires it.

SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

Simply run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
Enter password:
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

Trivia: If you want to know what the HY00 stands for (a follow up I asked years later)....

In case you use MySQL on windows and get the error below
SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

From my post on how to resolve this issue:

The issue is you do not have the time zones data installed, to install
the time zones on a Windows machine, you will need:

Download "timezone_2020a_posix.zip - POSIX standard"
from https://dev.mysql.com/downloads/timezones.html

Stop the MySQL service

Extract the files from the downloaded file
and copy them with overwrite
to C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql
Yes, to C:\ProgramData.. and not to C:\Program Files (x86)\MySQL...

Start the MySQL service


Difference between On Delete Cascade & On Update Cascade in mysql I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE and ON DELETE CASCADE

My Parent Table

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

My Question is: What is the difference between the following sql queries.

ON DELETE CASCADE

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

ON UPDATE CASCADE

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON UPDATE CASCADE
) ENGINE=INNODB;

ON UPDATE CASCADE ON DELETE CASCADE

CREATE TABLE child (
        id INT,
        parent_id INT,
        INDEX par_ind (parent_id),
        FOREIGN KEY (parent_id)
            REFERENCES parent(id)
            ON UPDATE CASCADE ON DELETE CASCADE
    ) ENGINE=INNODB;

Are there any errors in the queries? What do these queries (1,2 & 3) mean?? Are they same???

A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here.

In the SQL 2003 standard there are 5 different referential actions:

CASCADE
RESTRICT
NO ACTION
SET NULL
SET DEFAULT

To answer the question:

CASCADE

ON DELETE CASCADE means that if the parent record is deleted, any child records are also deleted. This is not a good idea in my opinion. You should keep track of all data that's ever been in a database, although this can be done using TRIGGERs.  (However, see caveat in comments below).
ON UPDATE CASCADE means that if the parent primary key is changed, the child value will also change to reflect that. Again in my opinion, not a great idea. If you're changing PRIMARY KEYs with any regularity (or even at all!), there is something wrong with your design. Again, see comments.
ON UPDATE CASCADE ON DELETE CASCADE means that if you UPDATE OR DELETE the parent, the change is cascaded to the child. This is the equivalent of ANDing the outcomes of first two statements.

RESTRICT

RESTRICT means that any attempt to delete and/or update the parent will fail throwing an error. This is the default behaviour in the event that a referential action is not explicitly specified.

  For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT`.

NO ACTION

NO ACTION: From the manual. A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

SET NULL

SET NULL - again from the manual. Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. This is not the best of ideas IMHO, primarily because there is no way of "time-travelling" - i.e. looking back into the child tables and associating records with NULLs with the relevant parent record - either CASCADE or use TRIGGERs to populate logging tables to track changes (but, see comments).

SET DEFAULT

SET DEFAULT. Yet another (potentially very useful) part of the SQL standard that MySQL hasn't bothered implementing! Allows the developer to specify a value to which to set the foreign key column(s) on an UPDATE or a DELETE. InnoDB and NDB will reject table definitions with a SET DEFAULT clause.

As mentioned above, you should spend some time looking at the documentation, here.

I don't have enough reputation to comment on the previous answers.  So I thought I'd elaborate a bit.

1) ON DELETE CASCADE means if the parent record is deleted, then any referencing child records are also deleted.  ON UPDATE  defaults to RESTRICT, which means the UPDATE on the parent record will fail.

2) ON DELETE action defaults to RESTRICT, which means the DELETE on the parent record will fail.  ON UPDATE CASCADE will update all referencing child records when the parent record is updated.

3) See the CASCADE actions in 1) and 2) above.

On using parent record IDs as foreign keys (in child tables) -- experience says a) if the IDs are auto-generated sequence numbers, then DO NOT use them as foreign keys. Use some other unique parent key instead. b) if the IDs are GUIDs, then it's ok to use them as foreign keys. You'll see the wisdom in this suggestion when you export and import the records or copy records to another database. It's too cumbersome to deal with auto-generated sequence numbers during data migration when they are referenced as foreign keys.


SQL Server not starting at Windows startup but starts manually My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it starts just fine when I manually start the service.

Event Viewer shows that the service fails to start because of a connection timeout. There are 2 entries regarding this timeout:

  Entry 1

  A timeout was reached (30000 milliseconds) while waiting for the SQL
  Server (SQLEXPRESS) service to connect.

  Entry 2

  The SQL Server (SQLEXPRESS) service failed to start due to the
  following error:  The service did not respond to the start or control
  request in a timely fashion.

I checked the ERRORLOG files and there are no errors logged around the time of those Event Viewer entries.

Any ideas on the cause of this problem or how I can investigate further?

This issue happens because the SQL Server lost the logon id credentials or used an account without rights to start services.

For starting your services automatically, SQL Server needs an account with rights to start services during operating system start up.

To solve this issue you need to go
Administrative Tools > Services > SQL Server(MSSQLSERVER or the name of your instances) > Right Click >  Properties > Move to Log On > Set the Log On option using a Local System Account

If you have chosen this option, hit on Browse Button and find the Local Service Account or Network service Account there > Click in Apply.

Restart your computer and you will able to start your SQL Server.

I went to Local service and changed

SQL Server(MSSQLSERVER
SQL Server Agent (MSSQLSERVER)

Set them to Automatic Delayed
and
Logon type to

Local System Account
Tick ( Allow service to Interact with desktop)

And it starts automatically


Is it possible to virtually vertical partition and oracle database table? Just like the question asks; is it possible to transparently partition a table vertically, by using virtual partitions? I'd imagine if it was possible, that columns would just be stored in separate table spaces or some such.

In my specific environment, we have an application server that batch writes a large amount of data every 15 minutes or so. There is way more data than our reporting applications need. The primary fact table has roughly 50 columns. We use about 10 of them.

Is there any scenario (even if virtual vertical partitions do not exist), that such a scheme could improve performance? I'd imagine that having your data split across multiple tablespaces (and therefore potentially multiple disks) would improve the seek times.

I'd imagine that having your data split across multiple tablespaces (and therefore potentially multiple disks) would improve the seek times.

Your whole premise behind the question is faulty. You do not need to split your data between tablespaces to spread the i/o across more disks, you need to increase the number of disks in your RAID10 or (better still) ASM array. You will get less performance gain, less space efficiency and far more maintenance trying to manually tune the i/o like you are suggesting.

ASM beats RAID10 primarily because it understands the data being written to it - so for example it can vary the stripe size between data blocks and logs.

Create a materialised view with the 10 columns then index that view