Biggest/Longest records in varchar table

We recently noticed that in one of our biggest tables some of the rows are a few times bigger than others. By "bigger" I mean longer and taking more storage space.

How to display top 1000 biggest rows in the table?

Almost all columns are varchar so it would be great if the query could sum up the size of the data in each row and show the biggest rows.

I tried to modify this:

select MyVarcharColumnName from MyTableName where len(MyVarcharColumnName) = (select max(len(MyVarcharColumnName)) from MyTableName)

and this:

select max(len(Desc)) from table_name

but

I get an error

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Desc'.

-------------Problems Reply------------

How about this?

select top 1000 t.*
from MyTableName t
order by len(MyVarcharColumnName) desc;

If you have multiple columns, you can add them to the order by:

order by (len(col1) + len(col2) + . . . ) desc

I have used len() for this calculation, because that is what you are using in your question. You might also be interested in [DATALENGTH()][1].

It thinks that Desc is a keyword rather than an identifier. Try to enclose it in angle brackets ([]), like this:

select max(len([Desc])) from table_name

Category:sql Views:0 Time:2018-11-10

Related post

  • I want to show records from a table having a date column from sqldatabase in dates order. How should I? 2009-10-09

    I want to show records from a table having a date column from sqldatabase in dates order. How should I? --------------Solutions------------- SELECT * FROM yourTable ORDER BY yourDateColumn you can use order by select * from my_table t order by t.date

  • Database slow retriving/updating/inserting problem with more than 5mil records in each table 2009-12-25

    How to structure database to avoid slowdowns? (Engine: MyISAM) Currently i have database with more than 5milion records in one table that causes slow data retrieving. I'm currently searching for ways to structure database to avoid this kinds of datab

  • how we can delete record of a table when record of another table deleted using function,trigger in postgresql -concepts 2010-02-04

    how we can delete record of a table when record of another table deleted using function,trigger in postgresql -concepts --------------Solutions------------- Use "ON DELETE CASCADE" on the column definition. Something like this will do what you want:

  • SQL query to select records from three Tables seperatly 2010-04-15

    SQL query which select the record from three tables and there is no relation between these tables. Actually I want to make it a VIEW. suppose there are three tales Table1, Table2, Table3 I want to show records of Table1 first with some filter criteri

  • How can I clone records in a table and know the new and old ID? 2010-07-20

    I want to copy N number of records within a table which is easy with a INSERT SELECT however after the insert I want a list showing oldId and newId. So lets say I have a SELECT statement which returns: ID | Name 3 | Test A 4 | Test B 5 | Test C If I

  • sql server insert records from one table to another 2010-07-26

    how does one insert records from one table to another that has a unique index in the destination table without going through the insert and then removal of duplicates by deleting the index? INSERT INTO forms(url,feedUrl, dateadded) SELECT url, feedur

  • How to delete two records from two tables? 2011-03-15

    Hello I need to be able to search for a record that is a year old and then delete it. I have this script which allows me to delete the record from one table, based on a date given by another table,however I need to add code to this so that I am able

  • How to pick Duplicate Records from SQL Table? 2011-07-08

    SQL Table How to pick duplicate records, do i need to use cursor ??? Following is my Query.. select RxNbr,[Fill Date],NDC,GenericCode from vw_TempTransaction order by GenericCode Asc If the Column : RxNbr and Column : [Fill Date] has the same value a

  • How to delete the contents of a related record from two tables? 2011-12-11

    The StudentDetails.Students is the master table which forms a relationship with RegistrationDetails.Registration. Therefore, StudentID is the primary key in the former whereas it is a foreign key in the latter. Now I've tried each of the following co

  • How to merge records from two tables using group by clause in oracle? 2012-03-10

    I have two tables ward1 and ward2ie; create table ward1 ( ward_id int ) create table ward2 ( ward_id int, ward_name varchar(10) ) and the values are -- insert into ward1 values(101); insert into ward1 values(101); insert into ward1 values(102); inser

  • Getting unique record from a table in mysql 2012-03-29

    I have a problem in mysql. I need to fetch certain sets of records from a table that has the following data. Let me brief you guys about the table. every primary item has a similar item and that similar item has a size. but we have to show unique ite

  • Fetch records from database table for month wise 2014-10-19

    I want to retrieve records from a table based on some conditions See this is my condition : Query to get sum of amount based on the transaction type month wise See below is my table: +-----------+-------------+ | Field | Type | +-----------+---------

  • Enforce inserting records into multiple tables 2015-01-07

    I have two tables parent and child. I would like to insert a record in both tables only if the query succeeds for both. Ideally, integrity would be enforced by the database, however, doing so using the application would be acceptable if necessary. Po

  • Change each record in a table with no primary key? 2008-11-09

    I have a table in a database that represents dates textually (i.e. "2008-11-09") and I would like to replace them with the UNIX timestamp. However, I don't think that MySQL is capable of doing the conversion on its own, so I'd like to write a little

  • NHibernate - creating records in related tables 2008-11-21

    Hibernate version: 2.0.1GA I'm trying to add a single record in two tables. The tables are related. I have a beginner's knowledge of NHiberate so I'm hoping there's a better way to do this. Presently, I am under the belief that the only way I can get

  • using buttons to call specific records from a table 2009-01-13

    How do i pull the last records from different tables all with various amounts of records in them? here is some of my code, but it is returning nothing as my last set of records: private int firstRecord = 1; private int lastRecord = 10; private int so

  • SQL query: Delete all records from the table except latest N? 2009-02-23

    Is it possible to build a single mysql query (without variables) to remove all records from the table, except latest N (sorted by id desc)? Something like this, only it doesn't work :) delete from table order by id ASC limit ((select count(*) from ta

  • How do I delete a record from a table? 2009-03-26

    How do I delete a record from a table? For example, the table has two columns: eno and ename. I want to delete one value in ename from the table: Eno Ename 1 asha 2 bimal I want to delete the value 'bimal'. --------------Solutions------------- Your q

  • Deleting a record in a table which has foreign key relational tables 2009-05-14

    Hi guys i am new to database my question is how to delete a records in a table which has foreign key relation suppose i have a 2 tables employee & department which has deptID as relation now i want to delete a specific row in a table like "delete

  • Delete all records from a table 2009-05-18

    How can I delete all records from a table using SubSonic? The Delete method has three overloads, but each one expects some parameters. And how can I delete records using a query (e.g. delete all records where column1 > 100) --------------Solutions

  • How do I join to the latest record in the table? 2009-05-19

    What I need done is simple... but its 3am and Im probably overlooking the obvious. Im coding a simple forum. One table stores the forum titles, descriptions, etc, while the other stores the posts. In the forum listing, that shows the list of all foru

  • Is there any demo to insert a new record into a table with identity primary key using Subsonic 3? 2009-07-07

    Is there any demo to insert a new record into a table with identity primary key using Subsonic 3? --------------Solutions------------- Create DAL object Assign values to all properties except primary key property Call Save() Subsonic takes care of th

  • How to delete all records in a table using SubSonic 3 2009-08-26

    I'm trying to delete all the records from a table using this approach: new Delete<Contact>().Execute(); This statement fails with a NullReferenceException in BuildDeleteStatement method at line: sb.Append(query.FromTables[0].QualifiedName); Bec

  • How to convert records in a table to xml format in PHP? 2009-09-16

    I fetched some records from a table named a2h_member in php. i want to convert it into XML format. How can i do this?. Give me some suggestions or code. Please Help me --------------Solutions------------- The simpler the better: $xml = '<?xml vers

  • How to refresh a page when record in db table get changed in ASP.Net 3.5 2009-09-17

    I have a page that contains a Gridview showing a record from a db table e.g. "tblEmployee". If a record is inserted/updated/deleted in that table then I need my page to be reloaded so that the gridview will show the updated records. Records may be in

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.151 (s). 11 q(s)