Search Results

Search found 26115 results on 1045 pages for 'table alias'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 170/1045 | < Previous Page | 166 167 168 169 170 171 172 173 174 175 176 177  | Next Page >

  • In MySQL how can I tell what character set a particular table is using?

    - by muudscope
    I have a large mysql table that I think might be using the wrong character set. If so I'll need to change it using ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 But since this is a very large table, I'd rather not run this command unless I have to. So my question is, how can I ask mysql what the character set is on a particular table? I can call status in mysql to see the database's character set, but that doesn't necessarily mean all the tables have the same character set, right?

    Read the article

  • How do I detect a Word table with (horizontally) merged cells?

    - by Reuben
    When a Word table contains horizontally merged cells, accessing aTable.Columns.First or performing a For Each over aTable.Columns will result in an error. Is there a way to determine if a table contains horizontally merged cells without resulting in an error? I've read Determine if a Word cell is merged, but that is about detecting if a particular Word table cell is merged, rather than does the whole table have any merged cells.

    Read the article

  • JPA - Can I create an Entity class, using an @DiscriminatorValue, that doesn't have its own table?

    - by DaveyDaveDave
    Hi - this is potentially a bit complex, so I'll do my best to describe my situation - it's also my first post here, so please forgive formatting mistakes, etc! I'm using JPA with joined inheritance and a database structure that looks like: ACTION --------- ACTION_ID ACTION_MAPPING_ID ACTION_TYPE DELIVERY_CHANNEL_ACTION -------------------------- ACTION_ID CHANNEL_ID OVERRIDE_ADDRESS_ACTION -------------------------- ACTION_ID (various fields specific to this action type) So, in plain English, I have multiple different types of action, all share an ACTION_MAPPING, which is referenced from the 'parent' ACTION table. DELIVERY_CHANNEL_ACTION and OVERRIDE_ADDRESS_ACTION both have extra, supplementary data of their own, and are mapped to ACTION with a FK. Real-world, I also have a 'suppress' action, but this doesn't have any supplementary data of its own, so it doesn't have a corresponding table - all it needs is an ACTION_MAPPING, which is stored in the ACTION table. Hopefully you're with me so far... I'm creating a new project from scratch, so am pretty flexible in what I can do, but obviously would like to get it right from the outset! My current implementation, which works, has three entities loosely defined as follows: @Entity @Table(name="ACTION") @Inheritance(strategy=InheritanceType.JOINED) @DiscriminatorValue("SUPPRESS") public class Action @Entity @Table(name="DELIVERY_CHANNEL_ACTION") @DiscriminatorValue("DELIVERY_CHANNEL") public class DeliveryChannelAction extends Action @Entity @Table(name="OVERRIDE_ADDRESS_ACTION") @DiscriminatorValue("OVERRIDE_ADDRESS") public class OverrideAddressAction extends Action That is - I have a concrete base class, Action, with a Joined inheritance strategy. DeliveryChannelAction and OverrideAddressAction both extend Action. What feels wrong here though, is that my Action class is the base class for these two actions, but also forms the concrete implementation for the suppress action. For the time being this works, but at some point more actions are likely to be added, and there's every chance that some of them will, like SUPPRESS, have no supplementary data, which will start to get difficult! So... what I would like to do, in the object model world, is to have Action be abstract, and create a SuppressAction class, which is empty apart from having a @DiscriminatorValue("SUPPRESS"). I've tried doing exactly what is described above, so, changing Action to: @Entity @Table(name="ACTION") @Inheritance(strategy=InheritanceType.JOINED) public abstract class Action and creating: @DiscriminatorValue("SUPPRESS") public class SuppressAction extends Action but no luck - it seems to work fine for DeliveryChannelAction and OverrideAddressAction, but when I try to create a SuppressAction and persist it, I get: java.lang.IllegalArgumentException: Object: com.mypackage.SuppressAction[actionId=null] is not a known entity type. at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.registerNewObjectForPersist(UnitOfWorkImpl.java:4147) at org.eclipse.persistence.internal.jpa.EntityManagerImpl.persist(EntityManagerImpl.java:368) at com.mypackage.test.util.EntityTestUtil.createSuppressAction(EntityTestUtil.java:672) at com.mypackage.entities.ActionTest.testCRUDAction(ActionTest.java:27) which I assume is down to the fact that SuppressAction isn't registered as an entity, but I don't know how I can do that, given that it doesn't have an associated table. Any pointers, either complete answers or hints for things to Google (I'm out of ideas!), most welcome :) EDIT: to correct my stacktrace.

    Read the article

  • Mutiple FK columns all pointing to the same parent table - a good idea?

    - by Randy Minder
    For those of you who live and breath database design, have you ever found compelling reasons to have multiple FK's in a table that all point to the same parent table? We recently had to deal with a situation where we had a table that contained six columns which were all FK columns to the same parent table. We're debating whether this indicates a poor design on our part or whether this is more common than we think. Thanks very much.

    Read the article

  • Is any tool can help create/fix a table into normal form?

    - by WuYi
    Hi,I know the importance of being normal in database design. But many people always lives with broken windows. Is any tool can help create/fix a table to normal form? I mean to force developer create table in normal form, or help to check if the table being normal base on existed data within the table. Thanks……

    Read the article

  • Can you define values in a SQL statement that you can join/union, but are not stored in a table outs

    - by Mervyn
    I'm trying to create a query and need to join against something that I can define values in without creating a table. I'll attempt to describe what I'm trying to do: table1 is joined on field a with table2 (titles for FK in table 1) - Table1 has values outside of what exists in table2 - I want to add an additional 'table' to be unioned with table2 and then joined with table 1 Thanks

    Read the article

  • How do I mysql select with aliases from another table?

    - by Rob
    I'm working with a CMS system where I cannot control database column names. And I've got two related tables: Table: content +------------+----------+----------+----------+----------+ | content_id | column_1 | column_2 | column_3 | column_4 | +------------+----------+----------+----------+----------+ | 1 | stuff | junk | text | info | | 2 | trash | blah | what | bio | +------------+----------+----------+----------+----------+ Table: column_names +------------+-------------+ | column_id | column_name | +------------+-------------+ | 1 | good_text | | 2 | bad_text | | 3 | blue_text | | 4 | red_text | +------------+-------------+ What I'd like to do here is select from the first table, but select the columns AS the column_name from the second table. So my result would look like: +------------+-----------+----------+-----------+----------+ | content_id | good_text | bad_text | blue_text | red_text | +------------+-----------+----------+-----------+----------+ | 1 | stuff | junk | text | info | | 2 | trash | blah | what | bio | +------------+-----------+----------+-----------+----------+

    Read the article

  • Configure TFS portal afterwards

    Update #1 January 8th, 2010: There is an updated post on this topic for Beta 2: http://www.ewaldhofman.nl/post/2009/12/10/Configure-TFS-portal-afterwards-Beta-2.aspx Update #2 October 10th, 2010: In the new Team Foundation Server Power Tools September 2010, there is now a command to create a portal. tfpt addprojectportal   Add or move portal for an existing team project Usage: tfpt addprojectportal /collection:uri                              /teamproject:"project name"                              /processtemplate:"template name"                              [/webapplication:"webappname"]                              [/relativepath:"pathfromwebapp"]                              [/validate]                              [/verbose] /collection Required. URL of Team Project Collection. /teamproject Required. Specifies the name of the team project. /processtemplate Required. Specifies that name of the process template. /webapplication The name of the SharePoint Web Application. Must also specify relativepath. /relativepath The path for the site relative to the root URL for the SharePoint Web Application. Must also specify webapplication. /validate Specifies that the user inputs are to be validated. If specified, only validation will be done and no portal setting will be changed. /verbose Switches on the verbose mode. I created a new Team Project in TFS 2010 Beta 1 and choose not to configure SharePoint during the creation of the Team Project. Of course I found out fairly quickly that a portal for TFS is very useful, especially the Iteration and the Product backlog workbooks and the dashboard reports. This blog describes how you can configure the sharepoint portal afterwards. Update: September 9th, 2009 Adding the portal afterwards is much easier as described below. Here are the steps Step 1: Create a new temporary project (with a SharePoint site for it). Open the Team Explorer Right click in the Team Explorer the root node (i.e. the project collection) Select "New team project" from the menu Walk throught he wizard and make sure you check the option to create the portal (which is by default checked) Step 2: Disable the site for the new project Open the Team Explorer Select the team project you created in step 1 In the menu click on Team -> Show Project Portal. In the menu click on Team -> Team Project Settings -> Portal Settings... The following dialog pops up Uncheck the option "Enable team project portal" Confirm the dialog with OK Step 3: Enable the site for the original one. Point it to the newly created site. Open the Team Explorer Select the team project you want to add the portal to In the menu open Team -> Team Project Settings -> Portal Settings... The same dialog as in step 2 pops up Check the option "Enable team project portal" Click on the "Configure URL" button The following dialog pops up   In the dialog select in the combobox of the web application the TFS server Enter in the Relative site path the text "sites/[Project Collection Name]/[Team Project Name created in step 1]" Confirm the "Specify an existing SharePoint Site" with OK Check the "Reports and dashboards refer to data for this team project" option Confirm the dialog "Project Portal Settings" with OK Step 4: Delete the temporary project you created. In Beta 1, I have found no way to delete a team project. Maybe it will be available in TFS 2010 Beta 2. Original post Step 1: Create new portal site Go to the sharepoint site of your project collection (/sites//default.aspx">/sites//default.aspx">http://<servername>/sites/<project_collection_name>/default.aspx) Click on the Site Actions at the left side of the screen and choose the option Site Settings In the site settings, choose the Sites and workspaces option Create a new site Enter the values for the Title, the description, the site address. And choose for the TFS2010 Agile Dashboard as template. Create the site, by clicking on the Create button Step 2: Integrate portal site with team project Open Visual Studio Open the Team Explorer (View -> Team Explorer) Select in the Team Explorer tool window the Team Project for which you are create a new portal Open the Project Portal Settings (Team -> Team Project Settings -> Portal Setings...) Check the Enable team project portal checkbox Click on Configure URL... You will get a new dialog as below Enter the url to the TFS server in the web application combobox And specify the relative site path: sites/<project collection>/<site name> Confirm with OK Check in the Project Portal Settings dialog the checkbox "Reports and dashboards refer to data for this team project" Confirm the settings with OK (this takes a while...) When you now browse to the portal, you will see that the dashboards are now showing up with the data for the current team project. Step 3: Download process template To get a copy of the documents that are default in a team project, we need to have a fresh set of files that are not attached to a team project yet. You can do that with the following steps. Start the Process Template Manager (Team -> Team Project Collection Settings -> Process Template Manager...) Choose the Agile process template and click on download Choose a folder to download Step 4: Add Product and Iteration backlog Go to the Team Explorer in Visual Studio Make sure the team project is in the list of team projects, and expand the team project Right click the Documents node, and choose New Document Library Enter "Shared Documents", and click on Add Right click the Shared Documents node and choose Upload Document Go the the file location where you stored the process template from step 3 and then navigate to the subdirectory "Agile Process Template 5.0\MSF for Agile Software Development v5.0\Windows SharePoint Services\Shared Documents\Project Management" Select in the Open Dialog the files "Iteration Backlog" and "Product Backlog", and click Open Step 5: Bind Iteration backlog workbook to the team project Right click on the "Iteration Backlog" file and select Edit, and confirm any warning messages Place your cursor in cell A1 of the Iteration backlog worksheet Switch to the Team ribbon and click New List. Select your Team Project and click Connect From the New List dialog, select the Iteration Backlog query in the Workbook Queries folder. The final step is to add a set of document properties that allow the workbook to communicate with the TFS reporting warehouse. Before we create the properties we need to collect some information about your project. The first piece of information comes from the table created in the previous step.  As you collect these properties, copy them into notepad so they can be used in later steps. Property How to retrieve the value? [Table name] Switch to the Design ribbon and select the Table Name value in the Properties portion of the ribbon [Project GUID] In the Visual Studio Team Explorer, right click your Team Project and select Properties.  Select the URL value and copy the GUID (long value with lots of characters) at the end of the URL [Team Project name] In the Properties dialog, select the Name field and copy the value [TFS server name] In the Properties dialog, select the Server Name field and copy the value [UPDATE] I have found that this is not correct: you need to specify the instance of your SQL Server. The value is used to create a connection to the TFS cube. Switch back to the Iteration Backlog workbook. Click the Office button and select Prepare – Properties. Click the Document Properties – Server drop down and select Advanced Properties. Switch to the Custom tab and add the following properties using the values you collected above. Variable name Value [Table name]_ASServerName [TFS server name] [Table name]_ASDatabase tfs_warehouse [Table name]_TeamProjectName [Team Project name] [Table name]_TeamProjectId [Project GUID] Click OK to close the properties dialog. It is possible that the Estimated Work (Hours) is showing the #REF! value. To resolve that change the formula with: =SUMIFS([Table name][Original Estimate]; [Table name][Iteration Path];CurrentIteration&"*";[Table name][Area Path];AreaPath&"*";[Table name][Work Item Type]; "Task") For example =SUMIFS(VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Original Estimate]; VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Iteration Path];CurrentIteration&"*";VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Area Path];AreaPath&"*";VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Work Item Type]; "Task") Also the Total Remaining Work in the Individual Capacity table may contain #REF! values. To resolve that change the formula with: =SUMIFS([Table name][Remaining Work]; [Table name][Iteration Path];CurrentIteration&"*";[Table name][Area Path];AreaPath&"*";[Table name][Assigned To];[Team Member];[Table name][Work Item Type]; "Task") For example =SUMIFS(VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Remaining Work]; VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Iteration Path];CurrentIteration&"*";VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Area Path];AreaPath&"*";VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Assigned To];[Team Member];VSTS_ab392b55_6647_439a_bae4_8c66e908bc0d[Work Item Type]; "Task") Save and close the workbook. Step 6: Bind Product backlog workbook to the team project Repeat the steps for binding the Iteration backlog for thiw workbook too. In the worksheet Capacity, the formula of the Storypoints might be missing. You can resolve it with: =IF([Iteration]="";"";SUMIFS([Table name][Story Points];[Table name][Iteration Path];[Iteration]&"*")) Example =IF([Iteration]="";"";SUMIFS(VSTS_487f1e4c_db30_4302_b5e8_bd80195bc2ec[Story Points];VSTS_487f1e4c_db30_4302_b5e8_bd80195bc2ec[Iteration Path];[Iteration]&"*"))

    Read the article

  • How to make a table structure for products to be available for both wholesale and retail?

    - by kmy
    Ignoring the different column details like colors, shapes, and sizes, I already have an idea of dealing with that, I'm more interested in dealing with retail pricing, whole pricing (with or without a table), and possibly discount. If anything, I want to know what I should also take account for. Will it just be adding a quantity column, two pricing cols, and two discount columns, and a quantity limit? If I were to add a pricing table it would be inserted as a json file, would this be a bad idea? Please give any insight about this.

    Read the article

  • Where should I put a method that returns a list of active entries of a table?

    - by darga33
    I have a class named GuestbookEntry that maps to the properties that are in the database table named "guestbook". Very simple! Originally, I had a static method named getActiveEntries() that retrieved an array of all GuestbookEntry objects. Each row in the guestbook table was an object that was added to that array. Then while learning how to properly design PHP classes, I learned some things: Static methods are not desirable. Separation of Concerns Single Responsibility Principle If the GuestbookEntry class should only be responsible for managing single guestbook entries then where should this getActiveEntries() method most properly go? Update: I am looking for an answer that complies with the SOLID acronym principles and allows for test-ability. That's why I want to stay away from static calls/standard functions. DAO, repository, ...? Please explain as though your explanation will be part of "Where to Locate FOR DUMMIES"... :-)

    Read the article

  • Microsoft fait un pas de plus vers la démocratisation de Surface, en dévoilant une nouvelle mouture de sa table tactile

    Microsoft fait un pas de plus vers la démocratisation de Surface, en dévoilant une nouvelle mouture de sa table tactile Mise à jour du 06.01.2011 par Katleen Souvenez-vous de la Surface, cette table basse tactile développée par Microsoft que nous vous présentions en novembre 2009. L'objet était déjà très intéressant, mais connaissait quelques lenteurs et était plutôt massif (ceux qui l'ont déjà déplacé s'en souviennent assurément). En plus de cela, le joujou était extrêmement coûteux, de quoi vous faire dire que son acquisition, ça ne sera même pas dans dix ou vingt ans (sans les intérêts). Microsoft nourrissait déjà à l'époque l'espoir de voir un jour sa technologie plus accessible. Et la firme semble emprunter ce chemin.

    Read the article

  • Can it be a good idea to create a new table for each client of a webapp?

    - by Will
    This is semi-hypothetical, and as I've no experience in dealing with massive database tables, I have no idea if this is horrible for some reason. On to the situation: Imagine a web based application - lets say accounting software - which has 20,000 clients and each client has 1000+ entries in a table. That's 20 million rows which I know can certainly slow down complex queries. In a case like this, does it make more sense to create a new table in the database for each client? How do databases react to having 20k (or more!) tables?

    Read the article

  • Trying to import SQL file in a xampp server returns error

    - by Victor_J_Martin
    I have done a ER diagram in Mysql Workbench, and I am trying load in my server with phpMyAdmin, but it returns me the next error: Error SQL Query: -- ----------------------------------------------------- -- Table `BDA`.`UG` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`UG` ( `numero_ug` INT NOT NULL, `nombre` VARCHAR(45) NOT NULL, `segunda_firma_autorizada` VARCHAR(45) NOT NULL, `fecha_creacion` DATE NOT NULL, `nombre_depto` VARCHAR(140) NOT NULL, `dni` INT NOT NULL, `anho_contable` INT NOT NULL, PRIMARY KEY (`numero_ug`), INDEX `nombre_depto_idx` (`nombre_depto` ASC), INDEX `dni_idx` (`dni` ASC), INDEX `anho_contable_idx` (`anho_contable` ASC), CONSTRAINT `nombre_depto` FOREIGN KEY (`nombre_depto`) REFERENCES `BDA`.`Departamento` (`nombre_depto`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `dni` FOREIGN KEY (`dni`) REFERENCES `BDA`.`Trabajador` (`dni`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `anho_contable` FOREIGN KEY (`anho_contable`) REFERENCES `BDA`.`Capitulo_Contable` (`anho_contable`) [...] MySQL said: Documentation #1022 - Can't write; duplicate key in table 'ug' I export the result of the diagram from Mysql Workbench to a SQL file, and this file is what I'm trying to upload. This is the file. I can not find the duplicate key. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; CREATE SCHEMA IF NOT EXISTS `BDA` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; USE `BDA` ; -- ----------------------------------------------------- -- Table `BDA`.`Departamento` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Departamento` ( `nombre_depto` VARCHAR(140) NOT NULL, `area_depto` VARCHAR(140) NOT NULL, PRIMARY KEY (`nombre_depto`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Trabajador` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Trabajador` ( `dni` INT NOT NULL, `direccion` VARCHAR(140) NOT NULL, `nombre` VARCHAR(45) NOT NULL, `apellidos` VARCHAR(140) NOT NULL, `fecha_nacimiento` DATE NOT NULL, `fecha_contrato` DATE NOT NULL, `titulacion` VARCHAR(140) NULL, `nombre_depto` VARCHAR(45) NOT NULL, PRIMARY KEY (`dni`), INDEX `nombre_depto_idx` (`nombre_depto` ASC), CONSTRAINT `nombre_depto` FOREIGN KEY (`nombre_depto`) REFERENCES `BDA`.`Departamento` (`nombre_depto`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Capitulo_Contable` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Capitulo_Contable` ( `anho_contable` INT NOT NULL, `numero_ug` INT NOT NULL, `debe` DOUBLE NOT NULL, `haber` DOUBLE NOT NULL, PRIMARY KEY (`anho_contable`), INDEX `numero_ug_idx` (`numero_ug` ASC), CONSTRAINT `numero_ug` FOREIGN KEY (`numero_ug`) REFERENCES `BDA`.`UG` (`numero_ug`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`UG` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`UG` ( `numero_ug` INT NOT NULL, `nombre` VARCHAR(45) NOT NULL, `segunda_firma_autorizada` VARCHAR(45) NOT NULL, `fecha_creacion` DATE NOT NULL, `nombre_depto` VARCHAR(140) NOT NULL, `dni` INT NOT NULL, `anho_contable` INT NOT NULL, PRIMARY KEY (`numero_ug`), INDEX `nombre_depto_idx` (`nombre_depto` ASC), INDEX `dni_idx` (`dni` ASC), INDEX `anho_contable_idx` (`anho_contable` ASC), CONSTRAINT `nombre_depto` FOREIGN KEY (`nombre_depto`) REFERENCES `BDA`.`Departamento` (`nombre_depto`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `dni` FOREIGN KEY (`dni`) REFERENCES `BDA`.`Trabajador` (`dni`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `anho_contable` FOREIGN KEY (`anho_contable`) REFERENCES `BDA`.`Capitulo_Contable` (`anho_contable`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Cliente` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Cliente` ( `cif_cliente` INT NOT NULL, `nombre_cliente` VARCHAR(140) NOT NULL, PRIMARY KEY (`cif_cliente`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Ingreso` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Ingreso` ( `id` INT NOT NULL, `concepto` VARCHAR(45) NOT NULL, `importe` DOUBLE NOT NULL, `fecha` DATE NOT NULL, `cif_cliente` INT NOT NULL, `numero_ug` INT NOT NULL, PRIMARY KEY (`id`), INDEX `cif_cliente_idx` (`cif_cliente` ASC), INDEX `numero_ug_idx` (`numero_ug` ASC), CONSTRAINT `cif_cliente` FOREIGN KEY (`cif_cliente`) REFERENCES `BDA`.`Cliente` (`cif_cliente`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `numero_ug` FOREIGN KEY (`numero_ug`) REFERENCES `BDA`.`UG` (`numero_ug`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Proveedor` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Proveedor` ( `cif_proveedor` INT NOT NULL, `nombre_proveedor` VARCHAR(140) NOT NULL, PRIMARY KEY (`cif_proveedor`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `BDA`.`Gasto` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `BDA`.`Gasto` ( `id` INT NOT NULL, `concepto` VARCHAR(45) NOT NULL, `importe` DOUBLE NOT NULL, `fecha` DATE NOT NULL, `factura` INT NOT NULL, `cif_proveedor` INT NOT NULL, `numero_ug` INT NOT NULL, PRIMARY KEY (`id`), INDEX `cif_proveedor_idx` (`cif_proveedor` ASC), INDEX `numero_ug_idx` (`numero_ug` ASC), CONSTRAINT `cif_proveedor` FOREIGN KEY (`cif_proveedor`) REFERENCES `BDA`.`Proveedor` (`cif_proveedor`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `numero_ug` FOREIGN KEY (`numero_ug`) REFERENCES `BDA`.`UG` (`numero_ug`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Thanks for your advices.

    Read the article

  • parse Linq To Xml with attribute nodes

    - by Manoj
    I am having xml with following structure <ruleDefinition appId="3" customerId = "acf"> <node alias="element1" id="1" name="department"> <node alias="element2" id="101" name="mike" /> <node alias="element2" id="102" name="ricky" /> <node alias="element2" id="103" name="jim" /> </node> </ruleDefinition> Here nodes are differentiated using alias and not with node tag. As you can see top level node element1 has same node name "node" as element2. I want to parse this XML based on attribute alias. What should be the Linq-To-Xml code (using C#)to acheive this?

    Read the article

  • How to recursively delete some xml elements using XSLT

    - by Monomachus
    Hi, So I got this situation which sucks. I have an XML like this <table border="1" cols="200 100pt 200"> <tr> <td>isbn</td> <td>title</td> <td>price</td> </tr> <tr> <td /> <td /> <td> <span type="champsimple" id="9b297fb5-d12b-46b1-8899-487a2df0104e" categorieid="a1c70692-0427-425b-983c-1a08b6585364" champcoderef="01f12b93-b4c5-401b-9da1-c9385d77e43f"> [prénom] </span> <span type="champsimple" id="e103a6a5-d1be-4c34-8a54-d234179fb4ea" categorieid="a1c70692-0427-425b-983c-1a08b6585364" champcoderef="01f12b93-b4c5-401b-9da1-c9385d77e43f">[nom]</span> <span></span> </td> </tr> <tr></tr> <tr> <td></td> <td>Phill It in</td> </tr> <tr> <table id="cas1"> <tr> <td ></td> <td >foo</td> </tr> <tr> <td >bar</td> <td >boo</td> </tr> </table> </tr> <tr> <table id="cas2"> <tr> <td ></td> <td >foo</td> </tr> <tr> <td ></td> <td >boo</td> </tr> </table> </tr> <tr> <table id="cas3"> <tr> <td >bar</td> <td ></td> </tr> <tr> <td >foo</td> <td >boo</td> </tr> </table> </tr> <tr> <table id="cas4"> <tr> <td /> <td /> </tr> <tr> <td>foo</td> <td>boo</td> </tr> </table> </tr> <table id="cas4"> <tr> <td /> <td /> </tr> <tr> <td>foo</td> <td>boo</td> </tr> </table> <tr> <td /> <td /> </tr> </table> Now the question is how would I recursively delete all empty td, tr and table elements? Now I use this XSLT <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output omit-xml-declaration="yes" indent="yes"/> <xsl:strip-space elements="*" /> <xsl:template match="node()|@*"> <xsl:copy> <xsl:apply-templates select="node()|@*"/> </xsl:copy> </xsl:template> <xsl:template match="td[not(node())]" /> <xsl:template match="tr[not(node())]" /> <xsl:template match="table[not(node())]" /> </xsl:stylesheet> But it doesn't do very well. After I delete td, a tr becomes empty but it doesn't handle that. Too bad. See the table element with "cas4". <table border="1" cols="200 100pt 200"> <tr> <td>isbn</td> <td>title</td> <td>price</td> </tr> <tr> <td> <span type="champsimple" id="9b297fb5-d12b-46b1-8899-487a2df0104e" categorieid="a1c70692-0427-425b-983c-1a08b6585364" champcoderef="01f12b93-b4c5-401b-9da1-c9385d77e43f"> [prénom] </span> <span type="champsimple" id="e103a6a5-d1be-4c34-8a54-d234179fb4ea" categorieid="a1c70692-0427-425b-983c-1a08b6585364" champcoderef="01f12b93-b4c5-401b-9da1-c9385d77e43f">[nom]</span> <span /> </td> </tr> <tr> <td>Phill It in</td> </tr> <tr> <table id="cas1"> <tr> <td>foo</td> </tr> <tr> <td>bar</td> <td>boo</td> </tr> </table> </tr> <tr> <table id="cas2"> <tr> <td>foo</td> </tr> <tr> <td>boo</td> </tr> </table> </tr> <tr> <table id="cas3"> <tr> <td>bar</td> </tr> <tr> <td>foo</td> <td>boo</td> </tr> </table> </tr> <tr> <table id="cas4"> <tr /> <tr> <td>foo</td> <td>boo</td> </tr> </table> </tr> <table id="cas4"> <tr /> <tr> <td>foo</td> <td>boo</td> </tr> </table> <tr /> </table> How would you solve this problem?

    Read the article

  • Reverse Expression.Like criterion

    - by Joel Potter
    How should I go about writing a backwards like statement using NHibernate criteria? WHERE 'somestring' LIKE [Property] + '%' Sub Question: Can you access the abstract root alias in a SQLCriterion expression? This is somewhat achievable using the SQLCriterion expression Expression.Sql("? like {alias}.[Property] + '.%'", value, NHibernateUtil.String); However, in the case of class inheritance, {alias} is replaced with the incorrect alias for the column. Example (these classes are stored in separate tables): public abstract class Parent { public virtual string Property { get; set; } } public class Child : Parent { } The above query executed with Child as the root type will replace {alias} with the alias to the Child table rather than the Parent table. This results in an invalid column exception. I need to execute a like statement as above where the property exists on the parent table rather than on the root type table.

    Read the article

  • How do I UPDATE a Linked Server table where "alias" is required, in SQL Server 2000?

    - by Mark Hurd
    In SQL Server 2005 tablename can be used to distinguish which table you're referring to: UPDATE LinkedServer.database.user.tablename SET val=u.val FROM localtable u WHERE tablename.ID=u.ID In SQL Server 2000 this results in Server: Msg 107, Level 16, State 2 The column prefix 'tablename' does not match with a table name or alias name used in the query. Trying UPDATE LinkedServer.database.user.tablename SET val=u.val FROM localtable u WHERE LinkedServer.database.user.tablename.ID=u.ID results in Server: Msg 117, Level 15, State 2 The number name 'LinkedServer.database.user.tablename' contains more than the maximum number of prefixes. The maximum is 3. And, of course, UPDATE LinkedServer.database.user.tablename SET val=u.val FROM localtable u WHERE ID=u.ID results in Server: Msg 209, Level 16, State 1 Ambiguous column name 'ID'. (In fact searching on "The number name contains more than the maximum number of prefixes. The maximum is 3." I found the answer, but I've typed up this question and I'm going to post it! :-) )

    Read the article

  • SQL SERVER – Using expressor Composite Types to Enforce Business Rules

    - by pinaldave
    One of the features that distinguish the expressor Data Integration Platform from other products in the data integration space is its concept of composite types, which provide an effective and easily reusable way to clearly define the structure and characteristics of data within your application.  An important feature of the composite type approach is that it allows you to easily adjust the content of a record to its ultimate purpose.  For example, a record used to update a row in a database table is easily defined to include only the minimum set of columns, that is, a value for the key column and values for only those columns that need to be updated. Much like a class in higher level programming languages, you can also use the composite type as a way to enforce business rules onto your data by encapsulating a datum’s name, data type, and constraints (for example, maximum, minimum, or acceptable values) as a single entity, which ensures that your data can not assume an invalid value.  To what extent you use this functionality is a decision you make when designing your application; the expressor design paradigm does not force this approach on you. Let’s take a look at how these features are used.  Suppose you want to create a group of applications that maintain the employee table in your human resources database. Your table might have a structure similar to the HumanResources.Employee table in the AdventureWorks database.  This table includes two columns, EmployeID and rowguid, that are maintained by the relational database management system; you cannot provide values for these columns when inserting new rows into the table. Additionally, there are columns such as VacationHours and SickLeaveHours that you might choose to update for all employees on a monthly basis, which justifies creation of a dedicated application. By creating distinct composite types for the read, insert and update operations against this table, you can more easily manage this table’s content. When developing this application within expressor Studio, your first task is to create a schema artifact for the database table.  This process is completely driven by a wizard, only requiring that you select the desired database schema and table.  The resulting schema artifact defines the mapping of result set records to a record within the expressor data integration application.  The structure of the record within the expressor application is a composite type that is given the default name CompositeType1.  As you can see in the following figure, all columns from the table are included in the result set and mapped to an identically named attribute in the default composite type. If you are developing an application that needs to read this table, perhaps to prepare a year-end report of employees by department, you would probably not be interested in the data in the rowguid and ModifiedDate columns.  A typical approach would be to drop this unwanted data in a downstream operator.  But using an alternative composite type provides a better approach in which the unwanted data never enters your application. While working in expressor  Studio’s schema editor, simply create a second composite type within the same schema artifact, which you could name ReadTable, and remove the attributes corresponding to the unwanted columns. The value of an alternative composite type is even more apparent when you want to insert into or update the table.  In the composite type used to insert rows, remove the attributes corresponding to the EmployeeID primary key and rowguid uniqueidentifier columns since these values are provided by the relational database management system. And to update just the VacationHours and SickLeaveHours columns, use a composite type that includes only the attributes corresponding to the EmployeeID, VacationHours, SickLeaveHours and ModifiedDate columns. By specifying this schema artifact and composite type in a Write Table operator, your upstream application need only deal with the four required attributes and there is no risk of unintentionally overwriting a value in a column that does not need to be updated. Now, what about the option to use the composite type to enforce business rules?  If you review the composition of the default composite type CompositeType1, you will note that the constraints defined for many of the attributes mirror the table column specifications.  For example, the maximum number of characters in the NationaIDNumber, LoginID and Title attributes is equivalent to the maximum width of the target column, and the size of the MaritalStatus and Gender attributes is limited to a single character as required by the table column definition.  If your application code leads to a violation of these constraints, an error will be raised.  The expressor design paradigm then allows you to handle the error in a way suitable for your application.  For example, a string value could be truncated or a numeric value could be rounded. Moreover, you have the option of specifying additional constraints that support business rules unrelated to the table definition. Let’s assume that the only acceptable values for marital status are S, M, and D.  Within the schema editor, double-click on the MaritalStatus attribute to open the Edit Attribute window.  Then click the Allowed Values checkbox and enter the acceptable values into the Constraint Value text box. The schema editor is updated accordingly. There is one more option that the expressor semantic type paradigm supports.  Since the MaritalStatus attribute now clearly specifies how this type of information should be represented (a single character limited to S, M or D), you can convert this attribute definition into a shared type, which will allow you to quickly incorporate this definition into another composite type or into the description of an output record from a transform operator. Again, double-click on the MaritalStatus attribute and in the Edit Attribute window, click Convert, which opens the Share Local Semantic Type window that you use to name this shared type.  There’s no requirement that you give the shared type the same name as the attribute from which it was derived.  You should supply a name that makes it obvious what the shared type represents. In this posting, I’ve overviewed the expressor semantic type paradigm and shown how it can be used to make your application development process more productive.  The beauty of this feature is that you choose when and to what extent you utilize the functionality, but I’m certain that if you opt to follow this approach your efforts will become more efficient and your work will progress more quickly.  As always, I encourage you to download and evaluate expressor Studio for your current and future data integration needs. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: CodeProject, Pinal Dave, PostADay, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

    Read the article

  • SharePoint 2010 Center and Fixed Width of all content on page including the ribbon

    - by Bill Daugherty
    All, I am trying to make the width of the sharepoint 2010 web site to be within a fixed width and centered across the screen. I would like for it to be 800px and centered. When i do this, it seems like it starts to work until the ribbion bar renters. Here is my attempt so far: body.v4/* _lcid="1033" _version="14.0.4536" _LocalBinding */ body,form{ margin:0px; width:800px; text-align:center; vertical-align:middle; } .ms-toolbar{ font-family:verdana; font-size:8pt; text-decoration:none; /* [ReplaceColor(themeColor:"Hyperlink")] */ color:#0072BC; } a.ms-toolbar:hover{ text-decoration:underline; /* [ReplaceColor(themeColor:"Accent1",themeShade:"0.8")] */ color:#005e9a; } .ms-toolbar-togglebutton-on{ /* [ReplaceColor(themeColor:"Accent3-Darker")] */ border:1px solid #2353b2; /* [ReplaceColor(themeColor:"Accent4-Lightest")] */ background-color:#fffacc; } table.ms-toolbar{ height:45px; border:none; /* [RecolorImage(themeColor:"Light2",includeRectangle:{x:0,y:610,width:1,height:42})] */ background:url("/_layouts/images/bgximg.png") repeat-x -0px -610px; /* [ReplaceColor(themeColor:"Light1")] */ background-color:#fff; } table.ms-toolbar{ /* [ReplaceColor(themeColor:"Light2-Lightest")] */ border:1px solid #f1f1f2; } .ms-menutoolbar{ /* [ReplaceColor(themeColor:"Light2-Lightest")] */ border-bottom:1px solid #f1f1f2; /* [ReplaceColor(themeColor:"Light1")] */ background-color:#fff; /* [RecolorImage(themeColor:"Light2",includeRectangle:{x:0,y:610,width:1,height:42})] */ background:url("/_layouts/images/bgximg.png") repeat-x -0px -610px; height:45px; } .ms-menutoolbar td{ padding:0px 0px 0px 4px; margin:0px; border:none; } .ms-menutoolbar td a{ /* [ReplaceColor(themeColor:"Hyperlink")] */ color:#0072bc; font-size:8pt; font-family:verdana; text-decoration:none; } .ms-menutoolbar td a:hover{ /* [ReplaceColor(themeColor:"Hyperlink",themeShade:"0.82")] */ color:#005e9a; text-decoration:none; } .ms-menubuttoninactivehover,.ms-buttoninactivehover{ margin:3px; padding:3px 4px 4px 4px; border:1px solid transparent; background-color:transparent; white-space:nowrap; } .ms-menubuttonactivehover,.ms-buttonactivehover{ margin:3px; padding:3px 4px 4px 4px; /* [RecolorImage(themeColor:"Light1-Darkest",includeRectangle:{x:0,y:431,width:1,height:21})] */ background:url("/_layouts/images/bgximg.png") repeat-x -0px -431px; /* [ReplaceColor(themeColor:"Light1")] */ background-color:#fff; /* [ReplaceColor(themeColor:"Light1-Lighter")] */ border:solid 1px #cccccc; cursor:pointer; } .ms-buttoninactivehover{ white-space:nowrap; } .ms-buttoninactivehover img,.ms-buttonactivehover img{ margin:0px 1px 0px 0px; } td.ms-menutoolbarheader{ font-size:10pt; font-family:verdana; /* [ReplaceColor(themeColor:"Accent3-Medium")] */ color:#204d89; font-weight:bold; line-height:16px; padding-left:7px; padding-right:7px; } .ms-listheaderlabel{ /* [ReplaceColor(themeColor:"Dark2")] */ color:#204d89; } .ms-listheaderlabel,.ms-viewselector,.ms-viewselectortext,.ms-viewselectorhover{ font-size:8pt; font-family:tahoma; } .ms-menutoolbar td td.ms-viewselector,.ms-menutoolbar td td.ms-viewselectorhover,.ms-toolbar td td.ms-viewselector,.ms-toolbar td td.ms-viewselectorhover,td.ms-viewselector{ /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; /* [ReplaceColor(themeColor:"Dark2-Medium")] */ border:1px solid #D3D6DA; font-weight:bold; padding:0px; } .ms-menutoolbar td td{ border:none; } div.ms-viewselector,div.ms-viewselectorhover{ padding:2px 4px 2px 4px; cursor:pointer; } div.ms-viewselector a,div.ms-viewselectorhover a.ms-menu-a span{ /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; } .ms-viewselector-arrow{ vertical-align:middle; } .ms-menutoolbar td td.ms-viewselectorhover,.ms-toolbar td td.ms-viewselectorhover{ /* [RecolorImage(themeColor:"Accent1",method:"Tinting",includeRectangle:{x:0,y:654,width:1,height:18})] */ background:url("/_layouts/images/bgximg.png") repeat-x -0px -654px; /* [ReplaceColor(themeColor:"Accent1-Lighter")] */ border-color:#91cdf2; /* [ReplaceColor(themeColor:"Accent1",themeTint:"0.35")] */ background-color:#ccebff; } .ms-bottompaging{ /* [ReplaceColor(themeColor:"Accent3-Lightest")] */ background:#ebf3ff; } .ms-bottompagingline1{ height:3px; /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; } .ms-bottompagingline2,.ms-bottompagingline3{ height:1px; /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; } .ms-bottompaging .ms-vb{ /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; } .ms-bottompagingline2 img,.ms-bottompagingline3 img,.ms-partline img{ display:none; } .ms-paging{ padding-left:11px; padding-right:11px; padding-bottom:4px; font-family:tahoma,sans-serif; font-size:8pt; font-weight:normal; /* [ReplaceColor(themeColor:"Accent3-Darker")] */ color:#204d89; } .ms-bottompaging .ms-paging{ /* [ReplaceColor(themeColor:"Dark1-Medium")] */ color:#4c4c4c; } .ms-menutoolbar .ms-splitbuttondropdown{ padding:3px 2px 0px 2px; } .ms-menutoolbar .ms-splitbuttontext{ padding:0px 7px 1px 7px; } .ms-splitbutton{ margin:0px 2px; } .ms-splitbuttonhover{ margin:0px 2px; /* [RecolorImage(themeColor:"Accent6-Darker",method:"Tinting",includeRectangle:{x:0,y:431,width:1,height:21})] */ background:url("/_layouts/images/bgximg.png") repeat-x -0px -431px; border-collapse:collapse; height:22px; background-color:#fff; } .ms-splitbuttonhover .ms-splitbuttondropdown{ padding:3px 1px 0px 2px; } .ms-splitbuttonhover .ms-splitbuttontext{ padding:0px 6px 0px 6px; } .ms-splitbuttonhover .ms-splitbuttondropdown,.ms-splitbuttonhover .ms-splitbuttontext{ border:solid 1px #cccccc; cursor:pointer; } .ms-propertysheet { font-size:1em; } .ms-propertysheet th.ms-gridT1 { text-align:left; /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; width:190px; } .ms-viewselect a:link{ font-size:8pt; font-family:Verdana,sans-serif; /* [ReplaceColor(themeColor:"Accent3")] */ color:#003399; } select{ font-size:8pt; font-family:Verdana,sans-serif; } hr{ /* [ReplaceColor(themeColor:"Accent3")] */ color:#003399; height:2px; } .ms-input{ font-size:8pt; font-family:Verdana,sans-serif; } .ms-treeviewouter{ margin-top:5px; } .ms-quicklaunch table td{ /* [ReplaceColor(themeColor:"Accent3-Lighter")] */ border-top:1px solid #add1ff; } .ms-quicklaunch .ms-treeviewouter table td{ border-top:none; } .ms-quicklaunch table.ms-navheader td,.ms-quicklaunch span.ms-navheader{ padding:1px 4px 4px 4px; } div.ms-treeviewouter > div > div{ border:none; } .ms-quicklaunch span.ms-navheader{ /* [ReplaceColor(themeColor:"Accent3-Lightest")] */ background-color:#d6e8ff; /* [ReplaceColor(themeColor:"Accent3-Lighter")] */ border-top:1px solid #add1ff; /* [ReplaceColor(themeColor:"Accent3-Lightest")] */ border-left:solid 1px #f2f8ff; /* [ReplaceColor(themeColor:"Accent3-Lighter")] */ border-bottom:1px solid #add1ff; padding:1px 6px 3px 6px; } .ms-quicklaunch table.ms-navsubmenu2 td{ border:none; } .ms-quicklaunch table.ms-selectednavheader td{ width:100%; /* [ReplaceColor(themeColor:"Accent6-Lightest")] */ background-color:#fff699; } .ms-quicklaunch table.ms-selectednavheader{ border:none; } .ms-quicklaunch span{ display:block; } .ms-quicklaunch div.ms-navsubmenu1 br{ display:none; } .ms-quicklaunch table.ms-selectednav{ /* [ReplaceColor(themeColor:"Accent6-Darker")] */ border:solid 1px #d2b47a; /* [RecolorImage(themeColor:"Accent1",method:"Tinting")] */ background-image:url("/_layouts/images/selectednav.gif"); background-repeat:repeat-x; /* [ReplaceColor(themeColor:"Accent6-Lightest")] */ background-color:#ffe6a0; margin:2px; margin-bottom:0; width:97%; } .ms-quicklaunch table.ms-selectednav td{ background:transparent url("/_layouts/images/selectednavbullet.gif"); background-repeat:no-repeat; background-position:left top; /* [ReplaceColor(themeColor:"Light1")] */ border:solid 1px #ffffff; padding:0px 4px 1px 12px; margin:0px; } table.ms-selectednav td a.ms-selectednav{ background:none; /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; } .ms-quicklaunch table.ms-selectednavheader td{ width:100%; /* [ReplaceColor(themeColor:"Accent6-Lighter")] */ background-color:#ffe6a0; /* [RecolorImage(themeColor:"Accent1",method:"Tinting")] */ background-image:url("/_layouts/images/selectednav.gif"); background-repeat:repeat-x; padding-top:2px; padding-bottom:2px; /* [ReplaceColor(themeColor:"Light1")] */ border-top:solid 1px #ffffff; /* [ReplaceColor(themeColor:"Light1")] */ border-left:solid 1px #ffffff; padding:1px 6px 3px 6px; } .ms-selectednavheader a{ font-weight:bold; /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; text-decoration:none; } .ms-selectednavheader a:hover{ /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; text-decoration:underline; } table.ms-navitem td,span.ms-navitem{ background-image:url("/_layouts/images/navBullet.gif"); background-repeat:no-repeat; background-position:left top; padding:3px 6px 4px 16px; font-family:tahoma; } .ms-navsubmenu1{ width:100%; border-collapse:collapse; /* [ReplaceColor(themeColor:"Light1-Lightest")] */ background-color:#f2f8ff; } .ms-navsubmenu2{ width:100%; /* [ReplaceColor(themeColor:"Light1-Lightest")] */ background-color:#f2f8ff; margin-bottom:6px; } table.ms-navselected{ padding:2px; } table.ms-navselected,span.ms-navselected{ /* [RecolorImage(themeColor:"Accent6",method:"Tinting")] */ background-image:url("/_layouts/images/SELECTEDNAV.GIF"); /* [ReplaceColor(themeColor:"Accent6-Lighter")] */ background-color:#ffe6a0; background-repeat:repeat-x; } table.ms-navselected td{ background-image:url("/_layouts/images/navBullet.gif"); background-repeat:no-repeat; background-position:top left; padding:3px 6px 4px 17px; } table.ms-navheader td{ background-image:none; } .ms-navheader a{ font-weight:bold; /* [ReplaceColor(themeColor:"Accent3")] */ color:#003399; text-decoration:none; } .ms-navheader a:hover{ /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; text-decoration:underline; } .ms-navitem a{ /* [ReplaceColor(themeColor:"Dark2")] */ color:#3b4f65 !important; text-decoration:none; display:inline-block; } .ms-navitem a:hover{ /* [ReplaceColor(themeColor:"Accent1")] */ color:#44aff6 !important; text-decoration:underline !important; } .ms-quicklaunchouter{ border:none; margin-bottom:5px; } .ms-quicklaunchouter{ margin:0px 1px 2px 1px; } .ms-treeviewouter a.ms-navitem{ padding:4px 4px 5px; margin-left:4px; border-color:transparent; border-width:1px; border-style:solid !important; } .ms-tvselected a.ms-navitem{ /* [RecolorImage(themeColor:"Light1")] */ background:url("/_layouts/images/selbg.png") repeat-x left top; /* [ReplaceColor(themeColor:"Accent1",themeTint:"0.15")] */ background-color:#ccebff; /* [ReplaceColor(themeColor:"Accent1-Lighter")] */ border-color:#91cdf2; /* [ReplaceColor(themeColor:"Accent1-Lightest")] */ border-top-color:#c6e5f8; border-width:1px; border-style:solid !important; /* [ReplaceColor(themeColor:"Dark2")] */ color:#003759 !important; display:inline-block; } .ms-tvselected a:hover{ /* [ReplaceColor(themeColor:"Dark2")] */ color:#003759 !important; } table.ms-recyclebin td{ /* [ReplaceColor(themeColor:"Light1-Lightest")] */ background-color:#f2f8ff; width:100%; /* [ReplaceColor(themeColor:"Light1")] */ border-top:solid 1px #ffffff; /* [ReplaceColor(themeColor:"Light1")] */ border-left:solid 1px #ffffff; padding:3px 5px 7px 3px; } table.ms-recyclebin td a{ font-weight:bold; /* [ReplaceColor(themeColor:"Accent5-Darker")] */ color:#008800; text-decoration:none; } table.ms-recyclebin td a:hover{ /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; text-decoration:underline; } .ms-quickLaunch{ padding-top:5px; } .ms-quickLaunch h3{ font-size:1em; font-weight:normal; /* [ReplaceColor(themeColor:"Dark2")] */ color:#929fad; margin:0px 0px 6px 10px; } .ms-quicklaunchheader{ padding:2px 6px 4px 10px; font-weight:bold; /* [ReplaceColor(themeColor:"Light1-Lighter")] */ color:#676767; background-image:url("/_layouts/images/quickLaunchHeader.gif"); background-repeat:repeat-x; /* [ReplaceColor(themeColor:"Accent3-Lightest")] */ background-color:#d6e8ff; /* [ReplaceColor(themeColor:"Light1-Lightest")] */ border-left:solid 1px #f2f8ff; margin-left:-7px; font-size:inherit; } .ms-quicklaunchheader a,.ms-unselectednav a{ /* [ReplaceColor(themeColor:"Dark1-Lighter")] */ color:#676767 !important; text-decoration:none; } .ms-quicklaunchheader a:hover{ /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000 !important; text-decoration:underline; } .ms-navline{ /* [ReplaceColor(themeColor:"Light1-Darker")] */ border-bottom:1px solid #adadad; } .ms-navwatermark{ /* [ReplaceColor(themeColor:"Accent6-Lighter")] */ color:#ffdf88; } .ms-selectednav{ border:1px solid #2353b2; /* [ReplaceColor(themeColor:"Accent6-Lightest")] */ background:#fff699; padding-top:1px; padding-bottom:2px; } .ms-unselectednav{ /* [ReplaceColor(themeColor:"Accent3-Medium")] */ border:1px solid #83b0ec; padding-top:1px; padding-bottom:2px; } .ms-verticaldots{ /* [ReplaceColor(themeColor:"Accent3-Medium")] */ border-right:1px solid #83b0ec; border-left:none; } .ms-nav{ /* [ReplaceColor(themeColor:"Accent3-Medium")] */ background-color:#83b0ec; font-family:tahoma; } .ms-globalTitleArea{ text-align:right; background-image:url("/_layouts/images/siteTitleBKGD.gif"); background-position:right top; background-repeat:repeat-y; padding-left:5px; padding-right:0px; padding-top:1px; } .ms-titlearea{ /* [ReplaceColor(themeColor:"Dark1-Lighter")] */ color:#666666; font-family:tahoma; font-size:8pt; letter-spacing:.1em; } .ms-titlearea a { /* [ReplaceColor(themeColor:"Accent3-Darker")] */ color:#3966bf; text-decoration:none; } .ms-titlearea a:hover { /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; text-decoration:underline; } .ms-titlearealeft { /* [ReplaceColor(themeColor:"Accent3-Lightest")] */ background-color:#d6e8ff; } TD.ms-titleareaframe,Div.ms-titleareaframe,.ms-pagetitleareaframe{ background:url("/_layouts/images/bgximg.png") repeat-x -0px -461px; /* [ReplaceColor(themeColor:"Accent3-Lightest")] */ background-color:#d6e8ff; text-align:left; } div.ms-titleareaframe{ height:100%; } .ms-pagetitleareaframe table{ background-image:url("/_layouts/images/topshape.jpg"); background-repeat:no-repeat; background-position:332px 4px; height:54px; } .ms-titlearealine{ /* [ReplaceColor(themeColor:"Accent3-Medium")] */ background-color:#83b0ec; } .ms-titleareaframe table td.ms-titlearea,.ms-areaseparator table td.ms-titlearea,.ms-pagetitleareaframe table td.ms-titlearea{ padding:7px 0px 1px 0px; } .ms-sitemapdirectional,.ms-sitemapdirectional a{ unicode-bidi:embed; } .ms-areaseparatorcorner{ background-image:url("/_layouts/images/framecornergrad.gif"); background-position:left top; background-repeat:repeat-y; height:8px; /* [ReplaceColor(themeColor:"Accent5-Medium")] */ border-right:1px solid #6f9dd9; } td.ms-areaseparatorleft{ background:#d6e8ff url("/_layouts/images/bgximg.png") repeat-x -0px -461px; /* [ReplaceColor(themeColor:"Accent5-Medium")] */ border-right:1px solid #6f9dd9; height:100%; } div.ms-areaseparatorleft{ background-repeat:no-repeat; background-position:-143px 0px; /* [ReplaceColor(themeColor:"Accent5-Medium")] */ border-right:1px solid #6f9dd9; height:100%; } div.ms-areaseparatorright{ /* [ReplaceColor(themeColor:"Accent5-Medium")] */ border-left:1px solid #6f9dd9; padding-right:2px; height:100%; } .ms-titlearearight .ms-areaseparatorright{ background:#d6e8ff url("/_layouts/images/bgximg.png") repeat-x -0px -461px; /* [ReplaceColor(themeColor:"Accent5-Medium")] */ border-left:1px solid #6f9dd9; padding-right:2px; height:100%; } .ms-areaseparator{ /* [ReplaceColor(themeColor:"Accent4-Lightest")] */ background-color:#ffeaad; border-right:none; border-left:none; padding-left:5px; height:61px; } .ms-pagemargin{ background-color:#83b0ec; height:100%; } td.ms-rightareacell div.ms-pagemargin{ /* [ReplaceColor(themeColor:"Accent3-Medium")] */ background-color:#83b0ec; height:100%; /* [ReplaceColor(themeColor:"Accent3-Medium")] */ border-left:solid 1px #83b0ec; } .ms-bodyareacell{ vertical-align:top; } .ms-pagebottommargin,.ms-pagebottommarginleft,.ms-pagebottommarginright{ /* [ReplaceColor(themeColor:"Accent3-Medium")] */ background:#83b0ec; } .ms-bodyareapagemargin{ /* [ReplaceColor(themeColor:"Accent3-Medium")] */ background:#83b0ec; /* [ReplaceColor(themeColor:"Accent3-Lighter")] */ border-top:1px solid #6f9dd9; } .ms-bodyareaframe{ vertical-align:top; height:100%; /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; /* [ReplaceColor(themeColor:"Accent3-Lighter")] */ border:1px solid #6f9dd9; } .ms-bodyareaframe{ padding:10px; } .ms-pagetitle{ /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; font-family:verdana; font-size:16pt; margin:0px 0px 4px 0px; font-weight:normal; } .ms-pagetitle a{ text-decoration:none; /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; margin:0; font-weight:normal; } .ms-pagetitle a:hover{ } .ms-vh table.ms-selectedtitle,.ms-vh2 table.ms-selectedtitle,.ms-vh-icon table.ms-selectedtitle,.ms-vh table.ms-unselectedtitle,.ms-vh2 table.ms-unselectedtitle,.ms-vh-icon table.ms-unselectedtitle{ height:21px; } .ms-vh table.ms-selectedtitle,.ms-vh2 table.ms-selectedtitle,.ms-vh-icon table.ms-selectedtitle{ /* [ReplaceColor(themeColor:"Light1-Lighter")] */ background-color:#dde1e5; border:none; } .ms-vh2 .ms-selectedtitle .ms-vb,.ms-vh2 .ms-unselectedtitle .ms-vb{ padding-left:5px; padding-right:5px; padding-top:1px; } .ms-vh-icon .ms-selectedtitle .ms-vb,.ms-vh-icon .ms-unselectedtitle .ms-vb{ padding-left:0px; vertical-align:middle; } .ms-propertysheet th.ms-vh2,.ms-propertysheet th.ms-vh2-nofilter{ font-family:tahoma; } .ms-listviewtable .ms-vh2,.ms-summarystandardbody .ms-vh2{ padding:1px 1px 0px 1px; } .ms-listviewtable .ms-vb2,.ms-summarystandardbody .ms-vb2{ padding-left:2px; padding-right:7px; } .ms-selectedtitle{ /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; /* [ReplaceColor(themeColor:"Accent4-Darker")] */ border:1px solid #b09460; margin:0px; padding:0px; cursor:pointer; } .ms-selectedtitlealternative { /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; /* [ReplaceColor(themeColor:"Accent4-Darker")] */ border:1px solid #b09460; margin:0px; padding:0px; cursor:pointer; } .ms-unselectedtitle{ background-color:transparent; margin:0px; padding:0px; } .ms-newgif{ display:inline-block; margin-left:5px; } .ms-menuimagecell{ /* [RecolorImage(themeColor:"Accent1",method:"Tinting")] */ background:url("/_layouts/images/selectednav.gif") repeat-x; /* [ReplaceColor(themeColor:"Accent6-Lighter")] */ background-color:#ffe6a0; cursor:pointer; /* [ReplaceColor(themeColor:"Light1")] */ border:solid 1px #ffffff; padding:0px; height:18px; } .ms-vh .ms-menuimagecell,.ms-vh2 .ms-menuimagecell,.ms-vh-icon .ms-menuimagecell{ height:20px; } .ms-vh .ms-menuimagecell img,.ms-vh2 .ms-menuimagecell img,.ms-vh-icon .ms-menuimagecell img{ margin-top:2px; margin-bottom:2px; } .ms-descriptiontext{ /* [ReplaceColor(themeColor:"Dark1-Medium")] */ color:#4c4c4c; font-family:tahoma; font-size:8pt; text-align:left; } .ms-statusdescriptiontext { color:#4c4c4c; background-color:#FFFF00; font-family:tahoma; font-size:8pt; text-align:left; } .ms-webpartpagedescription{ font-family:verdana; font-size:8pt; /* [ReplaceColor(themeColor:"Dark1-Lighter")] */ color:#5a5a5a; padding:8px 12px 0px 12px; } .ms-separator { /* [ReplaceColor(themeColor:"Light2",themeShade:"0.02")] */ color:#f1f1f2; background-repeat:repeat-x; border:none; padding-left:4px; font-size:10pt; } .ms-rtetoolbarmenu .ms-separator{ padding-left:0px !important; /* [ReplaceColor(themeColor:"Accent3-Medium")] */ color:#83b0ec; } .ms-separator img { height:12px; width:1px; margin:0px 1px 0px 1px; /* [ReplaceColor(themeColor:"Light2",themeShade:"0.02")] */ background:#f1f1f2; } .ms-propertysheet th.ms-authoringcontrols { /* [ReplaceColor(themeColor:"Accent3-Lightest")] */ background-color:#f1f1f2; text-align:left; } table.ms-authoringcontrols > tbody > tr > td{ vertical-align:middle; } td.ms-authoringcontrols > label,td.ms-authoringcontrols > span > label,td.ms-authoringcontrols > table > tbody > tr > td > label{ vertical-align:middle; } .ms-propertysheet th.ms-linksectionheader { /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; font-family:tahoma; font-size:8pt; font-weight:bold; text-align:left; } .ms-linksectionitemdescription{ padding-left:3px; padding-top:7px; } .ms-propertysheet .ms-sectionheader a,.ms-propertysheet .ms-sectionheader a:hover { /* [ReplaceColor(themeColor:"Dark1-Lighter")] */ color:#525252; text-decoration:none; } .ms-partline { height:3px; /* [ReplaceColor(themeColor:"Dark2",themeTint:"0.17")] */ border-bottom:1px solid #EBEBEB; } .ms-propertysheet{ font-family:verdana; font-size:1em; text-align:left; /* [ReplaceColor(themeColor:"Dark1-Medium")] */ color:#4c4c4c; } .ms-propertysheet th{ font-family:verdana; font-size:8pt; /* [ReplaceColor(themeColor:"Dark1-Medium")] */ color:#4c4c4c; font-weight:normal; } .ms-propertysheet a{ text-decoration:none; /* [ReplaceColor(themeColor:"Accent3-Darker")] */ color:#3966bf; } .ms-propertysheet a:hover{ text-decoration:underline; /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; } .ms-vh,.ms-vh2,.ms-vh-icon-empty,.ms-vhImage,.ms-vh2-nograd,.ms-vh3-nograd,.ms-vh2-nograd-icon,.ms-vh2-nofilter-icon,.ms-ph{ font-weight:normal; /* [ReplaceColor(themeColor:"Light1-Medium")] */ color:#b2b2b2; text-align:left; text-decoration:none; vertical-align:top; } .ms-vh-icon{ vertical-align:middle; } .ms-gb,.ms-gb2,.ms-gbload,.ms-vb-tall,.ms-vb-user,.ms-pb,.ms-pb-selected td{ /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; } .ms-gb a,.ms-gb2 a{ /* [ReplaceColor(themeColor:"Accent3")] */ color:#003399; } .ms-vh,.ms-vh2,.ms-vh-icon,.ms-vh-icon-empty,.ms-vhImage,.ms-gb,.ms-gb2,.ms-gbload,.ms-vb,.ms-vb2,.ms-vb-tall,.ms-vb-user,.ms-vh2-nograd,.ms-vh3-nograd,.ms-vh2-nograd-icon,.ms-vh2-nofilter-icon,.ms-pb,.ms-pb-selected,.ms-ph{ font-size:8pt; line-height:1.2; font-family:Verdana,Helvetica,sans-serif; } .ms-vh,.ms-vh2,.ms-vh2-nograd,.ms-vh3-nograd,.ms-vh2-nograd-icon,.ms-vh2-nofilter-icon,.ms-ph{ white-space:nowrap; } .ms-vh,.ms-vh2,.ms-vh-icon,.ms-vh2-nofilter-icon,.ms-viewheadertr .ms-vh-group,.ms-vh2-nograd,.ms-vh3-nograd,.ms-vh2-nograd-icon,.ms-ph,.ms-pickerresultheadertr{ background-repeat:repeat-x; padding-top:1px; padding-bottom:0px; } .ms-viewheadertr th{ padding-top:5px !important; } .ms-disc .ms-viewheadertr th.ms-vh2{ padding:1px 5px 0px 4px; } .ms-disc .ms-vh2 .ms-selectedtitle .ms-vb,.ms-disc .ms-vh2 .ms-unselectedtitle .ms-vb{ padding-left:4px; } th.ms-vh3-nograd{ width:12px; /* [ReplaceColor(themeColor:"Light1-Darker")] */ color:#949494; font-size:8pt; font-family:tahoma,sans-serif; } .ms-vh .ms-vh{ background-image:none; border-left:none; padding-left:1px; background-color:transparent; } .ms-vh2,.ms-ph{ padding:3px 8px 1px; } .ms-vh-div{ padding-top:5px; } .ms-vh-icon,.ms-vh2-nograd-icon,.ms-vh2-nofilter-icon{ width:12px; } .ms-vh-icon{ padding-left:6px; padding-right:4px; padding-bottom:3px; } .ms-vh-icon-empty{ width:0px; } .ms-vh a,.ms-vh a:visited,.ms-vh2 a{ /* [ReplaceColor(themeColor:"Dark1-Lightest")] */ color:#7f7f7f; text-decoration:none; } .ms-vh a:hover,.ms-vh2 a:hover{ text-decoration:underline; } .ms-imnImgTD { padding-right:2px; padding-bottom:5px; } .ms-vhltr .ms-imnImgTD { padding-right:2px; } .ms-vhrtl .ms-imnImgTD { padding-left:2px; } .ms-imnTxtTD { padding-top:0px; } .ms-vhImage{ width:18pt } .ms-standardheader{ font-size:1em; margin:0em; text-align:left; /* [ReplaceColor(themeColor:"Dark1")] */ color:#525252; } .ms-formlabel h3.ms-standardheader{ font-weight:normal; color:auto; } .ms-linksectionheader .ms-standardheader{ /* [ReplaceColor(themeColor:"Dark1")] */ color:#000000; } .ms-gb{ height:22px; /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; font-weight:bold; /* [ReplaceColor(themeColor:"Accent3-Lighter")] */ border-bottom:1px solid #8ebbf5; /* [ReplaceColor(themeColor:"Light1-Lightest")] */ border-top:1px solid #f9f9f9; padding-bottom:3px; } .ms-gb .ms-vb2{ font-weight:normal; } .ms-listviewtable .ms-gb,.ms-listviewtable .ms-gb2{ padding-top:14px; } .ms-gb2{ height:22px; /* [ReplaceColor(themeColor:"Dark1-Medium")] */ color:#4c4c4c; padding-bottom:3px; /* [ReplaceColor(themeColor:"Accent3-Lightest")] */ border-bottom:1px solid #e3efff; /* [ReplaceColor(themeColor:"Light1-Lightest")] */ border-top:1px solid #f9f9f9; } .ms-gbload{ height:22px; /* [ReplaceColor(themeColor:"Dark1-Medium")] */ color:#4c4c4c; /* [ReplaceColor(themeColor:"Light1")] */ background-color:#ffffff; padding-bottom:3px; } .ms-vb,.ms-vb2,.ms-vb-user,.ms-vb-tall,.ms-pb,.ms-pb-selected { /* [ReplaceColor(themeColor:"Dark1")] */ color:#6d6f72; vertical-align:top; } .ms-vb a:link,.ms-vb2 a:link,.ms-vb-user a:link{ /* [ReplaceColor(themeColor:"Hyperlink")] */ color:#0072BC; text-decoration:none; } .ms-vb a:hover,.ms-vb2 a:hover,.ms-vb-user a:hover{ text-decoration:underline; } .ms-vb a:visited,.ms-vb2 a:visited,.ms-vb-user a:visited{ /* [ReplaceColor(themeColor:"Hyperlink")] */ color:#0072BC; text-decoration:none; } .ms-vb a:visited:hover,.ms-vb2 a:visited:hover,.ms-vb-user a:visited:hover{ /* [ReplaceColor(themeColor:"Hyperlink")] */ color:#0072BC; text-decoration:underline; } .ms-alternatingstrong .ms-vb a:link,.ms-alternatingstrong .ms-vb2 a:link,.ms-alternatingstrong .ms-vb-user a:link,.ms-alternatingstrong .ms-vb a:visited,.ms-alternatingstrong .ms-vb2 a:visited,.ms-alternatingstrong .ms-vb-user a:visited,.ms-alternatingstrong .ms-vb a:visited:hover,.ms-alternatingstrong .ms-vb2 a:visited:hover,.ms-alternatingstrong .ms-vb-user a:visited:hover{ /* [ReplaceColor(themeColor

    Read the article

  • SQL SERVER – Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2

    - by Pinal Dave
    This is the second part of the series Incremental Statistics. Here is the index of the complete series. What is Incremental Statistics? – Performance improvements in SQL Server 2014 – Part 1 Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2 DMV to Identify Incremental Statistics – Performance improvements in SQL Server 2014 – Part 3 In part 1 we have understood what is incremental statistics and now in this second part we will see a simple example of incremental statistics. This blog post is heavily inspired from my friend Balmukund’s must read blog post. If you have partitioned table and lots of data, this feature can be specifically very useful. Prerequisite Here are two things you must know before you start with the demonstrations. AdventureWorks – For the demonstration purpose I have installed AdventureWorks 2012 as an AdventureWorks 2014 in this demonstration. Partitions – You should know how partition works with databases. Setup Script Here is the setup script for creating Partition Function, Scheme, and the Table. We will populate the table based on the SalesOrderDetails table from AdventureWorks. -- Use Database USE AdventureWorks2014 GO -- Create Partition Function CREATE PARTITION FUNCTION IncrStatFn (INT) AS RANGE LEFT FOR VALUES (44000, 54000, 64000, 74000) GO -- Create Partition Scheme CREATE PARTITION SCHEME IncrStatSch AS PARTITION [IncrStatFn] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) GO -- Create Table Incremental_Statistics CREATE TABLE [IncrStatTab]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [ModifiedDate] [datetime] NOT NULL) ON IncrStatSch(SalesOrderID) GO -- Populate Table INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate]) SELECT     [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate] FROM       [Sales].[SalesOrderDetail] WHERE      SalesOrderID < 54000 GO Check Details Now we will check details in the partition table IncrStatSch. -- Check the partition SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('IncrStatTab') GO You will notice that only a few of the partition are filled up with data and remaining all the partitions are empty. Now we will create statistics on the Table on the column SalesOrderID. However, here we will keep adding one more keyword which is INCREMENTAL = ON. Please note this is the new keyword and feature added in SQL Server 2014. It did not exist in earlier versions. -- Create Statistics CREATE STATISTICS IncrStat ON [IncrStatTab] (SalesOrderID) WITH FULLSCAN, INCREMENTAL = ON GO Now we have successfully created statistics let us check the statistical histogram of the table. Now let us once again populate the table with more data. This time the data are entered into a different partition than earlier populated partition. -- Populate Table INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate]) SELECT     [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate] FROM       [Sales].[SalesOrderDetail] WHERE      SalesOrderID > 54000 GO Let us check the status of the partition once again with following script. -- Check the partition SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('IncrStatTab') GO Statistics Update Now here has the new feature come into action. Previously, if we have to update the statistics, we will have to FULLSCAN the entire table irrespective of which partition got the data. However, in SQL Server 2014 we can just specify which partition we want to update in terms of Statistics. Here is the script for the same. -- Update Statistics Manually UPDATE STATISTICS IncrStatTab (IncrStat) WITH RESAMPLE ON PARTITIONS(3, 4) GO Now let us check the statistics once again. -- Show Statistics DBCC SHOW_STATISTICS('IncrStatTab', IncrStat) WITH HISTOGRAM GO Upon examining statistics histogram, you will notice that now the distribution has changed and there is way more rows in the histogram. Summary The new feature of Incremental Statistics is indeed a boon for the scenario where there are partitions and statistics needs to be updated frequently on the partitions. In earlier version to update statistics one has to do FULLSCAN on the entire table which was wasting too many resources. With the new feature in SQL Server 2014, now only those partitions which are significantly changed can be specified in the script to update statistics. Cleanup You can clean up the database by executing following scripts. -- Clean up DROP TABLE [IncrStatTab] DROP PARTITION SCHEME [IncrStatSch] DROP PARTITION FUNCTION [IncrStatFn] GO Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: SQL Statistics, Statistics

    Read the article

  • Design Pattern for Complex Data Modeling

    - by Aaron Hayman
    I'm developing a program that has a SQL database as a backing store. As a very broad description, the program itself allows a user to generate records in any number of user-defined tables and make connections between them. As for specs: Any record generated must be able to be connected to any other record in any other user table (excluding itself...the record, not the table). These "connections" are directional, and the list of connections a record has is user ordered. Moreover, a record must "know" of connections made from it to others as well as connections made to it from others. The connections are kind of the point of this program, so there is a strong possibility that the number of connections made is very high, especially if the user is using the software as intended. A record's field can also include aggregate information from it's connections (like obtaining average, sum, etc) that must be updated on change from another record it's connected to. To conserve memory, only relevant information must be loaded at any one time (can't load the entire database in memory at load and go from there). I cannot assume the backing store is local. Right now it is, but eventually this program will include syncing to a remote db. Neither the user tables, connections or records are known at design time as they are user generated. I've spent a lot of time trying to figure out how to design the backing store and the object model to best fit these specs. In my first design attempt on this, I had one object managing all a table's records and connections. I attempted this first because it kept the memory footprint smaller (records and connections were simple dicts), but maintaining aggregate and link information between tables became....onerous (ie...a huge spaghettified mess). Tracing dependencies using this method almost became impossible. Instead, I've settled on a distributed graph model where each record and connection is 'aware' of what's around it by managing it own data and connections to other records. Doing this increases my memory footprint but also let me create a faulting system so connections/records aren't loaded into memory until they're needed. It's also much easier to code: trace dependencies, eliminate cycling recursive updates, etc. My biggest problem is storing/loading the connections. I'm not happy with any of my current solutions/ideas so I wanted to ask and see if anybody else has any ideas of how this should be structured. Connections are fairly simple. They contain: fromRecordID, fromTableID, fromRecordOrder, toRecordID, toTableID, toRecordOrder. Here's what I've come up with so far: Store all the connections in one big table. If I do this, either I load all connections at once (one big db call) or make a call every time a user table is loaded. The big issue here: the size of the connections table has the potential to be huge, and I'm afraid it would slow things down. Store in separate tables all the outgoing connections for each user table. This is probably the worst idea I've had. Now my connections are 'spread out' over multiple tables (one for each user table), which means I have to make a separate DB called to each table (or make a huge join) just to find all the incoming connections for a particular user table. I've avoided making "one big ass table", but I'm not sure the cost is worth it. Store in separate tables all outgoing AND incoming connections for each user table (using a flag to distinguish between incoming vs outgoing). This is the idea I'm leaning towards, but it will essentially double the total DB storage for all the connections (as each connection will be stored in two tables). It also means I have to make sure connection information is kept in sync in both places. This is obviously not ideal but it does mean that when I load a user table, I only need to load one 'connection' table and have all the information I need. This also presents a separate problem, that of connection object creation. Since each user table has a list of all connections, there are two opportunities for a connection object to be made. However, connections objects (designed to facilitate communication between records) should only be created once. This means I'll have to devise a common caching/factory object to make sure only one connection object is made per connection. Does anybody have any ideas of a better way to do this? Once I've committed to a particular design pattern I'm pretty much stuck with it, so I want to make sure I've come up with the best one possible.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 166 167 168 169 170 171 172 173 174 175 176 177  | Next Page >